子查询的使用

最新推荐文章于 2023-10-17 22:50:17 发布
最新推荐文章于 2023-10-17 22:50:17 发布
阅读量210 收藏
点赞数
分类专栏： MySQL 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_51351504/article/details/124451791
版权

一.单行子查询

1.1 单行操作符

子查询的编写技巧  --> 1)  从里往外写  2)  从外往里写

题目：查询工资大于149号员工工资的员工的信息
SELECT last_name,employee_id,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE employee_id = 149
);
题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
)
AND salary > (
SELECT salary
FROM employees
WHERE employee_id = 143
);
题目：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);
SELECT MIN(salary),last_name
FROM employees
题目：查询与141号员工的manager_id和department_id
相同的其他员工的employee_id， manager_id，department_id
#方式一
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id=(
SELECT manager_id
FROM employees
WHERE employee_id=141)
AND department_id=(
SELECT department_id
FROM employees
WHERE employee_id=141)
AND employee_id <> 141;
#方式二
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id) = (
SELECT manager_id,department_id
FROM employees
WHERE employee_id=141
)
AND employee_id <> 141;
题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >(
SELECT MIN(salary)
FROM employees
WHERE department_id =50)
题目：显式员工的employee_id,last_name和location。
其中，若员工department_id与location_id为1800 的department_id相同，
则location为’Canada’，其余则为’USA’。
SELECT employee_id,last_name, CASE department_id WHEN(SELECT department_id FROM departments WHERE location_id=1800)
 THEN 'Canada' ELSE 'USA' END "location"
FROM employees
#子查询中的空值问题(内查询为空)
SELECT last_name, job_id FROM employees WHERE job_id = 
(SELECT job_id FROM employees WHERE last_name = 'Haas');
#非法使用子查询
SELECT employee_id, last_name FROM employees WHERE salary 
= (SELECT MIN(salary) FROM employees GROUP BY department_id); 

二. 多行子查询

内查询返回多行
使用多行比较操作符

2.1 多行比较操作符

 2.2 例子 

题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
                     SELECT salary
                      FROM employees
                      WHERE job_id = 'IT_PROG' 
)
题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
                     SELECT salary
                      FROM employees
                      WHERE job_id = 'IT_PROG' 
)
题目：查询平均工资最低的部门id
#mysql中聚合函数不能嵌套使用
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id)

2.3 空值问题 

SELECT last_name

FROM employees

WHERE employee_id NOT IN ( SELECT manager_id FROM employees );

内查询有null值

 三. 相关子查询

每次都会执行一次子查询

题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
# 方式一
SELECT last_name,salary,department_id 
FROM employees e1
WHERE salary > ( 
                    SELECT AVG(salary)
                    FROM employees
                    WHERE department_id = e1.department_id
                );
 # 方式二 在from 中声明子查询(声明个表)
 SELECT last_name,salary,department_id 
 FROM employees e,(
 SELECT department_id,AVG(salary) avg1
 FROM employees
 GROUP BY department_id) t_dep_avg
 WHERE e.department_id = t_dep_avg.department_id
 AND e.salary > t_dep_avg.avg1
 ####
 SELECT last_name,salary,e1.department_id 
 FROM employees e1,
 (SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2 
 WHERE e1.`department_id` = e2.department_id 
 AND e2.dept_avg_sal < e1.`salary`;
 题目：查询员工的id,salary,按照department_name 排序
 SELECT employee_id,salary FROM employees e ORDER BY 
 ( SELECT department_name FROM departments d WHERE e.`department_id` = d.`department_id` );
 题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同
id的员工的employee_id,last_name和其job_id
SELECT e.employee_id, last_name,e.job_id FROM employees e 
WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id); 

结论: 在 select结构中出了group by和limit 中都可以声明子查询 

3.1  EXISTS 与 NOT EXISTS关键字 

题目：查询公司管理者的employee_id，last_name，job_id，department_id信息

#方式一
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id 
FROM employees e1 JOIN employees e2 
WHERE e1.employee_id = e2.manager_id;
# 方式二
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_id
                      FROM employees
                     )
#方式三
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
   SELECT *
   FROM employees e2
   WHERE e1.employee_id = e2.manager_id

)

题目：查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT department_id, department_name 
FROM departments d 
WHERE NOT EXISTS (SELECT 'X' 
                  FROM employees 
                  WHERE department_id = d.department_id);

四. 子查询练习 

#查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (SELECT department_id
                       FROM employees
                       WHERE last_name = 'Zlotkey'
                       )
#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (SELECT AVG(salary)
               FROM employees)
#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary > ALL (SELECT salary
                    FROM employees
                    WHERE job_id='SA_MAN')
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (SELECT department_id
                       FROM employees
                       WHERE last_name LIKE '%u%')
