h1.003.MySQL数据库-经典50题问题与答案解析

练习好MySQL语句是非常重要,其实我一直认为MySQL语句写的就是一个感觉,当你SQL语句写多了,你会发现一件事,只要按照一套自己固定的思维思想去分析需求,没有那么的困难!

在做题的时候没有参考过其他人的答案,所以我知道肯定有一些答案相较于正确答案写的比较啰嗦。

但是对于SQL的优化又是另外一个方面的事情了。

0.数据库50题练习的初始化SQL语句

# 创建SQL_QUESTION数据库
CREATE DATABASE IF NOT EXISTS sql_50_question DEFAULT CHARACTER SET utf8mb4;
USE sql_50_question; # 使用SQL_QUESTION数据库
# 建表-学生表
CREATE TABLE `student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
# 建表-课程表
CREATE TABLE `course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
# 建表-教师表
CREATE TABLE `teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
# 建表-成绩表
CREATE TABLE `score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
# 插入数据-插入学生表测试数据
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 teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('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"课程成绩高的学生的信息及课程分数

-- 1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数.
/*
步骤分析:
1.先查询score表中的c_id是"01"的所有信息(伪表s_one),再查询score表中c_id是"02"的所有信息(伪表s_two).
2.再将s_one和s_two两张伪表进行内连接查询,查询条件(1)s_one和s_two的s_id列相同 (2)s_one表的score(01学科分数)大于s_two表的score(02学科分数),只保留s_id列作为伪表(所有01比02成绩高的学生Id)返回.
3.将student表与第二步中查询到的只有s_id列的伪表进行内连接查询,条件是student表的s_id与伪表的s_id列相同,即可查询到01比02成绩高的学生信息.
补:第二步完成之后既可以用子查询也可以用内连接(连接查询的效率高于子查询)
 */
SELECT stu.*
FROM student stu
         INNER JOIN (SELECT s_one.s_id AS id
                     FROM (SELECT * FROM score WHERE c_id = '01') s_one
                              INNER JOIN (SELECT * FROM score WHERE c_id = '02') s_two
                                         ON s_ONE.s_id = s_two.s_id AND s_one.s_score > s_two.s_score) stu_id
WHERE stu_id.id = stu.s_id;

2.查询在SC表存在成绩的学生信息

-- 2.查询在SC表存在成绩的学生信息(内连接)
/*
步骤分析:
1.先通过去重查询把score分数表中的所有存在的学生ID查询出来(结果是伪表exists_student_id单行单列)
2.再让student表和查询出来的伪表exists_student_id进行内连接查询,只保留stu_info表的全部内容
*/
SELECT stu_info.* FROM student stu_info INNER JOIN (SELECT DISTINCT s_id FROM score s) exists_student_id ON stu_info.s_id = exists_student_id.s_id;

-- 2.查询在SC表存在成绩的学生信息(子查询)
/*
步骤分析:
1.先通过去重查询把score分数表中的所有存在的学生ID查询出来(结果是伪表exists_student_id单行单列)
2.再让student表和查询出来的伪表exists_student_id进行子查询,查询student表的id存在于exists_student_id中的信息,只保留stu_info表的全部内容.
*/
SELECT stu_info.* FROM student stu_info WHERE stu_info.s_id IN (SELECT DISTINCT s_id FROM score s);

3.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)

-- 3.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)
-- 3.1.查询学生的选课总数以及总成绩
SELECT s.s_id AS student_id, COUNT(s.s_id) AS student_course_count, SUM(s.s_score) AS student_score_sum
FROM score s
GROUP BY s.s_id;

-- 3.2.左连接查询所有学生的编号,姓名与3.1结果的伪表进行左连接查询。
SELECT student.s_id,
       student.s_name,
       IFNULL(student_grup_info.student_course_count, NULL),
       IFNULL(student_grup_info.student_score_sum, NULL)
FROM student student
         LEFT JOIN (SELECT s.s_id         AS student_id,
                           COUNT(s.s_id)  AS student_course_count,
                           SUM(s.s_score) AS student_score_sum
                    FROM score s
                    GROUP BY s.s_id) student_grup_info ON student.s_id = student_grup_info.student_id;

4.查有成绩的学生信息

-- 4.查有成绩的学生信息.
/*
步骤分析:
1.先通过去重查询把score分数表中的所有存在的学生ID查询出来(结果是伪表exists_student_id单行单列)
2.再让student表和查询出来的伪表exists_student_id进行子查询,查询student表的id存在于exists_student_id中的信息.
 */
SELECT stu.*
FROM student stu
         INNER JOIN (SELECT DISTINCT s_id FROM score) s on stu.s_id = s.s_id;

5.查询「李」姓老师的数量

