组团学

MySQL子查询

阅读 (190195)

在SQL语言中,一个SELECT-FROM-WHERE语句称为一个查询块。当获得一个查询的答案需要多个步骤的操作,首先必须创建一个查询来确定用户不知道但包含在数据库中的值,将一个查询块嵌套在另一个查询块的WHERE字句或HAVING短语的条件中查询块称为子查询或内层查询。上层的查询块曾为父查询或外层查询。子查询的结果作为输入传递回“父查询”或“外部查询”。父查询将这个值结合到计算中,以便确定最后的输出。

一、子查询概述

1.1、什么是子查询

子查询是一种常用计算机语言sql中select语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时,称之为子查询。

如:

查询渠道部有那些员工

#第一步,查询出'渠道部'的id
mysql> select id from dept where name='渠道部';
+----+
| id |
+----+
| 2  |
+----+
1 行于数据集 (0.02 秒)
#第二步,通过查询出的‘渠道部’的id,在查询渠道部的员工信息
mysql> select * from emp where dept_id=2;
+----+--------+--------+--------+------------+---------+
| id | name   | gender | salary | join_date  | dept_id |
+----+--------+--------+--------+------------+---------+
| 2  | 李四 | 男    | 3600   | 2010-12-02 | 2       |
| 3  | 王五 | 男    | 9000   | 2008-08-08 | 2       |
+----+--------+--------+--------+------------+---------+
2 行于数据集 (0.01 秒)

子查询方式

mysql> select * from emp where dept_id=(select id from dept where name='渠道部');
+----+--------+--------+--------+------------+---------+
| id | name   | gender | salary | join_date  | dept_id |
+----+--------+--------+--------+------------+---------+
| 2  | 李四 | 男    | 3600   | 2010-12-02 | 2       |
| 3  | 王五 | 男    | 9000   | 2008-08-08 | 2       |
+----+--------+--------+--------+------------+---------+
2 行于数据集 (0.03 秒)

1.2、子查询特点

一个查询的结果做为另一个查询的条件

有查询的嵌套,内部的查询称为子查询

子查询要使用括号

1.3、子查询结果的三种情况

单行单列

image20200215215222589.png

多行单列

image20200215215408848.png

多行多列

image20200215215431152.png

二、单行单列查询

子查询结果只要是单行单列,肯定在 WHERE 后面作为条件,父查询使用:比较运算符,如:> 、<、<>、=、>=、<=等

格式:
select */字段列表 from 数据库表名 where 字段名=(子查询);

案例:

查询工资最高的员工信息

分析:

先找出最高工资,在查找员工信息

mysql> select max(salary) from emp;
+-------------+
| max(salary) |
+-------------+
| 9000        |
+-------------+
1 行于数据集 (0.01 秒)

mysql> select * from emp where salary=(select max(salary) from emp);
+----+--------+--------+--------+------------+---------+
| id | name   | gender | salary | join_date  | dept_id |
+----+--------+--------+--------+------------+---------+
| 3  | 王五 | 男    | 9000   | 2008-08-08 | 2       |
+----+--------+--------+--------+------------+---------+
1 行于数据集 (0.01 秒)

查询工资小于平均工资的员工信息

分析:

先算出员工的平均工资,在查看小于平均工资的员工信息

mysql> select avg(salary) from emp;
+-------------------+
| avg(salary)       |
+-------------------+
| 5994.333333333333 |
+-------------------+
1 行于数据集 (0.02 秒)

mysql> select * from emp where salary<(select avg(salary) from emp);
+----+--------+--------+--------+------------+---------+
| id | name   | gender | salary | join_date  | dept_id |
+----+--------+--------+--------+------------+---------+
| 2  | 李四 | 男    | 3600   | 2010-12-02 | 2       |
| 4  | 赵六 | 女    | 5000   | 2015-10-07 | 3       |
| 5  | 吴七 | 女    | 4500   | 2011-03-14 | 1       |
+----+--------+--------+--------+------------+---------+
3 行于数据集 (0.03 秒)

三、单行多列查询

子查询结果只要是单行多列,结果集类似于一个数组,父查询使用in、not in运算符

