SQL速查

发表于 更新于 分类于
SQL基础语法速查回顾。

基础回顾：SQL 十四分钟速成班！
实战练习：SQL之母

SQL书写与执行顺序

从上到下是关键字的书写顺序，需要严格遵守，完事儿sql会优化成注释的顺序执行 

1
2
3
4
5
6
7
SELECT name,age  -- 5.挑展示字段
FROM user_info   -- 1.先找哪张表
WHERE age>18     -- 2.先筛数据
GROUP BY age     -- 3.再分组
HAVING ...       -- 4.再过滤分组
ORDER BY age     -- 6.排序
LIMIT 10         -- 7.截取

需要特别注意的点：

  • WHERE在前，GROUP BY在后：WHERE不能用聚合函数（count/sum等），HAVING专门过滤分组后的聚合结果

  • ORDER BY、字段别名逻辑：SELECT执行在ORDER BY之前，所以排序可以直接使用字段别名

  • LIMIT永远最后执行：是最终结果的截取，不参与前置筛选、分组

基础关键字速查

统一测试表：user_info（用户表），用于所有示例演示

字段名 字段含义 数据类型
id 用户ID（主键） int
name 用户名 varchar
age 年龄 int
gender 性别 varchar
salary 薪资 decimal
create_time 注册时间 datetime

SELECT && FROM （查询与指定数据源）

作用：指定要查询的字段，支持单字段、多字段、全部字段、自定义计算、别名

标准语法：SELECT 字段1,字段2,... FROM 表名;

实战示例：

Text
1
2
3
4
5
6
7
8
# 查询所有字段
SELECT * FROM user_info;

# 查询指定字段，并设置字段别名
SELECT id,name,age AS 用户年龄 FROM user_info;

# 字段计算（薪资翻倍）
SELECT name,salary,salary*2 AS 翻倍薪资 FROM user_info;

DISTINCT （结果去重）

作用：对SELECT查询的最终字段结果去重

实战示例：

Text
1
2
3
4
5
# 查询所有不重复的性别
SELECT DISTINCT gender FROM user_info;

# 多字段联合去重（性别+年龄都相同才去重）
SELECT DISTINCT gender,age FROM user_info;

CASE WHEN（条件分支）

根据条件返回不同的查询结果。

语法： 

1
2
3
4
CASE WHEN (条件1) THEN 结果1
     WHEN (条件2) THEN 结果2
     ...
     ELSE 其他结果 END

实战示例： 

1
2
3
4
5
SELECT
  name,
  CASE WHEN (name = '鸡哥') THEN '会' ELSE '不会' END AS can_rap
FROM
  student;

时间函数

常用的时间函数有：

  • DATE：获取当前日期
  • DATETIME：获取当前日期时间
  • TIME：获取当前时间

还有很多时间函数，比如计算两个日期的相差天数、获取当前日期对应的毫秒数等，实际运用时自行查阅即可，此处不做赘述。

示例： 

1
2
3
4
5
6
7
8
-- 获取当前日期
SELECT DATE() AS current_date;

-- 获取当前日期时间
SELECT DATETIME() AS current_datetime;

-- 获取当前时间
SELECT TIME() AS current_time;

字符串处理

字符串处理是一类用于处理文本数据的函数。它们允许我们对字符串进行各种操作，如转换大小写、计算字符串长度以及搜索和替换子字符串等。

示例： 

1
2
3
4
5
6
7
8
9
10
11
-- 将姓名转换为大写
SELECT name, UPPER(name) AS upper_name
FROM employees;

-- 计算姓名长度
SELECT name, LENGTH(name) AS name_length
FROM employees;

-- 将姓名转换为小写并进行条件筛选
SELECT name, LOWER(name) AS lower_name
FROM employees;

聚合函数

聚合函数是一类用于对数据集进行 汇总计算 的特殊函数。它们可以对一组数据执行诸如计数、求和、平均值、最大值和最小值等操作。聚合函数通常在 SELECT 语句中配合 GROUP BY 子句使用。

