MySQL 8.0实战：50道经典练习题解析（附完整SQL脚本）

MySQL 8.0实战：50道经典练习题深度解析与性能优化指南

在数据库学习的道路上，SQL查询能力的提升往往需要大量实践。这套针对MySQL 8.0的50道经典练习题，不仅涵盖了基础查询到高级分析的全方位技能点，更融入了MySQL 8.0特有的窗口函数、CTE等现代SQL特性。我们将通过真实的教学数据库模型，带你从零开始构建完整的SQL思维体系。

1. 环境准备与数据建模

1.1 数据库初始化与表结构设计

我们先创建一个教学管理系统的经典数据模型，包含学生、课程、教师和成绩四个核心实体：

-- MySQL 8.0中建议使用显式字符集
CREATE DATABASE school_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE school_db;

-- 学生表（增加邮箱字段以适应现代需求）
CREATE TABLE Student (
    SId VARCHAR(10) PRIMARY KEY,
    Sname VARCHAR(10) NOT NULL,
    Sage DATE NOT NULL,
    Ssex ENUM('男','女') NOT NULL,
    Semail VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 教师表（增加职称字段）
CREATE TABLE Teacher (
    TId VARCHAR(10) PRIMARY KEY,
    Tname VARCHAR(10) NOT NULL,
    Ttitle VARCHAR(20)
) ENGINE=InnoDB;

-- 课程表（增加学分字段）
CREATE TABLE Course (
    CId VARCHAR(10) PRIMARY KEY,
    Cname VARCHAR(10) NOT NULL,
    TId VARCHAR(10),
    Credit TINYINT,
    FOREIGN KEY (TId) REFERENCES Teacher(TId)
) ENGINE=InnoDB;

-- 成绩表（使用复合主键）
CREATE TABLE SC (
    SId VARCHAR(10),
    CId VARCHAR(10),
    score DECIMAL(5,2) CHECK (score BETWEEN 0 AND 100),
    PRIMARY KEY (SId, CId),
    FOREIGN KEY (SId) REFERENCES Student(SId),
    FOREIGN KEY (CId) REFERENCES Course(CId)
) ENGINE=InnoDB;

注意：MySQL 8.0默认使用InnoDB引擎，支持事务和外键约束，这是与老版本的重要区别

1.2 测试数据插入优化

使用事务批量插入可以提高数据初始化效率：

START TRANSACTION;

-- 学生数据
INSERT INTO Student VALUES
('01','赵雷','1990-01-01','男','zhaolei@example.com'),
('02','钱电','1990-12-21','男','qiandian@example.com'),
('03','孙风','1990-05-20','男','sunfeng@example.com');

-- 教师数据
INSERT INTO Teacher VALUES
('01','张三','教授'),
('02','李四','副教授'),
('03','王五','讲师');

COMMIT;

2. 基础查询进阶技巧

2.1 条件查询的现代写法

比较课程"01"和"02"成绩的三种场景，展示MySQL 8.0的CTE特性：

-- 使用CTE(Common Table Expression)提高可读性
WITH 
course01 AS (SELECT SId, score FROM SC WHERE CId = '01'),
course02 AS (SELECT SId, score FROM SC WHERE CId = '02')
SELECT s.*, c01.score AS score01, c02.score AS score02
FROM Student s
JOIN course01 c01 ON s.SId = c01.SId
JOIN course02 c02 ON s.SId = c02.SId
WHERE c01.score > c02.score;

2.2 聚合函数实战

计算学生平均成绩并筛选，注意NULL处理：

SELECT s.SId, s.Sname, 
       ROUND(AVG(sc.score),2) AS avg_score,
       COUNT(sc.CId) AS course_count
FROM Student s
LEFT JOIN SC sc ON s.SId = sc.SId
GROUP BY s.SId, s.Sname
HAVING avg_score >= 60 OR avg_score IS NULL
ORDER BY avg_score DESC;

3. 高级查询与窗口函数

3.1 排名计算的四种方式

MySQL 8.0新增的窗口函数大大简化了排名计算：

SELECT 
    SId, CId, score,
    ROW_NUMBER() OVER(PARTITION BY CId ORDER BY score DESC) AS row_num,
    RANK() OVER(PARTITION BY CId ORDER BY score DESC) AS rank_val,
    DENSE_RANK() OVER(PARTITION BY CId ORDER BY score DESC) AS dense_rank_val,
    PERCENT_RANK() OVER(PARTITION BY CId ORDER BY score DESC) AS percent_rank
FROM SC;

3.2 成绩分段统计

使用CASE表达式实现多条件统计：

SELECT 
    CId,
    COUNT(*) AS total_students,
    SUM(CASE WHEN score >= 85 THEN 1 ELSE 0 END) AS excellent,
    SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) AS good,
    SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) AS pass,
    SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS fail,
    ROUND(100 * AVG(CASE WHEN score >= 60 THEN 1 ELSE 0 END), 2) AS pass_rate
FROM SC
GROUP BY CId
ORDER BY pass_rate DESC;

4. 性能优化实战

4.1 索引策略分析

为查询性能添加合适的索引：

-- 为经常查询的条件创建索引
ALTER TABLE SC ADD INDEX idx_cid_score (CId, score);
ALTER TABLE Student ADD INDEX idx_name (Sname);

-- 查看查询执行计划
EXPLAIN SELECT * FROM Student WHERE Sname LIKE '李%';

4.2 查询重写优化

比较两种查询写法的性能差异：

-- 原始写法（可能产生临时表）
SELECT * FROM Student 
WHERE SId IN (SELECT SId FROM SC WHERE score > 80);

-- 优化写法（使用JOIN）
SELECT DISTINCT s.* FROM Student s
JOIN SC sc ON s.SId = sc.SId AND sc.score > 80;

5. 复杂业务场景解决方案

5.1 查询学习完全相同课程的学生

WITH 
student_courses AS (
    SELECT SId, GROUP_CONCAT(CId ORDER BY CId) AS course_pattern
    FROM SC
    GROUP BY SId
)
SELECT s1.SId AS student1, s2.SId AS student2
FROM student_courses s1
JOIN student_courses s2 ON s1.course_pattern = s2.course_pattern
WHERE s1.SId < s2.SId;

5.2 动态年龄计算

考虑闰年和月份因素的精确年龄计算：

SELECT 
    SId, Sname, Sage,
    TIMESTAMPDIFF(YEAR, Sage, CURDATE()) - 
    CASE WHEN DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(Sage, '%m%d') 
         THEN 1 ELSE 0 END AS exact_age
FROM Student;

6. 实战案例：学生成绩分析系统

6.1 综合成绩单生成

SELECT 
    s.SId, s.Sname,
    GROUP_CONCAT(c.Cname SEPARATOR ', ') AS courses,
    GROUP_CONCAT(sc.score SEPARATOR