多表查询

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

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

老师表

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

在做多表查询的时候：

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

Mysql 多表查询 - 图1

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

使用 where条件

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

image.png

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

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

inner join

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

  1. select  列名 from 表1
  2. inner join 表2
  3. on 表1 和 表2 的关系;
  1. 查询学生姓名，年龄，老师姓名；

使用 inner join 的写法：

  1. select w_stu.name, age, w_teacher.name from w_stu
  2. inner join w_teacher
  3. on w_stu.teacher_id = w_teacher.id;
  1. 查找出 张老师的学生；
    1. select w_stu.name, age, w_teacher.name from w_stu
    2. inner join w_teacher
    3. on w_stu.teacher_id = w_teacher.id
    4. where w_teacher.name="张老师";
    image.png

其他两种方式

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

使用子查询

  1. select * from w_stu
  2. WHERE teacher_id = (
  3.         SELECT id from w_teacher
  4.         WHERE name = "张老师"
  5. )
  1. 查询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;
  1. 现在三张表
  3. 学生表<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)
  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)
  7. 1. 查询学生的各科成绩
  8. ```sql
  9. select w_stu.name, w_course.course, w_score.score FROM w_score
  10. INNER JOIN w_stu
  11. ON w_score.stu_id = w_stu.ID
  12. INNER JOIN w_course
  13. ON w_course.id = w_score.course_id;

image.png

  1. 查询每个学生的语文成绩

    1. select w_stu.name, w_course.course, w_score.score FROM w_score
    2. INNER JOIN w_stu
    3. ON w_score.stu_id = w_stu.ID
    4. INNER JOIN w_course
    5. ON w_course.id = w_score.course_id
    6. WHERE course="语文";

    image.png

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

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

    image.png

  3. 统计每个stu的总分

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

    总结

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

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

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

    image.png

left join 左联

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

image.png

image.png

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

image.png

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

Mysql 多表查询 - 图12

  1. 以 w_stu 表为主, 显示所有的信息
    1. select * from w_stu as a
    2. left join w_teacher as b
    3. on a.teacher_id = b.id;
    image.png

左表为主

right join

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

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

image.png

总结

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

全外连接

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

union 拼接

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

  2. SELECT * from w_stu as a
  3. LEFT JOIN w_teacher as b
  4. ON a.Teacher_Id = b.ID
  7. UNION
  10. select * from w_stu as a
  11. right join w_teacher as b
  12. on a.teacher_id = b.id;

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

union all

  1. SELECT * from w_stu as a
  2. LEFT JOIN w_teacher as b
  3. ON a.Teacher_Id = b.ID
  6. UNION ALL
  9. select * from w_stu as a
  10. right join w_teacher as b
  11. on a.teacher_id = b.id;

image.png

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

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

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

  2. 查询语文分数大于数学分数的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; — 找出最大

  4. <a name="UotD9"></a>
  5. # 面试问题
  7. 1. 常用多表查询语法有哪些？
  9. 使用 inner join， left join， right join, union, union all 进行多表。<br />在查询的时候 需要将表之间的关系找出来。
  11. 2. left join 与 right join 的区别?
  13. left join 以 左表为主 （left join 左边的表为主）， 另外表中没有与之对应的数据，查询结果则显示为 null。<br />right join 以右表为主 （right join 右边的表为主）， 另外表中没有与之对应的数据，查询结果则显示为 null。<br />在公司中，大部分的开发和测试人员 习惯使用左表。
  15. 3. union 和 union all 的区别。
  17. union 会将两个结果中的数据进行去重。<br />union all 将两个结果拼接在一起，不会进行去重。
  20. <a name="AX49q"></a>
  21. # 作业
  23. 1. 查询stu姓名，年龄，老师名字，科目，成绩；
  24. 1. 查询 stu姓名，总成绩；
  25. 1. 查询 各科成绩都大于60分的stu姓名，科目，成绩；
  27. 分析： 最低分 大于60 即可。 min()<br />成绩表中 按照 stu_id 进行分组 统计每个stu的最低分
  28. ```sql
  29. select stu_id, min(score) from w_score
  30. group by stu_id;

image.png
添加条件 min(score) > 60

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

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

  1. SELECT a.`Name`, b.score, c.course from w_stu  as a
  2. INNER JOIN w_score as b
  3. ON a.ID = b.stu_id
  4. INNER JOIN w_course as c
  5. ON c.id = b.course_id
  7. WHERE a.id in (
  8.     select stu_id from w_score
  9.     group by stu_id HAVING MIN(score) > 60
  10. )
  12. ORDER BY a.`Name`, b.score DESC;
  1. 统计每个老师对应的stu数， 显示老师姓名，stu数；
  2. 查询stu姓名，科目，成绩，成绩按照降序排序；