侧边栏壁纸
博主头像
一朵云的博客博主等级

拥抱生活，向阳而生。

  • 累计撰写 107 篇文章
  • 累计创建 28 个标签
  • 累计收到 7 条评论

目 录CONTENT

文章目录
数据库

MySQL -- 关于查询那点儿事

一朵云
一朵云
2025-08-22 / 0 评论 / 0 点赞 / 25 阅读 / 18174 字

MySQL -- 关于查询那点儿事

前言：

温故而知新，最近准备出去面试了，重新写篇博客梳理下MySQL中常用查询。

​ 

准备：

建库：

cmge_test

CREATE DATABASE IF NOT EXISTS cmge_test
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

建表：

-- 创建 users 表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    city VARCHAR(50)
);

-- 创建 orders 表（order_date 为 BIGINT 存储时间戳）
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10, 2),
    order_timestamp BIGINT NOT NULL COMMENT '时间戳（毫秒）',
    status VARCHAR(20)
);

插入数据：

INSERT INTO users (user_id, name, city) VALUES
(1, '张三', '北京'),
(2, '李四', '上海'),
(3, '王五', '北京'),
(4, '赵六', '深圳'),
(5, '陈七', '上海');

INSERT INTO orders (order_id, user_id, amount, order_timestamp, status) VALUES
(101, 1, 100.00, 1755684000000, 'completed'),
(102, 1, 200.00, 1755786600000, 'completed'),
(103, 2, 150.00, 1755786600000, 'canceled'),
(104, 2, 300.00, 1755863700000, 'completed'),
(105, 3, 50.00,  1755863700000, 'completed'),
(106, 4, NULL,    1755950400000, 'completed'),
(107, 5, 200.00, 1755950400000, 'completed');

users表：

image-xhsu.png

orders表：

image-rikw.png

​ 

​ 

1、聚合函数

①、计数 COUNT

  • 查询订单总数
SELECT COUNT(*) AS order_num FROM cmge_test.orders;

image-efld.png

  • 查询有效订单数（只查询成功支付的状态）
SELECT COUNT(*) AS order_num FROM cmge_test.orders WHERE status = "completed";

image-msnq.png

​ 

②、求和 SUM

  • 查询所有有效订单之和
SELECT SUM(amount) AS order_amount_total FROM cmge_test.orders WHERE status = "completed";

注意：NULL 不参与计算

image-jhkj.png

​ 

③、平均数 AVG

  • 查询有效的平均订单金额
SELECT AVG(amount) AS order_avg_amount FROM cmge_test.orders WHERE status = "completed";

注意：NULL 不参与计算，也就是850/5=170

image-abxj.png

​ 

④、最大值 MAX

  • 查询最高有效单价
SELECT MAX(amount) AS order_max_amount FROM cmge_test.orders WHERE status = "completed";

image-zqnf.png

​ 

⑤、最小值 MIN

  • 查询最低有效单价
SELECT MIN(amount) AS order_max_amount FROM cmge_test.orders WHERE status = "completed";

注意：NULL 不参与计算

image-avmd.png

​ 

分组聚合 group by

原则：没有在 GROUP BY 里的字段，不能放在 SELECT 中！！！

  • 按user_id统计用户有效订单总额
SELECT
	SUM( amount ) AS order_amount_total,
	user_id 
FROM
	cmge_test.orders 
WHERE
	STATUS = "completed" 
	AND amount IS NOT NULL 
GROUP BY
	user_id;

image-gwyf.png

​ 

对分组内容进行过滤 HAVING

  • 按user_id统计用户有效订单总额，且订单总和大于200的
SELECT
	SUM( amount ) AS order_amount_total,
	user_id 
FROM
	cmge_test.orders 
WHERE
	STATUS = "completed" 
	AND amount IS NOT NULL 
GROUP BY
	user_id 
HAVING
	SUM( amount ) > 200;

image-qadx.png

​ 

​ 

2、多表查询

连接查询

​ 

内连接：

解释： INNER JOIN 只返回两个表中都有匹配记录的数据。

​ 

a、查询已完成订单的用户信息

SELECT
	u.NAME,
	o.amount,
	o.STATUS 