-- 5.查询「李」姓老师的数量.
/*
1.指定条件为模糊查询LIKE,李姓表示名称的第一个字必须是李,后面跟随字符的个数随意(李%)
2.聚合查询COUNT(*)就是个数.
 */
SELECT COUNT(*)
FROM teacher
WHERE t_name LIKE '李%';

6.查询学过「张三」老师授课的同学的信息

-- 6.查询学过「张三」老师授课的同学的信息(内连接)
-- 6.1 查询张三老师教授过哪些课程(保留课程id)(伪表zs_teacher_course_id_table)
SELECT c.c_id zs_course_id
FROM course c
         INNER JOIN teacher t ON c.t_id = t.t_id AND t.t_name = '张三';

-- 6.2 查询哪些学生在score中zs_course_id这一门课有分数(保留学生Id)(伪表zs_student_id_table)
SELECT s.s_id
FROM score s
         INNER JOIN (SELECT c.c_id zs_course_id
                     FROM course c
                              INNER JOIN teacher t ON c.t_id = t.t_id AND t.t_name = '张三') zs_teacher_course_id_table
                    ON s.c_id = zs_teacher_course_id_table.zs_course_id;

-- 6.3 查询学生表中的与6.2步结果伪表的id列相同的学生信息
SELECT s.s_id, s.s_name, s_birth, s.s_sex
FROM student s
         INNER JOIN (SELECT s.s_id
                     FROM score s
                              INNER JOIN (SELECT c.c_id zs_course_id
                                          FROM course c
                                                   INNER JOIN teacher t ON c.t_id = t.t_id AND t.t_name = '张三') zs_teacher_course_id_table
                                         ON s.c_id = zs_teacher_course_id_table.zs_course_id) zs_student_id_table
                    ON s.s_id = zs_student_id_table.s_id;

-- 6.查询学过「张三」老师授课的同学的信息(子查询版)
-- 6.1 查询张三老师的信息Id
SELECT t.t_id
FROM teacher t
WHERE t_name = '张三';
-- 6.2 查询张三老师所教的课程Id
SELECT c.c_id
FROM course c
WHERE c.t_id = (SELECT t.t_id FROM teacher t WHERE t_name = '张三');
-- 6.3 查询成绩表中所有包含张三老师所教课程Id的学生Id
SELECT s.s_id
FROM score s
WHERE s.c_id IN (SELECT c.c_id FROM course c WHERE c.t_id = (SELECT t.t_id FROM teacher t WHERE t_name = '张三'));
-- 6.4 查询学生表中所有指定Id的学生信息
SELECT *
FROM student s
WHERE s.s_id IN (SELECT s.s_id
                 FROM score s
                 WHERE s.c_id IN
                       (SELECT c.c_id FROM course c WHERE c.t_id = (SELECT t.t_id FROM teacher t WHERE t_name = '张三')));

7.查询没有学全所有课程的同学的信息

-- 7.查询没有学全所有课程的同学的信息.
-- 7.1.通过查询课程表查询出当前的所有课程数量(单行单列)(伪表course_count)
SELECT COUNT(*) count
FROM course;

-- 7.2 按照s_id对score表进行分组查询,查询出每个s_id的对应的课程数量(伪表s_count_table)
SELECT s.s_id, COUNT(s.s_id) s_count
FROM score s
GROUP BY s.s_id;

-- 7.3 由于有的学生一门课也没有上,所以不能保证7.2就是最终结果,让student表与7.2步查询到的表进行左连接查询,如果s_count_table.s_count为NULL,则修改为0.(伪表s_true_count_table)
SELECT stu.s_id, IFNULL(s_count_table.s_count, 0) s_true_count
FROM student stu
         LEFT JOIN (SELECT s.s_id, COUNT(s.s_id) s_count FROM score s GROUP BY s.s_id) s_count_table
                   ON stu.s_id = s_count_table.s_id;

-- 7.4 让7.3查询出的伪表s_true_count_table与7.1步的course_count进行内连接查询,查询出所有课程门数不是全部课程的学生Id.
SELECT s_true_count_table.s_id
FROM (SELECT stu.s_id, IFNULL(s_count_table.s_count, 0) s_true_count
      FROM student stu
               LEFT JOIN (SELECT s.s_id, COUNT(s.s_id) s_count FROM score s GROUP BY s.s_id) s_count_table
                         ON stu.s_id = s_count_table.s_id) s_true_count_table
         INNER JOIN (SELECT COUNT(*) count
                     FROM course) course_count ON s_true_count_table.s_true_count != course_count.count;

