2023-11-11 第09章_子查询

作者: 大也 | 来源:发表于2023-11-13 09:06 被阅读0次

1.子查询指一个查询语句嵌套在另一个查询语句内部的查询以上最好不能超过3个selected 不过现实学习阶段先这样
2.分类单行子查询、多行子查询、相关子查询
3.非法使用子查询多个结果集使用 = 号

自连接方式比子查询好 from 中加字查询做表比判断和having 中写判断查询好
5.符号 -- -- = / > / >= / < / <= / <>
6.-- -- !!!! GROUP BY SELECT department_id,job_id 中出现的GROUP BY 一定要出现。反之不一定
7.-- 请全使用 NOT 没有NO 用NOT

8.-- -- ???? 理解一下解题思路 SELECT 里面也能放子查询能放字查询的位置有哪些
9.-- -- 这里为啥不分组前面为啥分组 GROUP BY 使用场景

子查询中的空值问题返回空row
-- SELECT last_name
-- FROM employees
-- WHERE employee_id NOT IN (
-- SELECT manager_id
-- FROM employees
-- );
关键字
IN 等于列表中的任意一个
ANY/SOME 需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL 需要和单行比较操作符一起使用，和子查询返回的所有值比较
EXISTS 与 NOT EXISTS关键字这个不是很熟练多看多练
-- -- 题目:查询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
-- )
12.-- 自链接不能用*
-- SELECT DISTINCT e1.employee_id, e1.last_name, e1.salary
-- FROM employees e1 JOIN employees e2
-- WHERE e1.employee_id = e2.manager_id;
-- SELECT DISTINCT *
-- FROM employees em JOIN employees ma
-- WHERE ma.employee_id = em.manager_id
13.没有例子没练的记录一下
相关更新
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
相关删除
DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

例子链接

-- SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
-- FROM employees;
--
-- SELECT job_id, COUNT(). -- COUNT() COUNT(1) 最好使用这几个 COUNT(employee_id)
-- FROM employees
-- GROUP BY job_id;

-- 1.where子句可否使用组函数进行过滤?
-- NO
-- 2.查询公司员工工资的最大值，最小值，平均值，总和
-- SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
-- FROM employees;
-- 3.查询各job_id的员工工资的最大值，最小值，平均值，总和
-- SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
-- FROM employees
-- GROUP BY job_id
-- 4.选择具有各个job_id的员工人数
-- SELECT job_id,COUNT(1)
-- FROM employees
-- GROUP BY job_id
-- 5.查询员工最高工资和最低工资的差距(DIFFERENCE)
-- SELECT MAX(salary) - MIN(salary) 'DIFFERENCE'
-- FROM employees
-- 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
-- SELECT manager_id, MIN(salary)
-- FROM employees
-- WHERE NOT ISNULL(employees.manager_id)
-- GROUP BY manager_id
-- HAVING MIN(salary) > 6000
-- 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
-- SELECT AVG(salary) ,department_name , location_id,COUNT(employee_id) 'count'
-- FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id
-- GROUP BY department_name , location_id
-- 8.查询每个工种、每个部门的部门名、工种名和最低工资

-- SELECT job_id, department_name, MIN(salary)
-- FROM employees
-- RIGHT JOIN departments ON
-- employees.department_id = departments.department_id
-- GROUP BY department_name,job_id

-- SELECT abel.salary , abel.last_name
-- FROM employees e, employees abel
-- WHERE e.salary < abel.salary
-- AND e.last_name = 'Abel'

/*
SELECT ...... -- 存在聚合函数和子查询（2）
FROM ........ -- 可以是子查询的公式做表也可以是计算出的别名表（1）
(left/right)JOIN ... ON -- 多表连接（1）
WHERE ..... -- 不包含聚合函数的过滤条件（1）
GROUP BY ...... -- 分组（WITH ROLLUP 底部出现总和谨慎和ORDER BY 使用（1）（1）
HAVING ..... -- 聚合函数的过滤条件（1）
ORDER BY ...asc/desc -- 排序（3）
LIMIT..... -- 分页（3）

-- !!!! GROUP BY SELECT department_id,job_id 中出现的GROUP BY 一定要出现。反之不一定

FROM -> ON ->(LEFT/RIGHT)JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT-> ORDER BY -> LIMIT
*/

