1.什么是视图?

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

2.为什么要使用视图?

如果要频繁使用一张虚拟表,可以不用重复查询

3.如何使用视图?

create view 表1_表2 as

select * from 表1 inner join 表2 on 条件;

例:

    create view teacher_course as

    select * from teacher inner join course on teacher.tid = course.teacher_id;

强调:

1.在硬盘中,视图只有表结构文件,没有表数据文件

2.视图通常是用于查询,尽量不要修改视图中的数据

1.为何要用触发器

触发器专门针对我们对某一张表数据 增insert,删delete,改update 的行为,着类行为一旦执行,就会触发触发器的执行,即自动运行另外一段sql代码

创建触发器语法:

# 针对增

create trigger tri_after_insert_ti after insert on 表名 for each row

begin

    sql代码...

end

create trigger tri_before_insert_ti 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_cmd CHAR (64),

    err_time datetime

);

​

delimiter $$  # 将mysql默认的结束符由;换成$$

create trigger tri_after_insert_cmd after insert on cmd for each row

begin

    if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象

        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);

    end if;

end $$

delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

​

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志

INSERT INTO cmd (

    USER,

    priv,

    cmd,

    sub_time,

    success

)

VALUES

    ('egon','','ls -l /etc',NOW(),'yes'),

    ('egon','','cat /etc/passwd',NOW(),'no'),

    ('egon','','useradd xxx',NOW(),'no'),

    ('egon','','ps aux',NOW(),'yes');

​

# 查询errlog表记录

select * from errlog;
# 删除触发器

drop trigger tri_after_insert_cmd;

# 创建一张表

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

1.修改数据之前先开启事务操作
start transaction;
# 修改操作

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元

# 回滚到上一个状态
rollback;
# 开启事务后,只要没有执行commit操作,数据其实都没有真正刷到硬盘
commit;
'''

开启事务检测操作是否完整,不完整主动回滚到上一个状态,如果完整就应该执行commit操作
'''

# python代码角度,应该实现的伪代码逻辑

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;

# 如何检测异常?

创建存储过程

delimiter $  # 修改结束符号

create procedure p1(

    in m int,  # in宝石这个参数必须只能是传入不能被传出 int是参数类型

    in n int,

    out res int,  # out表示这个参数可以被返回出去

    inout y int,  # inout表示既可以传入也可以传出

)

begin

    sql语句;

end $

delimiter ;  # 将修改符号改回来

1.在mysql中调用 call + 存储名 调用

call p1(2,4,10);  # 报错

set @res = 10  # res 的值是用来判断存储过程是否被执行成功的依据,所以需要先定义一个变量 @res = 10

call p1(2,4,@res);

select @res;  # 查看结果,若@res变量值发生了变化,执行成功

2.在python中调用

pymysql链接mysql

产生的游表cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;

cursor.excute('select @_p1_2;')

存储过程与事务使用举例(了解)

delimiter //

create PROCEDURE p5(

    OUT p_return_code tinyint

)

BEGIN

    DECLARE exit handler for sqlexception

    BEGIN

        -- ERROR

        set p_return_code = 1;

        rollback;

    END;

​

​

  DECLARE exit handler for sqlwarning

  BEGIN

      -- WARNING

      set p_return_code = 2;

      rollback;

  END;

​

  START TRANSACTION;

      update user set balance=900 where id =1;

      update user123 set balance=1010 where id = 2;

      update user set balance=1090 where id =3;

  COMMIT;

​

  -- SUCCESS

  set p_return_code = 0; #0代表执行成功

​

​

END //

delimiter ;

CREATE TABLE blog (

    id INT PRIMARY KEY auto_increment,

    NAME CHAR (32),

    sub_time datetime

);

​

INSERT INTO blog (NAME, sub_time)

VALUES

    ('第1篇','2015-03-01 11:31:21'),

    ('第2篇','2015-03-11 16:31:21'),

    ('第3篇','2016-07-01 10:21:31'),

    ('第4篇','2016-07-22 09:23:21'),

    ('第5篇','2016-07-23 10:11:11'),

    ('第6篇','2016-07-25 11:21:31'),

    ('第7篇','2017-03-01 15:33:21'),

    ('第8篇','2017-03-01 17:32:21'),

    ('第9篇','2017-03-01 18:31:21');

​

select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

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

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