【Mysql】视图|触发器|存储过程|函数|索引

1.视图

MySQL视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

1）视点集中

2）简化操作

3）定制数据

4）合并分割数据

5）安全性

创建格式：create view 视图名称 as SQL语句

删除格式：drop view 视图名称

修改格式：alter view 视图名称 as SQL语句

创建视图后使用视图修改后原始表也会跟着修改，原始表修改视图也会修改，因为视图时动态查询的。

创建视图存在如下注意事项：

（1）运行创建视图的语句需要用户具有创建视图（crate view）的权限，若加了[or replace]时，还需要用户具有删除视图（drop view）的权限；

（2） select语句不能包含from子句中的子查询；

（3） select语句不能引用系统或用户变量；

（4） select语句不能引用预处理语句参数；

（5）在存储子程序内，定义不能引用子程序参数或局部变量；

（6）在定义中引用的表或视图必须存在。但是，创建了MySQL视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用check table语句；

（7）在定义中不能引用temporary表，不能创建temporary视图；

（8）在视图定义中命名的表必须已存在；

（9）不能将触发程序与视图关联在一起；

（10）在视图定义中允许使用order by，但是，如果从特定视图进行了选择，而该视图使用了具有自己order by的语句，它将被忽略。

2.触发器

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。

1、创建基本语法

# 插入前
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

特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。

2、删除触发器

DROP TRIGGER tri_after_insert_tb1;

3、使用触发器

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

insert into tb1(num) values(666)

3.存储过程--类似于函数

基本创建格式：

下面示例中没有实际应用价值，专注于知识点，可以这样使用它。

delimiter //　　　　　　　#将执行SQL语句的结束符号改为//

create procedure fun(

in parameter int，　　　　　　　　　　　   #传入参数

out ret int，　　　　　　　　　　　　　　　#返回参数

inout currency int　　　　　　　　　　　　 #可作用为传入参数也可作用为返回参数

)

begin　　　　　　　　　　　　　　　　　　  #存储过程开始 中间可以有多条SQL语句

　　declare temp1 int；　　　　　　#声明变量　　

　　declare temp2 int default 0；　#声明变量并赋值

　　set temp1 = 1；　　　　　　　  #赋值操作 

　　set ret = temp1 + parameter + temp2；

　　set currency  = currency  + parameter + temp1 + temp2；

　　SQL语句；　　　　　　#通常以（；）结束SQL语句，所以前面需要改变结束符号

　　SQL语句；

end//　　　　　　　　　　#储存过程结束

delimiter ；　　　　　　 #修改回默认结束符号delimiter //

CREATE PROCEDURE fun ( IN parameter INT, OUT ret INT, INOUT currency INT ) BEGIN
    DECLARE
        temp1 INT;
    DECLARE
        temp2 INT DEFAULT 0;
    
    SET temp1 = 1;
    
    SET ret = temp1 + parameter + temp2;
    
    SET currency = currency + parameter + temp1 + temp1;


END // delimiter;

基本执行格式

set @ret = 0；　　　　　　 #不写默认为null

set @currency = 4；

call fun(123，@ret，@currency)；#如何fun执行SQL语句有返回的结果集语句则返回 结果集 和 返回参数

select @ret；　　　　　　  #124

select @currency；　　　　 #128

SET @ret = 0;

SET @currency = 4;
CALL fun ( 123, @ret, @currency );
SELECT
    @ret;
SELECT
    @currency;

删除格式

drop procedure 存储过程名称

1.存储过程：if else 执行格式

declare i int default 1

if i = 1 then　　　　#if条件表达式 then 是固定格式 下面同

　　select　1；

elseif i = 2 then　　

　　select 2；

else

　　select 5；

end if；　

 

2.存储过程：where 执行格式

 

declare num int;

set num = 0；

while num < 10 do

　　select num;

　　set num = num+1;

end while;

2.存储过程：repeat 执行格式

declare i int;

set i = 0;

repeat

　　select i;

　　set i = i + 1;

　　until i >= 5

end repeat;

2.存储过程：loop 执行格式

declare i int default 0;

loop_label:loop

　　select i;

　　set i = i + 1;

　　if i>=5 thewn

　　　　leave loop_label;

　　　　end if;

end loop;

3.动态执行SQL语句（应用场景少）

delimiter \\

DROP PROCEDURE EXISTS proc_sql \\

CREATE PROCEDURE proc_sql()

BEGIN

　　declare p1 int;

　　set p1 = 11;

　　set @p1 = p1;

　　PREPARE prod FROM 'select * from tab2 where nid > ?'　　#这里SQL可以使用参数传递

　　EXECUTE prod USING @p1;　　　　　　　　　　　　　　　　 #必须使用@ 类型 

　　DEALLOCATE prepare prod;　　　　　　　　　　　　　　    #执行prod

END\\

delimiter ;

pymysql执行使用 callproc（）方法

cursor.callproc('fun',args=(1,2,3))　　　　　　　　　　#在pymysql中使用存储过程　　并传递3个参数

result1 = cursor.fetchall()　　　　　　　　　　　　　　#获取fun存储过程中使用select查询到的结果集

result2 = execute('select @_fun_0,@_fun_1,@_fun_2,')　 #获取传递参数的数值 pymysql在内部将 传递的三个参数创建了三个变量，它无法识别in out inout等，需要自行判断

conn.commit()　　　　　　　　　　　　　　　　　　　　  #执行提交，否则存储过程中，update等更新修改操作无法被执行

事物

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性

支持事务的存储过程

delimiter \\
create PROCEDURE p1(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; 
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 
 
  START TRANSACTION; 
    DELETE from tb1;
    insert into tb2(name)values('seven');
  COMMIT; 
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END\\
delimiter ;

内置函数

https://dev.mysql.com/doc/refman/5.7/en/functions.html

执行计划

索引

索引补充