MySQL内置功能之视图、触发器和存储过程

主要内容：

　　一、视图

　　二、触发器

　　三、存储过程

 

1️⃣  视图

　　一、关于视图的理解

　　1.1、何谓视图？

　　视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，

用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。

　　1.2、视图能是干嘛的？

　　使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的

数据时就无需重写复杂的sql了，直接去视图中查找即可，因此极大方便了我们的查询过程。

　　1.3、视图的缺点

　　视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中

的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用。

　　1.4、实例：

#两张有关系的表
mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
rows in set (0.00 sec)

mysql> select * from teacher;
+-----+-----------------+
| tid | tname           |
+-----+-----------------+
|   1 | 张磊老师        |
|   2 | 李平老师        |
|   3 | 刘海燕老师      |
|   4 | 朱云海老师      |
|   5 | 李杰老师        |
+-----+-----------------+
rows in set (0.00 sec)

#查询李平老师教授的课程名
mysql> select cname from course where teacher_id = (select tid from teacher where tname='李平老师');
+--------+
| cname  |
+--------+
| 物理   |
| 美术   |
+--------+
rows in set (0.00 sec)

#子查询出临时表，作为teacher_id等判断依据
select tid from teacher where tname='李平老师'

　　二、创建视图

#语法：CREATE VIEW 视图名称 AS  SQL语句
create view teacher_view as select tid from teacher where tname='李平老师';

#于是查询李平老师教授的课程名的sql可以改写为
mysql> select cname from course where teacher_id = (select tid from teacher_view);
+--------+
| cname  |
+--------+
| 物理   |
| 美术   |
+--------+
rows in set (0.00 sec)

#！！！注意注意注意：
#1. 使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高

#2. 而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，
那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，
你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便

　　三、使用视图

#修改视图，原始表也跟着改
mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
rows in set (0.00 sec)

mysql> create view course_view as select * from course; #创建表course的视图
Query OK, 0 rows affected (0.52 sec)

mysql> select * from course_view;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
rows in set (0.00 sec)

mysql> update course_view set cname='xxx'; #更新视图中的数据
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> insert into course_view values(5,'yyy',2); #往视图中插入数据
Query OK, 1 row affected (0.03 sec)

mysql> select * from course; #发现原始表的记录也跟着修改了
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
|   1 | xxx   |          1 |
|   2 | xxx   |          2 |
|   3 | xxx   |          3 |
|   4 | xxx   |          2 |
|   5 | yyy   |          2 |
+-----+-------+------------+
rows in set (0.00 sec)

 注意：我们不应该修改视图中的记录，而且是在涉及多个表的时候，根本无法修改视图记录。如下图所示：

mysql> select * from dep;
+----+--------+
| id | name   |
+----+--------+
|  1 | 财务   |
|  2 | 技术   |
|  3 | 后勤   |
+----+--------+

ysql> select * from user;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | cc1  |      1 |
|  2 | cc2  |      1 |
|  3 | cc3  |      3 |
+----+------+--------+
3 rows in set (0.00 sec)

mysql> create view user_dep as select user.id,user.name,user.dep_id,dep.name as dep_name
    -> from user left join dep on user.dep_id = dep.id;
Query OK, 0 rows affected (0.13 sec)

mysql> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| class         |
| dep           |
| user          |
| user_dep      |
+---------------+
4 rows in set (0.00 sec)

mysql> select * from user_dep;
+----+------+--------+----------+
| id | name | dep_id | dep_name |
+----+------+--------+----------+
|  1 | cc1  |      1 | 财务     |
|  2 | cc2  |      1 | 财务     |
|  3 | cc3  |      3 | 后勤     |
+----+------+--------+----------+
3 rows in set (0.06 sec)

mysql> insert into user_dep values(4,'cc4',2,'技术');
ERROR 1471 (HY000): The target table user_dep of the INSERT is not insertable-into    # 无法插入数据

　　四、修改视图

语法：ALTER VIEW 视图名称 AS SQL语句

　　实例：

mysql> alter view user_dep as select * from user where id<3;
Query OK, 0 rows affected (0.11 sec)

mysql> select * from user_dep;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | cc1  |      1 |
|  2 | cc2  |      1 |
+----+------+--------+
2 rows in set (0.03 sec)

　　五、删除视图

语法：DROP VIEW 视图名称

　　实例：

mysql> drop view user_dep;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_dep;
ERROR 1146 (42S02): Table 'db4.user_dep' doesn't exist

 

2️⃣  触发器

