【MySQL 数据库】3、多表查询

た入场券 2023-10-10 21:50 112阅读 0赞

#### 目录 ####

*  一、多表关系
 *  二、多表查询
 *  三、内连接
 *  四、外连接
 *  五、自连接
 *  六、联合查询
 *  七、什么是子查询
 *  八、标量子查询
 *  九、列子查询
 *  十、行子查询
 *  十一、表子查询
 *  十二、多表查询练习题

## 一、多表关系 ##

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

*  一对多(多对一)
 *  多对多
 *  一对一

![在这里插入图片描述][9a9e6a48488b428ab0af4a339fe9c564.png]

![在这里插入图片描述][8a060aa446ab41af9ed45d76cdcc73b3.png]

![在这里插入图片描述][3e09b7c8dc844cfab65f2c438bda0755.png]

## 二、多表查询 ##

![在这里插入图片描述][fe640e18365f42bab7bde787a553ae58.png]

CREATE TABLE dept ( id INT auto_increment COMMENT 'ID' PRIMARY KEY, NAME VARCHAR ( 50 ) NOT NULL COMMENT '部门名称' ) COMMENT '部门表';
    
    INSERT INTO dept ( id, NAME )
    VALUES
    	( 1, '研发部' ),
    	( 2, '市场部' ),
    	( 3, '财务部' ),
    	( 4, '销售部' ),
    	( 5, '总经办' ),
    	( 6, '人事部' );

CREATE TABLE emp (
    id INT auto_increment COMMENT 'ID' PRIMARY KEY,
    NAME VARCHAR ( 50 ) NOT NULL COMMENT '姓名',
    age INT COMMENT '年龄',
    job VARCHAR ( 20 ) COMMENT '职位',
    salary INT COMMENT '薪资',
    entrydate date COMMENT '入职时间',
    managerid INT COMMENT '直属领导ID',
    dept_id INT COMMENT '部门ID' 
    ) COMMENT '员工表';
    
    ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY ( dept_id ) REFERENCES dept ( id );
    
    INSERT INTO emp ( id, NAME, age, job, salary, entrydate, managerid, dept_id )
    VALUES
    	( 1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5 ),
    	( 2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1 ),
    	( 3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1 ),
    	( 4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1 ),
    	( 5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1 ),
    	( 6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1 ),
    	( 7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3 ),
    	( 8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3 ),
    	( 9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3 ),
    	( 10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2 ),
    	( 11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2 ),
    	( 12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2 ),
    	( 13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2 ),
    	( 14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4 ),
    	( 15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4 ),
    	( 16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4 ),
    	( 17, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL );

![在这里插入图片描述][d75b386715a349cd899be9f4245ea6ae.png]

# 会产生笛卡尔乘积
    select * from emp, dept;

# 消除笛卡尔乘积
    select * from emp, dept where emp.dept_id = dept.id;

`多表查询分类:`

![在这里插入图片描述][a1cd0d432f0d4b32b54662f3dad12b2f.png]

## 三、内连接 ##

![在这里插入图片描述][3c56439e5c0741b5b549ab7f00f7014e.png]

![在这里插入图片描述][424cfc5a48a34563bb7a330991c75703.png]

`1、查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)`

# 隐式内连接
    SELECT
    	emp.NAME '员工名',
    	dept.NAME '部门名' 
    FROM
    	emp,
    	dept 
    WHERE
    	emp.dept_id = dept.id;

`2、查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)`

SELECT
    	e.NAME '员工名',
    	d.NAME '部门名' 
    FROM
    	emp e
    	INNER JOIN dept d ON e.dept_id = d.id;

## 四、外连接 ##

![在这里插入图片描述][47fa65b630a3421d98b8f902f8f70758.png]

`1、查询emp表的所有数据, 和对应的部门信息`  
![在这里插入图片描述][1819086a0da5494f9f975468c3dd7dc4.png]

SELECT
    	e.*,
    	d.name '部门名'
    FROM
    	emp e
    	LEFT JOIN dept d ON e.dept_id = d.id;

`2、查询dept表的所有数据, 和对应的员工信息(右外连接)`

# 右外连接
    SELECT
    	d.*,
    	e.*  
    FROM
    	emp e
    	right JOIN dept d ON e.dept_id = d.id;

# 左外连接
    SELECT
    	d.*,
    	e.* 
    FROM
    	dept d
    	LEFT JOIN emp e ON d.id = e.dept_id;

