多表查询

从多张表中进行数据查询
学生表

ID	Name	AGE	Teacher_Id
1	张三	13	1
2	李四	14	1
3	王五	15	2

老师表

ID	Name
1	张老师
2	李老师
3	王老师

在做多表查询的时候：

找两个表之间的关系
使用单表方式。

Mysql 多表查询 - 图1

查询学生姓名，年龄，老师姓名；

使用 where条件

select w_stu.name,age,w_teacher.name from w_stu,w_teacher
where teacher_id = w_teacher.id;

w_stu.name 表示是学生表中姓名
w_teacher.name 表示老师表中的姓名

我们在做多表查询的时候，注意要找到两个表之间的关系。
如果两个表中的字段有重名的，字段前加上表名以便于区分。

inner join

在使用多表查询的时候，一般使用 inner join 将两个表关联在一起进行查询。
固定语法

select  列名 from 表1
inner join 表2
on 表1 和 表2 的关系;

查询学生姓名，年龄，老师姓名；

使用 inner join 的写法：

select w_stu.name, age, w_teacher.name from w_stu
inner join w_teacher
on w_stu.teacher_id = w_teacher.id;

查找出张老师的学生；

select w_stu.name, age, w_teacher.name from w_stu
inner join w_teacher
on w_stu.teacher_id = w_teacher.id
where w_teacher.name="张老师";

其他两种方式

select w_stu.name,age,w_teacher.name from w_stu,w_teacher
where teacher_id = w_teacher.id 
AND  w_teacher.name="张老师";

使用子查询

select * from w_stu
WHERE teacher_id = (
        SELECT id from w_teacher
        WHERE name = "张老师"
)

查询age>15 显示学生姓名，老师姓名，学生年龄； ```sql select w_stu.name,w_teacher.name,age from W_stu inner join w_teacher on w_stu.teacher_id = w_teacher.id where age>15;

现在三张表

学生表<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/87080/1646806801196-4a0b04ee-72a7-4f60-b90a-024e6d8a435f.png#clientId=ua0cb6c18-d56e-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=140&id=u7e0cedd0&margin=%5Bobject%20Object%5D&name=image.png&originHeight=279&originWidth=598&originalType=binary&ratio=1&rotation=0&showTitle=false&size=22030&status=done&style=none&taskId=uc15f14f6-ffc9-493a-af3c-b15a6071228&title=&width=299)<br />课程表<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/87080/1646806831959-fff32066-1b60-4148-9fd2-a268a3ab0347.png#clientId=ua0cb6c18-d56e-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=152&id=u598d7f37&margin=%5Bobject%20Object%5D&name=image.png&originHeight=303&originWidth=428&originalType=binary&ratio=1&rotation=0&showTitle=false&size=15949&status=done&style=none&taskId=u09557827-b9ca-4766-a104-68d702de9e0&title=&width=214)<br />成绩表<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/87080/1646806847101-12984991-3d16-4311-b1d7-03e3e1c854b1.png#clientId=ua0cb6c18-d56e-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=201&id=ue6851de1&margin=%5Bobject%20Object%5D&name=image.png&originHeight=402&originWidth=623&originalType=binary&ratio=1&rotation=0&showTitle=false&size=32838&status=done&style=none&taskId=u14b6eb4a-9c77-4a06-bd0e-cc5b72c3c38&title=&width=311.5)

三张表之间的关系<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/87080/1646809224473-56c8e5bc-3bfc-4a2b-882a-3527b2ed007b.png#clientId=ua0cb6c18-d56e-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=323&id=u16daf0fc&margin=%5Bobject%20Object%5D&name=image.png&originHeight=645&originWidth=741&originalType=binary&ratio=1&rotation=0&showTitle=false&size=71458&status=done&style=none&taskId=u5ccbf637-2a77-43a2-b42e-80fcc078f4f&title=&width=370.5)

1. 查询学生的各科成绩
```sql
select w_stu.name, w_course.course, w_score.score FROM w_score
INNER JOIN w_stu
ON w_score.stu_id = w_stu.ID
INNER JOIN w_course
ON w_course.id = w_score.course_id;

查询每个学生的语文成绩

select w_stu.name, w_course.course, w_score.score FROM w_score
INNER JOIN w_stu
ON w_score.stu_id = w_stu.ID
INNER JOIN w_course
ON w_course.id = w_score.course_id
WHERE course="语文";

查询语文分数大于60的所有stu

select w_stu.name, w_course.course, w_score.score FROM w_score
INNER JOIN w_stu
ON w_score.stu_id = w_stu.ID
INNER JOIN w_course
ON w_course.id = w_score.course_id
WHERE course="语文" AND w_score.score > 60;

统计每个stu的总分