FROM
	users u
	INNER JOIN cmge_test.orders o ON u.user_id = o.user_id;

image-zckp.png

​ 

b、所有有效订单的用户信息

SELECT
	u.user_id,
	u.`name`,
	u.city ,
	o.order_id,
	o.amount
FROM
	cmge_test.users u
	INNER JOIN cmge_test.orders o ON o.user_id = u.user_id 
WHERE
	o.`status` = "completed" 
	AND o.amount > 0

image-kvkb.png

​ 

​ 

交叉连接：

解释：CROSS JOIN 是 INNER JOIN 的一个特殊形式 —— **没有 ON 条件的内连接！ **

CROSS JOIN 会将左表的每一行与右表的每一行进行组合，生成“笛卡尔积”（Cartesian Product）

示例：

表A：

color
红
蓝

表B：

size
S
M
L

执行sql后会得到：

SELECT * FROM colors CROSS JOIN sizes;
color size
红 S
红 M
红 L
蓝 S
蓝 M
蓝 L

​ 

​ 

左连接：

解释： LEFT JION 以左表为基准，返回左表中的所有记录；对于右表，仅当其记录满足 ON 条件时才被匹配进来。如果右表没有匹配的记录，则对应字段用 NULL 填充。（简单地说：返回 左表所有记录，右表没有匹配的用 NULL 填充）

技巧： 想要查“每个用户”的订单数据，我们要以“用户”为基准，所以左表是users表！

​ 

a、查找注册但未下单的用户

准备：先往users表插入一条数据。

INSERT INTO `cmge_test`.`users`(`user_id`, `name`, `city`) VALUES (6, '小明', '广州');

SELECT
	u.user_id,
	u.`name`,
	u.city 
FROM
	cmge_test.users u
	LEFT JOIN cmge_test.orders o ON o.user_id = u.user_id 
WHERE
	o.user_id IS NULL

image-bffg.png

​ 

b、查询有订单但用户信息缺失的“脏数据”

准备：插入一条脏数据

INSERT INTO `cmge_test`.`orders`(`order_id`, `user_id`, `amount`, `order_timestamp`, `status`) VALUES (108, 9, 1000.00, 1755950400000, 'completed');


SELECT
	* 
FROM
	cmge_test.orders o
	LEFT JOIN cmge_test.users u ON u.user_id = o.user_id 
WHERE
	u.user_id IS NULL

image-pdwb.png

​ 

c、查询每个用户的订单总金额（含零订单用户）

SELECT
	SUM( o.amount ) AS total_amount,
	u.user_id,
	u.`name`,
	u.city 
FROM
	cmge_test.users u
	LEFT JOIN cmge_test.orders o ON o.user_id = u.user_id 
GROUP BY
	u.user_id,
	u.`name`,
	u.city

image-gimi.png

​ 

d、查询每个用户的最近一次订单时间（含未下单用户）

SELECT
	u.user_id,
	u.`name`,
	MAX( o.order_timestamp ) AS last_order_time 
FROM
	cmge_test.users u
	LEFT JOIN cmge_test.orders o ON o.user_id = u.user_id 
GROUP BY
	u.user_id,
	u.`name` 
ORDER BY
	last_order_time DESC

image-yfpg.png

​ 

​ 

右连接：

解释： RIGHT JION以右表为基准，返回右表中的所有记录；对于左表，仅当其记录满足 ON 条件时才被匹配进来。如果左表没有匹配的记录，则对应字段用 NULL 填充。（简单地说：返回 右表所有记录，左表没有匹配的用 NULL 填充）

技巧：少用，甚至不用，完全能用左连接代替！！！“从左到右”，即左为主表的思维更适合阅读。

​ 

💡 思考：

啥时候用内连接，啥时候用左连接？

“要某个表的全部数据时，就放左边，用 LEFT JOIN”；“只看两边都有的，就用 INNER JOIN”

流程：

  1. 想查谁？ → 把它作为左表
  2. 要不要包含“没匹配的”？
    → 要 → LEFT JOIN
    → 不要 → INNER JOIN