-- 7.5 让student表与7.4步的表格进行内连接查询,查询出所有的学生信息.
SELECT stu.*
FROM student stu
         INNER JOIN (SELECT s_true_count_table.s_id
                     FROM (SELECT stu.s_id, IFNULL(s_count_table.s_count, 0) s_true_count
                           FROM student stu
                                    LEFT JOIN (SELECT s.s_id, COUNT(s.s_id) s_count FROM score s GROUP BY s.s_id) s_count_table
                                              ON stu.s_id = s_count_table.s_id) s_true_count_table
                              INNER JOIN (SELECT COUNT(*) count
                                          FROM course) course_count
                                         ON s_true_count_table.s_true_count != course_count.count) s_all_count
                    ON stu.s_id = s_all_count.s_id;

8.查询至少有一门课与学号为"01"的同学所学相同的同学的信息

-- 8.1 查询01学号的学生学习的课程信息
SELECT s.c_id
FROM score s
WHERE s_id = '01';
-- 8.2 查询课程表中所有与包含01学生课程Id的成绩信息的学生Id
SELECT DISTINCT s.s_id
FROM score s
WHERE c_id IN (SELECT s.c_id FROM score s WHERE s_id = '01')
  AND s.s_id != '01';
-- 8.3 使用student表与8.2的结果进行子查询
SELECT *
FROM student
WHERE s_id IN (SELECT DISTINCT s.s_id
               FROM score s
               WHERE c_id IN (SELECT s.c_id FROM score s WHERE s_id = '01') AND s.s_id != '01');

9.查询和"01"号的同学学习的课程完全相同的其他同学的信息

-- 9.查询和"01"号的同学学习的课程完全相同的其他同学的信息
-- 9.1 查询01号同学学习的所有的课程Id
SELECT s.c_id
FROM score s
WHERE s_id = '01';
-- TODO:需要明确如何对比伪表结果

10.查询没学过"张三"老师讲授的任一门课程的学生姓名(有优化空间)

-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 10.1 查询张三老师教授的课程Id
SELECT c.c_id
FROM course c
WHERE c.t_id = (SELECT teacher.t_id FROM teacher WHERE t_name = '张三');
-- 10.2 查询学习过张三老师课程的学生Id
SELECT s.s_id
FROM score s
WHERE c_id IN (SELECT c.c_id FROM course c WHERE c.t_id = (SELECT teacher.t_id FROM teacher WHERE t_name = '张三'));
-- 10.3 查询所有学生的Id不在10.2结果中的数据
SELECT *
FROM student s
WHERE s.s_id NOT IN (SELECT s.s_id
                     FROM score s
                     WHERE c_id IN (SELECT c.c_id
                                    FROM course c
                                    WHERE c.t_id = (SELECT teacher.t_id FROM teacher WHERE t_name = '张三')));

11.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩

SELECT sc.s_id, stu.s_name, AVG(s_score)
FROM score sc
         INNER JOIN student stu
                    ON
                        sc.s_score < 60 AND sc.s_id = stu.s_id
GROUP BY sc.s_id
HAVING COUNT(*) >= 2;

12.检索"01"课程分数小于60,按分数降序排列的学生信息

-- 12.检索01课程分数小于60,按分数降序排列的学生信息
SELECT *
FROM student stu
         INNER JOIN (SELECT s.s_id, s.s_score FROM score s WHERE s.c_id = '01' AND s.s_score < 60) a
                    ON stu.s_id = a.s_id
ORDER BY a.s_score DESC;

13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率（及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90

14.要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

-- 14.要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT COUNT(s.s_id) c, s.c_id
FROM score s
GROUP BY s.c_id
ORDER BY c DESC, s.c_id;

15.按各科成绩进行排序,并显示排名,Score重复时保留名次空缺

16.按各科成绩进行排序,并显示排名,Score重复时合并名次

17.查询学生的总成绩,并进行排名,总分重复时保留名次空缺

18.查询学生的总成绩,并进行排名,总分重复时不保留名次空缺

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

20.查询各科成绩前三名的记录

21.查询每门课程被选修的学生数

22.查询出只选修两门课程的学生学号和姓名

23.查询男生、女生人数

24.查询名字中含有「风」字的学生信息

25.查询同名同姓学生名单,并统计同名人数

26.查询1990年出生的学生名单

27.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列

28.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

29.查询课程名称为「数学」,且分数低于60的学生姓名和分数

30.查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)

31.查询任何一门课程成绩在70分以上的姓名、课程名称和分数

32.查询不及格的课程

33.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

34.求每门课程的学生人数

35.成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩

36.成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩

37.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

38.查询每门功成绩最好的前两名

39.统计每门课程的学生选修人数(超过5人的课程才统计)

40.检索至少选修两门课程的学生学号

41.查询选修了全部课程的学生信息

42.查询各学生的年龄,只按年份来算

43.按照出生日期来算,当前月日<出生年月的月日则,年龄减一

44.查询本周过生日的学生

45.查询下周过生日的学生

46.查询本月过生日的学生

47.查询下月过生日的学生

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