開發人員不得不會的MySQL多表聯合查詢方法!

花辭樹sy發表於2022-12-20

為什麼需要多張資料庫表進行查詢呢?因為 如果設計成一張表會造成資料冗餘,造成資料庫空間浪費,然而有時我們需要將多張表的資料整合並且查詢出來,這時就需要透過表之間的主外來鍵關聯在一起進行查詢。

檢視獲取表結構資訊,根據表結構信進行連表查詢


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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章