常见的聚合函数包括：

COUNT：计算指定列的行数或非空值的数量。
SUM：计算指定列的数值之和。
AVG：计算指定列的数值平均值。
MAX：找出指定列的最大值。
MIN：找出指定列的最小值。 

1
2
3
4
5
6
7
8
9
10
11
-- 使用聚合函数 COUNT 计算订单表中的总订单数（即计算行数）
SELECT COUNT(*) AS order_num
FROM orders;

-- 使用聚合函数 COUNT(DISTINCT 列名) 计算订单表中不同客户的数量
SELECT COUNT(DISTINCT customer_id) AS customer_num
FROM orders;

-- 使用聚合函数 SUM 计算总订单金额
SELECT SUM(amount) AS total_amount
FROM orders;

开窗函数

开窗函数是一种强大的查询工具，它允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息 。

开窗函数可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，开窗函数不会导致结果集的行数减少。

SUM OVER

语法： 

1
SUM(计算字段名) OVER (PARTITION BY 分组字段名)

我们希望计算每个客户的订单总金额，并显示每个订单的详细信息： 

1
2
3
4
5
6
7
8
SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total_amount
FROM
    orders;

SUM OVER ORDER BY

sum over order by，可以实现同组内数据的 累加求和 。

用法： 

1
SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)

RANK

根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。在排名过程中，相同的值将被赋予相同的排名，而不同的值将被赋予不同的排名。

当存在并列（相同排序值）时，Rank 会跳过后续排名，并保留相同的排名。

Rank 开窗函数的常见用法是在查询结果中查找前几名（Top N）或排名最高的行。

Rank 开窗函数的语法如下： 

1
2
3
4
RANK() OVER (
  PARTITION BY 列名1, 列名2, ... -- 可选，用于指定分组列
  ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS rank_column

我们希望为每个客户的订单按照订单金额降序排名，并显示每个订单的详细信息。

order_id customer_id order date total_amount
1 101 2023-01-01 200
2 102 2023-01-05 350
3 101 2023-01-10 120
4 103 2023-01-15 500 
1
2
3
4
5
6
7
8
SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS customer_rank
FROM
    orders;

注意：是分组之后对每个分组单独进行排名

得到结果：

order_id customer_id total_amount 分区内排名
1 101 200 1
3 101 120 2
2 102 350 1
4 103 500 1

ROW_NUMBER

它与之前讲到的 Rank 函数不同，Row_Number 函数为每一行都分配一个唯一的整数值，不管是否存在并列（相同排序值）的情况。每一行都有一个唯一的行号，从 1 开始连续递增。

语法： 

1
2
3
4
ROW_NUMBER() OVER (
  PARTITION BY column1, column2, ... -- 可选，用于指定分组列
  ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS row_number_column

注意：如果进行了分组，结果可能和rank是一样的，因为每个分组都单独执行排序，不同的是，分组内用于order的值，如果相同，rank给的排名相同，row_number会不同。

LAG / LEAD

1）Lag 函数

Lag 函数用于获取 当前行之前 的某一列的值。它可以帮助我们查看上一行的数据。

Lag 函数的语法如下： 

1
LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)
  • column_name：要获取值的列名。
  • offset：表示要向上偏移的行数。例如，offset为1表示获取上一行的值，offset为2表示获取上两行的值，以此类推。
  • default_value：可选参数，用于指定当没有前一行时的默认值。
  • PARTITION BY和ORDER BY子句可选，用于分组和排序数据。

2）Lead 函数

Lead 函数用于获取 当前行之后 的某一列的值。它可以帮助我们查看下一行的数据。

Lead 函数的语法如下：

1
LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)

offset在这里就是表示向下偏移的行数了，其他和LAG一样。

WHERE （行数据筛选）

作用：过滤原始表单行数据，分组前筛选，不支持聚合函数

常用运算符：>、<、=、!=、IN、LIKE（%, _用于匹配）、BETWEEN AND、IS NULL

