文章目录
- 概述
- where型子查询
- from型子查询
- EXISTS型子查询
- 复制表子查询
概述
在某些情况下,当进行一个查询时,需要的条件或数据要用另一个select语句的结果,这个时候,就要用到**子查询**。
为了给主查询(外部查询)提供数据而首先执行的查询(内部查询)被叫做子查询
一般根据子查询的嵌入位置分为:where型子查询、from型子查询、exists型子查询。
说明:以下相关的演示SQL语句涉及到的表及数据,
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%';