多表联查过程

  1. 首先找到需要的关联表
  2. 找到关联列(不同表中有关系的列)
  3. join on关联起来
  4. where进行定位
  5. group_by进行站队
  6. order by进行排序

test table1
多表联查


举例

环境准备:school库备份

school库介绍

course 课程

course

sc 成绩

sc

student 学生表

student

teacher 教师表

teacher


传统连接

#例1:查询world库中人口数小于100的城市

1
SELECT name,CountryCode FROM world.city WHERE Population<100;

#人口数小于100人的城市,所在国家的国土面积(城市名,国家名,国土面积)
多表连接查询
传统连接:基于WHERE条件
1.找表之间的关系列
2.排列查询条件

1
2
3
4
5
6
7
8
DESC world.country;
SELECT name,CountryCode FROM world.city WHERE Population<100;
SELECT name,SurfaceArea FROM world.country WHERE Code='PCN'
合并
SELECT world.city.name,world.country.name,world.country.SurfaceArea,world.city.Population
FROM world.city,world.country #相关表
WHERE world.city.CountryCode = world.country.Code #关联条件
AND world.city.Population<100; #判断条件

自连接(了解)

内连接 *****

1
2
A   B
A.x B.y
关键字:join on
作用:
  • 以相同的关联列将两个表进行关联,数据取交集,并形成新表。
语句:
1
2
3
4
select * 
from a_table as a
join b_table as b
on a.a_id = b.b_id;
说明:
  • 组合两个表中的记录,返回关联字段相符的记录,也就是返回两个表的交集(阴影)部分。

内连接


过程
  1. 找表之间的关系列
  2. 将两表放在join左右
  3. 将关联条件了放在on后面
  4. 将所有的查询条件进行罗列
1
2
3
4
5
6
7
8
9
语句格式:
select A.m,B.n
from
A join B //两张表
on A.x=B.y //表中的关系列
where
group by
order by
limit

内连接改写例1:

#查询人口数量小于100人的国家名,城市名,国土面积

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
关联的表:world.city world.country
city;
DESC world.country;
关联列:world.city.CountryCode world.country.Code

SELECT country.name,city.name,country.SurfaceArea,city.Population
FROM
world.city JOIN world.country
ON city.CountryCode=country.Code
WHERE world.city.Population<100;
结果:
+----------+-----------+-------------+------------+
| name | name | SurfaceArea | Population |
+----------+-----------+-------------+------------+
| Pitcairn | Adamstown | 49.00 | 42 |
+----------+-----------+-------------+------------+
例2:

#1.查询张oldguo老师所教的课程名称。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
关联表:course(课程表) teacher(教师表)
DESC school.course;
DESC school.teacher;

关联列:school.course.tno school.teacher.tno
关联后临时表:
+------+--------+-----+-----+--------+
| cno | cname | tno | tno | tname |
+------+--------+-----+-----+--------+
| 1001 | linux | 101 | 101 | oldboy |
| 1002 | python | 102 | 102 | hesw |
| 1003 | mysql | 103 | 103 | oldguo |
+------+--------+-----+-----+--------+

SELECT teacher.tname,course.cname
FROM
school.course JOIN school.teacher
ON course.tno=teacher.tno
WHERE teacher.tname='oldguo';
结果:
+------+-------+-----+-----+--------+
| cno | cname | tno | tno | tname |
+------+-------+-----+-----+--------+
| 1003 | mysql | 103 | 103 | oldguo |
+------+-------+-----+-----+--------+

#2.统计一下每门课程的总成绩

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SELECT * FROM school.course;
SELECT * FROM school.te;

关联表:school.course school.sc
关联列:school.course.cno school.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 |
+------+--------+-----+-----+------+-------+


SELECT course.cname,SUM(sc.score)
FROM school.course
JOIN school.sc
ON course.cno=sc.cno
GROUP BY course.cname,course.cno; //以两个条件构成分组条件,防止单科多老师出现。
结果:
+--------+---------------+
| cname | SUM(sc.score) |
+--------+---------------+
| linux | 484 |
| mysql | 614 |
| python | 210 |
+--------+---------------+

#3.统计一下每门课程的总成绩和课程代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT course.cno,course.cname,SUM(sc.score) 
FROM school.course
JOIN school.sc
ON course.cno=sc.cno
GROUP BY course.cname;
结果:
ERROR:sql_mode=only_full_group_by
#5.7版本会报错的情况,在sqlyog中一下操作没问题,但命令行会报错。
原因:
1.在select后面的列,不是分组的条件,并且没有在函数(sum中)中包裹。
2.如果GROUP BY 后是主键列或唯一列。如下

