数据库之视图、触发器、事务、函数相关、流程控制及存储过程

最新推荐文章于 2021-02-18 01:15:02 发布

一切随心走_水瓶

最新推荐文章于 2021-02-18 01:15:02 发布

阅读量239

点赞数

分类专栏： python 文章标签： mysql 数据库触发器 python

本文链接：https://blog.csdn.net/wcg920212/article/details/108498558

版权

python 专栏收录该内容

107 篇文章 7 订阅

订阅专栏

1、视图

1.1、定义

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

使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用

1.2、视图的增删改查

# 创建视图
mysql> create view emp2dep as select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id=dep.id;
Query OK, 0 rows affected (0.86 sec)

# 查看视图
mysql> select * from emp2dep;
+----+-------+--------+------+--------+----------+
| id | name  | sex    | age  | dep_id | dep_name |
+----+-------+--------+------+--------+----------+
|  1 | allen | male   |   18 |    200 | 技术     |
|  2 | lucy  | female |   48 |    201 | 人力资源 |
|  3 | lily  | male   |   38 |    201 | 人力资源 |
|  4 | jack  | female |   28 |    202 | 销售     |
|  5 | tom   | male   |   18 |    200 | 技术     |
+----+-------+--------+------+--------+----------+
5 rows in set (0.29 sec)

# 更新视图中的数据
mysql> update emp2dep set name='ALLEN' where id=1;
Query OK, 1 row affected (0.32 sec)

mysql> select * from emp2dep;
+----+-------+--------+------+--------+----------+
| id | name  | sex    | age  | dep_id | dep_name |
+----+-------+--------+------+--------+----------+
|  1 | ALLEN | male   |   18 |    200 | 技术     |
|  2 | lucy  | female |   48 |    201 | 人力资源 |
|  3 | lily  | male   |   38 |    201 | 人力资源 |
|  4 | jack  | female |   28 |    202 | 销售     |
|  5 | tom   | male   |   18 |    200 | 技术     |
+----+-------+--------+------+--------+----------+
5 rows in set (0.00 sec)

# 修改视图里的数据影响基表，查看emp表数据也跟着发生变化了
mysql> select * from emp;
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | ALLEN | male   |   18 |    200 |
|  2 | lucy  | female |   48 |    201 |
|  3 | lily  | male   |   38 |    201 |
|  4 | jack  | female |   28 |    202 |
|  5 | tom   | male   |   18 |    200 |
|  6 | andy  | female |   18 |    204 |
+----+-------+--------+------+--------+
6 rows in set (0.10 sec)

# 修改视图
mysql> alter view emp2dep as select * from emp where id>3;
Query OK, 0 rows affected (1.31 sec)

mysql> select * from emp2dep;
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  4 | jack | female |   28 |    202 |
|  5 | tom  | male   |   18 |    200 |
|  6 | andy | female |   18 |    204 |
+----+------+--------+------+--------+
3 rows in set (0.32 sec)

# 删除视图
mysql> drop view emp2dep;
Query OK, 0 rows affected (1.42 sec)

2、触发器

2.1、定义

使用触发器可以定制用户对表进行[增、删、改]操作时前后的行为
注意: 没有查询
    
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    sql语句;
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    sql语句;
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    sql语句;
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    sql语句;
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    sql语句;
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    sql语句;
END

2.2、使用

重点: 触发器无法由用户直接调用，而知由于对表的[增/删/改]操作被动引发的

mysql> create table users(id int primary key auto_increment,name varchar(10),gender tinyint);
Query OK, 0 rows affected (2.78 sec)

mysql> create table user_more(name varchar(10));
Query OK, 0 rows affected (3.00 sec)

# 需求: 插入users数据前将name字段先插入user_more表中

mysql> delimiter $$  # 自定义语句结束符,避免冲突
mysql> create trigger tri_before_insert_users before insert on users for each row
       begin
       insert into user_more values(NEW.name);
       end $$  # sql执行结束
Query OK, 0 rows affected (0.61 sec)
mysql> delimiter ;  # 修改mysql结束标识再改为;

mysql> insert into users values(null,'tom',1);
Query OK, 1 row affected (0.57 sec)

mysql> select * from users;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  1 | tom  |      1 |
+----+------+--------+
1 row in set (0.00 sec)

mysql> select * from user_more;
+------+
| name |
+------+
| tom  |
+------+
1 row in set (0.00 sec)

2.3、应用

# 准备数据
mysql> CREATE TABLE cmd (
    ->     id INT PRIMARY KEY auto_increment,
    ->     USER CHAR (32),
    ->     priv CHAR (10),
    ->     cmd CHAR (64),
    ->     sub_time datetime, # 提交时间
    ->     success enum ('yes', 'no')  # 0代表执行失败
    -> );
Query OK, 0 rows affected (3.36 sec)

mysql> CREATE TABLE errlog (
    ->     id INT PRIMARY KEY auto_increment,
    ->     err_cmd CHAR (64),
    ->     err_time datetime
    -> );
