美文网首页数据库学习
2023-11-11 第09章_子查询

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

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

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

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

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

  1. 子查询中的空值问题 返回空row
    -- SELECT last_name
    -- FROM employees
    -- WHERE employee_id NOT IN (
    -- SELECT manager_id
    -- FROM employees
    -- );
  2. 关键字
    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
)

相关文章

  • 2018-06-04

    第11章 子查询 11.1 子查询 SQL 允许创建子查询（subquery），即嵌套在其他查询中的查询。 11....

  • 《mysql必知必会》读书实战笔记14-子查询

    第14章 使用子查询 14.1子查询 简单查询：查询单个数据表的select查询语句。 子查询：嵌套在其他查询中的...

  • oracle学习笔记六——查询之子查询

    六、子查询 1.子查询导入 --查询工资比SCOTT高的员工信息 --第1步. 得到SCOTT的工资 SQL> s...

  • Oracel_子查询

    SQL子查询 子查询语法 子查询 (内查询) 在主查询之前一次执行完成。 子查询的结果被主查询(外查询)使用 。 ...

  • Oracle | 子查询和伪列

    1. 子查询 （1）单行子查询 （2）多行子查询 1）ANY子查询 2）ALL 子查询 2. 伪列...

  • 《SQL必知必会》第 11 课 使用子查询

    目标： 11.1 子查询 11.2 利用子查询进行过滤 11.3 作为计算字段使用子查询 11.1 子查询 查询（...

  • MySQL 子查询

    什么是子查询 为什么要使用子查询 子查询的分类 怎样使用子查询 关联子查询 要使用的数据表 1. 什么是子查询？ ...

  • 数据库第七天

    子查询 查询里面还有查询注意: 子查询优先于主查询执行 最好子查询用括号 查询比ALLEN工资高的员工信息 单行子...

  • SQL查询_高级查询

    SQL查询_高级查询 一、子查询 子查询出现的位置一般为条件语句，oracle会先执行子查询，再执行父查询，子查询...

  • 17/12/6 子查询

    17/12/6 子查询 单行子查询 括号内的查询叫做子查询，也叫内部查询，先于主查询的执行。 子查询可以嵌入1.w...

网友评论

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

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

    延伸阅读

    深度阅读

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

    栏目导航

    热点阅读

    数据库学习

    关于我们|服务条款|联系我们|2023-11-11 第09章_子查询|投稿指南|网站地图|RSS订阅|排版工具|手机版

    提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

    Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

    备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

    本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

    所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！