多表查询

image.png
有两张表
课程表和老师表。
两个表之间的关系，是通过tid关联在一起

基本语法

  1. select 字段名 from table1,table2
  2. where table1.字段 = table2.字段 -- 两个表之间的关联
  1. 查询老师编号，姓名，所教授的课程名。 ```python

select course.tid,tname,cname from teacher,course where teacher.tid = course.tid; — 两个表之间的关联

  1. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1632375613856-90ba0120-8385-4381-98fe-029b0bd6d0e1.png#clientId=u885477f5-a747-4&from=paste&height=101&id=uc7fb2b1f&margin=%5Bobject%20Object%5D&name=image.png&originHeight=202&originWidth=446&originalType=binary&ratio=1&size=28848&status=done&style=none&taskId=u551800b7-f531-42bc-aee6-2427708da81&width=223)
  3. 2. 查询学生的姓名（sname）对应的成绩(score).
  5. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1632376013305-0dec9665-8224-4644-85a7-8e36cbb50e12.png#clientId=u8b85fa2b-a9d7-4&from=paste&height=494&id=ud458c960&margin=%5Bobject%20Object%5D&name=image.png&originHeight=988&originWidth=846&originalType=binary&ratio=1&size=290784&status=done&style=none&taskId=u8875e2c1-e6ee-4ffc-84c2-18a8804e747&width=423)

select sc.sid, sname,score FROM scholar,sc WHERE scholar.sid = sc.sid;

  1. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1632376542814-9b4ff2b1-2265-48bc-86fc-e062c3391360.png#clientId=u8b85fa2b-a9d7-4&from=paste&height=269&id=u70a1718f&margin=%5Bobject%20Object%5D&name=image.png&originHeight=538&originWidth=412&originalType=binary&ratio=1&size=73493&status=done&style=none&taskId=ufb1da575-d9f8-4451-994a-d4b5d7f4d4e&width=206)
  3. 3. 查询学生的姓名，总分,并按照总分进行降序排序；
  4. ```sql
  5. -- 查询学生的姓名，总分 
  7. SELECT sc.Sid,sname,sum(score) from  scholar,sc
  8. WHERE scholar.Sid=sc.Sid 
  9. GROUP BY sc.Sid
  10. ORDER BY sum(score) DESC;

image.png

  1. 查询总分最高的同学姓名，总分； ```sql — 过滤最高分

select sum(score) from sc GROUP BY sid order by sum(score) desc LIMIT 1

— 根据最高分 找到对应的人员信息 SELECT sc.Sid,sname,sum(score) from scholar,sc WHERE scholar.Sid=sc.Sid GROUP BY sc.Sid having SUM(score) = ( select sum(score) from sc GROUP BY sid order by sum(score) desc LIMIT 1 )

  1. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1632377662011-c17996ee-a0f4-4ecf-aca4-e8aab870488f.png#clientId=u8b85fa2b-a9d7-4&from=paste&height=77&id=uaa9f2969&margin=%5Bobject%20Object%5D&name=image.png&originHeight=154&originWidth=532&originalType=binary&ratio=1&size=14891&status=done&style=none&taskId=u7de74d9b-ba72-4622-847a-bb53e37d49d&width=266)
  3. 4. 查询学号（sid）， 学生姓名（sname），科目名称（cname），科目成绩（score）；
  5. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1632377835283-6e318498-c469-460a-b2e6-a05af26d5d79.png#clientId=u8b85fa2b-a9d7-4&from=paste&height=515&id=u2330f50b&margin=%5Bobject%20Object%5D&name=image.png&originHeight=1030&originWidth=1610&originalType=binary&ratio=1&size=475591&status=done&style=none&taskId=u2a4c0a61-cc08-4136-850c-ba37fac8b3a&width=805)
  6. ```sql
  7. select scholar.sid,sname,cname,score from scholar,sc,course
  8. where sc.sid = scholar.sid and sc.cid = course.cid;