## 五、自连接 ##

![在这里插入图片描述][01b23ec0c6274076af2244ace183818a.png]

`1、查询员工 及其 所属领导的名字`

![在这里插入图片描述][5f7210a1ef364ec1b073de9c10af11a7.png]

SELECT
    	e.*,
    	m.NAME '领导名' 
    FROM
    	emp e
    	JOIN emp m ON e.managerid = m.id;
    SELECT
    	* 
    FROM
    	emp;

SELECT
    	e.*,
    	m.NAME '领导名' 
    FROM
    	emp e,
    	emp m 
    WHERE
    	e.managerid = m.id;

`2、查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来`

SELECT
    	e.NAME '员工名',
    	e.managerid '领导id',
    	m.NAME '领导名' 
    FROM
    	emp e
    	LEFT JOIN emp m ON e.managerid = m.id;

## 六、联合查询 ##

![在这里插入图片描述][6414ae9e482e4a58bf72acafc4737b20.png]  
`需求：将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来`

# 没有去除重复项
    SELECT
    	emp.NAME,
    	emp.age,
    	emp.salary 
    FROM
    	emp 
    WHERE
    	emp.salary < 5000 UNION ALL SELECT emp.NAME, emp.age, emp.salary FROM emp WHERE emp.age > 50;

# 去重
    SELECT
    	emp.NAME,
    	emp.age,
    	emp.salary 
    FROM
    	emp 
    WHERE
    	emp.salary < 5000 UNION SELECT emp.NAME, emp.age, emp.salary FROM emp WHERE emp.age > 50;

![在这里插入图片描述][7290d4cfac8c4c92afc08cf8f792f36c.png]

![在这里插入图片描述][afad269d968e40a8b2ff435cda3822b5.png]

## 七、什么是子查询 ##

![在这里插入图片描述][b900e4277f7e4612be2150657e96e2a4.png]

![在这里插入图片描述][1d0f9b375d024a5b964de5deec4ce658.png]

## 八、标量子查询 ##

![在这里插入图片描述][a9b1455b3b0d47d184a443062072eeb7.png]

`1.查询 "销售部" 的所有员工信息`

SELECT
    	* 
    FROM
    	emp 
    WHERE
    	dept_id = ( SELECT id FROM dept WHERE NAME = '销售部' );

`2.查询在 "方东白" 入职之后的员工信息`

SELECT
    	* 
    FROM
    	emp 
    WHERE
    	entrydate > ( SELECT entrydate FROM emp WHERE NAME = '方东白' );

## 九、列子查询 ##

![在这里插入图片描述][5bc97504fc364198b53e9a9866373e5d.png]

`1.查询 "销售部" 和 "市场部" 的所有员工信息`

SELECT
    	* 
    FROM
    	emp 
    WHERE
    	dept_id IN ( SELECT id FROM dept WHERE NAME = '销售部' OR NAME = '市场部' );

`2.查询比 财务部 所有人工资都高的员工信息`

SELECT
    	* 
    FROM
    	emp 
    WHERE
    	salary > ALL ( SELECT salary FROM emp WHERE dept_id = ( SELECT id FROM dept WHERE NAME = '财务部' ) );

> 【比 财务部 所有人工资都高的员工信息】换句话说就是：比 财务部 工资最高的人的工资还高的员工信息

# 标量子查询
    SELECT
    	* 
    FROM
    	emp 
    WHERE
    	salary > ( SELECT max( salary ) FROM emp WHERE dept_id = ( SELECT id FROM dept WHERE NAME = '财务部' ) );

`3.查询比研发部其中任意一人工资高的员工信息`

SELECT
    	* 
    FROM
    	emp 
    WHERE
    	salary > SOME ( SELECT salary FROM emp WHERE dept_id = ( SELECT id FROM dept WHERE NAME = '研发部' ) );

## 十、行子查询 ##

![在这里插入图片描述][bb679a2f45cf4f6696e425fec796b26f.png]

`查询与 "张无忌" 的薪资及直属领导相同的员工信息`

SELECT
    	* 
    FROM
    	emp 
    WHERE
    	( salary, managerid ) = ( SELECT salary, managerid FROM emp WHERE NAME = '张无忌' );

## 十一、表子查询 ##

![在这里插入图片描述][dd7870a066894c8c900ff810fcd8dd6b.png]  
`1.查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息`

