数据库之进阶(视图,事务,存储过程)

1.视图
什么是视图:
通过查询得到的虚拟表,然后保存下来,下次用的时候直接用即可
为什么要用视图:
减少sql代码和重复查询
怎么用视图:
语法:create view 视图名 as 虚拟表(通过查询获得的虚拟表)

create view b2a as 
select b.book_jd,group_concat(author.name) "author_name" from
book_author as b left join author on b.author_id = author.id group
by b.book_id

注意:
1.在硬盘中,视图只有表结构文件,没有表数据文件
2.视图通常用于查询,尽量不要修改视图中的数据
(通常视图是多个表的拼接结果,不知道会改了哪个表)
开发时,通常不用视图,扩展性差,如果表修改了,还要修改视图

2.触发器
什么叫触发器:
在满足对某张表数据的增,删,改(查询不会触发)的情况下,自动触发的功能
称之为触发器#类似log模块的功能,log是我们在需要的地方人为设置
触发器是自动触发的

为什么要用触发器:
可以监测对表的修改动作,以便后续检查

怎么用触发器:
触发器语法:

①触发器的命名 tri_after(两种选择,after,before)_insert(三种选择,update,insert,delete)_t2(对哪张表进行的修改)
②创建语法:
create trigger tri_after_insert_t2   after insert  on t2 for each row;
delimiter // #将mysql默认的结束标志;改为//
begin
       if NEW.success = "no" then#将插入的每条记录当做对象NEW
        insert into errlog(err_cmd,err_time)values(NEW.cmd,NEW.sub_time);
        end if;
end //
delimiter ; #将结束标志改回来

例子:
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代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

创建触发器:
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 ;

插入数据:
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');
#now() mysql函数获取当前时间

#查询errlog,触发器执行成功
select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2019-01-13 13:51:47 |
|  2 | useradd xxx     | 2019-01-13 13:51:47 |
+----+-----------------+---------------------+

注意:1.创建前将默认结束标志修改 delimiter //
         2.创建触发器时,不用加;结束
        3.流程控制的if语句 判断条件一个= ,不以;作为判断结束,而是以then
          4.触发器执行的sql语句必须以;结束            
        5.创建完成后,将结束标志改回;  delimiter ;

事务:
什么是事务:
开启一个事务可以包含一系列sql语句,这些sql语句要么同时成功,要么一个都别想成功,称之为事务的原子性(原子不可再分)
事务的作用:
transaction 英文也叫交易,主要用途保证交易的安全性
转账的时候,本质就是一方减钱另一方加钱,事务可以保证这两个行为要么同时成功,
要么都失败,避免了一方减钱,另一方没有加钱的情况发生
如何用事务:
语法:
start transaction;#开启事务
rollback; #未有commit之前都可以清除修改
commit; #提交后才会将修改写入硬盘

用例:
create  table  user (
id  int primary key auto_increment,
name char(16),
salary float);

insert into user(name,salary) values("zb",1000),("egon",1000);

错误示范:
update user set  salary = 900 where name="zb";
update user set  salarys= 1010 where name = "egon";
#以上第一条语句执行成功,第2条由于字段名错误未有执行成功


start transaction;
update user set  salary = 900 where name="zb";
update user set  salarys= 1010 where name = "egon";
第二条报错
使用rollback;回滚,将修改动作取消
mysql> select * from user;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | zb   |    900 |
|  2 | egon |   1000 |
+----+------+--------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | zb   |   1000 |
|  2 | egon |   1000 |

开启事务后对表进行修改,需要使用commit才会将修改写入硬盘,
提交后使用回滚rollback将无法回到未修改前状态

#事务缺少检测异常机制

存储过程

什么是存储过程:
存储过程包含了一系列可执行的sql语句,存储过程存放于mysql中(在mysql中实现的),通过调用它的名字可以执行其内部的一堆sql
相当于提供的接口

三种开发模式:
1.应用程序:只需要开发应用程序的逻辑,mysql编写好存储过程,以供程序调用
优点:开发效率,执行效率都高
缺点:人为因素,跨部门沟通等问题,会导致扩展性差

2.应用程序:除了开发应用程序的逻辑,还需要编写原生的sql
优点:比方式1扩展性高(非技术性)
缺点:开发效率,执行效率都不如方式1,编写原生sql太过于复杂,而且需要考虑sql语句的优化问题(应该主要是查询问题,索引相关)


3.应用程序:开发应用程序的逻辑,不需要编写原生sql,基于别人写好的框架来处理数据,orm
优点:不用再编写原生sql,开发效率比方式2高,同时兼容方式2扩展性高的好处
缺点:执行效率连方式2都不如

创建表:
create table teacher(
id int primary key auto_increment,
name char(16),
sex enum("male","female") default "male");

insert into teacher(name,sex) values("egon","male"),("alex","male"),("zero","male"),("owen","female");

#存储过程语法:
delimiter //
create procedure p1(
in m int,
in n int,
out res int )
#inout 即可以当输出又可以当输入)#创建存储过程跟事务一样不用加分号

begin
select name from teacher where id >m and id< n;
set res = 0;
end //
delimiter ;
#in 输入变量,不可当做输出变量
#out 输出数据
#inout 可输入可输出变量

使用
①在mysql中调用
set @res =10;
call p1(2,4,@res);#out的参数必须是变量,执行存储过程中的代码
select  @res;
②在python中使用
import pymysql as psql
#1.创建连接
c = psql.connect(
host = "localhost",
port = 3306,
db = "db12",
user="root",
passwd = "123")
#2.建立游标
cursor = c.cursor(psql.cursors.DictCursor)
#3.调用存储过程
cursor.callproc("p1",(2,4,10))#@_p1_0=2,@_p1_1=4,@_p1_2=10
print(cursor.fetchall())#获取结果
#select @_p1_2;mysql 语句
cursor.execute("select @_p1_2;")#返回结果是影响的行数
print(cursor.fetchone())#拿到返回结果
cursor.close()
c.closer()
#执行结果
#[{'name': 'zero'}]
#{'@_p1_2': 0}

查看存储过程
show create procedure p1\G;
#\G让字段竖着排
删除存储过程
drop procedure p1;

存储过程跟事务连用:
存储过程相当于python函数,封装功能
事务保证多个sql语句同时成功
本质帮事务加上异常检测和处理操作
delimiter //
    create procedure p2(
    out res int)
begin#begin内的是存储过程内的sql语句,需要以分号结束
    declare exit handler for sqlexception#检测错误异常
    begin
    set res = 1;
    rollback;
    end;
    
    declare exit handler for sqlwarning#检测警告异常
    begin
    set res =2;
    rollback;
    end;
    
    start transaction;
    delete from tb1;#不存在的表,执行失败,验证用
    insert into teacher(name)values("hah");
    commit;
    set res = 0;
end //
delimiter ;

在sql中调用
set @res = 123;
call p2(@res);
查看结果
select @res;

mysql函数:NOW()获取当前时间,dataformat(sub_time,"%Y-%m")将字段sub_time按年月格式分组