开发者空间里玩转MySQL之表数据操作 - 查询

1 概述

1.1 案例介绍

数据库操作是软件开发的基础核心，MySQL凭借其高效稳定、易用可靠的特点，广泛应用于电商、社交、企业系统等各类业务场景，是开发者必备的关键技能。 本案例在开发者空间开发桌面，通过标准SQL语句实现MySQL数据的查询基础操作，帮助开发者快速上手数据库开发。

1.2 适用对象

• 企业
• 个人开发者
• 高校学生

1.3 案例时间

本案例总时长预计40分钟。

1.4 案例流程

{{{width="40%" height="auto"}}}

说明： ① 领取空间开发桌面； ② 在空间开发桌面终端进入Mysql； ③ 进行数据库的表数据的查询操作；

1.5 资源总览

本案例预计花费总计0元。 |资源名称 |规格 |单价（元）| 时长（分钟）| |---------------|---------------|---------------|---------------| |开发者空间 - 空间开发桌面|ARM| 4 vCPUs 8GB | Ubuntu | 0 |40|

2 表数据查询操作

数据查询是数据库最核心的功能之一。本文将以我们设计的教务系统数据库为基础，详细讲解 SELECT 语句的各种用法，包括基本查询、条件筛选、分组统计、排序分页、连接查询和子查询等高级功能。我们将结合实际场景，展示如何高效地从数据库中获取所需信息。

2.1 准备工作

为顺利进行案例讲解，这里预先预置了一些数据，需要先将数据插入到数据表中； 打开新终端：

# 进入指定文件夹（可自定义）
cd /home/developer/Downloads

# 下载资源文件
wget https://dtse-mirrors.obs.cn-north-4.myhuaweicloud.com/case/0040/teacher.sql
wget https://dtse-mirrors.obs.cn-north-4.myhuaweicloud.com/case/0040/course.sql
wget https://dtse-mirrors.obs.cn-north-4.myhuaweicloud.com/case/0040/student_data.sql
wget https://dtse-mirrors.obs.cn-north-4.myhuaweicloud.com/case/0040/enrollment_inserts.sql

在登录MySQL的终端：

# 登录mysql
sudo mysql -u root -p

# 选择数据库
use university;

# 依次运行
Source /home/developer/Downloads/teacher.sql
Source /home/developer/Downloads/course.sql
Source /home/developer/Downloads/student_data.sql
Source /home/developer/Downloads/enrollment_inserts.sql

插入后，更新下课程表的选课人数字段，保证数据统一：

UPDATE course c
SET c.current_enrollment = (
    SELECT COUNT(*) 
    FROM enrollment e 
    WHERE e.course_id = c.course_id
);

2.2 SELECT语句

SELECT 语句是 SQL 中最常用的命令，用于从数据库表中检索数据。 基本语法：

SELECT [DISTINCT] 列名1, 列名2, ...
FROM 表名
[WHERE 条件]
[GROUP BY 分组列]
[HAVING 分组条件]
[ORDER BY 排序列 [ASC|DESC]]
[LIMIT 偏移量, 行数];

拓展条件，如分组，排序，偏移等在后面讲解； 最简单的无条件全列查询语句示例：

-- 查询所有学生基本信息
SELECT * FROM student;

-- 查询所有教师基本信息
SELECT * FROM teacher;

-- 查询所有课程基本信息
SELECT * FROM course;

-- 查询所有选课基本信息
SELECT * FROM enrollment;

操作结果：

2.3 投影查询

投影查询是最基础也是最重要的操作之一，它指的是从表中选择特定的列而非全部列的操作。 基本语法：

SELECT列名1, 列名2, ...
FROM 表名

与全列查询相比，投影查询的特点是只读取指定列的数据，I/O操作更少，内存占用更小，网络传输效率高； 投影查询的适用场景： - 性能敏感场景：大型表查询（百万级以上记录）、频繁执行的查询且网络带宽有限等情况 - 安全需求场景：需要隐藏敏感列（如密码、身份证号）或不同角色看到不同数据视图等 - 报表生成场景：定制化报表输出、数据导出需求或第三方系统接口等 - 视图创建的场景

