4.MySQL进阶：视图/触发器/函数/存储过程/索引

参考：
7天MySQL魔鬼训练营（入门到高手）Day61
配套资源 密码: pmrv
Python开发【第十八篇】：MySQL（二）

一、视图

视图是一个虚拟表（非真实存在，不可直接操作），其本质是【根据已有SQL语句为某个查询语句设置别名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。（工程不常用，别名可读性低）

例如：假设100个SQL语句，用到其中88个（select * from tb1 where id>10）；
给这88个记录创建视图: v1，之后均在v1中查找：select .. from v1		
	100个SQL：
			88: v1（即select * from tb1 where id>10）
			
			select .. from v1	-- 使用视图
			select asd from v1
			
假设有临时表搜索:
SELECT *FROM(SELECT nid,NAME FROM tb1 WHERE nid > 2) AS A 
WHERE A. NAME > 'alex';

创建格式：CREATE VIEW 视图名称 AS  SQL语句
CREATE VIEW v1 AS 
	SELECT nid, name FROM A WHERE nid > 4

修改格式：ALTER VIEW 视图名称 AS SQL语句
ALTER VIEW v1 AS
	SELECT A.nid,B. NAME FROM A 
		LEFT JOIN B ON A.id = B.nid LEFT JOIN C ON A.id = C.nid
			WHERE A.id > 2 AND C.nid < 5

删除：
drop view 视图名称;

二、触发器

当对某张表做：增删改操作时，可以使用触发器自定义关联行为。

1、创建基本语法
创建插入前触发器：
	delimiter //
	-- 自定义delimiter终止符带‘//’
	create trigger t1 BEFORE INSERT on student for EACH ROW
	-- BEFORE/AFTER; INSERT/UPDATE/DELETE
	BEGIN	-- 上面每插一条数据，begin~end执行一遍
	 	INSERT into teacher(tname) values(NEW.sname); -- 表示新插入数据的sname
	 	INSERT into class(name) values(NEW.sname);	
	END //
	delimiter ;
	
	insert into student(gender,class_id,sname) values('女',1,'陈涛'),('女',1,'张根');
	-- 之后teacher，class表分别更新

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH RO
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW

特别的：NEW,代指新数据，insert会用；OLD代指旧数据,delete，update会用

2.删除触发器：drop trigger t1;

3.使用触发器
触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。
insert into tb1(num) values(666)

三、函数（对性能要求不高）

1.内置函数；2.自定义函数

各内置函数详见中文版 OR 官方

类比python：
	def f1(a1,a2):
		return a1 + a2
	f1()	//自定义函数
	bin()	//内置函数
		
1.内置函数：执行函数 select CURDATE();
	假设：	
			blog
			id       title            ctime
			 1        asdf        2019-11
			 2        asdf        2019-11
			 3        asdf        2019-10
			 4        asdf        2019-10
		
select ctime,count(1) from blog group ctime	-- 按照时间计数
			
select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m")
--DATE_FORMAT为对时间格式化，%Y-%m年大写，月小写
			2019-11   2
			2019-10   2		
			
2.自定义函数(有返回值)：
delimiter \\
create function f1(
	i1 int,
	i2 int)
	returns int
BEGIN
	declare num int default 0;	-- 声明变量
	set num = i1 + i2;
	return(num);
END \\
delimiter ;
			
3..函数调用：SELECT f1(1,100);	-- ，结果101

4..删除函数：drop function func_name;

四、存储过程

存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

pymysql执行存储过程：
	别名() 用于替代程序员写SQL语句
	保存在MySQL上的一个别名 => 一坨SQL语句
	
	方式一：（比较新，工程用的不多）
		MySQL: 存储过程
		程序：调用存储过程
	方式二： 
		MySQL：。。
		程序：SQL语句
	方式三：
		MySQL：。。
		程序：类和对象（SQL语句）

1.创建无参数存储过程（简单）
	create procedure p1()
	BEGIN
	    select * from student;	--结果集
	    INSERT into teacher(tname) values("ct");
	END
	
	-- 执行存储过程
	call p1()
	-- 结果集调用.py中：pymysql
	-- cursor.callproc('p1')
	
	--删除存储过程
	drop procedure proc_name;
	
