视图、触发器、存储过程、流程控制、事物

视图

视图是一张虚拟的表(只有表结构,没有表数据),但是它的修改会影响关联它的表

#语法：CREATE VIEW 视图名称 AS  SQL语句
create view teacher_view as select tid from teacher where tname='李平老师';
  mysql> show tables;
  +---------------+
  | Tables_in_crm |
  +---------------+
  | class |
  | course |
  | score |
  | student |
  | teacher |
  | teacher_view |  #看这里
  +---------------+

  mysql> desc teacher_view;  #有表结构
  +-------+---------+------+-----+---------+-------+
  | Field | Type | Null | Key | Default | Extra |
  +-------+---------+------+-----+---------+-------+
  | tid | int(11) | NO | | 0 | |
  +-------+---------+------+-----+---------+-------+
  1 row in set (0.04 sec)

  mysql> select * from teacher_view; #有表数据
  +-----+
  | tid |
  +-----+
  | 2 |
  +-----+
  1 row in set (0.00 sec)

注意：视图只有表结构，没有表数据,视图的好处是以后我们如果再需要查询或者使用上面的虚拟表，就可以直接使用这个视图了，sql的代码量也会省很多。但是弊端也很致命，看下面注意的内容。

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

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

#2. 而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，然后再到自己的应用程序里面将那个sql语句改一改，需要很多的修改工作，并而对视图的更改通常在一般中型及以上公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便
这么多的弊端，为什么mysql还要提供这个东西呢，有一点是因为mysql想把所有数据处理的工作全部接手过来，但其实还有其他的原因，等我们讲完存储过程在和大家说吧。
#3 并且注意：视图一般都是用于查询，尽量不要修改(插入、删除等)视图中的数据，虽然有时候可以修改成功，但是尽量不要这样做，因为这个视图可能是多个表联合起来生成的一个结果，如果你修改它，可能会造成很多表里面的数据都跟着被修改了

 

 触发器

　　使用触发器可以定制用户对某一张表的数据进行【增、删、改】操作时前后的行为
(没有查询)，在进行增删改操作的时候，触发的某个操作，称为触发器，也就是增删改的行为触发另外的一种行为，
触发的行为无非就是sql语句的事情，及自动运行另外一段sql语句
注意:名字的设置要见名知义

 

示例
delimiter //  #将结束符; 改为//
create trigger t_name before(after) insert(update\delete) on 表名 for each row
 begin  #一直到end,这里面的代码就是触发器要做的sql事情,
    select * from xx;
    ...
  end //
delimiter ;

 

事物(原子性,持久性,一致性,隔离性)  

原子性:要么全部成功,要么全部失败
一致性: 数据的一致
隔离性:别人看不到事物里面的执行结果(conmilit提交之前),只能看到磁盘上的数据
持久性:改完就没法再回滚回去,只能手动修改

 

　　事务用于将某些操作的多个SQL作为原子性操作，也就是这些sql语句要么同时成功，要么都不成功，一旦有某一个出现错误，
即可回滚到原来的状态，从而保证数据库数据完整性。

　　简单来说：我给一个姑娘转账，姑娘那儿收到了200，你的账户上扣了200，这两个操作是不是两个sql语句，这两个sql语句是你的应用程序发给mysql服务端的，并且这两个sql语句都要一起执行，不然数据就错了，
你想想是不是。并且如果你通过应用程序发送这两条sql的时候，由于网络问题，你只发送了一个sql过来，那只有一个账户改了数据，另外一个没改，那数据是不是就出错了啊。这就是事务要完成的事情。

 

start transaction;
一堆sql语句
如果失败
declare exit handler fro sqlexception
begin
rollback;  #回滚
end

commit;  #提交

 

存储过程        (可以有参数[in(传入参数的) , out(返回值) , inout(既可传入又可以返回值)])

类似于python里的 函数时的操作(创建,调用都有很相似)

#创建存储过程
delimiter //
create procedure p1(in n1 int,out n2 int, inout n3 int)  #out,inout 传参时要给个变量名,mysql里面变量要以@开头
BEGIN
    select * from blog;
    INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;

#调用存储过程,先设置变量set @...
set @res=1;
set @res2=2;
call p1(1,@res,@res2);#类似于MySQL的函数，但不是函数，MySQL的函数(count()\max()\min()等等)都是放在sql语句里面用的，不能单独的使用，存储过程是可以直接调用的  call 名字+括号;

select @res,@res2;#(查看变量值) #类似于MySQL的函数，但不是函数，MySQL的函数(count()\max()\min()等等)都是放在sql语句里面用的，不能单独的使用，存储过程是可以直接调用的  call 名字+括号;
#MySQL的视图啊触发器啊if判断啊等等都能在存储过程里面写，这是一大堆的sql的集合体，都可以综合到这里面

#python中基于pymysql调用
　　cursor.callproc('p1',(1,2,3)) #底层会将输入的数据转换成 @_p1_1,@_p1_2,@_p1_3
　　print(cursor.fetchall())

流程控制

if条件语句
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 ;

 

循环语句
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 ;