MySQL学习笔记11-----子查询

已于 2022-05-05 09:33:17 修改
阅读量1.2k 收藏 1
点赞数
分类专栏： 康师傅MySQL 文章标签： sql
于 2022-05-03 15:38:39 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/pythonofstar/article/details/124542443
版权

目录

1.子查询规范 

2.子查询分类

2.1 单行子查询

2.2 多行子查询

临时表    

2.3 相关子查询

EXISTS 与 NOT EXISTS关键字

子查询可以理解为嵌套

问题：想查出表里工资比 ‘ Abel ’ 工资高的人的姓名和工资

        方法一：先查出Abel 的工资是11000，再查出工资大于11000的人

        方法二：自连接（并不适用于所有表）

        注意，如果对应的数据库写错，会带来错误的效果：

      一定要注意对应，将e表作为一个参考表，所有的数据都是从e1里挑选的，只需要借用一下e表中的Abel ，所以SELECT中的数据都是e1的数据，但是AND后的拣选条件中，是对照表e的条件

        方式三：子查询 

        在方式一的基础上，把第一个查询嵌套到第二个查询的WHERE里

 

↓     ↓     ↓     ↓

         自连接好于子查询

1.子查询规范 

外查询：也称为主查询

内查询：也称为子查询

        1.子查询要包含在一个括号（）内

        2. 子查询通常放在右侧

        3.单行操作符对应单行子查询，多行操作符对应多行子查询

2.子查询分类

1. 按返回结果的条目数分为：单行子查询和多行子查询

        单行子查询：返回的结果是单行的信息，例如上题中的某个人的工资

        多行子查询：返回结果是多行的信息

2.按内查询是否被执行多次：相关子查询和不相关子查询

        不相关子查询： 首先计算内查询的结果，然后遍历外查询，一条条和内查询的结果进行比较

        相关子查询：

举个栗子：相关子查询：查询工资大于本部门平均工资的员工信息

                  不相关子查询：查询工资大于本公司平均工资的员工信息

2.1 单行子查询

        单行比较操作符：在主查询的 WHERE 后可以使用的连接符

操作符含义
=等于
>大于
>=大于等于
<小于
<=小于等于

<>

不等于

        子查询编写及技巧：从里往外写，或从外往里写（废话）

          单行子查询返回的字段一定和主查询里 WHERE 处等着的字段是一样的  ，否则就是非法使用子查询  

        题目1 ：返回 job_id 与141号员工相同，且工资大于143号员工的员工 last_name , job_id , salary

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
)

         题目2 ：返回公司工资最少的员工的 last_name , job_id , salary

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

          题目3 ：查询与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 = 147
)
AND department_id=(
	SELECT department_id
	FROM employees
WHERE employee_id = 147
);
AND employee_id <> 147;-- 其他员工
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 <> 147;-- 其他员工

        第二种方式虽然效率高一点，但是应用场景比较局限 

         题目4 ,HAVING与子查询：查询最低工资大于50号部门最低工资的部门 id 和其最低工资 

USE atguigudb;
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary)>(
					SELECT MIN(salary)
					FROM employees
					WHERE department_id = '50'
					);

         题目5 ,CASE与子查询：查询员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余为’USA’。

USE atguigudb;
SELECT employee_id,last_name,CASE department_id WHEN 20 THEN 'Canada'																						                                        
                                           ELSE 'USA' END "location"
FROM employees

2.2 多行子查询

当内查询返回多于一条的记录，这时候用单行运算符就不合适了

        问题1： 找出员工最低工资与部门最低工资相等的员工

USE atguigudb;
SELECT last_name,job_id,salary
FROM employees 
WHERE salary in(
	        SELECT MIN(salary)
	        FROM employees
            GROUP BY department_id
                );

        问题2： 找出其他 job_id 中比 job_id 为 ‘ IT_PROG ’ 部门任一工资低的员工的信息

SELECT last_name,job_id,salary
FROM employees 
WHERE salary < ANY(
	                SELECT salary
	                FROM employees
	                WHERE job_id = 'IT_PROG'
                  )
