MySQL_6 自连接和外连接

深碍√TFBOYSˉ_ 2023-09-27 23:21 7阅读 0赞

**目录**

一、自连接

1.概述 :

2.语法 :

3.演示 :

二、外连接

1.为什么需要外连接？

2.外连接的定义 :

3.外连接的演示 :

1° 左外连接

2° 右外连接

3° 对部门表问题的解决

--------------------

## 一、自连接 ##

### 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`;

![331c904e9a8946bb81a018104436881b.png][]

**现要求——**  
**查询职员编号，职员姓名，以及职员对应的的上级编号和上级姓名。如下**:

# 相当于把第一张表当作了下属表，第二张表当作了上级表（本质是同一张表）。
    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;

![e3a91c66b5564c4296c5fa5bd6c338c2.png][]

--------------------

## 二、外连接 ##

### 1.为什么需要外连接？ ###

> **多表查询中**，通过WHILE子句的条件来对笛卡尔积的结果进行过滤；但是查**询结果只会显示WHERE关联条件匹配成功的记录，而不显示匹配失败的记录**。

举个栗子，当前有两张表分别是**雇员表emp**和**部门表dep**，如下图所示 :

![e3858d2d27244b0bbcf388f9143b9e45.png][]

![4543637f1f3044a980963d2b7d1bb5b0.png][]

**现要求——**  
查询员工姓名，员工职位，员工部门号以及对应的部门名称，并且如果某个部门下没有员工，也要求显示出部门号和部门名称。

SELECT ename,ecareer,emp.deptno,dname
    		FROM emp,dep
    		WHERE emp.deptno = dep.dno;

![3ec515c66eb246ce9f45cf996a902c8e.png][]

显然，普通的多表查询无法满足我们的需求。这是因为雇员表中没有出现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效果如下 :**

![9e4273696877406ead0f23fa2233e5e4.png][]

**创建成绩表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效果如下 :**

![02b9aa61699047c4adb10c532125e35a.png][]

**现要求——**  
查询学生的编号，姓名和成绩；并要求显示所有的学生，若该学生无成绩，则成绩一栏显示为空。

SELECT stus.id, `name`, score
    		FROM stus LEFT JOIN scores
    		ON stus.id = scores.id;

![0cf5f927426c4c02b7c863d4e3362319.png][]

#### 2° 右外连接 ####

仍然操作学生表stus和成绩表scores，  
**现要求——**  
要求查询学生的编号，姓名，性别及成绩；并要求显示所有的成绩，若学生表中无对应的学生，则学生的相关信息显示为空。

SELECT stus.id, `name`, sex, score
    		FROM stus RIGHT JOIN scores
    		ON stus.id = scores.id;

![faf9630d43dc4319ae182028137eb173.png][]

#### 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;

![777e974ee8844911a847438672095d0e.png][]

![7108c1c61e4f454ea38b7322ccfd26d6.png][]

**System.out.println("END------------------------------------------------------------------------------");**

[331c904e9a8946bb81a018104436881b.png]: https://img-blog.csdnimg.cn/331c904e9a8946bb81a018104436881b.png
[e3a91c66b5564c4296c5fa5bd6c338c2.png]: https://img-blog.csdnimg.cn/e3a91c66b5564c4296c5fa5bd6c338c2.png
[e3858d2d27244b0bbcf388f9143b9e45.png]: https://img-blog.csdnimg.cn/e3858d2d27244b0bbcf388f9143b9e45.png
[4543637f1f3044a980963d2b7d1bb5b0.png]: https://img-blog.csdnimg.cn/4543637f1f3044a980963d2b7d1bb5b0.png
[3ec515c66eb246ce9f45cf996a902c8e.png]: https://img-blog.csdnimg.cn/3ec515c66eb246ce9f45cf996a902c8e.png
[9e4273696877406ead0f23fa2233e5e4.png]: https://img-blog.csdnimg.cn/9e4273696877406ead0f23fa2233e5e4.png
[02b9aa61699047c4adb10c532125e35a.png]: https://img-blog.csdnimg.cn/02b9aa61699047c4adb10c532125e35a.png
[0cf5f927426c4c02b7c863d4e3362319.png]: https://img-blog.csdnimg.cn/0cf5f927426c4c02b7c863d4e3362319.png
[faf9630d43dc4319ae182028137eb173.png]: https://img-blog.csdnimg.cn/faf9630d43dc4319ae182028137eb173.png
[777e974ee8844911a847438672095d0e.png]: https://img-blog.csdnimg.cn/777e974ee8844911a847438672095d0e.png
[7108c1c61e4f454ea38b7322ccfd26d6.png]: https://img-blog.csdnimg.cn/7108c1c61e4f454ea38b7322ccfd26d6.png