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

落日映苍穹つ 2023-07-21 06:00 30阅读 0赞

参考：  
[7天MySQL魔鬼训练营（入门到高手）][7_MySQL]Day61  
[配套资源][Link 1] 密码: pmrv  
[Python开发【第十八篇】：MySQL（二）][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.自定义函数

各内置函数详见[中文版][Link 2] OR [官方][Link 3]

类比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 ;

[7_MySQL]: https://www.bilibili.com/video/BV1DE411n7fU?p=47
[Link 1]: https://pan.baidu.com/s/1UYbftr4OANuhnvmfPYwNqA
[Python_MySQL]: https://www.cnblogs.com/wupeiqi/articles/5713323.html
[Link 2]: http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions
[Link 3]: https://dev.mysql.com/doc/refman/5.7/en/functions.html