MySQL学习笔记（四）
有点自闭昂，电脑意外关机，之前打的东西没有保存下来，只好重新整理一次。
一、杂七杂八（前面内容的补充）
1. union——将表进行上下链接，区别与join
union 会自动对两张表进行去重， union all 不会实现去重。
-- 自动去重 select id,name from tb1 union select num,sname from tb2 -- 不去重 select sid,sname from student UNION ALL select sid,sname from student
2. 设置用户的权限管理
（1）基于用户的权限管理
（2）基于角色的权限管理——之后权限更改时更好调整，因为一般权限都是随角色更改的，而不是随着用户更改的。
多考虑考虑需求，理清楚需求之后再行动，不要盲目敲代码
二、视图
某个查询语句创建别名，日后方便使用。
视图是一个虚拟表，非物理表。（但是如果对虚拟表进行数据的插入操作，那么原本的物理表中的数据也会发生变化）
#创建视图 CREATE VIEW v1 AS (SELECT tname FROM teacher WHERE tid < 3) #删除视图 DROP VIEW v1 #修改视图 ALTER VIEW v1 AS （SQL语句）
三、触发器
只需要执行第一个操作，后续操作可自动触发
-- 整体基本语法 # 插入前 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN ... END # 插入后 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN ... END # 删除前 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN ... END # 删除后 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW BEGIN ... END # 更新前 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN ... END # 更新后 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW BEGIN ... END
-- 实际案例——插入新值的案例 -- 当执行对student表中的插入语句时，就会执行对class中插入值“aaaa” CREATE TRIGGER tyr1 BEFORE INSERT on student for EACH ROW BEGIN INSERT into class(caption) VALUES ("aaaa"); END// delimiter; INSERT into student(gender, class_id, sname) VALUES ("男",2,"张良2号") -- 对插入值进行动态更换——使用“new”，这回使得在class中的插入值就是新插入的student表中sname这一列的值 delimiter// CREATE TRIGGER try2 BEFORE INSERT on student for EACH ROW BEGIN INSERT into class(caption) VALUES (new.sname); END// delimiter;
-- 动态删除值的案例，需要使用的是“old” delimiter// CREATE TRIGGER try8 after DELETE on student FOR EACH ROW BEGIN DELETE from class where caption = old.sname; END// delimiter; DELETE FROM student where sname = "张良2号"
四、函数
（会降低MySQL的性能，比如在索引中使用函数的话会使得索引不能加速查找。如果想用函数，可以在程序级别使用）
1. 内置函数
（1）重点关注对字符串进行处理的各种函数（官方文档）
（2）重点关注函数 DTAE_FORMAT() （官方文档）。
#一个小例子 #创建表 CREATE TABLE timetable( id int auto_increment PRIMARY KEY, title char(50), ctime char(50) ) #插入数据 INSERT into timetable(title, ctime) VALUES("aaaa","2019-11-11 11:11"),("bbbb","2019-11-10 11:11"),("cccc","2019-10-11 11:11"),("dddd","2019-10-09 11:11") #按时间年月进行划分（“y”表示取年份的后两位） SELECT DATE_FORMAT(ctime,"%Y-%m"), count(1) FROM TIMETABLE GROUP BY DATE_FORMAT(ctime,"%Y-%m")
2. 自定义函数
函数内部不能调用表，不能出现像是 select * from student 这样的语句。
举一个老师的小案例放在下面：
delimiter \\ create function f1( i1 int, i2 int) returns int BEGIN declare num int; set num = i1 + i2; return(num); END \\ delimiter ; #调用函数 select f1(1, 100)
pay attention: 不过昂，我在打上面的代码的时候，碰到一个报错，是这样子的：
“This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”。我不懂为啥会报错，似乎是因为这个 log_bin_trust_function_creators 的值是0的原因，可以调用 show variables like 'log_bin_trust_function_creators'; 这个看一下，应该是“off”。记得输入 set global log_bin_trust_function_creators=1; 这个就好了，你在看就会变成“on”，然后你的函数就能成功建立了。不过看这篇博文里面说“这样添加了参数以后，如果mysqld重启，那个参数又会消失，因此记得在my.cnf配置文件中添加： log_bin_trust_function_creators=1”，所以各位有必要的话，也可以找到这个文件进行修改。
五、存储过程（相于前面的内容更重要一点）
保存在MySQL上的别名 ——> 一坨SQL语句。使用别名就能查到结果。可以用来替代程度员写SQL语句。（区别与函数，可以调用表）
方式一：MySQL中放存储过程，程序员调用存储过程。
方式二：MySQL中啥都不做，程序员在程序中写SQL语句。
方式三：MySQL中啥都不做，程序员写类和对象（本质上还是SQL语句）。
（其实没懂上面三个在干什么，有什么太大差别。慢慢来吧）
1. 创建简单存储过程
#在数据中要写的代码
delimiter \\ CREATE PROCEDURE p1() BEGIN SELECT * from student; INSERT INTO teacher(tname) VALUES("阿狸"); END \\ delimiter ; #sql中调用存储过程的方式 call p1()
#在python中要写的代码
#在python中直接使用方法 import pymysql conn = pymysql.connect(host='localhost', user='root', password='', database='practice') cursor = conn.cursor() cursor.callproc('p1') #注意是用这个函数调用！！！ result = cursor.fetchall() print(result) conn.commit() cursor.close() conn.close()
2. 传参数型存储过程（in, out, inout）
（1）in——表示要传入存储过程的参数
#在数据库中要写的代码
delimiter \\ CREATE PROCEDURE p2( in n1 int, in n2 int ) BEGIN SELECT * from student WHERE sid > n1 and sid <n2; END \\ delimiter ;
#在python中要写的代码
import pymysql conn = pymysql.connect(host='localhost', user='root', password='', database='practice') cursor = conn.cursor() cursor.callproc('p2',(1,4)) #注意此处的传参方式 result = cursor.fetchall() print(result) cursor.close() conn.close()
（2）out——存储过程内部可以修改参数值，并传出
这个稍微复杂一点，意思就是可以在执行sql语句的同时，修改值后往外传值
out常用于返回存储过程的执行结果，比如返回1表示执行成功，返回2表示执行失败。
# MySQL中
delimiter \\ CREATE PROCEDURE p3( in n1 int, out n2 int ) BEGIN SET n2 = 123123; #多说一句，n2只能往外传，即使传入的@a1变量有值，在begin与end中写一个select n2也传不出来值，结果是空值 SELECT * from student WHERE sid > n1; END \\ delimiter ; SET @a1 = 123; #设定一个基于会话级别的变量 call p3(1,@a1); #调用存储过程 SELECT @a1; #查看调用存储过程之后，变量的值是否有发生变化
#python中
import pymysql conn = pymysql.connect(host='localhost', user='root', password='', database='practice') cursor = conn.cursor() cursor.callproc('p3', (1, 2)) #调用存储过程 result1 = cursor.fetchall() print(result1) #输出存储过程获取值的结果 #但是同时由于存储过程内部对传入参数的值进行了更改，我们希望获取更改后的值 #此时需要使用SQL语句获取更改后的值 cursor.execute('select @_p3_0, @_p3_1') result2 = cursor.fetchall() print(result2) #输出获取的变量 （此时包含未改变的n1） cursor.close() conn.close()
（3）inout 既能往里传，又能往外拿
delimiter \\ CREATE PROCEDURE p4( in n1 int, INOUT n2 int #注意这里变成了inout ) BEGIN SELECT n2; #注意，此时n2能传出来了 SET n2 = 123123; SELECT * from student WHERE student.sid > n1; END \\ delimiter ; -- SET @a1 = 123; call p4(1,@a1); SELECT @a1;
3. 存储过程中的事务
原子性操作：最小操作单位，最小整体。如一个配套事件，就叫一个原子性操作，必须一起发生。
delimiter \\ CREATE PROCEDURE p5( OUT p_return_code tinyint ) #设定p_return_code的返回值是为了判断该存储过程是否成功执行 BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; #执行过程中发生错误，返回1 rollback; #出错回滚语句 END; START TRANSACTION; #开始事务，也就是开始SQL语句的配套操作 INSERT into student(gender, class_id, num)VALUES("女","1","阿离") insert into teacher(tname)values('李平老师'); COMMIT; #表示事务结束 -- SUCCESS set p_return_code = 2; #完美执行，返回2 END \\ delimiter ;
#调用该过程，并检测是否成功执行 set @v2 = 0; call p5(@v2); SELECT @v2;
4. 包含游标的存储过程（利用游标建立循环）
（1）能不用游标，尽量不用游标，因为性能低。在对每一行数据都要进行操作时需要使用。
（2）例子：现在有两个表，一个A表一个B表，都有两列，一列id ，一列num。现在只有A表有数据，我们的目的是把A表中num一列的数据放入B表中，但是需要进行一下计算，B表num中的数据得是（num+id）。
delimiter // create procedure p6() begin declare row_id int; -- 自定义变量1 declare row_num int; -- 自定义变量2 DECLARE done INT DEFAULT FALSE; DECLARE my_cursor CURSOR FOR select id,num from A; #设定游标，即在表A中循环读取数据 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; #设定终止条件——当读入数据为空时终止。因为SQL不能自己判断终止条件。 open my_cursor; #打开游标 xxoo: LOOP #开始循环 fetch my_cursor into row_id,row_num; if done then leave xxoo; #如果done是TRUE的话就离开循环 END IF; insert into B(num) values(row_id+row_num); end loop xxoo; #结束循环 close my_cursor; #关闭游标 end // delimter ; call p6() #调用存储过程
5. 动态执行SQL，同时防止SQL注入
delimiter \\ CREATE PROCEDURE p7( in arg INT) BEGIN set @arg = arg; -- 检测sql语句的合法性 PREPARE prop FROM "SELECT * from student WHERE id > ?"; -- 格式化tpl+arg EXECUTE prop USING @arg; #要求后面的变量必须是会话级别的变量，因此才会有前面的set步骤 -- 执行sql语句 DEALLOCATE PREPARE prob; END \\ delimiter ; call p7(3)
