python学习第48天视图、触发器、事务、存储过程、函数、控制流程

一、视图

1、什么是视图
视图就是通过查询得到一张虚拟表，然后保存下来，下次用的直接使用即可

2、为什么要用视图

查询出来 的表打印出来就没了，再次需要的话还要再次查询
如果要频繁使用一张虚拟表，可以不用重复查询

3、如何用视图

create view teacher2course as
select * from teacher inner join course
    on teacher.tid = course.teacher_id;

强调
1、在硬盘中，视图只有表结构文件，没有表数据文件
2、视图通常是用于插叙，尽量不要修改视图中的数据

drop view teacher2course;删除

二、触发器

1、什么是触发器

在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器

2 为何要用触发器？
触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行
就会触发触发器的执行，即自动运行另外一段sql代码

3 创建触发器语法

（1）针对插入

create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
    sql代码。。。
end

create trigger tri_after_insert_t2 before insert on 表名 for each row
begin
    sql代码。。。
end

tri_after_insert_t1为触发器的名字

for each row监测每一条改动

（2）针对删除

create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
    sql代码。。。
end

create trigger tri_after_delete_t2 before delete on 表名 for each row
begin
    sql代码。。。
end

（3）针对修改

create trigger tri_after_update_t1 after update on 表名 for each row
begin
    sql代码。。。
end

create trigger tri_after_update_t2 before update on 表名 for each row
begin
    sql代码。。。
end

（4）案例

#创建触发器

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 ;


drop trigger tri_after_insert_cmd;

#验证触发器

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
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');

#结果

#查询错误日志，发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2017-09-14 22:18:48 |
|  2 | useradd xxx     | 2017-09-14 22:18:48 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)

三、事务

01 什么是事务
开启一个事务可以包含一些sql语句，这些sql语句要么同时成功，要么一个都别想成功，称之为事务的原子性

start transaction
rollback 出现错误则执行回滚
commit 没有错误则提交

（1）四个特性：

原子性：

一致性:完整性一致，约束条件都一样的

隔离性：

持久性：
（2）四个隔离级别：

读未提交

读已提交：防止脏读，不能防止幻读和不可重复读

可重复读

序列化执行(串行)  安全，效率低

2、事务的作用

保证sql语句的执行，当需要保证一堆sql 要么都成功 要么都失败时，比如转账

3、怎么用

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);

try:
    update user set balance=900 where name='wsb'; #买支付100元
    update user set balance=1010 where name='egon'; #中介拿走10元
    update user set balance=1090 where name='ysb'; #卖家拿到90元
except 异常:
    rollback;
else:
    commit;

和捕捉异常一样

四、存储过程

1、什么是存储过程

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

2、三种开发模型

1、
        应用程序：只需要开发应用程序的逻辑
        mysql：编写好存储过程，以供应用程序调用

        优点：开发效率，执行效率都高
        缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差

    2、
        应用程序：除了开发应用程序的逻辑，还需要编写原生sql
        mysql：

        优点：比方式1，扩展性高(非技术性的)
        缺点：
            1、开发效率，执行效率都不如方式1
            2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题


    3、
        应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM
        mysql：

        优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处
        缺点：执行效率连方式2都比不过

通常我们开发选第二种和第三种

3、创建存储过程（好比Python中的函数）

elimiter $$
create procedure p1(
    in m int,
    in n int,
    out res int
)
begin
    select tname from teacher where tid > m and tid < n;
    set res=0;
end $$
delimiter ;

关键字procedure  m和n为参数，数据格式为整型 ，out是参数，也是返回值，数据类型为整型

4、如何调用存储过程

（1）在mysql中直接调用

call p1(2,4,10);直接传值不合理的，在报错，第三个必须要是变量

set @res=10

call p1(2,4,@res);
#查看结果
select @res;

（2）在python程序中调用

# import pymysql
#
# conn=pymysql.connect(
#     host='127.0.0.1',
#     port=3306,
#     user='root',
#     password='123',
#     charset='utf8',
#     database='db42'
# )
#
# cursor=conn.cursor(pymysql.cursors.DictCursor)
#
# cursor.callproc('p1',(2,4,10)) #@_p1_0=2,@_p1_1=4,@_p1_2=10
#
#
# print(cursor.fetchall())
#
# cursor.execute('select @_p1_2;')
# print(cursor.fetchone())
#
# cursor.close()
# conn.close()

五、数据库函数

1、定义：

在SQL 语句中，表达式可用于一些诸如SELECT语句的ORDER BY 或 DELETE或 UPDATE语句的 WHERE ⼦句或 SET语句之类的地放。使用文本值、column值、NULL值、函数、 操作符来书 写 表达式。这些内置函数极大提高了我们的开发效率。

 

2、自定义函数

！！！注意！！！

#函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能
#若要想在begin...end...中写sql，请用存储过程

delimiter //
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END //
delimiter ;

3、删除函数：

drop function func_name;

4、执行函数

# 获取返回值
select UPPER('egon') into @res;
SELECT @res;


# 在查询中使用
select f1(11,nid) ,name from tb2;

 六、控制流程

 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 ;

2、循环语句：
（1）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 ;

（2）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 ;

（3）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