SELECT course.cno,course.cname,SUM(school.sc.score)
FROM school.course
JOIN school.sc
ON course.cno=sc.cno
GROUP BY school.course.cno; #此处为主键或唯一列可避免该错误。
结果:
+------+--------+----------------------+
| cno | cname | SUM(school.sc.score) |
+------+--------+----------------------+
| 1001 | linux | 484 |
| 1002 | python | 210 |
| 1003 | mysql | 614 |
+------+--------+----------------------+

#4. 查询oldguo老师教的学生姓名列表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
关联后的临时表:
+-----+--------+------+--------+-----+-----+------+-------+-----+---------+------+------+
| tno | tname | cno | cname | tno | sno | cno | score | sno | sname | sage | ssex |
+-----+--------+------+--------+-----+-----+------+-------+-----+---------+------+------+
| 101 | oldboy | 1001 | linux | 101 | 1 | 1001 | 80 | 1 | zhang3 | 18 | m |
| 102 | hesw | 1002 | python | 102 | 1 | 1002 | 59 | 1 | zhang3 | 18 | m |
| 102 | hesw | 1002 | python | 102 | 2 | 1002 | 90 | 2 | zhang4 | 18 | m |
| 103 | oldguo | 1003 | mysql | 103 | 2 | 1003 | 100 | 2 | zhang4 | 18 | m |
| 101 | oldboy | 1001 | linux | 101 | 3 | 1001 | 99 | 3 | li4 | 18 | m |
| 103 | oldguo | 1003 | mysql | 103 | 3 | 1003 | 40 | 3 | li4 | 18 | m |
| 101 | oldboy | 1001 | linux | 101 | 4 | 1001 | 79 | 4 | wang5 | 19 | f |
| 102 | hesw | 1002 | python | 102 | 4 | 1002 | 61 | 4 | wang5 | 19 | f |
| 103 | oldguo | 1003 | mysql | 103 | 4 | 1003 | 99 | 4 | wang5 | 19 | f |
| 103 | oldguo | 1003 | mysql | 103 | 5 | 1003 | 40 | 5 | zh4 | 18 | m |
| 101 | oldboy | 1001 | linux | 101 | 6 | 1001 | 89 | 6 | zhao4 | 18 | m |
| 103 | oldguo | 1003 | mysql | 103 | 6 | 1003 | 77 | 6 | zhao4 | 18 | m |
| 101 | oldboy | 1001 | linux | 101 | 7 | 1001 | 67 | 7 | ma6 | 19 | f |
| 103 | oldguo | 1003 | mysql | 103 | 7 | 1003 | 82 | 7 | ma6 | 19 | f |
| 101 | oldboy | 1001 | linux | 101 | 8 | 1001 | 70 | 8 | oldboy | 20 | m |
| 103 | oldguo | 1003 | mysql | 103 | 9 | 1003 | 80 | 9 | oldgirl | 20 | f |
| 103 | oldguo | 1003 | mysql | 103 | 10 | 1003 | 96 | 10 | oldp | 25 | m |
+-----+--------+------+--------+-----+-----+------+-------+-----+---------+------+------+

SELECT teacher.tname,group_CONCAT(school.student.sname)
FROM school.teacher
JOIN school.course
ON teacher.tno = course.tno
JOIN school.sc
ON course.cno = sc.cno
JOIN school.student
ON sc.sno = student.sno
WHERE teacher.tname='oldguo'; //查询一个老师用where
结果:
+--------+---------------------------------------------+
| tname | group_CONCAT(school.student.sname) |
+--------+---------------------------------------------+
| oldguo | zhang4,li4,wang5,zh4,zhao4,ma6,oldgirl,oldp |
+--------+---------------------------------------------+

#5. 查询所有老师教的学生姓名列表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
关联后的临时表:
见4

SELECT teacher.tname,group_CONCAT(school.student.sname)
FROM school.teacher
JOIN school.course
ON teacher.tno = course.tno
JOIN school.sc
ON course.cno = sc.cno
JOIN school.student
ON sc.sno = student.sno
GROUP BY teacher.tno //查询所有老师需要用group by进行站队
结果:
+--------+---------------------------------------------+
| tname | group_CONCAT(school.student.sname) |
+--------+---------------------------------------------+
| oldboy | zhang3,li4,wang5,zhao4,ma6,oldboy |
| hesw | zhang3,wang5,zhang4 |
| oldguo | zhang4,li4,zh4,zhao4,ma6,oldgirl,wang5,oldp |
+--------+---------------------------------------------+

#6. 查询oldboy老师教的成绩大于70的学生姓名,和成绩

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
关联后的临时表:
见4

SELECT student.sname,sc.score
FROM school.teacher
JOIN school.course
ON teacher.tno = course.tno
JOIN school.sc
ON course.cno = sc.cno
JOIN school.student
ON sc.sno = student.sno
WHERE teacher.tname='oldboy' AND sc.score>70;
结果:
+--------+-------+
| sname | score |
+--------+-------+
| zhang3 | 80 |
| li4 | 99 |
| wang5 | 79 |
| zhao4 | 89 |
+--------+-------+