AND job_id<>'IT_PROG'

        问题3： 找出其他 job_id 中比 job_id 为 ‘ IT_PROG ’ 部门所有员工工资低的员工的信息

SELECT last_name,job_id,salary
FROM employees 
WHERE salary < ALL(
	                SELECT salary
	                FROM employees
	                WHERE job_id = 'IT_PROG'
                  )
AND job_id<>'IT_PROG'

                把上题 ANY 换成ALL即可 

临时表    

    问题4： 查询平均工资最低的部门 id

        方式一思路：将各个部门的平均工资形成一张临时表，再去查询这个临时表里最低的工资即可。注意，临时表必须有别名

USE atguigudb;
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
                    ) 
        AS t_avg_sal
                        );

         方式二:

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(
                        SELECT AVG( salary)
                        FROM employees
                        GROUP BY department_id
                        );

          以上案例都是不相关子查询          

2.3 相关子查询

        首先从外查询取得某个信息，然后带入内查询，再将内查询结果返回给外查询。只要是内查询里出现外查询的表了，就是相关子查询

        问题一：查询员工中工资大于本部门平均工资的员工信息

                方式一：运用自相关的方法，创建两个表互相引用

                首先过度一下，查询工资低于60号部门平均工资的员工

SELECT last_name,salary,department_id
FROM employees 
WHERE salary>(
            SELECT AVG( salary)
            FROM employees 
            WHERE department_id = 60
            );

                再将两个表赋予不同的别名，用内表引用外表的信息 ，就ok了

SELECT last_name,salary,department_id
FROM employees outer -- 外表
WHERE salary>(
              SELECT AVG( salary)
              FROM employees inner -- 内表
              WHERE department_id = outer.department_id -- 外表的department_id传进来，进行内                                                                                                                                
                                                         -- 查询，然后再传出去
            );

                方式二：在FROM中声明子查询

SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
                SELECT department_id,AVG(salary) avg_sal
                FROM employees
                GROUP BY department_id) "t_avg_sal"
WHERE e.department_id = t_avg_sal.department_id
AND e.salary > t_avg_sal.avg_sal

        问题2(在ORDER BY 中使用子查询)：查询员工信息，按照department_name排序

SELECT employee_id,salary
FROM employees e
ORDER BY(
	    SELECT department_name
	    FROM departments d
	    WHERE e.department_id = d.department_id
        ) ASC;

        结论：在 SELECT , FROM , WHERE , HAVING , ORDER BY 中都可以使用子查询

GROUP BY 和 LIMIT 处不可以写子查询 

        问题3：若 employees 表中 employee_id 与 job_history 表中 employee_id 相同的数目不小于2(调过岗)，输出这些相同 id 的员工的 employee_id,last_name和其 job_id

SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (
	        SELECT COUNT(*)
	        FROM job_history j
	        WHERE e.employee_id = j.employee_id
           );

EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
        如果在子查询中不存在满足条件的行：
                                                                条件返回 FALSE
                                                                继续在子查询中查找
        如果在子查询中存在满足条件的行：
                                                                不在子查询中继续查找
                                                                条件返回 TRUE
NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

可以用 WHERE IN 的都可以换成 EXIST

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

        方式一：自连接

SELECT mgr.employee_id,mgr.last_name,mgr.job_id
DISTINCT FROM employees emp JOIN employees mgr -- 加个DISTINCT，去重
ON emp.manager_id = mgr.employee_id

        方式二：子查询

SELECT employee_id,last_name,job_id
FROM employees
WHERE employee_id IN(
                    SELECT DISTINCT manager_id
                    FROM employees
                    );

        方式三：使用 EXISTS

SELECT employee_id,last_name,job_id
FROM employees e1
WHERE EXISTS(
            SELECT *  -- 这里写什么都可以，返回的只是这条记录
            FROM employees e2
            WHERE e1.employee_id = e2.manager_id
            );

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

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