#5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE  department_id IN (SELECT department_id
                        FROM departments
                        WHERE location_id = 1700
                        )
#6.查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees  
WHERE  manager_id IN (SELECT employee_id
FROM employees
WHERE last_name='King'
                      )
#7.查询工资最低的员工信息: last_name, salary
SELECT last_name,salary
FROM employees
WHERE salary <= (SELECT MIN(salary)
                 FROM employees
                 )

#8.查询平均工资最低的部门信息
# 方式一(把平均工资做成一个新表,新表必须起个别名)
SELECT *
FROM departments
WHERE department_id = (SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) = (SELECT MIN(avg_sal)
			FROM (SELECT AVG(salary) avg_sal
			      FROM employees
			      GROUP BY department_id) t_dep_avg))

#方式二
SELECT *
FROM departments
WHERE department_id = (SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) <=  ALL (SELECT AVG(salary)
					      FROM employees
					      GROUP BY department_id
					   ))
#方式三(用limit求出最低平均工资)
SELECT *
FROM departments
WHERE department_id = (SELECT department_id
                       FROM employees
                       GROUP BY department_id
                       HAVING AVG(salary) = (SELECT AVG(salary)
                                             FROM employees
                                             GROUP BY department_id
                                             ORDER BY AVG(salary)
                                             LIMIT 0,1
                                             ))
 #方式四
 SELECT *
 FROM departments d,(SELECT department_id,AVG(salary)
                     FROM employees
                     GROUP BY department_id
                     ORDER BY AVG(salary)
                     LIMIT 0,1) t_dep_avg
 WHERE d.department_id = t_dep_avg.department_id
#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
#方式一
SELECT *
 FROM departments d,(SELECT department_id,AVG(salary)
                     FROM employees
                     GROUP BY department_id
                     ORDER BY AVG(salary)
                     LIMIT 0,1) t_dep_avg
 WHERE d.department_id = t_dep_avg.department_id
#方式二
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal 
FROM departments d 
WHERE department_id = ( 
			SELECT department_id 
			FROM employees 
			GROUP BY department_id 
			HAVING AVG(salary) = ( 
					SELECT MIN(dept_avgsal) 
					FROM (
					SELECT AVG(salary) dept_avgsal 
					FROM employees 
					GROUP BY department_id ) avg_sal ) );
#10.查询平均工资最高的 job 信息(四种写法)
SELECT *
FROM jobs
WHERE  job_id =(SELECT job_id
               FROM employees
               GROUP BY job_id
               HAVING AVG(salary) >= ALL (SELECT AVG(salary)
                                          FROM employees
                                          GROUP BY job_id
                                          ))

#11.查询平均工资高于公司平均工资的部门有哪些?
#方式一
SELECT department_id 
FROM employees 
WHERE department_id IS NOT NULL 
GROUP BY department_id 
HAVING AVG(salary) > ( SELECT AVG(salary) FROM employees );
#方式二
SELECT t_dep_avg.department_id
FROM (SELECT department_id,AVG(salary) 'avg1'
FROM employees
GROUP BY department_id) t_dep_avg
WHERE t_dep_avg.avg1>(SELECT AVG(salary)
        FROM employees)
#12.查询出公司中所有 manager 的详细信息
#方式1： 
SELECT  DISTINCT e1.last_name,e1.salary
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id
#方式二
SELECT employee_id,last_name,salary 
FROM employees 
WHERE employee_id IN ( 
			SELECT DISTINCT manager_id 
			FROM employees );
#方式三
SELECT employee_id, last_name, salary FROM employees e1 
WHERE EXISTS ( 
		SELECT * 
		FROM employees e2 
		WHERE e2.manager_id = e1.employee_id);
#13.各个部门中最高工资中最低的那个部门的最低工资是多少?
SELECT MIN(salary) 
FROM employees 
WHERE department_id = ( 
		SELECT department_id 
		FROM employees 
		GROUP BY department_id 
		HAVING MAX(salary) <= ALL( 
					SELECT MAX(salary) max_sal 
					FROM employees 
					GROUP BY department_id ) );



#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#方式一
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (SELECT manager_id 
FROM employees
WHERE department_id =(SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) = (
					SELECT MAX(avg_sal)
					FROM
						(
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						) t_dep_avg

						)
						)
			      )

 #方式二
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (SELECT manager_id 
FROM employees
WHERE department_id =(SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) >= ALL (
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						) 
						)
			      )     
# 方式三
SELECT *
FROM employees
WHERE employee_id IN (

			SELECT manager_id
			FROM employees e,(SELECT department_id
					FROM employees
					GROUP BY department_id
					ORDER BY AVG(salary)
					LIMIT 0,1
					) t_dep_avg
			WHERE e.department_id = t_dep_avg.department_id
			

)

