2021-01-31

联表查询

JOIN：select语句的条件之一，可以根据某些连接条件从其他表中获取数据。

JOIN的含义就如英文单词“join”一样，连接两张表，大致分为内连接，右连接，左连接，外连接，自连接。

当然你也可以连接三张表四张表，但是这将大大降低数据库性能，为什么这么说呢？

这是因为所有的连接在筛选之前都会先计算所有表的笛卡尔积

笛卡尔积，也叫交叉连接：CROSS JOIN

要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。多数情况下,笛卡尔积是无意义的,非常耗费资源,要尽量避免。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZFJwRxc5-1612076694267)(C:\Users\86135\AppData\Roaming\Typora\typora-user-images\image-20210129201815017.png)]

1.INNER JOIN 内连接

内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集。

内连接：[inner] join：从左表中取出每一条记录,去右表中与所有的记录进行匹配: 匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留.

基本语法：

​ 左表 [inner] join 右表 on

​ 左表.字段 = 右表.字段;
on表示连接条件: 条件字段就是代表相同的业务含义(如my_student.c_id和my_class.id)

当两个表中存在相同意义的字段的时候，就可以通过该字段来连接查询这两个表，当该字段的值相同时就可以查出该记录。

内连接可以没有连接条件: 没有on之后的内容,这个时候系统会保留所有结果。

select 查询的内容

from 表1

inner join 表2

思路：

1.查询的字段需要的表
2.确认使用的连接方式（7种）
确定交叉表（相同内容）

例1.1.查询学生的编号，性别，语文成绩，数学成绩

SELECT ID,sex,chinese,math
FROM student
INNER JOIN grade

例1.2查询学生的姓名，性别，语文成绩，数学成绩

SELECT `name`,sex,chinese,math
FROM student
INNER JOIN grade

join on 连接查询

关键字：on

on：直接跟条件表达式

例1.3查询学生的姓名，性别，语文成绩，数学成绩

SELECT g.`name`,sex,chinese,math
FROM student AS s
INNER JOIN grade AS g
ON s.name=g.name

1.as 语句的应用

2.确定查询的交叉部分属于哪个表

多个交叉表

两个：

SELECT * FROM 表1 INNER JOIN 表2

ON 表1.字段号=表2.字段号

三个：

SELECT * FROM (表1 INNER JOIN 表2

ON 表1.字段号=表2.字段号) INNER JOIN 表3

ON 表1.字段号=表3.字段号

四个：SELECT * FROM ((表1 INNER JOIN 表2

ON 表1.字段号=表2.字段号) INNER JOIN 表3

ON 表1.字段号=表3.字段号)INNER JOIN 表4

ON Member.字段号=表4.字段号

注意：在建立数据表时，如果一个表与多个表联接，那么这一个表中的字段必须是**“数字”数据类型，而多个表中的相同字段必须是主键**，而且是“自动编号”数据类型。否则，很难联接成功。

2.LEFT JOIN 左连接

RIGHT JOIN 右连接

• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

区分左右表，什么连接就以哪个表为主

例1.4查询所有学生的姓名，性别，语文成绩，数学成绩

SELECT s.name,sex,chinese,math
FROM student AS s
LEFT JOIN grade AS g
ON g.name=s.name

注意：s.name 与 g.name所呈现的不同结果

左表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,右表的字段都置NULL。

右表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,左表的字段都置NULL。

拓展

SELECT s.name,sex,chinese,math
FROM grade AS g
RIGHT JOIN student AS s
ON g.name=s.name

SELECT g.name,sex,chinese,math
FROM grade AS g
RIGHT JOIN student AS s
ON g.name=s.name

例1.5查询参加考试的学生的姓名，性别，语文成绩，数学成绩

SELECT g.name,sex,chinese,math
FROM grade AS g
LEFT JOIN student AS s
ON g.name=s.name

拓展

SELECT g.name,sex,chinese,math
FROM student AS s
RIGHT JOIN grade AS g
ON g.name=s.name

SELECT s.name,sex,chinese,math
FROM student AS s
RIGHT JOIN grade AS g
ON g.name=s.name

例1.6查询Chinese为空的学生

SELECT s.name,age,chinese,math
FROM grade AS g
RIGHT JOIN student AS s
ON s.name = g.name
WHERE chinese IS NULL

例1.7 查询数学成绩小于70的学生

SELECT s.name,age,chinese,math
FROM grade AS g
RIGHT JOIN student AS s
ON s.name = g.name
WHERE math <"70"

例1.8 查询数学成绩小于70的女学生（and）

SELECT s.name,age,chinese,math
FROM grade AS g
RIGHT JOIN student AS s
ON s.name = g.name
AND sex="女"
WHERE math <"70"

例1.9 一对多查询

g>s>g1

select * from a left join b on a.id = b.id left join a.id = c.id，

a表先和b表进行连接，会生成一张中间临时表，然后这张表的数据再和c表进行连接，最后生成的表的数据就是a left join b left join c 的。

SELECT s.name,age,chinese,math
FROM grade AS g
RIGHT JOIN student AS s
ON s.name = g.name
RIGHT JOIN grade1 AS g1
ON s.sex=g1.sex

s g1

SELECT s.name,age,chinese,math
FROM grade AS g
RIGHT JOIN student AS s
ON s.name = g.name
RIGHT JOIN grade1 AS g1
ON g.id=g1.id

3. FULL OUTER JOIN

FULL JOIN：只要其中一个表中存在匹配，则返回行

SELECT sex,age,chinese,math
FROM grade AS g
FULL JOIN student AS s
ON s.name = g.name

MySQL暂不支持FULL JOIN,要实现完全外部链接需要额外处理。

MySQL实现完全外部链接，要使用 UNION（ALL) 将一个左链接、和一个右链接去重合并。

SELECT sex,age,chinese,math
FROM grade AS g
LEFT JOIN student AS s
ON s.name = g.name
UNION  ALL
SELECT sex,age,chinese,math
FROM grade AS g
RIGHT JOIN student AS s
ON s.name = g.name

4.自连接

自连接查询其实等同于连接查询，需要两张表，只不过它的左表(父表)和右表(子表)都是自己。做自连接查询的时候，是自己和自己连接，分别给父表和子表取两个不同的别名，然后附上连接条件。

父表

parent_id	id	cate_name
0	1	数码产品
0	2	家用产品
2	5	电器
0	9	汽车品牌

子表

parent_id	id	cate_name
1	3	笔记本
1	4	智能手机
2	5	电器
2	6	家具
5	7	冰箱
5	8	洗衣机
9	10	别克
9	11	宝马
9	12	雪佛兰

SELECT a.cate_name AS 父栏目,b.cate_name AS 子栏目
FROM thing AS a
INNER JOIN thing AS b
ON a.id=b.parent_id

左表是子表，右表是父表（连接条件是子表的parent_id等于父表的id。）

SELECT a.id,a.cate_name AS 子栏目,b.cate_name  AS 父栏目
FROM thing AS a
LEFT JOIN thing AS b 
ON a.parent_id=b.id