image.png

  1. 汇总每门课程课程名，总成绩，平均成绩，考试人数

    1. select cname, sum(score), avg(score), count(1) from scholar,sc,course
    2. where sc.sid = scholar.sid and sc.cid = course.cid
    3. GROUP BY cname;

    image.png

    总结

    在做多表查询的时候：

  2. 找到每个表之间对应的关系。关系写在 where 子句中。

  3. 后面的操作跟单表操作原理一样。

    内联接 inner join

    Mysql 04-多表联查 - 图5
    image.png

inner join 在连接多个表的时候主要过滤出来表中共同的数据。

  1. 使用 inner join 查询 学生姓名(sname)，学生成绩（score）;

    1. select sname,score from scholar
    2. inner join sc              -- 连接表
    3. on sc.sid = scholar.sid;    -- 表之间的关联

    使用 inner join 内连接 与上面使用 where 多表查询 效果一样。

  2. 查询学生姓名（sname），考试科目（cname），成绩（score）；

image.png

  1. select sname,cname,score from scholar
  2. inner join sc 
  3. on scholar.sid = sc.sid
  4. inner join course
  5. on sc.cid = course.cid;

image.png

  1. 学生姓名（sname），考试科目（cname），对应老师姓名（tname），成绩（score）；
    1. SELECT sname, cname,tname,score from scholar
    2. INNER JOIN sc
    3. ON sc.Sid = scholar.Sid
    4. INNER JOIN course
    5. ON course.cid = sc.cid 
    6. INNER JOIN teacher
    7. ON teacher.Tid = course.Tid;
    image.png

左联结 left join

Mysql 04-多表联查 - 图10

  1. 查询学生姓名，学生的成绩（如果学生没有对应的成绩，显示为Null）

image.png

  1. select sname,score from scholar
  2. left join sc
  3. on scholar.sid = sc.sid;

image.png

  1. 查询张三同学的所有的考试科目（cname），成绩（score）；
    1. SELECT "张三" AS 姓名, cname 课程 ,score 成绩 from course
    2. LEFT JOIN (SELECT * from sc WHERE sid in (SELECT sid from scholar WHERE sname="张三" )) as stu1
    3. ON stu1.cid = course.cid
    image.png

右联结 right join

跟 left join 效果一样，以右边的表为主表。

需要注意的是： Mysql 不支持 外连接；

组合查询union

