六.视图与存储
| 数据库对象 | 描述 |
|---|---|
| 表 | 表是存储数据的逻辑单元 |
| 数据字典 | 存放数据库相关信息的表 |
| 约束 | 执行数据校验的规则，用于保证数据合法性 |
| 视图 | 一个或多个数据表内数据的逻辑显示，视图并不存储数据 |
| 索引 | 用于提高查询性能，相当于书的目录 |
| 存储过程 | 用于完成一次完整的夜幕处理，没有返回值 |
| 存储函数 | 用于完成一次特定的计算，具有一个返回值 |
| 触发器 | 相当于一个事件监听器，用来监听数据库发生的事件 |
一.视图
视图建立在已有表之上，视图赖以建立的表称为基表。对视图进行更改则会更改基表对应的内容。
可以认为视图即是被存储起来的SELECT语句。创建视图的过程只能复制对应的数据，约束等内容不会被纳入视图中。
1.创建
CREATE VIEW new_view AS SELECT * FROM test;
其中，VIEW后接创建的视图的名称，AS后接该视图存储的SELECT语句。
在创建过程中也可以自定义对应字段的名称
CREATE VIEW new_view(new_id,new_name) AS SELECT id,name FROM test;
视图在数据库中同样被归为表
SHOW TABLES;
2.更新数据
此处更新数据指更新视图内的数据，如增、删、改。
视图有点类似go语言内的切片，更改视图会导致其底层的表产生对应的更改。
UPDATE new_view SET name = 'Outer' WHERE id = 1;
DELETE FROM new_view WHERE id = 2;
同理，更新基表中的数据同样会影响视图内的数据。
通过聚合函数或多表联结获取的视图时不可以随意更改的，一般只能更改视图与基表一对一的数据。如通过AVG获取的视图的列，是不可以被更改的。
3.修改与删除
REPLACE语句可以对视图或表进行更改，可以通过CREATE OR REPLACE来实现os_create的操作。
CREATE OR REPLACE VIEW new_view AS SELECT id,name FROM test WHERE id > 10;
或者也可以直接使用ALTER语句
ALTER VIEW new_view AS SELECT id,name FROM test WHERE id > 10;
删除视图时使用DROP语句
DROP VIEW new_view;
DROP VIEW IF EXISTS new_view;
二.存储过程
存储过程类似其他编程语言中的void函数或方法。
存储过程的参数类型可以是IN、OUT、INOUT，主要分为以下几点：
1.没有参数（无参数无返回）
2.仅仅带IN类型（有参数无返回）
3.仅仅带OUT类型（无参数有返回）
4.既带IN又带OUT类型（有参数有返回）
5.带INOUT类型（有参数有返回）
1.创建
CREATE PROCEDURE ... (IN|OUT|INOUT ... ...)
BEGIN
......
END
其中，PROCEDURE后跟存储过程的名字，IN|OUT|INOUT后跟参数名和参数类型（默认为IN），BEGIN和END中间为存储过程的内容。
如：
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM test;
END $
DELIMITER ;
其中DILIMITER的作用是将;结束改为$结束，待PROCEDURE语句结束后，再从$改回;
创建带输出的存储过程
DELIMITER $
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
SELECT MIN(salary) INTO ms
FROM employees;
END $
DELIMITER ;
创建带有输入的存储过程
DELIMITER $
CREATE PROCEDURE show_name_salary(IN test_name VARCHAR(15))
BEGIN
SELECT id FROM employees WHERE employees.`name` = test_name;
END $
DELIMITER ;
创建带输入输出的存储过程同理，只需要设置形如(IN id INT,OUT name VARCHAR(15))，IN和OUT之间用逗号,隔开即可。
INOUT即输入输出时使用的是同一变量。
2.调用
使用CALL关键字来调用存储过程
CALL select_all_data();
如果是有返回值的存储过程，则需要设置返回的变量
CALL show_min_salary(@ms);
SELECT @ms;
如果是有传入值的存储过程，则需要输入传入的变量，可以直接传参，也可以定义一个变量，再将变量传入
CALL show_name_salary('Abel');
-- ----------------------------
SET @new_name = 'Abel';
CALL show_name_salary(@new_name);
其中SET即定义了一个new_name变量，内容为Abel。
由于存储过程和存储函数的相似性，存储过程的查看、修改和删除过程在下一部分与存储函数一同说明。
三.存储函数
存储函数即用户自定义的函数，其使用方法和系统函数一致。
1.创建
CREATE FUNCTION ... RETURNS ...
BEGIN
......
END
其中，FUNCTION后接存储函数的名字，RETURNS后接返回值的数据类型。
DELIMITER $
CREATE FUNCTION id_by_name()
RETURNS VARCHAR(15)
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
BEGIN
RETURN (SELECT id FROM test WHERE name = 'Outer');
END //
DELIMITER ;
其中，DETERMINISTIC、CONTAINS SQL、READS SQL DATA是设置一些函数的参数，若不想使用这些参数，可以使用
SET GLOBAL log_bin_trust_function_creators = 1;
2.调用
调用与系统函数的调用一致，如
SELECT id_by_name();
SELECT name_by_id(100);
3.查看、修改和删除
存储过程和存储函数的查看：
SHOW CREATE PROCEDURE ... ;
SHOW CREATE FUNCTION ... ;
或使用STATUS关键字
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS LIKE 'id_by_name';
也可以从information_schema.Routines内查看
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'id_by_name' AND ROUTINE_TYPE = 'FUNCTION';
修改则是修改存储过程或存储函数的限制，如
ALTER PROCEDURE id_by_name SQL SECURITY INVOKER COMMENT 'search_id_by_name';
常见的限制如下表：
| 存储限制 | 作用 |
|---|---|
| CONTAINS SQL | 子程序包含SQL语句，但不包含读或写数据的语句 |
| NO SQL | 子程序不包含SQL语句 |
| READS SQL DATA | 子程序包含可读数据的语句 |
| MODIFIES SQL DATA | 子程序包含可写数据的语句 |
| SQL SECURITY | 指明权限，DEFINER表示只有定义者自己才能执行该语句，INVOKER则调用者可以执行该语句 |
| COMMENT ‘string’ | 表示注释信息 |
删除则仍然是使用DROP语句
DROP PROCEDURE id_by_name;
DROP FUNCTION IF EXISTS id_by_name;
七.变量、语句与触发器
一.变量
变量分为系统变量和用户自定义变量。
1.系统变量
系统变量根据其作用范围可分为全局系统变量和会话系统变量
查看所有的系统变量：
SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;
SHOW VARIABLES;
GLOBAL为全局系统变量，SESSION为会话系统变量。
要想查看指定的系统变量，则使用SELECT语句
SELECT @@global.变量名;
SELECT @@session.变量名;
SELECT @@变量名;
其中@@前缀表示系统变量。
修改系统变量的操作：
SET @@global.变量名 = 变量值;
SET GLOBAL 变量名 = 变量值;
SET @@session.变量名 = 变量值;
SET SESSION 变量名 = 变量值;
一旦重启MySQL服务，则修改过的系统变量将会回复默认值。
2.用户变量
用户变量根据作用范围可分为会话用户变量和局部变量
其中，局部变量只在BEGIN和END语句块内有效，只能在存储过程和函数中使用。
用户变量使用@开头。
a.会话用户变量
变量的定义：
SET @用户变量 := 变量值;
SET @用户变量 = 变量值;
SELECT @用户变量 := 函数 FROM ...;
SELECT 函数 INTO @用户变量 FROM ...;
如：
SELECT @count := COUNT(*) FROM test;
SELECT COUNT(*) INTO @count FROM test;
b.局部变量
局部变量的顶用需要使用DECLARE关键字，且只在BEGIN与END之间有效。
DECLARE 变量名 类型 DEFAULT 缺省值;
默认缺省值为NULL。
如：
DELIMITER //
CREATE PROCEDURE test_var()
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE b INT;
DECLARE name VARCHAR(25);
SET a := 1;
SET b := 2;
SELECT employee_name INTO name FROM employees;
SELECT a,b,name;
END //
DELIMITER ;
二.定义条件与处理程序
定义条件和处理程序主要用于处理程序运行过程中产生的异常或错误。
1.定义条件
定义条件可以对指定的错误码进行命名。
DECLARE 错误名称 CONDITION FOR 错误码;
如，MySQL中违反非空约束的错误码为ERROR 1048(23000)，此处1048为错误码，23000为状态值 (STATE_VALUE)
则可以使用DECLARE进行修改。
既可以使用错误码，也可以使用状态码。
DECLARE Field_can_NOT_Be_NULL CONDITION FOR 1048;
DECLARE Field_can_NOT_Be_NULL CONDITION FOR SQLSTATE '23000';
2.处理程序
处理程序的常用语句为
DECLARE 处理方法 HANDLER FOR 错误类型 处理语句;
处理方法有三个取值：
| 处理方法 | 作用 |
|---|---|
| CONTINUE | 遇到错误时忽略错误，继续执行语句。 |
| EXIT | 遇到错误时退出程序 |
| UNDO | 遇到错误时撤回操作（MySQL不支持该操作） |
此外，错误类型包括如下内容：
| 错误类型 | 内容 |
|---|---|
| SQLSTATE | 长度为5的sqlstate_value类型的错误代码 |
| MYSQL_error_code | 匹配数值类型错误代码 |
| 自定义错误名称 | 用DECLARE操作自定义的错误名称 |
| SQLWARNING | 匹配所有以01开头的SQLSTATE错误代码 |
| NOT FOUND | 匹配所有以02开头的SQLSTATE错误代码 |
| SQLEXCEPTION | 匹配所以非01或02开头的错误代码 |
例如
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @flag = 1;
三.流程控制
1.分支语句
IF语句：
IF 表达式1 THEN 操作1
ELSEIF 表达式2 THEN 操作2 ...
ELSE 操作N
END IF
CASE语句：
CASE
WHEN 条件1 THEN 操作1
WHEN 条件2 THEN 操作2
...
ELSE 操作N
END CASE
若为BEGIN、END语句则需要在END后加CASE，若为SELECT语句则不需要。
2.循环语句
分析循环语句要着重分析其组成，常见的循环语句应包含四个内容：
- 初始条件
- 循环条件
- 循环体
- 迭代条件
LOOP语句：
add_loop:LOOP
......
END LOOP add_loop;
其中add_loop为自定义的LOOP名称。
如：
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id + 1;
IF id >= 10 THEN LEAVE add_loop;
END IF;
END LOOP add_loop;
其中，初始化条件为DECLARE id INT DEFAULT 0，循环条件为IF id >= 10 THEN LEAVE add_loop，迭代条件为SET id = id + 1，循环体也是SET id = id + 1。
WHILE语句：
add_while:WHILE 循环条件 DO
循环体
END WHILE add_while;
其中add_while为自定义的WHILE名称。
如：
DELIMITER //
CREATE PROCEDURE test_while()
BEGIN
DECLARE num INT DEFAULT 1;
WHILE num <= 10 DO
SET num = num + 1;
END WHILE;
END //
DELIMITER ;
其中，num<=10是循环条件，SET num = num + 1是迭代条件和循环体，DECLARE num INT DEFAULT 1是初始化条件。
REPEAT语句：
add_repeat:REPEAT
循环体
UNTIL 终止条件
END REPEAT add_repeat;
其中add_repeat为自定义的REPEAT名称。
如：
DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN
DECLARE num INT DEFAULT 1;
REPEAT
SET num = num + 1;
UNTIL num >= 10
END REPEAT;
END //
DELIMITER ;
注，UNTIL语句不可加分号，因为UNTIL ... END REPEAT是一体的语句。
3.跳转语句
LEAVE语句
LEAVE用于循环语句或BEGIN ... END程序体内，类似其他编程语言的break，但要在其后加上退出的程序体名称。
LEAVE 标记名;
若是要跳出BEGIN ... END程序体，则需要给BEGIN语句命名
DELIMITER //
CREATE PROCEDURE leave_test()
add_begin:BEGIN
LEAVE add_begin;
END //
DELIMITER ;
ITERATE语句
LEAVE用于循环语句或BEGIN ... END程序体内，类似其他编程语言的continue，但要在其后加上退出的程序体名称。
如：
DELIMITER //
CREATE PROCEDURE iterate_test()
BEGIN
DECLARE num INT DEFAULT 0;
loop_label:LOOP
SET num = num + 1;
IF num < 10 THEN ITERATE loop_label;
ELSEIF num > 15 THEN LEAVE loop_label;
END IF;
END LOOP;
END //
DELIMITER ;
四.游标
游标让SQL这种面向集合的语言有了面向过程开发的能力。其作用类似于其他编程语言中的GET。
游标的声明：
DECLARE cursor_name CURSOR FOR 查询语句;
这里的查询语句指的是SELECT语句，如：
DECLARE cursor_name CURSOR FOR SELECT employee_id,salary FROM employees;
接下来需要打开游标
OPEN cursor_name;
使用游标
FETCH cursor_name INTO 变量;
每次都将取一行的数据，并将其数据存入INTO后的变量。
关闭游标
CLOSE cursor_name;
五.触发器
触发器用于检测用户的操作，并实施对应的操作。
1.创建
创建触发器：
CREATE TRIGGER 触发器名称
BEFORE/AFTER 操作 ON 表名 FOR EACH ROW 触发器执行的语句;
如：
DELIMITER //
CREATE TRIGGER before_insert_test BEFORE INSERT ON test FOR EACH ROW
BEGIN
INSERT INTO test message VALUES('inserted');
END //
DELIMITER ;
在触发器的定义中，可以使用NEW关键字来代表新插入的数据。
DELIMITER //
CREATE TRIGGER before_insert_test BEFORE INSERT ON test FOR EACH ROW
BEGIN
DECLARE newdata INT;
SELECT salary INTO newdata FROM test WHERE id = NEW.id;
END //
DELIMITER ;
2.查看和删除
有三种查看触发器的方法。
SHOW TRIGGER;
SHOW CREATE TRIGGER before_insert_test;
SELECT * FROM information_schema.TRIGGERS;
删除触发器
DROP TRIGGER IF EXISTS 触发器名称;