-- SELECT AVG(salary),department_id,SUM(salary)
-- FROM employees
-- WHERE NOT ISNULL(department_id)
-- GROUP BY department_id;
--
-- SELECT AVG(salary),job_id
-- FROM employees
-- GROUP BY job_id;
-- -- !!!! GROUP BY SELECT department_id,job_id 中出现的GROUP BY 一定要出现。反之不一定
-- SELECT AVG(salary),job_id,department_id
-- FROM employees
-- GROUP BY department_id,job_id;
--
-- SELECT MAX(salary),department_id
-- FROM employees
-- WHERE department_id IN (10,20,30,40)
-- GROUP BY department_id
-- HAVING MAX(salary) > 10000;

-- -- = / > / >= / < / <= / <>
-- SELECT salary ,last_name
-- FROM employees
-- WHERE salary > (
-- SELECT salary
-- FROM employees
-- WHERE last_name = 'Abel'
-- );
-- -- 自连
-- SELECT e.last_name,e.salary
-- FROM employees e JOIN employees abel
-- ON abel.last_name = 'Abel'
-- WHERE e.salary > abel.salary
--
-- -- 查询工资大于149号员工工资的员工的信息
--
-- SELECT e.salary
-- FROM employees e
-- WHERE e.salary >(
-- SELECT ev.salary
-- FROM employees ev
-- WHERE ev.employee_id = '149'
-- )
-- -- 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
--
-- SELECT last_name,job_id,salary
-- FROM employees ev
-- WHERE ev.job_id = (
--
-- SELECT job_id
-- FROM employees e
-- WHERE e.employee_id = '141'
--
-- ) AND ev.salary > (
-- SELECT salary
-- FROM employees e
-- WHERE e.employee_id = '143'
-- )
--
-- -- 返回公司工资最少的员工的last_name,job_id和salary
-- SELECT last_name,job_id,salary
-- FROM employees
-- WHERE salary <= (
-- SELECT MIN(salary)
-- FROM employees
-- )
--
-- -- 题目:查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id， manager_id，department_id
-- SELECT employee_id , manager_id , department_id
-- FROM employees
-- WHERE employees.manager_id IN (
-- SELECT manager_id
-- FROM employees e
-- WHERE e.employee_id IN ('141','174')
-- ) AND employees.department_id IN (
-- SELECT department_id
-- FROM employees e
-- WHERE e.employee_id IN ('141','174')
-- )
--
-- -- 题目:查询最低工资大于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'
-- )

-- -- ???? 理解一下解题思路 SELECT 里面也能放子查询
-- -- 题目:显式员工的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
-- 子查询不返回任何行. 返回 null
-- 多行子查询使用单行比较符 = 号使用在了返回多个结果的子查询中

-- IN / ANY / ALL / SOME
-- -- ???? 理解一下解题思路 SELECT 里面也能放子查询
-- 题目:返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
-- SELECT employee_id , last_name , job_id ,salary
-- FROM employees
-- WHERE salary < ANY (
-- SELECT salary
-- FROM employees
-- WHERE job_id = 'IT_PROG'
-- )
-- -- 题目:查询平均工资最低的部门id -> 查询表中所有部门中平均工资最低的部门的部门 id
-- SELECT department_id
-- FROM employees
-- GROUP BY department_id
-- HAVING AVG(salary) <= (
-- SELECT MIN(ta_em_de_avg.em_de_avg)
-- FROM (
-- SELECT AVG(salary) em_de_avg
-- FROM employees
-- GROUP BY department_id
-- ) ta_em_de_avg
-- )
--
-- SELECT department_id
-- FROM employees
-- GROUP BY department_id
-- HAVING AVG(salary) <= ALL (
-- SELECT AVG(salary)
-- FROM employees
-- GROUP BY department_id
-- )

-- -- 3.3 空值问题？
-- -- 这里为啥不分组前面为啥分组
-- -- 题目:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
-- SELECT last_name,salary,department_id
-- FROM employees em
-- WHERE salary > (
-- SELECT AVG(salary)
-- FROM employees
-- WHERE department_id = em.department_id
-- )
--
-- SELECT last_name,salary,em.department_id
-- FROM employees em ,(
-- SELECT department_id,AVG(salary) dept_avg_sal
-- FROM employees
-- GROUP BY department_id
-- ) e
-- WHERE em.department_id = e.department_id
-- AND em.salary > e.dept_avg_sal
--
-- -- 题目:查询员工的id,salary,按照department_name 排序
-- SELECT employee_id ,salary
-- FROM employees
-- ORDER BY (
-- SELECT department_name
-- FROM departments
-- WHERE employees.department_id = departments.department_id
-- )
--
-- -- 题目:若employees表中employee_id与job_history表中employee_id相同的数目不小于
-- -- 2，输出这些相同 id的员工的employee_id,last_name和其job_id
--
-- SELECT employees.employee_id,employees.salary,employees.job_id
-- FROM employees
-- WHERE 2 <= (
-- SELECT COUNT(1)
-- FROM job_history
-- WHERE employee_id = employees.employee_id
-- )

