SQL 多表联合查询，收藏直接起飞！

为什么需要多张数据库表进行查询呢？因为如果设计成一张表会造成数据冗余，造成数据库空间浪费，然而有时我们需要将多张表的数据整合并且查询出来，这时就需要通过表之间的主外键关联在一起进行查询。

查看获取表结构信息，根据表结构信进行连表查询

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