实战示例：

Text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 精准条件：查询25岁用户
SELECT * FROM user_info WHERE age = 25;

# 范围条件：薪资5000-10000
SELECT * FROM user_info WHERE salary BETWEEN 5000 AND 10000;

# 模糊查询：姓名含“张”
SELECT * FROM user_info WHERE name NOT LIKE '%张%';

# 空值判断：未填写薪资的用户
SELECT * FROM user_info WHERE salary IS NOT NULL;

# 多条件组合：成年男性
SELECT * FROM user_info WHERE age >= 18 AND gender = '男';

GROUP BY （分组聚合）

作用：根据指定字段分组，常搭配聚合函数使用（count、sum、avg、max、min）

核心规则：SELECT后的非聚合字段，必须出现在GROUP BY中

常用聚合函数：

  • count()：统计数量

  • sum()：求和

  • avg()：平均值

  • max()/min()：最大/最小值

实战示例：

Text
1
2
3
4
5
6
7
8
9
# 按性别分组，统计每组人数、平均薪资
SELECT gender,COUNT(*) AS 人数,AVG(salary) AS 平均薪资 
FROM user_info 
GROUP BY gender;

# 按年龄分组，统计每组最高薪资
SELECT age,MAX(salary) AS 最高薪资 
FROM user_info 
GROUP BY age;

HAVING （分组后筛选）

作用：过滤GROUP BY分组后的聚合结果，支持聚合函数

和WHERE的核心区别：WHERE筛原始数据（分组前），HAVING筛分组结果（分组后）

实战示例：

Text
1
2
3
4
5
6
7
8
9
10
11
# 按性别分组，筛选出人数大于2的分组
SELECT gender,COUNT(*) AS 人数 
FROM user_info 
GROUP BY gender 
HAVING COUNT(*) > 2;

# 筛选平均薪资大于6000的年龄分组
SELECT age,AVG(salary) AS 平均薪资 
FROM user_info 
GROUP BY age 
HAVING AVG(salary) > 6000;

ORDER BY （结果排序）

作用：对最终查询结果排序，支持升序、降序，可按别名排序

排序规则：ASC（升序，默认可省略）、DESC（降序）

实战示例：

Text
1
2
3
4
5
6
7
8
# 按薪资降序排序
SELECT name,salary FROM user_info ORDER BY salary DESC;

# 多字段排序：先按年龄升序，同年龄按薪资降序
SELECT name,age,salary FROM user_info ORDER BY age ASC,salary DESC;

# 按字段别名排序
SELECT name,salary*1.5 AS 税后薪资 FROM user_info ORDER BY 税后薪资 DESC;

LIMIT （结果截取）

作用：限制查询结果条数，分页核心语法，所有关键字最后执行

语法：LIMIT 起始下标,条数（起始下标从0开始，省略参数，那就默认为0）

实战示例：

Text
1
2
3
4
5
6
7
8
# 查询前3条数据（截断）
SELECT * FROM user_info LIMIT 3;

# 分页查询：第2页，每页5条（偏移：下标5开始，取后5条数据）
SELECT * FROM user_info LIMIT 5,5;

# 结合排序：查询薪资最高的前3人
SELECT name,salary FROM user_info ORDER BY salary DESC LIMIT 3;

万能模板

以下为标准书写顺序万能模板，完全贴合开发编码规范，底层执行遵循上述引擎顺序，可直接改参数复用

Text
1
2
3
4
5
6
7
SELECT 字段1,字段2,聚合函数(字段) AS 别名
FROM 表名
WHERE 原始数据筛选条件
GROUP BY 分组字段
HAVING 分组后聚合筛选条件
ORDER BY 排序字段 DESC/ASC
LIMIT 起始下标,展示条数;

常错的点

  1. WHERE中使用聚合函数：报错！聚合筛选必须用HAVING
  2. GROUP BY字段不匹配：SELECT的普通字段必须全部在GROUP BY中
  3. ORDER BY写在LIMIT之后：报错！排序必须在截取之前
  4. HAVING写在GROUP BY之前：报错！先分组再筛选分组结果

