视图、触发器、事务（了解知识点），存储过程（重点）

1.视图
 强调
1、字段名不能重复
2、视图是为了简化查询的sql语句，不应该修改视图中的记录
create view emp2dep as select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id = dep.id;

注意：

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

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

2.触发器

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') #no代表执行失败，执行失败就会切换到errlog
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_id int
);

delimiter $$  # 每插入一行操作，触发器都会检测一次，如果success为no把为no的id传进去
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
    if NEW.success = 'no' then
        insert into errlog(err_id) values(NEW.id);
    end if;
END $$
delimiter ;

INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');

3.事物

#transaction:事务，交易
# 事务可以包含一系列的sql语句，事务的执行具有原子性
#1、原子性：
#包含多条sql语句要么都执行成功，要么都执行不成功
#2、回滚

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);


start transaction;
try:
    update user set balance=930 where id=1;
    update user set balance=1010 where id=2;
    update user set balance=1090 where id=3;
    commit;
except Exception:
    rollback（）;

 

4.存储过程

方案一：
    应用程序：调用存储过程
    mysql：编写存储过程

方案二：
    应用程序：原生sql，纯sql语句
    mysql：

方案三：
    应用程序：ORM(类/对象 --->本质还是原生sql)
    mysql：



执行效率：
    方案一 > 方案二 -> 方案三

开发效率：
    方案一 > 方案三 -> 方案二

无参存储过程

# 无参
delimiter $$
create procedure p1()
BEGIN
    select * from blog;
END $$
delimiter ;

create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

delimiter $$
create procedure p2()
BEGIN
    declare n int default 1;
    while (n < 100) do
        insert into s1 values(n,concat('egon',n),'male',concat('egon',n,'@163.com'));
        set n=n+1;
    end while;
END $$
delimiter ;

有参存储过程

# 有参
delimiter $$
create procedure p3(
    in n int,
    out res int
)
BEGIN
    select * from blog where id > n;
    set res = 0;
END $$
delimiter ;

# 直接在mysql中调用：
mysql> set @x=111;
mysql> call p3(3,@x);
mysql> select @x;
+------+
| @x   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)


# 在python中调用：
cursor.callproc('p4',(3,111)) #set @_p4_0 = 3; set @_p4_1 = 111
print(cursor.fetchall())
cursor.execute('select @_p4_1;')
print(cursor.fetchone())

在python中完整调用

import pymysql

client=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='egon123',
    database='db5',
    charset='utf8'
)

cursor=client.cursor(pymysql.cursors.DictCursor)
res=cursor.callproc('p4',(3,111)) #set @_p4_0 = 3; set @_p4_1 = 111
# print(res)

print(cursor.fetchall())

cursor.execute('select @_p4_1;')
print(cursor.fetchone())

cursor.close()
client.close()