视图函数触发器存储过程

视图

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。

创建视图

--格式：CREATE VIEW 视图名称 AS  SQL语句

CREATE VIEW v1 (学号,姓名,班级) AS 
SELECT
sid,
sname,
class.caption 
FROM
    student
    LEFT JOIN class ON student.class_id = class.cid

mysql> desc v1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| 学号   | int(11)     | NO   |     | 0       |       |
| 姓名   | varchar(32) | NO   |     | NULL    |       |
| 班级   | varchar(32) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

mysql> select * from v1;
+--------+--------+--------------+
| 学号   | 姓名   | 班级         |
+--------+--------+--------------+
|      1 | 理解   | 三年二班     |
|      2 | 钢蛋   | 三年二班     |
|      3 | 张三   | 三年二班     |
|      4 | 张一   | 三年二班     |
|      5 | 张二   | 三年二班     |
|      6 | 张四   | 三年二班     |
|      7 | 铁锤   | 三年三班     |
|      8 | 李三   | 三年三班     |
|      9 | 李一   | 三年三班     |
|     10 | 李二   | 三年三班     |
|     11 | 李四   | 三年三班     |
|     12 | 如花   | 一年二班     |
|     13 | 刘三   | 一年二班     |
|     14 | 刘一   | 一年二班     |
|     15 | 刘二   | 一年二班     |
|     16 | 刘四   | 一年二班     |
+--------+--------+--------------+

如果v1视图带列名的话，要和下面查询语句中的列数一致，否则出错

修改视图

-- 格式：ALTER VIEW 视图名称 AS SQL语句

ALTER VIEW v1 AS
SELET 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

------------------------
ALTER VIEW v1 (
学号,姓名) AS SELECT
sid,
sname 
FROM
    student
----------------------------------

修改内容

因为视图本身没有数据，因此对视图进行的dml操作最终都体现在基表中

mysql> create view v_student as select * from student;

mysql> select * from v_student;
+--------+--------+------+
| 学号    | name   | sex  |
+--------+--------+------+
|      1 | 张三    | M    |
|      2 | 李四    | F    |
|      5 | 王五    | NULL |
+--------+--------+------+

mysql> update v_student set name='钱六' where 学号='1';

mysql> select * from student;
+--------+--------+------+
| 学号    | name   | sex  |
+--------+--------+------+
|      1 | 钱六    | M    |
|      2 | 李四    | F    |
|      5 | 王五    | NULL |
+--------+--------+------+

当然，视图的DML操作，不是所有的视图都可以做DML操作。

有下列内容之一，视图不能做DML操作：

①select子句中包含distinct

②select子句中包含组函数

③select语句中包含group by子句

④select语句中包含order by子句

⑤select语句中包含union 、union all等集合运算符

⑥where子句中包含相关子查询

⑦from子句中包含多个表

⑧如果视图中有计算列，则不能更新

⑨如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

如果你创建的视图中选择了比较少的列，而基表中存在其他不能为空且无默认值的列，则是无法执行insert语句的

mysql> insert into v1(学号,姓名) values(17,'test2');
ERROR 1423 (HY000): Field of view 'practise.v1' underlying table doesn't have a default value

删除视图

--格式：DROP VIEW 视图名称

DROP VIEW v1

触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

image

触发器的特性：

1、有begin end体，begin end;之间的语句可以写的简单或者复杂

2、什么条件会触发：I、D、U

3、什么时候触发：在增删改前或者后

4、触发频率：针对每一行执行

5、触发器定义在表上，附着在表上。

！！尽量少使用触发器，不建议使用。

假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。因此我们特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高，资源消耗要小。

触发器尽量少的使用，因为不管如何，它还是很消耗资源，如果使用的话要谨慎的使用，确定它是非常高效的：触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

创建触发器

创建单个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;

例1：创建了一个名为trig1的触发器，一旦在work表中有插入动作，就会自动往time表里插入当前时间

