转载声明：文章来源https://zhuanlan.zhihu.com/p/149454961

1、引入相关子查询

（1）不相关的子查询：子查询可以独立运行，先运行子查询，再运行外查询。

（2）相关子查询：子查询不可以独立运行，并且先运行外查询，再运行子查询

（3）好处：简单 功能强大（一些使用不相关子查询不能实现或者实现繁琐的子查询，可以使用相关子查询实现）

（4）缺点：稍难理解

【示例4】引入相关子查询

--查询最高工资的员工 
select max(sal) from emp  
select * from emp where sal = (select max(sal) from emp)   
--查询本部门最高工资的员工  
--查询10部门最高工资的员工 
select max(sal) 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 and  sal = (select max(sal) from emp where deptno = 20)  
--查询30部门最高工资的员工 
select * from emp e1 where e1.deptno = 30 and  sal = (select max(sal) from emp e2 where e2.deptno = 30)  
 
-- 使用语法更简单的相关子查询实现 
select * from emp e1 where sal = (select max(sal) from emp e2 where e2.deptno = e1.deptno)

2、相关子查询

【示例5】相关子查询

-- 查询[工资高于其所在部门的平均工资的]那些员工 
-- 查询[工资高于其所在部门10的平均工资的]那些员工 
select avg(sal) from emp where deptno = 10  
select * from emp where deptno = 10 and sal >2916.66666666667  
select * from emp e1 where e1.deptno = 10 and sal >(select avg(sal) from emp e2 where e2.deptno = 10)  
--  查询[工资高于其所在部门20的平均工资的]那些员工 
 select * from emp e1 where e1.deptno = 20 and sal >(select avg(sal) from emp e2 where e2.deptno = 10)  
-- 查询[工资高于其所在部门30的平均工资的]那些员工 
 select * from emp e1 where e1.deptno = 30 and sal >(select avg(sal) from emp e2 where e2.deptno = 30)  
-- 使用相关子查询实现 
select * from emp e1 where sal >(select avg(sal) from emp e2 where e2.deptno = e1.deptno)  
 order by deptno

3、子查询和连接查询结合使用

子查询和连接查询可以结合使用，实现更加复杂的查询功能

【示例6】子查询和连接查询结合使用

-- 查询每个部门平均薪水 
 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

注意：子查询不仅可以出现在where条件中，还可以出现在from中；此示例是一个不相关子查询。

4、索引

对于任何的数据库管理系统，索引都是进行优化的最主要因素。对于少量的数据，即使没有合适的索引对数据库性能的影响并不是很大，但是随着数据量的增加，数据库性能会急剧下降。所以索引目的在于提高检索数据的效率

（1） 什么是索引 index

汉语字典6000多个汉字，如何查找？检字法（拼音，部首），加快查找速度。

一本书 400页，如何查找？使用章节目录|附录，加快了查找速度

数据库表10万，13亿条记录，如何查找？使用索引，查询内容先查询索引，根据索引可以定位到要查询的内容，可以提高查询速度。

（2）索引的作用

提高了查询的速度

（3）常见问题

索引占用空间吗？
占用空间，但是空间小；能够带来速度的明显提升

索引是不是越多越好？
不是
索引也占用空间，多个索引就好占用更多的空间；给经常需要用到的内容建立索引，否则会查询建立了索引，占用了空间，但是很少使用

索引会提高查询的速度，但是会降低添加，更新，删除的速度（不仅操作数据库表，也要操作索引）

一般给哪些列建立索引
Emp (empno,ename,job ,mgr,hiredate,sal,comm,deptno，email)
数据库会自动给主键empno和唯一键email、外键deptno建立索引
要给经常出现在where子句中的或者order by子句中的列建立索引
sal deptno ename

索引类型
1.单列索引和多列索引：sal | sal hiredate
2.唯一索引和非唯一索引：empno | sal deptno
3.存储结构：B-Tree、R-Tree和Hash。其中B-Tree是最流行的存储结构，一般也是默认的存储结构

【示例7】索引的使用

-- 显示了所有的数据  没有索引也行 
select * from emp;    
-- 会自动给主键列建立索引，此时查询条件是主键列，自动使用了索引 
select * from emp where empno = 7839;   
 
-- 查看表的索引 
show index from emp;  
-- 没有给ename列建立索引，所以此时是逐个比较，依次查询，效率低下 
select * from emp where ename = 'BLAKE' 
-- 针对ename列创建索引 
create index index_emp_name on emp(ename);  
-- 自动使用索引，SQL语句无变化，效率提高 
select * from emp where ename = 'BLAKE' 
-- 修改索引  先删除再创建   
-- 删除索引 
alter table emp drop index index_emp_name  
drop index index_emp_name on emp  
-- 删除了给ename列建立的索引，所以此时是逐个比较，依次查询，效率低下 
select * from emp order by ename;   
-- 没有对应的索引，此时的排序，效率低下 
select * from emp order by sal,hiredate   
-- 创建索引 
create index index_emp_sal_desc_hiredate on emp(sal desc,hiredate)  
-- 使用索引即可（索引肯定是有序），效率提高 
select * from emp order by sal,hiredate