mysql-视图、触发器、事务、存储过程、流程控制

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql-视图、触发器、事务、存储过程、流程控制

我要学编程 2018-09-18 20:55:00 浏览777
展开阅读全文

目录

  1. 视图
  2. 触发器
  3. 事务
  4. 存储过程
  5. 流程控制

一、视图

视图是由查询结果构成的一张虚拟表,和真实的表一样,带有名称的列和行数据

强调:视图是永久存储的,但是视图存储的不是数据,只是一条sql语句

视图的特点:

  1. 视图的列可以来自不同的表,是表的抽象和逻辑意义上建立的新关系。 
  2. 视图是由基本表(实表)产生的表(虚表)。
  3. 视图的建立和删除不影响基本表。 
  4. 对视图内容的更新(添加、删除和修改)直接影响基本表。 
  5. 当视图来自多个基本表时,不允许添加和删除数据。

优点:

  1. 可以简化查询(多表查询转换为直接通过视图查询)
  2. 可以进行权限控制(把表的权限封闭,开发对应的视图权限)

(一)、创建视图

create view 视图名称  as sql 查询语句  例子:CREATE view test_view as SELECT * from test;

(二)、查询视图

select * from 视图名 [where 条件]

(三)、修改视图

alter view 视图名称 AS SQL语句; 例子:ALTER view test_view as SELECT * from test_view WHERE salary>10000

(四)、删除视图

drop view 视图名称; 
例子:drop view test_view

二、触发器

触发器可以监视用户对表的增、删、改操作,并触发某种操作(没有查),自动执行,无法直接调用。

创建触发器语法的四要素:

  1.监视地点(table)

  2.监视事件(insert/update/delete)

  3.触发时间(before/after)

  4.触发事件(insert/update/delete)

(一)、创建触发器

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
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 //
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 ;


#往表cmd中插入记录,触发触发器,根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('A','0755','ls -l /etc',NOW(),'yes'),
    ('A','0755','cat /etc/passwd',NOW(),'no'),
    ('A','0755','useradd xxx',NOW(),'no'),
    ('A','0755','ps aux',NOW(),'yes');


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

强调:NEW表示即将插入的数据行,OLD表示即将删除的数据行

(二)、查看触发器

show triggers

(三)、删除触发器

drop trigger 触发器的名称

三、事务

事务用于将某些操作的多个SQL作为原子性操作,意思就是,事务是一组sql语句集合。

一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。在事务内的语句, 要么全部执行成功, 要么全部执行失败。

(一)、事务的特性

事务具有以下四个特性(ACID)

  1.原子性:事务是一个整体,不可分割,包含在其中的sql操作要么全部成功,要么全部失败回滚,不可能只执行其中一部分操作。

  2.一致性:当事务执行后 所有的数据都是完整的(外键约束 非空约束)。

  3.持久性:一旦事务提交,数据永久保存在数据库中

  4.隔离性:事务之间相互隔离,一个事务的执行不影响其他事务的执行

SQL标准定义了4类隔离级别,包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的。低级别的隔离级一般支持更高的并发处理,并拥有更低的系统开销。

(二)、事务的隔离级别

  1.READ UNCOMMITED(未提交读):所有事务都可以看到其他未提交事务的执行结果。很少用于实际应用,因为它的性能不比其他级别好多少

  2.READ COMMITED(提交读):大部分数据库默认级别,不包括mysql。一个事务从开始到提交之前, 所做的任何修改对其他事务都是不可见的。

  3.REPEATABLE READ(可重复读):mysql默认级别,解决了脏读的问题. 该级别保证了在同一个事务中多次读取同样记录的结果时一致的. 无法解决幻读问题

  4.SERIALIZABLE(可串行化):是最高的隔离级别,强制事务排序,使之不可能相互冲突,从而解决幻读问题

脏读: 一个事物 读到了 另一个事务未提交的数据 查询 之前要保证 所有的更新都已经完成。

不可重复读:在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读:指的是当某个事务在读取某个范围内的记录时, 另外一个事务又在该范围内插入了新的记录, 当之前的事务再次读取该范围的记录时, 会产生幻行(Phantom Row).

 (三)、事务操作

start transaction; 开启一个事物
commit 提交事物
rollback 回滚事务

注:mysql默认开启自动提交事务,pymysql默认是不自动提交,需手动commit

四、存储过程

存储过程包含了一系列可执行的sql语句的集合,类似于函数(方法)。

使用存储过程的优点:

#1. 用于替代程序写的SQL语句,实现程序与sql解耦

#2. 基于网络传输,传别名的数据量小,而直接传sql数据量大

缺点:不方便扩展

(一)、使用存储过程

创建语法:
    create procedure 过程的名称 ({in,out,inout}  数据类型 参数名称)
    begin
        具体的sql代码
    end
    参数前面需要指定参数的作用
in 表示该参数用于传入数据 out 用于返回数据 inout 即可传入 也可返回 参数类型是 mysql中的数据类型

调用语法:
  call 存储过程()
         案例:创建一个存储过程 作用是将两个整数相加
            create procedure add_p (in a int,in b int)
            begin
                select a + b;
            end

            //
      调用 call add_p(1,2)

         案例:创建一个存储过程 作用是将两个整数相加 将结果保存在变量中
        定义一个变量
        set @su = 100;
            create procedure add_p2 (in a int,in b int,out su int)
            begin
                set su = a + b;
            end

            //
    
    定义变量 set @su = 100;
    调用过程 call  add_p2(10,20,@su);

          注意  在存储过程中 需要使用分号来结束一行 但是分号有特殊含义
          得将原始的结束符 修改为其他符号
            delimiter // 结束符更换为//
            delimiter;
案列
在存储过程中 需要使用分号来结束一行 但是分号有特殊含义
得将原始的结束符 修改为其他符号
      delimiter // 结束符更换为//
      delimiter;
    create procedure show_p (in a int)
    begin
    if a = 1 then
        select "壹";
    elseif a = 2 then
        select "贰";
    else
        select "other";
    end if;
    end //
使用存储过程 完成 输入 一个 数字 1或2 显示 壹 或 贰

(二)、删除存储过程

drop procedure proc_name;

五、流程控制

(一)、条件语句

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

(二)、循环语句

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 ;
while
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 ;
repeat
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
loop

 

焚膏油以继晷,恒兀兀以穷年。

网友评论

登录后评论
0/500
评论
我要学编程
+ 关注