mysql> CREATE TRIGGER trig1 AFTER INSERT
    -> ON work FOR EACH ROW
    -> INSERT INTO time VALUES(NOW());

创建多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件

ON 表名 FOR EACH ROW

BEGIN

        执行语句列表

END;

例2：定义一个触发器，一旦有满足条件的删除操作，就会执行BEGIN和END中的语句

mysql> DELIMITER ||
mysql> CREATE TRIGGER trig2 BEFORE DELETE
    -> ON work FOR EACH ROW
    -> BEGIN
    -> 　　INSERT INTO time VALUES(NOW());
    -> 　　INSERT INTO time VALUES(NOW());
    -> END||
mysql> DELIMITER ;

DELIMITER

这个是设置sql语句的结束符号的，默认为; 但是触发器和函数、存储过程定义过程中肯定有包含;的语句，所以先设置成其他的符号。后面再设置回来。

NEW和OLD

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

MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

①在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

②在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；

③在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);

mysql> delimiter $$
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    -> 　　IF NEW.amount < 0 THEN
    -> 　　　　SET NEW.amount = 0;
    -> 　　ELSEIF NEW.amount > 100 THEN
    -> 　　　　SET NEW.amount = 100;
    -> 　　END IF;
    -> END$$
mysql> delimiter ;

mysql> update account set amount=-10 where acct_num=137;

mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |    0.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+

mysql> update account set amount=200 where acct_num=137;

mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |  100.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+

查看触发器

1、SHOW TRIGGERS语句查看触发器信息

mysql> SHOW TRIGGERS\G;

……

结果，显示所有触发器的基本信息；无法查询指定的触发器。

2、在information_schema.triggers表中查看触发器信息

mysql> SELECT * FROM information_schema.triggers\G

删除触发器

DROP TRIGGER [IF EXISTS][schema_name.]trigger_name

函数

内置函数

直接看官方文档吧

自定义函数

mysql> DELIMITER //
mysql> CREATE FUNCTION Ver_Compare (n INT, m INT)
    -> RETURNS VARCHAR(50)    #声明返回的数据类型
        -> BEGIN
    -> 　　DECLARE s VARCHAR(50);
    -> 　　IF n = m THEN SET s = 'equals';
    -> 　　ELSE
    -> 　　　　IF n > m THEN SET s = 'greater';
    -> 　　　　ELSE SET s = 'less';
    -> 　　　　END IF;
    -> 　　　　SET s = CONCAT('is ', s, ' than');
    -> 　　END IF;
    -> 　　SET s = CONCAT(n, ' ', s, ' ', m, '.');
    -> 　　RETURN s;    #一个实际的返回值
    -> END //
mysql> DELIMITER ;

-----------------------------
delimiter \\
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END \\
delimiter ;

函数调用

#获取返回值
mysql> set @ax = Ver_Compare(1,2);
mysql> select @ax;
+-------------------+
| @ax               |
+-------------------+
| 1 is less than 2. |
+-------------------+

# 在查询中使用
select f1(11,nid) ,name from tb2;

在我们调用函数的时候，函数需要出现在 = 的右边（也就是说调用函数需要有变量接收其结果）

查看函数

show function status\G;

删除函数

drop function func_name;

存储过程

一组可编程的函数，是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

优点(为什么要用存储过程？)：

①将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用

②批量处理：SQL+循环，减少流量，也就是“跑批”

③统一接口，确保数据的安全

相对于oracle数据库来说，MySQL的存储过程相对功能较弱，使用较少。

创建存储过程

无参数存储过程

-- 创建存储过程

delimiter //
create procedure p1()
BEGIN
    select * from test_procedure;
END//
delimiter ;



-- 执行存储过程

call p1()

有参数

in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值

in输入参数

mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;

mysql> set @p_in=1; #设置一个session级的变量

