多表查询

多个表放在一起联合查询。
学生表 student

成绩表 score

内联 inner join … on

在多表查询的时候，比如上面两个表 ，学生表中的 s_no 跟 成绩表中 s_no 相等。在做两个表查询的时候。
s_no 作为两个表之间的关联纽带。

inner join 表示两个表中 都包含有。

1. 查询 学生表，成绩表，参加考试的每位同学的成绩。 ```sql

select xuesheng.id,xuesheng.s_no,s_name,score from chengji INNER JOIN xuesheng — 内联接 ON xuesheng.s_no = chengji.s_no; — 连接的条件

![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1636083613878-8b133a33-aa1c-47e9-b107-87e902d47699.png#clientId=ued56f69c-6cf4-4&from=paste&height=84&id=uc463c2f8&margin=%5Bobject%20Object%5D&name=image.png&originHeight=168&originWidth=478&originalType=binary&ratio=1&size=9599&status=done&style=none&taskId=u9f4d3601-bc2e-4310-a7eb-84701081680&width=239)
- 因为两个表中 都有id，所有在select 后面使用 student.id ， 表示使用student中的id
- 因为两个表中都有s_no, student.s_no 表示查看学生表中 s_no;
- 因为两个表中只有一个 s_name,s_score ， 所以可以只写 s_name, s_score.
2. 查询 学生表，成绩表，参加考试的每位同学的成绩, 并按照成绩从大到小排序。
```sql
select xuesheng.id,xuesheng.s_no,s_name,score from chengji
INNER JOIN xuesheng   -- 内联接
ON xuesheng.s_no = chengji.s_no;   -- 连接的条件
order by score;

练习

表A:图书信息表

问题(写sQL):
1、查询书名“B”开头且2014年(包含2014年)之后购入的书籍清单，显示字段 barcode,name, amount,date

select a.barcode,name,amount,date from a
inner join b   
on a.barcode = b.barcode   -- 表关联
where name like "B%" and year(date)>=2014;   -- 条件

2、统计每部书的总数量，显示字段： barcode,name,总量

select a.barcode , name , sum(amount) 总量 from b
INNER JOIN a on a.Barcode = b.Barcode  -- 把表关联
group by a.barcode,name;    -- 因为 barcode 对应 name 分组的时候用两个字段分组；

3、统计购入次数大于1次的书，显示字段： barcode,name,购入次数

select a.barcode, name, COUNT(*) as 购入次数 from a 
INNER JOIN b on a.Barcode = b.Barcode
GROUP BY b.Barcode, name HAVING COUNT(*)>1;

4、统计每本书的销量，按照销量大小降序排序，显示 barcode, name, 销量；

-- 统计每本书的销量，按照销量大小降序排序，显示 barcode, name, 销量；
SELECT b.barcode,NAME,SUM(amount)FROM a 
INNER JOIN b on a.barcode=b.barcode  -- 表关联
GROUP BY b.barcode,NAME      -- 分组
ORDER BY SUM(amount) DESC ;  -- 排序

5、统计销量最高的图书，显示 barcode, name, 销量；

-- 1. 找到最大销量值。
-- 1.1 先找每个书的销量
select barcode,sum(amount) from b GROUP BY barcode;
-- 1.2 排序
select sum(amount) from b GROUP BY barcode order by sum(amount) desc limit 1;
-- 2  barcode, name, 销量
select a.barcode,name,sum(amount) as 销量 from a 
INNER JOIN b ON a.Barcode = b.Barcode 
GROUP BY a.barcode,name
HAVING 销量=100;  -- 100 是上一句查询出来的结果
-- 3 合并语句
select a.barcode,name,sum(amount) as 销量 from a 
INNER JOIN b ON a.Barcode = b.Barcode 
GROUP BY a.barcode,name
-- 将100 替换为具体的查询语句
HAVING 销量=(select sum(amount) from b GROUP BY barcode order by sum(amount) desc limit 1);

左联 left join … on

左联和内联的区别:
内联取两个表共有的。
左联是以 左表 为主，跟另外一张表关联，如果另外一张表中，没有对应的值，就显示为Null。

学生表和成绩表
关系: xuesheng.s_no = chengji.s_no

查询所有同学的考试成绩。显示 学号（s_no），姓名（s_name），成绩（score）；
因为要查看所有的学生，以学生表 为主。 xuesheng 表作为左表。

select xuesheng.s_no,s_name,score from  xuesheng   -- xuesheng 主表
left join chengji
on xuesheng.s_no = chengji.s_no;

右连right join

跟左连接原理一样，以右表为主。
市场上 90% 的开发人员习惯使用左连。

举个例子

SELECT * from xuesheng
RIGHT JOIN chengji
ON xuesheng.s_no = chengji.s_no;

right join 后面跟 chengji 表，所以是以chengji表为主表， chengji表中有个 fanmao06的学号，但是学生表中没有这个学号，查询出来的结果就会为空。

查询 chengji 表与 kecheng 表， chengji为主表。使用右连。

select chengji.c_no,c_name,score from kecheng
right join chengji
on chengji.c_no = kecheng.c_no;

