mysql子查询

前言：

在学完子查询后，总是不值得什么时候需要使用子查询。本文用子查询的三个使用场景分析，为什么要使用子查询，什么时候要时候子查询。

子查询概述

含义：嵌套在查询中的查询，也叫嵌套查询。

注意：子查询先于主查询执行。

---

表结构和建表语句在最下面

子查询可以嵌套的位置

where、from、having

1、where：

使用场景：

  返回值为一列一行的时候使用，或者一列多行时候使用。例如在where语句中想使用分组函数可以用子查询，在子查询返回分组函数的值。

比较时候会用到的运算符：

单行运算符：>、=、>=、<、<>、<=。

多行运算符： IN、ANY、ALL。

注意：

    1、any和all都可以用单行运算符+子查询中使用分组函数替换，但是in无法替换。

    2、如果子查询返回值中有空值，不能使用not in,这样会让主查询中所有的值都为null。

例子1：

       1，查询工资最低的员工姓名，岗位及工资（一行一列）

select ename,job,sal
from emp
where sal=(select min(sal) from emp);

例子二：

2.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工（多行多列）

select ename,job,deptno
from emp
where job in (select job from emp where deptno = 10)

2、from

使用场景：

返回值为多行多列时候使用。将返回值当成一张表。

例子：

查询员工工资为其部门最低工资的员工的编号和姓名及工资。

select empno,ename,sal
from emp ,
(select deptno,min(sal) sal_min
from emp
group by deptno) dep_min_sal
where sal = sal_min and emp.deptno=dep_min_sal.deptno

3、having

使用场景：

用在分组后排除结果，一般都是排除和自己属性一样的。

例子：

查询部门人数 大于所有 部门平均人数的的部门编号，部门名称，部门人数

select emp.deptno,dept.dname,count(emp.ename) count_deptno
from dept,emp
where emp.deptno is not null and dept.deptno = emp.deptno
group by deptno
having count_deptno > (select count(ename)/(select count(deptno)from dept) from emp)

 

---

表结构和建表语句

 

 

 

drop TABLE emp;
drop TABLE dept;
drop TABLE salgrade;
create table dept
(
deptno integer(4) not null,
dname varchar(14),
loc varchar(13)
);
alter table dept add constraint pk_dept primary key (deptno);
create table emp
(
empno integer(4) not null,
ename varchar(10),
job varchar(9),
mgr integer(4),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno integer(2)
);
alter table emp add constraint pk_emp primary key (empno);
alter table emp add constraint fk_deptno foreign key (deptno) references dept (deptno);
create table salgrade
(
grade integer(1),
losal decimal(7,2),
hisal decimal(7,2)
);
insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'BOSTON');
insert into SALGRADE (GRADE, LOSAL, HISAL) values (1, 700, 1200);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (5, 3001, 9999);
-- 下面要特别注意，%d-%m-%Y 里的 d 和 m 一定要小写
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980', '%d-%m-%Y'), 800.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-02-1981', '%d-%m-%Y'), 1600.00, 300.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-02-1981', '%d-%m-%Y'), 1250.00, 500.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('02-04-1981', '%d-%m-%Y'), 2975.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981', '%d-%m-%Y'), 1250.00, 1400.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('01-05-1981', '%d-%m-%Y'), 2850.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('09-06-1981', '%d-%m-%Y'), 2450.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, STR_TO_DATE('19-04-1987', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, STR_TO_DATE('17-11-1981', '%d-%m-%Y'), 5000.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('08-09-1981', '%d-%m-%Y'), 1500.00, 0.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, STR_TO_DATE('23-05-1987', '%d-%m-%Y'), 1100.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 950.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-01-1982', '%d-%m-%Y'), 1300.00, null, 10);