前言

题目来源于知乎,原文请点击这里。中间有几道题不太清楚,其余的都是自己解答的。

表结构及数据

表结构

image-20200302110015768

sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
create table `student`(
    `sid` varchar(10),
    `sname` varchar(10),
    `sage` datetime,
    `ssex` varchar(10)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '学生表';

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' , '');

create table `course`(
    `cid` varchar(10),
    `cname` varchar(10),
    `tid` varchar(10)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '课程表';

insert into `course` values('01' , '语文' , '02');
insert into `course` values('02' , '数学' , '01');
insert into `course` values('03' , '英语' , '03');

create table `teacher`(
    `tid` varchar(10),
    `tname` varchar(10)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '教师表';

insert into `teacher` values('01' , '张三');
insert into `teacher` values('02' , '李四');
insert into `teacher` values('03' , '王五');

create table `sc`(
    `sid` varchar(10),
    `cid` varchar(10),
    `score` decimal(18,1)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '学生成绩表';

insert into `sc` values('01' , '01' , 80);
insert into `sc` values('01' , '02' , 90);
insert into `sc` values('01' , '03' , 99);
insert into `sc` values('02' , '01' , 70);
insert into `sc` values('02' , '02' , 60);
insert into `sc` values('02' , '03' , 80);
insert into `sc` values('03' , '01' , 80);
insert into `sc` values('03' , '02' , 80);
insert into `sc` values('03' , '03' , 80);
insert into `sc` values('04' , '01' , 50);
insert into `sc` values('04' , '02' , 30);
insert into `sc` values('04' , '03' , 20);
insert into `sc` values('05' , '01' , 76);
insert into `sc` values('05' , '02' , 87);
insert into `sc` values('06' , '01' , 31);
insert into `sc` values('06' , '03' , 34);
insert into `sc` values('07' , '02' , 89);
insert into `sc` values('07' , '03' , 98);

问题

1、查询“01”课程比“02”课程成绩高的学生的信息及课程分数
1.1首先通过sc表的 join 查询找出01课程比02课程成绩高的学生 id和分数信息

1
2
3
4
5
6
7
8
9
mysql> select * from (select sid, score as class1 from sc where sc.cid='01') as t1, 
 (select sid, score as class2 from sc where sc.cid="02") as t2 
 where t1.sid=t2.sid and t1.class1 > t2.class2;
+------+--------+------+--------+
| sid  | class1 | sid  | class2 |
+------+--------+------+--------+
| 02   |   70.0 | 02   |   60.0 |
| 04   |   50.0 | 04   |   30.0 |
+------+--------+------+--------+

1.2 将1.1的查询作为一个临时表,配合 student 表查询到最后的结果

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> select * from student right join (select t1.sid,class1,class2 from 
 (select sid, score as class1 from sc where sc.cid='01') as t1, 
 (select sid, score as class2 from sc where sc.cid="02") as t2 
 where t1.sid=t2.sid and t1.class1 > t2.class2) r on r.sid = student.sid;
+------+--------+---------------------+------+------+--------+--------+
| sid  | sname  | sage                | ssex | sid  | class1 | class2 |
+------+--------+---------------------+------+------+--------+--------+
| 02   |    | 1990-12-21 00:00:00 |    | 02   |   70.0 |   60.0 |
| 04   |    | 1990-08-06 00:00:00 |    | 04   |   50.0 |   30.0 |
+------+--------+---------------------+------+------+--------+--------+

2、查询存在“01”课程但可能不存在“02”课程的情况(不存在时显示为null) 分别查出02和02课程的情况作为两个临时表,然后进行左连接。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> select * from (select * from sc where sc.cid="01") as t1 
 left join (select * from sc where sc.cid="02") as t2 on t1.sid=t2.sid;
+------+------+-------+------+------+-------+
| sid  | cid  | score | sid  | cid  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
| 06   | 01   |  31.0 | NULL | NULL |  NULL |
+------+------+-------+------+------+-------+

3、查询同时存在“01”和“02”课程的情况
3.1 用内连接查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql> select * from (select * from sc where sc.cid="01") as t1 
 inner join (select * from sc where sc.cid="02") as t2 on t1.sid=t2.sid;
+------+------+-------+------+------+-------+
| sid  | cid  | score | sid  | cid  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
+------+------+-------+------+------+-------+

3.2 用子查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql> select * from (select * from sc where sc.cid="01") as t1, 
 (select * from sc where sc.cid="02") as t2 where t1.sid=t2.sid;
+------+------+-------+------+------+-------+
| sid  | cid  | score | sid  | cid  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
+------+------+-------+------+------+-------+

4、查询选择了“02”课程但没有“01”课程的情况
4.1 右连接,踢出左边的

1
2
3
4
5
6
7
8
mysql> select * from (select * from sc where sc.cid="01") as t1 
 right join (select * from sc where sc.cid="02") as t2 on t1.sid=t2.sid
 where t1.sid is null;
+------+------+-------+------+------+-------+
| sid  | cid  | score | sid  | cid  | score |
+------+------+-------+------+------+-------+
| NULL | NULL |  NULL | 07   | 02   |  89.0 |
+------+------+-------+------+------+-------+

4.2 子查询

1
2
3
4
5
6
mysql> select * from sc where sid not in (select sid from sc where cid="01") and cid="02";
+------+------+-------+
| sid  | cid  | score |
+------+------+-------+
| 07   | 02   |  89.0 |
+------+------+-------+

5、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
两个表进行内连接,然后根据 sid 分组,计算平均分数,最后过滤

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql> select sc.sid,student.sname, avg(sc.score) as avg_score from student 
 inner join sc on student.sid=sc.sid group by sc.sid having avg_score > 60;
+------+--------+-----------+
| sid  | sname  | avg_score |
+------+--------+-----------+
| 01   |    |  89.66667 |
| 02   |    |  70.00000 |
| 03   |    |  80.00000 |
| 05   |    |  81.50000 |
| 07   |    |  93.50000 |
+------+--------+-----------+

6、查询在sc表存在成绩的学生信息
6.1 子查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> select * from student where sid in (select distinct(sid) from sc);
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 01   |    | 1990-01-01 00:00:00 |    |
| 02   |    | 1990-12-21 00:00:00 |    |
| 03   |    | 1990-05-20 00:00:00 |    |
| 04   |    | 1990-08-06 00:00:00 |    |
| 05   |    | 1991-12-01 00:00:00 |    |
| 06   |    | 1992-03-01 00:00:00 |    |
| 07   |    | 1989-07-01 00:00:00 |    |
+------+--------+---------------------+------+

6.2 右连接然后去重

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> select distinct student.* from student right join sc on student.sid = sc.sid;
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 01   |    | 1990-01-01 00:00:00 |    |
| 02   |    | 1990-12-21 00:00:00 |    |
| 03   |    | 1990-05-20 00:00:00 |    |
| 04   |    | 1990-08-06 00:00:00 |    |
| 05   |    | 1991-12-01 00:00:00 |    |
| 06   |    | 1992-03-01 00:00:00 |    |
| 07   |    | 1989-07-01 00:00:00 |    |
+------+--------+---------------------+------+

7、查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
7.1、左连接,根据 sid 分组,count 求选课总数,sum 求成绩总和,注意 count 和 sum 的区别
原文中的解法是排除了没有选课的08号同学的,但是题目说的是所有同学,所以我觉得应该是要包含08号同学才对。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
mysql> select student.sid,sname,count(cid),sum(score) from student 
 left join sc on student.sid=sc.sid group by student.sid;
+------+--------+------------+------------+
| sid  | sname  | count(cid) | sum(score) |
+------+--------+------------+------------+
| 01   |    |          3 |      269.0 |
| 02   |    |          3 |      210.0 |
| 03   |    |          3 |      240.0 |
| 04   |    |          3 |      100.0 |
| 05   |    |          2 |      163.0 |
| 06   |    |          2 |       65.0 |
| 07   |    |          2 |      187.0 |
| 08   |    |          0 |       NULL |
+------+--------+------------+------------+

8、查询“李”姓老师的数量
like 查询,然后进行 count

1
2
3
4
5
6
mysql> select count(*) from teacher where tname like "李%";
+----------+
| count(*) |
+----------+
|        1 |
+----------+

9、查询学过“张三”老师授课的同学的信息
9.1 多次子查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
mysql> select * from student where sid in 
 (select sid from sc where cid in 
 (select cid from course where tid in 
 (select tid from teacher where tname="张三")));
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 01   |    | 1990-01-01 00:00:00 |    |
| 02   |    | 1990-12-21 00:00:00 |    |
| 03   |    | 1990-05-20 00:00:00 |    |
| 04   |    | 1990-08-06 00:00:00 |    |
| 05   |    | 1991-12-01 00:00:00 |    |
| 07   |    | 1989-07-01 00:00:00 |    |
+------+--------+---------------------+------+

9.2 多次 inner join
先根据course表跟teacher表连接找到正确的课程 ID

1
2
3
4
5
6
7
mysql> select course.cid from course inner join teacher on 
 course.tid=teacher.tid where teacher.tname="张三";
+------+
| cid  |
+------+
| 02   |
+------+

然后再用sc表跟上面查询结果进行连接,找到 sid

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
mysql> select sc.sid from sc inner join 
 (select course.cid from course inner join teacher on 
 course.tid=teacher.tid where teacher.tname="张三")  as tc on sc.cid = tc.cid;
+------+
| sid  |
+------+
| 01   |
| 02   |
| 03   |
| 04   |
| 05   |
| 07   |
+------+

最后用student表跟上面的查询结果进行连接,得到最终结果

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
mysql> select * from student inner join 
 (select sc.sid from sc inner join 
 (select course.cid from course inner join teacher on course.tid=teacher.tid 
 where teacher.tname="张三")  as tc on sc.cid = tc.cid) as stc on student.sid=stc.sid;
+------+--------+---------------------+------+------+
| sid  | sname  | sage                | ssex | sid  |
+------+--------+---------------------+------+------+
| 01   |    | 1990-01-01 00:00:00 |    | 01   |
| 02   |    | 1990-12-21 00:00:00 |    | 02   |
| 03   |    | 1990-05-20 00:00:00 |    | 03   |
| 04   |    | 1990-08-06 00:00:00 |    | 04   |
| 05   |    | 1991-12-01 00:00:00 |    | 05   |
| 07   |    | 1989-07-01 00:00:00 |    | 07   |
+------+--------+---------------------+------+------+

10、查询没有学全所有课程的同学的信息
10.1 not in

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql> select * from student where student.sid not in 
 (select sc.sid from sc group by sc.sid 
 having count(sc.cid) = (select count(1) from course));
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 05   |    | 1991-12-01 00:00:00 |    |
| 06   |    | 1992-03-01 00:00:00 |    |
| 07   |    | 1989-07-01 00:00:00 |    |
| 08   |    | 1990-01-20 00:00:00 |    |
+------+--------+---------------------+------+

11、查询至少有一门课与学号为“01”的同学所学相同的同学的信息
先找到01同学所学的课程

1
2
3
4
5
6
7
8
mysql> select cid from sc where sid="01";
+------+
| cid  |
+------+
| 01   |
| 02   |
| 03   |
+------+

再找到和01同学有相同课程的同学

1
2
3
mysql> select distinct sc02.sid from sc as sc02 inner join 
(select cid from sc where sid="01") as sc01 
on sc01.cid=sc02.cid where sc02.sid !="01";

最后通过 in 查询找到所有同学的信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
mysql> select * from student where sid in 
 (select distinct sc02.sid from sc as sc02 inner join 
 (select cid from sc where sid="01") as sc01 
 on sc01.cid=sc02.cid where sc02.sid !="01");
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 02   |    | 1990-12-21 00:00:00 |    |
| 03   |    | 1990-05-20 00:00:00 |    |
| 04   |    | 1990-08-06 00:00:00 |    |
| 05   |    | 1991-12-01 00:00:00 |    |
| 06   |    | 1992-03-01 00:00:00 |    |
| 07   |    | 1989-07-01 00:00:00 |    |
+------+--------+---------------------+------+

也可以通过内连接获取结果

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
mysql> select student.* from student inner join 
(select distinct sc02.sid from sc as sc02 inner join 
(select cid from sc where sid = "01") as sc01 on sc01.cid = sc02.cid 
where sc02.sid != "01") as sc03 on student.sid=sc03.sid;
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 02   |    | 1990-12-21 00:00:00 |    |
| 03   |    | 1990-05-20 00:00:00 |    |
| 04   |    | 1990-08-06 00:00:00 |    |
| 05   |    | 1991-12-01 00:00:00 |    |
| 06   |    | 1992-03-01 00:00:00 |    |
| 07   |    | 1989-07-01 00:00:00 |    |
+------+--------+---------------------+------+

两种方式的执行计划如下(表里没有建任何索引)

1
2
3
4
5
6
7
8
9
mysql> explain select student.* from student inner join (select distinct sc02.sid from sc as sc02 inner join (select cid from sc where sid = "01") as sc01 on sc01.cid = sc02.cid where sc02.sid != "01") as sc03 on student.sid=sc03.sid;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               |
|  1 | PRIMARY     | student    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |    12.50 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | sc         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   18 |    10.00 | Using where; Using temporary                       |
|  2 | DERIVED     | sc02       | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   18 |     9.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
1
2
3
4
5
6
7
8
9
mysql> explain select * from student where sid in   (select distinct sc02.sid from sc as sc02 inner join   (select cid from sc where sid="01") as sc01   on sc01.cid=sc02.cid where sc02.sid !="01");
+----+--------------+-------------+------------+--------+---------------+------------+---------+---------------------+------+----------+----------------------------------------------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref                 | rows | filtered | Extra                                              |
+----+--------------+-------------+------------+--------+---------------+------------+---------+---------------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE       | student     | NULL       | ALL    | NULL          | NULL       | NULL    | NULL                |    8 |   100.00 | Using where                                        |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 33      | subject.student.sid |    1 |   100.00 | NULL                                               |
|  2 | MATERIALIZED | sc          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL                |   18 |    10.00 | Using where                                        |
|  2 | MATERIALIZED | sc02        | NULL       | ALL    | NULL          | NULL       | NULL    | NULL                |   18 |     9.00 | Using where; Using join buffer (Block Nested Loop) |
+----+--------------+-------------+------------+--------+---------------+------------+---------+---------------------+------+----------+----------------------------------------------------+

12、查询和“01”号的同学学习的课程完全相同的其他同学的信息
看了评论,感觉下面的解法有问题,下面的解法有可能出现选的课程比01号同学多,但是01号同学的课程都选了,这应该不符合完全相同的题意。具体的解法再研究研究吧。 第一步还是要先找到01同学所学的课程

1
2
3
4
5
6
7
8
mysql> select cid from sc where sid = "01";
+------+
| cid  |
+------+
| 01   |
| 02   |
| 03   |
+------+

将上面的结果作为临时表,跟 sc 进行内连接,找到跟01同学有相同课程的同学

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> select * from sc as sc02 inner join (select cid from sc where sid = "01")
 as sc01 on sc01.cid=sc02.cid;
+------+------+-------+------+
| sid  | cid  | score | cid  |
+------+------+-------+------+
| 01   | 01   |  80.0 | 01   |
| 01   | 02   |  90.0 | 02   |
| 01   | 03   |  99.0 | 03   |
| 02   | 01   |  70.0 | 01   |
| 02   | 02   |  60.0 | 02   |
| 02   | 03   |  80.0 | 03   |
| 03   | 01   |  80.0 | 01   |
| 03   | 02   |  80.0 | 02   |
| 03   | 03   |  80.0 | 03   |
| 04   | 01   |  50.0 | 01   |
| 04   | 02   |  30.0 | 02   |
| 04   | 03   |  20.0 | 03   |
| 05   | 01   |  76.0 | 01   |
| 05   | 02   |  87.0 | 02   |
| 06   | 01   |  31.0 | 01   |
| 06   | 03   |  34.0 | 03   |
| 07   | 02   |  89.0 | 02   |
| 07   | 03   |  98.0 | 03   |
+------+------+-------+------+

对上面的结果进行分组去重并计算每个同学的课程数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
mysql> select sc02.*, count(sc02.cid) as c_count from sc as sc02 inner join 
(select cid from sc where sid = "01") as sc01 
on sc01.cid=sc02.cid group by sc02.sid;
+------+------+-------+---------+
| sid  | cid  | score | c_count |
+------+------+-------+---------+
| 01   | 01   |  80.0 |       3 |
| 02   | 01   |  70.0 |       3 |
| 03   | 01   |  80.0 |       3 |
| 04   | 01   |  50.0 |       3 |
| 05   | 01   |  76.0 |       2 |
| 06   | 01   |  31.0 |       2 |
| 07   | 02   |  89.0 |       2 |
+------+------+-------+---------+

因为上面查出来的同学的课程id都是01号同学的课程,所以只要课程数跟01号同学的相同,那么学的课程就肯定跟01号同学完全一样。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> select sc02.*, count(sc02.cid) as c_count from sc as sc02 inner join 
(select cid from sc where sid = "01") as sc01 
on sc01.cid=sc02.cid group by sc02.sid 
having c_count = (select count(*) from sc where sid = "01");
+------+------+-------+---------+
| sid  | cid  | score | c_count |
+------+------+-------+---------+
| 01   | 01   |  80.0 |       3 |
| 02   | 01   |  70.0 |       3 |
| 03   | 01   |  80.0 |       3 |
| 04   | 01   |  50.0 |       3 |
+------+------+-------+---------+

接下来就可以根据上面的 sid 获取其他同学的信息了,这里还需要过滤01号同学

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> select * from student where sid in 
(select sc02.sid from sc as sc02 inner join 
(select cid from sc where sid = "01") as sc01 on sc01.cid=sc02.cid 
where sc02.sid != "01" group by sc02.sid 
having count(sc02.cid) = (select count(*) from sc where sid = "01"));
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 02   |    | 1990-12-21 00:00:00 |    |
| 03   |    | 1990-05-20 00:00:00 |    |
| 04   |    | 1990-08-06 00:00:00 |    |
+------+--------+---------------------+------+

注意:where 条件要用在 group by 的前面。

13、查询没学过“张三”老师讲授的任一门课程的学生姓名
先找出张三老师的课程 id

1
2
3
4
5
6
7
mysql> select c.cid from course as c inner join teacher as t on c.tid=t.tid 
where t.tname="张三";
+------+
| cid  |
+------+
| 02   |
+------+

然后找出学过张三老师可能的学生(如果这里去找没有学过张三老师课程的学生,那结果将是错误的)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
mysql> select sc.sid from sc where sc.cid in 
(select c.cid from course as c inner join teacher as t 
on c.tid=t.tid where t.tname="张三");
+------+
| sid  |
+------+
| 01   |
| 02   |
| 03   |
| 04   |
| 05   |
| 07   |
+------+

最后再通过 student 表来过滤上面的结果。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> select sname from student where sid not in 
(select sc.sid from sc where sc.cid in 
(select c.cid from course as c inner join teacher as t 
on c.tid=t.tid where t.tname="张三"));
+--------+
| sname  |
+--------+
|    |
|    |
+--------+

join 查询的写法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql> select sname from student left join 
(select sc.sid from sc inner join 
(select c.cid from course as c inner join teacher as t 
    on c.tid=t.tid where t.tname="张三") as tc on tc.cid=sc.cid ) as stc 
on student.sid=stc.sid where stc.sid is null;
+--------+
| sname  |
+--------+
|    |
|    |
+--------+

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

1
2
3
4
5
6
7
8
9
mysql> select sname,st.sid,avg(sc01.score) from student as st inner join 
(select sid,score from sc where score < 60) as sc01 
on sc01.sid = st.sid group by sc01.sid having count(sc01.sid) >= 2;
+--------+------+-----------------+
| sname  | sid  | avg(sc01.score) |
+--------+------+-----------------+
|    | 04   |        33.33333 |
|    | 06   |        32.50000 |
+--------+------+-----------------+

想复杂了的解法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> select sn.sname,sn.sid,so.avg_score from 
(select st.sname,st.sid from student as st inner join 
(select sid from sc where score < 60 group by sid having count(cid) >= 2) as lt60 
on st.sid = lt60.sid) as sn inner join (select sc.sid,avg(sc.score) as avg_score 
from sc inner join (select sid from sc where score < 60 group by sid having count(cid) >= 2) as lt60 
on sc.sid = lt60.sid group by sc.sid) as so on sn.sid = so.sid;
+--------+------+-----------+
| sname  | sid  | avg_score |
+--------+------+-----------+
|    | 04   |  33.33333 |
|    | 06   |  32.50000 |
+--------+------+-----------+

15、检索“01”课程分数小于 60,按分数降序排列的学生信息

1
2
3
4
5
6
7
8
9
mysql> select st.* from student as st inner join 
(select * from sc where cid="01" and score < 60) as sc01 
on st.sid=sc01.sid order by sc01.score desc;
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 04   |    | 1990-08-06 00:00:00 |    |
| 06   |    | 1992-03-01 00:00:00 |    |
+------+--------+---------------------+------+

16、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> select * from sc inner join 
(select sid,avg(score) as avg_score from sc group by sid) as avr 
on sc.sid = avr.sid order by avr.avg_score desc;
+------+------+-------+------+-----------+
| sid  | cid  | score | sid  | avg_score |
+------+------+-------+------+-----------+
| 07   | 02   |  89.0 | 07   |  93.50000 |
| 07   | 03   |  98.0 | 07   |  93.50000 |
| 01   | 02   |  90.0 | 01   |  89.66667 |
| 01   | 03   |  99.0 | 01   |  89.66667 |
| 01   | 01   |  80.0 | 01   |  89.66667 |
| 05   | 01   |  76.0 | 05   |  81.50000 |
| 05   | 02   |  87.0 | 05   |  81.50000 |
| 03   | 02   |  80.0 | 03   |  80.00000 |
| 03   | 03   |  80.0 | 03   |  80.00000 |
| 03   | 01   |  80.0 | 03   |  80.00000 |
| 02   | 03   |  80.0 | 02   |  70.00000 |
| 02   | 01   |  70.0 | 02   |  70.00000 |
| 02   | 02   |  60.0 | 02   |  70.00000 |
| 04   | 01   |  50.0 | 04   |  33.33333 |
| 04   | 02   |  30.0 | 04   |  33.33333 |
| 04   | 03   |  20.0 | 04   |  33.33333 |
| 06   | 01   |  31.0 | 06   |  32.50000 |
| 06   | 03   |  34.0 | 06   |  32.50000 |
+------+------+-------+------+-----------+

17、查询各科成绩最高分、最低分和平均分,以如下形式显示:

1
2
3
4
以如下形式显示:
课程 ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

先通过case when 计算出每科成绩的分数属于哪个等级

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
mysql> select * , case
       when score >= 60
       then 1
       else 0
       end as '及格',
       case
       when score >= 70 and score < 80
       then 1
       else 0
       end as '中等',
       case
       when score >= 80 and score < 90
       then 1
       else 0
       end as '优良',
       case
       when score >= 90
       then 1
       else 0
       end as '优秀'
       from sc;
+------+------+-------+--------+--------+--------+--------+
| sid  | cid  | score |    |    |    |    |
+------+------+-------+--------+--------+--------+--------+
| 01   | 01   |  80.0 |      1 |      0 |      1 |      0 |
| 01   | 02   |  90.0 |      1 |      0 |      0 |      1 |
| 01   | 03   |  99.0 |      1 |      0 |      0 |      1 |
| 02   | 01   |  70.0 |      1 |      1 |      0 |      0 |
| 02   | 02   |  60.0 |      1 |      0 |      0 |      0 |
| 02   | 03   |  80.0 |      1 |      0 |      1 |      0 |
| 03   | 01   |  80.0 |      1 |      0 |      1 |      0 |
| 03   | 02   |  80.0 |      1 |      0 |      1 |      0 |
| 03   | 03   |  80.0 |      1 |      0 |      1 |      0 |
| 04   | 01   |  50.0 |      0 |      0 |      0 |      0 |
| 04   | 02   |  30.0 |      0 |      0 |      0 |      0 |
| 04   | 03   |  20.0 |      0 |      0 |      0 |      0 |
| 05   | 01   |  76.0 |      1 |      1 |      0 |      0 |
| 05   | 02   |  87.0 |      1 |      0 |      1 |      0 |
| 06   | 01   |  31.0 |      0 |      0 |      0 |      0 |
| 06   | 03   |  34.0 |      0 |      0 |      0 |      0 |
| 07   | 02   |  89.0 |      1 |      0 |      1 |      0 |
| 07   | 03   |  98.0 |      1 |      0 |      0 |      1 |
+------+------+-------+--------+--------+--------+--------+

将上面的结果当做子表来查询就可以了,再次应该注意 sum 和 count 的区别

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select cid as '课程id', count(cid) as '课程数', max(score) as '最高分', 
    min(score) as '最低分',avg(score) as '平均分',sum()/count(sid) as '及格率',
    sum()/count(sid) as '中等率',sum()/count(sid) as '优良率',sum()/count(sid) as '优秀率'
    from (
    select * , case
           when score >= 60
           then 1
           else 0
           end as '及格',
           case
           when score >= 70 and score < 80
           then 1
           else 0
           end as '中等',
           case
           when score >= 80 and score < 90
           then 1
           else 0
           end as '优良',
           case
           when score >= 90
           then 1
           else 0
           end as '优秀'
           from sc
    ) as sc01 group by sc01.cid;
+----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| id   |     |     |     |     |     |     |     |     |
+----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 01       |         6 |      80.0 |      31.0 |  64.50000 |    0.6667 |    0.3333 |    0.3333 |    0.0000 |
| 02       |         6 |      90.0 |      30.0 |  72.66667 |    0.8333 |    0.0000 |    0.5000 |    0.1667 |
| 03       |         6 |      99.0 |      20.0 |  68.50000 |    0.6667 |    0.0000 |    0.3333 |    0.3333 |
+----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

其实题目说的是要展示课程名称,但是原文的解法是展示课程数,下面是课程名称的sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select cid as '课程id', sc01.cname, max(score) as '最高分', 
    min(score) as '最低分',avg(score) as '平均分',sum()/count(sid) as '及格率',
    sum()/count(sid) as '中等率',sum()/count(sid) as '优良率',sum()/count(sid) as '优秀率'
    from (
    select * , case
           when score >= 60
           then 1
           else 0
           end as '及格',
           case
           when score >= 70 and score < 80
           then 1
           else 0
           end as '中等',
           case
           when score >= 80 and score < 90
           then 1
           else 0
           end as '优良',
           case
           when score >= 90
           then 1
           else 0
           end as '优秀'
           from (select sc.*,course.cname from sc left join course on sc.cid = course.cid) as sc01
    ) as sc01 group by sc01.cid
+----------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| id   | cname  |     |     |     |     |     |     |     |
+----------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 01       |    |      80.0 |      31.0 |  64.50000 |    0.6667 |    0.3333 |    0.3333 |    0.0000 |
| 02       |    |      90.0 |      30.0 |  72.66667 |    0.8333 |    0.0000 |    0.5000 |    0.1667 |
| 03       |    |      99.0 |      20.0 |  68.50000 |    0.6667 |    0.0000 |    0.3333 |    0.3333 |
+----------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

18、按各科成绩进行排序,并显示排名,score重复时保留名次空缺
虽然都是排序,但还是看了原文的解法,分组的时候需要对课程和学生都进行。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> select sc01.*, count(sc01.score) as '排名' from sc as sc01 
        left join sc as sc02 on sc01.cid = sc02.cid and sc01.score < sc02.score 
        group by sc01.cid, sc01.sid order by sc01.cid, ;
+------+------+-------+--------+
| sid  | cid  | score |    |
+------+------+-------+--------+
| 01   | 01   |  80.0 |      1 |
| 03   | 01   |  80.0 |      1 |
| 05   | 01   |  76.0 |      2 |
| 02   | 01   |  70.0 |      3 |
| 04   | 01   |  50.0 |      4 |
| 06   | 01   |  31.0 |      5 |
| 07   | 02   |  89.0 |      1 |
| 01   | 02   |  90.0 |      1 |
| 05   | 02   |  87.0 |      2 |
| 03   | 02   |  80.0 |      3 |
| 02   | 02   |  60.0 |      4 |
| 04   | 02   |  30.0 |      5 |
| 07   | 03   |  98.0 |      1 |
| 01   | 03   |  99.0 |      1 |
| 02   | 03   |  80.0 |      2 |
| 03   | 03   |  80.0 |      2 |
| 06   | 03   |  34.0 |      4 |
| 04   | 03   |  20.0 |      5 |
+------+------+-------+--------+

19、按各科成绩进行行排序,并显示排名,score重复时合并名次

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> select sc01.*, count(sc02.score) + 1 as '排名' from sc as sc01 
        left join sc as sc02 on sc01.cid = sc02.cid and sc01.score < sc02.score 
        group by sc01.cid, sc01.sid order by sc01.cid, ;
+------+------+-------+--------+
| sid  | cid  | score |    |
+------+------+-------+--------+
| 01   | 01   |  80.0 |      1 |
| 03   | 01   |  80.0 |      1 |
| 05   | 01   |  76.0 |      3 |
| 02   | 01   |  70.0 |      4 |
| 04   | 01   |  50.0 |      5 |
| 06   | 01   |  31.0 |      6 |
| 01   | 02   |  90.0 |      1 |
| 07   | 02   |  89.0 |      2 |
| 05   | 02   |  87.0 |      3 |
| 03   | 02   |  80.0 |      4 |
| 02   | 02   |  60.0 |      5 |
| 04   | 02   |  30.0 |      6 |
| 01   | 03   |  99.0 |      1 |
| 07   | 03   |  98.0 |      2 |
| 02   | 03   |  80.0 |      3 |
| 03   | 03   |  80.0 |      3 |
| 06   | 03   |  34.0 |      5 |
| 04   | 03   |  20.0 |      6 |
+------+------+-------+--------+

20、查询学生的总成绩,并进行排名,总分重复时保留名次空缺
需要引入临时变量

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
mysql> select sc01.*, @rank := @rank + 1 AS '排名' from 
        (select sid, sum(score) as '总分' from sc group by sid order by sum(score) desc) as sc01, 
        (select @rank :=0) as sr;
+------+--------+--------+
| sid  |    |    |
+------+--------+--------+
| 01   |  269.0 |      1 |
| 03   |  240.0 |      2 |
| 02   |  210.0 |      3 |
| 07   |  187.0 |      4 |
| 05   |  163.0 |      5 |
| 04   |  100.0 |      6 |
| 06   |   65.0 |      7 |
+------+--------+--------+

21、查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
看的原文的解法,需要定义两个变量,并且通过 case when 判断

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
select 
  sc01.*,
  case
    when @fscore = sc01.sum_score 
    then @rank 
    when @fscore := sc01.sum_score 
    then @rank := @rank + 1 
  end as  
from 
  (select sc.sid, sum(score) as sum_score from sc group by sid order by sum_score desc) as sc01, 
  (select @rank :=0, @fscore := NULL) as rf;
 +------+-----------+--------+
 | sid  | sum_score |    |
 +------+-----------+--------+
 | 01   |     269.0 |      1 |
 | 03   |     240.0 |      2 |
 | 02   |     210.0 |      3 |
 | 07   |     187.0 |      4 |
 | 05   |     163.0 |      5 |
 | 04   |     100.0 |      6 |
 | 06   |      65.0 |      7 |
 +------+-----------+--------+

22、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
先统计所属的段落

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
mysql> select * , case
       when score >= 85
       then 1
       else 0
       end as 'one',
       case
       when score >= 70 and score < 85
       then 1
       else 0
       end as 'two',
       case
       when score >= 60 and score < 70
       then 1
       else 0
       end as 'three',
       case
       when score < 60
       then 1
       else 0
       end as 'four'
       from sc;
+------+------+-------+-----+-----+-------+------+
| sid  | cid  | score | one | two | three | four |
+------+------+-------+-----+-----+-------+------+
| 01   | 01   |  80.0 |   0 |   1 |     0 |    0 |
| 01   | 02   |  90.0 |   1 |   0 |     0 |    0 |
| 01   | 03   |  99.0 |   1 |   0 |     0 |    0 |
| 02   | 01   |  70.0 |   0 |   1 |     0 |    0 |
| 02   | 02   |  60.0 |   0 |   0 |     1 |    0 |
| 02   | 03   |  80.0 |   0 |   1 |     0 |    0 |
| 03   | 01   |  80.0 |   0 |   1 |     0 |    0 |
| 03   | 02   |  80.0 |   0 |   1 |     0 |    0 |
| 03   | 03   |  80.0 |   0 |   1 |     0 |    0 |
| 04   | 01   |  50.0 |   0 |   0 |     0 |    1 |
| 04   | 02   |  30.0 |   0 |   0 |     0 |    1 |
| 04   | 03   |  20.0 |   0 |   0 |     0 |    1 |
| 05   | 01   |  76.0 |   0 |   1 |     0 |    0 |
| 05   | 02   |  87.0 |   1 |   0 |     0 |    0 |
| 06   | 01   |  31.0 |   0 |   0 |     0 |    1 |
| 06   | 03   |  34.0 |   0 |   0 |     0 |    1 |
| 07   | 02   |  89.0 |   1 |   0 |     0 |    0 |
| 07   | 03   |  98.0 |   1 |   0 |     0 |    0 |
+------+------+-------+-----+-----+-------+------+

统计各阶段的人数和百分比

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select sc01.cid as '课程编号',sum(sc01.one) as '100-85',sum(sc01.one)/count(sid) as '百分比', 
    sum(sc01.two) as '85-70', sum(sc01.two)/count(sid) as '百分比',sum(sc01.three) as '70-60', 
    sum(sc01.three)/count(sid) as '百分比', sum(sc01.four) as '60-0', sum(sc01.four)/count(sid) as '百分比'
    from (
    select * , case
           when score >= 85
           then 1
           else 0
           end as 'one',
           case
           when score >= 70 and score < 85
           then 1
           else 0
           end as 'two',
           case
           when score >= 60 and score < 70
           then 1
           else 0
           end as 'three',
           case
           when score < 60
           then 1
           else 0
           end as 'four'
           from sc
    ) as sc01 group by sc01.cid;
+--------------+--------+-----------+-------+-----------+-------+-----------+------+-----------+
|      | 100-85 |     | 85-70 |     | 70-60 |     | 60-0 |     |
+--------------+--------+-----------+-------+-----------+-------+-----------+------+-----------+
| 01           |      0 |    0.0000 |     4 |    0.6667 |     0 |    0.0000 |    2 |    0.3333 |
| 02           |      3 |    0.5000 |     1 |    0.1667 |     1 |    0.1667 |    1 |    0.1667 |
| 03           |      2 |    0.3333 |     2 |    0.3333 |     0 |    0.0000 |    2 |    0.3333 |
+--------------+--------+-----------+-------+-----------+-------+-----------+------+-----------+

添加课程名称

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> select sc01.cid as '课程编号', sc01.cname as '课程名称', sum(sc01.one) as '100-85',sum(sc01.one)/count(sid) as '百分比', 
    sum(sc01.two) as '85-70', sum(sc01.two)/count(sid) as '百分比',sum(sc01.three) as '70-60', 
    sum(sc01.three)/count(sid) as '百分比', sum(sc01.four) as '60-0', sum(sc01.four)/count(sid) as '百分比'
    from (
    select * , case
           when score >= 85
           then 1
           else 0
           end as 'one',
           case
           when score >= 70 and score < 85
           then 1
           else 0
           end as 'two',
           case
           when score >= 60 and score < 70
           then 1
           else 0
           end as 'three',
           case
           when score < 60
           then 1
           else 0
           end as 'four'
           from (select sc.*,course.cname from sc left join course on sc.cid = course.cid) as sco
    ) as sc01 group by sc01.cid;

+--------------+--------------+--------+-----------+-------+-----------+-------+-----------+------+-----------+
|       |       | 100-85 |     | 85-70 |      | 70-60  |     | 60-0 |     |
+--------------+--------------+--------+-----------+-------+-----------+-------+-----------+------+-----------+
| 01           |          |      0 |    0.0000 |     4 |    0.6667 |     0 |    0.0000 |    2 |    0.3333 |
| 02           |          |      3 |    0.5000 |     1 |    0.1667 |     1 |    0.1667 |    1 |    0.1667 |
| 03           |          |      2 |    0.3333 |     2 |    0.3333 |     0 |    0.0000 |    2 |    0.3333 |
+--------------+--------------+--------+-----------+-------+-----------+-------+-----------+------+-----------+

23、查询各科成绩前三名的记录

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
mysql> select sc01.*, count(sc02.score)+1 as ranking from sc as sc01 
    left join sc as sc02 on sc01.cid = sc02.cid and sc01.score < sc02.score 
    group by sc01.cid, sc01.sid having ranking <= 3 order by sc01.cid, ranking asc;
+------+------+-------+---------+
| sid  | cid  | score | ranking |
+------+------+-------+---------+
| 01   | 01   |  80.0 |       1 |
| 03   | 01   |  80.0 |       1 |
| 05   | 01   |  76.0 |       3 |
| 01   | 02   |  90.0 |       1 |
| 07   | 02   |  89.0 |       2 |
| 05   | 02   |  87.0 |       3 |
| 01   | 03   |  99.0 |       1 |
| 07   | 03   |  98.0 |       2 |
| 02   | 03   |  80.0 |       3 |
| 03   | 03   |  80.0 |       3 |
+------+------+-------+---------+

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

1
2
3
4
5
6
7
8
mysql> select cid, count(sid) from sc group by cid;
+------+------------+
| cid  | count(sid) |
+------+------------+
| 01   |          6 |
| 02   |          6 |
| 03   |          6 |
+------+------------+

25、查询出只选修两门课程的学生学号和姓名
先查出只选修两门课程的学生 id

1
2
3
4
5
6
7
8
mysql> select sid from sc group by sid having(count(cid)) = 2;
+------+
| sid  |
+------+
| 05   |
| 06   |
| 07   |
+------+

从student 表查姓名

1
2
3
4
5
6
7
8
9
mysql> select sid,sname from student where sid in 
    (select sid from sc group by sid having(count(cid)) = 2);
+------+--------+
| sid  | sname  |
+------+--------+
| 05   |    |
| 06   |    |
| 07   |    |
+------+--------+

直接 inner join 的解法

1
2
3
4
5
6
7
8
9
mysql> select student.sid,student.sname from sc inner join student 
    on sc.sid = student.sid group by sc.sid having(count(sc.sid)) = 2;
+------+--------+
| sid  | sname  |
+------+--------+
| 05   |    |
| 06   |    |
| 07   |    |
+------+--------+

26、查询男生、女生人数
直接 group by ,然后 count

1
2
3
4
5
6
7
mysql> select ssex,count(ssex) as '人数' from student group by ssex;
+------+--------+
| ssex |    |
+------+--------+
|    |      4 |
|    |      4 |
+------+--------+

参考原文的解法

1
2
3
4
5
6
7
mysql> select sum(case when ssex='' then 1 else 0 end) as '男生人数',
     sum(case when ssex='' then 1 else 0 end) as '女生人数' from student;
+--------------+--------------+
|      |      |
+--------------+--------------+
|            4 |            4 |
+--------------+--------------+

27、查询名字中含有“风”字的学生信息

1
2
3
4
5
6
mysql> select * from student where sname like '%风%';
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 03   |    | 1990-05-20 00:00:00 |    |
+------+--------+---------------------+------+

28、查询同名同性别学生名单,并统计同名人数
由于原来的数据没有符合条件的,这里临时加入三个

1
2
3
insert into `student` values('09' , '王菊' , '1990-01-20' , '');
insert into `student` values('10' , '王菊' , '1990-01-20' , '');
insert into `student` values('11' , '赵雷' , '1991-01-01' , '');

查询结果

1
2
3
4
5
6
mysql> select *,count(sname) from student group by sname,ssex having(count(sname))>1;
+------+--------+---------------------+------+--------------+
| sid  | sname  | sage                | ssex | count(sname) |
+------+--------+---------------------+------+--------------+
| 08   |    | 1990-01-20 00:00:00 |    |            2 |
+------+--------+---------------------+------+--------------+

如果不要求性别也相同,就是下面这个结果

1
2
3
4
5
6
7
mysql> select *,count(sname) from student group by sname having(count(sname))>1;
+------+--------+---------------------+------+--------------+
| sid  | sname  | sage                | ssex | count(sname) |
+------+--------+---------------------+------+--------------+
| 08   |    | 1990-01-20 00:00:00 |    |            3 |
| 01   |    | 1990-01-01 00:00:00 |    |            2 |
+------+--------+---------------------+------+--------------+

29、查询 1990 年出生的学生名单

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql> select * from student where sage like '1990%';
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 01   |    | 1990-01-01 00:00:00 |    |
| 02   |    | 1990-12-21 00:00:00 |    |
| 03   |    | 1990-05-20 00:00:00 |    |
| 04   |    | 1990-08-06 00:00:00 |    |
| 08   |    | 1990-01-20 00:00:00 |    |
| 10   |    | 1990-01-20 00:00:00 |    |
+------+--------+---------------------+------+
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql> select * from student where year(sage)=1990;
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 01   |    | 1990-01-01 00:00:00 |    |
| 02   |    | 1990-12-21 00:00:00 |    |
| 03   |    | 1990-05-20 00:00:00 |    |
| 04   |    | 1990-08-06 00:00:00 |    |
| 08   |    | 1990-01-20 00:00:00 |    |
| 10   |    | 1990-01-20 00:00:00 |    |
+------+--------+---------------------+------+

30、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
原来的数据效果不明显,所以再加一个

1
2
3
insert into `sc` values('11' , '01' , 24);
--  算错了,总数一样了,但是平均数不一样,再加一个
insert into `sc` values('10' , '01' , 137);
1
2
3
4
5
6
7
8
9
mysql> select sid, cid, avg(score) as avg_score from sc 
    group by cid order by avg_score desc, sid asc;
+------+------+-----------+
| sid  | cid  | avg_score |
+------+------+-----------+
| 01   | 02   |  72.66667 |
| 01   | 01   |  68.50000 |
| 01   | 03   |  68.50000 |
+------+------+-----------+

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

1
2
3
4
5
6
7
8
9
mysql> select st.sid,st.sname, avg(sc.score) as avg_score from student as st 
    right join sc on st.sid = sc.sid group by sc.sid having avg_score > 85;
+------+--------+-----------+
| sid  | sname  | avg_score |
+------+--------+-----------+
| 01   |    |  89.66667 |
| 07   |    |  93.50000 |
| 10   |    | 137.00000 |
+------+--------+-----------+

32、查询课程名称为“数学”,且分数低于 60 的学生姓名和分数
因为有成绩的话,在 student 表里肯定是有数据的,所以这里用 inner join,反之则应该用 right join

1
2
3
4
5
6
7
8
mysql> select st.sname,sct.score from student as st inner join 
    (select sc.* from sc inner join course on sc.cid = course.cid 
    where course.cname='数学' and sc.score < 60) as sct on sct.sid=st.sid;
+--------+-------+
| sname  | score |
+--------+-------+
|    |  30.0 |
+--------+-------+

33、 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
08和09并没有选课

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select st.sid,st.sname,sc.cid,sc.score from student as st left join sc on st.sid = sc.sid;
+------+--------+------+-------+
| sid  | sname  | cid  | score |
+------+--------+------+-------+
| 01   |    | 01   |  80.0 |
| 01   |    | 02   |  90.0 |
| 01   |    | 03   |  99.0 |
| 02   |    | 01   |  70.0 |
| 02   |    | 02   |  60.0 |
| 02   |    | 03   |  80.0 |
| 03   |    | 01   |  80.0 |
| 03   |    | 02   |  80.0 |
| 03   |    | 03   |  80.0 |
| 04   |    | 01   |  50.0 |
| 04   |    | 02   |  30.0 |
| 04   |    | 03   |  20.0 |
| 05   |    | 01   |  76.0 |
| 05   |    | 02   |  87.0 |
| 06   |    | 01   |  31.0 |
| 06   |    | 03   |  34.0 |
| 07   |    | 02   |  89.0 |
| 07   |    | 03   |  98.0 |
| 11   |    | 01   |  24.0 |
| 10   |    | 01   | 137.0 |
| 08   |    | NULL |  NULL |
| 09   |    | NULL |  NULL |
+------+--------+------+-------+

如果要排除没有选课的,则应该用 inner join

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> select st.sid,st.sname,sc.cid,sc.score from student as st inner join sc on st.sid = sc.sid;
+------+--------+------+-------+
| sid  | sname  | cid  | score |
+------+--------+------+-------+
| 01   |    | 01   |  80.0 |
| 01   |    | 02   |  90.0 |
| 01   |    | 03   |  99.0 |
| 02   |    | 01   |  70.0 |
| 02   |    | 02   |  60.0 |
| 02   |    | 03   |  80.0 |
| 03   |    | 01   |  80.0 |
| 03   |    | 02   |  80.0 |
| 03   |    | 03   |  80.0 |
| 04   |    | 01   |  50.0 |
| 04   |    | 02   |  30.0 |
| 04   |    | 03   |  20.0 |
| 05   |    | 01   |  76.0 |
| 05   |    | 02   |  87.0 |
| 06   |    | 01   |  31.0 |
| 06   |    | 03   |  34.0 |
| 07   |    | 02   |  89.0 |
| 07   |    | 03   |  98.0 |
| 11   |    | 01   |  24.0 |
| 10   |    | 01   | 137.0 |
+------+--------+------+-------+

34、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
先判断各个分数是否在70以上

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> select *, 
        case 
        when score > 70 
        then 1 
        else 0 
        end as seven 
        from sc;
+------+------+-------+-------+
| sid  | cid  | score | seven |
+------+------+-------+-------+
| 01   | 01   |  80.0 |     1 |
| 01   | 02   |  90.0 |     1 |
| 01   | 03   |  99.0 |     1 |
| 02   | 01   |  70.0 |     0 |
| 02   | 02   |  60.0 |     0 |
| 02   | 03   |  80.0 |     1 |
| 03   | 01   |  80.0 |     1 |
| 03   | 02   |  80.0 |     1 |
| 03   | 03   |  80.0 |     1 |
| 04   | 01   |  50.0 |     0 |
| 04   | 02   |  30.0 |     0 |
| 04   | 03   |  20.0 |     0 |
| 05   | 01   |  76.0 |     1 |
| 05   | 02   |  87.0 |     1 |
| 06   | 01   |  31.0 |     0 |
| 06   | 03   |  34.0 |     0 |
| 07   | 02   |  89.0 |     1 |
| 07   | 03   |  98.0 |     1 |
| 11   | 01   |  24.0 |     0 |
| 10   | 01   | 137.0 |     1 |
+------+------+-------+-------+

查询符合条件的 sid

1
2
3
4
5
6
7
8
9
mysql> select *,sum(seven) as num from 
    (select *, case when score > 70 then 1 else 0 end as seven from sc) 
    as sct group by sct.sid having num = (select count(1) from course);
+------+------+-------+-------+------+
| sid  | cid  | score | seven | num  |
+------+------+-------+-------+------+
| 01   | 01   |  80.0 |     1 |    3 |
| 03   | 01   |  80.0 |     1 |    3 |
+------+------+-------+-------+------+

跟 student 表连接,查询学生姓名

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> select * from student as st inner join (select *,sum(seven) as num 
    from (select *, case when score > 70 then 1 else 0 end as seven from sc)
     as sct group by sct.sid having num = (select count(1) from course)) 
    as sc01 on sc01.sid = st.sid;
+------+--------+---------------------+------+------+------+-------+-------+------+
| sid  | sname  | sage                | ssex | sid  | cid  | score | seven | num  |
+------+--------+---------------------+------+------+------+-------+-------+------+
| 01   |    | 1990-01-01 00:00:00 |    | 01   | 01   |  80.0 |     1 |    3 |
| 03   |    | 1990-05-20 00:00:00 |    | 03   | 01   |  80.0 |     1 |    3 |
+------+--------+---------------------+------+------+------+-------+-------+------+

查询所有的课程 id

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
mysql> select * from sc inner join 
    (select sid from (select *, case when score > 70 then 1 else 0 end as seven from sc) 
    as sct group by sct.sid having sum(seven) = (select count(1) from course)) 
    as sc01 on sc.sid = sc01.sid;
+------+------+-------+------+
| sid  | cid  | score | sid  |
+------+------+-------+------+
| 01   | 01   |  80.0 | 01   |
| 01   | 02   |  90.0 | 01   |
| 01   | 03   |  99.0 | 01   |
| 03   | 01   |  80.0 | 03   |
| 03   | 02   |  80.0 | 03   |
| 03   | 03   |  80.0 | 03   |
+------+------+-------+------+

查询课程名称

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
mysql> select co.cid,co.cname,sc02.score,sc02.sid from course as co right join 
    (select sc.* from sc inner join (select sid from 
    (select *, case when score > 70 then 1 else 0 end as seven from sc) as sct 
    group by sct.sid having sum(seven) = (select count(1) from course)) as sc01 
    on sc.sid = sc01.sid) as sc02 on sc02.cid=co.cid;
+------+--------+-------+------+
| cid  | cname  | score | sid  |
+------+--------+-------+------+
| 01   |    |  80.0 | 01   |
| 01   |    |  80.0 | 03   |
| 02   |    |  90.0 | 01   |
| 02   |    |  80.0 | 03   |
| 03   |    |  99.0 | 01   |
| 03   |    |  80.0 | 03   |
+------+--------+-------+------+

最后将课程名称和学生信息合并

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
select st01.sname as '姓名',sc03.cname as '课程',sc03.score as '分数' from 
    (select st.sname,st.sid from student as st inner join (select *,sum(seven) as num 
    from (select *, case when score > 70 then 1 else 0 end as seven from sc)
     as sct group by sct.sid having num = (select count(1) from course)) 
    as sc01 on sc01.sid = st.sid) as st01 inner join (
select co.cid,co.cname,sc02.score,sc02.sid from course as co right join 
    (select sc.* from sc inner join (select sid from 
    (select *, case when score > 70 then 1 else 0 end as seven from sc) as sct 
    group by sct.sid having sum(seven) = (select count(1) from course)) as sc01 
    on sc.sid = sc01.sid) as sc02 on sc02.cid=co.cid
) as sc03 on st01.sid = sc03.sid;
+--------+--------+--------+
|    |    |    |
+--------+--------+--------+
|    |    |   80.0 |
|    |    |   80.0 |
|    |    |   90.0 |
|    |    |   80.0 |
|    |    |   99.0 |
|    |    |   80.0 |
+--------+--------+--------+

35、查询不及格的课程
感觉题意不清晰,没啥意义

1
2
3
4
5
6
7
8
mysql> select cid from sc where score < 60 group by cid;
+------+
| cid  |
+------+
| 01   |
| 02   |
| 03   |
+------+

36、查询课程编号为“01”且课程成绩在 80 分以上的学生的学号和姓名

1
2
3
4
5
6
7
mysql> select st.sid as '学号',st.sname as '姓名' from student as st 
    where sid in (select sid from sc where cid='01' and score > 80);
+--------+--------+
|    |    |
+--------+--------+
| 10     |    |
+--------+--------+
1
2
3
4
5
6
7
mysql> select st.sid as '学号',st.sname as '姓名' from student as st inner join 
    (select sid from sc where cid='01' and score > 80) as sc01 on sc01.sid = st.sid;
+--------+--------+
|    |    |
+--------+--------+
| 10     |    |
+--------+--------+

下面这样更合适一些吧

1
2
3
4
5
6
7
mysql> select st.sid as '学号',st.sname as '姓名' from student as st inner join 
    sc on st.sid=sc.sid where sc.cid='01' and sc.score > 80 ;
+--------+--------+
|    |    |
+--------+--------+
| 10     |    |
+--------+--------+

37、求每门课程的学生人数
简版的

1
2
3
4
5
6
7
8
mysql> select cid,count(sid) from sc group by cid;
+------+------------+
| cid  | count(sid) |
+------+------------+
| 01   |          8 |
| 02   |          6 |
| 03   |          6 |
+------+------------+

把课程名称也展示出来

1
2
3
4
5
6
7
8
9
mysql> select sc.cid as '课程id', co.cname as '课程名称', count(sc.sid) as '人数' 
    from sc inner join course as co on co.cid = sc.cid group by sc.cid;
+----------+--------------+--------+
| id   |      |    |
+----------+--------------+--------+
| 01       |          |      8 |
| 02       |          |      6 |
| 03       |          |      6 |
+----------+--------------+--------+

38、成绩有重复的情况下,查询选修“张三”老师所授课程的学生中,成绩最高的学生信息及其成绩
先找到张三老师的授课信息

1
2
3
4
5
6
7
mysql> select co.* from course as co inner join teacher as t 
    on co.tid = t.tid where t.tname='张三';
+------+--------+------+
| cid  | cname  | tid  |
+------+--------+------+
| 02   |    | 01   |
+------+--------+------+

再找到选修张三老师课程的学生成绩信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> select sc.* from sc inner join course as co on sc.cid = co.cid 
    inner join teacher as t on co.tid = t.tid where t.tname='张三';
+------+------+-------+
| sid  | cid  | score |
+------+------+-------+
| 01   | 02   |  90.0 |
| 02   | 02   |  60.0 |
| 03   | 02   |  80.0 |
| 04   | 02   |  30.0 |
| 05   | 02   |  87.0 |
| 07   | 02   |  89.0 |
+------+------+-------+

制造最高分重复的情况

1
insert into `sc` values('08' , '02' , 90);

找出最高的分数

1
2
3
4
5
6
7
mysql> select max(score) from sc inner join course as co on sc.cid = co.cid 
    inner join teacher as t on co.tid = t.tid where t.tname='张三';
+------------+
| max(score) |
+------------+
|       90.0 |
+------------+

找出最高分的学生ID

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> select sc.* from sc inner join course as co on sc.cid = co.cid 
    inner join teacher as t on co.tid = t.tid where t.tname='张三' 
    and sc.score in ( select max(score) from sc inner join course as co 
    on sc.cid = co.cid inner join teacher as t on co.tid = t.tid where t.tname='张三');
+------+------+-------+
| sid  | cid  | score |
+------+------+-------+
| 01   | 02   |  90.0 |
| 08   | 02   |  90.0 |
+------+------+-------+

最后查找学生信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql> select st.*,sc.score from student as st inner join sc on st.sid = sc.sid 
    inner join course as co on sc.cid = co.cid 
    inner join teacher as t on co.tid = t.tid where t.tname='张三' 
    and sc.score in ( select max(score) from sc inner join course as co 
    on sc.cid = co.cid inner join teacher as t on co.tid = t.tid where t.tname='张三');
+------+--------+---------------------+------+-------+
| sid  | sname  | sage                | ssex | score |
+------+--------+---------------------+------+-------+
| 01   |    | 1990-01-01 00:00:00 |    |  90.0 |
| 08   |    | 1990-01-20 00:00:00 |    |  90.0 |
+------+--------+---------------------+------+-------+

如果没有重复的话,直接排序取第一条就行,会简单很多

39、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
感觉题意有问题,参考原文的写法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> select distinct sc01.* from sc as sc01 inner join sc sc02 where sc01.score = sc02.score and sc01.cid != sc02.cid;
+------+------+-------+
| sid  | cid  | score |
+------+------+-------+
| 02   | 03   |  80.0 |
| 03   | 02   |  80.0 |
| 03   | 03   |  80.0 |
| 01   | 01   |  80.0 |
| 03   | 01   |  80.0 |
+------+------+-------+

40、查询每门成绩最好的前两名
感觉很神奇的写法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
mysql> select * from sc where (select count(*) from sc as sc01 where sc.cid = sc01.cid 
    and sc.score < sc01.score) < 2 order by cid asc, sc.score desc;
+------+------+-------+
| sid  | cid  | score |
+------+------+-------+
| 10   | 01   | 137.0 |
| 01   | 01   |  80.0 |
| 03   | 01   |  80.0 |
| 01   | 02   |  90.0 |
| 08   | 02   |  90.0 |
| 01   | 03   |  99.0 |
| 07   | 03   |  98.0 |
+------+------+-------+

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

1
2
3
4
5
6
7
8
mysql> select cid,count(sid) from sc group by cid having count(sid) > 5;
+------+------------+
| cid  | count(sid) |
+------+------------+
| 01   |          8 |
| 02   |          7 |
| 03   |          6 |
+------+------------+

42、检索至少选修两门课程的学生学号

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> select sid from sc group by sid having count(cid) >= 2;
+------+
| sid  |
+------+
| 01   |
| 02   |
| 03   |
| 04   |
| 05   |
| 06   |
| 07   |
+------+

43、查询选修了全部课程的学生信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> select st.* from student as st inner join sc on st.sid = sc.sid group by sc.sid 
    having count(sc.cid) = (select count(*) from course);
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 01   |    | 1990-01-01 00:00:00 |    |
| 02   |    | 1990-12-21 00:00:00 |    |
| 03   |    | 1990-05-20 00:00:00 |    |
| 04   |    | 1990-08-06 00:00:00 |    |
+------+--------+---------------------+------+

44、查询各学生的年龄,只按年份来算
需要用的日期相关的函数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
mysql> select sname, year(now()) - year(sage) as '年纪' from student;
+--------+--------+
| sname  |    |
+--------+--------+
|    |     30 |
|    |     30 |
|    |     30 |
|    |     30 |
|    |     29 |
|    |     28 |
|    |     31 |
|    |     30 |
|    |     29 |
|    |     30 |
|    |     29 |
+--------+--------+

45、按照出生日期来算,当前月日 < 出生年月的月日,则年龄减一
利用 case when 进行计算

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select sname, case
       when (date_format(now(), '%m-%d') - date_format(sage, '%m-%d')) < 0
       then year(now()) - year(sage) - 1
       else year(now()) - year(sage)
       end as '年纪'
       from student;
+--------+--------+
| sname  |    |
+--------+--------+
|    |     30 |
|    |     29 |
|    |     29 |
|    |     29 |
|    |     28 |
|    |     28 |
|    |     30 |
|    |     30 |
|    |     29 |
|    |     30 |
|    |     29 |
+--------+--------+

46、查询本周过生日的学生
利用 week 函数

1
2
3
4
5
6
7
8
 // 
mysql> insert into `student` values('13' , '本周' , '2020-03-17' , '');
mysql> select * from student where week(sage) = week(now());
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 13   |    | 2020-03-17 00:00:00 |    |
+------+--------+---------------------+------+

47、查询下周过生日的学生
这道题应该不能简单的用当前的周数加1,万一是每年的最后一周呢?
所以这里在计算下周的周数是用当前时间直接加1周

1
2
3
4
5
6
7
8
mysql> insert into `student` values('13' , '下周' , '2020-03-25' , '');

mysql> select * from student where week(sage) = week(now() + interval 1 week);
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 13   |    | 2020-03-25 00:00:00 |    |
+------+--------+---------------------+------+

48、查询本月过生日的学生

1
2
3
4
5
6
mysql> select * from student where month(sage) = month(now());
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 06   |    | 1992-03-01 00:00:00 |    |
+------+--------+---------------------+------+

49、查询下月过生日的学生
类似下周,不能在当前的基础上加1
先造一条4月的数据

1
insert into `student` values('12' , '四月' , '1990-04-20' , '');
1
2
3
4
5
6
mysql> select * from student where month(sage) = month(now() + interval 1 month);
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 12   |    | 1990-04-20 00:00:00 |    |
+------+--------+---------------------+------+