INNER JOIN：

  • 查“所有完成订单的用户信息”（只看有效订单）
  • 查“下单金额大于 0 但状态不是 completed 的订单”
  • 查“北京用户的订单总金额”
  • 查“Alice 的所有订单，按时间倒序排列”
  • 查“订单金额最高的用户是谁？”

LEFT JOIN：

  • 查“每个用户的订单总金额”（含未下单用户）
  • 查“注册但未下单的用户”
  • 查“有订单但用户信息缺失的脏数据”
  • 查“每个用户的最近一次下单时间”（含未下单用户）
  • 查“每个城市的用户数和订单数”（含零订单城市）

​ 

​ 

子查询

解释：子查询是指在一个 SQL 查询语句中嵌套另一个查询语句。内部的查询称为“子查询”或“内层查询”，外部的查询称为“主查询”或“外层查询”。

子查询通常出现在 SELECT、FROM、WHERE 或 HAVING 子句中。

场景 是否适合用子查询
查询依赖另一个查询的结果 ✅ 非常适合
需要先聚合再筛选（如“高于平均值”） ✅ 推荐

准备数据：

-- 学生表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    class VARCHAR(20)  -- 班级，如 '高一(1)班'
);

-- 课程表
CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL,
    teacher VARCHAR(50)
);

-- 成绩表（关联学生和课程）
CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    score DECIMAL(5,2),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- 插入学生数据
INSERT INTO students (name, age, class) VALUES
('张三', 16, '高一(1)班'),
('李四', 17, '高一(1)班'),
('王五', 16, '高一(2)班'),
('赵六', 17, '高一(2)班'),
('钱七', 16, '高一(1)班');

-- 插入课程数据
INSERT INTO courses (course_name, teacher) VALUES
('数学', '王老师'),
('语文', '李老师'),
('英语', '张老师'),
('物理', '刘老师');

-- 插入成绩数据
INSERT INTO scores (student_id, course_id, score) VALUES
(1, 1, 88.5),  -- 张三 数学 88.5
(1, 2, 92.0),  -- 张三 语文 92.0
(2, 1, 76.0),  -- 李四 数学 76.0
(2, 3, 85.5),  -- 李四 英语 85.5
(3, 1, 95.0),  -- 王五 数学 95.0
(3, 4, 80.0),  -- 王五 物理 80.0
(4, 2, 73.0),  -- 赵六 语文 73.0
(5, 1, 90.0),  -- 钱七 数学 90.0
(5, 3, 88.0);  -- 钱七 英语 88.0

​ 

a、查询数学课程的平均分是多少？

SELECT
	AVG( score ) AS avg_score 
FROM
	cmge_test.scores 
WHERE
	course_id = ( SELECT id FROM cmge_test.courses WHERE course_name = '数学' )

但其实更推荐inner jion，效率更高：

SELECT
	AVG( s.score ) AS avg_score 
FROM
	cmge_test.scores s
	INNER JOIN cmge_test.courses c ON c.id = s.course_id 
WHERE
	c.course_name = "数学"

​ 

b、查询选修了“数学”或“语文”课程的学生姓名。

SELECT *
FROM cmge_test.students 
WHERE id IN (
    SELECT student_id 
    FROM cmge_test.scores 
    WHERE course_id IN (
        SELECT id 
        FROM cmge_test.courses 
        WHERE course_name IN ("数学", "语文")
    )
);

inner jion 的写法：

SELECT 
    c.course_name,
    stu.name,
    stu.id 
FROM 
    cmge_test.courses c 
    INNER JOIN cmge_test.scores s ON s.course_id = c.id 
    INNER JOIN cmge_test.students stu ON stu.id = s.student_id 
WHERE 
    c.course_name IN ("数学", "语文");

​ 

c、查询每个课程的平均分，并只显示高于总平均分的课程名称。

SELECT
    a.*,
    b.avg_score
FROM
    cmge_test.courses a
    INNER JOIN (
        SELECT 
            course_id, 
            AVG(score) AS avg_score 
        FROM 
            cmge_test.scores 
        GROUP BY 
            course_id
    ) b ON b.course_id = a.id
WHERE
    b.avg_score > (
        SELECT 
            AVG(score) 
        FROM 
            cmge_test.scores
    )

​ 

d、查询每门课程中，成绩高于该课程平均分的学生姓名和成绩。

