mysql所有选修课程都及格_mysql多表查询20题

1、查询所有的课程的名称以及对应的任课老师姓名

mysql> select course.cname,teacher.tname from course inner join teacher on course.teacher_id = teacher.id;

+--------+-----------------+

| cname | tname |

+--------+-----------------+

| 生物 | 张磊老师 |

| 物理 | 李平老师 |

| 美术 | 李平老师 |

| 体育 | 刘海燕老师 |

+--------+-----------------+

2、查询学生表中男女生各有多少人

mysql> select gender,count(gender) as male_count from student where gender = 'male' group by gender ;

+--------+------------+

| gender | male_count |

+--------+------------+

| male | 4 |

+--------+------------+

1 row in set (0.00 sec)

mysql> select gender,count(gender) as male_count from student where gender = 'female' group by gender ;

+--------+------------+

| gender | male_count |

+--------+------------+

| female | 2 |

+--------+------------+

3、查询物理成绩等于100的学生的姓名

mysql> select sname from (select score.student_id,score.number,student.id,student.sname

from score inner join student where student.id = score.student_id) as t1 where number = 100;

+--------+

| sname |

+--------+

| 铁蛋 |

+--------+

4、查询成绩大于八十分的同学的姓名和平均成绩

mysql> select student.id,student.sname,score.student_id,score.number from student inner join

score on student.id = score.student_id where score.number > 80 ;

+----+--------+------------+--------+

| id | sname | student_id | number |

+----+--------+------------+--------+

| 3 | 铜蛋 | 3 | 90 |

| 1 | 铁蛋 | 1 | 100 |

| 4 | 银弹 | 4 | 98 |

+----+--------+------------+--------+

mysql> select avg(number) from (select student.id,student.sname,score.student_id,score.number

from student inner join score on student.id = score.student_id where score.number > 80) as t;

+-------------+

| avg(number) |

+-------------+

| 96.0000 |

+-------------+

5、查询所有学生的学号，姓名,总成绩

mysql> select student.id,student.sname,t1.total_num from student left join(select score.student_id,

sum(score.number) as total_num from score group by score.student_id) as t1 on student.id = t1.student_id;

+----+--------+-----------+

| id | sname | total_num |

+----+--------+-----------+

| 1 | 铁蛋 | 100 |

| 2 | 钢蛋 | 80 |

| 3 | 铜蛋 | 90 |

| 4 | 银弹 | 98 |

| 5 | ety | NULL |

| 6 | yuu | NULL |

+----+--------+-----------+

6、 查询姓李老师的个数

mysql> select tname from teacher where tname like '李%';

+--------------+

| tname |

+--------------+

| 李平老师 |

| 李杰老师 |

+--------------+

7、 查询没有报李平老师课的学生姓名(未完成)

结果：学生姓名

查看李平老师教授的课程，然后和学生表建立关联

select student.sname,student.id from student left join

(select teacher.id,course.teacher_id,course.cname,teacher.tname from teacher

left join course on teacher.id = course.teacher_id) as t1 ;

8、 查询物理课程比生物课程高的学生的学号

结果：学生的学号-student.id

score绑定课程

mysql> select t1.student_id from (select student_id,number from score

where course_id = (select id from course where cname = '物理')) as t1 inner join

(select student_id,number from score where course_id = (select id from course where cname = '生物'))

as t2 on t1.student_id = t2.student_id where t1.number > t2.number;

+------------+

| student_id |

+------------+

| 2 |

+------------+

9、 查询没有同时选修物理课程和体育课程的学生姓名

结果：学生姓名

select student.sname from student where id in(select student_id from score where

course_id in(select id from course where cname = '物理' or cname = '体育')

group by student_id having count(course_id) = 1);

10、查询挂科超过两门(包括两门)的学生姓名和班级

结果：学生姓名和班级

mysql> select student.sname,class.caption from student inner join(select student_id from score where number <60

group by student_id having count(course_id) >= 2) as t1 inner join class on student.id = t1.student_id and

student.class_id = class.id;

流程注释：select 字段 from table. table里有学生姓名和班级，假设先把学生表和班级表连接起来，他们有共同字段class_id，内连接后可得到一张

学生对应班级的表。然后查询score表里的学生的每门成绩，如果有两门不到60的，获得该学生student_id，然后把上个表当作参数传入。

+--------+--------------+

| sname | caption |

+--------+--------------+

| 钢蛋 | 三年二班 |

+--------+--------------+

11 、查询选修了所有课程的学生姓名

mysql> select student.sname from student where id in (select student_id from score group by student_id having

count(course_id) = (select count(id) from course));

Empty set (0.14 sec)

12、查询李平老师教的课程的所有成绩记录

mysql> select * from score where course_id in (select id from course inner join

teacher on course.teacher_id = teacher.id where teacher.tname = '李平老师');

13、查询全部学生都选修了的课程号和课程名

mysql> select id,cname from course where id in (select course_id from score group by course_id

having count(student_id) = (select count(id) from student));

Empty set (0.01 sec)

14、查询每门课程被选修的次数

mysql> select course_id,count(student_id) from score group by course_id;

+-----------+-------------------+

| course_id | count(student_id) |

+-----------+-------------------+

| 1 | 2 |

| 2 | 1 |

| 3 | 1 |

+-----------+-------------------+

15、查询只选修了一门课程的学生姓名和学号

mysql> select id,sname from student where id in (select student_id from score group by student_id having count(course_id) = 1);

+----+--------+

| id | sname |

+----+--------+

| 1 | 铁蛋 |

| 3 | 铜蛋 |

+----+--------+

16、查询所有学生考出的成绩并按从高到低排序(成绩去重)

mysql> select distinct number from score order by number desc;

+--------+

| number |

+--------+

| 100 |

| 90 |

| 58 |

| 56 |

+--------+

17、查询平均成绩大于85的学生姓名和平均成绩

mysql> select sname,t1.avg_num from student inner join (select student_id,avg(number) as avg_num from score group by student_id having avg(number) >85) as t1 on student.id = t1.student_id;

+--------+----------+

| sname | avg_num |

+--------+----------+

| 铁蛋 | 100.0000 |

| 铜蛋 | 90.0000 |

+--------+----------+

2 rows in set (0.11 sec)

18、查询生物成绩不及格的学生姓名和对应生物分数

SELECT

sname ,

number

FROM

score

LEFT JOIN course ON score.course_id = course.id

LEFT JOIN student ON score.student_id = student.id

WHERE

course.cname = '生物'

AND score.number < 60;

+--------+--------+

| sname | number |

+--------+--------+

| 钢蛋 | 58 |

+--------+--------+

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

SELECT

sname

FROM

student

WHERE

id = (

SELECT

student_id

FROM

score

WHERE

course_id IN (

SELECT

course.id

FROM

course

INNER JOIN teacher ON course.teacher_id = teacher.id

WHERE

teacher.tname = '李平老师'

)

GROUP BY

student_id

ORDER BY

AVG(number) DESC

LIMIT 1

);

+--------+

| sname |

+--------+

| 钢蛋 |

+--------+

20、查询每门课程成绩最好的前两名学生姓名