union 可以将两个查询语句合并一张表中；
image.png

  1. 查询语文课程的总成绩,可以使用下面的sql ```sql SELECT course,sum(score) from students WHERE course = “语文” GROUP BY course
  1. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1632386816293-c88d9a1a-2f71-4242-b8c5-79819bb7a724.png#clientId=u8b85fa2b-a9d7-4&from=paste&height=61&id=uf66a0bcd&margin=%5Bobject%20Object%5D&name=image.png&originHeight=122&originWidth=372&originalType=binary&ratio=1&size=10764&status=done&style=none&taskId=ue6af19ce-c7aa-46ee-bbdf-d01e46a45d9&width=186)
  3. 2. 查询数学课程的总成绩,可以使用下面的sql
  4. ```sql
  5. SELECT course,sum(score) from students
  6. WHERE course = "数学"
  7. GROUP BY course
  1. 上面2条sql语句查询的结果可以合并在一起 ```sql SELECT course,sum(score) from students WHERE course = “语文” GROUP BY course

UNION — 连接两个数据

SELECT course,sum(score) from students WHERE course = “数学” GROUP BY course

  1. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1632386909937-de245531-7fff-448e-93b6-68cdfe95d0ce.png#clientId=u8b85fa2b-a9d7-4&from=paste&height=80&id=u404893ef&margin=%5Bobject%20Object%5D&name=image.png&originHeight=160&originWidth=376&originalType=binary&ratio=1&size=17795&status=done&style=none&taskId=u3f245db1-a386-4c5e-b3d5-d374fb4a3d5&width=188)
  3. <a name="pczMg"></a>
  4. # 面试问题
  6. 1. 左联和内联的区别？
  7. 1. 怎么做多表联查？
  9. <a name="SOmWb"></a>
  10. # 思维导图
  11. ![Mysql 查询.svg](https://cdn.nlark.com/yuque/0/2021/svg/87080/1632388852797-793c8915-a60f-4a1d-a7e4-ea8ff635eda2.svg#clientId=u1197c342-9290-4&from=ui&id=ub532d6f4&margin=%5Bobject%20Object%5D&name=Mysql%20%E6%9F%A5%E8%AF%A2.svg&originHeight=1320&originWidth=1522&originalType=binary&ratio=1&size=1525113&status=done&style=none&taskId=u257faa65-c6cf-47e2-9dc5-c67c52329c3)
  13. <a name="QIqWt"></a>
  14. # 作业
  15. 根据下面4个表<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1632389248259-469972af-83d9-482e-8866-ce20bf186a47.png#clientId=u1197c342-9290-4&from=paste&height=153&id=ud59d124f&margin=%5Bobject%20Object%5D&name=image.png&originHeight=306&originWidth=718&originalType=binary&ratio=1&size=50969&status=done&style=none&taskId=u5d5b715a-543b-415d-a051-3e90d2fc28a&width=359)![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1632389263348-ed418a09-f032-45f5-ae59-59d2daeeb709.png#clientId=u1197c342-9290-4&from=paste&height=282&id=u9f8e7589&margin=%5Bobject%20Object%5D&name=image.png&originHeight=564&originWidth=676&originalType=binary&ratio=1&size=86434&status=done&style=none&taskId=u4c188ff3-bb58-4e5c-a85a-d120bcfd0bd&width=338)<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1632389288755-065766d0-25d8-45a5-977f-8e3452c80ee7.png#clientId=u1197c342-9290-4&from=paste&height=129&id=u7914409e&margin=%5Bobject%20Object%5D&name=image.png&originHeight=258&originWidth=588&originalType=binary&ratio=1&size=32231&status=done&style=none&taskId=ud5dbb729-6ae0-46d8-a2b1-e4c0c5aa2a7&width=294)![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1632389306269-31a11ebc-5bc7-4edb-b38e-59a6c0c0a00d.png#clientId=u1197c342-9290-4&from=paste&height=123&id=u9cfe9a5d&margin=%5Bobject%20Object%5D&name=image.png&originHeight=246&originWidth=782&originalType=binary&ratio=1&size=37827&status=done&style=none&taskId=u0f7e90c4-9fd2-45e3-97fd-b3e02506b64&width=391)
  17. ---
  19. 1.查询姓"张"的学生名单；
  20. ```sql
  21. select * from scholar
  22. where sname like "张%";

2.查询平均成绩大于60分的同学的学号和平均成绩；

  1. select sid,  avg(score) from sc
  2. group by sid
  3. having avg(score) > 60;

3.查询所有同学的学号、姓名、选课数、总成绩；

  1. select scholar.sid,sname,count(course.cid),sum(score) from scholar
  2. inner join sc
  3. on scholar.sid = sc.sid
  4. inner join course 
  5. on course.cid = sc.cid
  7. GROUP BY scholar.Sid,Sname

4.查询没学过王大锤老师课的同学的学号、姓名；

  1. -- 1. 找到王大锤老师的 课程id cid
  2. select cid from course
  3. inner join teacher
  4. on course.tid = teacher.tid
  5. where tname = "王大锤"
  8. -- 2. 从成绩表中找到没有 王大锤的 cid的学号 sid
  10. select DISTINCT(sid) from sc
  11. where cid <> (
  12.     select cid from course
  13.   inner join teacher
  14.   on course.tid = teacher.tid
  15.   where tname = "王大锤"
  16. )
  19. -- 根据学号 可以找到姓名 
  21. SELECT sid,sname from scholar
  22. WHERE sid in (
  23.     select DISTINCT(sid) from sc
  24.     where cid <> (
  25.         select cid from course
  26.         inner join teacher
  27.         on course.tid = teacher.tid
  28.         where tname = "王大锤"
  29.     )
  30. );

