代码拉取完成，页面将自动刷新
同步操作将从 turnon/blog 强制同步，此操作会覆盖自 Fork 仓库以来所做的任何修改，且无法恢复！！！
确定后同步将在后台操作，完成时将刷新页面，请耐心等待。
title: SQL 语法高级特性
date: 2022-04-27 22:13:55
categories:
- 数据库
- 关系型数据库
- 综合
tags:
- 数据库
- 关系型数据库
- SQL
permalink: /pages/1ae1ca/
本文针对关系型数据库的基本语法。限于篇幅，本文侧重说明用法，不会展开讲解特性、原理。
本文语法主要针对 Mysql，但大部分的语法对其他关系型数据库也适用。
连接用于连接多个表，使用
JOIN关键字，并且条件语句使用ON而不是WHERE。
如果一个 JOIN 至少有一个公共字段并且它们之间存在关系，则该 JOIN 可以在两个或多个表上工作。
JOIN 保持基表（结构和数据）不变。连接可以替换子查询，并且比子查询的效率一般会更快。
JOIN 有两种连接类型：内连接和外连接。
内连接又称等值连接，使用 INNER JOIN 关键字。在没有条件语句的情况下返回笛卡尔积。
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
=）自连接可以看成内连接的一种，只是连接的表是自身而已。自然连接是把同名列通过 = 连接起来的，同名列可以有多个。
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
内连接提供连接的列，而自然连接自动连接所有同名列。自然连接使用 NATURAL JOIN 关键字。
SELECT *
FROM Products
NATURAL JOIN Customers;
外连接返回一个表中的所有行，并且仅返回来自此表中满足连接条件的那些行，即两个表中的列是相等的。外连接分为左外连接、右外连接、全外连接（Mysql 不支持）。
左外连接就是保留左表没有关联的行。
SELECT customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;
右外连接就是保留右表没有关联的行。
SELECT customers.cust_id, orders.order_num
FROM customers RIGHT JOIN orders
ON customers.cust_id = orders.cust_id;
UNION运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自UNION中参与查询的提取行。
UNION 基本规则：
默认会去除相同行，如果需要保留相同行，使用 UNION ALL。
只能包含一个 ORDER BY 子句，并且必须位于语句的最后。
应用场景：
组合查询示例：
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4All';
JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。🔔 注意：不同数据库的函数往往各不相同，因此不可移植。本节主要以 Mysql 的函数为例。
| 函数 | 说明 |
|---|---|
LEFT()、RIGHT()
|
左边或者右边的字符 |
LOWER()、UPPER()
|
转换为小写或者大写 |
LTRIM()、RTIM()
|
去除左边或者右边的空格 |
LENGTH() |
长度 |
SOUNDEX() |
转换为语音值 |
其中， SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。
SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')
YYYY-MM-DD
HH:MM:SS
| 函 数 | 说 明 |
|---|---|
AddDate() |
增加一个日期（天、周等） |
AddTime() |
增加一个时间（时、分等） |
CurDate() |
返回当前日期 |
CurTime() |
返回当前时间 |
Date() |
返回日期时间的日期部分 |
DateDiff() |
计算两个日期之差 |
Date_Add() |
高度灵活的日期运算函数 |
Date_Format() |
返回一个格式化的日期或时间串 |
Day() |
返回一个日期的天数部分 |
DayOfWeek() |
对于一个日期，返回对应的星期几 |
Hour() |
返回一个时间的小时部分 |
Minute() |
返回一个时间的分钟部分 |
Month() |
返回一个日期的月份部分 |
Now() |
返回当前日期和时间 |
Second() |
返回一个时间的秒部分 |
Time() |
返回一个日期时间的时间部分 |
Year() |
返回一个日期的年份部分 |
mysql> SELECT NOW();
2018-4-14 20:25:11
| 函数 | 说明 |
|---|---|
| SIN() | 正弦 |
| COS() | 余弦 |
| TAN() | 正切 |
| ABS() | 绝对值 |
| SQRT() | 平方根 |
| MOD() | 余数 |
| EXP() | 指数 |
| PI() | 圆周率 |
| RAND() | 随机数 |
| 函 数 | 说 明 |
|---|---|
AVG() |
返回某列的平均值 |
COUNT() |
返回某列的行数 |
MAX() |
返回某列的最大值 |
MIN() |
返回某列的最小值 |
SUM() |
返回某列值之和 |
AVG() 会忽略 NULL 行。
使用 DISTINCT 可以让汇总函数值汇总不同的值。
SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable
GROUP BY子句将记录分组到汇总行中，GROUP BY为每个组返回一个记录。
GROUP BY 可以按一列或多列进行分组。
GROUP BY 通常还涉及聚合函数：COUNT，MAX，SUM，AVG 等。
GROUP BY 按分组字段进行排序后，ORDER BY 可以以汇总字段来进行排序。
分组示例：
SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name;
分组后排序示例：
SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;
HAVING用于对汇总的GROUP BY结果进行过滤。HAVING要求存在一个GROUP BY子句。
WHERE 和 HAVING 可以在相同的查询中。
HAVING vs WHERE：
WHERE 和 HAVING 都是用于过滤。HAVING 适用于汇总的组记录；而 WHERE 适用于单个记录。使用 WHERE 和 HAVING 过滤数据示例：
SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;
（以下为 DDL 语句用法）
不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。
MySQL 默认采用隐式提交策略（autocommit），每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。
通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。
事务处理指令：
START TRANSACTION - 指令用于标记事务的起始点。SAVEPOINT - 指令用于创建保留点。ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START TRANSACTION 语句处。COMMIT - 提交事务。RELEASE SAVEPOINT：删除某个保存点。SET TRANSACTION：设置事务的隔离级别。事务处理示例：
-- 开始事务
START TRANSACTION;
-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');
-- 创建保留点 updateA
SAVEPOINT updateA;
-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');
-- 回滚到保留点 updateA
ROLLBACK TO updateA;
-- 提交事务，只有操作 A 生效
COMMIT;
（以下为 DCL 语句用法）
GRANT 和 REVOKE 可在几个层次上控制访问权限：
GRANT ALL 和 REVOKE ALL；新创建的账户没有任何权限。
账户用 username@host 的形式定义，username@% 使用的是默认主机名。
MySQL 的账户信息保存在 mysql 这个数据库中。
USE mysql;
SELECT user FROM user;
CREATE USER myuser IDENTIFIED BY 'mypassword';
UPDATE user SET user='newuser' WHERE user='myuser';
FLUSH PRIVILEGES;
DROP USER myuser;
SHOW GRANTS FOR myuser;
GRANT SELECT, INSERT ON *.* TO myuser;
REVOKE SELECT, INSERT ON *.* FROM myuser;
SET PASSWORD FOR myuser = 'mypass';
存储过程的英文是 Stored Procedure。它可以视为一组 SQL 语句的批处理。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。
定义存储过程的语法格式：
CREATE PROCEDURE 存储过程名称 ([参数列表])
BEGIN
需要执行的语句
END
存储过程定义语句类型：
CREATE PROCEDURE 用于创建存储过程DROP PROCEDURE 用于删除存储过程ALTER PROCEDURE 用于修改存储过程创建存储过程的要点：
DELIMITER 用于定义语句的结束符IN：存储过程的入参OUT：存储过程的出参INPUT：既是存储过程的入参，也是存储过程的出参BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。SET：赋值语句，用于对变量进行赋值。SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。每次只能给一个变量赋值，不支持集合的操作。IF…THEN…ENDIF：条件判断语句，可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。CASE：CASE 语句用于多条件的分支判断。创建存储过程示例：
DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
DECLARE c int;
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set sum = a + b;
END
;;
DELIMITER ;
使用存储过程示例：
set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;
存储过程的优点：
存储过程的缺点：
综上，存储过程的优缺点都非常突出，是否使用一定要慎重，需要根据具体应用场景来权衡。
触发器可以视为一种特殊的存储过程。
触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。
可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。
MySQL 不允许在触发器中使用 CALL 语句 ，也就是不能调用存储过程。
BEGIN 和 END
当触发器的触发条件满足时，将会执行 BEGIN 和 END 之间的触发器执行动作。
🔔 注意：在 MySQL 中，分号
;是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL 可以开始执行了。因此，解释器遇到触发器执行动作中的分号后就开始执行，然后会报错，因为没有找到和 BEGIN 匹配的 END。这时就会用到
DELIMITER命令（DELIMITER是定界符，分隔符的意思）。它是一条命令，不需要语句结束标识，语法为：DELIMITER new_delemiter。new_delemiter可以设为 1 个或多个长度的符号，默认的是分号;，我们可以把它修改为其他符号，如$-DELIMITER $。在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了$，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。
NEW 和 OLD
NEW 和 OLD 关键字，用来表示触发器的所在表中，触发了触发器的那一行数据。INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；NEW.columnName （columnName 为相应数据表某一列名）提示：为了理解触发器的要点，有必要先了解一下创建触发器的指令。
CREATE TRIGGER 指令用于创建触发器。
语法：
CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
trigger_statements
END;
说明：
BEFORE 或 AFTER。INSERT、UPDATE 或 DELETE。; 来结尾。创建触发器示例：
DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
INSERT INTO `user_history`(user_id, operate_type, operate_time)
VALUES (NEW.id, 'add a user', now());
END $
DELIMITER ;
查看触发器示例：
SHOW TRIGGERS;
删除触发器示例：
DROP TRIGGER IF EXISTS trigger_insert_user;
游标（CURSOR）是一个存储在 DBMS 服务器上的数据库查询，它不是一条
SELECT语句，而是被该语句检索出来的结果集。在存储过程中使用游标可以对一个结果集进行移动遍历。
游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。
使用游标的步骤：
DECLARE cursor_name CURSOR FOR <语句> 定义游标。这个过程没有实际检索出数据。OPEN cursor_name 打开游标。FETCH cursor_name INTO var_name ... 获取数据。CLOSE cursor_name 关闭游标。DEALLOCATE PREPARE 释放游标。游标使用示例：
DELIMITER $
CREATE PROCEDURE getTotal()
BEGIN
DECLARE total INT;
-- 创建接收游标数据的变量
DECLARE sid INT;
DECLARE sname VARCHAR(10);
-- 创建总数变量
DECLARE sage INT;
-- 创建结束标志变量
DECLARE done INT DEFAULT false;
-- 创建游标
DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
-- 指定游标循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
SET total = 0;
OPEN cur;
FETCH cur INTO sid, sname, sage;
WHILE(NOT done)
DO
SET total = total + 1;
FETCH cur INTO sid, sname, sage;
END WHILE;
CLOSE cur;
SELECT total;
END $
DELIMITER ;
-- 调用存储过程
call getTotal();
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。