文章目录
视图
数据库对象
视图的理解
① 视图，可以看做是一个虚拟表，本身是不存储数据的。
视图的本质，就可以看做是存储起来的SELECT语句
② 视图中SELECT语句中涉及到的表，称为基表
③ 针对视图做DML操作，会影响到对应的基表中的数据。反之亦然。
④ 视图本身的删除，不会导致基表中数据的删除。
⑤ 视图的应用场景：针对于小型项目，不推荐使用视图。针对于大型项目，可以考虑使用视图。
⑥ 视图的优点：简化查询；控制数据的访问。
创建、查看、更新、删除
========================创建视图========================
#针对单表情况1：视图中的字段与基表的字段有对应关系
CREATE VIEW vu_emp1 AS
SELECT employee_id,last_name,salary FROM emps;
#确定视图中字段名的方式1：
CREATE VIEW vu_emp2 AS
SELECT employee_id emp_id,last_name lname,salary #查询语句中字段的别名会作为视图中字段的名称出现
FROM emps
WHERE salary > 8000;
#确定视图中字段名的方式2：
CREATE VIEW vu_emp3(emp_id,NAME,monthly_sal) #小括号内字段个数与SELECT中字段个数相同
AS
SELECT employee_id,last_name,salary
FROM emps
WHERE salary > 8000;
#情况2：视图中的字段在基表中可能没有对应的字段
CREATE VIEW vu_emp_sal AS
SELECT department_id,AVG(salary) avg_sal FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;
#针对多表情况：
CREATE VIEW vu_emp_dept AS
SELECT e.employee_id,e.department_id,d.department_name
FROM emps e JOIN depts d
ON e.`department_id` = d.`department_id`;
========================利用视图对数据进行格式化========================
CREATE VIEW vu_emp_dept1 AS
SELECT CONCAT(e.last_name,'(',d.department_name,')') emp_info
FROM emps e JOIN depts d
ON e.`department_id` = d.`department_id`;
========================基于视图创建视图========================
CREATE VIEW vu_emp4 AS SELECT employee_id,last_name FROM vu_emp1;
========================查看视图========================
# 语法1：查看数据库的表对象、视图对象
SHOW TABLES;
#语法2：查看视图的结构
DESCRIBE vu_emp1;
#语法3：查看视图的属性信息
SHOW TABLE STATUS LIKE 'vu_emp1';
#语法4：查看视图的详细定义信息
SHOW CREATE VIEW vu_emp1;
========================“更新”删除视图数据========================
#更新视图的数据，会导致基表中数据的修改
UPDATE vu_emp1 SET salary = 20000 WHERE employee_id = 101;
#同理，更新表中的数据，也会导致视图中的数据的修改
UPDATE emps SET salary = 10000 WHERE employee_id = 101;
#删除视图中的数据，也会导致表中的数据的删除
DELETE FROM vu_emp1 WHERE employee_id = 101;
#不能更新视图中的数据的情况
1.avg_sal在基表中不存在，是聚合函数计算出来的
UPDATE vu_emp_sal SET avg_sal = 5000 WHERE department_id = 30;
2.视图的某个字在基表中不存在，也不支持删除
#修改视图
#方式1
CREATE OR REPLACE VIEW vu_emp1 AS
SELECT employee_id,last_name,salary,email
FROM emps WHERE salary > 7000;
#方式2
ALTER VIEW vu_emp1(eid,salary,email,hd) AS
SELECT employee_id,last_name,salary,email,hire_date FROM emps;
# 删除视图
DROP VIEW vu_emp4;
DROP VIEW IF EXISTS vu_emp2,vu_emp3;
不可更新的视图
存储过程和存储函数
概述
分类
参数类型：IN、OUT、INOUT。在一个存储过程中可以带多个。
- 没有参数（无参数无返回）
- 仅仅带IN类型（有参数无返回）
- 仅仅带OUT类型（无参数有返回）
- 既带IN又带OUT（有参数有返）
- 带INOUT(有参数有返回）
存储过程的创建和调用
# 语法格式
creatr procedure 存储过程名(IN|OUT|INOUT 参数名 参数类型，…)
[characteristics …]
begin
存储过程体
end
====================无参数无返回值====================
#举例1：创建存储过程select_all_data()，查看 employees 表的所有数据
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM employees;
END $
DELIMITER ;
#存储过程无参数无返回值的调用
CALL select_all_data();
#举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资
DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGIN
SELECT AVG(salary) FROM employees;
END //
DELIMITER ;
#存储过程无参数无返回值的调用
CALL avg_employee_salary();
====================带OUT返回值====================
#举例3：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
SELECT MIN(salary) INTO ms FROM employees;
END //
#存储过程无参数有返回值的调用
CALL show_min_salary(@ms);
#查看变量值
SELECT @ms;
====================带IN参数====================
#举例4：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名。
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
SELECT salary FROM employees WHERE last_name = empname;
END //
#存储过程有参数无返回值的调用
#调用方式1
CALL show_someone_salary('Abel');
#调用方式2 := 赋值符号
SET @empname := 'Abel';
CALL show_someone_salary(@empname);
====================带IN参数带OUT返回值====================
#举例5：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，
#并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。
DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
BEGIN
SELECT salary INTO empsalary FROM employees WHERE last_name = empname;
END //
#调用
SET @empname = 'Abel';
CALL show_someone_salary2(@empname,@empsalary);
SELECT @empsalary;
====================带INOUT有参数有返回值====================
#举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名。
DELIMITER $
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
SELECT last_name INTO empname FROM employees
WHERE employee_id = (
SELECT manager_id
FROM employees
WHERE last_name = empname
);
END $
#调用
SET @empname := 'Abel';
CALL show_mgr_name(@empname);
SELECT @empname;
存储函数的创建和调用
# 语法格式
creatr function 函数名(参数名 参数类型，…)
returns 返回值类型
[characteristics …]
begin
函数体 #函数体中一定要有 return 语句
end
#创建函数前执行此语句，函数未声明characteristics时保证函数的创建会成功， 不检验characteristics
SET GLOBAL log_bin_trust_function_creators = 1;
====================无参数====================
# 举例1：创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型。
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
BEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
#调用
SELECT email_by_name();
====================有参数====================
#举例2：创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回，数据类型为字符串型。
DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGIN
RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //
#调用
#方式1
SELECT email_by_id(101);
#方式2
SET @emp_id := 102;
SELECT email_by_id(@emp_id);
#举例3：创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。
DELIMITER //
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
BEGIN
RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END //
#调用
SET @dept_id := 50;
SELECT count_by_id(@dept_id);
存储过程和存储函数的对比
存储过程和存储函数的查看、修改、删除
#方式1. 使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW CREATE PROCEDURE show_mgr_name;
SHOW CREATE FUNCTION count_by_id;
#方式2. 使用SHOW STATUS语句查看存储过程和函数的状态信息
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE 'show_max_salary';
SHOW FUNCTION STATUS LIKE 'email_by_id';
#方式3.从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='email_by_id' AND ROUTINE_TYPE = 'FUNCTION';
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='show_min_salary' AND ROUTINE_TYPE = 'PROCEDURE';
#4.存储过程、存储函数的修改（只能修改相关特性，不影响存储过程或函数功能）
ALTER PROCEDURE show_max_salary SQL SECURITY INVOKER COMMENT '查询最高工资';
#5. 存储过程、存储函数的删除
DROP FUNCTION IF EXISTS count_by_id;
DROP PROCEDURE IF EXISTS show_min_salary;
变量
GLOBAL 与 SESSION 变量的使用
====================查询====================
#查询全局系统变量
SHOW GLOBAL VARIABLES; #617
#查询会话系统变量
SHOW SESSION VARIABLES; #640
或
SHOW VARIABLES; #默认查询的是会话系统变量
#查询部分系统变量
SHOW GLOBAL VARIABLES LIKE 'admin_%';
#查询部分会话变量
SHOW VARIABLES LIKE 'character_%';
#查看指定系统变量
SELECT @@global.max_connections;
SELECT @@global.character_set_client;
SELECT @@character_set_client; #先查询会话系统变量，再查询全局系统变量
====================修改====================
#修改系统变量的值
#全局系统变量：
#方式1：
SET @@global.max_connections = 161;
#方式2：
SET GLOBAL max_connections = 171;
#针对于当前的数据库实例是有效的，一旦重启mysql服务，就失效了。
#会话系统变量：
#方式1：
SET @@session.character_set_client = 'gbk';
#方式2：
SET SESSION character_set_client = 'gbk';
#针对于当前会话是有效的，一旦结束会话，重新建立起新的会话，就失效了。
会话用户变量和局部变量的使用
- 会话用户变量：作用域和会话变量一样，只对
当前连接会话有效。 - 局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在
存储过程和函数中使用。
====================会话用户变量的声明和赋值====================
#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;
#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];
SELECT @变量名
====================局部变量的声明和赋值====================
#定义:可以使用 DECLARE 语句定义一个局部变量
#作用域:仅仅在定义它的 BEGIN ... END 中有效
#位置:只能放在 BEGIN ... END 中，而且只能放在第一句
BEGIN
#声明局部变量
DECLARE 变量名1 变量数据类型[DEFAULT 变量默认值];
DECLARE 变量名2,变量名3,...变量数据类型 [DEFAULT 变量默认值];
#为局部变量赋值
SET 变量名1 =值;
SELECT 值 INTO 变量名2 [FROM 子句];
#查看局部变量的值
SELECT 变量1,变量2,变量3;
END
====================会话用户变量的声明和赋值和使用====================
#方式1：
SET @m1 = 1;
SET @m2 := 2;
SET @sum := @m1 + @m2;
SELECT @sum;
#方式2：
SELECT @count := COUNT(*) FROM employees;
SELECT @count;
SELECT AVG(salary) INTO @avg_sal FROM employees;
SELECT @avg_sal;
====================局部变量的声明和赋值和使用====================
# 举例1
DELIMITER //
CREATE PROCEDURE test_var()
BEGIN
#1、声明局部变量
DECLARE a INT DEFAULT 0;
DECLARE b INT ;
#DECLARE a,b INT DEFAULT 0;合并声明
DECLARE emp_name VARCHAR(25);
#2、赋值
SET a = 1;
SET b := 2;
SELECT last_name INTO emp_name FROM employees WHERE employee_id = 101;
#3、使用
SELECT a,b,emp_name;
END //
#调用存储过程
CALL test_var();
#举例2：声明局部变量，并分别赋值为employees表中employee_id为102的last_name和salary
DELIMITER //
CREATE PROCEDURE test_pro()
BEGIN
#声明
DECLARE emp_name VARCHAR(25);
DECLARE sal DOUBLE(10,2) DEFAULT 0.0;
#赋值
SELECT last_name,salary INTO emp_name,sal FROM employees WHERE employee_id = 102;
#使用
SELECT emp_name,sal;
END //
#调用存储过程
CALL test_pro();
定义条件与处理程序
定义条件 是事先定义程序执行过程中可能遇到的问题，处理程序 定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。
====================定义条件====================
#格式：
DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）
#案例1
#方式1：使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
#方式2：使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';
处理方式有3个取值：CONTINUE、EXIT、UNDO
- CONTINUE：表示遇到错误不处理，继续执行。
- EXIT:表示遇到错误马上退出。
- UNDO:表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
错误类型(即条件)可以有如下取值：
- SQLSTATE ‘字符串错误码’ ：表示长度为5的sqlstate_value类型的错误代码;
- MySQL_error_code：匹配数值类型错误代码;
- 错误名称：表示DECLARE… CONDITION定义的错误条件名称。
- SQLWARNING：匹配所有以01开头的SQLSTATE错误代码;
- NOT FOUND：匹配所有以02开头的SQLSTATE错误代码;
- SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码;
====================定义处理程序====================
#格式：
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
#举例：
#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR no_such_table SET @info = 'NO_SUCH_TABLE';
#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
#定义存储过程，体现错误的处理程序-1
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
#声明处理程序
#处理方式1：
DECLARE CONTINUE HANDLER FOR 1048 SET @prc_value = -1;
#处理方式2：
#DECLARE CONTINUE HANDLER FOR sqlstate '23000' SET @prc_value = -1;
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END //
#调用存储过程：
CALL UpdateDataNoCondition();
#查看变量：
SELECT @x,@prc_value;
#定义存储过程，体现错误的处理程序-2
DELIMITER //
CREATE PROCEDURE InsertDataWithCondition()
BEGIN
#处理程序
#方式1：
#declare exit handler for 1062 set @pro_value = -1;
#方式2：
#declare exit handler for sqlstate '23000' set @pro_value = -1;
#方式3：
#定义条件
DECLARE duplicate_entry CONDITION FOR 1062;
DECLARE EXIT HANDLER FOR duplicate_entry SET @pro_value = -1;
SET @x = 1;
INSERT INTO departments(department_name) VALUES('测试');
SET @x = 2;
INSERT INTO departments(department_name) VALUES('测试');
SET @x = 3;
END //
#调用
CALL InsertDataWithCondition();
SELECT @x,@pro_value;
流程控制
条件判断语句： IF语句和CASE语句
====================IF语句====================
DELIMITER //
CREATE PROCEDURE test_if()
BEGIN
#情况1：
#声明局部变量
#declare stu_name varchar(15);
#if stu_name is null
# then select 'stu_name is null';
#end if;
#情况2：二选一
#declare email varchar(25) default 'aaa';
#if email is null
# then select 'email is null';
#else
# select 'email is not null';
#end if;
#情况3：多选一
DECLARE age INT DEFAULT 20;
IF age > 40
THEN SELECT '中老年';
ELSEIF age > 18
THEN SELECT '青壮年';
ELSEIF age > 8
THEN SELECT '青少年';
ELSE
SELECT '婴幼儿';
END IF;
END //
#调用
CALL test_if();
====================CASE语句====================
DELIMITER //
CREATE PROCEDURE test_case()
BEGIN
#演示1：case ... when ...then ...
/*
declare var int default 2;
case var
when 1 then select 'var = 1';
when 2 then select 'var = 2';
when 3 then select 'var = 3';
else select 'other value';
end case;
*/
#演示2：case when ... then ....
DECLARE var1 INT DEFAULT 10;
CASE
WHEN var1 >= 100 THEN SELECT '三位数';
WHEN var1 >= 10 THEN SELECT '两位数';
ELSE SELECT '个数位';
END CASE;
END //
#调用
CALL test_case();
循环语句：LOOP、WHILE和REPEAT语句
====================LOOP语句 - 先判断条件是否满足后执行循环体====================
#语法格式
[loop_label:] LO0P
循环执行的语句
END LOOP [loop_label]
#举例1：
DELIMITER //
CREATE PROCEDURE test_loop()
BEGIN
#声明局部变量
DECLARE num INT DEFAULT 1;
loop_label:LOOP
#重新赋值
SET num = num + 1;
#可以考虑某个代码程序反复执行。（略）
IF num >= 10 THEN LEAVE loop_label;
END IF;
END LOOP loop_label;
#查看num
SELECT num;
END //
#调用
CALL test_loop();
#举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。
#声明存储过程“update_salary_loop()”，声明OUT参数num，输出循环次数。
#存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平
#均薪资达到12000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
#声明变量
DECLARE avg_sal DOUBLE ; #记录员工的平均工资
DECLARE loop_count INT DEFAULT 0;#记录循环的次数
#① 初始化条件
#获取员工的平均工资
SELECT AVG(salary) INTO avg_sal FROM employees;
loop_lab:LOOP
#② 循环条件
#结束循环的条件
IF avg_sal >= 12000
THEN LEAVE loop_lab;
END IF;
#③ 循环体
#如果低于12000，更新员工的工资
UPDATE employees SET salary = salary * 1.1;
#④ 迭代条件
#更新avg_sal变量的值
SELECT AVG(salary) INTO avg_sal FROM employees;
#记录循环次数
SET loop_count = loop_count + 1;
END LOOP loop_lab;
#给num赋值
SET num = loop_count;
END //
CALL update_salary_loop(@num);
SELECT @num;
====================WHILE语句 - 先判断条件是否满足后执行循环体====================
#语法格式
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];
#举例1：
DELIMITER //
CREATE PROCEDURE test_while()
BEGIN
#初始化条件
DECLARE num INT DEFAULT 1;
#循环条件
WHILE num <= 10 DO
#循环体（略）
#迭代条件
SET num = num + 1;
END WHILE;
#查询
SELECT num;
END //
#调用
CALL test_while();
#举例2：市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。
#声明存储过程“update_salary_while()”，声明OUT参数num，输出循环次数。
#存储过程中实现循环给大家降薪，薪资降为原来的90%。直到全公司的平均薪资
#达到5000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
#声明变量
DECLARE avg_sal DOUBLE ; #记录平均工资
DECLARE while_count INT DEFAULT 0; #记录循环次数
#赋值
SELECT AVG(salary) INTO avg_sal FROM employees;
WHILE avg_sal > 5000 DO
UPDATE employees SET salary = salary * 0.9 ;
SET while_count = while_count + 1;
SELECT AVG(salary) INTO avg_sal FROM employees;
END WHILE;
#给num赋值
SET num = while_count;
END //
#调用
CALL update_salary_while(@num);
SELECT @num;
====================REPEAT语句 - 先执行循环体后判断条件是否满足====================
#语法格式
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]
#举例1：
DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN
#声明变量
DECLARE num INT DEFAULT 1;
REPEAT
SET num = num + 1;
UNTIL num >= 10
END REPEAT;
#查看
SELECT num;
END //
#调用
CALL test_repeat();
#举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。
#声明存储过程“update_salary_repeat()”，声明OUT参数num，输出循环次数。
#存储过程中实现循环给大家涨薪，薪资涨为原来的1.15倍。直到全公司的平均
#薪资达到13000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGIN
#声明变量
DECLARE avg_sal DOUBLE ; #记录平均工资
DECLARE repeat_count INT DEFAULT 0; #记录循环次数
#赋值
SELECT AVG(salary) INTO avg_sal FROM employees;
REPEAT
UPDATE employees SET salary = salary * 1.15;
SET repeat_count = repeat_count + 1;
SELECT AVG(salary) INTO avg_sal FROM employees;
UNTIL avg_sal >= 13000
END REPEAT;
#给num赋值
SET num = repeat_count;
END //
#调用
CALL update_salary_repeat(@num);
SELECT @num;
跳转语句：ITERATE和LEAVE语句
LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。可以把 LEAVE 理解为 break。
ITERATE语句：只能用在循环语句(LOOP、REPEAT和WHILE语句)内，表示重新开始循环，将执行顺序转到语句段开头处。可以把ITERATE 理解为 continue，意思为“再次循环”。
====================LEAVE语句==================
#举例1：创建存储过程 “leave_begin()”，声明INT类型的IN参数num。给BEGIN...END加标记名，并在BEGIN...END中使用IF语句判断num参数的值。
DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
begin_label:BEGIN
IF num <= 0
THEN LEAVE begin_label;
ELSEIF num = 1
THEN SELECT AVG(salary) FROM employees;
ELSEIF num = 2
THEN SELECT MIN(salary) FROM employees;
ELSE
SELECT MAX(salary) FROM employees;
END IF;
#查询总人数
SELECT COUNT(*) FROM employees;
END //
#调用
CALL leave_begin(1);
#举例2：当市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。
#声明存储过程“leave_while()”，声明OUT参数num，输出循环次数，存储过程中使用WHILE
#循环给大家降低薪资为原来薪资的90%，直到全公司的平均薪资小于等于10000，并统计循环次数。
DELIMITER //
CREATE PROCEDURE leave_while(OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE;#记录平均工资
DECLARE while_count INT DEFAULT 0; #记录循环次数
SELECT AVG(salary) INTO avg_sal FROM employees; #① 初始化条件
while_label:WHILE TRUE DO #② 循环条件
#③ 循环体
IF avg_sal <= 10000 THEN
LEAVE while_label;
END IF;
UPDATE employees SET salary = salary * 0.9;
SET while_count = while_count + 1;
#④ 迭代条件
SELECT AVG(salary) INTO avg_sal FROM employees;
END WHILE;
#赋值
SET num = while_count;
END //
#调用
CALL leave_while(@num);
SELECT @num;
====================ITERATE语句==================
#举例： 定义局部变量num，初始值为0。循环结构中执行num + 1操作。
DELIMITER //
CREATE PROCEDURE test_iterate()
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;
SELECT 'XXX';
END LOOP;
END //
CALL test_iterate();
游标
在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里 游标 充当了指针的作用，我们可以通过操作游标来对数据行进行操作。
#在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下，
DECLARE cursor_name CURSOR FOR select_statement;
#这个语法适用于 MySQL，SQL Server，DB2和 MariaDB。如果是用 Oracle 或者 PostgreSQL，需要写成:
DECLARE cursor_name CURSOR IS select_statement,
#要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，这里 select_statement 代表的是 SELECT 语句，返回一个用于创建游标的结果集。
#打开游标
OPEN cursor_name;
#当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的 逐条读取 结果集中的记录做准备。
#使用游标
FETCH cursor_name INTO var_name [,var_name] …
#这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。
FETCH cur_emp INTO emp_id,emp_sal;
#游标的查询结果集中的字段数，，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时候，MySQL会提示错误。
#关闭游标
CLOSE cursor_name
#有 OPEN 就会有 CLOSE，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。因为游标会 占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。而关闭游标的操作，会释放游标占用的系统资源。
#关闭游标之后，我们就不能再检索查询结果中的数据行，如果需要检索只能再次打开游标。
#举例：创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，
#DOUBLE类型；声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，
#直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。
DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
#声明局部变量
DECLARE sum_sal DOUBLE DEFAULT 0.0; #记录累加的工资总额
DECLARE emp_sal DOUBLE; #记录每一个员工的工资
DECLARE emp_count INT DEFAULT 0;#记录累加的人数
#1.声明游标
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
#2.打开游标
OPEN emp_cursor;
REPEAT
#3.使用游标
FETCH emp_cursor INTO emp_sal;
SET sum_sal = sum_sal + emp_sal;
SET emp_count = emp_count + 1;
UNTIL sum_sal >= limit_total_salary
END REPEAT;
SET total_count = emp_count;
#4.关闭游标
CLOSE emp_cursor;
END //
DELIMITER ;
#调用
CALL get_count_by_limit_total_salary(200000,@total_count);
SELECT @total_count;
#PS：游标是 MySQL 的一个重要的功能，为 逐条读取 结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
#但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候,不仅会影响业务之间的效率，还会 消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。建议:养成用完之后就关闭的习惯，这样才能提高系统的整体效率。
触发器
创建
在实际开发中，我们经常会遇到这样的情况：有2个或者多个相互关联的表，如 商品信息和 库存信息分别存放在2个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录。
这样一来，我们就必须把这两个关联的操作步骤写到程序里面，而且要用 事务 包裹起来，确保这两个操作成为一个原子操作，要么全部执行，要么全部不执行。要是遇到特殊情况，可能还需要对数据进行手动维护，这样就很容易忘记其中的一步 ，导致数据缺失。
这个时候，咱们可以使用触发器。你可以创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了。
触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、 DELETE事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动 激发触发器执行相应的操作。当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。
#语法结构
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;
表名：表示触发器监控的对象。
BEFORE|AFTER：表示触发的时间。BEFORE 表示在事件之前触发;AFTER 表示在事件之后触发,
INSERT|UPDATE|DELETE：表示触发的事件。
。INSERT 表示插入记录时触发;
。UPDATE 表示更新记录时触发，
。 DELETE 表示删除记录时触发。
触发器执行的语句块：可以是单条SQL语句，也可以是由BEGIN...END结构组成的复合语句块。
#举例1：
#创建名称为before_insert_test_tri的触发器，向test_trigger数据表插入数据之前，
#向test_trigger_log数据表中插入before_insert的日志信息。
DELIMITER $
CREATE TRIGGER before_insert_test_tri
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log(t_log)
VALUES('before insert...');
END $
DELIMITER ;
#④ 测试
INSERT INTO test_trigger(t_note)
VALUES('Tom...');
SELECT * FROM test_trigger;
SELECT * FROM test_trigger_log;
#举例2：
#创建触发器emps_del_trigger，每当向emps表中删除一条记录时，同步将删除的这条记录添加到emps_back1表中
DELIMITER //
CREATE TRIGGER emps_del_trigger
BEFORE DELETE ON emps
FOR EACH ROW
BEGIN
#将emps表中删除的记录，添加到emps_back1表中。OLD表示被删除的对象的数据
INSERT INTO emps_back1(employee_id,last_name,salary)
VALUES(OLD.employee_id,OLD.last_name,OLD.salary);
END //
DELIMITER ;
#验证触发器是否起作用
DELETE FROM emps WHERE employee_id = 101;
DELETE FROM emps;
SELECT * FROM emps;
SELECT * FROM emps_back1;
#举例3：
#定义触发器“salary_check_trigger”，基于员工表“employees”的INSERT事件，
#在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，
#则报sqlstate_value为'HY000'的错误，从而使得添加失败。
DELIMITER //
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
#查询到要添加的数据的manager的薪资
DECLARE mgr_sal DOUBLE;
#NEW表示新添加的对象的数据
SELECT salary INTO mgr_sal FROM employees WHERE employee_id = NEW.manager_id;
IF NEW.salary > mgr_sal
THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
END IF;
END //
DELIMITER ;
#测试
DESC employees;
#添加成功：依然触发了触发器salary_check_trigger的执行，没有进if
INSERT INTO employees(employee_id,last_name,email,hire_date,job_id,salary,manager_id)
VALUES(300,'Tom','tom@126.com',CURDATE(),'AD_VP',8000,103);
#添加失败：依然触发了触发器salary_check_trigger的执行，进了if，数据没有添加成功
INSERT INTO employees(employee_id,last_name,email,hire_date,job_id,salary,manager_id)
VALUES(301,'Tom1','tom1@126.com',CURDATE(),'AD_VP',10000,103);
查看、删除
====================查看==================
#① 查看当前数据库的所有触发器的定义
SHOW TRIGGERS;
#② 查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER salary_check_trigger;
#③ 从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS;
====================删除==================
DROP TRIGGER IF EXISTS after_insert_test_tri;
优缺点
优点
1.触发器可以确保数据的完整性
2.触发器可以记录操作日志
3.触发器还可以用在操作数据前，对数据进行合法性检查
缺点
1.可读性差
2.相关数据的变更，可能会导致触发器出差
注意点
注意，如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。
例如：基于子表员工表(t_employee)的DELETE语句定义了触发器t1，而子表的部门编号(did)字段定义了外键约束引用了父表部门表(t_department)的主键列部门编号(did)，并且该外键加了“ON DELETE SET NULL”子句，那么如果此时删除父表部门表(t_department)在子表员工表(t_employee)有匹配记录的部门记录时，会引起子表员工表(t_employee)匹配记录的部门编号(did)修改为NULL，但是此时不会激活触发器t1。只有直接对子表员工表(t_employee)执行DELETE语句时才会激活触发器t1。