红魔咖啡馆

头发越掉越多，头发越掉越少

0%

【MySQL】存储对象

发表于 分类于 ，
本文字数： 5.1k 阅读时长 ≈ 9 分钟
处理非结构化数据，包括视图、存储过程、触发器等

视图

视图是虚拟存在的表，视图中的数据不在数据库中实际存在，行列数据来自自定义视图的查询中使用的表（基表），并且是在使用视图时动态生成的

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

语法

  • 创建：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED|LOCAL] CHECK OPTION];

  • 查看创建视图语句：SHOW CREATE VIEW 视图名称;

  • 查看视图数据：SELECT * FROM 视图名称 ...;

  • 修改：

    • 用创建语句，必须加OR REPLACE
    • ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED|LOCAL] CHECK OPTION];

  • 删除：DROP VIEW [IF EXISTS] 视图名称

视图可以用INSERT语句插入数据，数据插入到基表中

检查选项

WITH CASCADED/LOCAL CHECK OPTION：通过视图检查正在更改的每一个行，使其符合视图的定义。可以防止通过视图插入它查不到的数据（不满足SELECT条件的）

MySQL还允许基于另一个视图创建视图，他会检查依赖视图中的规则保持一致性

cascade

该选项会检查当前视图依赖的底层的所有视图是否满足定义，默认是这个值

e.g.

1
2
CREATE OR REPLACE VIEW stu_v_1 AS SELECT id, name FROM student WHERE id<=20;
CREATE OR REPLACE VIEW stu_v_2 FROM stu_v_1 WHERE id>=10 WITH CASCADED CHECK OPTION;

这样，只能插入在10和20之间的数据

local

local选项检查当前视图满足的定义并往上查看依赖的视图

若依赖的视图添加了检查选项，则继续检查满足的定义，否则不校验

e.g.

1
2
CREATE OR REPLACE VIEW stu_v_1 AS SELECT id, name FROM student WHERE id<=20;
CREATE OR REPLACE VIEW stu_v_2 FROM stu_v_1 WHERE id>=10 WITH LOCAL CHECK OPTION;

这样，可以插入大于等于10的数据

更新

要使视图可更新，视图中的行和基础表中的行要存在一对一的关系，且若视图中包含以下任何一项，不可更新：

  • 聚合函数或窗口函数
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION/UNION ALL

存储过程

存储过程是提前编译并存储在数据库中的一段SQL的集合，即代码的封装和调用

语法

  • 创建存储过程:
1
2
3
4
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	-- 语句
END;

  • 调用存储过程：CALL 名称([参数]);

  • 查看存储过程：SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA='XXX';

    SHOW CREATE PROCEDURE 存储过程名称;

  • 删除存储过程：DROP PROCEDURE [IF EXISTS] 存储过程名称;

  • 重定义分隔符：DELIMITER 符号，防止分号使得语句提前终止

变量

系统变量

MySQL服务器提供的变量，分为全局（GLOBAL）和会话变量（SESSION），前者对全局有效，后者只对当前会话有效

  • 查看：
    • 查看所有：SHOW [SESSION|GLOBAL] VARIABLES;
    • LIKE模糊匹配：SHOW [SESSION|GLOBAL] VARIABLES LIKE '......';
    • 查看指定：SELECT @@[SESSION|GLOBAL] 系统变量;
  • 设置：
    • SET [SESSION|GLOBAL] 系统变量名 = 值;
    • SET @@[session|global].系统变量名 = 值;

用户定义变量

用户自定义变量:

  • 赋值：

    • SET @var_name = expr [, @var_name = expr]...;

    • SET @var_name := expr [, @var_name := expr]...;

    • SELECT @var_name := expr [, @var_name := expr]...;

    • SELECT 字段名 INTO @var_name FROM 表名;

  • 使用：

    • SELECT @var_name;

不需要声明，默认获取到的值为NULL

局部变量

在局部生效的变量，需要通过DECLARE声明，范围为声明的BEGIN-END块内

语法：

  • 声明：DECLARE 变量名 变量类型 [DEFAULT...]
  • 赋值：
    • SET 变量名 = 值;
    • SET 变量名 := 值;
    • SELECT 字段名 INTO 变量名 FROM 表名..;

参数

分类：

用法：

1
2
3
4
CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
	-- 语句
END;

最后CALL的时候传入一个用户定义变量并SELECT就能拿到

条件判断

IF

语法：

1
2
3
4
5
6
7
IF 条件1 THEN
...
ELSEIF 条件2 THEN
...
ELSE
...
END IF;

CASE

语法：

1
2
3
4
5
CASE case_value
	WHEN when_value1 THEN statement_list1 
	[WHEN when_value2 THEN statement_list2]
	[ELSE statement_list]
END CASE;
1
2
3
4
5
CASE
	WHEN search_condition1 THEN statement_list1
	[WHEN search_condition2 THEN statement_list2]
	[ELSE statement_list]