实践中的建议： - 明确指定列名 - 按需选择列，只选择业务需要的列 - 合理使用别名：复杂表达式必须使用别名，提高结果集可读性，方便后续处理 - 注意列顺序：保持一致的列顺序有助于查询缓存，重要列优先显示，符合业务逻辑的排序 - 性能考虑：宽表（列多的表）特别需要注意，TEXT/BLOB类型列谨慎选择，索引列优先选择可提高性能

语句示例：

-- 课程表关键信息查询
SELECT course_id AS 课程编号,
       course_name AS 课程名称,
       credits AS 学分,
       semester AS 开课学期
FROM course;

操作结果：

2.4 选择查询

WHERE子句是SQL中最基础也是最重要的过滤机制，它通过指定条件来限制查询返回的行数，只返回满足条件的记录。 基本语法：

SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件表达式;

WHERE 子句与全表查询的对比： |特性 |WHERE 查询 |全表查询| |---------------|---------------|---------------| |I/O 操作 |只读取符合条件的行 |读取所有行| |内存占用 |仅加载必要数据 |加载全部数据| |网络传输 |数据量小 |数据量大| |执行速度 |通常更快 |通常较慢|

语句对比：

-- 全表查询（不推荐）
SELECT * FROM students;

-- WHERE 条件查询（推荐）
SELECT * FROM students WHERE department = '材料科学与工程学院';

WHER子句的适用场景： - 精确查询场景：精确匹配单个值/多个值 - 范围查询场景：数值范围、日期范围等 - 模糊查询场景：前缀匹配（LIKE '前缀%'）、后缀（LIKE '%后缀'）和包含（'%对象%'、'%对象1%对象2%'）等 - 复杂的条件组合场景

实践中的建议： - 总是为常用查询条件创建适当索引 - 避免在索引列上使用函数 - 考虑使用分区表处理大数据量表 - 定期优化和重写低效查询 - 条件子语句建议：条件表达式优化（如使用EXISTS代替IN，避免使用OR（可改用UNION）），正确的null值检查

语句示例：

-- 查询学分大于3的课程
SELECT course_id, course_name, credits
FROM course
WHERE credits > 3.0;

操作结果：

2.5 分组查询和统计计算

分组查询(GROUP BY)和统计计算是SQL中强大的数据分析工具，基于上述表结构，主要可以实现基础分组能力、统计计算能力、多维度分析能力； 基本语法：

SELECT 
    列名1, 
    列名2,
    ...,
    聚合函数(列名) 
FROM 
    表名
[WHERE 条件]
GROUP BY 
    列名1, 列名2, ...
[HAVING 分组后条件]
[ORDER BY 列名 [ASC|DESC]];

常用聚合函数： - COUNT() - 计数 - SUM() - 求和 - AVG() - 平均值 - MAX() - 最大值 - MIN() - 最小值 - GROUP_CONCAT() - 连接字符串

分组查询和统计计算适用的场景： - 数据汇总：需要对某个字段进行计数、求和、平均值、最大值或最小值等汇总操作 - 数据分类：需要将数据按照某个字段的值进行分类并显示各类别的信息 - 数据排序：需要根据某个字段的值对数据进行排序时 - 筛选特定分组：需要筛选出满足特定条件的分组时 - 多表连接：需要将多个表按照某个字段进行连接时

实践中的建议： - 索引优化：为常用分组列创建索引 - 查询优化：先使用WHERE过滤再分组，减少处理数据量，避免在GROUP BY列上使用函数，会破坏索引使用，对大表统计考虑使用采样查询或定时任务 - 结果优化：使用HAVING过滤分组结果，而非WHERE，限制返回结果数量避免大数据集，复杂统计可考虑使用物化视图存储 - 替代方案：大数据量时考虑使用窗口函数替代部分分组查询，频繁使用的统计结果可考虑定期预计算

语句示例：

-- 统计各学期课程的选课人数和平均成绩
SELECT 
    semester AS '学期',
    COUNT(*) AS '选课总人数',
    SUM(CASE WHEN grade IS NOT NULL THEN 1 ELSE 0 END) AS '已出成绩人数',
    AVG(grade) AS '平均成绩',
    MAX(grade) AS '最高分',
    MIN(grade) AS '最低分'
FROM 
    enrollment
GROUP BY 
    semester
