主流数据库之子查询

文章目录

前言

大家好，我是ice三分颜色。个人主页：ice三分颜色的博客

本文讲了有关子查询的一些相关内容。

走过路过的小伙伴们点个赞和关注再走吧，欢迎评论区交流，努力什么时候开始都不算晚，那不如就从这篇文章开始！

大家一起成长呀！笔芯

4.4子查询

子查询概述

子查询是指嵌套在其他SQL语句中的SELECT语句，也称为嵌套查询。通过子查询可以把一个复杂的查询分解成一系列的逻辑步骤，利用单个语句的组合解决复杂的查询问题。

包含子查询的查询语句可以写成连接查询的方式,因此，通过子查询也可以实现多表之间的查询。在有些方面，多表连接的性能要优于子查询，原因是连接不需要查询优化器执行排序等额外的操作。子查询的执行过程

嵌套查询的处理过程是从内层向外层处理，先处理最内层的子查询，然后把查询的结果用于其外查询的查询条件，再层层向外求解，最后得出查询结果。

子查询中的常见运算

子查询中可以包括in、not in、any、all、exists、not exists等逻辑运算符，也可以包含比较运算符“=”、“!=”、“>”和“<”等。

子查询的类型

根据子查询的结果可以将MySQL子查询分为4种类型：

返回—个表的子查询是表子查询

返回带有一个或多个值的一行的子查询是行子查询

返回—行或多行，但每行上只有一个值的是列子查询

只返回一个值的是标量子查询。从定义上讲，每个标量子查询都是一个列子查询和行子查询。

子查询注意事项

1.子查询需要用括号括起来。子查询中也可以再包含子查询，嵌套可以多至32层。

2.当需要返回一个值或一个值列表时，可以利用子查询代替一个表达式。也可以利用子查询返回含有多个列的结果集替代表或连接操作相同的功能。

3.子查询不能够检索数据类型为varchar(max)、nvarchar(max)和varbinary(max)的列。

4.子查询使用order by时，只能在外层使用，不能在内层使用。

子查询做表达式

子查询在select后使用，可以把子查询的结果当成一个普通的表达式来看待。

例：查询7369号员工薪资与公司平均薪资差

SELECT empno,ename,sal,(SELECT AVG(sal) FROM employee)"平均薪资",

sal-(SELECT AvG(sal)FROM employee)"薪资差"

FROM employee WHERE empno=7369;

子查询生成派生表

子查询在from子句中使用，作为派生表数据源。

例：查询各部门的部门编号、部门名称、部门总人数以及部门的平均工资

SELECT d.deptno,d.dname,e.amount,e.avgsal

FROM department d,

(SELECT deptno,COUNT(*) amount,AVG(sal) avgsal FROM employee GROUP BY deptno) e

WHERE d.deptno=e.deptno;

WHERE****子句中的子查询

where语句中的子查询实际上是将子查询的结果作为该语句条件中的一部分，然后利用这个条件过滤本层查询的数据。

例：查询工资高于平均工资的员工信息

SELECT empno,ename,sal FROM employee

WHERE sal>(SELECT AVG(sal)FROM employee);

WHERE****子句中带in关键字的子查询

当子查询返回的结果列包含一个值时，利用比较运算符就适用查询要求。假如一个子查询返回的结果集是值的列表，这时比较运算符就可以用in运算符代替。in运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。not in的作用与in相反。

例：查询哪些员工的工资为所任职位最高的

SELECT empno,ename,job,sal FROM employee

WHERE sal IN(SELECT MAX(sal) FROM employee GROUP BY job);

WHERE****子句中带exists关键字的子查询

使用exists关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值(true)，否则，将返回一个假值(false)。当返回的值为true时，外层查询语句将进行查询;当返回的为false时，外层查询语句不进行查询或者查询不出任何记录。not exists与exists刚好相反。

例：查询有下属员工的部门的信息(40号部门目前没有员工)

SELECT deptno,dname,loc FROM department d

WHERE EXISTS(SELECT *FROM employee e WHERE d.deptno=e.deptno);

WHERE****子句中对比较运算进行限制的子查询

ALL、SOME和ANY运算都是对比较运算的进一步限制。

ALL指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回true，否则返回false。

SOME或ANY是同义词，表示表达式只要与子查询结果集中的某个值满足比较的关系时就返回true，否则返回false。

例：查询人数最多的部门信息

SELECT deptno,dname,loc

FROM department

WHERE deptno

IN (SELECT deptno FROM employee GROUP BY deptno HAVING COUNT()>=ALL(SELECT COUNT()FROM employee GROUP BY deptno));

例：查询哪些员工的工资比任何一个职位的平均工资都低

SELECT empno,ename,job,sal

FROM employee

WHERE sal <ALL(SELECT AVG(sal) FROM employee GROUP BY job);

例：查询哪些员工的工资高于最低的职位平均工资

SELECT empno,ename,job,sal

FROM employee

WHERE sal > ANY(SELECT AVG(sal) FROM employee GROUP BY job);

利用子查询插入、更新与删除数据

利用子查询修改表数据

就是利用一个嵌套在insert、update或delete语句的子查询成批的添加、更新和删除表中的数据。

利用子查询插入数据

insert语句中的select子查询可用于将一个或多个其他表或视图的值添加到表中。使用select子查询可同时插入多行。

利用子查询更新数据

update语句中的select子查询可用于将一个或多个其他的表或视图的值进行更新。使用select子查询可同时更新多行数据。实际上是通过将子查询的结果作为更新条件表达式中的一部分。

利用子查询删除数据

delete语句中利用子查询，将子查询的结果作为删除条件表达式中的一部分来删除符合条件的数据行。

例：将2008-09-01年以后入职的员工记录添加到emptest表中。

INSERT INTO emptest (

SELECT*FROM employee WHERE hiredate>='2008-09-01');

查看下emptest表

注意：子查询的选择列表必须与insert语句列的列表匹配。如果insert语句没有指定列的列表，则选择列表必须与向其插入的表的列匹配且顺序一致。

例：将部门地址在DALLAS的员工的薪资增加5%。

UPDATE emptest SET sal=sal*1.05

WHERE deptno IN(SELECT deptno FROM department WHERE loc='DALLAS');