MySQL数据库服务作业讲解-Day02
数据库多表连接查询综合练习
查看获取表结构信息，根据表结构信进行连表查询
mysql> use school;
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course |
| sc |
| student |
| teacher |
+------------------+
7 rows in set (0.00 sec)
mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| sno | int | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | NO | | NULL | |
| sage | tinyint unsigned | NO | | NULL | |
| ssex | enum('f','m') | NO | | m | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno | int | NO | PRI | NULL | |
| cname | varchar(20) | NO | | NULL | |
| tno | int | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc sc;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| sno | int | NO | | NULL | |
| cno | int | NO | | NULL | |
| score | int | NO | | 0 | |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc teacher;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| tno | int | NO | PRI | NULL | |
| tname | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
01 查询平均成绩大于60分的同学的学号和平均成绩
根据题目需求所需调用表信息
# 通过 student表和sc表建立关联，获取学号和平均成绩
student sc
根据题目需求建立表与表关联
select * from student
join sc on student.sno=sc.sno
+-----+---------+------+------+-----+------+-------+
| sno | sname | sage | ssex | sno | cno | score |
+-----+---------+------+------+-----+------+-------+
| 1 | zhang3 | 18 | m | 1 | 1001 | 80 |
| 1 | zhang3 | 18 | m | 1 | 1002 | 59 |
| 2 | zhang4 | 18 | m | 2 | 1002 | 90 |
| 2 | zhang4 | 18 | m | 2 | 1003 | 100 |
| 3 | li4 | 18 | m | 3 | 1001 | 99 |
| 3 | li4 | 18 | m | 3 | 1003 | 40 |
| 4 | wang5 | 19 | f | 4 | 1001 | 79 |
| 4 | wang5 | 19 | f | 4 | 1002 | 61 |
| 4 | wang5 | 19 | f | 4 | 1003 | 99 |
| 5 | zh4 | 18 | m | 5 | 1003 | 40 |
| 6 | zhao4 | 18 | m | 6 | 1001 | 89 |
| 6 | zhao4 | 18 | m | 6 | 1003 | 77 |
| 7 | ma6 | 19 | f | 7 | 1001 | 67 |
| 7 | ma6 | 19 | f | 7 | 1003 | 82 |
| 8 | oldboy | 20 | m | 8 | 1001 | 70 |
| 9 | oldgirl | 20 | f | 9 | 1003 | 80 |
| 10 | oldp | 25 | m | 10 | 1003 | 96 |
+-----+---------+------+------+-----+------+-------+
根据题目需求进行相应处理操作
# 根据学员编号进行分组，获取每个学员的平均成绩
select student.sno,avg(sc.score) from student
join sc on student.sno=sc.sno
group by student.sno
+-----+---------------+
| sno | avg(sc.score) |
+-----+---------------+
| 1 | 69.5000 |
| 2 | 95.0000 |
| 3 | 69.5000 |
| 4 | 79.6667 |
| 5 | 40.0000 |
| 6 | 83.0000 |
| 7 | 74.5000 |
| 8 | 70.0000 |
| 9 | 80.0000 |
| 10 | 96.0000 |
+-----+---------------+
10 rows in set (0.00 sec)
# 完成题意需求
mysql> select student.sno as "学号",avg(sc.score) as "成绩平均分" from student
join sc on student.sno=sc.sno
group by student.sno
having avg(sc.score)>60;
+--------+-----------------+
| 学号 | 成绩平均分 |
+--------+-----------------+
| 1 | 69.5000 |
| 2 | 95.0000 |
| 3 | 69.5000 |
| 4 | 79.6667 |
| 6 | 83.0000 |
| 7 | 74.5000 |
| 8 | 70.0000 |
| 9 | 80.0000 |
| 10 | 96.0000 |
+--------+-----------------+
9 rows in set (0.00 sec)
02 查询所有同学的学号，姓名。选课数，总成绩
根据题目需求所需调用表信息
# 通过 student表和sc表建立关联，再通过sc表和course表建立关联，获取学号、姓名、课程数、总成绩
student course sc
根据题目需求建立表与表关联
select * from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| sno | sname | sage | ssex | sno | cno | score | cno | cname | tno |
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| 1 | zhang3 | 18 | m | 1 | 1001 | 80 | 1001 | linux | 101 |
| 1 | zhang3 | 18 | m | 1 | 1002 | 59 | 1002 | python | 102 |
| 2 | zhang4 | 18 | m | 2 | 1002 | 90 | 1002 | python | 102 |
| 2 | zhang4 | 18 | m | 2 | 1003 | 100 | 1003 | mysql | 103 |
| 3 | li4 | 18 | m | 3 | 1001 | 99 | 1001 | linux | 101 |
| 3 | li4 | 18 | m | 3 | 1003 | 40 | 1003 | mysql | 103 |
| 4 | wang5 | 19 | f | 4 | 1001 | 79 | 1001 | linux | 101 |
| 4 | wang5 | 19 | f | 4 | 1002 | 61 | 1002 | python | 102 |
| 4 | wang5 | 19 | f | 4 | 1003 | 99 | 1003 | mysql | 103 |
| 5 | zh4 | 18 | m | 5 | 1003 | 40 | 1003 | mysql | 103 |
| 6 | zhao4 | 18 | m | 6 | 1001 | 89 | 1001 | linux | 101 |
| 6 | zhao4 | 18 | m | 6 | 1003 | 77 | 1003 | mysql | 103 |
| 7 | ma6 | 19 | f | 7 | 1001 | 67 | 1001 | linux | 101 |
| 7 | ma6 | 19 | f | 7 | 1003 | 82 | 1003 | mysql | 103 |
| 8 | oldboy | 20 | m | 8 | 1001 | 70 | 1001 | linux | 101 |
| 9 | oldgirl | 20 | f | 9 | 1003 | 80 | 1003 | mysql | 103 |
| 10 | oldp | 25 | m | 10 | 1003 | 96 | 1003 | mysql | 103 |
+-----+---------+------+------+-----+------+-------+------+--------+-----+
根据题目需求进行相应处理操作
select student.sno,student.sname,count(course.cno),sum(sc.score) from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by student.sno;
+-----+---------+-------------------+---------------+
| sno | sname | count(course.cno) | sum(sc.score) |
+-----+---------+-------------------+---------------+
| 1 | zhang3 | 2 | 139 |
| 2 | zhang4 | 2 | 190 |
| 3 | li4 | 2 | 139 |
| 4 | wang5 | 3 | 239 |
| 5 | zh4 | 1 | 40 |
| 6 | zhao4 | 2 | 166 |
| 7 | ma6 | 2 | 149 |
| 8 | oldboy | 1 | 70 |
| 9 | oldgirl | 1 | 80 |
| 10 | oldp | 1 | 96 |
+-----+---------+-------------------+---------------+
10 rows in set (0.00 sec)
03 查询各科成绩最高和最低的分，以如下形式显示：课程ID，最高分，最低分
根据题目需求所需调用表信息
# 通过sc表和course表建立关联，获取课程ID、最高分、最低分
course sc
根据题目需求建立表与表关联
select * from course
join sc on course.cno=sc.cno;
+------+--------+-----+-----+------+-------+
| cno | cname | tno | sno | cno | score |
+------+--------+-----+-----+------+-------+
| 1001 | linux | 101 | 1 | 1001 | 80 |
| 1002 | python | 102 | 1 | 1002 | 59 |
| 1002 | python | 102 | 2 | 1002 | 90 |
| 1003 | mysql | 103 | 2 | 1003 | 100 |
| 1001 | linux | 101 | 3 | 1001 | 99 |
| 1003 | mysql | 103 | 3 | 1003 | 40 |
| 1001 | linux | 101 | 4 | 1001 | 79 |
| 1002 | python | 102 | 4 | 1002 | 61 |
| 1003 | mysql | 103 | 4 | 1003 | 99 |
| 1003 | mysql | 103 | 5 | 1003 | 40 |
| 1001 | linux | 101 | 6 | 1001 | 89 |
| 1003 | mysql | 103 | 6 | 1003 | 77 |
| 1001 | linux | 101 | 7 | 1001 | 67 |
| 1003 | mysql | 103 | 7 | 1003 | 82 |
| 1001 | linux | 101 | 8 | 1001 | 70 |
| 1003 | mysql | 103 | 9 | 1003 | 80 |
| 1003 | mysql | 103 | 10 | 1003 | 96 |
+------+--------+-----+-----+------+-------+
17 rows in set (0.00 sec)
根据题目需求进行相应处理操作
select course.cno,max(sc.score),min(sc.score) from course
join sc on course.cno=sc.cno
group by course.cno;
+------+---------------+---------------+
| cno | max(sc.score) | min(sc.score) |
+------+---------------+---------------+
| 1001 | 99 | 67 |
| 1002 | 90 | 59 |
| 1003 | 100 | 40 |
+------+---------------+---------------+
3 rows in set (0.00 sec)
04 统计各位老师，所教课程的及格率
根据题目需求所需调用表信息
# 通过teacher表和course表建立关联，再通过course表和sc表建立关联，获取各位老师所教课程的及格率
teacher course sc
根据题目需求建立表与表关联
select * from teacher
join course on teacher.tno=course.tno
join sc on course.cno=sc.cno;
+-----+--------+------+--------+-----+-----+------+-------+
| tno | tname | cno | cname | tno | sno | cno | score |
+-----+--------+------+--------+-----+-----+------+-------+
| 101 | oldboy | 1001 | linux | 101 | 1 | 1001 | 80 |
| 102 | xiaoQ | 1002 | python | 102 | 1 | 1002 | 59 |
| 102 | xiaoQ | 1002 | python | 102 | 2 | 1002 | 90 |
| 103 | xiaoA | 1003 | mysql | 103 | 2 | 1003 | 100 |
| 101 | oldboy | 1001 | linux | 101 | 3 | 1001 | 99 |
| 103 | xiaoA | 1003 | mysql | 103 | 3 | 1003 | 40 |
| 101 | oldboy | 1001 | linux | 101 | 4 | 1001 | 79 |
| 102 | xiaoQ | 1002 | python | 102 | 4 | 1002 | 61 |
| 103 | xiaoA | 1003 | mysql | 103 | 4 | 1003 | 99 |
| 103 | xiaoA | 1003 | mysql | 103 | 5 | 1003 | 40 |
| 101 | oldboy | 1001 | linux | 101 | 6 | 1001 | 89 |
| 103 | xiaoA | 1003 | mysql | 103 | 6 | 1003 | 77 |
| 101 | oldboy | 1001 | linux | 101 | 7 | 1001 | 67 |
| 103 | xiaoA | 1003 | mysql | 103 | 7 | 1003 | 82 |
| 101 | oldboy | 1001 | linux | 101 | 8 | 1001 | 70 |
| 103 | xiaoA | 1003 | mysql | 103 | 9 | 1003 | 80 |
| 103 | xiaoA | 1003 | mysql | 103 | 10 | 1003 | 96 |
+-----+--------+------+--------+-----+-----+------+-------+
根据题目需求进行相应处理操作
需要先提前掌握数据库case判断语句用法：
case when 条件 then 结果 end
结合以上case语句信息完成查询需求
mysql> select teacher.tname,course.cname,concat(floor(count(case when sc.score>=60 then 1 end)/count(*)*100),"%") from teacher
join course on teacher.tno=course.tno
join sc on course.cno=sc.cno
group by teacher.tno,course.cno;
+--------+--------+--------------------------------------------------------------------------+
| tname | cname | concat(floor(count(case when sc.score>=60 then 1 end)/count(*)*100),"%") |
+--------+--------+--------------------------------------------------------------------------+
| oldboy | linux | 100% |
| xiaoQ | python | 66% |
| xiaoA | mysql | 75% |
+--------+--------+--------------------------------------------------------------------------+
3 rows in set (0.00 sec)
05 查询每门课程被选修的学生数
根据题目需求所需调用表信息
# 通过student表和sc表建立关联，再通过sc表和crouse表建立关联，获取每门课程选修的学生数量
student sc course
根据题目需求建立表与表关联
select * from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno;
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| sno | sname | sage | ssex | sno | cno | score | cno | cname | tno |
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| 1 | zhang3 | 18 | m | 1 | 1001 | 80 | 1001 | linux | 101 |
| 1 | zhang3 | 18 | m | 1 | 1002 | 59 | 1002 | python | 102 |
| 2 | zhang4 | 18 | m | 2 | 1002 | 90 | 1002 | python | 102 |
| 2 | zhang4 | 18 | m | 2 | 1003 | 100 | 1003 | mysql | 103 |
| 3 | li4 | 18 | m | 3 | 1001 | 99 | 1001 | linux | 101 |
| 3 | li4 | 18 | m | 3 | 1003 | 40 | 1003 | mysql | 103 |
| 4 | wang5 | 19 | f | 4 | 1001 | 79 | 1001 | linux | 101 |
| 4 | wang5 | 19 | f | 4 | 1002 | 61 | 1002 | python | 102 |
| 4 | wang5 | 19 | f | 4 | 1003 | 99 | 1003 | mysql | 103 |
| 5 | zh4 | 18 | m | 5 | 1003 | 40 | 1003 | mysql | 103 |
| 6 | zhao4 | 18 | m | 6 | 1001 | 89 | 1001 | linux | 101 |
| 6 | zhao4 | 18 | m | 6 | 1003 | 77 | 1003 | mysql | 103 |
| 7 | ma6 | 19 | f | 7 | 1001 | 67 | 1001 | linux | 101 |
| 7 | ma6 | 19 | f | 7 | 1003 | 82 | 1003 | mysql | 103 |
| 8 | oldboy | 20 | m | 8 | 1001 | 70 | 1001 | linux | 101 |
| 9 | oldgirl | 20 | f | 9 | 1003 | 80 | 1003 | mysql | 103 |
| 10 | oldp | 25 | m | 10 | 1003 | 96 | 1003 | mysql | 103 |
+-----+---------+------+------+-----+------+-------+------+--------+-----+
根据题目需求进行相应处理操作
select course.cname,count(*) from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by course.cno;
+--------+----------+
| cname | count(*) |
+--------+----------+
| linux | 6 |
| python | 3 |
| mysql | 8 |
+--------+----------+
3 rows in set (0.00 sec)
06 查询出只选修了一门课程的全部学生的学号和姓名
根据题目需求所需调用表信息
# 通过student表和sc表建立关联，再通过sc表和crouse表建立关联，获取只选修一门课程的学生姓名和学号
student sc course
根据题目需求建立表与表关联
select * from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno;
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| sno | sname | sage | ssex | sno | cno | score | cno | cname | tno |
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| 1 | zhang3 | 18 | m | 1 | 1001 | 80 | 1001 | linux | 101 |
| 1 | zhang3 | 18 | m | 1 | 1002 | 59 | 1002 | python | 102 |
| 2 | zhang4 | 18 | m | 2 | 1002 | 90 | 1002 | python | 102 |
| 2 | zhang4 | 18 | m | 2 | 1003 | 100 | 1003 | mysql | 103 |
| 3 | li4 | 18 | m | 3 | 1001 | 99 | 1001 | linux | 101 |
| 3 | li4 | 18 | m | 3 | 1003 | 40 | 1003 | mysql | 103 |
| 4 | wang5 | 19 | f | 4 | 1001 | 79 | 1001 | linux | 101 |
| 4 | wang5 | 19 | f | 4 | 1002 | 61 | 1002 | python | 102 |
| 4 | wang5 | 19 | f | 4 | 1003 | 99 | 1003 | mysql | 103 |
| 5 | zh4 | 18 | m | 5 | 1003 | 40 | 1003 | mysql | 103 |
| 6 | zhao4 | 18 | m | 6 | 1001 | 89 | 1001 | linux | 101 |
| 6 | zhao4 | 18 | m | 6 | 1003 | 77 | 1003 | mysql | 103 |
| 7 | ma6 | 19 | f | 7 | 1001 | 67 | 1001 | linux | 101 |
| 7 | ma6 | 19 | f | 7 | 1003 | 82 | 1003 | mysql | 103 |
| 8 | oldboy | 20 | m | 8 | 1001 | 70 | 1001 | linux | 101 |
| 9 | oldgirl | 20 | f | 9 | 1003 | 80 | 1003 | mysql | 103 |
| 10 | oldp | 25 | m | 10 | 1003 | 96 | 1003 | mysql | 103 |
+-----+---------+------+------+-----+------+-------+------+--------+-----+
根据题目需求进行相应处理操作
select student.sno,student.sname from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by student.sno
having count(course.cno)=1;
+-----+---------+
| sno | sname |
+-----+---------+
| 5 | zh4 |
| 8 | oldboy |
| 9 | oldgirl |
| 10 | oldp |
+-----+---------+
4 rows in set (0.01 sec)
07 查询选修课程门数超过1门的学生信息
根据题目需求所需调用表信息
# 通过student表和sc表建立关联，再通过sc表和crouse表建立关联，获取选修超过一门课程的学生姓名和学号
student sc course
根据题目需求建立表与表关联
select * from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno;
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| sno | sname | sage | ssex | sno | cno | score | cno | cname | tno |
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| 1 | zhang3 | 18 | m | 1 | 1001 | 80 | 1001 | linux | 101 |
| 1 | zhang3 | 18 | m | 1 | 1002 | 59 | 1002 | python | 102 |
| 2 | zhang4 | 18 | m | 2 | 1002 | 90 | 1002 | python | 102 |
| 2 | zhang4 | 18 | m | 2 | 1003 | 100 | 1003 | mysql | 103 |
| 3 | li4 | 18 | m | 3 | 1001 | 99 | 1001 | linux | 101 |
| 3 | li4 | 18 | m | 3 | 1003 | 40 | 1003 | mysql | 103 |
| 4 | wang5 | 19 | f | 4 | 1001 | 79 | 1001 | linux | 101 |
| 4 | wang5 | 19 | f | 4 | 1002 | 61 | 1002 | python | 102 |
| 4 | wang5 | 19 | f | 4 | 1003 | 99 | 1003 | mysql | 103 |
| 5 | zh4 | 18 | m | 5 | 1003 | 40 | 1003 | mysql | 103 |
| 6 | zhao4 | 18 | m | 6 | 1001 | 89 | 1001 | linux | 101 |
| 6 | zhao4 | 18 | m | 6 | 1003 | 77 | 1003 | mysql | 103 |
| 7 | ma6 | 19 | f | 7 | 1001 | 67 | 1001 | linux | 101 |
| 7 | ma6 | 19 | f | 7 | 1003 | 82 | 1003 | mysql | 103 |
| 8 | oldboy | 20 | m | 8 | 1001 | 70 | 1001 | linux | 101 |
| 9 | oldgirl | 20 | f | 9 | 1003 | 80 | 1003 | mysql | 103 |
| 10 | oldp | 25 | m | 10 | 1003 | 96 | 1003 | mysql | 103 |
+-----+---------+------+------+-----+------+-------+------+--------+-----+
根据题目需求进行相应处理操作
select student.sno,student.sname from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by student.sno
having count(course.cno)>1;
+-----+--------+
| sno | sname |
+-----+--------+
| 1 | zhang3 |
| 2 | zhang4 |
| 3 | li4 |
| 4 | wang5 |
| 6 | zhao4 |
| 7 | ma6 |
+-----+--------+
6 rows in set (0.00 sec)
08 统计每门课程：优秀（85分以上），良好（70-85），一般（60-70），不及格（小于60）的学生列表
根据题目需求所需调用表信息
# 通过student表和sc表建立关联，再通过sc表和crouse表建立关联，获取优秀 良好 一般 不及格的学生列表信息
student sc course
根据题目需求建立表与表关联
select * from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno;
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| sno | sname | sage | ssex | sno | cno | score | cno | cname | tno |
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| 1 | zhang3 | 18 | m | 1 | 1001 | 80 | 1001 | linux | 101 |
| 1 | zhang3 | 18 | m | 1 | 1002 | 59 | 1002 | python | 102 |
| 2 | zhang4 | 18 | m | 2 | 1002 | 90 | 1002 | python | 102 |
| 2 | zhang4 | 18 | m | 2 | 1003 | 100 | 1003 | mysql | 103 |
| 3 | li4 | 18 | m | 3 | 1001 | 99 | 1001 | linux | 101 |
| 3 | li4 | 18 | m | 3 | 1003 | 40 | 1003 | mysql | 103 |
| 4 | wang5 | 19 | f | 4 | 1001 | 79 | 1001 | linux | 101 |
| 4 | wang5 | 19 | f | 4 | 1002 | 61 | 1002 | python | 102 |
| 4 | wang5 | 19 | f | 4 | 1003 | 99 | 1003 | mysql | 103 |
| 5 | zh4 | 18 | m | 5 | 1003 | 40 | 1003 | mysql | 103 |
| 6 | zhao4 | 18 | m | 6 | 1001 | 89 | 1001 | linux | 101 |
| 6 | zhao4 | 18 | m | 6 | 1003 | 77 | 1003 | mysql | 103 |
| 7 | ma6 | 19 | f | 7 | 1001 | 67 | 1001 | linux | 101 |
| 7 | ma6 | 19 | f | 7 | 1003 | 82 | 1003 | mysql | 103 |
| 8 | oldboy | 20 | m | 8 | 1001 | 70 | 1001 | linux | 101 |
| 9 | oldgirl | 20 | f | 9 | 1003 | 80 | 1003 | mysql | 103 |
| 10 | oldp | 25 | m | 10 | 1003 | 96 | 1003 | mysql | 103 |
+-----+---------+------+------+-----+------+-------+------+--------+-----+
根据题目需求进行相应处理操作
select course.cname as 课程名,
group_concat(case when sc.score>85 then student.sname end) as 优秀,
group_concat(case when sc.score>=70 and sc.score<85 then student.sname end) as 良好,
group_concat(case when sc.score>=60 and sc.score<70 then student.sname end) as 一般,
group_concat(case when sc.score<60 then student.sname end) as 不及格
from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by course.cno;
+-----------+-------------------+---------------------+--------+-----------+
| 课程名 | 优秀 | 良好 | 一般 | 不及格 |
+-----------+-------------------+---------------------+--------+-----------+
| linux | li4,zhao4 | zhang3,wang5,oldboy | ma6 | NULL |
| python | zhang4 | NULL | wang5 | zhang3 |
| mysql | oldp,wang5,zhang4 | oldgirl,ma6,zhao4 | NULL | zh4,li4 |
+-----------+-------------------+---------------------+--------+-----------+
09 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
根据题目需求所需调用表信息
# 通过student表和sc表建立关联，再通过sc表和crouse表建立关联，获取所有平均成绩大于85的学生姓名 学号 和平均成绩
student sc course
根据题目需求建立表与表关联
select * from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno;
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| sno | sname | sage | ssex | sno | cno | score | cno | cname | tno |
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| 1 | zhang3 | 18 | m | 1 | 1001 | 80 | 1001 | linux | 101 |
| 1 | zhang3 | 18 | m | 1 | 1002 | 59 | 1002 | python | 102 |
| 2 | zhang4 | 18 | m | 2 | 1002 | 90 | 1002 | python | 102 |
| 2 | zhang4 | 18 | m | 2 | 1003 | 100 | 1003 | mysql | 103 |
| 3 | li4 | 18 | m | 3 | 1001 | 99 | 1001 | linux | 101 |
| 3 | li4 | 18 | m | 3 | 1003 | 40 | 1003 | mysql | 103 |
| 4 | wang5 | 19 | f | 4 | 1001 | 79 | 1001 | linux | 101 |
| 4 | wang5 | 19 | f | 4 | 1002 | 61 | 1002 | python | 102 |
| 4 | wang5 | 19 | f | 4 | 1003 | 99 | 1003 | mysql | 103 |
| 5 | zh4 | 18 | m | 5 | 1003 | 40 | 1003 | mysql | 103 |
| 6 | zhao4 | 18 | m | 6 | 1001 | 89 | 1001 | linux | 101 |
| 6 | zhao4 | 18 | m | 6 | 1003 | 77 | 1003 | mysql | 103 |
| 7 | ma6 | 19 | f | 7 | 1001 | 67 | 1001 | linux | 101 |
| 7 | ma6 | 19 | f | 7 | 1003 | 82 | 1003 | mysql | 103 |
| 8 | oldboy | 20 | m | 8 | 1001 | 70 | 1001 | linux | 101 |
| 9 | oldgirl | 20 | f | 9 | 1003 | 80 | 1003 | mysql | 103 |
| 10 | oldp | 25 | m | 10 | 1003 | 96 | 1003 | mysql | 103 |
+-----+---------+------+------+-----+------+-------+------+--------+-----+
根据题目需求进行相应处理操作
select student.sno,student.sname,avg(sc.core) from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by student.sno
having avg(sc.core)>85
+-----+--------+---------------+
| sno | sname | avg(sc.score) |
+-----+--------+---------------+
| 2 | zhang4 | 95.0000 |
| 10 | oldp | 96.0000 |
+-----+--------+---------------+
