数据库的嵌套查询是个什么事？

数据库的嵌套查询

子查询标量子查询（单行单列）行子子查询（单行多列）列子子查询(多行单列)表子子查询(多行单列)UPDATA嵌套子查询DELETE嵌套子查询INSERT嵌套子查询

阅读指南： 本文章讲述了对于数据库的嵌套查询，讲述了什么是子查询，同时也列出了嵌套查询有哪些方式，如果读者感兴趣，后续我们会更新高级的操作在我们的对于数据库教程的合集中，大家可以来很我们一起学习讨论 合集链接： 数据库详细基础教程

子查询

​ 指的是在SQL语句中嵌套另一个完整的SELECT查询语句，这个嵌套的查询通常被称为子查询或内部查询

# 例如：查询嵌套子查询

SELECT studen_id, name, score FROM t_score WHERE score > (SELECT AVG(score) FROM t_score);

# 在UPDATE 语句中使用嵌套

UODATE t_scores SET score = (SELECT AVG(score) FROM t_scores) WHERE student_id = 5;

标量子查询（单行单列）

​ 用于值 值的更新，插入值，查询条件的值等等。

示例：

# 查询研发部门的所有员工信息 [间接条件]

# 查询研发部门的员工信息: 条件是研发部门 | 查询结果员工信息

# 步骤1: 根据部门名称查询部门的id

SELECT did FROM t_department WHERE dname = '研发部';

# 步骤2: 根据部门的id查询员工的集合

SELECT * FROM t_employee WHERE did = ( SELECT did FROM t_department WHERE dname = '研发部' );

# 查询每个部门的平均工资和公司的平均工资差

# 第一步：查询公司的平均工资

SELECT AVG(salary) FROM t_employee ;

# 第二步：查询每个部门的平均工资 [分组查询]

SELECT did , AVG(salary) , AVG(salary) - (SELECT AVG(salary) FROM t_employee) FROM t_employee GROUP BY did;

行子子查询（单行多列）

