MySQL_6 自连接和外连接
目录
一、自连接
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.演示 :
先来建一张职员表，如下——
CREATE TABLE IF NOT EXISTS `staff`(
`sno` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
`sname` VARCHAR(40) NOT NULL DEFAULT '',
`ssex` CHAR(10),
`ssalary` DECIMAL(8,2),
`mgr` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;
INSERT INTO `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的代码如下 :
CREATE TABLE IF NOT EXISTS `stus`(
`id` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
`name` VARCHAR(32) NOT NULL DEFAULT '',
`sex` CHAR(16) NOT NULL DEFAULT ''
) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;
INSERT INTO `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的代码如下 :
CREATE TABLE IF NOT EXISTS `scores`(
`id` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
`score` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;
INSERT INTO `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------------------------------------------------------------------------------");
- 点赞
- 收藏
- 关注作者
评论（0）