SQL表操作

SQL表操作主要分为两大块：表结构的增删改查（属于DDL）和表数据的增删改查（属于DML）。跨表合并查询则主要依靠 JOIN 操作。

表结构操作 (DDL)

这部分操作的是数据库表的”骨架”，不涉及具体数据。

CREATE TABLE (创建表)

这是最基础的操作，用于定义一个新表的结构。你需要指定表名、列名、每列的数据类型，以及可选的约束（如主键、外键等）。 

1
2
3
4
5
6
7
8
9
-- 一个标准的建表语句示例
CREATE TABLE `user` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID，自增主键',
  `username` VARCHAR(50) NOT NULL COMMENT '用户名',
  `email` VARCHAR(100) DEFAULT NULL COMMENT '用户邮箱',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

这个语句不仅定义了表，还指定了主键、唯一索引、默认值、存储引擎和字符集等关键属性。其中 COMMENT 关键字可以为表和字段添加注释，方便团队协作。

ALTER TABLE (修改表)

当业务变化时，需要用此命令来调整现有表的结构。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 1. 新增一个列
ALTER TABLE `user` ADD COLUMN `mobile` VARCHAR(20) NULL AFTER `email`;

-- 2. 修改一个列的数据类型
ALTER TABLE `user` MODIFY COLUMN `username` VARCHAR(100) NOT NULL;

-- 3. 重命名一个列
ALTER TABLE `user` CHANGE COLUMN `status` `state` TINYINT NOT NULL DEFAULT 1;

-- 4. 删除一个列
ALTER TABLE `user` DROP COLUMN `mobile`;

-- 5. 重命名整个表
RENAME TABLE `user` TO `app_user`;

以上示例涵盖了新增、修改、重命名和删除列或表等常见场景。

DROP TABLE (删除表)

此命令会彻底删除表的结构和其中的所有数据。这是一个不可逆的操作，务必谨慎。 

1
2
-- 安全做法：先判断表是否存在再删除，避免报错
DROP TABLE IF EXISTS `order_item`;

在删除前，建议先进行数据备份。

表数据操作 (DML)

这部分操作的是表中的具体数据行。

INSERT (插入数据)

向表中添加新的一行或多行数据。 

1
2
3
4
5
6
7
-- 1. 插入单行数据（全列插入）
INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);

-- 2. 插入多行数据（指定列插入）
INSERT INTO students (id, sn, name) VALUES 
  (102, 20001, '曹孟德'),
  (103, 20002, '孙仲谋');

插入时可以指定列，也可以不指定（需按顺序提供所有列的值）。

SELECT (查询数据)

从表中检索数据。这是最常用、最复杂的操作。 

1
2
3
4
5
-- 查询表中所有列的所有行
SELECT * FROM students;

-- 查询特定列，并添加筛选条件
SELECT id, name FROM students WHERE id > 100;

UPDATE (更新数据)

修改表中已有的数据行。 

1
2
-- 更新特定行的数据
UPDATE students SET qq = '123456' WHERE name = '孙悟空';

务必注意：UPDATE 语句通常要配合 WHERE 子句使用，否则会更新表中的所有行。

DELETE (删除数据)

从表中移除数据行。 

1
2
-- 删除特定行的数据
DELETE FROM students WHERE id = 100;

和 UPDATE 一样，DELETE 也必须谨慎使用 WHERE 子句，否则会清空整张表。

跨表合并查询 (JOIN)

这是关系型数据库的核心能力，用于将两个或多个表的数据根据关联条件合并到一个结果集中。

INNER JOIN (内连接)

只返回两个表中都能匹配上的行（会去除不匹配的行以及含有NULL的行）。 

1
2
3
4
-- 查询所有有订单的用户信息（示例中把表取了别称）
SELECT u.name, o.order_id
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

