開發人員不得不會的MySQL多表聯合查詢方法！
為什麼需要多張資料庫表進行查詢呢？因為 如果設計成一張表會造成資料冗餘，造成資料庫空間浪費，然而有時我們需要將多張表的資料整合並且查詢出來，這時就需要透過表之間的主外來鍵關聯在一起進行查詢。
檢視獲取表結構資訊，根據表結構信進行連表查詢
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)
1）查詢平均成績大於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)
2）查詢所有同學的學號、姓名、選課數和總成績
根據題目需求所需呼叫表資訊
# 透過 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)
3）查詢各科成績最高和最低的分，以如下形式顯示：課程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)
4）統計各位老師，所教課程的及格率
根據題目需求所需呼叫表資訊
# 透過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)
5）查詢每門課程被選修的學生數
根據題目需求所需呼叫表資訊
# 透過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)
6）查詢出只選修了一門課程的全部學生的學號和姓名
根據題目需求所需呼叫表資訊
# 透過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)
7）查詢選修課程門數超過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)
8）統計每門課程：優秀（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 |
+-----------+-------------------+---------------------+--------+-----------+
9）查詢平均成績大於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 |
+-----+--------+---------------+
需要領取【配套PDF版資料+影片教程合集】
請後臺私信我~
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70023624/viewspace-2928744/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 開發人員不得不知的MySQL索引和查詢優化MySql索引優化
- mysql中的多表關聯查詢MySql
- JPA 之 多表聯合查詢
- mybatis多表聯合查詢的寫法MyBatis
- jpa動態查詢與多表聯合查詢
- MYSQL學習筆記24: 多表查詢(聯合查詢,Union, Union All)MySql筆記
- 【MySQL】多表查詢MySql
- mysql多表查詢MySql
- MySQL 多表查詢MySql
- 多表聯合查詢 - 基於註解SQLSQL
- MySQL讓人又愛又恨的多表查詢MySql
- 讓人又愛又恨的Mysql多表查詢MySql
- JPA多表關聯查詢
- 史上最簡單MySQL教程詳解（基礎篇）之多表聯合查詢MySql
- mySQL多表查詢與事務MySql
- mysql多表查詢如何實現MySql
- 一個MySQL多表查詢的問題MySql
- 深入sql多表差異化聯合查詢的問題詳解SQL
- springDataJpa多表級聯查詢（@ManyToOne @OneToOne）Spring
- MyBatisPlus怎麼多表關聯查詢？MyBatis
- 如何做多表關聯查詢
- MYSQL學習筆記26: 多表查詢|子查詢MySql筆記
- MySQL資料庫基礎——多表查詢：子查詢MySql資料庫
- Java-MySql-函式、多表查詢JavaMySql函式
- MYSQL 怎麼 LEFT JOIN 多表聯查MySql
- 多表查詢
- mysql三表聯合查詢(員工表,部門表,工資表)MySql
- SQL查詢的：子查詢和多表查詢SQL
- onethinkphp 如何做多表關聯查詢PHP
- MySQL筆記-左連線的使用（left join有關聯的多表查詢）MySql筆記
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- 04多表查詢
- Spring Data JPA 實現多表關聯查詢Spring
- mysql多表多欄位查詢並去重MySql
- SQL優化之多表關聯查詢-案例一SQL優化
- Oracle-多表查詢Oracle
- ORM多表查詢下ORM
- mysql新建表和多表查詢，關鍵字joinMySql