Mysql子查询

最新推荐文章于 2023-06-08 16:29:19 发布
最新推荐文章于 2023-06-08 16:29:19 发布
阅读量348 收藏 1
点赞数
分类专栏： Mysql笔记 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44957186/article/details/114268355
版权

一、用到的三个表

员工表：
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
部门表：
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
薪资等级表：
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

二、子查询

嵌套在其他查查询中的查询叫做子查询。

问题描述：

假设现在我们要查询所有在 NEW YORK 和 CHICAGO工作的员工，但是

员工的工作地址信息存储在部门表中，员工具体姓名存在员工表中。

1、先查找 NEW YORK 和 CHICAGO工作的员工的部门编号

mysql> select deptno from dept where loc in ('NEW YORK','CHICAGO');
+--------+
| deptno |
+--------+
|     10 |
|     30 |
+--------+
2 rows in set (0.00 sec)

2、然后查询部门编号为10和30的员工

mysql> select ename ,deptno from emp where deptno in(10,30);
+--------+--------+
| ename  | deptno |
+--------+--------+
| ALLEN  |     30 |
| WARD   |     30 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| KING   |     10 |
| TURNER |     30 |
| JAMES  |     30 |
| MILLER |     10 |
+--------+--------+
9 rows in set (0.00 sec)

3、使用子查询合并两个查询语句

mysql> select ename ,deptno from emp where deptno in (select deptno from dept where loc in ('NEW YORK','CHICAGO'));
+--------+--------+
| ename  | deptno |
+--------+--------+
| ALLEN  |     30 |
| WARD   |     30 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| KING   |     10 |
| TURNER |     30 |
| JAMES  |     30 |
| MILLER |     10 |
+--------+--------+
9 rows in set (0.00 sec)

分析：

select 语句的执行是从内朝外的，上面的SQL语句其实执行两个操作，先部门表中找到了 new york 和 chicago 员工的部门编号，查询到的结果以IN操作符要求的逗号分隔的形式传给了外部查询的where子句，然后外部查询在正常执行，所以我们看到两种方案查询到的结果是相同的。

注意事项

  • 在where子句中使用子查询，应该保证select语句中有于where子句中相同数目的列。通常子查询将会返回单个列然后于单个列进行匹配，如果有需要也可以使用多个列。
  • 嵌套过多的子查询可能会严重影响查询的性能

三、格式化SQL

为了是包含子查询的SELECT语句阅读和调试更容易，把子查询做适当的分解多行和缩进可以简化子查询的使用。

示例：

mysql> select ename ,deptno 
	   from emp 
	   where deptno in (select deptno 
	   					from dept 
	   					where loc in ('NEW YORK','CHICAGO'));
+--------+--------+
| ename  | deptno |
+--------+--------+
| ALLEN  |     30 |
| WARD   |     30 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| KING   |     10 |
| TURNER |     30 |
| JAMES  |     30 |
| MILLER |     10 |
+--------+--------+
9 rows in set (0.00 sec)

四、作为计算字段使用子查询

问题：

统计员工表中每个部门的人数。

  1. 先检索部门列表
  2. 对于每个部门，统计其在员工表中出现的次数

例如：我们统计部门号为30的人数

mysql> select count(*) from emp where deptno = 30;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

为了将每个部门执行count(*)计算，应该将count(*)作为一个子查询。

mysql> select deptno,dname ,(select count(*) from emp where deptno = dept.deptno) as '人数' 
	   from dept 
	   order by (select count(*) 
	             from emp 
	             where deptno = dept.deptno) desc;
+--------+------------+--------+
| deptno | dname      | 人数   |
+--------+------------+--------+
|     30 | SALES      |      6 |
|     20 | RESEARCH   |      5 |
|     10 | ACCOUNTING |      3 |
|     40 | OPERATIONS |      0 |
+--------+------------+--------+
4 rows in set (0.00 sec)

分析：

上面的select语句对于每个部门都返回三列，deptno，dname和人数。人数是一个可计算的字段，它是由圆括号括起来的子查询建立的，这个子查询对检索出爱的灭个部门号都执行一次，这个例子中被执行了四次，因为一共就只有四个部门，每次检索到一个部门，他都会去员工表中统计一次人数。

五、相关子查询

涉及外部查询的子查询叫做相关子查询

当列名存在多义性例如上面的emp.deptno = dept.deptno,这时候就必须使用完全限定名（表明.列名）

六、用法总结

需要预先直到一个数据或者一堆数据作为某些查询的过滤条件或者总结字段时候我们先使用一个子查询

