06 - Views, stored procedures, triggers

06 - 视图、存储过程、触发器

1. 视图导论

1.1. 介绍

view 虚拟存在的表，并不在数据库中实际存在，行和列数据来自定义视图查询中使用多个表。

只保存了查询的 SQL 逻辑，不保存结果

1.2. 实践

CREATE OR REPLACE VIEW V1 AS SELECT JNO, CITY FROM j WHERE CITY = '天津';
// 创建视图

SHOW CREATE VIEW V1;
// 建图语句

SELECT * FROM V1;
// 查询视图

DROP VIEW IF EXISTS V1;
// 删除视图

2. 视图操作

增删改...

2.1. 视图检查选项

CASCADED 和 LOCAL

通过视图检查正在更改的每个行

会依赖视图中的规则以保持一致性...

依赖检查...

CASCADED

CASCADED：如果当前视图有检查选项，则插入数据要满足包括当前视图条件以及满足当前视图所依赖的视图的条件。它会递归地检查所有依赖的视图，确保数据操作符合所有相关视图的定义。

LOCAL

LOCAL：仅检查当前视图的定义条件，不考虑当前视图所依赖的视图的检查选项。如果当前视图有检查选项，则检查；如果没有，则不进行检查。

2.2. 视图的更新

要想更新：必须满足

  • 行与表与基础表存在一对一的关系

    • 下面这些不能更新

    • 聚合函数、窗口函数

    • DISTINCT

    • UNION 和 UNION ALL

    • GROUP BY

    • 等

2.3. 面试考点

  1. 请简述视图的概念及其特点。

    1. 视图是虚拟表，其内容是基于实际表的查询结果集，不存储数据，而是动态生成

  3. 当使用WITH CHECK OPTION创建视图时，其作用是什么？

    1. 其用于保证数据操作符合视图定义，确保数据一致性和完整性

  5. CASCADED和LOCAL选项在视图检查中的具体含义及区别是什么？

    1. CASCADED 会递归检查所有依赖视图

    2. 而 LOCAL 仅检查当前视图的定义条件

  7. 视图与表？

    1. 存储数据与否

    2. 创建方式

3. 视图的作用

3.1. 简单

可以把一些复杂的条件定义在视图中

3.2. 安全

数据库可以用户授权，用户只能查询和修改他们所能见到的

3.3. 数据独立

屏蔽基表修改带来的变化

3.4. 案例

create view tb_user as select id, name, profession, age, gender, status, createtime form tb_user
select * from tb_user_view;


-- 三表联查...可以保存为一个视图，没必要每次都联查条件
select * from student s, student_course sc, course c where s.id = sc.studentid and sc.courseid = c.id;
create view tb_stu_course_view as select s.name student_name,s.no student_no,c.name course_name from student s, student_course sc, course c where s.id = sc.studentid and sc.courseid = c.id;

4. 存储过程

4.1. 介绍

存储过程是事件经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作...

4.2. 特点

  • 封装，复用

  • 可以接收参数，可以返回数据

  • 减少网络交互，效率提升

4.3. 语法

创建

CREATE PROCEDURE --- 存储过程名称 p1()
BEGIN
  --- 用到的SQL语句
END;

调用

call p1();

查看

select * from information_schema.ROUTINES WHERE ROUNTINE_SCHEMA = ...;

删除

DROP PROCEDURE IF EXISTS P1;

注意

在命令行的时候尽量不用，因为一旦出现分号就认为结束

delimiter 来修改结束的标志

4.4. 复杂语法

变量

  • 系统变量：服务器提供，用户不用定义，GLOBAL 全局变量 和 SESSION 会话变量

SHOW SESSION | GLOBAL VARIABLES;
LIKE ...; --- 模糊匹配

  • 用户定义变量，用户根据自己需要定义的变量

    • 用户定义变量（User-Defined Variables）在 MySQL 中是一种非常灵活的工具，用户可以根据自己的需要定义和使用这些变量。

    • 存储中间结果

    • 跨查询传递数据

    • 动态生成数据

    • 简化查询

  • 局部变量，需要声明

    • 根据需要定义内部生效的变量，访问之前，需要 DECLARE 声明...用作存储过程内的局部变量和输入参数