SELECT 
    stu.name,
    co.course_name,
    sc.score
FROM scores sc
JOIN students stu ON sc.student_id = stu.id
JOIN courses co ON sc.course_id = co.id
WHERE sc.score > (
    SELECT AVG(score)
    FROM scores s2
    WHERE s2.course_id = sc.course_id  -- 关联外部 course_id
);

但推荐下面的：

SELECT
	stu.`name`,
	s.student_id,
	s.score,
	c.course_name
FROM
	cmge_test.courses c
	INNER JOIN cmge_test.scores s ON s.course_id = c.id
	INNER JOIN cmge_test.students stu ON stu.id = s.student_id
	INNER JOIN ( SELECT AVG( score ) AS avg_score, course_id FROM cmge_test.scores GROUP BY course_id ) aa ON aa.course_id = s.course_id 
WHERE
	s.score > aa.avg_score

​ 

e、显示每个学生的姓名、数学成绩，以及数学课程的平均分。

SELECT 
    s.name,
    sc.score AS math_score,
    (SELECT AVG(score) FROM scores 
     WHERE course_id = (
         SELECT id FROM courses WHERE course_name = '数学'
     )) AS avg_math
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
WHERE c.course_name = '数学';

推荐下面的，借助交叉查询将平均分传递给每一行：

SELECT
	stu.NAME,
	stu.id,
	s.score AS math_score,
	math_avg.avg_score 
FROM
	cmge_test.students stu
	INNER JOIN cmge_test.scores s ON s.student_id = stu.id
	INNER JOIN cmge_test.courses c ON c.id = s.course_id
	CROSS JOIN (
	SELECT
		AVG( s.score ) AS avg_score 
	FROM
		cmge_test.courses c
		INNER JOIN cmge_test.scores s ON s.course_id = c.id 
	WHERE
		c.course_name = "数学" 
	) math_avg 
WHERE
	c.course_name = "数学" 
ORDER BY
	s.score DESC

​ 

​ 

3、去重与排序

DISTINCT 关键字

解释：DISTINCT 用于去除查询结果中的重复行，只保留唯一值。（通常用于简单去重）

假设有一张 students 表：

id name class age
1 张三 一班 18
2 李四 一班 17
3 王五 二班 18
4 赵六 一班 18

​ 

a、查所有班级（去重）

SELECT DISTINCT class FROM students;

​ 

b、查“年龄+班级”的唯一组合

SELECT DISTINCT age, class FROM students;

重复的 “18，一班”会去掉一个。

​ 

c、查不同班级的数量

SELECT COUNT(DISTINCT class) FROM students;

​ 

​ 

ORDER BY

解释： ORDER BY 用于对查询结果进行排序，支持升序（ASC）和降序（DESC）。

​ 

a、按年龄降序（从大到小）

SELECT name, age FROM students ORDER BY age DESC;

​ 

b、查所有班级，并按班级名排序

SELECT DISTINCT class FROM students ORDER BY class;

​ 

c、分页 + 排序（常用组合）

-- 每页 10 条，查第 2 页
SELECT name, age FROM students ORDER BY age DESC LIMIT 10 OFFSET 10;

​ 

​ 

4、Null值处理

解释：NULL 表示 “未知”或“缺失”的值，不是 0，也不是空字符串 ''

判断是否为空必须使用 IS NULL 或者 IS NOT NULL

​ 

处理 NULL 的函数

​ 

(1) IFNULL(expr, replacement)

-- 如果 age 为 NULL，显示 0
SELECT name, IFNULL(age, 0) AS age FROM students;

​ 

(2) COALESCE(value1, value2, ..., valueN)

返回第一个非 NULL 的值

-- 如果 age 为 NULL，尝试用 backup_age，再不行用 0
SELECT name, COALESCE(age, backup_age, 0) AS age FROM students;

​ 

(3) NULLIF(expr1, expr2)

-- 如果 age = 0，认为是无效数据，转为 NULL
SELECT name, NULLIF(age, 0) AS age FROM students;
0
mysql
版权归属： 一朵云
本文链接： http://localhost:8090/archives/mysql----guan-yu-cha-xun-na-dian-er-shi
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区