union 连接

Mysql数据库中不支持外连接，使用 union 连接查询的结果。

SELECT count(*) from students
WHERE sex = "man"
UNION
SELECT count(*) from students
WHERE sex = "woman"

在两个select语句之间 使用 union 可以将两个结果放到一起；

查询students表中，显示语文分数最高的同学，数学分数最高的同学；
分别找出语文最高分，数学最高分，然后使用union 合并到一起;

-- 语文最高分
select max(score) from students where course = "语文";
select max(score) from students where course = "数学";
-- 使用 union 连接结果
SELECT * FROM students WHERE score = (select max(score) from students where course = "语文") AND course = "语文"
UNION
SELECT * FROM students WHERE score = (select max(score) from students where course = "数学") AND course = "数学"
UNION
SELECT * FROM students WHERE score = (select max(score) from students where course = "英语") AND course = "英语"

练习

1. 统计每个同学的参与考试成绩, 显示学号，姓名，科目，成绩

分析： 需要使用到 3张表

select xuesheng.s_no,s_name,c_name,score from xuesheng
inner join chengji on xuesheng.s_no = chengji.s_no
inner join kecheng on kecheng.c_no = chengji.c_no  -- 三个表关联

1. 查询每个同学考试的成绩， 显示 学号s_no, 姓名 s_name, 科目 c_name ,成绩 score ，老师名字t_name;

   select xuesheng.s_no, s_name,c_name,score,t_name from chengji
   INNER JOIN xuesheng on xuesheng.s_no = chengji.s_no
   INNER JOIN kecheng on kecheng.c_no = chengji.c_no 
   INNER JOIN laoshi on laoshi.t_no = kecheng.t_no;

作业

参考
Mysql-练习题

1. 统计参加的每门课程的人数， 显示 课程名c_name，数量。 ```sql select c_name, count(*) from chengji INNER JOIN kecheng on chengji.c_no = kecheng.c_no GROUP BY c_name;

2. 统计平均分最高的科目
```sql
-- 1. 找到最高的值
select avg(score) from chengji GROUP BY c_no ORDER BY avg(score) desc limit 1 ;
-- 2. 根据值找到对应的科目  -- 97.5
-- 找到平均分为97.5 的科目 
SELECT c_no, avg(score) from chengji 
GROUP BY c_no 
HAVING avg(score) = 97.5;    -- c4 ，c3
-- 3 找到对应的科目 
SELECT c_no, avg(score) as 平均分 from chengji 
GROUP BY c_no 
HAVING avg(score) = (select avg(score) from chengji GROUP BY c_no ORDER BY avg(score) desc limit 1);    -- 临时表
-- 合并
select c_name, 平均分 from kecheng 
INNER JOIN  
-- 上面的结果作为临时表
( SELECT c_no, avg(score) as 平均分 from chengji 
GROUP BY c_no 
HAVING avg(score) = (select avg(score) from chengji GROUP BY c_no ORDER BY avg(score) desc limit 1) ) as tmp 
on kecheng.c_no = tmp.c_no;

第二种写法

-- 1. 找到最高的值
select avg(score) from chengji GROUP BY c_no ORDER BY avg(score) desc limit 1 ;
-- 2 找到等于最高分的科目
SELECT c_name,AVG(score) from kecheng
INNER JOIN chengji on kecheng.c_no = chengji.c_no
-- 关联表之后 再做过滤
GROUP BY chengji.c_no,c_name
HAVING AVG(score) = (   -- 平均分最高
    select avg(score) from chengji GROUP BY c_no ORDER BY avg(score) desc limit 1 
)

1. 统计每个学生的总分

   SELECT s_name, sum(score) FROM xuesheng 
   LEFT JOIN chengji on xuesheng.s_no = chengji.s_no
   GROUP BY xuesheng.s_no, s_name

2. 统计总分最高的学生姓名，总分。 ```sql — 最高分 SELECT sum(score) FROM xuesheng LEFT JOIN chengji on xuesheng.s_no = chengji.s_no GROUP BY xuesheng.s_no, s_name ORDER BY SUM(score) DESC LIMIT 1;

— 找人 SELECT s_name, sum(score) FROM xuesheng LEFT JOIN chengji on xuesheng.s_no = chengji.s_no GROUP BY xuesheng.s_no, s_name HAVING SUM(score) = ( SELECT sum(score) FROM xuesheng LEFT JOIN chengji on xuesheng.s_no = chengji.s_no GROUP BY xuesheng.s_no, s_name ORDER BY SUM(score) DESC LIMIT 1 );

5. 统计参加考试的科目中 每门课成绩 都大于60分的学生 姓名，科目，成绩。
```sql
-- 最小分 大于60 的学号
SELECT s_no from chengji
GROUP BY s_no
HAVING min(score) > 60;
SELECT s_name,c_name,score from xuesheng 
INNER JOIN chengji on xuesheng.s_no = chengji.s_no
INNER JOIN kecheng on chengji.c_no = kecheng.c_no
where xuesheng.s_no in (SELECT s_no from chengji
GROUP BY s_no
HAVING min(score) > 60)