5.查询每门课程被选修的学生数；

  1. select cid, count(cid) from sc
  2. group by cid;
  5. -- 也可以
  6. select (select course.Cname from course WHERE course.Cid =sc.Cid) as 科目, count(cid) 选修人数 from sc
  7. group by cid;

7.查询学生名单，统计男生，女生人数；

  1. select ssex,count(ssex) from scholar
  2. group by ssex;

8.查询出只修了一门课程的全部学生的学号和姓名；

  1. -- 1. 查找修一门课的学号 sid
  2. SELECT sid from sc
  3. GROUP BY sid 
  4. HAVING count(sid) = 1
  6. -- 2. 根据学号 找人 
  8. select sid,sname from scholar
  9. WHERE sid in (
  10.     SELECT sid from sc
  11.     GROUP BY sid 
  12.     HAVING count(sid) = 1
  13. )

9.统计每个同学的课程成绩，并按课程号从大到小排序列；

  1. select sname,cname, score from scholar
  2. inner join sc 
  3. on sc.sid = scholar.sid
  4. inner join course
  5. on course.cid = sc.cid
  6. order by course.cid

10.语文成绩大于数学成绩的学生信息；

  2. select scholar.sid 学号,sname 名字 from scholar
  3. INNER JOIN sc 
  4. ON sc.sid = scholar.Sid
  5. INNER JOIN course
  6. ON sc.cid = course.Cid
  7. -- 语文成绩 并且语文大于数学
  8. WHERE cname = "语文" AND score > (
  9.     -- 查询数学成绩
  11.         SELECT 数学 from (select scholar.sid 学号,sname 名字,score 数学 from scholar
  12.                 INNER JOIN sc 
  13.                 ON sc.sid = scholar.Sid
  14.                 INNER JOIN course
  15.                 ON sc.cid = course.Cid
  16.                 WHERE cname = "数学") as math
  18.         WHERE 学号 = math.学号
  19. );

image.png
1.查出平均成绩>85的学生学号，姓名，平均成绩；

  1. select No,Name,avg(score) from students
  2. group by No,Name
  3. having avg(score) > 85;

2.在students表中 查询出每门课都大于80分的学生姓名；

  1. -- 都大于80， 最低分大于80 即可
  2. select no, name from students
  3. group by no,name
  4. having min(score) > 80;

3.查出平均成绩第一名的学生；

  1. -- 最高的平均成绩
  2. select avg(score) from students
  3. group by No
  4. order by avg(score) desc 
  5. limit 1
  7. -- 根据成绩找人
  8. select No,Name,avg(score) from students
  9. group by No,Name
  10. having avg(score) = (
  11.     select avg(score) from students
  12.     group by No
  13.     order by avg(score) desc 
  14.     limit 1
  15. );

4.查出所有语文成绩>数学成绩的学生学号，姓名，语文成绩，数学成绩；

  1. select No,Name,
  2. sum(case course when '语文' then score else 0 End) as '语文',
  3. sum(case course when '数学' then score else 0 End) as '数学',
  4. sum(case course when '英语' then score else 0 End) as '英语'
  5. from students
  6. GROUP BY No,Name

image.png

  1. -- 在从上面生成的表中做查询
  2. select * from 
  3. (select No,Name,
  4. sum(case course when '语文' then score else 0 End) as '语文',
  5. sum(case course when '数学' then score else 0 End) as '数学',
  6. sum(case course when '英语' then score else 0 End) as '英语'
  7. from students
  8. GROUP BY No,Name) as tmp
  9. where 语文>数学;

image.png

附件

https://pear-fall-0cf.notion.site/64874dc67957406ca99d5d5a26789752