MySQL子查询

MySQL子查询

在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、子查询结果的三种情况

单行单列

多行单列

多行多列

二、单行单列查询

子查询结果只要是单行单列，肯定在 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     | 研发部 |
+----+--------+--------+--------+------------+---------+-------+-----------+