MuteuooW
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql子查询外部查什么 内部查什么,MySQL子查询
weixin_26643379的博客
03-23 904
MySQL子查询MySQL中的子查询是一个查询，它嵌套在另一个SQL查询中，并与各种运算符一起嵌入SELECT，INSERT，UPDATE或DELETE语句。我们还可以将子查询与另一个子查询嵌套在一起。子查询称为内部查询，而包含子查询的查询称为外部查询。首先执行内部查询，将结果提供给外部查询，然后执行主/外部查询。 MySQL允许我们在任何地方使用子查询，但是必须在括号内将其关闭。 MySQL也将...
Mysql内外多表连接查询和子查询要点及案例
王大兴的王兴的博客
07-30 679
内连接 内连接：对于查询结果的要求：只有两个表中能够匹配到数据，才会做显示。 通过on关键字进行表与表之间的关联 select 表名1.列名1,表名1.列名2,表名2.列名1,表名2.列名2… from 表名1 inner join 表名2 on 表名1.列名=表名2.列名; 三表及其更多表连接 ：通过on关键字进行表与表之间的关联 select 表名1.列名1,表名1.列名2,表名2.列名1,表名2.列名2…表名3.列名3… from 表名1 inner join 表名2 on 表名1.列
普通查询、排序与分页查询、分组查询、子查询(内查询)、连接之 LEFT JOIN、RIGHT JION、INNER JOIN
weixin_43809550的博客
09-18 820
题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id。题目：查询与141号或174号员工的 manager_id 和 department_id 相同的其他员工的employee_id， manager_id，department_id。题目：查询公司管理者的employee_id，last_name，job_id，department_id信息。
MySQL --- 子查询 - 子查询概念、规范、分类
weixin_60569662的博客
07-22 644
在特定情况下，一个查询语句的条件需要另一个查询语句来获取，内层查询（innerquery）语句的查询结果，可以为外层查询（outerquery）语句提供查询条件。子查询可以返回一个标量(就一个值)、一个行、一个列或一个表，这些子查询分别称之为标量、行、列和表子查询。子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL4.1开始引入。子句中此时子查询返回的结果图一般都是多行多列，可以按照一张数据表(临时表)的形式操作。内层查询即子查询，外层查询即主查询，只是叫法不同而已。......
MySQL基础——多表查询练习题
最新发布
m0_73703409的博客
01-15 2751
1.查询员工的姓名、年龄、部门信息（隐式内连接）2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显示内连接）3.查询拥有员工的部门id、部门名称4.查询所有年龄大于40岁的员工，及其归属的部门名称；如果没有分配部门，也需要展示出来5.查询所有员工的工资等级6.查询‘研发部’所有员工的信息及工资等级(两种写法)7.查询‘研发部’员工的平均薪资8.查询工资比‘灭绝’高的员工信息9.查询比平均薪资高的员工信息10.查询低于本部门平均工资的员工信息。
MySQL多表查询练习
qq_32792547的博客
09-18 1698
关于表的创建，添加外键，多表查询的案例练习
MySQL之子查询经典案例
hcyxsh的博客
02-26 1157
MySQL之子查询经典案例 此博客包含所有类型的子查询，看完后你将可以掌握各种类型的子查询，需要数据库文件的请关注博主，私信博主获取 1、 查询工资最低的员工信息: last_name, salary #①查询最低的工资 SELECT MIN(salary) FROM employees #②查询last_name,salary，要求salary=① SELECT last_name,salary FROM employees WHERE salary=( SELECT MIN(salary) F
Oracle之SQL子查询
Mate小花粉的博客
08-28 1071
## 子查询 ##单行子查询：返回一行一列数据 多行子查询：返回多行单列数据 多列子查询：返回多列数据**单行子查询** 只返回一行一列数据的子查询被称为单行子查询。单行子查询语句可以使用在主句的where字句中、having 子句中和from子句中1、where子句中是使用子查询 在teachers表中，查询工资低于平均工资的所有教师。 在students表中，查询与王天仪同专业的所
数据库查询之（Ⅳ）子查询与合并查询
webdev
11-07 322
（以下例子操作的表都在如下表中） 嵌套查询：在Where子句中包含一个形如Select-From-Where的查询块，次查询块成为子查询或嵌套查询 1. 返回一个值的子查询（使用比较运算符（=，&gt;,&lt;,&gt;=,&lt;=,!=）） 例：查询与“刘伟”老师职称相同的教师号、姓名。 Select TNo,TN from T where prof=(select pro...
mysql 查询优化--（分组，函数的应用）例子查找学生-课程成绩低于课程平均分的学生信息
searlas的博客
08-23 9497
之前上课感觉group by 和 一张表变成两张表使用（类似 from user_class as uc1,user_class as uc2）没怎么学好，特此实验一番 user 表（用户即学生） class表 课程表 user_class表 用户选课表 查询目的 课程成绩低于课程平均分的学生信息。 第一次尝试，实现简单效果 mysql&amp;amp;gt; select uc.* fr...
练习及总结
weixin_64233616的博客
07-05 1221
1.查询员工姓名，年龄，职位，部门（隐式内连接） select e.name,e.age,e.job,d.name from emp e ,dept d where e.deot_id=d.id; 2.查询年龄小于30的员工姓名，年龄，职位，部门（显式内连接） select e.name,e.age,e.job,d.name from emp e inner join dept d on e.deot_id=d.id where e.age<30;-- on后面是连接条件 where后面是查询条件，
MySQL--多表查询
m0_49128301的博客
02-16 549
1、多表关系 项目开发中，在进行数据库表结构设计，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表之间也存在着各种联系，基本上分为三种： 一对多（多对一） 多对多 用可视化界面来展示多对多关联表的关系 create table student( id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名', ...
Oracle查询各个部门工资小于各个部门平均工资的员工
十年饮冰，难凉热血
07-12 8252
时时想着 SELECT A.* FROM emp A, ( SELECT deptno, AVG(sal) t --部门平均工资 FROM emp GROUP BY deptno ) B --B表分组后得到的视图 WHERE A.deptno=B.deptno --将
05. 基础篇 - 多表查询
蓝胖子 (*^_^*)的博客
02-24 1234
多表关系 一对多（多对一） 多对多 一对一 多表查询概述 内连接 -- 查询每一个员工的姓名，及关联部门名称(隐式内连接) -- 表结构：name，dept -- 连接条件：emp.dept_id = dept_id select emp.name ,dept.name from emp, dept where emp.dept_id = dept_id; -- 查询每一个员工的姓名，及关联部门名称(显式内连接) -- 表结构：
列出各个部门中工资高于本部门的平均工资的员工数和部门号
qq_41040268的博客
09-08 5605
列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序。用SQL语句详细介绍一下 采纳率：51%11级2013.11.17 select a.deptno,count(*) from emp as a, (select deptno,avg(sal) as avgsal from emp group by deptno) as b where a.deptno=b.deptno...
查询出部门名称、部门的员工数、部门的平均工资、部门的最低收入雇员姓名和最高收入雇员的姓名
风泊月
11-04 4596
select deptno,sal,ename from emp where sal||deptno in (select min(sal)||deptno from emp group by deptno) ; select d.deptno,d.dname, t.avg_sal, t.min_sal, e.ename 最低工资, t.max_sal, s.enam...
子查询与内连接查询应用实例
CaiXinXing的CSDN博客
10-20 360
子查询 select bep_si_id,bep_emp_id,bep_points,sum(bep_points) as bep_points_sum,bep_state, (select be.be_photo from t_base_employee be where be.be_id=bep.bep_emp_id ) as be_photo, (select si_name from t_base_storeinfo bs where bs.si_id=bep.bep_.
MySQL学习笔记：基础与进阶操作
"这篇学习笔记涵盖了MySQL的基础、进阶和运维知识，包括各种SQL语句的使用，如DDL、DML、DQL和DCL，以及数据库和表的操作。" 在MySQL中，数据库管理和操作是核心部分，主要包括数据定义语言（DDL）、数据操作语言...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值