-- -- 4.3 EXISTS 与 NOT EXISTS关键字
-- -- 题目:查询公司管理者的employee_id，last_name，job_id，department_id信息
-- SELECT DISTINCT ee.employee_id,ee.last_name,ee.job_id,ee.department_id
-- FROM employees ee,employees em
-- WHERE ee.employee_id = em.manager_id
--
-- SELECT ee.employee_id,ee.last_name,ee.job_id,ee.department_id
-- FROM employees ee
-- WHERE employees IN (
-- SELECT DISTINCT manager_id
-- FROM employees
-- )
--
-- -- 题目:查询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
-- )
--

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

-- 题目:在employees中增加一个department_name字段，数据为员工对应的部门名称

-- 题目:删除表employees中，其与emp_history表皆有的数据

1.查询和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'
)

-- ??? LIKE

4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

-- 和姓名中包含字母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 e.employee_id
FROM employees e JOIN
departments d ON e.department_id = d.department_id
WHERE d.location_id = '1700'

6.查询管理者是King的员工姓名和工资

SELECT m.last_name , m.salary
FROM employees e JOIN employees m
ON e.employee_id = m.manager_id
WHERE m.last_name = 'King'

7.查询工资最低的员工信息: last_name, salary

SELECT last_name,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
)

8.查询平均工资最低的部门信息

SELECT *
FROM departments ,(SELECT AVG(salary) tab_avg_part_avg,department_id
FROM employees
GROUP BY department_id
ORDER BY tab_avg_part_avg ASC
LIMIT 0,1
) tab_avg_part
WHERE departments.department_id = tab_avg_part.department_id

SELECT *
FROM departments
WHERE departments.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = ( SELECT AVG(salary) tab_avg_part_avg
FROM employees
GROUP BY department_id
ORDER BY tab_avg_part_avg ASC
LIMIT 0,1
)
)

SELECT *
FROM departments
WHERE departments.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary)
FROM employees
GROUP BY department_id
)
)

SELECT *
FROM departments
WHERE departments.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avg_part)
FROM (
SELECT AVG(salary) avg_part
FROM employees
GROUP BY department_id
) tab_avg
)
)

9.查询平均工资最低的部门信息和该部门的平均工资(相关子查询)

SELECT *
, (
SELECT AVG(salary) a_avg
FROM employees
GROUP BY department_id
ORDER BY a_avg ASC
LIMIT 0,1
) part_avg
FROM departments
WHERE departments.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT AVG(salary) a_avg
FROM employees
GROUP BY department_id
ORDER BY a_avg ASC
LIMIT 0,1
)
)

10.查询平均工资最高的 job 信息

SELECT *
FROM jobs
WHERE job_id = (
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (
SELECT AVG(salary) job_avg
FROM employees
GROUP BY job_id
ORDER BY job_avg DESC
LIMIT 0,1
)
)

11.查询平均工资高于公司平均工资的部门有哪些?

SELECT *
FROM departments
WHERE departments.department_id IN (
SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employees
)

)

12.查询出公司中所有 manager 的详细信息

SELECT *
FROM employees ew
WHERE ew.employee_id IN (
SELECT DISTINCT manager_id
FROM employees ma
)

-- 自链接不能用*
-- SELECT DISTINCT e1.employee_id, e1.last_name, e1.salary
-- FROM employees e1 JOIN employees e2
-- WHERE e1.employee_id = e2.manager_id;
-- SELECT DISTINCT *
-- FROM employees em JOIN employees ma
-- WHERE ma.employee_id = em.manager_id

SELECT *
FROM employees em
WHERE EXISTS (
SELECT manager_id
FROM employees ma
WHERE em.employee_id = ma.manager_id
)

13.各个部门中最高工资中最低的那个部门的最低工资是多少?

SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) = (
SELECT MAX(salary) part_max_s
FROM employees em
GROUP BY em.department_id
ORDER BY part_max_s ASC
LIMIT 0,1
)
)

SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) <= ALL (
SELECT MAX(salary) part_max_s
FROM employees em
GROUP BY em.department_id
)
)

SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) = (
SELECT MIN(part_max_s)
FROM (
SELECT MAX(salary) part_max_s
FROM employees em
GROUP BY em.department_id
) table_part_max_s

)