1、需要一个特定的值，但是这个值不能直接得到，需要进一步查询，子查询结果返回单行单列。

例如：查询所有薪资比MILLER高的员工

mysql> select ename,sal from emp where sal > all(select sal from emp where  ename
= 'miller');
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| FORD   | 3000.00 |
+--------+---------+
8 rows in set (0.00 sec)

2、需要一堆数据，这堆数据都被作为过滤的条件，子查询结果返回单行多列

mysql> select ename ,deptno from emp where deptno in (select deptno from dept where loc in ('NEW YORK','CHICAGO'));
+--------+--------+
| ename  | deptno |
+--------+--------+
| ALLEN  |     30 |
| WARD   |     30 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| KING   |     10 |
| TURNER |     30 |
| JAMES  |     30 |
| MILLER |     10 |
+--------+--------+
9 rows in set (0.00 sec)

3、需要多对信息（多列多行）作为过滤条件，子查询结果返回多列多行

mysql> select ename , sal , job ,deptno 
       from emp 
       where (deptno , sal ) in (select deptno , max(sal) 
                                 from emp 
                                 group by deptno);
+-------+---------+-----------+--------+
| ename | sal     | job       | deptno |
+-------+---------+-----------+--------+
| BLAKE | 2850.00 | MANAGER   |     30 |
| SCOTT | 3000.00 | ANALYST   |     20 |
| KING  | 5000.00 | PRESIDENT |     10 |
| FORD  | 3000.00 | ANALYST   |     20 |
+-------+---------+-----------+--------+
4 rows in set (0.00 sec)
Erase Me
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL子查询
01-08
多表关联或者表内字段关联时，或做相似功能判断时，往往会使用子查询来解决相应问题 1. 无关子查询: 内查询没有用到外查询的列,而且内查询可以单独运行. 2. 相关子查询: 内查询使用了外查询的列,而且内查询不能单独...
“1.创建一个视图，通过该视图可以查询到工作在NEW YORK和CHICAGO的员工编号，姓名，部门编号，入职日期。2.创建一个视图，通过该视图可以查询到每个部门的部门名称及最低工资。3.通过如上视图
酸奶公园
08-24 158
1.创建一个视图，通过该视图可以查询到工作在NEW YORK和CHICAGO的员工编号，姓名，部门编号，入职日期。2.创建一个视图，通过该视图可以查询到每个部门的部门名称及最低工资。3.通过如上视图，查询最低工资大于900的，部门部门名称及最低工资.”
MySQL数据库基础知识点
weixin_44813876的博客
06-08 1652
select u.name,m.name from t_user u join u_r ur on u.id=ur.uid join r_m rm on rm.rid=ur.rid join t_module m on m.id=rm.mid where m.name='男发帖';select s.name,t.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid where s.name='高圆圆';
MySQL查询select实例 【笔记】
Allen . Liu
08-28 636
use mydb; select * from EMP; select * from DEPT; select DISTINCT JOB from EMP; -- distinct 去除重复项 select MGR from EMP; select MGR as 主管编号 from EMP; -- 辅助查询，每列信息 起别名 as select EMPNO as 员工编号,J...
MySQL嵌套查询（子查询） 查询4
weixin_45736361的博客
04-08 957
嵌套查询（子查询） 一、不相关嵌套查询 1.查询工资比 Jones高的员工信息 SELECT *FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='Jones') 2.查询和雇员7369从事相同工作并且薪水大于雇员7876的雇员的姓名和工作 SELECT ename,job FROM emp WHERE job=(SELECT job FROM emp WHERE empno='7369')AND sal>(SELECT sal FROM
题库
概念革命师的博客
08-28 809
题库 在芝加哥工作的人中，谁的工资最高查询每个部门下有多少员工查询除去 salesman 所有平均工资超过 1500 的部门查询在 new york 工作的所有员工的姓名，部门名称和工资信息查询姓名为 King 的员工的编号，名称跟部门查询各种工作的最低工资查询工龄大于10年的所有员工信息查询每个部门员工数量，平均工资和平均工作年限统计各部门每个工种的人数，平均工资。查询从事同一种工作但
25.6 MySQL 子查询
最新发布
03-19
配套资源
MySQL子查询操作实例详解
09-09
主要介绍了MySQL子查询操作,结合实例形式较为详细的分析了mysql表的创建、常见子查询运算与关键字使用技巧,需要的朋友可以参考下
MySQL 子查询和分组查询
01-21
子查询是SQL查询中的重要一块，是我们基于多表之间进行数据聚合和判断的一种手段，使得我们的处理复杂数据更加的便捷，这一节我们主要来了解一下子查询。 先做一下数据准备，这边建立三张表：班级、学生、毕业成绩表...
实例详解mysql子查询
09-08
主要介绍了mysql子查询的相关资料，帮助大家更好的理解和使用MySQL数据库，感兴趣的朋友可以了解下
my基础四：高级查询练习
Struggle233的博客
04-06 1648
各个关键字的顺序： select ..... from .... where .... group by .... having .... order by ..... limit ....; ①groupby：分组查询 having：聚合函数的条件写在having后面 练习： emp:EMPNO| ENAME| JOB| MGR| HIREdate| SAL| COMM |...
Oracle作业5——多表查询、子查询
weixin_30706691的博客
10-19 740
一、基础练习： 1.查询和scott相同部门的员工姓名ename和雇用日期hiredate SELECT ENAME,HIREDATE FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SCOTT'); 2.查询在部门的loc为NEW YORK的部门工作的员工的员工姓名ename，部门名称dname和...
django数据库模型类和查询
qq_42956206的博客
11-29 214
from django.db import models # Create your models here. class Department(models.Model): name = models.CharField(max_length=20) create_date = models.DateTimeField() is_delete = models.BooleanField(default=False) class Meta: # 自定义表名，如果
MySQL查询
weixin_45056660的博客
03-23 4318
本文主要使用三个示例表：员工表（emp）、部门表（dept）和职位表（job）。 部门表（dept），包含部门编号（id）和部门名称（name）字段，主键为部门编号。 职位表（job），包含职位编号（id）和职位名称（title）字段，主键为职位编号。 员工表（emp），包含员工编号（id）、员工姓名（name）、性别（sex）、年龄（age）、入职日期（hiredate）、月薪（salary）、奖金（bonus）以及电子邮箱（email）、部门编号（did）、职位编号（jid）、上级领导编号（mid）。主
oracle学习总结-----子查询
小草的博客
01-18 534
子连接：(子查询一般不超过四层) --需求：查询在NEW YORK工作的员工信息(需要用多值连接符in) select * from emp where deptno in (select deptno from dept where loc='NEW YORK'); 多列子查询：  ---需求：如何查询与smith的部门与岗位相同的所有雇员。    select
第六章 分组函数 第十六章 序列课后作业
qq_43543543的博客
01-11 921
1.查询部门20的员工，每个月的工资总和及平均工资。 select deptno,sum(sal),avg(sal) from emp group by deptno having deptno=20 2.查询工作在CHICAGO的员工人数，最高工资及最低工资。 select count(*),max(sal),min(sal) from emp e,dept d where e.deptno=...
Mysql 多表连接查询
weixin_45605450的博客
05-27 429
Mysql 多表连接查询 多表连接类型 • 按连接条件分： • 等值连接 • 非等值连接 • 按其他连接方法分 • 外连接 • 内连接3.1.4 多表连接写法 • 连接：是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。 • 语法为： SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; 在 WHERE子句中书写连接条件。 如
数据库学习总结（七）——多表查询练习题（2）
热门推荐
qq_43501509的博客
01-08 1万+
这一篇的博客是基于scott.sql数据库操作的，备份文件上传在我的资源里 下载后导入数据库方法同我的上篇博客数据库学习总结（六） scott.sql内包含emp dept salgrde 三个表 雇员表：记录了一个雇员的基本信息 EMP（雇员表） NO 字段 类型 描述 1 EMPNO NUMBER(4) 雇 2 ENAME VARCHAR2(10) 表示雇员姓名 3 J...
第5章 多表连接 练习3 、4、课后作业（部分）
qq_39741000的博客
11-17 443
-- 查询所有工作在new york和chicago的员工姓名，员工编号，以及他们的经理姓名，经理编号。 -- 在上一题基础上，添加没有经理的员工King，并按照员工编号排序 SELECT e1.EMPNO,e1.ENAME,e1.MGR,e2.ENAME FROM emp e1 LEFT JOIN emp e2 ON e1.MGR = e2.EMPNO INNER JOIN dept d ON e1.DEPTNO = d.DEPTNO WHERE d.LOC IN ('NEW YORK','CHICAG.
MySQL 子查询
06-02
MySQL 子查询是嵌套在其他查询语句中的查询语句，主要用于在外部查询中使用内部查询的结果。它可以在 SELECT、INSERT、UPDATE、DELETE 和 WHERE 语句中使用。 例如，以下是一个使用子查询的 SELECT 语句： ``` ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值