视图、触发器、存储过程、函数

一、视图：
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、视图是为了简化查询的sql语句，不应该修改视图中的记录
create view emp2dep as select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id = dep.id;



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

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

03 创建触发器语法
# 针对插入
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


# 针对删除
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


# 针对修改
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


案例：
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_id int
);

delimiter $$
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');


删除触发器：drop trigger tri_after_insert_cmd;



三、事务：

#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=900 where id=1;
    update user set balance=1010 where id=2;
    update user set balance=1090 where id=3;
    commit;
except Exception:
    rollback;


四、流程控制：

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

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循环
loop循环





五、函数

1、强调：mysql内置的函数只能在sql语句中使用

mysql> select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
格式过时间






视图、触发器、事务 为了简化应用程序sql语句编写

存储过程把应用程序完全解放出来，不需要写任何sql语句



六、存储过程（*****）

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


程序与数据库结合使用的三种方案:
方案一：
        应用程序：只需要开发应用程序的逻辑
        mysql：编写好存储过程，以供应用程序调用

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

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

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

方案三：
        应用程序：ORM(类/对象 --->原生sql) 开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM
        mysql：

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


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

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


# 无参
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)
通常用返回值（等于0）标识sql语句运行成功

# 在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())