Query OK, 0 rows affected (1.92 sec)

# 需求: 将cmd表中success数据为no的cmd命令和错误发生时间插入errlog表中

mysql> delimiter $$
# 创建触发器
mysql> create trigger tri_after_insert_cmd after insert on cmd for each row
    -> begin
    ->     if NEW.success = 'no' then  # 等值判断只有一个等号
    ->         insert into errlog values(null,NEW.cmd,NEW.sub_time);  # 必须加分号
    ->     end if;  # 必须加分号
    -> end $$
Query OK, 0 rows affected (0.33 sec)

delimiter ;

# 往表cmd中插入记录,触发触发器,根据if的条件决定是否插入错误日志
mysql> insert into cmd (USER,priv,cmd,sub_time,success
    -> ) values('jack','0755','ls -l /etc',NOW(),'yes'),
    ->         ('jack','0755','cat /etc/passwd',NOW(),'no'),
    ->         ('jack','0755','useradd xxx',NOW(),'no'),
    ->         ('jack','0755','ps aux',NOW(),'yes');
Query OK, 4 rows affected (1.86 sec)

# 查询错误日志,发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2020-09-09 15:55:14 |
|  2 | useradd xxx     | 2020-09-09 15:55:14 |
+----+-----------------+---------------------+
2 rows in set (0.13 sec)

# 注意: NEW表示即将插入的数据行，OLD表示即将删除的数据行。

2.4、删除触发器

drop trigger 触发器名称;

3、事务

3.1、定义

数据库事务是指作为单个逻辑工作单元执行的一系列操作(SQL语句),这些操作要么全部执行,要么全部不执行

3.2、事务特性(4种)

原子性 (atomicity): 强调事务的不可分割.
一致性 (consistency): 事务的执行的前后数据的完整性保持一致.
隔离性 (isolation): 一个事务执行的过程中,不应该受到其他事务的干扰
持久性 (durability): 事务一旦结束,数据就持久到数据库

3.3、事务运行模式

自动提交事务: 默认事务管理模式,如果一个语句成功地完成,则提交该语句;如果遇到错误,则回滚该语句
显式事务: 以BEGIN TRANSACTION显式开始,以COMMIT或ROLLBACK显式结束
隐性事务: 当连接以此模式进行操作时,sql将在提交或回滚当前事务后自动启动新事务,无须描述事务的开始,只需提交或回滚每个事务,它生成连续的事务链

3.4、使用

mysql> select * from user;
+----+----------+---------+
| id | user     | salary  |
+----+----------+---------+
|  1 | zhangsan | 1000.00 |
|  2 | lisi     | 1000.00 |
|  3 | wangwu   | 1000.00 |
+----+----------+---------+
3 rows in set (0.11 sec)

# 原子操作(买家zhangsan向卖家wangwu转账100元,其中lisi是中介得到10元,卖家wangwu得到90元)
start transaction;
update user set salary=900 where user='zhangsan';  # 买支付100元
update user set salary=1010 where user='lisi';  # 中介拿走10元
update user set salary=1090 where user='wangwu';  # 卖家拿到90元
commit;  # 真正提交到数据库内

# 出现异常,回滚到初始状态
start transaction;
update user set salary=900 where user='zhangsan';  # 买支付100元
update user set salary=1010 where user='lisi';  # 中介拿走10元
update user set salary=1090 where user='wangwu';  # 卖家拿到90元,出现异常没有拿到
rollback;  # 回滚
commit;

mysql> select * from user;
+----+----------+---------+
| id | user     | salary  |
+----+----------+---------+
|  1 | zhangsan | 1000.00 |
|  2 | lisi     | 1000.00 |
|  3 | wangwu   | 1000.00 |
+----+----------+---------+
3 rows in set (0.00 sec)

# pymysql操作
try:
    cursor.execute(sql_1)  
    cursor.execute(sql_2)  
    cursor.execute(sql_3)  
except Exception as e:
    connect.rollback()  # 事务回滚
    print('事务处理失败', e)
else:
    connect.commit()  # 事务提交
    print('事务处理成功', cursor.rowcount)  # 关闭连接
cursor.close()
connect.close()

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

4、内置函数及自定义函数

# 内置函数
MySQL自动提供的函数
例如: database(), now(), md5()
    
# 自定义函数
1、创建函数:
mysql> delimiter $$

mysql> create function sayHello(name varchar(32)) returns varchar(40)
    -> begin
    ->     return concat('Hello', name);
    -> end $$
Query OK, 0 rows affected (0.94 sec)

mysql> delimiter ;

2、调用函数:
mysql> select sayHello('wcg');
+-----------------+
| sayHello('wcg') |
+-----------------+
| Hellowcg        |
+-----------------+
1 row in set (0.65 sec)

3、删除函数
mysql> drop function sayHello;
Query OK, 0 rows affected (1.02 sec)

5、流程控制

5.1、条件语句

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 ;

