MySQL细节知识1

参考资料：https://www.bilibili.com/video/BV1DE411n7fU?p=47

视图

视图本质上是给每一个查询语句设立了一个别名，在工作中并不推荐使用。

select .... from v1;
--创建
create view v1 as ....(SQL语句)
--PS:虚拟
--修改
alter view v1 as ...(SQL语句)
--删除
drop view v1;

触发器

触发器常用来实现关联操作。

insert into tb(...)
delimiter //
create trigger t1 before insert on student for each row
begin
insert into teacher(name) values(NEW.sanme);
end //
delimiter;

--NEW 代指新数据
--OLD 代指原有数据

函数

--内置函数：
--执行函数：select CURDATE();
select ctime, count(1) from blog group by ctime;
select DATE_FORMAT(ctime, "%Y-%m"), count(1) from blog group by DATE_FORMAT(ctime, "%Y-%m");

--自定义函数（有返回值）：
delimiter //
create function f1(i1 int, i2 int)
returns int
begin
declare num int default 0;
set num = i1 + i2;
return(num);
end //
delimiter ;

select f1(1, 100); 

存储过程

封装的思想和函数有些相似，但是存储过程中可以写SQL语句，传参的时候有in, out, inout等关键字。存储过程没有返回值，存储过程存于MYSQL服务器上。

call p2（1, 12）;
cursor.callproc(p2, (1, 12));

delimiter //
create procedure p3(in n1 int, inout n2 int)
begin
set n2 = 123;
select * from student where sid > n1;
end //
delimiter ;

set @v1 = 10;
call p2(12, @v1);
select @v1;

cursor.callproc(p3, (1, 2))
r1 = cursor.fetchone()
print(r1)

cursor.exercute('select @_p3_0, @_p3_1')
r2 = cursor.fetchall()
print(r2)

--存储过程的特殊之处
--a. 可传参 in，out, inout
--b. pymysql
--cursor.callproc(p3, (1, 2))
--r1 = cursor.fetchone()
--print(r1)

--事务操作
delimiter \\
create PROCEDURE p5(OUT p_return_code tinyint)
DECLARE exit handler for sqlexception
begin
--error
set p_return_code = 1;
rollback;
end;

start transaction
delete from t1;
insert into t2(name) values('david');
commit;

--success
set p_return_code = 0;
end \\
delimiter ;

游标(cursor)：用于循环操作。注意：这种操作的效率不高，一般不推荐使用，只有在对一张表的每一行数据分门别类做操作时，才会使用cursor进行循环操作。

1.声明游标
2.获取A表中的数据：
my_cursor select id, num from A;
3.for row_id, row_num in my_cursor:
#检测循环中是否还有数据，如果没有数据；
#break;
insert B(num) values(row_id+row_num);

delimiter //
create procedure p6()
begin
declare row_id int;
declare row_num varchar(10);
declare done int default false;

declare my_cursor cursor for select id, num from A;
declare CONTINUE HANDLER FOR NOT FOUND set done = TURE;

open my_cursor;
xxoo:
LOOP
fetch my_cursor into row_id, row_num;
if done then
leave xxoo;
end if;

set temp = row_id+row_num;
insert into B(num) values (temp);

end loop xxoo;
close my_cursor;
end //
delimiter ;

--6.动态注入sql
delimiter //
create procedure p7(in tql int, in arg varchar(255))
begin
--1.预检测某个东西，sql语句的合法性
--2.SQL=格式化 tql+arg
--3.执行SQL语句

set @xo = arg;
prepare xxx from 'select * from student where sid > ?';
execute xxx using @xo;
deallocate prepare prod;

end //
delimiter ;

call p7("select * from tb where id > ?", 9);

小结1：
用于替代程序员写SQL语句的方法：

• MySQL：存储过程。程序：调用存储过程。
• MySQL：。。程序：SQL语句
• MySQL：。。程序：类和对象（SQL语句）

小结2（存储过程小结）

• 简单
• 传参数（in, out, inout）
• 参数out(为什么有结果集又有伪造的返回值)
• 事务
• 游标
• 动态执行SQL(防止SQL注入)

分页显示

--1.普通
select * from userinfo limit 20, 10;
--2.在索引表中扫
select * from userinfo where id in (select id from userinfo limit 20, 10);
--3.记录当前页面中的最大和最小id：max_id,min_id
--3.1.页面中只有 上一页， 下一页
--下一页
select * from userinfo where id > max_id limit 10;
--上一页
select * from userinfo where id < min_id order by id desc limit 10;

--3.2.上一页 7 8 【9】 10 11 下一页
select * from userinfo where id in (select id from userinfo where id > max_id limit (页面差)*（一个页面展示的数据条数）)  as N order by N.id desc limit 10;

--PS:注意，由于表中的id可能因为insert和delete，可能会出现id不连续的情况，所以不推荐使用between .. and ..