ORDER BY 
semester DESC;

-- 统计各学分课程的分布情况
SELECT 
    credits AS '学分',
    COUNT(*) AS '课程数量',
    SUM(course_capacity) AS '总容量',
    SUM(current_enrollment) AS '当前选课人数',
    ROUND(SUM(current_enrollment) * 100.0 / SUM(course_capacity), 2) AS '选课率(%)'
FROM 
    course
WHERE 
    course_capacity > 0
GROUP BY 
    credits
ORDER BY 
    credits;

操作结果：

2.6 排序查询和限制查询结果的数量

排序查询(ORDER BY) - 对查询结果按照一个或多个列进行升序(ASC)或降序(DESC)排列 - 支持多列排序，优先级从左到右 - 可以结合表达式或函数进行排序

基本语法：

SELECT 列名1, 列名2, ...
FROM 表名
[WHERE 条件]
ORDER BY 
    排序列1 [ASC|DESC], 
    排序列2 [ASC|DESC], 
    ...

限制结果数量(LIMIT) - 限制返回的记录条数 - 支持分页查询(通过OFFSET或LIMIT x,y语法) - 常与排序一起使用获取"前N条"记录

基本语法：

-- 语法1：限制返回行数
SELECT 列名1, 列名2, ...
FROM 表名
[WHERE 条件]
[ORDER BY 子句]
LIMIT 行数;

-- 语法2：分页查询（跳过offset行，返回count行）
SELECT 列名1, 列名2, ...
FROM 表名
[WHERE 条件]
[ORDER BY 子句]
LIMIT offset, count;

-- 替代语法（MySQL 8.0+推荐）
SELECT 列名1, 列名2, ...
FROM 表名
[WHERE 条件]
[ORDER BY 子句]
LIMIT count OFFSET offset;

组合使用基本语法：

SELECT 列名1, 列名2, ...
FROM 表名
[WHERE 条件]
[GROUP BY 子句]
[HAVING 分组条件]
ORDER BY 排序列1 [ASC|DESC], 排序列2 [ASC|DESC], ...
LIMIT (页码-1)*每页数量, 每页数量;

分组查询和统计计算适用的场景： - 数据展示场景：信息表的分页展示、排序展示等 - 统计分析场景：TOP数据的展示、最多/大/少/小数据的查找等 - 业务处理场景：处理队列数据时按优先级排序、批量操作时限制每次处理数量、导出数据时分批处理等

实践中的建议： - 索引优化建议：为常用排序字段创建索引 - 性能优化建议：避免对大表进行全表扫描后排序，分页查询深度较大时考虑使用"记住上次最大值"方法，排序字段尽量使用索引覆盖 - 语法使用建议：明确指定ASC/DESC避免依赖默认值，分页查询使用LIMIT offset count语法更清晰，多列排序时注意字段顺序对结果的影响

语句示例：

-- 查询选课人数最多的8门课程
SELECT 
    course_id AS '课程ID',
    course_name AS '课程名称',
    current_enrollment AS '选课人数'
FROM 
    course
ORDER BY 
    current_enrollment DESC
LIMIT 8;

-- 查询成绩最高的15条选课记录
SELECT 
    enrollment_id AS '选课记录ID',
    student_id AS '学号',
    course_id AS '课程ID',
    grade AS '成绩'
FROM 
    enrollment
WHERE 
    grade IS NOT NULL
ORDER BY 
    grade DESC
LIMIT 15;

操作结果：

2.7 连接查询

2.7.1 交叉连接

交叉连接是关系代数中最基础的连接操作，生成两个表的笛卡尔积，即第一个表的每一行与第二个表的每一行组合。MySQL 8.0对交叉连接没有特别的语法增强，但优化器能更好地处理这类查询。 基础语法：

-- 显式语法（推荐）
SELECT t1.column1, t2.column2
FROM table1 t1
CROSS JOIN table2 t2;

-- 隐式语法（不推荐）
SELECT t1.column1, t2.column2
FROM table1 t1, table2 t2;

交叉连接适用的场景： - 组合生成：需要生成所有可能的组合时（如商品颜色和尺寸组合） - 小表分析：数据量小的表进行全组合分析（<1000行） - 特殊计算：某些数学计算或矩阵运算需要全量组合