#7. 统计zhang3,学习了几门课

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
关联后的临时表:
+-----+---------+------+------+-----+------+-------+------+--------+-----+
| 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.sname,GROUP_CONCAT(school.course.cname)
FROM school.student
JOIN school.sc
ON student.sno = sc.sno
JOIN school.course
ON sc.cno =course.cno
WHERE student.sname='zhang3'; //由于是查单个学生,所以不需要GROUP BY进行分组
#GROUP BY student.sno;
结果:
+--------+-----------------------------------+
| sname | GROUP_CONCAT(school.course.cname) |
+--------+-----------------------------------+
| zhang3 | linux,python |
+--------+-----------------------------------+

SELECT GROUP_CONCAT(school.course.cname)
FROM school.student
JOIN school.sc
ON student.sno = sc.sno
JOIN school.course
ON sc.cno = course.cno
WHERE school.student.sname='zhang3'
GROUP BY school.student.sno;
结果:
+-----------------------------------+
| GROUP_CONCAT(school.course.cname) |
+-----------------------------------+
| python,linux |
+-----------------------------------+

#8. 查询oldguo老师教的学生名.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
关联后的临时表:
见7

SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM school.teacher
JOIN school.course
ON teacher.tno = course.tno
JOIN school.sc
ON course.cno = sc.cno
JOIN school.student
ON sc.sno = student.sno
WHERE teacher.tname='oldguo' ////由于是查单个老师,所以不需要GROUP BY进行分组
#GROUP BY teacher.tname;
结果:
+--------+---------------------------------------------+
| tname | GROUP_CONCAT(student.sname) |
+--------+---------------------------------------------+
| oldguo | zhang4,li4,wang5,zh4,zhao4,ma6,oldgirl,oldp |
+--------+---------------------------------------------+

#9. 查询oldguo所教课程的平均分数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
关联后的临时表:
+-----+--------+------+--------+-----+-----+------+-------+
| tno | tname | cno | cname | tno | sno | cno | score |
+-----+--------+------+--------+-----+-----+------+-------+
| 101 | oldboy | 1001 | linux | 101 | 1 | 1001 | 80 |
| 102 | hesw | 1002 | python | 102 | 1 | 1002 | 59 |
| 102 | hesw | 1002 | python | 102 | 2 | 1002 | 90 |
| 103 | oldguo | 1003 | mysql | 103 | 2 | 1003 | 100 |
| 101 | oldboy | 1001 | linux | 101 | 3 | 1001 | 99 |
| 103 | oldguo | 1003 | mysql | 103 | 3 | 1003 | 40 |
| 101 | oldboy | 1001 | linux | 101 | 4 | 1001 | 79 |
| 102 | hesw | 1002 | python | 102 | 4 | 1002 | 61 |
| 103 | oldguo | 1003 | mysql | 103 | 4 | 1003 | 99 |
| 103 | oldguo | 1003 | mysql | 103 | 5 | 1003 | 40 |
| 101 | oldboy | 1001 | linux | 101 | 6 | 1001 | 89 |
| 103 | oldguo | 1003 | mysql | 103 | 6 | 1003 | 77 |
| 101 | oldboy | 1001 | linux | 101 | 7 | 1001 | 67 |
| 103 | oldguo | 1003 | mysql | 103 | 7 | 1003 | 82 |
| 101 | oldboy | 1001 | linux | 101 | 8 | 1001 | 70 |
| 103 | oldguo | 1003 | mysql | 103 | 9 | 1003 | 80 |
| 103 | oldguo | 1003 | mysql | 103 | 10 | 1003 | 96 |
+-----+--------+------+--------+-----+-----+------+-------+

SELECT teacher.tname,AVG(sc.score)
FROM school.teacher
JOIN school.course
ON teacher.tno = course.tno
JOIN school.sc
ON course.cno = sc.cno
WHERE school.teacher.tname='oldguo' //查询单个老师所教课程的平均成绩,不需要加GROUP BY
#GROUP BY school.teacher.tno;
结果:
+--------+---------------+
| tname | AVG(sc.score) |
+--------+---------------+
| oldguo | 76.7500 |
+--------+---------------+

#10. 每位老师所教课程的平均分,并按平均分排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
关联后的临时表:
见9

SELECT teacher.tname,AVG(sc.score)
FROM school.teacher
JOIN school.course
ON teacher.tno = course.tno
JOIN school.sc
ON course.cno = sc.cno
GROUP BY teacher.tno
ORDER BY AVG(sc.score) DESC; //ORDER BY ... desc降序排序
结果:
+--------+---------------+
| tname | AVG(sc.score) |
+--------+---------------+
| oldboy | 80.6667 |
| oldguo | 76.7500 |
| hesw | 70.0000 |
+--------+---------------+

