SQL 稍复杂一点语法的学习笔记

最近整理我手写的笔记, 其中有不少内容随着时代的变化, 用得越来越少了, 抑或是电子化了发到了我的博文中。然而我发现我当年学习的 SQL 笔记, 只电子化了特别基础和简单的 一篇, 后面还有一些躺在纸上。既然有点时间, 那我就把那些内容也都整理一下发上来吧。

另外注意的是, 部分内容是标准 SQL 规范的内容, 和 MySQL 并不一定一致。

数据查询

查询数据使用 SELECT, 其一般格式为:

SELECT [ ALL | DISTINCT ] <目标列表达式> [, <目标列表达式>, ...]
FROM <表名 or 视图名> [, <表名 or 视图名>, ...]
[ WHERE <条件表达式> ]
[ GROUP BY <列名1>,  [HAVING <条件表达式> ] ]
[ ORDER BY <列名2>,  [ ASC | DESC ] ]

单表查询

查询经过计算的值:

SELECT name, 2024 - birth_year ...;

效果是输出两列: 名字和年龄。可以指定输出列的名字, 如:

SELECT name AS 姓名, 2024 - birth_year AS 年龄 ...;

常用的查询表达式

在字符匹配中, 只能用 % 和 _ 两种通配符, 分别表示 “任意长度字符” 和 “一个 8 位字符”。比如查找姓李的学生:

SELECT grade, class, name FROM t_student WHERE name LIKE '李%';

聚合函数 aggregate functions

SELECT 后的对象中, SQL 支持插入函数, 进行结果的运算:

聚合函数也经常配合 GROUP BY 使用。比如统计选修课大于 3 门的学生信息:

SELECT no, name FROM t_student_class GROUP BY no HAVING COUNT(*) > 3;

连接查询

等值与非等值连接查询

这里主要是在 WHERE 语句中的语句部份, 一般来说该部份的格式为:

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

比如:

SELECT t_student.*, t_student_class.*
FROM t_student, t_student_class
WHERE t_student.no = t_student_class.student_no

如果某一个列名只在一个表出现, 那么可以省略表名。

外连接

对于被过滤条件舍弃掉的结果, 仍要以 NULL 显示出来的话, 那么就使用所谓的 外连接 模式, 如:

SELECT t_student.no, name, sex, birth_year, dept, grade, class
FROM t_student LEFT OUT JOIN t_student_class ON (t_student.no = t_student_class.student_no)

嵌套查询

SELECT 出来的结果, 可以作为另一句 SELECT 的源, 这就是嵌套查询, 如:

SELECT name FROM t_student WHERE no IN (
  SELECT student_no FROM t_student_class WHERE student_no = '2'
);

括号内的语句不能使用 ORDER BY 语句

如果被嵌套的子句（称为 内查询）的结果只有一个值时, 可以使用比较运算符, 如:

SELECT no, name, dept
FROM t_student
WHERE dept = (SELECT dept FROM t_student WHERE name ='张三')

如果子查询返回多个数字值, 也可以使用一个操作: ANY（有些 DB 用 SOME）或 ALL:

• 比较操作符 + ANY 或 SOME: 对结果任一值符合比较条件
• 比较操作符 + ALL: 对结果所有值符合比较条件

如: 查询其他系中臂计算机起任一学生年龄小的学生信息:

SELECT name, birth_year FROM t_student WHERE birth_year > ANY (
  SELECT age FROM t_student WHERE dept = 'CS'
) AND dept <> 'CS';

但聚合函数的效率高于 ANY / ALL

集合查询

同级的 SELECT 语句, 可以取集合操作:

1. 并集: UNION
2. 交集: INTERSECT
3. 差集: EXCEPT

MySQL 的数据类型

整型数值

如果在每个类型后面加上 UNSIGNED 则变为无符号整型。比如 TINYINT UNSIGNED 表示 uint8

浮点数值

浮点数分别是 FLOAT 和 DOUBLE, 实际上在 MySQL 场景中, 不太建议使用浮点数, 也不太需要使用。

定点数

定点数是 DECIMAL(m,d), 其中 m 表示数据总长, d 表示小数点后的位数。

字符串

日期和时间

现在推荐使用的只有 DATETIME(m) 了, 其中 m 表示秒后小数点位数。但是这个字段的时区收到多种配置的影响, 如果有比较准确的时间计算要求, 不太建议使用, 只建议作为参考。

MySQL 的 JOIN 语法

这里拿我们业务一条语句做分析

SELECT a.*
FROM ( SELECT uuid, max(id) AS id
       FROM t_dev_app
       WHERE status = 5 GROUP BY uuid
    ) AS b
