首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >SQL 稍复杂一点语法的学习笔记

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

原创
作者头像
amc
修改于 2024-10-26 12:42:24
修改于 2024-10-26 12:42:24
2800
举报
文章被收录于专栏:后台全栈之路后台全栈之路

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

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

数据查询

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

代码语言:sql
AI代码解释
复制
SELECT [ ALL | DISTINCT ] <目标列表达式> [, <目标列表达式>, ...]
FROM <表名 or 视图名> [, <表名 or 视图名>, ...]
[ WHERE <条件表达式> ]
[ GROUP BY <列名1>,  [HAVING <条件表达式> ] ]
[ ORDER BY <列名2>,  [ ASC | DESC ] ]

单表查询

查询经过计算的值:

代码语言:sql
AI代码解释
复制
SELECT name, 2024 - birth_year ...;

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

代码语言:sql
AI代码解释
复制
SELECT name AS 姓名, 2024 - birth_year AS 年龄 ...;

常用的查询表达式

功能

表达式

比较

= > < >= <= != <> !> !<

确定的范围

BETWEEN ... AND ..., NOT BETWEEN ... AND ...

确定的集合

IN, NOT IN

字符匹配

LIKE, NOT LIKE

空值

IS NULL, IS NOT NULL

多重条件

AND, OR, NOT

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

代码语言:sql
AI代码解释
复制
SELECT grade, class, name FROM t_student WHERE name LIKE '李%';

聚合函数 aggregate functions

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

运算符

后接格式

作用

COUNT

([ DISTINCT | ALL ] *)

统计个数

COUNT

([ DISTINCT | ALL ] <列名>)

统计某列值的个数

SUM

([ DISTINCT | ALL ] <列名>)

列值的和

AVG

([ DISTINCT | ALL ] <列名>)

列值的平均

MAX

([ DISTINCT | ALL ] <列名>)

列值的最大值

MIN

([ DISTINCT | ALL ] <列名>)

列值的最小值

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

代码语言:sql
AI代码解释
复制
SELECT no, name FROM t_student_class GROUP BY no HAVING COUNT(*) > 3;

连接查询

等值与非等值连接查询

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

代码语言:sql
AI代码解释
复制
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

比如:

代码语言:sql
AI代码解释
复制
SELECT t_student.*, t_student_class.*
FROM t_student, t_student_class
WHERE t_student.no = t_student_class.student_no

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

外连接

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

代码语言:sql
AI代码解释
复制
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 的源, 这就是嵌套查询, 如:

代码语言:sql
AI代码解释
复制
SELECT name FROM t_student WHERE no IN (
  SELECT student_no FROM t_student_class WHERE student_no = '2'
);

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

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

代码语言:sql
AI代码解释
复制
SELECT no, name, dept
FROM t_student
WHERE dept = (SELECT dept FROM t_student WHERE name ='张三')

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

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

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

代码语言:sql
AI代码解释
复制
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 的数据类型

整型数值

类型

说明

TINYINT

int8

SMALLINT

int16

MEDIUMINT

int24

INT

int32

BIGINT

int64

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

浮点数值

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

定点数

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

字符串

类型

说明

CHAR(n)

定长字符串, 最大 255, 末位不能有空格

VARCHAR(n)

变长字符串, 最大 65535

TINYTEXT

变长文本, 最大 255

TEXT

变长文本, 最大 65535

日期和时间

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


MySQL 的 JOIN 语法

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

代码语言:sql
AI代码解释
复制
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:

代码语言:sql
AI代码解释
复制
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 的 “存在即更新,不存在则插入” 语法

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

代码语言:sql
AI代码解释
复制
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 ...
)

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

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

代码语言:sql
AI代码解释
复制
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;

上面的语句中, 以下部份

代码语言:sql
AI代码解释
复制
SELECT * FROM (
  SELECT
  '2' AS partner_id,
  '10000004' AS business_party_id,
  'wx123456789abcdef' AS wx_app_id,
  ...
) AS tmp

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

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

代码语言:sql
AI代码解释
复制
INSERT ... ON DUPLICATE KEY UPDATE
INSERT ... ON DUPLICATE REPLACE

参考资料:


查看表的基本信息

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

代码语言:sql
AI代码解释
复制
SELECT * FROM information_schema.columns
WHERE table_schema='db 名'
AND table_name='表名'

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

代码语言:sql
AI代码解释
复制
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

字段

类型

作用

id

INT UNSIGNED

自增 id

trigger_time

BIGINT

触发时间戳

triggered

BOOL

是否已触发

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

代码语言:sql
AI代码解释
复制
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 的临时变量功能, 例:

代码语言:sql
AI代码解释
复制
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 语句就是弃用这个临时变量

参考资料:


