在SQL语言中，一个SELECT...FROM...WHERE语句会产生一个新的数据集；
将一个查询语句完全嵌套到另一个查询语句中的的WHERE或HAVING的条件表达式中，这种查询称为嵌套查询；
通常把内部的、被另一个查询语句调用的查询称为"子查询"；
将调用子查询的查询语句称为"父查询"，子查询还可以可以调用子查询；
SQL语句允许由一系列简单查询构成嵌套结构，从而实现嵌套查询，极大增强了SQL的查询能力，使得用户视图的多样性提升；
测试数据说明：
create database worker;
use worker;
create table DEPT
(
DEPTNO int(2) not null, #部门编号
DNAME varchar(14), #部门名称
LOC varchar(13) #部门地址
);
alter table DEPT add constraint PK_DEPT primary key (DEPTNO); #设定主键为部门编号
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');
create table emp
(
empNO int(4) not null, #员工编号
ENAME varchar(10), #员工名称
JOB varchar(9), #岗位名称
MGR int(4), #管理人员编号
HIREDATE date, #入职时间
SAL int(7 ), #薪资
COMM int(7 ), #奖金
DEPTNO int(2) #部门编号
);
alter table emp add constraint PK_emp primary key (empNO); #设定主键为empno
alter table emp add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO); #设定外键关联为depino
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,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, 300, 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, 500, 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, 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, 1400, 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, 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, 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,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,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, 0, 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,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,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,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,null, 10);
create table salgrade (
grade numeric primary key, #薪资等级
losal numeric, #最低薪资
hisal numeric #最高薪资
);
insert into salgrade values (1, 700, 1200); #薪资等级为五
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);
加载测试数据
create database worker;
use worker;
create table DEPT
(
DEPTNO int(2) not null,
DNAME varchar(14),
LOC varchar(13)
);
alter table DEPT add constraint PK_DEPT primary key (DEPTNO);
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');
create table emp
(
empNO int(4) not null,
ENAME varchar(10),
JOB varchar(9),
MGR int(4),
HIREDATE date,
SAL int(7 ),
COMM int(7 ),
DEPTNO int(2)
);
alter table emp add constraint PK_emp primary key (empNO);
alter table emp add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO);
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,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, 300, 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, 500, 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, 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, 1400, 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, 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, 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,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,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, 0, 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,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,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,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,null, 10);
create table salgrade (
grade numeric primary key,
losal numeric,
hisal numeric
);
insert into salgrade values (1, 700, 1200);
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);
一、1不相关子查询¶
子查询知识引入：
查询所有比'CLARK'工资高的员工的信息
# 没有应用子查询操作时
## 从 emp 表中筛选出员工姓名为 'CLARK' 的记录,然后返回该记录中的 sal（工资）字段
mysql> select sal from emp where ename='CLARK';
+------+
| sal |
+------+
| 2450 |
+------+
1 row in set (0.00 sec)
## 从 emp 表中筛选出所有工资（sal）大于 2450 的员工记录
mysql> select * from emp where sal > 2450;
+-------+-------+-----------+------+------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+-----------+------+------------+------+------+--------+
5 rows in set (0.00 sec)
# 进行应用子查询操作后
## 从 emp 表中查找员工姓名为 'CLARK' 的记录，并返回其工资（sal）
mysql> select * from emp where sal > (select sal from emp where ename='CLARK');
+-------+-------+-----------+------+------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+-----------+------+------------+------+------+--------+
5 rows in set (0.01 sec)
一条SQL语句含有多个select，先执行子查询，再执行外查询；
子查询可以独立运行，就称为不相关子查询；根据子查询的结果行数，可以分为单行子查询和多行子查询；
单行子查询：
单行子查询就是子查询输出的结果集为一行信息，可以直接进行比较即可；（< > =）
查询工资高于平均工资的雇员名字和工资；
mysql> select ename,sal from emp where sal >(select avg(sal) from emp);
+-------+------+
| ename | sal |
+-------+------+
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
| SCOTT | 3000 |
| KING | 5000 |
| FORD | 3000 |
+-------+------+
6 rows in set (0.00 sec)
查询和CLARK同一部门且比他工资低的雇员名字和工资；
mysql> select ename,sal,deptno from emp
where deptno = (select deptno from emp where ename='CLARK')
and sal <(select sal from emp where ename='CLARK');
+--------+------+--------+
| ename | sal | deptno |
+--------+------+--------+
| MILLER | 1300 | 10 |
+--------+------+--------+
1 row in set (0.00 sec)
查询和CLARK同一职位且比他工资高的雇员信息；
mysql>
select * from emp
where job = (select job from emp where ename='CLARK')
and sal >(select sal from emp where ename='CLARK');
+-------+-------+---------+------+------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
+-------+-------+---------+------+------------+------+------+--------+
2 rows in set (0.00 sec)
查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员；
# 没有应用子查询操作时
mysql>
select * from emp where ename='SCOTT';
+-------+-------+---------+------+------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+------+------+--------+
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
+-------+-------+---------+------+------------+------+------+--------+
1 row in set (0.00 sec)
select * from emp
where job='ANALYST' and HIREDATE <'1987-04-19';
+-------+-------+---------+------+------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+------+------+--------+
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+---------+------+------------+------+------+--------+
1 row in set (0.00 sec)
# 进行应用子查询操作时
mysql>
select * from emp
where job = (select job from emp where ename='SCOTT')
and hiredate < (select hiredate from emp where ename='SCOTT');
+-------+-------+---------+------+------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+------+------+--------+
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+---------+------+------------+------+------+--------+
1 row in set (0.00 sec)
查询中国城市人口数量高于全国城市人口平均值的城市名称和人口数量情况；
select name,population from city where
countrycode='chn' and
population > (select avg(population) from city where countrycode='chn');
查询和石家庄同一省份的其他城市信息，将其他城市人口数量比石家庄人口数量低的城市名称和人口数量输出显示；
select name,population,district from city where
district = (select district from city where name='shijiazhuang')
and
population < (select population from city where name='shijiazhuang');
多行子查询：
多行子查询就是子查询输出的结果集为多行信息，不能直接进行比较处理，需要借助 in 或者 any 或者 all 进行处理；
查询工资低于任意一个'CLERK'职位工资的雇员信息；
# 查询所有职务为 CLERK 的员工的工资
mysql> select * from emp where sal < (select max(sal) from emp where job ='CLERK');
+-------+--------+----------+------+------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
+-------+--------+----------+------+------------+------+------+--------+
5 rows in set (0.00 sec)
# 查询工资低于 CLERK 员工中最高工资的所有员工
mysql> select * from emp where sal < (select max(sal) from emp where job ='CLERK');
+-------+--------+----------+------+------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
+-------+--------+----------+------+------------+------+------+--------+
5 rows in set (0.00 sec)
# MySQL 无法将多行数据作为一个标量值进行比较。
mysql> select * from emp where sal < (select sal from emp where job ='CLERK');
ERROR 1242 (21000): Subquery returns more than 1 row
# 使用 ANY 运算符进行比较
mysql> select * from emp where sal < any(select sal from emp where job ='CLERK');
+-------+--------+----------+------+------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
+-------+--------+----------+------+------------+------+------+--------+
5 rows in set (0.00 sec)
# 排除 CLERK 自身，同时使用 ANY 运算符
mysql> select * from emp where sal < any(select sal from emp where job ='CLERK') AND job !='CLERK';
+-------+--------+----------+------+------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+------+------+--------+
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
+-------+--------+----------+------+------------+------+------+--------+
2 rows in set (0.00 sec)
查询工资比所有的'SALESMAN'都高的雇员的编号、名字和工资
# 使用子查询获取销售员的最高薪水，然后在主查询中查找薪水高于此最大值的员工
mysql> select empno,ename,sal from emp where sal > (select max(sal) from emp where job ='SALESMAN');
+-------+-------+------+
| empno | ename | sal |
+-------+-------+------+
| 7566 | JONES | 2975 |
| 7698 | BLAKE | 2850 |
| 7782 | CLARK | 2450 |
| 7788 | SCOTT | 3000 |
| 7839 | KING | 5000 |
| 7902 | FORD | 3000 |
+-------+-------+------+
6 rows in set (0.00 sec)
# 使用 > ALL 关键字，要求主查询中的薪水必须大于子查询返回的所有销售员的薪水
mysql> select empno,ename,sal from emp where sal > all(select sal from emp where job ='SALESMAN');
+-------+-------+------+
| empno | ename | sal |
+-------+-------+------+
| 7566 | JONES | 2975 |
| 7698 | BLAKE | 2850 |
| 7782 | CLARK | 2450 |
| 7788 | SCOTT | 3000 |
| 7839 | KING | 5000 |
| 7902 | FORD | 3000 |
+-------+-------+------+
6 rows in set (0.00 sec)
查询部门20中职务同部门10的雇员一样的雇员：
# IN 等于其中任何一个
select job from emp where deptno = 20;
select job from emp where deptno = 10;
#
select * from emp where deptno = 20 and job in ("MANAGER","PRESIDENT","CLERK");
# 使用in操作符返回 deptno 为 20 且 job 为 "MANAGER"、"PRESIDENT" 或 "CLERK" 的所有员工
select * from emp where deptno = 20 and job in (select job from emp where deptno = 10);
# 使用= any操作符返回 deptno 为 20 且 job 与 deptno 为 10 的员工的 job 相同的所有员工
select * from emp where deptno = 20 and job = any (select job from emp where deptno = 10);
in与= any两者之间的区别：
IN操作符既可以用于直接的值列表，也可以用于子查询返回的结果集；而= ANY操作符只能与子查询一起使用，不能直接用于值列表。IN操作符可以直接接受一个值列表，例如job IN ('MANAGER', 'PRESIDENT', 'CLERK')；而= ANY操作符需要一个子查询返回的结果集，例如job = ANY (SELECT job FROM ...)。
二、相关子查询¶
一条SQL语句含有多个select，先执行外查询，再执行子查询；
子查询不可以独立运行，就称为相关子查询；
- 相关子查询优势：操作简单，功能强大（一些使用不相关子查询不能实现或者实现繁琐的子查询，可以使用相关子查询实现）
- 相关子查询缺陷：语句操作稍难理解
子查询知识引入：
查询最高工资的员工(不相关子查询)
select max(sal) from emp;
select * from emp where sal = (select max(sal) from emp);
查询本部门最高工资的员工
# 查询本部门(10)最高工资的员工
select * from emp where deptno=10;
select * from emp where deptno=10 and sal = (select max(sal) from emp where deptno=10);
# 查询本部门(20)最高工资的员工
select * from emp where deptno=20;
select * from emp where deptno=20 and sal = (select max(sal) from emp where deptno=20);
# 查询本部门(30)最高工资的员工
select * from emp where deptno=30;
select * from emp where deptno=30 and sal = (select max(sal) from emp where deptno=30);
# 查询10部门、20部门、30部门中最高工资的员工
select * from emp where deptno=10 and sal = (select max(sal) from emp where deptno=10)
union
select * from emp where deptno=20 and sal = (select max(sal) from emp where deptno=20)
union
select * from emp where deptno=30 and sal = (select max(sal) from emp where deptno=30);
# 使用子查询筛选出每个部门最高工资的员工
SELECT e.*
FROM emp e
JOIN (
SELECT deptno, MAX(sal) AS max_sal
FROM emp
GROUP BY deptno
) AS subquery
ON e.deptno = subquery.deptno AND e.sal = subquery.max_sal;
# 使用窗口函数筛选出每个部门最高工资的员工
SELECT empno, ename, sal, deptno
FROM (
SELECT empno, ename, sal, deptno,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn
FROM emp
) AS ranked
WHERE rn = 1;
查询工资高于其所在部门的平均工资的那些员工
# 查询工资高于其所在部门10的平均工资的那些员工：
##第一步：算出部门10员工的平均工资
select avg(sal) from emp where deptno=10;
##第二步： 查询工资高于其所在部门10的平均工资的那些员工
select * from emp where deptno = 10 and sal >2916.6667;
##将第一步和第二步结合起来
select * from emp where deptno = 10 and sal >(select avg(sal) from emp where deptno=10);
# 查询工资高于其所在部门20的平均工资的那些员工：
select * from emp where deptno = 20 and sal >(select avg(sal) from emp where deptno=20);
# 查询工资高于其所在部门30的平均工资的那些员工：
select * from emp where deptno = 30 and sal >(select avg(sal) from emp where deptno=30);
##必须要应用别名功能
select * from emp where sal >(select avg(sal) from emp where deptno=deptno);
select * from emp e1 where sal >(select avg(sal) from emp e2 where e2.deptno=e1.deptno);
子查询信息练习题目分析：
01 查询每个部门平均薪水的等级（连接查询+子查询+分组查询）
# 查询每个部门平均薪水
select deptno,avg(sal) from emp group by deptno;
# 查询每个员工的薪水的等级
select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;
# 查询每个部门平均薪水的等级
select * from (select deptno,avg(sal) asl from emp group by deptno) asg
join salgrade sg on asg.asl between sg.losal and sg.hisal;
select asg.*,sg.grade from (select deptno,avg(sal) asl from emp group by deptno) asg
join salgrade sg on asg.asl between sg.losal and sg.hisal;
子查询不仅可以出现在where条件中，还可以出现在from中；此练习题是一个不相关子查询；
02 根据学生成绩情况查询数据
一张成绩表，里面有3个字段：语文，数学，英语；请用一条SQL语句查询这表里的记录并按以下条件显示出来：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格，显示格式为：
| 语文 | 数学 | 英语 |
|---|---|---|
| 及格 | 优秀 | 不及格 |
创建测试数据：
-- 创建数据表
create table table1 (chinese int(3),maths int(3),english int(3));
-- 创建测试数据
insert into table1 values(80,78,67);
insert into table1 values(100,45,67);
-- 查询测试数据信息
select * from table1;
此题需求，需要利用case where（多分支判断语句）或 if （双分支语句）进行题目处理；
-- 利用if语句实现方法：
select *,
if(chinese >=80,'优秀',if(chinese >=60,'及格','不及格'))
from table1;
select *,
if(chinese >=80,'优秀',if(chinese >=60,'及格','不及格')),
if(maths >=80,'优秀',if(maths >=60,'及格','不及格')),
if(english >=80,'优秀',if(english >=60,'及格','不及格'))
from table1;
select
if(chinese >=80,'优秀',if(chinese >=60,'及格','不及格')) as 语文,
if(maths >=80,'优秀',if(maths >=60,'及格','不及格')) as 数学,
if(english >=80,'优秀',if(english >=60,'及格','不及格')) as 英语
from table1;
-- 利用case语句实现方法：
select *,
case
when chinese >=80 then '优秀'
when chinese >=60 then '及格'
else '不及格'
end '语文'
from table1;
select
case
when chinese >=80 then '优秀'
when chinese >=60 then '及格'
else '不及格'
end '语文',
case
when maths >=80 then '优秀'
when maths >=60 then '及格'
else '不及格'
end '数学',
case
when english >=80 then '优秀'
when english >=60 then '及格'
else '不及格'
end '英语'
from table1;
02 根据比赛日胜负情况，统计胜负结果；（case-when/if+子查询+分组）
测试数据表中内容如下：
| 比赛日 | 胜负情况 |
|---|---|
| 2005-05-09 | 胜 |
| 2005-05-09 | 胜 |
| 2005-05-09 | 负 |
| 2005-05-09 | 负 |
| 2005-05-10 | 胜 |
| 2005-05-10 | 负 |
| 2005-05-10 | 负 |
如果要生成下表结果信息，该如何写SQL语句？
| 比赛日期 | 胜场数量 | 负场数量 |
|---|---|---|
| 2005-05-09 | 2 | 2 |
| 2005-05-10 | 1 | 2 |
创建测试数据：
-- 创建数据表
create table table2 (gamedate date, result char(3));
-- 创建测试数据
insert into table2 values ('2005-05-09','胜');
insert into table2 values ('2005-05-09','胜');
insert into table2 values ('2005-05-09','负');
insert into table2 values ('2005-05-09','负');
insert into table2 values ('2005-05-10','胜');
insert into table2 values ('2005-05-10','负');
insert into table2 values ('2005-05-10','负');
-- 查询测试数据信息
select * from table2;
此题需求，需要利用case where（多分支判断语句）/if（双分支判断语句）结合子查询，以及分组操作进行题目处理；
-- 利用if语句实现方法：
select *,
if(result='胜',1,0) 胜,
if(result='负',1,0) 负
from table2;
-- 方法01：直接使用group by
select gamedate,
sum(if(result='胜',1,0) )胜,
sum(if(result='负',1,0) )负
from table2
group by gamedate;
-- 方法02：直接使用group by+子查询
select gamedate,sum(胜) 胜,sum(负) 负 from (
select gamedate,
if(result='胜',1,0) 胜,
if(result='负',1,0) 负
from table2) table3
group by gamedate;
-- 利用case语句实现方法：
select gamedate,
case when result='胜' then 1 else 0 end 胜,
case when result='负' then 1 else 0 end 负
from table2;
-- 方法01：直接使用group by
select gamedate,
sum(case when result='胜' then 1 else 0 end) 胜,
sum(case when result='负' then 1 else 0 end) 负
from table2
group by gamedate;
-- 方法02：直接使用group by+子查询
select gamedate,sum(胜) 胜,sum(负) 负 from(
select gamedate,
case when result='胜' then 1 else 0 end 胜,
case when result='负' then 1 else 0 end 负
from table2) table3
group by gamedate;
一、元数据概念介绍¶
元（meta）一般会被翻译成中文是”关于...的...”，元数据（meta data）等价于data about data，表示关于数据的数据；
一般是元数据就是结构化数据，例如存储在数据库里的数据，规定了字段的长度。类型等；
元数据就是描述数据的数据，在MySQL中就是描述database的数据，属性，状态等相关信息；
表示在数据库服务中有哪些数据库，库中有哪些表，表中有多少字段，字段是什么类型等等，这样的数据就是数据库的元数据；
二、元数据获取方法¶
2.1 元数据获取方式一：利用命令获取（show）¶
常用SQL语句的show命令查看元数据信息
# 查询数据库服务中的所有数据库信息（数据库名称-元数据）
mysql> show databases;
# 查询数据库服务中的相应数据表信息（数据表名称-元数据）
mysql> show tables;
mysql> show tables from mysql;
# 查询数据库服务中的建库语句信息 （建库语句参数-元数据 建库语句就是DDL语句，定义建立数据库的属性信息）
mysql> show create database <库名>;
# 查询数据库服务中的建表语句信息 （建表语句参数-元数据 建表语句就是DDL语句，定义建立数据表的属性信息）
mysql> show create table <表名>;
# 查询数据库服务中的数据表的结构（数据表的列定义信息-元数据）
mysql> desc <表名>;
mysql> show columns from <表名>;
# 查询数据库服务中的相应数据表状态 （数据表的状态信息/统计信息-元数据）
mysql> show table status from <库名>;
# 查看数据库服务中的具体数据库表的状态信息（属于单库或单表查询）
mysql> show table status from world like 'city' \G
*************************** 1. row ***************************
Name: city -- 数据表名称信息
Engine: InnoDB -- 使用的数据库引擎信息
Version: 10
Row_format: Dynamic
Rows: 4046 -- 数据表的行数信息
Avg_row_length: 101 -- 平均行长度
Data_length: 409600
Max_data_length: 0
Index_length: 114688 -- 索引长度信息
Data_free: 0
Auto_increment: 4080 -- 自增列的值计数
Create_time: 2022-11-04 09:13:27 -- 数据表创建时间
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci -- 校对规则信息
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
# 查询数据库服务中的相应数据表的索引情况（了解即可）
mysql> show index from world.city;
# 查询数据库服务中的用户权限属性配置信息
mysql> show grants for root@'localhost';
# 查询数据库服务的系统状态信息，表示当前数据库的所有连接情况
mysql> show [full] processlist;
# 查询数据库服务的所有配置信息
mysql> show variables;
mysql> show variables like '%xx%';
# 查询数据库服务的系统整体状态，表示当前数据库服务运行的即时状态情况
mysql> show status;
mysql> show status like '%lock%';
# 查询数据库服务的所有二进制日志信息（binlog日志）
mysql> show binary logs;
# 查询数据库服务正在使用的二进制日志
mysql> show master status;
# 查询数据库服务具体二进制日志内容事件信息
mysql> show binlog events in 'binlog.000009';
# 查询数据库服务存储引擎相关信息
mysql> show engine innodb status \G;
# 在数据库服务主库查看从库信息
mysql> show slave hosts;
# 查询数据库服务主从状态信息
mysql> show slave status;
说明：使用show语句虽然可以快速得到相应的数据库元数据信息，但是查询功能过于单一，想查询全面信息，就需要执行多条语句；
2.2 元数据获取方式二：利用库中视图（information_schema）¶
视图概念：
将查询基表元数据语句信息方法封装在一个变量或别名中，这个封装好的变量或别名就成为视图，视图信息都是存储在内存中的表
元数据信息存储在系统基表中，通过一般的select命令只能查看数据信息，不能查看到系统基表，以免被随意调整篡改；
而查询基表的语句过于复杂，可以将整个查询基表语句定义为一个视图信息（等价于别名/变量），调取视图等价于调取查询基表语句；
information_schema库中的内存表都是每次数据库服务启动时生成的，里面存储了查询元数据基表的视图信息；
环境准备：
# 创建数据库db16
create database db16;
# 创建student表
CREATE TABLE db16.student (
sno VARCHAR(10) PRIMARY KEY,
sname VARCHAR(50),
sage INT,
ssex VARCHAR(10)
);
INSERT INTO db16.student (sno, sname, sage, ssex) VALUES
('S001', '张三', 20, '男'),
('S002', '李四', 19, '女'),
('S003', '王五', 21, '男');
# 创建teacher表
CREATE TABLE db16.teacher (
tno VARCHAR(10) PRIMARY KEY,
tname VARCHAR(50)
);
INSERT INTO db16.teacher (tno, tname) VALUES
('T001', '刘老师'),
('T002', '王老师');
# 创建course表
CREATE TABLE db16.course (
cno VARCHAR(10) PRIMARY KEY,
cname VARCHAR(100),
tno VARCHAR(10),
FOREIGN KEY (tno) REFERENCES teacher(tno)
);
INSERT INTO db16.course (cno, cname, tno) VALUES
('C001', '数学', 'T001'),
('C002', '英语', 'T002');
# 创建sc表
CREATE TABLE db16.sc (
sno VARCHAR(10),
cno VARCHAR(10),
score INT,
PRIMARY KEY (sno, cno),
FOREIGN KEY (sno) REFERENCES student(sno),
FOREIGN KEY (cno) REFERENCES course(cno)
);
INSERT INTO db16.sc (sno, cno, score) VALUES
('S001', 'C001', 58),
('S002', 'C001', 75),
('S003', 'C002', 45);
# 结果验证
show tables from db16;
视图定义：
-- 查询每位老师及其所教课程中成绩不及格的学生名单
SELECT
t.tname AS '教师姓名',
GROUP_CONCAT(s.sname ORDER BY s.sno SEPARATOR ', ') AS '不及格学生'
FROM db16.teacher t
JOIN db16.course c ON t.tno = c.tno
JOIN db16.sc sc ON c.cno = sc.cno
JOIN db16.student s ON sc.sno = s.sno
WHERE sc.score < 60
GROUP BY t.tno;
+--------------+-----------------+
| 教师姓名 | 不及格学生 |
+--------------+-----------------+
| 刘老师 | 张三 |
| 王老师 | 王五 |
+--------------+-----------------+
2 rows in set (0.00 sec)
-- 创建带注释的视图（推荐）
CREATE VIEW db16.teacher_failing_students_view AS
SELECT
t.tname AS '教师姓名',
GROUP_CONCAT(s.sname ORDER BY s.sno SEPARATOR ', ') AS '不及格学生',
COUNT(*) AS '不及格人数'
FROM db16.teacher t
JOIN db16.course c ON t.tno = c.tno
JOIN db16.sc sc ON c.cno = sc.cno
JOIN db16.student s ON sc.sno = s.sno
WHERE sc.score < 60
GROUP BY t.tno;
-- 查看视图数据
mysql> SELECT * FROM db16.teacher_failing_students_view;
+--------------+-----------------+-----------------+
| 教师姓名 | 不及格学生 | 不及格人数 |
+--------------+-----------------+-----------------+
| 刘老师 | 张三 | 1 |
| 王老师 | 王五 | 1 |
+--------------+-----------------+-----------------+
2 rows in set (0.00 sec)
-- 验证数据一致性
mysql> SELECT
t.tname,
c.cname,
s.sname,
sc.score
FROM db16.teacher t
JOIN db16.course c ON t.tno = c.tno
JOIN db16.sc sc ON c.cno = sc.cno
JOIN db16.student s ON sc.sno = s.sno
WHERE sc.score < 60;
+-----------+--------+--------+-------+
| tname | cname | sname | score |
+-----------+--------+--------+-------+
| 刘老师 | 数学 | 张三 | 58 |
| 王老师 | 英语 | 王五 | 45 |
+-----------+--------+--------+-------+
2 rows in set (0.00 sec)
视图查询：
# 切换进入information_schema数据库中查看表信息
mysql> use information_schema;
# 此时看到的所有表信息，其实都是视图信息
mysql> show tables;
# 查看获取视图信息创建语句，查看tables这个视图表的创建过程
mysql> show create view tables;
# 查看视图表信息应用
# 统计数据库资产信息（数据资产），获取每个库中表的个数和名称信息（业务相关）
-- 查看information_scheam中的tables表的结构信息；
mysql> desc information_schema.tables;
-- 列出所有数据库（schema）中的表信息。
mysql> select table_schema,count(*),group_concat(table_name) from information_schema.tables group by table_schema\G;
-- 只显示用户自建的数据库,通过 WHERE 子句排除了某些系统级的数据库（例如 mysql、sys、performance_schema 以及以 information_ 开头的数据库）
mysql> select table_schema,count(*),group_concat(table_name) from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') group by table_schema\G;
# 统计数据库资产信息（数据资产），获取每个数据库数据占用磁盘空间
mysql> select table_schema,sum(table_rows*avg_row_length+index_length)/1024/1024 from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') group by table_schema;
# 统计数据库资产信息（数据资产），获取具有碎片信息的表，碎片信息过多会导致索引信息失效，以及统计信息不真实的情况
mysql> select table_schema,table_name,data_free from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and data_free >0 ;
# 统计数据库资产信息（数据资产），处理具有碎片信息的表，可以对已经是innodb存储引擎的表做操作，实现批量整理碎片功能
mysql> alter table t1 engine=innodb;
-- 可以对已经是innodb存储引擎的表做操作，实现整理碎片功能
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and data_free >0 ;
# 模拟创建一些myisam引擎数据表
mysql> use db16;
mysql> create table t1 (id int) engine=myisam;
mysql> create table t2 (id int) engine=myisam;
mysql> create table t3 (id int) engine=myisam;
# 统计数据库资产信息（数据资产），获取数据库中非innodb表信息
mysql>select table_schema,table_name,engine from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and engine!='innodb';
# 统计数据库资产信息（数据资产），修改数据库中非innodb表信息替换成innodb,可以对不是innodb存储引擎的表做操作，实现数据表引擎修改
mysql> alter table world.t1 engine=innodb;
# 从系统视图 information_schema.tables 中筛选出所有非系统数据库（排除了 'mysql'、'sys'、'performance_schema'、'information_'）中存储引擎不是 InnoDB 的表，并利用 CONCAT 函数生成一系列修改存储引擎的 SQL 语句
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and engine !='innodb';
# 使用 CONCAT 函数拼接字符串，生成一系列将非 InnoDB 存储引擎的表转换为 InnoDB 的 ALTER TABLE 语句，然后通过 INTO OUTFILE 将结果写入文件 /tmp/alter.sql 中,由于 MySQL 服务器启用了 secure-file-priv 选项，它限制了导出文件的路径,所以导致执行后报错
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and engine!='innodb' into outfile '/tmp/alter.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
#解决方法：修改配置文件参数信息，实现将数据库操作的数据信息导入到系统文件中，配置完毕重启数据库服务
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
#可以对不是innodb存储引擎的表做操作，实现数据表批量化引擎修改，调用数据库脚本信息
mysql> source /tmp/alter.sql
tables视图表的结构信息：
关注的视图表字段说明：
| 序号 | 字段信息 | 解释说明 |
|---|---|---|
| 01 | TABLE_SCHEMA | 表示数据表所属库的名称信息 |
| 02 | TABLE_NAME | 表示数据库中所有数据表名称 |
| 03 | ENGINE | 表示数据库服务中的引擎信息 |
| 04 | TABLE_ROWS | 表示数据库相应数据表的行数 |
| 05 | AVG_ROW_LENGTH | 表示数据表中每行的平均长度 |
| 06 | INDEX_LENGTH | 表示数据表中索引信息的长度 |
| 07 | DATA_FREE | 表示数据库服务碎片数量信息 |
| 08 | CREATE_TIME | 表示数据表创建的时间戳信息 |
| 09 | UPDATE_TIME | 表示数据表修改的时间戳信息 |
| 10 | TABLE_COMMENT | 表示数据表对应所有注释信息 |
说明：使用information_schema的视图查看功能，可以看到全局数据库或数据表的元数据信息，探究全局层面的元数据
三、元数据调取函数¶
利用程序代码在语句数据库交互的过程,也可以调取数据库中的相关元数据信息:
# 获取数据库的产品名称
getDatabaseProductName
# 获取数据库的版本号
getDatabaseProductName
# 获取数据库的用户名
getUserName
# 获取数据库连接的URL
getURL
# 获取数据库的驱动名称
getDriverName
# 获取数据库的驱动版本号
driverVersion
# 查看数据库是否只允许读操作
isReadOnly
# 查看数据库是否支持事务
supportsTransactions
数据库元数据信息参考资料：https://www.yisu.com/jc/579710.html
知识拓展：数据库服务自带的视图库信息
MySQL自带三个系统数据库用于监控MySQL,这三个数据库分别从不同的角度观察MySQL数括库中数据信息:
| 序号 | 自带数据库 | 作用说明 |
|---|---|---|
| 01 | information_schema | 数据库中主要保存MySQL的静态元数据 |
| 02 | performance_schema | 数据库中主要保存MySQL性能相关的动态元数据 |
| 03 | sys | 数据库是建立在前两个数据库基础上的数据库,包括视图、存储过程和函数 |
MySQL默认数据库作用说明:information_schema
数据组成介绍
系统数据库information_schema中保存着MySQL的静态元数据,也有少量的动态元数据;
information_schema:实际上是一个虚拟数松库,它并不包含任何实际的数据,它足唯一一个在操作系统的文件系统上;
没有对应目录的数据库,因此该数据库中的表只能被查询,不能被修改;
利用如下SQL语句,可以查询出information_schema数据库中表类型和数量:
SELECT TABLE_TYPE, COUNT(*)
FROM information_schema.tables
WHERE table_schema = 'information_schema'
GROUP BY TABLE_TYPE;
+-------------+----------+
| TABLE_TYPE | COUNT(*) |
+-------------+----------+
| SYSTEM VIEW | 79 |
+-------------+----------+
1 row in set (0.00 sec)
可以看到,在MySQL8.0中information_schema数据库共有79个表,这些表都是同一个类型SYSTEM VIEW视图表;
information_schema数据库中并没有BASE TABLE类型的表;
静态元数据信息
知识扩展:MYSQL优化之碎片整理
在MySQL中,我们经常会使用VARCHAR、TEXT、BLOB等可变长度的文本数据类型。
不过,当我们使用这些数据类型之后,我们就不得不做一些额外的工作——MySQL数据碎片整理。
那么,为什么在使用这些数据类型之后,我们就要对MySQL定期进行碎片整理呢?
现在,我们先来看一个具体的例子。
在这里,我们使用如下SQL语句在MySQL自带的TEST数据库中创建名为DEMO的数据表并插入5条测试数据。
创建测试数据库:
CREATE DATABASE db17;
创建测试数据表:
CREATE TABLE db17.DEMO (
id INT UNSIGNED,
body TEXT
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
创建测试数据信息:
INSERT INTO db17.DEMO VALUES(1,'AAAAA');
INSERT INTO db17.DEMO VALUES(2,'BBBBB');
INSERT INTO db17.DEMO VALUES(3,'CCCCC');
INSERT INTO db17.DEMO VALUES(4,'DDDDD');
INSERT INTO db17.DEMO VALUES(5,'EEEEE');
然后以这5条测试数据为基础,使用如下INSERT INTO语句重复执行多次进行复制性插入;
# 大概生成260w行的数据表信息
INSERT INTO db17.DEMO SELECT id,body FROM db17.DEMO;
MySQL中MyISAM表的数据是以文件形式存储的,我们可以在MySQL存储数据的文件夹中找到数据库test目录下的demo.MYD文件。
此时,可以看到demo.MYD文件的大小约为50MB。
此时,假如我们需要删除DEM0表中所有ID列小于3的数据(即1和2),于是我们执行如下SQL语句:
-- 关闭安全模式
SET SQL_SAFE_UPDATES = 0;
-- 执行你的删除操作
DELETE FROM db17.DEMO WHERE id < 3;
-- 重新启用安全模式（建议操作后恢复）
SET SQL_SAFE_UPDATES = 1;
-- 此时可以看到DEMO表中的数据量只有原来的3/5,只剩下157万条数据记录
DELETE FROM db17.DEMO WHERE id <3;
DEMO表中的现有数据量只有原来的3/5,按理说:这个时侯demD.MYD文件的大小也应该只有原来的3/5左右。
不过,我们再次查看demo.MYD文件时,却惊奇地发现该文件大小一点都没有变!