mysql> call in_param(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+

+------+
| P_in |
+------+
|    2 |
+------+

mysql> select @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+

以上可以看出，p_in在存储过程中被修改，但并不影响@p_id的值，因为前者为局部变量、后者为全局变量（session级）。

out输出参数

mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;

mysql> set @p_out=1;

mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
　　#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
|     2 |
+-------+

mysql> select @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+
　　#调用了out_param存储过程，输出参数，改变了p_out变量的值

inout输入参数

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;

mysql> set @p_inout=1;

mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+

+---------+
| p_inout |
+---------+
|       2 |
+---------+

mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+

调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量.

有没有觉得像是引用传递？

结果集

delimiter //
create procedure p1()
begin
    select * from v1;
end //
delimiter ;

这样是返回select执行的结果集

结果集+out返回

delimiter //
create procedure p2(
    in n1 int,
    inout n3 int,
    out n2 int,
)
begin
    declare temp1 int ; #declare 声明变量temp1 
    declare temp2 int default 0;

    select * from v1;
    set n2 = n1 + 100;
    set n3 = n3 + n1 + 100;
end //
delimiter ;

注：declare可以声明一个变量。

这样的话又有select的结果集，又有n2，n3的返回值内容可以拿。那在命令行和python的pymysql中怎么操作？

命令行下

-- 创建存储过程
delimiter \\
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;
    
    set temp1 = 1;

    set r1 = i1 + i2 + temp1 + temp2;
    
    set i3 = i3 + 100;

end\\
delimiter ;

-- 执行存储过程
set @t1 =4;
set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

执行完这个存储过程后，out和inout的值自然已经发生变量，下面可以直接调用

pymysql

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()


print(result)

其中cursor.callproc为执行存储过程的方法，这样只执行了存储过程。如果要拿到out的返回值，那必然要在mysql中定义一个变量然后取回来。在pymysql模块中的体现，还要执行一次select语句，固定的格式

select @_p1_0,@_p1_1,@_p1_2,@_p1_3

select @_存储过程名字_参数0，@_存储过程名字_参数1，……

其实在pymysql中给我们构造了sql语句是这样的

set @_p3_0 = 12
set @_p3_1 = 2
call p3(@_p3_0,@_p3_1)
select @_p3_0,@_p3_1

所以可以取回out的值，并还能有结果集，只要一次fetchall就行了。

事务

delimiter \\
create PROCEDURE p1(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 有错误就回滚
    set p_return_code = 1; #这里的返回值 是用来表示是否执行成功 1是执行失败
    rollback; 
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return_code = 2; #2 就表示成功
    rollback; 
  END; 
 
  START TRANSACTION; 
    DELETE from tb1;
    insert into tb2(name)values('seven');
  COMMIT; 
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END\\
delimiter ;

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

游标

delimiter //
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;
    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;
            END IF;
            insert into teacher(tname) values(ssname);
        end loop xxoo;
    close my_cursor;
    end  //
delimter ;

在mysql中也可以执行类似遍历的操作。具体再了解

动态执行sql


delimiter \\
    CREATE PROCEDURE p4 (
        in nid int
    )
    BEGIN
        PREPARE prod FROM 'select * from student where sid > ?';
        EXECUTE prod USING @nid;
        DEALLOCATE prepare prod; 
    END\\
delimiter ;

可以在mysql 数据层防止sql注入监测

注意：

①如果过程没有参数，也必须在过程名后面写上小括号

例：CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……

②确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理

资料参考

ALL：MySQL（二）

视图：深入解析MySQL视图VIEW

触发器：MySQL触发器trigger的使用

函数：

存储过程：MySQL存储过程的创建及调用

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 206,602评论 6赞 481
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 88,442评论 2赞 382
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 152,878评论 0赞 344
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 55,306评论 1赞 279
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 64,330评论 5赞 373
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,071评论 1赞 285
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,382评论 3赞 400
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,006评论 0赞 259
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,512评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,965评论 2赞 325
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,094评论 1赞 333
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,732评论 4赞 323
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,283评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,286评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,512评论 1赞 262
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,536评论 2赞 354
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,828评论 2赞 345