数据库的复制和转移

重命名表

代码语言:sql
AI代码解释
复制
RENAME TABLE 旧名 TO 新名

复制一整个表

代码语言:sql
AI代码解释
复制
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 各种 Join 的方法效果

参考资料:


MySQL varchar 的编码格式说明

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

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

一般来说不使用 utf8mb4_general_csutf8mb4_general_ci。注意, 没有 "utf8mb4_unicode_cs"

参考资料:


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

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

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

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

发布日期: 2024-10-26

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

CC BY-NC-SA 4.0 DEED.png
CC BY-NC-SA 4.0 DEED.png

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
1 条评论
热度
最新
请问大神,如果要跑bluestore的fio怎么跑?
请问大神,如果要跑bluestore的fio怎么跑?
回复回复点赞举报
推荐阅读
JUC学习之共享模型上
两个线程对初始值为 0 的静态变量一个做自增,一个做自减,各做 5000 次,结果是 0 吗?
大忽悠爱学习
2021/12/20
4650
JUC学习之共享模型上
偏向锁批量重偏向与批量撤销
批量重偏向:当一个线程创建了大量对象并执行了初始的同步操作,后来另一个线程也来将这些对象作为锁对象进行操作,会导偏向锁重偏向的操作。 批量撤销:在多线程竞争剧烈的情况下,使用偏向锁将会降低效率,于是乎产生了批量撤销机制。 JVM的默认参数值: intx BiasedLockingBulkRebiasThreshold = 20 默认偏向锁批量重偏向阈值 intx BiasedLockingBulkRevokeThreshold = 40 默认偏向锁批量撤销阈值 批量重偏向 测试代码: pu
@阿诚
2021/02/04
1.6K0
面试专题:Synchronized 锁的升级过程(锁/对象状态)及底层原理
这个面试题其实涉及到的底层知识比较多,在Java中都知道synchronized,这是一个关键字,为什么使用了之后,可以结果多线程安全问题。里面内部流程是怎样的呢?加锁是加在哪里?金三银四越来越卷,面试官不再是,单纯的问如何解决线程安全,有没有使用过synchronized,而是想知道synchronized底层的知识点。本文就深入讲解synchronized底层原理,对象加锁是如果一步一步实现的。
小明爱吃火锅
2024/02/18
1.5K0
面试专题:Synchronized 锁的升级过程(锁/对象状态)及底层原理
JUC学习笔记——共享模型之管程
但是如果是多线程,就会因为上下文切换的缘由导致部分步骤出现交杂(我们给出正数示例):
秋落雨微凉
2022/11/18
7580
JUC学习笔记——共享模型之管程
多线程五 锁的膨胀过程
上一篇中,涉及到了锁升级的过程,也对其锁的升级有了一个大概的了解:单线程持有,在jvm延迟偏向的时间内是轻量级锁,之后为偏向锁,出现多个线程交替执行,对同一资源加锁会升级为轻量级锁,多个线程竞争拿不到锁会升级为重量级锁。在上一篇的基础上再进一步的了解锁升级的过程。
用针戳左手中指指头
2021/01/29
3090
多线程四 并发中锁的原理
偏向锁就是在运行过程中,对象的锁偏向某个线程,即在开启偏向锁的情况下,某个线程获得锁,当该线程下次想要获得锁时,不需要再获取锁(忽略synchronized关键字),直接执行代码
用针戳左手中指指头
2021/01/29
6170
多线程四 并发中锁的原理
Synchronized 原理与锁升级
锁对象为 this,校验锁对象就是 this 改造一下上方的同步方法,用 synchronized 加上锁对象的方式来校验锁对象就是 this
程序员NEO
2023/09/30
2760
Synchronized 原理与锁升级
synchronized 底层如何实现?什么是锁升级、降级?
synchronized 代码块是由一对 monitorenter/monitorexit 指令实现的,Monitor 对象是同步的基本实现单元。
王小明_HIT
2020/05/08
1.5K0
synchronized 底层如何实现?什么是锁升级、降级?
15000字、6个代码案例、5个原理图让你彻底搞懂Synchronized
本篇文章将围绕synchronized关键字,使用大量图片、案例深入浅出的描述CAS、synchronized Java层面和C++层面的实现、锁升级的原理、源码等
菜菜的后端私房菜
2024/07/15
5801
synchronized底层实现知多少?synchronized加锁还会升级?
线程2将count减到了97,线程3、线程1在某一刻也做了count--,但是结果却也是97,说明他们在做count--的时候并不知道有别的线程也操作了count。
行百里er
2020/12/02
4530
synchronized底层实现知多少?synchronized加锁还会升级?
synchronized锁详解
    如:两个线程对初始值为 0 的静态变量一个做自增,一个做自减,各做 5000 次,结果是 0 吗?(针对这个问题进行分析)