5.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
BEGIN
    
    declare i int default 0;
    loop_label: loop
        
        set i=i+1;
        if i<8 then
            iterate loop_label;
        end if;
        if i>=10 then
            leave loop_label;
        end if;
        select i;
    end loop loop_label;

END

6、存储过程

6.1、定义

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

使用存储过程的优点:
1、用于替代程序写的SQL语句,实现程序与sql解耦
2、基于网络传输,传别名的数据量小,而直接传sql数据量大

使用存储过程的缺点:
1、程序员扩展功能不方便

程序与数据库结合使用的三种方式:
# 方式一:
MySQL: 存储过程
程序: 调用存储过程

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

# 方式三
MySQL:
程序: 类和对象,即ORM(本质还是纯SQL语句)

6.2、创建存储过程(无参)

mysql> delimiter $$
mysql> create procedure p1()
    -> begin
    -> select * from user;
    -> end $$
Query OK, 0 rows affected (0.79 sec)
mysql> delimiter ;

# 在mysql中调用
mysql> call p1();  
+----+----------+---------+
| id | user     | salary  |
+----+----------+---------+
|  1 | zhangsan | 1000.00 |
|  2 | lisi     | 1000.00 |
|  3 | wangwu   | 1000.00 |
+----+----------+---------+
3 rows in set (0.13 sec)
Query OK, 0 rows affected (0.13 sec)

# 在python中基于pymysql调用
cursor.callproc('p1') 
print(cursor.fetchall())  # ((1, 'zhangsan', 1000.0), (2, 'lisi', 1000.0), (3, 'wangwu', 1000.0))

6.3、创建存储过程(有参)

对于存储过程,可以接收参数,其参数有三类:
    
in          仅用于传入参数用
out         仅用于返回值用
inout       既可以传入又可以当作返回值

mysql> delimiter $$
mysql> create procedure p2(in n1 int)
    -> begin
    ->     select * from user where id > n1;
    -> end $$
Query OK, 0 rows affected (0.52 sec)
mysql> delimiter ;

# 在mysql中调用
mysql> call p2(1);
+----+--------+---------+
| id | user   | salary  |
+----+--------+---------+
|  2 | lisi   | 1000.00 |
|  3 | wangwu | 1000.00 |
+----+--------+---------+
2 rows in set (0.61 sec)
Query OK, 0 rows affected (0.61 sec)

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

# 根据用户名和密码判断用户是否正确
mysql> delimiter $$
mysql> create procedure authUser(in user varchar(20),in pwd varchar(20))
    -> begin
    ->     declare result int default 0;
    ->     select count(*) into result from user
    ->     where user.user=user and user.pwd=pwd;
    ->     select if(result>0,'成功','失败');
    -> end $$
Query OK, 0 rows affected (0.84 sec)
mysql> delimiter ;

# 在mysql中调用
# 正确:
mysql> call authUser('zhangsan',123);
+----------------------------+
| if(result>0,'成功','失败') |
+----------------------------+
| 成功                       |
+----------------------------+
1 row in set (0.05 sec)

# 错误:
mysql> call authUser('zhangsan',111);
+----------------------------+
| if(result>0,'成功','失败') |
+----------------------------+
| 失败                       |
+----------------------------+
1 row in set (0.25 sec)

# 在python中基于pymysql调用
# 正确:
cursor.callproc('authUser', ('zhangsan', '123'))
print(cursor.fetchone())  # ('成功',)

# 错误:
cursor.callproc('authUser', ('zhangsan', '111'))
print(cursor.fetchone())  # ('失败',)

# 传入a和b两个值,最终a和b都翻倍
mysql> delimiter $$
mysql> create procedure compute(inout a int,inout b int)
    -> begin
    ->     set a = a*2;
    ->     set b = b*2;
    -> end $$
Query OK, 0 rows affected (0.65 sec)
mysql> delimiter ;

mysql> set @m=10;
Query OK, 0 rows affected (0.00 sec)

mysql> set @n=20;
Query OK, 0 rows affected (0.00 sec)

mysql> call compute(@m,@n);
Query OK, 0 rows affected (0.44 sec)

mysql> select @m,@n;
+------+------+
| @m   | @n   |
+------+------+
|   20 |   40 |
+------+------+
1 row in set (0.00 sec)

6.4、执行存储过程

# 无参数
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)

6.5、删除存储过程

drop procedure 存储过程名称;

一切随心走_水瓶

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

数据库之视图、触发器、事务、函数相关、流程控制及存储过程

文章目录

1、视图

1.1、定义

1.2、视图的增删改查

2、触发器

2.1、定义

2.2、使用

2.3、应用

2.4、删除触发器

3、事务

3.1、定义

3.2、事务特性(4种)

3.3、事务运行模式

3.4、使用

4、内置函数及自定义函数

5、流程控制

5.1、条件语句

5.2、循环语句

6、存储过程

6.1、定义

6.2、创建存储过程(无参)

6.3、创建存储过程(有参)

6.4、执行存储过程

6.5、删除存储过程