实践中的建议： - 性能警告：结果集行数为两表行数乘积（如1000×1000=1,000,000行） - 替代方案：考虑使用应用程序生成组合或预计算存储 - 索引无效：交叉连接不使用索引，全表扫描不可避免 - 明确需求：确保业务确实需要所有组合

语句示例：

-- 教师与学期的全组合（生成可能的授课学期）
SELECT 
    t.teacher_id AS '教师ID',
    t.title AS '职称',
    c.semester AS '学期'
FROM 
    teacher t
CROSS JOIN 
    (SELECT DISTINCT semester FROM course) c
ORDER BY 
t.teacher_id, c.semester;

-- 生成学生年龄与课程学分的全组合统计
SELECT 
    s.age_group AS '年龄区间',
    c.credit_range AS '学分区间',
    COUNT(*) AS '理论组合数'
FROM 
    (SELECT 
        CASE 
            WHEN age < 20 THEN '20岁以下'
            WHEN age BETWEEN 20 AND 22 THEN '20-22岁'
            WHEN age BETWEEN 23 AND 25 THEN '23-25岁'
            ELSE '25岁以上'
        END AS age_group
     FROM student) s
CROSS JOIN 
    (SELECT 
        CASE 
            WHEN credits < 2.0 THEN '2学分以下'
            WHEN credits BETWEEN 2.0 AND 3.0 THEN '2-3学分'
            ELSE '3学分以上'
        END AS credit_range
     FROM course) c
GROUP BY 
    s.age_group, c.credit_range
ORDER BY 
    s.age_group, c.credit_range;

操作结果：

2.7.2 内连接

内连接基于连接谓词返回两个表中满足条件的行集合。MySQL 8.0优化器对复杂内连接有更好的处理能力，支持哈希连接算法。 基础语法：

-- 标准语法（推荐）
SELECT t1.column1, t2.column2
FROM table1 t1
[INNER] JOIN table2 t2 ON t1.key = t2.key
[WHERE conditions];

-- USING语法（当列名相同时）
SELECT t1.column1, t2.column2
FROM table1 t1
JOIN table2 t2 USING (common_column);

交叉连接适用的场景： - 关联查询：需要查询相关联的数据 - 精确匹配：只关心有严格匹配关系的记录 - 多表关联：3个及以上表的关联查询（性能优于子查询）

实践中的建议： - 索引优化：为连接字段建立复合索引（如ALTER TABLE添加INDEX） - 执行计划：使用EXPLAIN分析复杂连接查询 - 避免歧义：明确指定表别名和列来源（如t1.id而非直接id） - 替代方案：对于简单查询，考虑使用WHERE子句连接

语句示例：

-- 查询学生选课记录及课程信息
SELECT 
    s.student_id AS '学号',
    s.department AS '学生院系',
    e.course_id AS '课程ID',
    c.course_name AS '课程名称',
    e.grade AS '成绩',
    e.semester AS '学期'
FROM 
    enrollment e
INNER JOIN 
    student s ON e.student_id = s.student_id
INNER JOIN 
    course c ON e.course_id = c.course_id
WHERE 
    e.grade IS NOT NULL
ORDER BY 
s.student_id, e.semester;

-- 查询特定学期选课学生的详细信息及课程信息
SELECT 
    s.student_id AS '学号',
    s.age AS '年龄',
    s.gender AS '性别',
    s.department AS '学生院系',
    c.course_id AS '课程ID',
    c.course_name AS '课程名称',
    t.title AS '授课教师职称'
FROM 
    enrollment e
INNER JOIN 
    student s ON e.student_id = s.student_id
INNER JOIN 
    course c ON e.course_id = c.course_id
INNER JOIN 
    teacher t ON c.teacher_id = t.teacher_id
WHERE 
e.semester = '2025 spring'
ORDER BY 
    s.department, s.student_id;

操作结果：

2.7.3 外连接

外连接保留至少一个表中的所有行，即使另一个表中没有匹配。MySQL 8.0优化了外连接处理，支持更高效的执行计划。 基础语法：

-- 左外连接（保留左表所有行）
SELECT t1.column1, t2.column2
FROM table1 t1
LEFT [OUTER] JOIN table2 t2 ON t1.key = t2.key;