JOIN t_dev_app AS a
ON a.id = b.id
WHERE has_publish = 1 AND a.status <> 9
ORDER BY a.create_time DESC LIMIT 80

看外层结构, 这其实是一个经典的 SELECT xxx FROM t_xxx WHERE condition 的句式, 其中的 FROM 就是本 JOIN 的语法所在: 将相对应的表中的部份列组合成了一个临时的新表。

AS 语句作为一个临时重命名列名的功能, 在各个位置均可以使用。

另外看一个较为简化的 JOIN:

SELECT a.id AS aid, b.id AS bid. a.uuid, a.agent_id, a.name
FROM t_dev_app AS a JOIN t_app_agent_filter AS b
ON a.agent_id = b.dev_app_agent_id AND b.status = 1

ON 指定了取 JOIN 的条件, 对于 JOIN (INNER JOIN), 使用 ON 与在外层加一个 WHERE 的效果是相同的。

但对于 LEFT JOIN 和 RIGHT JOIN 就要注意, ON 和 WHERE 的影响范围会有不同。

当效果相同时, 建议优先使用 ON。

JOIN 是左右取并集, LEFT JOIN 是左全集右并集, RIGHT JOIN 是左并集右全集。

参考资料:

• Mysql 连接的使用
• MySQL 的 join (结合) 语法
• SQL Joins Using WHERE or ON

MySQL 的 “存在即更新,不存在则插入” 语法

如果不利用 unique 规则的话, 只用一条 SQL 语句只能做到后半句。这里我们使用 INSERT ... SELECT 语法, 用一条语句来作为例子:

INSERT INTO t_app_agent_filter (mode, status, app_id, uid, ...)
SELECT 1, 1, 'some_appid', 'some_uuid', ...
FROM dual
WHERE NOT EXISTS (
  SELECT id FROM t_app_agent_filtet
  WHERE app_id = 'some_appid'
  AND status = 1
  AND ...
)

其中第二行是需要插入的值, 括号部份就是查重条件。

不过上面的语句其实也还是有一点缺陷的, 下面是进一步优化的结果, 还是直接用我们业务中的一个语句来说明

INSERT INTO t_payment_order_info (partner_id, business_party_id, wx_app_id, ...)
SELECT * FROM (
  SELECT
    '2' AS partner_id,
    '10000004' AS business_party_id,
    'wx123456789abcdef' AS wx_app_id,
    ...
) AS tmp
WHERE NOT EXISTS (
  SELECT id FROM t_payment_order_info
  WHERE wx_app_id = 'wx123456789abcdef'
  AND ...
) LIMIT 1;

上面的语句中, 以下部份

SELECT * FROM (
  SELECT
  '2' AS partner_id,
  '10000004' AS business_party_id,
  'wx123456789abcdef' AS wx_app_id,
  ...
) AS tmp

是为了防止不同列的数据值相等, 而导致出现 Duplicate column name 错误。说实话这个格式的逻辑我分析不出来, 只知道按模式套进去就行。

当然更为合适的方法是使用 unique 值来控制, 也就是:

INSERT ... ON DUPLICATE KEY UPDATE
INSERT ... ON DUPLICATE REPLACE

参考资料:

• mysql如何不重复插入满足某些条件的重复的记录的问题
• insert into ... values (SELECT ... FROM ...)
• mysql插入记录时检查记录是否已经存在,存在则更新,不存在则插入记录SQL
• MySQL 记录不存在插入 和 存在则更新
• MySQL: Insert record if not exists in table

查看表的基本信息

我们都知道用 DESC 表名 和 SHOW CREATE TABLE 表名 来查看, 但是这两种方法都看不带字段的注释。这里记录另一个方法, 用于查阅更多的表信息:

SELECT * FROM information_schema.columns
WHERE table_schema='db 名'
AND table_name='表名'

但是这个表中很多字段对我们来说可能用处不大, 下面是我个人觉得最有用的几个字段, 与 DESC 语法的各个列关联起来:

SELECT
  ORDINAL_POSITION AS No,
  COMUMN_NAME      AS Field,
  COLUMN_TYPE      AS Type,
  IS_NULLABLE AS Nul,
  COLUMN_DEFAULT as Dflt,
  COLUMN_KEY as Key,
  COLUMN_COMMENT as Comment,
FROM information_schema.columns
WHERE table_schema='db 名' AND table_name='表名'
ORDER BY ORDINAL_POSITION ASC

MySQL 中获得 last_update_id 的方法

这个问题经常出现在我们使用 MySQL 当作消息队列来使用的场景下。比如假设我们有一个表来表示定时任务: t_schedule_task