END CASE;

循环

WHILE

满足条件进入循环，就是while循环（废话）

语法：

1
2
3
WHILE 条件 DO
	SQL逻辑
END WHILE;

REPEAT

有条件的循环控制语句，满足条件退出循环，就是do…while循环

1
2
3
4
REPEAT
	SQL逻辑
	UNTIL 条件
END REPEAT;

LOOP

实现简单的循环，就是for循环，配合如下使用：

  • LEAVE：配合循环使用，跳出循环
  • ITERATE：直接进入下次循环

语法：

1
2
3
4
5
6
[begin_label:] LOOP
	SQL逻辑
END LOOP [end_label];

LEAVE label; -- 退出
ITERATE label; -- 跳过

e.g. 计算从1加到n的值

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE PROCEDURE p(IN n INT)
BEGIN
	DECLARE total INT DEFAULT 0;
	sum:LOOP
		IF n<=0 THEN
			LEAVE sum;
		END IF;
		
		SET tot:=tot+n;
		SET n:=n-1;
	END LOOP sum;
	SELECT total;
END;

游标

用来存储查询结果集的数据类型，在存储过程和函数中可以用游标对结果集进行循环处理：

  • 声明游标：DECLARE 游标名称 CURSOR FOR 查询语句;

    声明时，先声明普通变量再声明游标

  • 打开游标：OPEN 游标名称;

  • 获取游标记录：FETCH 游标名称 INTO 变量[, 变量,...];

  • 关闭游标：CLOSE 游标名称;

条件处理程序

条件处理程序可以用来定义流程控制结构执行过程中遇到问题时相应的处理步骤，语法为：

1
DECLARE handler_action HANDLER FOR condition_value[, condition_value...] statement;

handler_action： CONTINUE：继续执行当前程序 EXIT：退出当前程序 condition_value SQLSTATE sqlstate_value：状态码 SQLWARNING：所有以01开头的sqlstate代码缩写 NOT FOUND：所有以02开头的sqlstate代码缩写 SQLEXCEPTION：所有没被上面捕获的sqlstate代码缩写

e.g.

根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名(name)和专业(profession)，并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE PROCEDURE p(IN uage INT)
BEGIN
	DECLARE uname VARCHAR(100);
	DECLARE upro VARCHAR(100);
	DECLARE u_cursor CURSOR FOR SELECT name, profession FROM tb_user WHERE age<=uage;
	DECLARE EXIT HANDLER FOR SQLSTATE '02000' CLOSE u_cursor;
	
	CREATE TABLE IF NOT EXISTS tb_user_pro(
    	id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100),
        profession VARCHAR(100)
    );
    
    OPEN u_cursoor;
    WHILE true DO
    	FETCH u_cursor INTO uname, upro;
    	INSERT INTO tb_user_pro VALUES(null, uname, upro);
    END WHILE;
    CLOSE u_cursor;
    
END;

存储函数

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

语法

1
2
3
4
5
CREATE FUNCTION 存储函数名([参数列表])
RETURNS type[characteristic...]
BEGIN
	-- SQL 语句
END;

其中CHARACTERSTIC包括：

  • DETERMINISTIC：相同输入参数总是产生相同结果
  • NO SQL：不包含SQL同语句
  • READS SQL DATA：包含读取数据的语句，不包含写入的

e.g.

从1到n累加

1
2
3
4
5
6
7
8
9
10
11
CREATE FUNCTION p(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;

触发器

触发器是与表有关的数据库对象，在插入/更新/删除之前或之后触发并执行定义的SQL语句集合，可以在数据库端保证数据完整性

使用OLD和NEW引用触发器中发生变化的记录内容

分类

语法

  • 创建触发器：
1
2
3
4
5
6
CREATE TRIGGER trigger_name 
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名 FOR EACH ROW 
BEGIN
	trigger_stmt;
END;
  • 查看：SHWO TRIGGERS;
  • 删除：DROP TRIGGER [schema_name.]trigger_name;

INSERT型

e.g.为数据库的插入操作写一个触发器，用于记录日志（建表省略）

1
2
3
4
5
CREATE TRIGGER tb_user_insert_trigger
AFTER INSERT ON tb_user FOR EACH ROW
BEGIN
	INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params) VALUES (null, 'insert', now(), new.id, new.content);
END;

UPDATE型

1
2
3
4
5
CREATE TRIGGER tb_user_insert_trigger
AFTER UPDATE ON tb_user FOR EACH ROW
BEGIN
	INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params) VALUES (null, 'update', now(), new.id, concat(old.content, new.content));
END;

DELETE型

1
2
3
4
5
CREATE TRIGGER tb_user_insert_trigger
AFTER DELETE ON tb_user FOR EACH ROW
BEGIN
	INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params) VALUES (null, 'delete', now(), old.id, concat(old.content));
END;