#11. 查询oldguo所教的不及格的学生姓名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
关联后的临时表:
+-----+--------+------+--------+-----+-----+------+-------+-----+---------+------+------+
| tno | tname | cno | cname | tno | sno | cno | score | sno | sname | sage | ssex |
+-----+--------+------+--------+-----+-----+------+-------+-----+---------+------+------+
| 101 | oldboy | 1001 | linux | 101 | 1 | 1001 | 80 | 1 | zhang3 | 18 | m |
| 102 | hesw | 1002 | python | 102 | 1 | 1002 | 59 | 1 | zhang3 | 18 | m |
| 102 | hesw | 1002 | python | 102 | 2 | 1002 | 90 | 2 | zhang4 | 18 | m |
| 103 | oldguo | 1003 | mysql | 103 | 2 | 1003 | 100 | 2 | zhang4 | 18 | m |
| 101 | oldboy | 1001 | linux | 101 | 3 | 1001 | 99 | 3 | li4 | 18 | m |
| 103 | oldguo | 1003 | mysql | 103 | 3 | 1003 | 40 | 3 | li4 | 18 | m |
| 101 | oldboy | 1001 | linux | 101 | 4 | 1001 | 79 | 4 | wang5 | 19 | f |
| 102 | hesw | 1002 | python | 102 | 4 | 1002 | 61 | 4 | wang5 | 19 | f |
| 103 | oldguo | 1003 | mysql | 103 | 4 | 1003 | 99 | 4 | wang5 | 19 | f |
| 103 | oldguo | 1003 | mysql | 103 | 5 | 1003 | 40 | 5 | zh4 | 18 | m |
| 101 | oldboy | 1001 | linux | 101 | 6 | 1001 | 89 | 6 | zhao4 | 18 | m |
| 103 | oldguo | 1003 | mysql | 103 | 6 | 1003 | 77 | 6 | zhao4 | 18 | m |
| 101 | oldboy | 1001 | linux | 101 | 7 | 1001 | 67 | 7 | ma6 | 19 | f |
| 103 | oldguo | 1003 | mysql | 103 | 7 | 1003 | 82 | 7 | ma6 | 19 | f |
| 101 | oldboy | 1001 | linux | 101 | 8 | 1001 | 70 | 8 | oldboy | 20 | m |
| 103 | oldguo | 1003 | mysql | 103 | 9 | 1003 | 80 | 9 | oldgirl | 20 | f |
| 103 | oldguo | 1003 | mysql | 103 | 10 | 1003 | 96 | 10 | oldp | 25 | m |
+-----+--------+------+--------+-----+-----+------+-------+-----+---------+------+------+

SELECT student.sname,sc.score
FROM school.teacher
JOIN school.course
ON teacher.tno = course.tno
JOIN school.sc
ON course.cno = sc.cno
JOIN school.student
ON sc.sno = student.sno
WHERE teacher.tname='oldguo' AND sc.score<60;
结果:
+-------+-------+
| sname | score |
+-------+-------+
| li4 | 40 |
| zh4 | 40 |
+-------+-------+

#12. 查询所有老师所教学生不及格的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
关联后的临时表:
见11

SELECT teacher.tname,student.sname,sc.score
FROM school.teacher
JOIN school.course
ON teacher.tno = course.tno
JOIN school.sc
ON course.cno = sc.cno
JOIN school.student
ON sc.sno = student.sno
WHERE school.sc.score<60;
结果:
+--------+--------+-------+
| tname | sname | score |
+--------+--------+-------+
| hesw | zhang3 | 59 |
| oldguo | li4 | 40 |
| oldguo | zh4 | 40 |
+--------+--------+-------+

左连接(左外连接)

关键字:left join on / left outer join on
方式:

取左表所有的值,和右表里面关联的值(右表不存在的值以null填充)。形成新表。
内连接:只取满足where条件的值。
内连接

左外连接(and换成where条件):用于指定city为驱动表(结果集较少的表),用于减少循环判断的次数。

左外连接

说明:

left join 是left outer join的简写,它的全称是左外连接,是外连接中的一种。
左(外)连接,左表(a_table)的记录将会全部表示出来,而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
左外连接

右连接(右外连接)

关键字:right join on / right outer join on
语句:
1
2
3
4
select * 
from a_table a
right outer join b_table b
on a.a_id = b.b_id;
说明:

right join是right outer join的简写,它的全称是右外连接,是外连接中的一种。
与左(外)连接相反,右(外)连接,左表(a_table)只会显示符合搜索条件的记录,而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
右外连接