select w_stu.name, sum(w_score.score) FROM w_score
INNER JOIN w_stu
ON w_score.stu_id = w_stu.ID
INNER JOIN w_course
ON w_course.id = w_score.course_id
GROUP BY w_stu.ID, w_stu.`Name`;   -- 按照id，name进行分组；

总结

在做多表查询的时候，最重要的是：找到这些表之间的关系。将这些关系连接起来即可。

查询 stu姓名，年龄，老师姓名，科目，分数；

SELECT w_stu.`Name`, w_stu.Age, w_teacher.`Name`,w_course.course,w_score.score FROM w_score
INNER JOIN w_course ON w_score.course_id = w_course.id
INNER JOIN w_teacher ON w_teacher.course_id = w_course.id AND w_score.course_id = w_teacher.course_id
INNER JOIN w_stu ON w_stu.ID = w_score.stu_id;

left join 左联

inner join 表示两个表中共有的部分；
Mysql 多表查询 - 图8

SELECT * from w_stu as a
INNER JOIN w_teacher as b
ON a.Teacher_Id = b.ID;

左联查询以左表为主，会显示出左表的所有内容，

Mysql 多表查询 - 图12

以 w_stu 表为主, 显示所有的信息

select * from w_stu as a
left join w_teacher as b
on a.teacher_id = b.id;

左表为主

right join

右联是以右边表为主
Mysql 多表查询 - 图14
查询所有老师对应的stu
老师表为主

select * from w_stu as a
right join w_teacher as b
on a.teacher_id = b.id;

总结

左联与右联的区别：
left join 以左表为主（left join 左边的表为主），另外表中没有与之对应的数据，查询结果则显示为 null。
right join 以右表为主（right join 右边的表为主），另外表中没有与之对应的数据，查询结果则显示为 null。
在公司中，大部分的开发和测试人员习惯使用左表。

全外连接

Mysql 中不支持全外连接
如果想把所有表中的数据都显示出来，可以借助union 拼接；

union 拼接

将两条sql 的结果拼接在一起。


SELECT * from w_stu as a
LEFT JOIN w_teacher as b
ON a.Teacher_Id = b.ID


UNION


select * from w_stu as a
right join w_teacher as b
on a.teacher_id = b.id;

将左连接的语句和右链接的语句拼接在一起，就可以了。

union all

SELECT * from w_stu as a
LEFT JOIN w_teacher as b
ON a.Teacher_Id = b.ID


UNION ALL


select * from w_stu as a
right join w_teacher as b
on a.teacher_id = b.id;

union all 不会进行去重。会把所有的结果拼合在一起。

统计每个stu的总分，平均成绩，最高分，最低分；

select stu_id,sum(score),avg(score),max(score),min(score) from w_score
group by stu_id;

查询语文分数大于数学分数的stu； ```sql SELECT FROM — 查询出语文成绩 (select from w_score where course_id =1) as tmp1 — 作为临时表 INNER JOIN — 查询出数学成绩 (select * from w_score where course_id =2) as tmp2 — 作为临时表 ON tmp1.stu_id = tmp2.stu_id — 两个表关联 WHERE tmp1.score > tmp2.score; — 找出最大




<a name="UotD9"></a>
# 面试问题

1. 常用多表查询语法有哪些？

使用 inner join， left join， right join, union, union all 进行多表。<br />在查询的时候 需要将表之间的关系找出来。

2. left join 与 right join 的区别?

left join 以 左表为主 （left join 左边的表为主）， 另外表中没有与之对应的数据，查询结果则显示为 null。<br />right join 以右表为主 （right join 右边的表为主）， 另外表中没有与之对应的数据，查询结果则显示为 null。<br />在公司中，大部分的开发和测试人员 习惯使用左表。

3. union 和 union all 的区别。

union 会将两个结果中的数据进行去重。<br />union all 将两个结果拼接在一起，不会进行去重。


<a name="AX49q"></a>
# 作业

1. 查询stu姓名，年龄，老师名字，科目，成绩；
1. 查询 stu姓名，总成绩；
1. 查询 各科成绩都大于60分的stu姓名，科目，成绩；

分析： 最低分 大于60 即可。 min()<br />成绩表中 按照 stu_id 进行分组 统计每个stu的最低分
```sql
select stu_id, min(score) from w_score
group by stu_id;

添加条件 min(score) > 60


select stu_id from w_score
group by stu_id HAVING MIN(score) > 60;

已经找到对应stu的id，根据id 找对应的人员；

SELECT a.`Name`, b.score, c.course from w_stu  as a
INNER JOIN w_score as b
ON a.ID = b.stu_id
INNER JOIN w_course as c
ON c.id = b.course_id

WHERE a.id in (
    select stu_id from w_score
    group by stu_id HAVING MIN(score) > 60
)

ORDER BY a.`Name`, b.score DESC;

统计每个老师对应的stu数，显示老师姓名，stu数；
查询stu姓名，科目，成绩，成绩按照降序排序；