#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
#方式一:
SELECT department_id
FROM employees
WHERE department_id NOT IN (SELECT DISTINCT department_id
                  FROM employees
                  WHERE job_id='ST_CLERK')
#方式二
SELECT department_id
FROM departments d
WHERE NOT EXISTS (SELECT *
                  FROM employees e   
                  WHERE e.department_id = d.department_id
                  AND  job_id='ST_CLERK')           
#16. 选择所有没有管理者的员工的last_name
SELECT last_name
FROM employees e1
WHERE NOT EXISTS(
                 SELECT *
                 FROM employees mgr
                 WHERE mgr.employee_id = e1.manager_id)
#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
#方式一
SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE manager_id IN (
                     SELECT employee_id
		     FROM employees
		     WHERE last_name='De Haan')
#方式二
SELECT employee_id,last_name,hire_date,salary
FROM employees e1
WHERE EXISTS (
               SELECT *
               FROM employees mgr
               WHERE e1.manager_id = mgr.employee_id
               AND mgr.last_name='De Haan')
#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
SELECT employee_id,last_name,salary
FROM employees e1 
WHERE salary > ( 
                # 查询某员工所在部门的平均 
                SELECT AVG(salary) 
                FROM employees e2 
                WHERE e2.department_id = e1.`department_id` );
#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
SELECT department_name
FROM departments d
WHERE  5 < (
            SELECT COUNT(*)
            FROM employees e
            WHERE d.department_id = e.department_id
            )
#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
SELECT country_id
FROM locations l
WHERE 2 < (
            SELECT COUNT(*)
            FROM departments d
            WHERE d.location_id = l.location_id)

#相关子查询,从外往里写

