MySQL基础篇：子查询

概述

在某些情况下，当进行一个查询时，需要的条件或数据要用另一个select语句的结果，这个时候，就要用到**子查询**。

为了给主查询（外部查询）提供数据而首先执行的查询（内部查询）被叫做子查询

一般根据子查询的嵌入位置分为：where型子查询、from型子查询、exists型子查询。

说明：以下相关的演示SQL语句涉及到的表及数据，详见：MySQL基础篇：SELECT几种子句.

where型子查询

where型子查询即把内层sql语句查询的结果作为外层sql查询的条件。

• 子查询要包含在括号内；
• 建议将子查询放在比较条件的右侧；
• 单行操作符对应单行子查询，多行操作符对应多行子查询
  • 单行操作符 ——右边子查询必须返回的是单个值，单行比较运算符（=，>,>=,<=,<,<>）
  • 多行操作符——右边子查询可以返回多行，但必须是单列，ALL、ANY、IN其中，ALL和ANY运算符必须与单行比较运算符（=，>,>=,<=,<,<>）结合使用

IN：等于任何一个

ALL：和子查询返回的所有值比较。

例如：sal>ALL(1,2,3)等价于sal>1&&sal>2&&sal>3，即大于所有。

ANY：和子查询返回的任意一个值比较。

例如，sal> ANY(1,2,3)等价于sal>1 or sal >2 or sal >3 ，即大于任意一个就可以。

EXISTS：判断子查询是否有返回结果（不关心具体行数和内容），如果返回则为true，否则为false。

#子查询
#where型子查询

#查询比“李四”的工资高的员工编号
mysql> select * from salary where basic_salary >(select basic_salary from employee inner join salary on employee.eid=salary.eid where employee.ename='李四');
+-----+--------------+--------------------+
| eid | basic_salary | performance_salary |
+-----+--------------+--------------------+
|   1 | 12000        | 6000               |
|   3 | 11000        | 5500               |
+-----+--------------+--------------------+
2 rows in set
#查询和张三、王五在同一个部门的员工
mysql> select * from employee where dept_id IN(select dept_id from employee where ename='张三' OR ename='王五');
+-----+-------+--------+--------------------+-------+---------+
| eid | ename | gender | card_id            | ‘mid‘ | dept_id |
+-----+-------+--------+--------------------+-------+---------+
|   1 | 张三  | 男     | 123456789012345678 | NULL  |       1 |
|   4 | 王五  | 男     | 123456789012115678 |     1 |       1 |
|   5 | 谷雨  | 男     | 123456789012115978 |     1 |       1 |
+-----+-------+--------+--------------------+-------+---------+
3 rows in set

mysql> select * from employee where dept_id = ANY(select dept_id from employee where ename='张三' OR ename='王五');
+-----+-------+--------+--------------------+-------+---------+
| eid | ename | gender | card_id            | ‘mid‘ | dept_id |
+-----+-------+--------+--------------------+-------+---------+
|   1 | 张三  | 男     | 123456789012345678 | NULL  |       1 |
|   4 | 王五  | 男     | 123456789012115678 |     1 |       1 |
|   5 | 谷雨  | 男     | 123456789012115978 |     1 |       1 |
+-----+-------+--------+--------------------+-------+---------+
3 rows in set
#查询全公司工资最高的员工编号、基本工资
mysql> select eid,basic_salary from salary where basic_salary =(select max(basic_salary) from salary);
+-----+--------------+
| eid | basic_salary |
+-----+--------------+
|   1 | 12000        |
+-----+--------------+
1 row in set

mysql> select eid,basic_salary from salary where basic_salary >= ALL(select basic_salary from salary);
+-----+--------------+
| eid | basic_salary |
+-----+--------------+
|   1 | 12000        |
+-----+--------------+
1 row in set

from型子查询

from型子查询即把内层sql语句查询的结果作为临时表供外层sql语句再次查询使用。

# from型
#找出比部门平均工资高的员工编号、基本工资
mysql> select employee.eid,basic_salary 
    -> from salary inner join employee inner join(
    -> select emp.dept_id as did,avg(s.basic_salary) as avg_salary
    -> from employee emp,salary s
    -> where emp.eid=s.eid
    -> group by emp.dept_id) as temp
    -> on salary.eid=employee.eid and employee.dept_id=temp.did
    -> where salary.basic_salary > temp.avg_salary;
+-----+--------------+
| eid | basic_salary |
+-----+--------------+
|   1 | 12000        |
|   3 | 11000        |
+-----+--------------+
2 rows in set

EXISTS型子查询

mysql> select * from depart
ment;
+-----+--------+--------------+------------+
| did | dname  | description  | manager_id |
+-----+--------+--------------+------------+
|   1 | 研发部 | 业务平台研发 | NULL       |
|   2 | 市场部 | 市场推广     | NULL       |
|   3 | 财务部 | 财务管理     | NULL       |
+-----+--------+--------------+------------+
3 rows in set

mysql> 
mysql> 
#exists型
#查询部门信息，该部门必须有员工
mysql> select * from department 
    -> where exists (select * from employee where employee.dept_id=department.did);
+-----+--------+--------------+------------+
| did | dname  | description  | manager_id |
+-----+--------+--------------+------------+
|   1 | 研发部 | 业务平台研发 | NULL       |
|   2 | 市场部 | 市场推广     | NULL       |
+-----+--------+--------------+------------+
2 rows in set

mysql> 

复制表子查询

（1）复制表

1、拷贝表结构

CREATE TABLE newtable like oldtable;

2、拷贝表结构和数据（但约束与索引除外）

CREATE TABLE newtable AS (SELECT * FROM oldtable);

3、拷贝表结构+数据

CREATE TABLE newtable like oldtable;
INSERT INTO newtable SELECT * FROM oldtable;

4、跨数据库拷贝表

CREATE TABLE newtable like poss.oldtable;
CREATE TABLE newposs.newtable like oldposs.oldtable;

5、拷贝一个表中其中的一些字段（指定新名），其中一些数据

CREATE TABLE newtable AS
(
	SELECT id,username AS ename,password AS pass FROM user WHERE id <100
);

（2）复制数据

• 在INSERT语句中加入了子查询

• 不必书写VALUES子句

• 子查询中的值列表应与INSERT子句中的列名对应

INSERT INTO emp2
SELECT * FROM employees WHERE department_id = 90;
或
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees WHERE job_id LIKE '%REP%';