那么从数据库中取一条记录的语句便为:

UPDATE t_schedule_task SET triggered = 1
WHERE trigger_time <= UNIX_TIMESTAMP() AND triggered=0
LIMIT 1;

但是上面的语句是无法获得 id 的, 因为可能会有多条数据都满足 WHERE 条件, 并且有多个 client 可能同时执行, 从而锁定了满足同一个条件的多条语句。

解决方法, 可以在表中加入一个 uuid 字段, 每次执行的时候都 update 一个唯一 id, 如果更新数量大于 0 的话再 select 就行。如果实在是没法改, 那么也有另一个方法, 就是用 MySQL 的临时变量功能, 例:

SET @updated_id_magic := 0;

UPDATE t_schedule_task SET triggered=1, id=(SELECT @updated_id_magic := id)
WHERE trigger_time <= UNIX_TIMESTAMP() AND triggered=0
LIMIT 1;

SELECT @updated_id_magic AS updated_id;

SET @updated_id_magic = NULL;

可以看到, 这个操作需要四条语句, 效率比较低。需要注意的是:

1. 最开始的 updated_id_magic 最好换成一个临时随机的值, 比如 uuid 或随机值如 updated_id_114514, 避免重复
2. 临时变量的生存期是整个 MySQL 连接期间, 在复用连接的情况下, 最后一条 SET NULL 语句就是弃用这个临时变量

参考资料:

• 转: MYSQL获取更新行的主键ID
• MySQL Delete Session Variable

数据库的复制和转移

重命名表

RENAME TABLE 旧名 TO 新名

复制一整个表

INSERT INTO 新表 (字段, ...)
SELECT 字段, ...
FROM 旧表

MySQL INSERT ... ON DUPLICATE KEY UPDATE 相关问题

前面提到的可以用该方法来实现 “不存在则插入, 存在则更新” 的功能, 但是这条语句会遇到两个问题:

1. 当使用自增 id 时, 每执行一次, 即便没有插入, 也会导致表的自增 id 的下一个目标值加一
2. 如果频繁操作, 自增 id 可能用完, 导致溢出, 并且在溢出之后, 由于数据库中实际上 id 不连续, 因此各 client 需要尝试下一个 id 从而使数据库性能下降

原因: InnoDB 重的 innodb_autoinc_lock_mode 默认等于 1, 这个模式下为了有更高的并发性能导致的（具体看参考资料）

这个问题有以下的几种解决思路:

1. MySQL 启动的时候指定 innodb_autoinc_lock_mode = 0, 这个值是不能在 MySQL 运行时修改。或者使用 2, 不过 2 的缺点是会导致自增 id 不保证连续
2. 先进行 UPDATE, 更新数为 0 的话再执行 INSERT, 这适合 UPDATE 概率较高的业务逻辑, 此外这个逻辑也要确保在 UPDATE 的时候必然有一个字段被更新
3. 使用前面的 SELECT od INSERT 模式, 这种模式则适合 UPDATE 概率低, 但 INSERT 概率比较高的模式

参考资料:

• MySQL使用 on duplicate key update 引起主键不连续自增
• MySQL自增id溢出
• Why are 2 rows affected in my 'INSERT ... ON DUPLICATE KEY UPDATE'?

MySQL 各种 Join 的方法效果

参考资料:

• What is thr difference between JOIN and UNION?

MySQL varchar 的编码格式说明

基本上我们都会要求使用 utf8mb4, 但是实际上还有更加详细的。总体而言, 可以参考以下指导:

• 如果是一个 ID, 则使用 utf8mb4_bin, 区分大小写
• 如果是一个简单的描述文字, 则使用 utf8mb4_unicode_ci
• URL, 使用 utf8mb4_bin

一般来说不使用 utf8mb4_general_cs 和 utf8mb4_general_ci。注意, 没有 "utf8mb4_unicode_cs"

参考资料:

• Mysql的utf8与utf8mb4区别, utf8mb4_bin、utf8mb4_general_ci与utf8mb4_unicode_ci的选择
• mysql默认排序规则utf8mb4_mysql-虽然排序规则是utf8mb4_unicode_ci,但SQL并没有区分u和ü...
• mysql修改某字段的格式为utf8mb4

本文章采用 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议 进行许可。

原作者: amc, 原文发布于腾讯云开发者社区, 也是本人的博客。欢迎转载, 但请注明出处。

原作者: amc, 欢迎转载, 但请注明出处。

原文标题: 《SQL 稍复杂一点语法的学习笔记》

发布日期: 2024-10-26

原文链接: https://cloud.tencent.com/developer/article/2461737。

CC BY-NC-SA 4.0 DEED.png