格式:
select */字段列表 from 数据库表名 where 字段名 in (子查询);

案例:

查询工资大于 5000 的员工,来自于哪些部门的名字

分析:

先找出工资大于5000的员工的部门ID,通过部门ID查找对应的部门名字

mysql> select dept_id from emp where salary>5000;
+---------+
| dept_id |
+---------+
| 1       |
| 2       |
+---------+
2 行于数据集 (0.01 秒)

mysql> select * from dept where id in(select dept_id from emp where salary>5000); 
+----+-----------+
| id | name      |
+----+-----------+
| 1  | 研发部 |
| 2  | 渠道部 |
+----+-----------+
2 行于数据集 (0.01 秒)

查询研发部与渠道部所有的员工信息

分析:

先查找研发部与渠道部的id,通过id查找到研发部与渠道部的员工信息

mysql> select id from dept where name='研发部' or name='渠道部';
+----+
| id |
+----+
| 1  |
| 2  |
+----+

mysql> select * from emp where dept_id in(select id from dept where name in('研发部','渠道部'));
+----+--------+--------+--------+------------+---------+
| id | name   | gender | salary | join_date  | dept_id |
+----+--------+--------+--------+------------+---------+
| 1  | 张三 | 男    | 7200   | 2013-02-24 | 1       |
| 2  | 李四 | 男    | 3600   | 2010-12-02 | 2       |
| 3  | 王五 | 男    | 9000   | 2008-08-08 | 2       |
| 5  | 吴七 | 女    | 4500   | 2011-03-14 | 1       |
+----+--------+--------+--------+------------+---------+
4 行于数据集 (0.02 秒)

四、多行多列

子查询结果只要是多行多列,肯定在 FROM 后面作为表,子查询作为表需要取别名,否则这张表没有名称则无法访问表中的字段。

格式:
select */字段列表 from (子查询) [as] 表别名 where 条件表达式;

案例:

查询出 2011 年以后入职的员工信息,包括部门名称

分析:
先找出2011年入职的员工信息组成一个新表,在查询所在的部门信息

mysql> select * from emp where join_date>='2011-01-01';
+----+--------+--------+--------+------------+---------+
| id | name   | gender | salary | join_date  | dept_id |
+----+--------+--------+--------+------------+---------+
| 1  | 张三 | 男    | 7200   | 2013-02-24 | 1       |
| 4  | 赵六 | 女    | 5000   | 2015-10-07 | 3       |
| 5  | 吴七 | 女    | 4500   | 2011-03-14 | 1       |
+----+--------+--------+--------+------------+---------+
3 行于数据集 (0.01 秒)

mysql> select * from dept d,(select * from emp where join_date>='2011-01-01') e where d.id=e.dept_id;
+----+-----------+-------+---------+--------+--------+------------+---------+
| id | name      | id(2) | name(2) | gender | salary | join_date  | dept_id |
+----+-----------+-------+---------+--------+--------+------------+---------+
| 1  | 研发部 | 1     | 张三  | 男    | 7200   | 2013-02-24 | 1       |
| 3  | 教务部 | 4     | 赵六  | 女    | 5000   | 2015-10-07 | 3       |
| 1  | 研发部 | 5     | 吴七  | 女    | 4500   | 2011-03-14 | 1       |
+----+-----------+-------+---------+--------+--------+------------+---------+
3 行于数据集 (0.02 秒)

还可以使用表连接查询

mysql> select * from emp inner join dept on emp.dept_id = dept.id where join_date >='2011-1-1';
+----+--------+--------+--------+------------+---------+-------+-----------+
| id | name   | gender | salary | join_date  | dept_id | id(2) | name(2)   |
+----+--------+--------+--------+------------+---------+-------+-----------+
| 1  | 张三 | 男    | 7200   | 2013-02-24 | 1       | 1     | 研发部 |
| 4  | 赵六 | 女    | 5000   | 2015-10-07 | 3       | 3     | 教务部 |
| 5  | 吴七 | 女    | 4500   | 2011-03-14 | 1       | 1     | 研发部 |
+----+--------+--------+--------+------------+---------+-------+-----------+
需要 登录 才可以提问哦