-- 右外连接（保留右表所有行）
SELECT t1.column1, t2.column2
FROM table1 t1
RIGHT [OUTER] JOIN table2 t2 ON t1.key = t2.key;

交叉连接适用的场景： - 左外连接：主从表查询（如所有客户及其订单，包括无订单客户） - 右外连接：逆向关系查询（较少使用，通常用左连接替代） - 缺失分析：查找没有关联记录的行（WHERE t2.key IS NULL）

实践中的建议： - 主从设计：明确主表（LEFT JOIN左侧）和从表 - NULL处理：使用COALESCE或IFNULL处理可能为NULL的列 - 性能注意：外连接通常比内连接开销大 - 替代方案：考虑分步查询（先查主表，再批量查关联数据

语句示例：

-- 左外连接（LEFT JOIN）示例，查询所有学生及其选课信息（包括未选课的学生）
SELECT s.student_id, s.class, e.course_id, e.grade
FROM student s
LEFT JOIN enrollment e ON s.student_id = e.student_id;

-- 右外连接（RIGHT JOIN）示例，查询所有课程及其选课学生（包括无人选的课程）
SELECT c.course_id, c.course_name, e.student_id, e.grade
FROM enrollment e
RIGHT JOIN course c ON e.course_id = c.course_id;

-- 左外连接与聚合结合示例，统计每个学生的选课数量和平均成绩（包括未选课学生）
SELECT s.student_id, s.class, 
       COUNT(e.course_id) AS course_count,
       AVG(e.grade) AS avg_grade
FROM student s
LEFT JOIN enrollment e ON s.student_id = e.student_id
GROUP BY s.student_id, s.class;

操作结果：

2.8 子查询

2.8.1 IN子查询

IN 子查询用于判断某个值是否存在于子查询返回的结果集中，是 MySQL 中最常用的子查询类型之一。MySQL 8.0 对 IN 子查询进行了多项优化，包括半连接优化和物化优化。 基本语法：

-- 标准语法
SELECT 列名 FROM 表1 
WHERE 列名 IN (SELECT 列名 FROM 表2 WHERE 条件);

-- NOT IN 语法
SELECT 列名 FROM 表1 
WHERE 列名 NOT IN (SELECT 列名 FROM 表2 WHERE 条件);

适用场景： - 集合成员检查：检查值是否在某个集合中 - 多值过滤：替代多个 OR 条件的复杂查询 - 关联数据查询：查找与另一表有关联的记录

使用建议： - 索引优化：确保子查询的列有适当索引 - NULL 值注意：NOT IN 子查询对 NULL 值处理需特别小心 - 性能考虑：大数据集考虑使用 JOIN 替代 - EXISTS 对比：当子查询返回大量数据时，考虑使用 EXISTS

语句示例：

-- 查询当前学期选课人数超过课程容量90%的课程
SELECT c.course_id, c.course_name, c.course_capacity, c.current_enrollment
FROM course c
WHERE c.course_id IN (
    SELECT e.course_id
    FROM enrollment e
    WHERE e.semester = '2025 Spring'
    GROUP BY e.course_id
    HAVING COUNT(*) > 0.9 * (
        SELECT course_capacity 
        FROM course 
        WHERE course_id = e.course_id
    )
);

操作结果：

2.8.2 比较子查询

比较子查询将一个值与子查询返回的单个值进行比较，MySQL 8.0 支持 =, >, <, >=, <=, <> 等比较运算符。优化器能更好地处理这类子查询。 基本语法：

-- 标准语法
SELECT 列名 FROM 表1 
WHERE 列名 比较运算符 (SELECT 列名 FROM 表2 WHERE 条件);

-- 常用比较运算符
=, >, <, >=, <=, <>, !=

适用场景： - 单值比较：与聚合结果比较（如高于平均值的记录） - 极值查询：查找最大/最小值相关记录 - 层级比较：比较不同层级的指标

使用建议： - 确保单值：子查询必须返回单个值，否则会报错 - 聚合函数：常与 MAX, MIN, AVG 等聚合函数配合使用 - 索引使用：为比较列创建合适索引 - 替代方案：考虑使用 JOIN 或变量存储中间结果

语句示例：