2. 传参数（in仅传入参数,out仅返回值用,inout均可）
	1）in
	create procedure p2(
		in n1 int,
		in n2 int)
	BEGIN
		select * from student where sid > n1;
	END
	-- 调用	
	call p2(12,2)
	-- cursor.callproc('p2',(12,2))
	2）参数 out，inout
	delimiter //
	create procedure p3(
		in n1 int,
		inout n2 int
	)
	BEGIN
		set n2 = 123123;
		select * from student where sid > n1;
	END //
	delimiter ;
	
	set @v1 = 10;		-- session类型变量v1，赋值10
	call p2(12,@v1);	--（in，out）
	select @v1;			-- 查看@v1
	
	set @_p3_0 = 12
	set @_p3_1 = 2
	call p3(@_p3_0,@_p3_1)
	select @_p3_0,@_p3_1
		
	cursor.callproc('p3',(12,2))	--只能拿到结果集
	r1 = cursor.fetchall()		--拿回结果集
	print(r1)
	
	cursor.execute('select @_p3_0,@_p3_1')	--@_名称_位置
	--拿结果集 & 返回值（in 12，out 123123）
	r2 = cursor.fetchall()
	print(r2)
								
	为什么有结果集又有out伪造的返回值？
	delimiter //
	create procedure p3(
		in n1 int,
		out n2 int  --  用于标识存储过程的执行结果  1,2
	)
	BEGIN
		insert into vv(..)
		insert into vv(..)
	END //
	delimiter ;
	
3.事务详见 https://blog.csdn.net/jeanlu/article/details/105255222

4. 游标：对于每一行分门别类计算（操作）
	1）声明游标
	2）获取表A中数据
	3）检测循环是否还有数据，若无break
	
	create procedure p3()
	begin 
	    declare ssid int; -- 自定义变量1  
	    declare ssname varchar(50); -- 自定义变量2  
	    DECLARE done INT DEFAULT FALSE;
	    
	    DECLARE my_cursor CURSOR FOR select sid,sname from student;	--从student表获取数据
	    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;		--终止游标条件
	                        
	    open my_cursor;		--打开游标
	    xxoo: LOOP
		 fetch my_cursor into ssid,ssname;	--每一行获取数据
	          if done then 
	         	  leave xxoo;		--break，跳出循环
	      	  END IF;
	   	 insert into teacher(tname) values(ssname);
	    end loop xxoo;
	    close my_cursor;
	end  
	
5. 动态执行SQL（防SQL注入）
	SQLdelimiter \\
	CREATE PROCEDURE p4 (
	    in nid int
	)
	BEGIN
		set @nid=nid;
	    PREPARE prod FROM 'select * from student where sid > ?';	--1.预检测sql语句合法性
	    EXECUTE prod USING @nid;		--2.格式化nid，只能是session变量
	    DEALLOCATE prepare prod; 		--3.执行已格式化的sql语句
	END\\
	delimiter ;

五、索引

索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

                    30
 
        10                        40
 
   5         15            35             66
 
1   6     11    19      21      39     55     100

MySQL中常见索引有：

• 普通索引：加速查询
• 唯一索引：加速查询 和 唯一约束（可含null）
• 主键索引：加速查询 和 唯一约束（不可含null）
• 组合索引：将n个列组合成一个索引

1、普通索引

创建表 + 索引：
 create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

创建索引：
create index index_name on table_name(column_name)

删除索引：
drop index_name on table_name;

查看索引：
show index from table_name;
注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

代码使用：
create index ix_extra on in1(extra(32));

2、唯一索引

创建表 + 唯一索引
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

创建唯一索引：
create unique index 索引名 on 表名(列名)

删除唯一索引：
drop unique index 索引名 on 表名

3、主键索引

创建表 + 创建主键
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table in1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)

创建主键:
alter table 表名 add primary key(列名);

删除主键:

alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

4、组合索引

其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = ‘alex’ and n2 = 666。

创建表:
create table in3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)

创建组合索引:
create index ix_name_email on in3(name,email);

如上创建组合索引之后，查询：

name and email  	  -- 使用索引
name                  -- 使用索引
email                 -- 不使用索引

注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

六、其他

1、if条件语句;
2、循环语句;(while/repeat/loop)
3、动态执行SQL语句（防SQL注入）

1、if条件语句
delimiter \\
CREATE PROCEDURE proc_if ()
BEGIN
    declare i int default 0;
    if i = 1 THEN SELECT 1;
    ELSEIF i = 2 THEN SELECT 2;
    ELSE SELECT 7;
    END IF;
END\\
delimiter ;

2、循环语句
-- while循环
delimiter \\
CREATE PROCEDURE proc_while ()
BEGIN
    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;
END\\
delimiter ;

-- repeat循环
delimiter \\
CREATE PROCEDURE proc_repeat ()
BEGIN
    DECLARE i INT ;
    SET i = 0 ;
    repeat
        select i;
        set i = i + 1;
        until i >= 5
    end repeat;
END\\
delimiter ;

-- loop循环


3、动态执行SQL语句（防SQL注入）
delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
    declare p1 int;
    set p1 = 11;
    set @p1 = p1;

    PREPARE prod FROM 'select * from tb2 where nid > ?';
    EXECUTE prod USING @p1;
    DEALLOCATE prepare prod; 

END\\
delimiter ;