【数据库基础知识六】内连接、外连接、自连接

目录

内连接

语法

外连接

外连接的分类

自连接

---

内连接

语法

SELECT t1.xxx, t2.xxx, ....

FROM 表1  t1

JOIN 表2 t2  ON 与表1 的连接条件

【JOIN 表3  t3 ON  与表1 或表2 的连接条件】

内连接的查询与关联查询一致，区别在于我们将关联关系（连接条件）单独定义在JOIN后面紧跟的ON子句上。

优点：关联查询时结构清晰，连接条件与过滤条件分开在不同的子句定义。ON子句定义连接条件，WHERE 子句定义过滤条件

查看每个班的班主任是谁？

i.原来写法

SELECT c,name ,t.name

FROM class  c,  teacher t

WHERE c.teacher_id=t.id

i.内连接的写法

SELECT c.name ,t.name

FROM class c

JOIN  teacher t ON c.teacher_id=t.id

 查看1年级1班的学生的名字和其所来自的城市?

i.原来的写法

SELECT s.name ,c.name,l.name

FROM student s ,  class c,  location l

WHERE  s.class_id= c.id

AND  s.location_id =l.id

AND c.name='1年级1班’

i.内连接写法

SELECT s.name, c.name,l.name

FROM student  s

JOIN class c  ON  s.class_id=c.id

WHERE  c.name='1年级1班'

查看3年级2班每个同学的数学成绩，并按成绩的降序展示

SELECT s.name,su.name,sss.score,c.name

FROM class c

JOIN student s ON s.class_id=c.id

JOIN t_stu_subject_score  sss  ON  s.id=sss.stu_id

JOIN subject su ON sss.subject_id =su.id

WHERE c.name='数学'

ORDER BY sss.score DESC

查看4年级每个班的数学平均分是多少?

SELECT AVG(sss.score), su.name, c.name

FROM class c

JOIN student s ON s.class_id=c.id

JOIN t_stu_subject_score  sss ON s.id=sss.stu_id

JOIN subject su ON sss.subject_id=su.id

WHERE c.name='4年级%'

AND su.name='数学'

GROUP BY c.name,su.name

班级名相同且科目相同的记录看做一组

GROUP BY 子句中如果出现多个字段，则是将结果集中指定的这些字段值的组合相同的记录看作一组。

外连接

外连接的作用是将关联查询中不满足关联条件的记录显示在结果集中。

外连接的分类

1、左外连接LEFT JOIN

左外连接是将LEFT JOIN 左侧的表作为主表，结果集中会包含该表所有满足过滤条件的记录，当来自于LEFT JOIN 右侧表的字段时，不满足连接条件的记录值全为NULL。

2、右外连接RIGHT JOIN

右外连接是RIGHT JOIN 右侧的表作为主表，结果集中会包含该表所有满足过滤条件的记录，当来自于RIGHT JOIN 左侧表的字段时，不满足连接条件的记录值全为NULL。

查看每个班级名字和班主任的名字

SELECT c.name,t.name

FROM class c

JOIN teacher t ON c.teacher_id=t.id

查看每个班级名字和班主任的名字,如果该老师不带班也要把老师显示出来

需求:当teacher表中有不满足连接条件的记录时，也要将其显示出来

SELECT c.name,t.name

FROM class c

RIGHT JOIN teacher t ON c.teacher_id=t.id

class表中如:1年级4班没有被列出在结果集中

teacher表中如:韩少云没有被列出在结果集中

原因:这些记录不满足连接条件 c.teacher_id=t.id

结论:不满足连接条件的记录不会在关联查询和内连接中被查询出来

 查看每个班级名字和班主任的名字,如果该班没有班主任，也要将该班级查询出来

SELECT c.name ,t.name

FROM class c

LEFT JOIN teacher t ON c.teacher_id=t.id

UNION取并集，将左连接与右连接并集，可以达到全连接效果

union可以连接两个DQL语句的结果集，并将它们并为一个结果集，其中重复的记录仅显示一次。

SELECT c.name,t.name

FROM class c

LEFT JOIN teacher t ON c.teacher_id=t.id

UNION

SELECT c.name,t.name

FROM class  c

RIGHT JOIN teacher t ON c.teacher_id=t.id

自连接

同一张表中的一条数据可以对应多条数据。

自连接通常用于保存具有相同属性且存在上下级关系的树状结构数据使用

例如：公司中的组织架构，电商中的类别树

查看每个老师和他的领导是谁?

关联查询写法

SELECT t.name, m.name

FROM teacher t, teacher m

WHERE t.manager=m.id

内连接

SELECT t.name, m.name

FROM teacher t

JOIN teacher m ON t.manager=m.id

把所有老师和他的上司列出来，没有上司的也要把该老师列出来

SELECT t.name ,m.name

FROM teacher t

LEFT JOIN teacher m ON t.manager=m.id

 把所有老师和他的上司列出来，没有上司的也要把该老师列出来

SELECT t.name，m.name

FROM teacher t

LEFT JOIN teacher m ON t.manager=m.id

查看刘苍松的下属所带班级的名称分别是什么?

SELECT t.name,c.name ,m.name

FROM teacher t

JOIN teacher m ON t.manager=m.id

JOIN class c ON c.teacher_id=t.id

WHERE m.name='刘苍松'

查看3年级2班的班长是谁?

SELECT s.name

FROM student s

 JOIN class c ON c.id=s.class_id

WHERE c.name='3年级2班'

AND s.id=s.team_leader;

年龄最大的学生所在班的班主任的上司是谁?

SELECT s.name  '学生名字' , c.name  '所在班级'

                   t.name  '班主任'  ， m.name '班主任上司'        ##取别名

FROM student  s

JOIN class c ON s.class_id=c.id

JOIN teacher t ON c.teacher_id=t.id

JOIN teacher m  ON t.manager =m.id                          ##取另一个别名，调取上司路径

WHERE  s.birth=(SELECT MIN(birth)  FROM student)

 刘苍松的下属所带的班级各多少人?

1.统计的是那几张表的记录数？

学生的数量，数据来自学生表student

2、过滤条件

上司是刘苍松，来自表：teacher（上司表）

3、学生表与上司表没有直接联系，因此要找到关联关系表

student->class->teacher(老师)->teacher(上司)

准备要统计的数据

列出数据

学生名，所在班级，班主任，班主任上司

SELECT s.name ,c.name,t.name, m.name

RFOM student  s

JOIN class c ON s.class_id = c.id

JOIN teacher t ON c.teacher_id =t.id

JOIN teacher m ON t.manager =m.id

WHRE m.name='刘苍松'

最终

SELECT COUNT(*) ,s.gender,c.name,t.name,m.name

RFOM student  s

JOIN class c ON s.class_id = c.id

JOIN teacher t ON c.teacher_id =t.id

JOIN teacher m ON t.manager =m.id

WHRE m.name='刘苍松'

GROUP BY s.gender,c.name,t.name,m.name

ORDER BY c.name;

关联查询中未指定连接条件，或指定的连接条件无效时会产生笛卡尔积

结果集的数据量是参数查询表中记录数的乘积,除非应用需要，否则应尽量避免

例如：

SELECT COUNT(*)

FROM class c ,teacher t