　　一、触发器能干什么？

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

　　二、创建触发器

　　语法：

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

　　实例：

#创建表
mysql> create table cmd(
    -> id int primary key auto_increment,
    -> user char(24),
    -> pri char(16),
    -> cmd char(66),
    -> sub_time datetime,
    -> success enum('yes','no')
    -> );
Query OK, 0 rows affected (0.38 sec)

mysql> create table errlog(
    -> id int primary key auto_increment,
    -> err_cmd char(66),
    -> err_time datetime
    -> );
Query OK, 0 rows affected (0.39 sec)

　　插入操作

delimiter //
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
     if new.success = 'no' then     # #等值判断只有一个等号
             insert into errlog(err_cmd,err_time) 
                      values(new.cmd,new.sub_time) ;   # 必须加分号
     end if ;  # 必须加分号
end//
delimiter ;  # 必须加分号


# 往表cmd中插入记录，触发触发器，根据IF条件决定是否插入错误日志
insert into cmd(
    user,
    pri,
    cmd,
    sub_time,
    success
)   values
    ('cc1','1001','ls -l /etc',NOW(),'yes'),
    ('cc2','1002','ipconfig',NOW(),'no'),
    ('cc3','1003','tasklist',NOW(),'no');
     
# 查询错误日志，发现有两条
mysql> select * from errlog;
+----+----------+---------------------+
| id | err_cmd  | err_time            |
+----+----------+---------------------+
|  1 | ipconfig | 2018-05-24 17:57:59 |
|  2 | tasklist | 2018-05-24 17:57:59 |
+----+----------+---------------------+
2 rows in set (0.00 sec)

　　注意：触发器是在数据插入后，触发的，NEW表示即将插入的行，OLD表示即将删除的行。

 

　　二、使用触发器　

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

　　三、删除触发器

drop trigger tri_after_insert_cmd;

 

3️⃣  存储过程

　　一、何谓事务？

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql语句。

　　使用存储过程的优点：

 

#1. 用于替代程序写的SQL语句，实现程序与sql解耦

#2. 基于网络传输，传别名的数据量小，而直接传sql数据量大

 

　　使用存储过程的缺点：

 

# 程序员扩展功能不方便

 

　　程序与数据库结合使用的三种方式

# 方式一：
    MySQL：存储过程
    程序：调用存储过程

# 方式二：
    MySQL:无
    程序：纯SQL语句。

# 方式三：
    MySQL：无
    程序：类和对象，即ORM（本质还是纯sql语句）

　　二、创建简单存储过程（无参）

delimiter //
create procedure p1()
begin
        select * from blog;
        insert into blog(name,sub_time) values('xxx',now());
end //
delimiter ;

# 在MySQL中调用
call p1()

# 在python中基于pymysql调用
cursor.callproc('p1')
print(cursor.fetchall())

　　三、创建存储过程（有参）

对于存储过程，可以接收参数，其参数有三类：

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

delimiter //
create procedure p2(
    in n1 int,
    in n2 int
)
BEGIN

    select * from blog where id > n1;
END //
delimiter ;

#在mysql中调用
call p2(3,2)

#在python中基于pymysql调用
cursor.callproc('p2',(3,2))
print(cursor.fetchall())

in:传入参数

delimiter //
create procedure p3(
    in n1 int,
    out res int
)
BEGIN
    select * from blog where id > n1;
    set res = 1;
END //
delimiter ;

#在mysql中调用
set @res=0; #0代表假（执行失败），1代表真（执行成功）
call p3(3,@res);
select @res;

#在python中基于pymysql调用
cursor.callproc('p3',(3,0)) #0相当于set @res=0
print(cursor.fetchall()) #查询select的查询结果

cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值
print(cursor.fetchall())

out：返回值

delimiter //
create procedure p4(
    inout n1 int
)
BEGIN
    select * from blog where id > n1;
    set n1 = 1;
END //
delimiter ;

#在mysql中调用
set @x=3;
call p4(@x);
select @x;


#在python中基于pymysql调用
cursor.callproc('p4',(3,))
print(cursor.fetchall()) #查询select的查询结果

cursor.execute('select @_p4_0;') 
print(cursor.fetchall())

inout:既可以传入又可以返回

 

四、执行存储过程

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

执行存储过程

在MySQL中执行存储过程-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

执行存储过程

在MySQL中执行存储过程

#!/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)

在python中基于pymysql执行存储过程

 

五、删除存储过程

drop procedure proc_name;