SQL语句 - 多表查询使用详细介绍

爱被打了一巴掌 2023-09-23 14:54 55阅读 0赞

#### 文章目录 ####

*  多表查询
 *   *  多表查询简介
     *  多表查询内连接
     *  多表查询外连接
     *  多表查询子查询

## 多表查询 ##

### 多表查询简介 ###

**例如我们有一张员工表和部门表, 员工表有6条数据, 部门表表有4条数据**:

# 创建部门表
    CREATE TABLE dept (
    	did INT PRIMARY KEY AUTO_INCREMENT,
    	dname VARCHAR (20)
    );
    
    -- 添加部门数据
    INSERT INTO dept (dNAME)
    VALUES ('研发部'), ('市场部'), ('财务部'), ('销售部');
    
    # 创建员工表
    CREATE TABLE emp (
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	NAME VARCHAR (10),
    	gender CHAR (1),
    	-- 性别
    	salary DOUBLE,
    	-- 工资
    	join_date DATE,
    	-- 入职日期
    	dep_id INT,
    	FOREIGN KEY (dep_id) REFERENCES dept (did) -- 外键，关联部门表(部门表的主键)
    );
    
    -- 添加员工数据
    INSERT INTO emp (
    	name,
    	gender,
    	salary,
    	join_date,
    	dep_id
    )
    VALUES(
    	'孙悟空',
    	'男',
    	7200,
    	'2013-02-24',
    	1
    ), (
    	'猪八戒',
    	'男',
    	3600,
    	'2010-12-02',
    	2
    ), (
    	'唐僧',
    	'男',
    	9000,
    	'2008-08-08',
    	2
    ), (
    	'白骨精',
    	'女',
    	5000,
    	'2015-10-07',
    	3), (
    	'蜘蛛精',
    	'女',
    	4500,
    	'2011-03-14',
    	1
    ), (
    	'小白龙',
    	'男',
    	2500,
    	'2011-02-14',
    	NULL
    );

**使用多表查询, 查询员工表和部门表两张表**:

SELECT * FROM emp, dept;

**此时查询到的结果会有24条数据, 为什么会这样呢**?

> 笛卡尔积：有A, B两个集合, 取A, B集合所有组合情况(4\*6=24);

**我们多表查询, 更多的是消除这些无效的数据**

> 例如上面表中, 我们可以加上emp表中的dep\_id和dept表中的did相等的条件, 筛选出有效的数据

SELECT * FROM emp, dept WHERE emp.dep_id = dept.did;

**多表查询：从多张表查询数据, 有下面两种方式**:

> **连接查询**
> 
> > 内连接：相当于查询A B交集数据
> > 
> > 外连接：
> > 
> >  *  左外连接：相当于查询A表所有数据和交集部分数据
> >  *  右外连接：相当于查询B表所有数据和交集部分数据
> 
> **子查询**

### 多表查询内连接 ###

**内连接相当于查询 A B 交集数据**:

![在这里插入图片描述][fa566da4d03f46a0b26d00b93b9ea1a4.png_pic_center]

**内连接查询语法**:

-- 隐式内连接
    SELECT 字段列表 FROM 表1,表2… WHERE 条件;
    
    -- 显式内连接
    SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

**隐式演示如下**:

> 查询emp表中的name和gender, dept表中的did

SELECT emp.name, emp.gender, dept.did FROM emp, dept WHERE emp.dep_id = dept.did;

> 在多表查询的时候, 我们也是可以给表取别名的

SELECT t1.name, t1.gender, t2.did FROM emp t1, dept t2 WHERE t1.dep_id = t2.did;

**显式内连接**:

> 外连查询`emp.dep_id = dept.did`的所有员工信息

SELECT * FROM emp INNER JOIN dept ON emp.dep_id = dept.did;

> INNER是可以省略的

SELECT * FROM emp JOIN dept ON emp.dep_id = dept.did;

> 查询emp表中的name和gender, dept表中的did, 同样显式内连接同样是可以取别名的

SELECT t1.name, t1.gender, t2.did FROM emp t1 JOIN dept t2 ON t1.dep_id = t2.did;

### 多表查询外连接 ###

**外连接查询语法**:

> 左外连接：相当于查询A表所有数据和交集部分数据
> 
> 右外连接：相当于查询B表所有数据和交集部分数据

-- 左外连接
    SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
    
    -- 右外连接
    SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

**演示如下**:

-- 左外连接
    SELECT * FROM emp LEFT JOIN dept ON emp.dep_id = dept.did;
    
    -- 右外连接
    SELECT * FROM emp RIGHT JOIN dept ON emp.dep_id = dept.did;

### 多表查询子查询 ###

**子查询概念**：

> 查询中嵌套查询，称嵌套查询为子查询

**子查询根据查询结果不同，作用不同**：

> 单行单列: 作为条件值，使用 = != > <等进行条件判断;
> 
> 指的是子查询语句的查询结果是单行单列的, 例如查询猪八戒工资结果如下:

![在这里插入图片描述][dccd35acfc284fa889bdcf91e685c76b.png_pic_center]

> 单行单列子查询语法如下:

SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);

> 演示: 查询emp表中猪八戒的工资, 然后再查出工资高于猪八戒工资的员工信息;

SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name = '猪八戒');

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

> 多行单列: 作为条件值，使用 in 等关键字进行条件判断;
> 
> 指的是子查询的结果是多行单列的, 例如查询出dept表中财务部和市场部的did结果如下:

![在这里插入图片描述][b6b29b06f684407e92219ab90cbf57da.png_pic_center]

> 多行单列查询语法如下:

SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);

> 演示: 查询财务部和市场部的所有员工信息

SELECT * FROM emp WHERE dep_id 
    IN (SELECT did FROM dept WHERE dname = '财务部' OR dname = '市场部');

> 多行多列: 作为虚拟表;
> 
> 子查询出来的结果是多行多列: 例如查询入职日期是’2011-11-11’之后的员工信息, 结果如下

![在这里插入图片描述][f0bd268913ac44ba90bc6760b0f16882.png_pic_center]

> 多行多列语法如下:

SELECT 字段列表 FROM (子查询) WHERE 条件;

> 演示: 查询入职日期是’2011-11-11’之后的员工信息和部门信息

SELECT * FROM (SELECT * FROM emp WHERE join_date > '2011-11-11') t1, dept WHERE t1.dep_id = dept.did;

[fa566da4d03f46a0b26d00b93b9ea1a4.png_pic_center]: https://img-blog.csdnimg.cn/fa566da4d03f46a0b26d00b93b9ea1a4.png#pic_center
[dccd35acfc284fa889bdcf91e685c76b.png_pic_center]: https://img-blog.csdnimg.cn/dccd35acfc284fa889bdcf91e685c76b.png#pic_center
[b6b29b06f684407e92219ab90cbf57da.png_pic_center]: https://img-blog.csdnimg.cn/b6b29b06f684407e92219ab90cbf57da.png#pic_center
[f0bd268913ac44ba90bc6760b0f16882.png_pic_center]: https://img-blog.csdnimg.cn/f0bd268913ac44ba90bc6760b0f16882.png#pic_center