![在这里插入图片描述][a009dcbb5c36459a94e64d13655306e1.png]

SELECT
    	* 
    FROM
    	emp 
    WHERE
    	( job, salary ) IN ( SELECT job, salary FROM emp WHERE NAME IN ( '宋远桥', '鹿杖客' ) );

`2.查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息`

**把查询出来的结果集做为临时表来用**

# 子查询的结果作为临时表
    SELECT
    	t.*,
    	d.*
    FROM
    	( SELECT * FROM emp WHERE entrydate > '2006-01-01' ) t
    	LEFT JOIN dept d ON t.dept_id = d.id;

## 十二、多表查询练习题 ##

# losal: 最低薪资
    # hisal: 最高薪资
    # grade: 等级
    CREATE TABLE salgrade ( grade INT, losal INT, hisal INT ) COMMENT '薪资等级表';
    
    INSERT INTO salgrade
    VALUES
    	( 1, 0, 3000 );
    INSERT INTO salgrade
    VALUES
    	( 2, 3001, 5000 );
    INSERT INTO salgrade
    VALUES
    	( 3, 5001, 8000 );
    INSERT INTO salgrade
    VALUES
    	( 4, 8001, 10000 );
    INSERT INTO salgrade
    VALUES
    	( 5, 10001, 15000 );
    INSERT INTO salgrade
    VALUES
    	( 6, 15001, 20000 );
    INSERT INTO salgrade
    VALUES
    	( 7, 20001, 25000 );
    INSERT INTO salgrade
    VALUES
    	( 8, 25001, 30000 );

`1.查询员工的姓名、年龄、职位、部门信息 （隐式内连接）`

# 隐式内连接
    SELECT
    	e.NAME,
    	e.age,
    	e.job,
    	d.id '部门id',
    	d.NAME '部门名' 
    FROM
    	emp e,
    	dept d 
    WHERE
    	e.dept_id = d.id;

`2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）`

# 显示内连接
    SELECT
    	e.NAME,
    	e.age,
    	e.job,
    	d.id '部门id',
    	d.NAME '部门名' 
    FROM
    	emp e
    	INNER JOIN dept d ON e.dept_id = d.id 
    WHERE
    	e.age < 30;

`3.查询拥有员工的部门ID、部门名称`

# 显式内连接
    SELECT DISTINCT
    	d.id,
    	d.NAME 
    FROM
    	dept d
    	INNER JOIN emp e ON d.id = e.dept_id;
    	
    # 隐式内连接
    SELECT DISTINCT
    	d.id,
    	d.NAME 
    FROM
    	emp e,
    	dept d 
    WHERE
    	d.id = e.dept_id;

`4.查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出 来(外连接)`

SELECT
    	e.*,
    	d.NAME '部门名' 
    FROM
    	emp e
    	LEFT JOIN dept d ON e.dept_id = d.id 
    WHERE
    	e.age > 40;

`5.查询所有员工的工资等级` ☆☆☆  
**表连接的条件是：工资范围（不一定只有外键可以作为表连接的条件）**

SELECT
    	e.id,
    	e.NAME,
    	e.salary,
    	g.losal,
    	g.hisal,
    	g.grade '工资等级' 
    FROM
    	emp e
    	JOIN salgrade g ON e.salary BETWEEN g.losal 
    	AND g.hisal;

`6.查询 "研发部" 所有员工的信息及 工资等级`

SELECT
    	e.*,
    	s.* 
    FROM
    	emp e
    	JOIN salgrade s ON e.salary BETWEEN s.losal 
    	AND s.hisal 
    WHERE
    	e.dept_id = ( SELECT id FROM dept WHERE NAME = '研发部' );

`7.查询 "研发部" 员工的平均工资`

SELECT
    	avg( e.salary ) '平均工资' 
    FROM
    	emp e 
    WHERE
    	e.dept_id = ( SELECT id FROM dept WHERE NAME = '研发部' );

SELECT
    	avg( e.salary ) '平均工资' 
    FROM
    	emp e,
    	dept d 
    WHERE
    	e.dept_id = d.id 
    	AND d.NAME = '研发部';

`8.查询工资比 "灭绝" 高的员工信息`

# 标量子查询
    SELECT
    	e.* 
    FROM
    	emp e 
    WHERE
    	e.salary > ( SELECT salary FROM emp WHERE NAME = '灭绝' )