14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

-- 最高平均工资部门ID
SELECT *
FROM employees ma
WHERE ma.employee_id = (
-- mana id
SELECT
DISTINCT
manager_id
FROM employees
WHERE manager_id IS NOT NULL
And department_id = (
SELECT
department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
-- 最高平均工资
SELECT AVG(salary) part_avg
FROM employees
GROUP BY department_id
ORDER BY part_avg DESC
LIMIT 0,1
)
)

)

SELECT employee_id,last_name, department_id, email, salary FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees e
GROUP BY department_id
HAVING AVG(salary)>=ALL(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
)

                                                                                )

                                        )

SELECT *
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees e,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal DESC
LIMIT 0,1) dept_avg_sal
WHERE e.department_id = dept_avg_sal.department_id
)

15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

SELECT DISTINCT department_id
FROM employees
WHERE department_id != (
SELECT DISTINCT department_id
FROM employees
WHERE job_id = 'ST_CLERK'
)

SELECT department_id
FROM departments d
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 d.department_id = e.department_id
AND job_id = 'ST_CLERK'
);

16. 选择所有没有管理者的员工的last_name

SELECT last_name
FROM employees
WHERE ISNULL(manager_id)

SELECT last_name
FROM employees e1
WHERE NOT EXISTS (
SELECT *
FROM employees e2
WHERE e1.manager_id = e2.employee_id
);

查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'

SHOW DATABASES
SHOW TABLES
DESC employees
DESC atguigudb

SELECT *
FROM employees em
WHERE em.manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name = 'De Haan'
)

18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(难)

-- 本部门平均工资高

SELECT employee_id , last_name, salary
FROM employees em , (
SELECT AVG(salary) part_avg,department_id
FROM employees
GROUP BY department_id
) table_part_avg
WHERE em.department_id = table_part_avg.department_id
AND em.salary > table_part_avg.part_avg

SELECT ee.employee_id , ee.last_name, ee.salary
FROM employees ee
WHERE salary > (
SELECT AVG(salary)
FROM employees em
GROUP BY department_id
HAVING ee.department_id = em.department_id
)

19.查询每个部门下的部门人数大于 5 的部门名称

SELECT department_name
FROM departments
WHERE department_id IN (

                SELECT department_id
                FROM employees
                GROUP BY department_id
                HAVING COUNT(*) > 5

)

20.查询每个国家下的部门个数大于 2 的国家编号

DESC locations
SELECT country_id
FROM locations l
WHERE 2 < (
SELECT COUNT(*)
FROM departments d
WHERE l.location_id = d.location_id
)

网友评论

数据库学习

本文标题：2023-11-11 第09章_子查询

本文链接：https://www.haomeiwen.com/subject/lbwkwdtx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

2023-11-11 第09章_子查询

1.查询和Zlotkey相同部门的员工姓名和工资

2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

5.查询在部门的location_id为1700的部门工作的员工的员工号

6.查询管理者是King的员工姓名和工资

7.查询工资最低的员工信息: last_name, salary

8.查询平均工资最低的部门信息

9.查询平均工资最低的部门信息和该部门的平均工资(相关子查询)

10.查询平均工资最高的 job 信息

11.查询平均工资高于公司平均工资的部门有哪些?

12.查询出公司中所有 manager 的详细信息

13.各个部门中最高工资中最低的那个部门的最低工资是多少?

14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

16. 选择所有没有管理者的员工的last_name

查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'

18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(难)

19.查询每个部门下的部门人数大于 5 的部门名称

20.查询每个国家下的部门个数大于 2 的国家编号

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

数据库学习

2023-11-11 第09章_子查询

1.查询和Zlotkey相同部门的员工姓名和工资

2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

5.查询在部门的location_id为1700的部门工作的员工的员工号

6.查询管理者是King的员工姓名和工资

7.查询工资最低的员工信息: last_name, salary

8.查询平均工资最低的部门信息

9.查询平均工资最低的部门信息和该部门的平均工资(相关子查询)

10.查询平均工资最高的 job 信息

11.查询平均工资高于公司平均工资的部门有哪些?

12.查询出公司中所有 manager 的详细信息

13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?

14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

16. 选择所有没有管理者的员工的last_name

查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'

18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(难)

19.查询每个部门下的部门人数大于 5 的部门名称

20.查询每个国家下的部门个数大于 2 的国家编号

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

数据库学习

13.各个部门中最高工资中最低的那个部门的最低工资是多少?