if

  • if ... then

    • else ...

参数

  • IN 输入 默认

  • OUT 输出

  • INOUT 输入输出都可以

存储过程名称后面需要定义参数的类型和名称
create procedure p4(IN score int, out result varchar(10))
begin

  if score >= 85 THEN
    set result := 'excel';
  elseif score >= 60 THEN
    set result := 'pass';
  ELSE
    set result := 'fail';
  end if;
end;

call p4(18, @result1);

select @result1

CASE

流程控制语句的 CASE

  • 语法 1

CASE + 表达式

END CASE...

  • 语法 2

CASE

WHEN EXPR

END

WHILE 循环

有条件的循环控制语句，满足条件后执行循环体中的SQL语句...

注意条件控制

REPEAT

有条件的循环控制语句，满足条件后退出循环

先循环一次再进行判断...

LOOP

简单循环，如果不在SQL中增加退出循环条件，可以实现死循环...可以配合 LEAVE 和 ITERATE 使用...

CREATE PROCEDURE p13(IN n INT)
BEGIN
  DECLARE total INT DEFAULT 0;
  sum:LOOP
  IF n <= 0 THEN
      LEAVE sum;
  END IF;

  IF n % 2 = 1 THEN
    SET n := n - 1;
    ITERATE sum;
  END IF;
  SET total := total + n;
  SET n := n - 1;
  END LOOP sum;
  SELECT total;
END;

注意再 ITERATE sum 之前更新 n...

4.5. 游标

CURSOR

对存储结果进行循环处理...

  • 声明游标

  • 打开游标

  • 获取游标记录

  • 关闭游标

CREATE PROCEDURE p14(IN uage INT)
BEGIN
  DECLARE name VARCHAR(50);
  DECLARE profession VARCHAR(50);
  DECLARE u_cursor CURSOR FOR SELECT name, profession FROM users WHERE age <= uage;
  DROP TABLE IF EXISTS u_info;
  CREATE TABLE IF NOT EXISTS u_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    profession VARCHAR(50)
  );
  OPEN u_cursor;
  WHILE TURE DO
    FETCH u_cursor INTO name, profession;
    
    INSERT INTO u_info (name, profession) VALUES (null, name, profession);
  END WHILE;
END;

4.6. 条件处理程序...

Handler...

定义在流程控制过程中遇到问题是相应的解决步骤...

handler_action

CONTINUE

EXIT

condition_value

SQLSATTE sqlstate_value

SQLWARNING

NOT FOUND

SQLEXCEPTION

5. 存储函数

语法结构和流程控制和存储过程差不多...

存储函数是有返回值的存储过程，存储函数的参数只能是 IN 类型的...

characteristic

  • DETERMINISTIC 相同输入产生相同结果

  • NO SQL 不含 SQL

  • READS SQL DATA 读取数据语句

实践

CREATE FUNCTION fun1(n int)
returns INT DETERMINISTIC
BEGIN
  DECLARE total int DEFAULT 0;
  WHILE n > 0 DO
   SET total := total + n;
   SET n := n - 1;
  END WHILE;
  return total;
end;

能够使用函数的地方，都能使用 存储过程

存储函数用得少一些

6. 触发器

与表有关的数据库对象

在insert update delete 之前之后可以触发触发器定义的SQL语句集合...

触发器的特性额可以协助应用在数据库端确保数据完整性...

6.1. 触发器类型

  • INSERT

  • UPDATE

  • DELETE

6.2. 触发器语法...

创建

行级触发器...语句级？

目前只支持行级...

CREATE TRIGGER...

查看

SHOW TRIGGERS;

删除

DROP TRIGGER TRIGGER_NAME;

6.3. 实践

触发器能够在一个操作之后返回一个输出的日志？类似调试的 printf 的作用...

通过 OLD 和 NEW 来索取更新之前和之后的值？

行级触发器，操作影响了多少行，就会产生多少条日志...

Previous05 - SQL OptimizationNext07 - Lock

Last updated