选择困难症患者emm...
关注
专栏目录
【MySQL】练习五 数据库完整性
兔子王
04-10 865
主要内容 1、三类完整性的定义 2、触发器的作用
SQL查询：如何在where条件中使用子查询
最新发布
sg_knight的专栏
03-28 1674
子查询在 WHERE 条件中的使用能够极大地增强 SQL 查询的灵活性和表达能力，可以满足复杂查询需求。但是在使用子查询时，需要注意查询效率和结果集的大小，避免性能问题和不必要的复杂性。
Mysql8.0习题系列（五）：子查询（一篇搞懂子查询做题技巧，超详细）
阿里云专家博主、数据科学领域优质创作者、统计er在读
03-24 1954
Mysql8.0习题系列 软件下载地址 提取码：7v7u 数据下载地址 提取码：e6p9 文章目录Mysql8.0习题系列子查询1.查询和Zlotkey相同部门的员工姓名和工资2.查询工资比公司平均工资高的员工的员工号，姓名和工资。3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名5.查询在部门的location_id为1700的部门工作的员工的员工号6.查询管理者是Ki
练习5:MySQL数据插入、更新与删除
匿名攻城狮
08-29 3000
案例:创建表books，对数据进行插入、更新和删除操作，掌握数据表的基本操作。books表结构以及表中的记录如下表： 案例操作过程： (1)创建数据表books，并按照表8.1所示的结构定义各个字段。 (2)将表8.2中的记录插入books表中。分别使用不同的方法插入记录。 (3)将小说类型(novel)的书的价格都增加5。 (4)将名称为EmmaT的书的价格改为40，并将note说明改为dram...
MySQL入门之课后思考题第八章
MrSuper王的博客
05-05 1070
下面使用 mysqldump 命令备份 chapter08 数据库的语句中，正确的是（） A、 mysqldump –u root –p itcast chapter08>d:/chapter08.sql B、 mysqldump -uroot -pitcast chapter08>d:/chapter08.sql C、 mysqldump –u root –p itcast chap...
深入浅出mysql第8章(字符集)习题答案
yaoguaiba579567的博客
01-18 451
1、
Mybatis子查询使用
qq_36939013的博客
08-01 3367
查询一年内的达标天数 <select id="getAllDistrictDayAqis" resultMap="AirDayReportMap"> SELECT district_code as "districtCode", aqi, aqi_grade as "aqiGrade", ...
子查询EXISTS和IN的使用
ABCisCOOL的博客
04-03 1495
1.掌握子查询in的使用 2.掌握子查询exists的使用 之前已经知道了运算符IN，它允许我们在WHERE子句中过滤某个字段的 多个值。 where子句使用in语法 select column_name from table_name where column_name in (value1,value2) 如果运算符IN后面的值是来源于某个查询结果，并非是指定的几个值，这时就需要用到子查询。...
clickhouse 子查询_Clickhouse 查询语句之 WITH语句 FROM语句
weixin_39736547的博客
12-19 3627
WITH语句已经成为SQL语句必不可少的部分，Clickhouse目前的版本对CTE(Common Table Expression 公共表达式)的支持比较有限，相关的限制如下：1.Recursive queries are not supported.2.When subquery is used inside WITH section, it’s result should be scalar...
sql的子查询和外部查询以及关联相关问题！
qq_43401942的博客
07-18 1409
sql，关联，数据库
mysql第八章_MySQL_第八章
weixin_39898150的博客
01-19 215
第八章：Dao模式JDBC封装为了提高代码的维护性和扩展性，我们使用JDBC进行封装数据，先定义统一的API，将操作数据的代码抽象到接口中，业务逻辑代码只需要调用这些接口的实现类的对象，就可以实现对数据的访问了，从而隔离实现的细节，采用面向接口编程，可以降低代码间的耦合度，提高代码的扩展性和维护性。DAO(data access Objects) 数据存取对象，位于业务逻辑和持久化之间，实现对...
MySQL 第八章-聚合函数+课后练习
weixin_45865571的博客
04-23 949
#1、常见的几个聚合函数 #1.1 AVG / SUM : 只适用于数值类型的字段 （或变量） SELECT AVG(salary) FROM employees; SELECT SUM(salary) FROM employees; #1.2 MAX / MIN 而这个使用于，数值类型和字符串类型，日期时间类型的字段（或变量） SELECT MAX(salary),MIN(salary) FROM employees; SELECT MAX(last_name),MIN(last_name)
MySQL_第八章
GTS311111111的博客
10-09 293
第八章：Dao模式 JDBC封装 为了提高代码的维护性和扩展性，我们使用JDBC进行封装数据， 先定义统一的API，将操作数据的代码抽象到接口中，业务逻辑代码只需要调用这些接口的实现类的对象，就可以实现对数据的访问了，从而隔离实现的细节，采用面向接口编程，可以降低代码间的耦合度，提高代码的扩展性和维护性。 DAO(data access Objects) 数据存取...
在mysql中为了加快数据表_数据库原理与应用教程 何玉洁 五——八章课后习题部分答案...
weixin_39739234的博客
02-05 1031
第五章一、试说明使用视图的好处。答：1、简化数据查询语句2、使用户能从多角度看待同一数据3、提高了数据的安全性4、提供了一定程度的逻辑独立性二、使用视图可以加快数据的查询速度，这句话对吗？为什么？答：不对。因为数据库管理系统在对视图进行查询时，首先检查要查询的视图是否存在，如果存在，则从数据字典中提取视图的定义，把视图的定义语句对视图语句结合起来，转换成等价的对基本表的查询，然后再执行转换后的查询...
1、查询姓名中包含‘u’字母的员工记录2、同名去重3、字段计算
weixin_43826111的博客
02-23 1510
1、select *from emp where ename like %u%; 2、select distinct ( ename) from emp ; 3、select （comn+sal，0） as money from emp （如果comn+sal等于空就返回零）
MySQL数据库技术 第二版 章末 答案—单元8
qq_40927409的博客
12-20 8810
MySQL数据库技术 第二版 章末 答案—单元8 以下是我个人所尝试过得答案，若有不正确的地方请告知，谢谢！ 由于实在是太多内容了，原谅我省略题目内容 单元8 数据库编程 ---- P166-P168 跳到底部，进行题目筛选 实训8 底部 1、存储过程 -- 创建存储过程 USE yggl; DELIMITER $$ CREATE PROCEDURE eqIncome(IN propler1 CHAR(6),IN propler2 CHAR(6),OUT isHei...
mysql第八章_MySQL必知必会--第五章~第八章
weixin_39612297的博客
02-28 117
第六章--过滤数据1. 在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤， WHERE子句在表名(FROM子句)之后给出例如：select prod_names， prod_id, prod_price from products where prod_price=2.5;WHERE子句的位置 在同时使用ORDERBY和WHERE子句时，应 该让ORDER BY位于WHERE之...
P48 第09章_子查询练习题
weixin_54671087的博客
11-28 2010
第09章_子查询练习题 #1.查询和Zlotkey相同部门的员工姓名和工资 SELECT last_name,salary FROM employees WHERE department_id IN( SELECT department_id FROM employees WHERE last_name='Zlotkey' ) #2.查询工资比公司平均工资高的员工的员工号，姓名和工资。 SELECT employee_id,last_name,salary FROM employee
Mysql——查询sql语句练习
qq_63099085的博客
10-17 1115
select student2.s_id,student2.s_name,r.选课数,r.总成绩 from student2,(select score2.s_id,sum(score2.s_score) as 总成绩,count(score2.s_score) as 选课数 from score2 group by score2.s_id) as r where student2.s_id=r.s_id;
sql子查询使用in
07-29
SQL子查询使用IN关键字可以用于在查询中检查一个值是否存在于另一个查询的结果中。它的语法如下： ```sql SELECT column_name(s) FROM table_name WHERE value IN (SELECT column_name FROM table_name WHERE ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值