SQL中的数据库对象

视图：VIEW

1. 概念
   ① 虚拟表，本身不存储数据，可以看做是存储起来的SELECT语句
   ② 视图中SELECT语句中涉及到的表，称为基表
   ③ 针对视图做DML操作，对影响到基表中的数据，反之亦然
   ④ 创建、删除视图本身，不会影响到基表

2. 创建视图

   CREATE VIEW view_emp_avg_salary(dept_id, avg_salary)
   AS
   SELECT department_id, AVG(salary)
   FROM employees
   WHERE department_id IS NOT NULL
   GROUP BY department_id;
   

3. 查看视图：DESC 视图名;

4. 修改视图

   # 方式一
   CREATE OR REPLACE VIEW 视图名
   AS
   SELECT ...#方式二
   ALTER VIEW 视图名
   AS
   SELECT ...
   

5. 删除视图

   DROP VIEW 视图名;
   

索引：INDEX

用于提高查询性能

存储过程：PROCEDURE

用于完成一次完整的业务处理，没有返回值，但可以通过传出参数将多个值传给调用者

1. 创建
   ① 无参数无返回值

   # 查询所有员工信息
   DELIMITER $
   CREATE PROCEDURE select_all_emp()
   BEGINSELECT * FROM employees;
   END $
   DELIMITER;
   # 调用
   CALL select_all_emp();
   

   ② 无参数有返回值：OUT

   # 查询员工中的最低工资，并将最低工资输出到ms
   DELIMITER $
   CREATE PROCEDURE select_min_sal(OUT ms DECIMAL)
   BEGINSELECT MIN(salary) INTO msFROM employees;
   END $
   DELIMITER;
   # 调用
   CALL select_min_sal(@ms);
   # 查看参数结果
   SELECT @ms;
   

   ③ 有参数无返回值：IN

   # 查询指定姓名的员工工资
   DELIMITER $
   CREATE PROCEDURE show_salary_by_lastname(IN lastName VARCHAR(50))
   BEGINSELECT salaryFROM employeesWHERE last_name = lastName;
   END $
   DEIMITER;
   # 调用
   CALL show_salary_by_lastname('Popp');
   

   ④ 有参数有返回值：IN、OUT

   # 查询指定姓名的员工工资和部门，并从参数中返回出来
   DELIMITER $
   CREATE PROCEDURE show_salary_by_lastname2(IN lastName VARCHAR(50), OUT sal DECIMAL, OUT dept_id INT)
   BEGINSELECT salary,department_id INTO sal, dept_idFROM employeesWHERE last_name = lastName;
   END $
   DELIMITER;
   # 调用
   CALL show_salary_by_lastname2('Popp', @sal, @deptId);
   # 查询结果
   SELECT @sal, @deptId;
   

   ⑤ 有参数有返回值：INOUT

   # 查询指定员工的领导姓名，并从参数中返回出来
   DELIMITER $
   CREATE PROCEDURE show_manager_by_lastname(INOUT lastName VARCHAR(50))
   BEGINSELECT m.last_name INTO lastNameFROM employees e JOIN employees mON e.manager_id = m.employee_idWHERE e.last_name = lastName;
   END $
   DELIMITER;
   # 调用
   SET @lastname = 'Popp';
   CALL show_manager_by_lastname(@lastname);
   # 查询结果
   SELECT @lastname;

2. characteristics
   ① LANGUAGE SQL：表明存储过程执行体是由SQL语句构成的
   ② [NOT] DETERMINISTIC：存储过程的执行结果是否确定，默认为不确定
   ③ CONTAINS SQL：存储过程的执行体包含SQL语句，但不包含读写数据的SQL语句，默认是这个
   ④ NO SQL：存储过程的执行体不包含任何SQL语句
   ⑤ READS SQL DATA：存储过程的执行体包含读数据的SQL语句
   ⑥ MODIFIES SQL DATA：存储过程的执行体包含写数据的SQL语句
   ⑦ SQL SECURITY {DEFINER | INVOKER}：执行权限
   ⑧ COMMENT：备注信息
3. 查看创建信息：SHOW CREATE PRODECURE 存储过程名称;
4. 查看状态信息：SHOW PROCEDURE STATUS LIKE '存储过程名称';
5. 修改特性

   ALTER PROCEDURE '存储过程名称'
   SQL SECURITY INVOKER
   COMMENT '备注信息...';
   

6. 删除：DROP PROCEDURE '存储过程名称';

存储函数：FUNCTION

用于完成一次特定的计算，有一个返回值