行子子查询: 单行和多列,一般都是整体比较 (多列) in = (行子结果）

示例：

# 查询和白露性别和部门相同信息的员工

# 步骤1: 查询白露的性别和部门

SELECT gender , did FROM t_employee WHERE ename = '白露';

# 步骤2: 查询和白露信息相同的其他员工

SELECT * FROM t_employee WHERE (gender,did) = (SELECT gender , did FROM t_employee WHERE ename = '白露')

SELECT * FROM t_employee WHERE (gender,did) IN (SELECT gender , did FROM t_employee WHERE ename = '白露')

# 行子(单行多列) 拆分成多个标量子查询 (如干个单值)

SELECT gender FROM t_employee WHERE ename = '白露';

SELECT did FROM t_employee WHERE ename = '白露';

SELECT * FROM t_employee WHERE gender = (SELECT gender FROM t_employee WHERE ename = '白露')

AND

did = (SELECT did FROM t_employee WHERE ename = '白露');

列子子查询(多行单列)

# 查询和“白露”，“谢吉娜”同一部门的员工姓名和电话。

# 步骤1: “白露”，“谢吉娜”所属部门did

SELECT did FROM t_employee WHERE ename = '谢吉娜' OR ename = '白露'

SELECT did FROM t_employee WHERE ename IN ('谢吉娜' ,'白露')

# 步骤2: 查询2和5部门的员工信息 2 5 -> in

SELECT * FROM t_employee WHERE did IN (SELECT did FROM t_employee WHERE ename IN ('谢吉娜' ,'白露'))

SELECT * FROM t_employee WHERE did NOT IN (SELECT did FROM t_employee WHERE ename IN ('谢吉娜' ,'白露'))

# 列子子查询还可以使用 any 任意一个值 == in || all 是两个值 = and

SELECT * FROM t_employee WHERE did = ANY(SELECT did FROM t_employee WHERE ename IN ('谢吉娜' ,'白露'))

# 查询薪资比“白露”，“李诗雨”，“黄冰茹”三个人的薪资都要高的员工姓名和薪资

# 步骤1: 查询他们三的工资

SELECT salary FROM t_employee WHERE ename IN ('李诗雨' ,'白露','黄冰茹')

# 步骤2: 查询大于他们所有的 any all

SELECT * FROM t_employee WHERE salary > ALL(SELECT salary FROM t_employee WHERE ename IN ('李诗雨' ,'白露','黄冰茹'))

SELECT MAX(salary) FROM t_employee WHERE ename IN ('李诗雨' ,'白露','黄冰茹')

SELECT * FROM t_employee WHERE salary > (SELECT MAX(salary) FROM t_employee WHERE ename IN ('李诗雨' ,'白露','黄冰茹'))

# 查询所有部门的 [[部门编号、部门名称]](部门表) + 连接查询(水平)+ [[部门平均薪资]] (员工表+分组查询)

# 步骤1:查询部门的平均工资 员工表 +分组

SELECT did , AVG(salary) avs FROM t_employee GROUP BY did;

# 步骤2: 查询部门的信息和平均工资

SELECT d.did,d.dname,temp.avs FROM t_department d LEFT JOIN

(SELECT did , AVG(salary) avs FROM t_employee GROUP BY did) AS temp

ON d.did = temp.did;

表子子查询(多行单列)

# 查询所有部门的 [[部门编号、部门名称]](部门表) + 连接查询(水平)+ [[部门平均薪资]] (员工表+分组查询)

# 步骤1:查询部门的平均工资 员工表 +分组

SELECT did , AVG(salary) avs FROM t_employee GROUP BY did;

# 步骤2: 查询部门的信息和平均工资

SELECT d.did,d.dname,temp.avs FROM t_department d LEFT JOIN

(SELECT did , AVG(salary) avs FROM t_employee GROUP BY did) AS temp

ON d.did = temp.did;

UPDATA嵌套子查询

# 将“测试部”部门的员工薪资改为原来薪资的1.5倍。

# 步骤1: 查询测试部门对应的部门id

SELECT did FROM t_department WHERE dname = '测试部';

# 步骤2: 根据部门id修改员工的薪水

UPDATE t_employee SET salary = salary * 1.5 WHERE did = (SELECT did FROM t_department WHERE dname = '研发部');

# 将没有部门的员工的部门改为“测试部”部门。

# 步骤1: 查询测试部门对应的部门id

SELECT did FROM t_department WHERE dname = '测试部';

# 步骤2: 修改语句

UPDATE t_employee SET did = (SELECT did FROM t_department WHERE dname = '测试部')

WHERE did IS NULL;

# 修改“t_employee”表中“李冰冰”的薪资值等于“孙红梅”的薪资值。

# 步骤1: 查询孙红梅的薪资 [员工]

SELECT salary FROM t_employee WHERE ename = '孙红梅';

# 步骤2: 修改员工表的薪资 李冰冰 [员工]

UPDATE t_employee SET salary = (SELECT salary FROM t_employee WHERE ename = '孙红梅') WHERE ename = '李冰冰';

DELETE嵌套子查询

# 将“测试部”部门的员工删除。

# 步骤1: 我们先根据部门名称查询部门id

SELECT did FROM t_department WHERE dname = '测试部'

# 步骤2: 在员工表中完成根据部门id删除员工数据

DELETE FROM t_employee WHERE did = (SELECT did FROM t_department WHERE dname = '测试部');

# 从“t_employee”表中删除和“李冰冰”同一个部门的员工记录。。

# 步骤1: 先查询李冰冰对应的部门编号

SELECT did FROM t_employee WHERE ename = '李冰冰'

# 步骤2: 删除李冰冰部门的其他员工

DELETE FROM t_employee WHERE did = (SELECT did FROM t_employee WHERE ename = '李冰冰');

# 完成和内层是同一个表! 双方占有同一个表的引用! mysql的保护机制,不让这么操作

DELETE FROM t_employee WHERE did = (SELECT did FROM ( SELECT did FROM t_employee WHERE ename = '李冰冰' ) temp );

INSERT嵌套子查询

# 创建表(employee),复制某个表的结构(t_employee)

CREATE TABLE employee LIKE t_employee;

# 使用INSERT语句+子查询，复制数据，此时INSERT不用写values

INSERT INTO employee (SELECT * FROM t_employee)

# 同时复制表结构+数据 [创建表并复制数据]

CREATE TABLE employee1 AS (SELECT * FROM t_employee)