目录
一、自连接
1.概述 :
自连接是指在同一张表上的连接查询（将同一张看做两张表)；自连接本质上就是特殊的多表查询。
2.语法 :
SELECT column_1 [AS alias_1], column_2 [AS alias_2]...
FROM table_1 表别名, table_2 表别名...
WHERE connect_expression...;
注意事项——
1° 可以根据需要对查询的字段起别名；
2° 对表进行自连接时，必须对该表起两个别名，否则报错；对表起别名时不需要用到AS。
3.演示 :
先来建一张职员表，如下——
CREATETABLE IF NOT EXISTS `staff`( `sno` MEDIUMINTUNSIGNEDNOTNULL DEFAULT 0, `sname` VARCHAR(40)NOTNULL DEFAULT '', `ssex` CHAR(10), `ssalary` DECIMAL(8,2), `mgr` MEDIUMINTUNSIGNEDNOTNULL DEFAULT 0) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO `staff`(`sno`,`sname`,`ssex`,`ssalary`,`mgr`)VALUES(1000,'Cyan','male',15000,500),(1001,'Ice','male',17000,501),(1002,'Rain','female',20000,502),(1003,'Five','female',9900,503),(1004,'Rose','female',13500,504),(1005,'Wood','male',12700,505),(500,'爷1号','male',99999,0),(501,'爷2号','male',99999,0),(502,'爷3号','male',99999,0),(503,'爷4号','male',99999,0),(504,'爷5号','male',99999,0),(505,'爷6号','male',99999,0);SELECT*FROM `staff`;
编辑
现要求——
查询职员编号，职员姓名，以及职员对应的的上级编号和上级姓名。如下:
# 相当于把第一张表当作了下属表，第二张表当作了上级表（本质是同一张表）。 SELECT `demo_1`.`sno` AS'sub_no', `demo_1`.`sname` AS'sub_name', `demo_1`.`mgr` AS'sup_no', `demo_2`.`sname` AS'sup_name'FROM `staff` `demo_1`, `staff` `demo_2` WHERE `demo_1`.mgr= `demo_2`.sno;
编辑
二、外连接
1.为什么需要外连接？
多表查询中，通过WHILE子句的条件来对笛卡尔积的结果进行过滤；但是查询结果只会显示WHERE关联条件匹配成功的记录，而不显示匹配失败的记录。
举个栗子，当前有两张表分别是雇员表emp和部门表dep，如下图所示 :
现要求——
查询员工姓名，员工职位，员工部门号以及对应的部门名称，并且如果某个部门下没有员工，也要求显示出部门号和部门名称。
sSELECT ename,ecareer,emp.deptno,dname FROM emp,dep WHERE emp.deptno= dep.dno;
编辑
显然，普通的多表查询无法满足我们的需求。这是因为雇员表中没有出现40部门的员工，因此不满足WHERE子句的关联条件，而多表查询只会显示匹配WHERE子句条件的记录，因而也就无法显示40部门的部门号和部门名称，但40部门又确实是真实存在的，这便是多表查询的一个弊端。
2.外连接的定义 :
外连接最常用的有两种，左外连接和右外连接。其中，左外连接表示连接后左侧的表会完全显示；右外连接则表示连接后右侧的表会完全显示。
左外连接使用格式如下——
SELECT column_1, column_2...column_n
FROM table_1 LEFT JOIN table_2
ON connect_expression;
右外连接使用格式如下——
SELECT column_1, column_2...column_n
FROM table_1 RIGHT JOIN table_2
ON connect_expression;
注意事项——
左外连接后，左表中无法匹配查询条件的记录也会显示，但左表中不存在的字段数据自动为NULL；右外连接后，右表中无法匹配查询条件的记录也会显示，但右表中不存在的字段数据自动为NULL。
3.外连接的演示 :
1° 左外连接
先来建两张表——学生表stus和成绩表scores。
创建学生表stus的代码如下 :
CREATETABLE IF NOT EXISTS `stus`( `id` MEDIUMINTUNSIGNEDNOTNULL DEFAULT 0, `name` VARCHAR(32)NOTNULL DEFAULT '', `sex` CHAR(16)NOTNULL DEFAULT '') CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO `stus`(`id`, `name`, `sex`)VALUES(1,'Cyan','male'),(2,'Five','female'),(3,'Ice','male'),(4,'Rain','female'),(5,'Kaiyu','male');SELECT*FROM `stus`;
学生表stus效果如下 :
编辑
创建成绩表scores的代码如下 :
CREATETABLE IF NOT EXISTS `scores`( `id` MEDIUMINTUNSIGNEDNOTNULL DEFAULT 0, `score` MEDIUMINTUNSIGNEDNOTNULL DEFAULT 0) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO `scores` VALUES(1,141),(2,135),(5,142),(7,138),(10,150);SELECT*FROM `scores`;
成绩表scores效果如下 :
编辑
现要求——
查询学生的编号，姓名和成绩；并要求显示所有的学生，若该学生无成绩，则成绩一栏显示为空。
SELECT stus.id, `name`, score FROM stus LEFT JOIN scores ON stus.id= scores.id;
编辑
2° 右外连接
仍然操作学生表stus和成绩表scores，
现要求——
要求查询学生的编号，姓名，性别及成绩；并要求显示所有的成绩，若学生表中无对应的学生，则学生的相关信息显示为空。
SELECT stus.id, `name`, sex, score FROM stus RIGHT JOIN scores ON stus.id= scores.id;
编辑
3° 对部门表问题的解决
针对于“为什么需要外连接”中引出的关于显示所有部门的问题，现在可以通过左外连接和右外连接进行解决。
需求如下——
查询员工姓名，员工职位，员工部门号以及对应的部门名称，并且如果某个部门下没有员工，也要求显示出部门号和部门名称
代码如下 :
# 方式一 : 左外连接 SELECT ename,ecareer,dep.dno,dname FROM dep LEFT JOIN emp ON dep.dno= emp.deptno;# 方式二 : 右外连接 SELECT ename,ecareer,dep.dno,dname FROM emp RIGHT JOIN dep ON emp.deptno= dep.dno;
System.out.println("END------------------------------------------------------------------------------");