`9.查询比平均薪资高的员工信息`

SELECT
    	e.* 
    FROM
    	emp e 
    WHERE
    	e.salary > ( SELECT avg( salary ) FROM emp );

`10.查询低于本部门平均工资的员工信息`

SELECT
    	* 
    FROM
    	emp e1 
    WHERE
    	e1.salary < ( SELECT avg( e2.salary ) FROM emp e2 WHERE e2.dept_id = e1.dept_id );

`11.查询所有的部门信息, 并统计部门的员工人数`

SELECT
    	d.id,
    	d.NAME,
    	( SELECT count( * ) FROM emp e WHERE e.dept_id = d.id ) '人数' 
    FROM
    	dept d;

[9a9e6a48488b428ab0af4a339fe9c564.png]: https://img-blog.csdnimg.cn/9a9e6a48488b428ab0af4a339fe9c564.png
[8a060aa446ab41af9ed45d76cdcc73b3.png]: https://img-blog.csdnimg.cn/8a060aa446ab41af9ed45d76cdcc73b3.png
[3e09b7c8dc844cfab65f2c438bda0755.png]: https://img-blog.csdnimg.cn/3e09b7c8dc844cfab65f2c438bda0755.png
[fe640e18365f42bab7bde787a553ae58.png]: https://img-blog.csdnimg.cn/fe640e18365f42bab7bde787a553ae58.png
[d75b386715a349cd899be9f4245ea6ae.png]: https://img-blog.csdnimg.cn/d75b386715a349cd899be9f4245ea6ae.png
[a1cd0d432f0d4b32b54662f3dad12b2f.png]: https://img-blog.csdnimg.cn/a1cd0d432f0d4b32b54662f3dad12b2f.png
[3c56439e5c0741b5b549ab7f00f7014e.png]: https://img-blog.csdnimg.cn/3c56439e5c0741b5b549ab7f00f7014e.png
[424cfc5a48a34563bb7a330991c75703.png]: https://img-blog.csdnimg.cn/424cfc5a48a34563bb7a330991c75703.png
[47fa65b630a3421d98b8f902f8f70758.png]: https://img-blog.csdnimg.cn/47fa65b630a3421d98b8f902f8f70758.png
[1819086a0da5494f9f975468c3dd7dc4.png]: https://img-blog.csdnimg.cn/1819086a0da5494f9f975468c3dd7dc4.png
[01b23ec0c6274076af2244ace183818a.png]: https://img-blog.csdnimg.cn/01b23ec0c6274076af2244ace183818a.png
[5f7210a1ef364ec1b073de9c10af11a7.png]: https://img-blog.csdnimg.cn/5f7210a1ef364ec1b073de9c10af11a7.png
[6414ae9e482e4a58bf72acafc4737b20.png]: https://img-blog.csdnimg.cn/6414ae9e482e4a58bf72acafc4737b20.png
[7290d4cfac8c4c92afc08cf8f792f36c.png]: https://img-blog.csdnimg.cn/7290d4cfac8c4c92afc08cf8f792f36c.png
[afad269d968e40a8b2ff435cda3822b5.png]: https://img-blog.csdnimg.cn/afad269d968e40a8b2ff435cda3822b5.png
[b900e4277f7e4612be2150657e96e2a4.png]: https://img-blog.csdnimg.cn/b900e4277f7e4612be2150657e96e2a4.png
[1d0f9b375d024a5b964de5deec4ce658.png]: https://img-blog.csdnimg.cn/1d0f9b375d024a5b964de5deec4ce658.png
[a9b1455b3b0d47d184a443062072eeb7.png]: https://img-blog.csdnimg.cn/a9b1455b3b0d47d184a443062072eeb7.png
[5bc97504fc364198b53e9a9866373e5d.png]: https://img-blog.csdnimg.cn/5bc97504fc364198b53e9a9866373e5d.png
[bb679a2f45cf4f6696e425fec796b26f.png]: https://img-blog.csdnimg.cn/bb679a2f45cf4f6696e425fec796b26f.png
[dd7870a066894c8c900ff810fcd8dd6b.png]: https://img-blog.csdnimg.cn/dd7870a066894c8c900ff810fcd8dd6b.png
[a009dcbb5c36459a94e64d13655306e1.png]: https://img-blog.csdnimg.cn/a009dcbb5c36459a94e64d13655306e1.png