-- 查询学分高于该教师所授课程平均学分的课程
SELECT c.course_id, c.course_name, c.credits, t.teacher_id, t.title
FROM course c
JOIN teacher t ON c.teacher_id = t.teacher_id
WHERE c.credits > (
    SELECT AVG(credits)
    FROM course
    WHERE teacher_id = c.teacher_id
);
-- 查询成绩高于该课程平均成绩的学生选课记录
SELECT e.student_id, e.course_id, e.grade, c.course_name
FROM enrollment e
JOIN course c ON e.course_id = c.course_id
WHERE e.grade > (
    SELECT AVG(grade)
    FROM enrollment
    WHERE course_id = e.course_id
    AND grade IS NOT NULL
);

操作结果：

2.8.3 EXISTS子查询

EXISTS 子查询检查子查询是否返回任何行，只返回布尔值(true/false)。MySQL 8.0 对 EXISTS 子查询进行了半连接优化，性能显著提升。 基本语法：

-- 标准语法
SELECT 列名 FROM 表1 
WHERE EXISTS (SELECT 1 FROM 表2 WHERE 关联条件);

-- NOT EXISTS 语法
SELECT 列名 FROM 表1 
WHERE NOT EXISTS (SELECT 1 FROM 表2 WHERE 关联条件);

适用场景： - 存在性检查：检查关联记录是否存在（如是否有订单的客户） - 复杂条件：替代复杂的 JOIN 或 IN 查询 - 性能敏感：当子查询表很大但匹配行很少时 - 相关子查询：需要引用外部查询的列时

使用建议： - SELECT 1：子查询中使用 SELECT 1 而非 SELECT * 提高性能 - 索引优化：确保关联条件列有索引 - 与 JOIN 比较：测试 EXISTS 和 JOIN 的性能差异 - NOT EXISTS：查找没有关联记录的查询首选

语句示例：

-- 查询成绩高于该学生所有课程平均成绩的选课记录
SELECT e.student_id, e.course_id, e.grade, c.course_name
FROM enrollment e
JOIN course c ON e.course_id = c.course_id
WHERE e.grade > (
    SELECT AVG(grade)
    FROM enrollment
    WHERE student_id = e.student_id
    AND grade IS NOT NULL
)
AND EXISTS (
    SELECT 1
    FROM enrollment e2
    WHERE e2.student_id = e.student_id
    AND e2.grade IS NOT NULL
);

操作结果：

2.8.4 特点比较：

子查询类型	返回要求	性能特点	适用场景	MySQL 8.0 优化
IN 子查询	多值	中等，可能被优化为半连接	集合成员检查	半连接优化、物化优化
比较子查询	单值	高效，如果子查询简单	单值比较	更好的执行计划生成
EXISTS 子查询	布尔值	高效，特别是匹配行少时	存在性检查	半连接优化、反连接优化

2.9 联合查询

联合查询(UNION)用于合并两个或多个SELECT语句的结果集，是MySQL中实现数据垂直合并的主要方式。MySQL 8.0对UNION查询进行了多项优化： - 性能提升：改进了临时表处理机制 - 哈希连接优化：对UNION结果的处理更高效 - CTE支持：可与WITH子句结合使用 - 执行计划改进：能生成更优的查询执行计划

基本语法：

-- 基础语法
SELECT 列1, 列2 FROM 表1 [WHERE 条件]
UNION [ALL | DISTINCT]
SELECT 列1, 列2 FROM 表2 [WHERE 条件]
[ORDER BY 子句]
[LIMIT 子句];

语法要点： - 每个SELECT语句必须有相同数量的列 - 对应列的数据类型必须兼容 - 列名以第一个SELECT语句的列名为准 - ORDER BY和LIMIT作用于整个UNION结果

适用场景： - 数据合并：合并结构相似的不同表数据 - 报表统计：生成综合报表 - 数据分片查询：查询分布在多个表中的数据 - 数据对比：比较不同数据集的差异

语句示例：

-- 查询各院系教师职称和各院系学生班级（合并统计）
SELECT department, title AS item, '教师职称' AS type
FROM teacher
WHERE title IS NOT NULL
UNION
SELECT department, class AS item, '学生班级' AS type
FROM student
WHERE class != '未分配'
ORDER BY department, type, item;

操作结果：