MySQL Notes 02
八、字符集
1.存储过程：
网页/其他客户端： 连接器： MySQL服务器：
客户端的编码 ------------> 临时编码 -----------> 最终保存编码
案例一：
客户端的编码是GBK，服务器的编码是UTF8，
方案一：
先声明 客户端的编码是 GBK ，
再声明 连接器使用 GBK 编码。
运行结果：
接收客户端的GBK，转化成GBK，暂存在连接器中，由连接器转成UTF8，最终存储在服务器。
方案二：
先声明 客户端的编码是 GBK ，
再声明 连接器使用 UTF8 编码。
运行结果：
接收客户端的GBK，转化成UTF8，暂存于连接器中，由连接器转成UTF8，最终存储在服务器。
案例二：
客户端的编码是GB2312，服务器的编码是UTF8，
方案：
先声明 客户端的编码是 GB2312 ，
再声明 连接器使用 GBK 编码。
运行结果：
接收客户端的GB2312，转化成GBK，暂存于连接器中，再由连接器转成UTF8，最终存储在服务器。
★如何保证 存储过程中 不会出现乱码：
保证 字符集在每个阶段都 转化为 不变的 或 更大的 字符集（至少保持不变）。
例如：客户端是GBK编码，连接器是UTF8编码，服务器是UTF8编码，这样就是合理的。
反之，例如：客户端是UTF8编码，连接器是GBK编码，服务器是GBK编码，这样在 UTF8 转化成 GBK 时就会存在一定风险，有可能会丢失一些字符。
☆ UTF8 是目前最为庞大的字符集。GBK 只能表示4万多个字符，而 UTF8 理论上可以表示上千万个字符。
（ UTF8 的1个字符由3个字节组成，而 GBK 的1个字符由2个字节组成）
正因为 UTF8 的字符集比GBK更庞大。所以当 UTF8 转化为 GBK 时就会有丢失字符的可能！
2.取出过程：
网页/其他客户端： MySQL服务器：
客户端的编码 <------ 查询结果的字符集 ------- 最终保存编码
我们可以声明 查询结果的字符集 。
★声明 查询结果字符集 实质上就是 将 查询结果的字符集 转化为 客户端能够正常显示的编码。
所以 查询结果字符集 必须与 客户端的编码 一致。
例如：客户端是GBK编码，则 查询结果字符集 也必须是GBK。
反之，例如：客户端是GBK编码，而 查询结果字符集 是UTF8，则客户端将会显示乱码。
总结：
★三个重要概念：
① 客户端字符集
② 连接器字符集
③ 查询结果字符集
案例：【杜绝乱码的出现】
在XP的 cmd 窗口中，客户端是GBK编码，服务器是UTF8编码。
作以下声明：
①声明 客户端的编码是 GBK 则：set character_set_client=gbk;
②声明 连接器使用 GBK 或 UTF8 则：set character_set_connection=gbk; 或 set character_set_connection=utf8;
③声明 查询结果字符集为 GBK 则：set character_set_results=gbk;
★如果以上三个声明的字符集都一样(都是GBK)，则可以简化为：set names gbk;
在建表时：
create table 表名(
......
)charset utf8;
# 声明 服务器是以 UTF8 编码来存储数据。
九、存储引擎
设有张马虎、李小心两人，都是地铁口的自行车管理员。
每天都有很多人来存取自行车，张马虎的管理方式是：来存自己存，不记录存的是什么车，取时交5毛，也不检查取的是否是自己的车。
而李小心则在存取自行车时，记录存车人的特征与自行车的特征，当人来取车时，还要小心核查一下人与车的特征是否对应。
思考:
张马虎和李小心谁对业务的处理速度更高?
二者谁对自行车管理更安全?
★数据库对同样的数据，有着不同的存储方式和管理方式。在MySQL中，称为存储引擎。
最常用的存储引擎是 Myisam 与 InnoDB ，其主要特点如下：
+---------------------+------------+-------------+
| 特 点 | Myisam | InnoDB |
+---------------------+------------+-------------+
|批量插入的速度 | 高 | 低 |
+---------------------+------------+-------------+
|事务安全 | | 支持 |
+---------------------+------------+-------------+
|全文索引 | 支持 | (5.5版本 支持)
+---------------------+------------+-------------+
|锁机制 | 表锁 | 行锁 |
+---------------------+------------+-------------+
表锁 就是以【表】为单位来锁。
例如：我正在读取 A表 的第3行记录，就会把 A表 锁起来不让其他人操作。
行锁 就是以【行】为单位来锁。
例如：我正在读取 A表 的第3行记录，就会把 A表的第3行 锁起来不让其他人操作。
声明 存储引擎的形式，如下：
create table 表名(
......
)engine innodb 或 myisam
存储引擎的选择：
文章、新闻等对安全性要求不高的，选 myisam引擎。
订单、资金、账单、火车票等对安全性要求高的，可以选用 innodb引擎。
对于临时中转表，可以用 memory引擎，速度最快。
十、事务
思考以下问题：
我去银行给朋友汇款，我的卡上有1000元，朋友卡上500元，我给我朋友转账200元（无须手续费），
如果，我的钱刚扣，而朋友的钱又没加 时，网线断了(或停电了 之类的情况)，怎么办？
事务的4个特性，这4个特性通常称为 ACID 特性：
1.原子性(Atomicity)：
（原子意为最小的粒子，或者说不能再分的事物）
一个事务是一个不可分割的工作单位。
组成事务的所有操作 要么全部执行(都成功执行)，要么全部取消(都不执行)（例如 上面的汇款的情况）。
2.一致性(Consistency)：
指 数据的总额，在事务前/后 依然能 按照规则 保持一致。
例如：
我卡上有200元，小明卡上有100元，总额是300元。
现在我给小明汇100元。
事务完成后，我的卡上只剩100元，小明的卡上有200元，但总额依然是300元。
3.隔离性(Isolation)：
★ 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
例如：
当你正在用事务对 某一个表进行操作，则其他人不能同时对该表进行操作，必须等你的事务执行完毕之后。
★ 若当前操作没有执行完毕，则对其他事务绝对不可见。
反面教材：
曾经有这样的一宗诈骗，a、b两人，a到银行，打算凭卡号存款10000元，当存到一半时，b已经在ATM看到了存进来的10000元，他马上取出。
但其实，此时a还没完全存款完毕，a就提出各种原因，要求取消存款，这样他们俩就骗取了银行N多的钱，结果被关进牢房。
这是因为银行在系统方面的 隔离性 没做好，让骗徒有机可乘。
4.持久性(Durability)：
一旦事务完毕，无法撤销。
如果出了错误，只能通过“补偿性事务”进行补救。
事务的使用：
★注意：若要使用事务，必须在建表的时候，选择 innodb存储引擎 或 bdb存储引擎。
开启一个事务：start transaction;
提交一个事务：commit;
撤销(回滚)一个还没有提交的事务：rollback;
例如：
start transaction; # 开启事务。
update 表 set money = money + 1000 where name = '李四';
update 表 set money = money - 1000 where name = '张三';
commit; # 提交事务。
★注意：
①当一个事务 被 commit 或 rollback ，该事务就结束了。
②有一些语句会造成事务的隐式提交，比如：start transaction
十一、索引
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
我们可以针对表中的某一个字段(列)建立索引。
最常用的两个索引算法：btree(二叉树)索引、hash(哈希)索引
索引的好处与坏处：
好处：
使用索引会提高 查询 速度。
坏处：
使用索引会降低 增、删、改 的速度，而且增大了表的文件大小（索引文件甚至可能比数据文件还大）。
在 mysql 目录下的 data 目录下 保存着 所有数据库的信息文件，在每个数据库对应的目录下，它的每张表 都有对应的3个信息文件。
.frm 结构文件，代表表的结构信息。例如：该表有多少个字段，每个字段叫什么名字，使用了什么存储引擎，都在这里做了描述。
.MYD 数据文件，代表该表的数据。
.MYI 索引文件，代表该表的索引。
常常会出现 某张表的 .MYI(索引文件)，比 .MYD(数据文件)还要大。
面试题：
如果要连续导入 一百万 条数据，但是因为有索引，索引每导入一条数据 都要修改一下索引，这样效率很低。
我们应该如何提高导入的效率呢？
答：我们可以把所有的索引先删掉，然后批量导入，最后再建立索引。
索引的使用原则：
①不过度索引
②索引条件列（常用于where条件的字段，例如：id、用户名 等）
③索引散列值，过于集中的值不要索引（例如：给性别“男”、“女”加索引，意义不大）
★索引的类型：
①普通索引 index 作用：仅仅是加快查询速度。
②主键索引 primary key 作用：一张表，只能用一个字段作为主键，而且每个主键是唯一的，不能重复的。例如：id 。
③唯一索引 unique 作用：其值也是唯一的，不能重复的。例如：username、email 。
④全文索引 fulltext
作用：上述3种索引，都是针对 字段里的值 进行索引。但是全文索引可以针对 字段里的值 中的某个单词 进行索引。
例如：有一个字段text，该字段用于存放长篇的文章。我们不可能在查询时 使用 where text = "...文章内容..." 这种形式。这时候，我们就需要用到全文索引。
建立索引：
①我们可以在建表时，直接声明索引，即在字段声明完毕后，再声明索引。
例如：
create table 表名(
#声明字段：
id int,
username varchar(20),
school varchar(20),
intro text,
#建立索引：
primary key(id), #建立主键索引。
unique(username), #建立唯一索引。
index(school), #建立普通索引。
fulltext(intro) #建立全文索引。
)engine myisam charset utf8;
②我们也可以在建表以后，通过 alter table 的形式 建立索引。
例如：
alter table 表名 add primary key (字段名); #建立主键索引。
alter table 表名 add unique (字段名); #建立唯一索引。
alter table 表名 add index (字段名); #建立普通索引。
alter table 表名 add fulltext (字段名); #建立全文索引。
查看索引：
show index from 表名;
（如果觉得不好看可以在后面加上 \G）
show index from 表名 \G;
删除索引：
删除索引时，要用 索引的名字（key_name），而在不特殊声明的情况下，索引的名字与列名是一样的。（我们可以通过 查看索引 查找）
alter table 表名 drop index 索引的名字;
删除主键索引（★因为一张表中只允许有一个主键索引，所以 索引的名字 都不用加）：
alter table 表名 drop primary key;
全文索引的使用：
例如：
select id,match(全文索引名) against ('单词') from 表名;
执行上述语句中【 match(全文索引名) against ('单词') 】会返回一个“匹配度” 如下：
+-----+------------------------------------------------+
| id | match(全文索引名) against ('单词') |
+-----+------------------------------------------------+
| 1 | 0.94626384973526 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
+-----+------------------------------------------------+
★匹配度越高，代表相似度越高。匹配度为0，则代表毫不相关。
因此，我们可以根据上述的原理，得到下面的语句：
select * from 表名 where match(全文索引名) against ('单词') > 0;
★注意：
①在全文索引中，过短的 或 出现过于频繁的 单词，不会为之建立索引。
例如：is,this,thar,there,to 等等。这个概念叫“停止词”。
②在MySQL的默认情况下，全文索引对于中文意义不大。
因为英文有空格、标点符号来拆成单词，进而对单词进行索引。
但对于中文，没有空格来隔开单词，所以MySQL无法识别每个中文词。
怎么让全文索引对中文有比较大的意义呢？
答：利用 中文分词技术，Sphinx中包含中文分词技术。
十二、触发器 trigger
一触即发：监视某件事，当某件事发生时，触发另一件事。
例如：
在商城中，客户每买一个商品，该商品的库存记录中就应该减少一个。
★触发器的4要素：
①监视的地点 ---> 【表】
②监视的时间 --->（之前、之后）
③监视的事件 --->【增、删、改】
④触发的事件 --->【增、删、改】
建立触发器:
★注意：在建立触发器前，我们需要先修改语句的定界符（默认是分号 ; ）。
★修改语句的定界符：
delimiter 新的定界符
例如：
delimiter $
建立触发器的语句：
delimiter $
create trigger 触发器名 【监视时间 即after或before】 【监视事件】 on 【监视地点 即表名】 for each row
begin
【触发事件1】;
【触发事件2】;
【触发事件3】;
...
【触发事件n】;
end$
★监视的表 中 新增的行：new.字段名
★监视的表 中 删除的行：old.字段名
★监视的表 中 修改前的行：old.字段名
★监视的表 中 修改后的行：new.字段名
例一，添加某个订单时，减少商品的库存：
先分析 触发器的4要素：
监视的地点 --> 订单表
监视的时间 --> after
监视的事件 --> insert★
触发的事件 --> update语句
delimiter $
create trigger 触发器名 after insert on 订单表 for each row
begin
update 商品表 set num = num - new.订单表中商品数量字段 where goods_id = new.订单表中商品id;
end$
# 上述语句的意思是：当 订单表 发生 insert操作 之后，就在 商品表 中执行指定的 update 语句。
例二，取消某个订单时，恢复(增加)商品的库存：
先分析 触发器的4要素：
监视的地点 --> 订单表
监视的时间 --> after
监视的事件 --> delete★
触发的事件 --> update语句
delimiter $
create trigger 触发器名 after delete on 订单表 for each row
begin
update 商品表 set num = num + old.订单表中商品数量字段 where goods_id = old.订单表中商品id;
end$
例三，修改某个订单时，修改商品的库存：
先分析 触发器的4要素：
监视的地点 --> 订单表
监视的时间 --> after
监视的事件 --> update★
触发的事件 --> update语句
delimiter $
create trigger 触发器名 after update on 订单表 for each row
begin
update 商品表 set num = num - new.订单表中商品数量字段 + old.订单表中商品数量字段 where goods_id = old.订单表中商品id;
end$
# num - (new.XXX - old.XXX) = num - new.XXX + old.XXX
例四【经典案例】，要求 对所下的订单进行判断，如果订单的数量>5，就认为是恶意订单，强制把所订的商品数量改成5：
先分析 触发器的4要素：
监视的地点 --> 订单表
监视的时间 --> before★
监视的事件 --> insert
触发的事件 --> update语句
delimiter $
create trigger 触发器名 before insert on 订单表 for each row
begin
if new.订单表中商品数量字段 > 5
then
set new.订单表中商品数量字段 = 5;
end if;
update 商品表 set num = num-new.订单表中商品数量字段 where goods_id = new.订单表中商品id;
end$
# 上述语句的意思是：当 订单表 发生 insert操作 之前，先判断 要下订单的商品的数量如果大于5 则改为5，然后修改商品表中的库存，最后再给 订单表 进行 insert操作。
★注意：如果你要对【监视的事件】进行判断，则【监视的时间】需要用 before ，如上例。
查看触发器：
show triggers;
（如果觉得不好看可以在后面加上 \G）
show triggers \G;
删除触发器：
drop trigger 触发器名;
十三、视图
把一个查询结果时时刻刻映射到一张表上。这张表不实际存在，其实它只是一个查询结果。这种“表”就叫视图。
★注意：
从结构上来说，视图和表是区分不出来的。
我们可以在 show tables; 的结果中 找到已经创建好的视图。
我们也可以通过 desc 视图名; 查看视图的结构。
创建视图：
create view 视图名 as 【select语句】;
# 意思就是 用【select语句】的结果 创意一个视图。
删除视图：
drop view 视图名;
修改视图：
alter view 视图名 as 【select语句】;
★视图的作用：
①我们可以通过视图，把 指定的查询结果 当作 表 来使用，从而简化查询。
②因为MySQL的权限控制只能精确到表，而不能精确到字段。但我们可以通过视图来实现“对字段的权限控制”。
例如，有一张 员工信息表，表上有4个字段，分别是：姓名、年龄、入职年份、工资。
要求 工资 字段不允许查看：
create view 视图名 as select 姓名,年龄,入职年龄 from 员工信息表;
#思路分析：挑选出 员工信息表 中 允许查看的字段 创建成一个视图，并对该视图的权限开放。
③在大数据分表时可以用到
比如，当表的行数超过 200万行时，就会变慢。我们可以把一张表的数据拆成4张表来存放。
把一张表的数据分散到4张表里，分散的方法有很多，最常用的方法是将id取模：
id%4 + 1 = [1,2,3,4]
例如：
$_GET['id'] = 17，
17%4 + 1 = 2，
$tableName = 'table'.'2'
当要查询 id=17 的记录时，我们可以根据同样的算法 对id进行计算id%4+1，就可以知道该记录存放在 table2 中。
我们还可以通过视图，把4张表合并成一个视图，从而进行查询：
create view 视图名 as
select * from table1
union
select * from table2
union
select * from table3
union
select * from table4;
视图与表的关系：
视图是表的查询结果，如果表的数据改变了，自然会影响视图的数据。
如果视图改变了呢？
答：视图的增、删、改也会影响表。但是，视图并非总是能增、删、改的。
如果 视图中的某个字段 是通过【聚合函数】或 表达式 所得出的结果，则不能对其进行增、删、改操作。
对视图 insert 时，还应注意：
视图必须包含表中没有默认值的列。
视图的 algorithm (算法)：
Algorithm = merge/temptable/undefined
merge:
如果创建视图时选用 algorithm = merge 此时视图仅仅是一个“select语句”。
即 当查询该视图时，系统会先分析【查询视图的语句】和【创建视图的语句】，然后将两者合并成一个综合的select语句。
例如：
create algorithm = merge view 视图名 as 【select语句】;
temptable:
如果创建视图时选用 algorithm = temptable 此时系统会根据视图的创建语句 建立一张临时表。
即 当查询该视图时，系统先根据【创建视图的语句】建立一张临时表，然后再根据【查询视图的语句】对该临时表进行查询。
例如：
create algorithm = temptable view 视图名 as 【select语句】;
undefined:
未定义，自动让系统帮你选。
十四、存储过程
存储过程 就是把一条或多条的sql语句，封装成一个 sql语句集，调用该 sql语句集，则能执行多条sql语句。
存储过程就类似“函数”。
★事实上，在MySQL中也有 函数，但它与存储过程的差别非常小，所以我们可以将存储过程理解为函数。
（在MySQL中，存储过程 与 函数 的分别就是：存储过程没有返回值，而函数有返回值。）
创建存储过程：
delimiter $
create procedure 存储过程名(参数)
begin
sql语句1;
sql语句2;
sql语句3;
...
sql语句n;
end$
★调用存储过程：
call 存储过程名(参数);
查看已有的存储过程：
show procedure status;
（如果觉得不好看可以在后面加上 \G）
show procedure status \G;
删除存储过程：
drop procedure 存储过程名;
★使用参数：
例如：
delimiter $
create procedure 存储过程名(i int)
begin
select * from 表名 where id = i;
end$
★使用控制结构：（MySQL也可以编程）
例如：
delimiter $
create procedure 存储过程名(n tinyint)
begin
declare s int; # 声明变量s
declare i int; # 声明变量i
set s=0;
set i=0;
#当i<n时执行循环：
while i<n
do
set s=s+i;
set i=i+1;
end while;
#可以通过 select 查看变量：
select s;
end$
MySQL的函数
例如:
#创建一个最简单的求和函数：
delimiter $
create function qiuhe(x int,y int)
returns int #声明返回值的类型。
begin
return x+y;
end$
#调用函数：
select qiuhe(5,6)$
十五、MySQL的备份与恢复
我们可以对【表】进行备份，也可以对【库】进行备份。
在 MySQL安装目录下的 bin目录下，可以找到一个程序 mysqldump.exe 。
mysqldump.exe 是官方自带 专门用于导出数据的工具。
使用 mysqldump.exe 导出数据（备份数据）：
1.导出一个库下的所有表，即一个库中的内容，而不是一个库（以表为单位）：
先来到 MySQL安装目录下的 bin目录下，然后输入下命令行
mysqldump -u用户名 -p密码 库名 > 路径/备份文件名.sql
例如：
mysqldump -uroot -p111111 test > D:/test.sql
★注意：MySQL的导出文件 的内容都是 sql语句。而 SQLServer的导出文件 的内容是二进制数据。
2.导出指定的表（以表为单位）：
先来到 MySQL安装目录下的 bin目录下，然后输入下命令行
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 路径/备份文件名.sql
例如：
mysqldump -uroot -p111111 test table01 table02 table03 > D:/test_tables.sql
3.导出整个库（以库为单位）：
先来到 MySQL安装目录下的 bin目录下，然后输入下命令行
mysqldump -u用户名 -p密码 -B 库1 库2 库3 > 路径/备份文件名.sql
例如：
mysqldump -uroot -p111111 -B test shop students > D:/database.sql
4.导出所有库（以库为单位）：
先来到 MySQL安装目录下的 bin目录下，然后输入下命令行
mysqldump -u用户名 -p密码 -A > 路径/备份文件名.sql
例如：
mysqldump -uroot -p111111 -A > D:/all.sql
导入数据（恢复数据）：
★其实就是 告诉客户端，用指定文件的内容当成sql语句来执行。
1.直接导入库（以库为单位）：
（在mysql客户端下，执行以下语句）
source 路径/备份文件名.sql
（也可以直接运行以下命令行）
mysql -u用户名 -p密码 < 路径/备份文件名.sql
2.给某个库导入表（以表为单位）：
（在mysql客户端下，执行以下语句）
use 库名; #先选择库。
source 路径/备份文件名.sql
（也可以直接运行以下命令行）
mysql -u用户名 -p密码 库名 < 路径/备份文件名.sql
