day37_mysql扩展知识
1、事务
1.1 事务
事务用于将某些操作的多个sql作为原子性操作，一旦某一个出现错误，即可回滚到原来状态，从而保证数据库数据完整性
即操作数据前设置事务开始，数据操作完成，提交事物
操作数据期间，数据只是临死的，只有提交事务之后，才能真正保存到数据库
语法：
start transaction;
'''数据操作'''
# 若有异常，即可回滚到start transaction;
rollback;
# 若无异常，提交commit 若没有提交，那么别的用户查看该数据表不会更新，并且当前用户退出后再登陆查看也不会保存数据
commit;
1.2 事务的特征
原子性（Atomicity）：原子意为最小的粒子，即不能再分的事务，要么全部执行，要么全部取消
一致性（Consistency)：指事务发生前后，数据的总额依然匹配
隔离性（Isolation)：简单点说，某个事务的操作，对其他事务是不可见的
永久性 (Durability)：当事务完成后，其影响应该保留下来，不能撤销，只能通过“补偿性事务”来抵消之前的错误
1.3 存储引擎
InnoDB：支持事务，支持行锁
MyIsam：不支持事务，支持表锁
mysql5.5版本以后，默认使用InnoDB
建表的时候，会自动设置存储引擎，也可手动设置
create table user(
id int,
name varchar(32),
salary int not null default 0,
)engine=InooDB charset utf8;
2、视图
2.1 什么是视图
视图是一个虚拟表，本质是根据sql语句获取动态的数据集，并为其命名
使用视图名即可获取结果集，并将结果集当作表来使用
使用视图保存临时表，存放在数据库中
如果使用sql过分依赖数据库中的视图，即具有强耦合性，那么扩展sql将极为不便，因此不推荐使用
2.2 使用视图
2.2.1 创建视图
语法：
create view 视图名称 as sql语句
2.2.2 使用视图
视图存储的是临时表，即将sql语句的结果集作为一个表，因此我们在需要用到这个语句时，或者需要用到这个结果集里的数据时，就能使用视图，将之当成一个表来使用，无需再重写sql语句
2.2.3 修改视图
语法：
alter view 视图名称 as sql语句
2.2.4 删除视图
语法：
drop view 视图名称
2.2.5 注意事项
1、对视图的操作会改变原表数据
2、虽然无需再重写sql语句，但是效率并不高，甚至还不如重写sql语句
3、一旦sql需要修改，且涉及到视图的部分，那么必须去数据库中修改，因为视图时存储在数据库中的，很麻烦
4、在涉及多个表的情况下，根本无法修改视图中的记录
3、触发器
使用触发器，可以定制用户对表进行【增删改】操作时前后的行为，需要注意，没有查询
3.1 创建触发器
语法：
create 触发器名 触发条件 数据库操纵语言DML on 表名
[for each row]
when (condition)
declare
begin
--触发器代码 # 触发器的程序体可以是一句sql语句，也可以是begin和end包含起来的多条语句
end;
触发条件：before/after/instead of
数据库操纵语言DML：insert/update/delete
[for each row]:可选项，选了，说明该触发器是一个行级触发器，代表处理每一条记录都会执行触发器；不选，代表是一个语句级别的触发器，每个DML语句触发一次
when (condition):当为行级触发器时，可以设置触发器的响应条件，当操作的记录满足condition时，触发器才被执行;condition中可以通过new和old对象来引用操作的记录
delimiter的作用
设定sql分隔符的命令，默认为;
当使用begin和end包含多个sql语句时，由于;是分隔符，解释器遇到它就会执行sql语句，但是此时没有检测和begin匹配的end就会报错
因此，在这个情况下，需要将sql分隔符设定改成别的，在end之后再改回默认值;
实例：
# 创建触发器：当向tb1表中添加一条数据的同时， 向tb2表添加一条数据
delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON t2 FOR EACH ROW
BEGIN
INSERT INTO t3 (NAME) VALUES ('aa');
END //
delimiter ;
查看触发器结构：
语法：
show triggers\G;
3.2 使用触发器
触发器无法被用户直接调用，只有再触发条件出发时，才会被动执行
3.3 删除触发器
语法：
drop trigger 触发器名
4、存储过程
4.1 什么是存储过程
存储过程包含了一系列的可执行sql代码
存储过程存放于mysql中，通过调用它的名字可以执行内部的一堆sql
4.2 存储过程优缺点
优点：
1、用于替代程序写的sql语句，实现程序与sql解耦
2、基于网络传输，传别名的数据量小，而直接传sql数据量大
缺点：
程序扩展性差
4.3 创建存储过程
4.3.1 无参
delimiter //
create procedure 存储过程别名()
BEGIN
sql语句
END //
delimiter ;
实例：
delimiter //
create procedure p1()
BEGIN
select * from user where id=2;
END //
delimiter ;
4.3.2 有参
delimiter //
create procedure 存储过程别名(
in # 用于传入参数
out # 用于返回值
inout # 既可以传入又可以当作返回值)
BEGIN
sql语句
END //
delimiter ;
实例：
delimiter //
create procedure p1()
BEGIN
select * from user where id=2;
END //
delimiter ;
4.4 执行存储过程
语法：
call 存储过程别名();
4.5 删除存储过程
语法：
drop procedure 存储过程别名 # 注意，不带括号
5、函数
mysql提供了许多内置函数，
HAR_LENGTH(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。
CONCAT(str1,str2,...)
字符串拼接
如有任何一个参数为NULL ，则返回值为 NULL。
FORMAT(X,D)
将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。
例如：
SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。
LEFT(str,len)
返回字符串str 从开始的len位置的子序列字符。
LOWER(str)
变小写
UPPER(str)
变大写
LTRIM(str)
返回字符串 str ，其引导空格字符被删除。
RTRIM(str)
返回字符串 str ，结尾空格字符被删去。
SUBSTRING(str,pos,len)
获取字符串子序列
LOCATE(substr,str,pos)
获取子序列索引位置
REPEAT(str,count)
返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
若 count <= 0,则返回一个空字符串。
若str 或 count 为 NULL，则返回 NULL 。
REPLACE(str,from_str,to_str)
返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)
返回字符串 str ，顺序和字符顺序相反。
RIGHT(str,len)
从字符串str 开始，返回从后边开始len个字符组成的子序列
6、运维方向
6.1 数据库备份
语法：
mysqldump -h 服务器 -u用户名 -p密码 数据库名 表1名, 表2名,.... > aaa.sql
用法：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql
#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql
#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql
6.2 重新导入
语法：
source .sql备份文件路径
7 三大范式
7.1 范式
1、设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小
2、目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）
7.2 三大范式：第一范式、第二范式、第三范式
第一范式：数据库表的每一列必须是一个值唯一确定的信息，不能是列表，集合等不确定信息
第二范式：
1、在第一范式的基础上
2、数据库的表中每一条记录都是唯一的，依赖于主键来实现
第三范式：
1、在第二范式的基础上
2、多表之间的数据可以通过主键唯一找到的时候，这些数据字段只能在一张表中，避免冗余
