10、MySQL——子查询

目录

一、子查询

1、子查询出现的位置

2、子查询结果集的形式

二、实例演示 

1、查询工资高于JONES的员工 

1.1  分析

1.2  代码

2、查询与SCOTT同一部门的员工

2.1  分析

2.2  代码

3、工资高于30号部门所有人的员工信息

3.1  分析

3.2  代码

4、查询工作和工资与MARTIN完全相同的员工信息 

4.1  分析

4.2  代码

5、查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

 6、自连接：自己连接自己，起别名

6.1  求7369员工编号、姓名、经理编号和经理姓名

 6.2  求各个部门薪水最高的员工所有信息

---

一、子查询

一个select语句中包含另一个完整的select语句。

--->子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。 

1、子查询出现的位置

（1）Where之后，作为被查询的条件之一；

        当子查询出现在where后作为条件时，还可以使用如下关键字：

--->ANY

--->ALL

（2）FROM之后，做表。 

2、子查询结果集的形式

--->单行单列（用于条件）

--->单行多列（用于条件）

--->多行单列（用于条件）

--->多行多列（做表）

二、实例演示 

创建两张表并添加数据：

CREATE DATABASE mysql10;
USE mysql10;
CREATE TABLE emp(
empno INT COMMENT '员工编号',
ename VARCHAR(50) COMMENT '员工姓名',
job VARCHAR(50) COMMENT '员工工作',
mgr INT COMMENT '领导编号',
hiredate DATE COMMENT '入职日期',
sal DECIMAL(7,2)COMMENT '月薪',
comm DECIMAL(7,2) COMMENT '奖金',
deptno INT COMMENT '部门编号'
);
 
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

USE mysql10;
CREATE TABLE dept(
deptno INT COMMENT '部门编码',
dname VARCHAR(14) COMMENT '部门名称',
loc VARCHAR(13) COMMENT '部门所在地点'
);
 
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');

1、查询工资高于JONES的员工 

1.1  分析

先查询JONES的工资：

SELECT sal FROM emp WHERE ename='JONES';

再把JONES的工资作为条件去查询工资高于JONES的员工：

SELECT * FROM emp WHERE sal>(JONES的工资); 

1.2  代码

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='JONES'); 

2、查询与SCOTT同一部门的员工

2.1  分析

先查询SCOTT的部门：

SELECT deptno FROM emp WHERE ename='SCOTT';

再查询和SCOTT同部门的员工：

SELECT * FROM emp WHERE deptno=(SCOTT的部门); 

2.2  代码

SELECT * FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='SCOTT'); 

3、工资高于30号部门所有人的员工信息

查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

--->子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

3.1  分析

第一步：查询30部门所有人的工资：

SELECT sal FRON emp WHERE deptno=30; 

第二步：查询高于30部门所有人的工资：

SELECT * FROM emp WHERE sal>ALL(30部门所有人的工资); 

3.2  代码

SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30); 

4、查询工作和工资与MARTIN完全相同的员工信息 

4.1  分析

第一步：查询MARTIN的工作和工资

SELECT job,sal FROM emp WHERE ename='MARTIN';

第二步：查询出与MARTIN工作和工资相同的员工

SELECT * FROM emp WHERE (job,sal) IN (MARTIN的工作和工资); 

4.2  代码

SELECT* FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN'; 

5、查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

无需子查询；

查询列：员工名称、员工工资、部门名称、部门地址；

查询表：emp和dept，分析得出，不需要外连接（外连接的特性：某一行（或某些行）记录上会出现一半有值，一半为NULL值）；

条件：员工编号为7788

 第一步：去除多表，只查一张表，这里去除部门表，只查员工表

SELECT ename,sal FROM emp WHERE empno=7788;

第二步：把第一步与deft做内连接查询，添加主外键条件去除无用笛卡尔积

SELECT e.ename,e.sal,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno AND empno=7788; 

第三步： 第二步中的dept表表示所有行所有列的一张完整的表，这里可以把dept替换成所有行，但只有dname和loc列的表，这需要子查询。

查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。

SELECT dname,loc,deptno From dept;

第四步：替换第二步中的dept

SELECT e.ename,e.sal,d.dname,d.loc FROM emp e,(SELECT dname,loc,deptno FROM dept) d WHERE e.deptno=d.deptno AND e.empno=7788;

 6、自连接：自己连接自己，起别名

6.1  求7369员工编号、姓名、经理编号和经理姓名

SELECT e1.empno,e1.ename,e2.mgr,e2.ename FROM emp e1,emp e2 WHERE e1.mgr=e2.empno AND e1.empno=7369;

 

 6.2  求各个部门薪水最高的员工所有信息

SELECT * FROM emp e,(SELECT MAX(sal) maxsal,deptno FROM emp GROUP BY deptno) a WHERE e.deptno=a.deptno AND e.sal=a.maxsal;