忧愁的chafry
2022/10/30
6060
synchronized锁详解
4. synchronized详解
  多线程编程中,有可能会出现多个线程同时访问同一个共享、可变资源的情况,这个资源我们称之其为临界资源;这种资源可能是:对象、变量、文件等。
用户7798898
2020/09/27
5270
4. synchronized详解
并发基石-Markword与锁升级
synchronized关键字是java提供的互斥锁关键字,我们常说的互斥锁一般都是非自旋锁,即竞争不到锁的线程会进入阻塞状态知道被唤醒 今天我们来讲讲java中用来对synchronized进行优化的三种锁,同时会介绍markword对象头 目前我在网上搜到的十几篇博客讲的都有问题,可能有写对的我没搜到. 很多人不经过验证直接把markOop.hpp中的JavaThread*当成ThreadId这是错误的,实际是java线程在C语言的指针 并且未计算过hashCode和计算过hashCode的情况也是不一样的 本篇博客最后会展示使用jol工具,读取展示对象头的结果进行验证 附上openjdk的markOop.hpp链接
歪歪梯
2020/06/19
6382
深入了解Java锁
继 打印Java对象头,我们再深入探索一下Java锁。无锁状态我们就不说了,下面我们一一打印偏向锁、轻量锁,重量锁的对象头。
@阿诚
2021/02/04
4760
synchronized 锁的升级过程
测试代码 static A obj; // -XX:BiasedLockingStartupDelay=0 偏向锁开关 // -XX:+PrintFlagsInitial 打印所有参数 public static void main(String[] args) throws InterruptedException { obj = new A(); // Thread.sleep(60000); System.out.println(ClassLayout.parseInstance(ob
付威
2021/03/07
6950
多线程基础(五):java对象的MarkWord及synchronized锁升级过程
在前面聊过了如何使用synchronized,以及synchronized不同的加锁方式分别锁的是哪些对象。本文对synchronized底层的原理进行深层次的分析。
冬天里的懒猫
2020/09/10
9750
多线程基础(五):java对象的MarkWord及synchronized锁升级过程
[JAVA基础] - JVM对象内存布局及锁的标记位
一、对象布局 1、对象头 1)存储对象自身的运行时数据 hash码、GC分代年龄、锁状态标志、线程持有的锁、偏向线程ID、偏向时间戳等。占位32/64位虚拟机分别占32/64个比特,官方称"Mark Word" 2)类型指针 指向对象的元数据,如果是数组,还会存储数组长度。 2、实例数据 3、对齐填充 要求对象是8的整数倍,对象头已经是8位的整数倍,只填充实例数据即可。 二、Object o = new Object()内存占用情况 占用16个字节 对象头12个字节,对齐填充4个字
夹胡碰
2022/05/19
4760
[JAVA基础] - JVM对象内存布局及锁的标记位
synchronized锁升级原理
KlassWord(下图_klass)占32位 64位系统的Klass Word不是32位,默认64位,开启指针压缩后为32(感谢评论老哥的指出)
CBeann
2023/12/25
1980
synchronized锁升级原理
synchronized的实现原理——对象头解密
并发编程式Java基础,同时也是Java最难的一部分,因为与底层操作系统和硬件息息相关,并且程序难以调试。本系列就从synchronized原理开始,逐步深入,领会并发编程之美。
夜勿语
2020/09/07
6080
面经手册 · 第15篇《码农会锁,synchronized 解毒,剖析源码深度分析!》
这是最近我总能被问到的问题,也确实是。一个初入编程职场的新人,或是一个想重新努力学习的老司机,这也不会,那也不会,总会犯愁从哪开始。
小傅哥
2020/11/04
5740
面经手册 · 第15篇《码农会锁,synchronized 解毒,剖析源码深度分析!》
推荐阅读
相关推荐
JUC学习之共享模型上
更多 >
LV.1
这个人很懒,什么都没有留下~
目录
  • 数据查询
    • 单表查询
    • 常用的查询表达式
    • 聚合函数 aggregate functions
    • 连接查询
    • 嵌套查询
    • 集合查询
  • MySQL 的数据类型
    • 整型数值
    • 浮点数值
    • 定点数
    • 字符串
    • 日期和时间
  • MySQL 的 JOIN 语法
  • MySQL 的 “存在即更新,不存在则插入” 语法
  • 查看表的基本信息
  • MySQL 中获得 last_update_id 的方法
  • 数据库的复制和转移
  • MySQL INSERT ... ON DUPLICATE KEY UPDATE 相关问题
  • MySQL 各种 Join 的方法效果
  • MySQL varchar 的编码格式说明
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档