# 查询指定部门的员工人数
DELIMITER $
CREATE FUNCTION select_count_by_dept(dept_id INT)
RETURNS INT
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
BEGINRETURN(SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END $
DELIMITER;
# 使用
SELECT select_count_by_dept(50);

触发器：TRIGGER

相当于事件监听器，当数据库发生特定事件时，触发器被触发，完成相应的处理

1. 创建

   代码格式：
   CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
BEGIN
代码块;
END;

   ① 案例一：向一张表中添加数据之前，同时向另一张表添加数据

   DELIMITER //
   CREATE TRIGGER test_before_trigger
   BEFORE INSERT ON table1
   FOR EACH ROW
   BEGININSERT INTO table2(name)VALUES ('before_name...');
   END //
   DELIMITER;
   # 测试
   INSERT INTO table1(age)
   VALUES(18);
   

   ② 案例二：在向employees表中添加员工信息之前，检验此员工的工资是否大于领导的工资，大于的话报错，否则添加成功（NEW代表添加的数据信息，OLD代表删除的数据信息）

   DELIMITER //
   CREATE TRIGGER verify_trigger
   BEFORE INSERT ON employees
   FOR EACH ROW
   BEGINDECLARE manager_sal DECIMAL DEFAULT 0.0;SELECT salary INTO manager_sal FROM employees WHERE employee_id = NEW.manager_id;IF NEW.salary > manager_sal# 抛出错误THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '添加失败：薪资高于领导工资';END IF;
   END //
   DELIMITER;
   

2. 查看
   ① 查看数据库中的所有触发器：SHOW TRIGGERS;
   ② 查看某个触发器的定义：SHOW CREATE TRIGGER 触发器名称;

3. 删除：DROP TRAGGER 触发器名称;

变量

1. 系统变量：以@@开头
   ① 查看全局系统变量：SHOW GLOBAL VARIABLES;
   ② 查看会话系统变量

   SHOW SESSION VARIABLES;
   SHOW VARIABLES;
   

   ③ 查看指定系统变量

   SELECT @@global.max_connections; # 查看服务器最大连接数
   SELECT @@global.character_set_client; # 查看字符集
   SELECT @@session.character_set_client; # 查看字符集
   SELECT @@session.pseudo_thread_id; # 查看当前会话的MySQL连接ID
   SELECT @@character_set_client; # 先查看会话的字符集，若没有再查看全局的字符集
   

   ④ 修改系统变量的值

   SET @@global.max_connections = 166; # 修改全局变量，方式一
   SET GLOBAL max_connection = 167; # 修改全局变量，方式二SET @@session.character_set_client = 'gbk'; # 修改会话变量，方式一
   SET SESSION character_set_client = 'gbk'; # 修改会话变量，方式二
   

2. 用户变量
   ① 会话用户变量：以@开头

   # 声明和初始化
   SET @a = 1;
   SET @b := 2;
   SELECT @avg_sal := AVG(salary) FROM employees; # 这种情况必须用:=
   SELECT COUNT(*) INTO @emp_total FROM employees;
   #使用
   SELECT @a, @b, @avg_sal, @emp_total;
   

   ② 局部变量
   说明：
   a、使用关键字：DECLARE
   b、只能在存储过程和存储函数中使用
   c、必须写在BEGIN...END的首行
   d、格式：DECLARE 变量名 类型 [DEFAULT 值] ，若不指明DEFAULT，则默认是NULL

   DELIMITER $
   CREATE PROCEDURE test()
   BEGIN# 声明局部变量DECLARE a INT DEFAULT 0;DECLARE b INT;DECLARE name VARCHAR(30);#赋值SET a = 5;SET b := 6;SELECT last_name INTO nameFROM employeesWHERE employee_id = 100;#使用SELECT a, b, name;
   END $
   DELIMITER;
   

定义条件与处理程序

1. 定义条件
   ① 格式：DECLARE 错误名称 CONDITION FOR 错误码;
   ② 目的：给错误码命名
   ③ 错误码分类：MySQL_error_code、sqlstate_value
2. 定义处理程序
   ① 格式：DECLARE 处理方式 HANDLER FOR 错误类型 处理语句;
   ② 处理方式分类：
   a、CONTINUE：遇到错误不处理，继续执行
   b、EXIT：遇到错误马上退出
   c、UNDO：遇到错误后撤回之前的操作
   ③ 错误类型分类
   a、SQLSTATE '字符串错误码'：字符串类型错误码，长度为5
   b、MySQL_error_code：数值类型错误码
   c、错误名称：定义条件时声明的名称
   d、SQLWARNING：匹配所有以01开头的错误码
   e、NOT FOUND：匹配所有以02开头的错误码
   f、SQLEXCEPTION：匹配没有被d、e匹配到的错误码
3. 案例

   DELIMITER $
   CREATE PROCEDURE UpdateTest()
   BEGIN# 因email是NOT NULL，所以执行第一个UPDATE会报错“1048 - Column 'email' cannot be null”# 在此声明错误处理DECLARE CONTINUE HANDLER FOR 1048 SET @message = 'aaa';SET @a = 1;UPDATE employees SET email = NULL WHERE employee_id = 100;SET @a = 2;UPDATE employees SET email = '[e-mail]' WHERE employee_id = 100;SET @a = 3;
   END $
   DELIMITER;# 调用
   CALL UpdateTest();
   # 查看变量
   SELECT @a, @message; // 3, aaa
   

流程控制

分支结构

1. IF

   DELIMITER //
   CREATE PROCEDURE testIf()
   BEGINDECLARE score DECIMAL(10,2) DEFAULT 85;IF score >= 90 THEN SELECT 'A';ELSEIF score >= 80 AND score < 90 THEN SELECT 'B';ELSEIF score >= 60 AND score < 80 THEN SELECT 'C';ELSE SELECT 'D';END IF;
   END //
   DELIMITER;CALL testIf();
   

2. CASE
   ① CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END CASE;

   DELIMITER //
   CREATE PROCEDURE testCase()
   BEGINDECLARE n INT DEFAULT 2;CASE nWHEN 1 THEN SELECT 'n = 1';WHEN 2 THEN SELECT 'n = 2';ELSE SELECT 'n = 9';END CASE;
   END //
   DELIMITER;
   

   ② CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END CASE;

   DELIMITER //
   CREATE PROCEDURE testCase()
   BEGINDECLARE n INT default 10;CASEWHEN n > 20 THEN SELECT 'n > 20';WHEN n >= 10 THEN SELECT 'n >= 10';ELSE SELECT 'n < 10';END CASE;
   END //
   DELIMITER;
   

循环结构

3. LOOP

   DELIMITER //
   CREATE PROCEDURE testLoop()
   BEGINDECLARE n INT DEFAULT 1; # n = 1# 开始循环aaa: LOOPIF n >= 8THEN LEAVE aaa;END IF;SET n = n + 1;END LOOP aaa;SELECT n;
   END //
   DELIMITER;
   

4. WHILE

   DELIMITER //
   CREATE PROCEDURE testWhile()
   BEGINDECLARE n INT DEFAULT 1;WHILE n < 10DOSET n = n + 1;END WHILE;SELECT n;
   END //
   DELIMITER;
   

5. REPEAT

   DELIMITER //
   CREATE PROCEDURE testRepeat()
   BEGINDECLARE n INT DEFAULT 1;REPEATSET n = n + 1;UNTIL n > 10END REPEAT;SELECT n;
   END //
   DELIMITER;
   

跳转语句

1. LEAVE
   ① 同BREAK，格式：LEAVE 循环名;
   ② 使用场景：循环体中、BEGIN...END中

   DELIMITER //
   CREATE PROCEDURE testLeave(IN num INT)
   label_test: BEGINIF num = 0 THEN LEAVE label_test;ELSEIF num < 5 THEN SELECT num;ELSE SELECT 'num >= 5';END IF;SELECT 'is end';
   END //
   DELIMITER;
   

2. ITERATE
   ① 同CONTINUE，格式：ITERATE 循环名;
   ② 使用场景：循环体中

   DELIMITER //
   CREATE PROCEDURE test_iterate()
   BEGINDECLARE num  INT DEFAULT 0;iterate_test: LOOPSET num = num + 1;IF num > 5 THEN LEAVE iterate_test;ELSEIF num > 2 THEN ITERATE iterate_test;ELSE SELECT 'is end';END IF;END LOOP;SELECT num;
   END //
   DELIMITER;
   

游标

随意的定位到某一条记录，可以逐条读取数据。在使用游标过程中，会对数据加锁，不适合高并发场景。

DELIMITER //
CREATE PROCEDURE test_cursor(IN limit_total_salary DECIMAL, OUT total_count INT)
BEGIN# 声明变量DECLARE sum_sal DECIMAL DEFAULT 0.0;DECLARE emp_count INT DEFAULT 0;DECLARE emp_sal DECIMAL;# 声明游标DECLARE cursor_test CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;# 打开游标OPEN cursor_test;# 使用游标WHILE sum_sal < limit_total_salaryDOFETCH cursor_test INTO emp_sal;SET sum_sal = sum_sal + emp_sal;SET emp_count = emp_count + 1;END WHILE;SET total_count = emp_count;# 关闭游标CLOSE cursor_test;
END //
DELIMITER;
# 调用
CALL test_cursor(100000, @count);
SELECT @count;