LEFT/RIGHT JOIN (左/右外连接)

  • LEFT JOIN 返回左表（users）的所有行，即使右表（orders）中没有匹配。
  • RIGHT JOIN 则相反。

有些数据库并不支持 RIGHT JOIN 语法，那么如何实现 RIGHT JOIN 呢？

其实只需要把主表（from 后面的表）和关联表（LEFT JOIN 后面的表）顺序进行调换即可！ 

1
2
3
4
-- 查询所有用户及其订单（没有订单的用户也会显示）
SELECT users.name, orders.order_id
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id;

FULL OUTER JOIN (全外连接)

返回两个表中的所有行，无论是否匹配。MySQL 不直接支持，但可以通过 UNION 实现。

CROSS JOIN (交叉连接，关联查询)

返回两个表的笛卡尔积，即左表的每一行都与右表的每一行组合。使用需谨慎。

其中，CROSS JOIN 是一种简单的关联查询，不需要任何条件来匹配行，它直接将左表的 每一行 与右表的 每一行 进行组合，返回的结果是两个表的笛卡尔积。

使用 CROSS JOIN 进行关联查询，将员工表和部门表的所有行组合在一起，获取员工姓名、工资、部门名称和部门经理，示例 SQL 代码如下： 

1
2
3
SELECT e.emp_name, e.salary, d.department, d.manager
FROM employees e
CROSS JOIN departments d;

上面的 SQL 还可以简化为： 

1
2
SELECT e.emp_name, e.salary, d.department, d.manager
FROM employees e, departments d;

通过逗号分隔表名，隐式地实现了笛卡尔积，是 SQL 早期的写法，功能上与 CROSS JOIN 完全相同。

注意，在多表关联查询的 SQL 中，我们最好在选择字段时指定字段所属表的名称（比如 e.emp_name），还可以通过给表起别名（比如 employees e）来简化 SQL 语句。

子查询（可跨表进行条件过滤）

当执行包含子查询的查询语句时，数据库引擎会首先执行子查询，然后将其结果作为条件或数据源来执行外层查询。

我们希望查询出有订单总金额 > 200 的客户的姓名和城市信息，示例 SQL 如下： 

1
2
3
4
5
6
7
8
9
-- 主查询
SELECT name, city
FROM customers
WHERE customer_id IN (
    -- 子查询
    SELECT DISTINCT customer_id
    FROM orders
    WHERE total_amount > 200
);

编写一个 SQL 查询，使用子查询的方式来获取存在对应班级的学生的所有数据，返回学生姓名（name）、分数（score）、班级编号（class_id）字段。 

1
2
3
4
5
6
7
8
9
10
select
    name, score, class_id
from
    student
where class_id IN (
    select
        id
    from
        class
);

子查询中的一种特殊类型是 “exists” 子查询（与之相对的是 “not exists”），用于检查主查询的结果集是否存在满足条件的记录，它返回布尔值（True 或 False），而不返回实际的数据。

我们希望查询出 存在订单的 客户姓名和订单金额： 

1
2
3
4
5
6
7
8
9
-- 主查询
SELECT name, total_amount
FROM customers
WHERE EXISTS (
    -- 子查询
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id
);

上述语句中，先遍历客户信息表的每一行，获取到客户编号；然后执行子查询，从订单表中查找该客户编号是否存在，如果存在则返回结果。

组合查询

包括两种常见的组合查询操作：UNION 和 UNION ALL。

UNION 操作：它用于将两个或多个查询的结果集合并， 并去除重复的行 。即如果两个查询的结果有相同的行，则只保留一行。

UNION ALL 操作：它也用于将两个或多个查询的结果集合并， 但不去除重复的行 。即如果两个查询的结果有相同的行，则全部保留。

示例： 

1
2
3
4
5
6
7
8
9
10
11
12
13
-- UNION 操作
SELECT name, age, department
FROM table1
UNION
SELECT name, age, department
FROM table2;

-- UNION ALL 操作
SELECT name, age, department
FROM table1
UNION ALL
SELECT name, age, department
FROM table2;