MySQL作为目前最常用的关系型数据库管理系统之一，广泛应用于各类信息化系统中，为了帮助初学者更好地掌握MySQL，本文将通过一些经典习题来解析MySQL的核心概念和操作技巧，这些习题涵盖了数据库设计、数据查询、表结构设计以及索引优化等方面，旨在全面提升读者的MySQL使用能力。

一、数据库设计与表结构

1、创建一个学生信息管理系统

创建三张表：学生表（Student）、课程表（Course）和成绩表（Score）。

   CREATE TABLE Student (
       SId VARCHAR(20) PRIMARY KEY,
       SName VARCHAR(20) NOT NULL DEFAULT '',
       SBirth DATE NOT NULL DEFAULT '1990-01-01',
       SSex VARCHAR(10) NOT NULL DEFAULT ''
   );
   CREATE TABLE Course (
       CId VARCHAR(20) PRIMARY KEY,
       CName VARCHAR(20) NOT NULL DEFAULT '',
       TId VARCHAR(20) NOT NULL,
       FOREIGN KEY (TId) REFERENCES Teacher(TId)
   );
   CREATE TABLE Score (
       SId VARCHAR(20),
       CId VARCHAR(20) NOT NULL,
       SScore INT(3),
       PRIMARY KEY (SId, CId),
       FOREIGN KEY (SId) REFERENCES Student(SId),
       FOREIGN KEY (CId) REFERENCES Course(CId)
   );

2、插入数据

向上述表中插入测试数据。

   INSERT INTO Student VALUES ('01', '赵雷', '1990-01-01', '男');
   INSERT INTO Student VALUES ('02', '钱电', '1990-12-21', '男');
   INSERT INTO Student VALUES ('03', '孙风', '1990-05-20', '男');
   INSERT INTO Student VALUES ('04', '李云', '1990-08-06', '男');
   INSERT INTO Student VALUES ('05', '周梅', '1991-12-01', '女');
   INSERT INTO Student VALUES ('06', '吴兰', '1992-03-01', '女');
   INSERT INTO Student VALUES ('07', '郑竹', '1989-07-01', '女');
   INSERT INTO Student VALUES ('08', '王菊', '1990-01-20', '女');
   INSERT INTO Course VALUES ('01', '语文', '02');
   INSERT INTO Course VALUES ('02', '数学', '01');
   INSERT INTO Course VALUES ('03', '英语', '03');
   INSERT INTO Score VALUES ('01', '01', 80);
   INSERT INTO Score VALUES ('01', '02', 90);
   INSERT INTO Score VALUES ('01', '03', 99);
   INSERT INTO Score VALUES ('02', '01', 70);
   INSERT INTO Score VALUES ('02', '02', 60);
   INSERT INTO Score VALUES ('02', '03', 80);
   INSERT INTO Score VALUES ('03', '01', 80);
   INSERT INTO Score VALUES ('03', '02', 80);
   INSERT INTO Score VALUES ('03', '03', 80);
   INSERT INTO Score VALUES ('04', '01', 50);
   INSERT INTO Score VALUES ('04', '02', 30);
   INSERT INTO Score VALUES ('04', '03', 20);
   INSERT INTO Score VALUES ('05', '01', 76);
   INSERT INTO Score VALUES ('05', '02', 87);
   INSERT INTO Score VALUES ('06', '01', 31);
   INSERT INTO Score VALUES ('06', '03', 34);
   INSERT INTO Score VALUES ('07', '02', 89);
   INSERT INTO Score VALUES ('07', '03', 98);

二、数据查询与过滤

1、查询"01"课程比"02"课程成绩高的学生信息及课程分数

   SELECT a.*, b.s_score AS score01, c.s_score AS score02
   FROM student a
   JOIN score b ON a.s_id = b.s_id AND b.c_id = '01'
   LEFT JOIN score c ON a.s_id = c.s_id AND c.c_id = '02' AND c.s_score < b.s_score;

2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

   SELECT SId, SName, AVG(SScore) AS AvgScore
   FROM Score
   GROUP BY SId, HAVING AvgScore >= 60;

3、查询所有同学的学编号、学生姓名、选课总数和总成绩

   SELECT a.SId, a.SName, COUNT(DISTINCT b.CId) AS course_count, total_score
   FROM Student a
   JOIN Score b ON a.SId = b.SId
   GROUP BY a.SId, a.SName;

三、高级查询与优化

1、统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比

   SELECT c.CName, c.CId,
          SUM(CASE WHEN sc.SScore BETWEEN 60 AND 100 THEN 1 ELSE 0 END) AS '60-100',
          SUM(CASE WHEN sc.SScore BETWEEN 0 AND 59 THEN 1 ELSE 0 END) AS '0-59'
     FROM Course c
     LEFT JOIN Score sc ON c.CId = sc.CId
     GROUP BY c.CId;

2、查询每门课程被选修的学生数

   SELECT c.CName, c.CId, COUNT(DISTINCT sc.SId) AS student_count
   FROM Course c
   LEFT JOIN Score sc ON c.CId = sc.CId
   GROUP BY c.CId;

3、查询有两门及其以上不及格课程的同学的学号和姓名

   SELECT a.SId, a.SName
   FROM Student a
   JOIN Score b ON a.SId = b.SId
   WHERE b.SScore < 60
   GROUP BY a.SId, a.SName;

四、综合练习与实战应用

1、查询学过“张三”老师讲授的所有课程的同学的信息

   SELECT DISTINCT a.SId, a.SName, a.SBirth, a.SSex
   FROM Student a
   JOIN Score b ON a.SId = b.SId
   JOIN Course c ON b.CId = c.CId
   WHERE c.TId IN (SELECT TId FROM Teacher WHERE TName = '张三');

2、查询没学过“张三”老师讲授课程的学生信息

   SELECT a.SId, a.SName, a.SBirth, a.SSex
   FROM Student a
   WHERE a.SId NOT IN (
       SELECT DISTINCT b.SId
       FROM Score b
       JOIN Course c ON b.CId = c.CId
       WHERE c.TId IN (SELECT TId FROM Teacher WHERE TName = '张三')