Oracle之查询详解
查询是数据的一个重要操作。用户发送查询请求，经编译软件变异成二进制文件供服务器查询，后返回查询结果集给用户，查询会产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正的存储，每次执行查询只是从数据表中提取数据，并按照表的形式显示出来。
SELECT <列名>
FGROM <表名>
[WHERE <查询条件表达式>]
[GROUP BY <分组表达式>]
[HAVING <分组查询表达式>]
[ORDER BY <排序的列名> [ASC或DESC]]
group by
group by 用于对查询的结果分组统计，通过对group by后面的名字进行分组后输出结果。
select deptno,count(ename) from emp group by deptno;
统计公司每个部门的员工人数
select deptno,avg(sal),max(sal) from emp group by deptno;
显示每个部门的平均工资和最高工资
select deptno,gender,count(ename) from emp group by deptno,gender;
按照性别统计各个部门人数//需要手动添加一个gender列，并添加属性
group by后面还可以跟多列表示多列分组，在多列分组时放前面的优先分组。
group by 列名,列名
select count(*) as 人数,deptno as 部门号,job 工作 from emp group by deptno,job order by deptno;
having
having 子句用于限制分组显示结果，其只能和group by一起连用。在where中没有办法直接使用聚合函数，即sum avg等无法使用，所以引用了having，在having中可以使用这些函数。
select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;
显示工资低于2000的部门号和它的平均工资
select deptno as 部门号,count(*) as 人数 from emp group by deptno having count(*)>4;
显示部门人数大于4的部门
order by
order by 表示排序，后跟列名和排序方式。如果什么都不加默认为升序。ASC表示升序，DESC表示降序。
select empno,ename,sal from emp order by sal;
select empno,ename,sal from emp order by sal asc;
select empno,ename,sal from emp order by sal desc;
在Oracle中还可以设置多列排序
order by 列名1 升降,列名2 升降;
前面的为主要排序，后面的为次一级排序。
注：碰到自己与自己比较的情况下，不能用having，可以创建一个新列。
select depton,count(deptno) from emp group by deptno having sal>avg(sal);
select emp.deptno,count(*) from emp,(select deptno,avg(sal) avgsal from emp group by deptno) a where emp.deptno=a.deptno and emp.sal>a.avgsal group by emp.deptno;
查询每个部门中工资高于该部门平均工资的员工人数
注：如果select语句同时包含group by,having,order by,按group by,having,order by排序
分组和聚合一起使用，目的是为了统计信息。
where是为了from服务的，只能跟真实的字段，用来筛选from子句中指定的操作所产生的行
group by 用来分组where子句的输出
having 用来从分组的结果中筛选行
order by用来对筛选的结果进行排序
复杂查询
（1）分组函数：max min avg sum count
max表示该列的最大值，min表示该列的最小值，avg表示该列的平均值，sum表示该列的和，count表示该列的行数。
注：分组函数(max、min、avg、count、sum)只能出现在选择列表中having子句、order by子句、不能出现在where子句和group by子句中。
select max(sal),min(sal) from emp;
如何显示所有员工最高工资和最低工资
select avg(sal),sum(sal) from emp;
显示所有员工的平均工资和工资总和
select count(ename) from emp; //count用于计算行
计算共有多少员工
select ename,job from emp where sal=(select max(sal) from emp);
显示工资最高的员工的名字，工作岗位(max等没办法直接跟在where后面)
select ename,sal from emp where sale>(select avg(sal) from emp);
显示工资高于平均员工信息
（2）多表查询
多表查询是指两个和两个以上的表或者是视图的查询，在实际应用中，当查询单个表不能满足需求时，一般使用多表查询。如：显示sales部门位置和其员工的姓名，这种情况下需要使用到(dept表和emp表)。
多表查询的连接一般可以分为：内连接、左外连接、右外连接、全连接。
注：在使用多表查询的时候每个表可以设置别名，如果表指定了别名，那么语句中所有语句必须使用别名，而不能再使用实际表名。且在写属性的时候如果属性为其中一个表特有的属性则不需要写别名，如果是两个表都有则必须指定是哪一个表的哪个属性格式为：表名.属性名。
select 列名 from 表1 别名,表2 别名...
select d.loc,e.ename,e.job from emp e,dept d where e.deptno=d.deptno
注：e是emp的别名，d是dept的别名。
但如果对表进行了操作则需要设置别名，如：查询每个部门中工资高于该部门平均工资的员工人数。在其中有一个avg表，这个表必须设置别名（提醒：如果仅有一个被修改的表，则可以不设置别名，但如果有多个表则必须设置别名）。
select emp.deptno,count(*) from emp,(select deptno,avg(sal) avgsal from emp group by deptno) a where emp.deptno=a.deptno and emp.sal>a.avgsal group by emp.deptno;
内连接
内连接通过使用比较运算符来使每个表的通用列中的值匹配来组成一个新表，即：把两个表中间共有的那些行拿出来进行连接，如果某些行不是两个表共有的，则不进行连接。
select
from 表1
inner join 表2
on 匹配条件
或
select
from 表1 表2
where匹配条件
select * from emp,dept where emp.deptno=dept.deptno;
select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;
左外连接
左外连接与内连接的区别是：设置左外连接的时候设置了主表和附表，主表在前，附表在后。内连接是将两个表匹配的地方输出出来，而左外连接则是主表全写，附表一一对应，附表有则加上，没有不写。
select
from 表1
left join 表2
on 匹配条件
select e.ename,e.job,d.loc from dept d left join emp e on d.deptno=e.deptno;
将dept表放在前面，以dept表为主表，emp表做辅表进行链接
select e.ename,d.dname,d.loc from emp e left join dept d on e.deptno=d.deptno;
将emp表放在前面，是以emp表作为主表，dept表作为辅表进行链接
右外连接
右外连接和左外连接基本相同只是右外连接的主表写在后边。
select
from 表1
right join 表2
on 匹配条件
select e.ename,d.loc from emp e right join dept d on e.deptno=d.deptno;
这个dept表放在后面，以dept表为主表，emp做辅表进行链接
select dname,ename,sal from dept left join emp on dept.deptno=emp.deptno;
查询每个部门下的员工的姓名，工资
全连接
全连接是在等值连接的基础上将左表和右表的未匹配数据都加上，使用的关键字为full outer join或者full join。
select
from 表1
full join 表2
on 匹配条件
select e.ename,d.loc from emp e full join dept d on e.deptno=d.deptno;
自连接
还有一种特殊情况即自连接，在Oracle中一个表无法与自己进行比较，所以当需要自己表的两个信息做比较的时候也需要使用连接来连接，即同一张表的连结查询。
select a.ename 员工,b.ename 领导 from emp a,emp b where a.mgr=b.empno;
select a.ename 员工,b.ename 领导 from emp a left join emp b on a.mgr=b.empno;
（3）子查询
子查询是指嵌套在其他sql语句中的select语句，也叫嵌套查询。sql语句执行顺序为从右到左执行，所以在执行查询时会先执行左侧的子查询后进行主查询。
子查询分为单行子查询和多行子查询，单行子查询是指返回一行数据的子查询语句，多行子查询是指返回多行数据的查询语句。子查询还可以分为多列子查询、多行子查询、多列多行子查询。
在进行子查询时如果内部查询不返回任何记录，则外部条件中字段DEPTNO与NULL比较永远为假，也就是说外部查询不返回任何结果。
总结为：
单行子查询是指子查询只返回单列、单行数据
多行子查询是指返回单列多行数据，都是针对单列而言的
多列子查询则是指查询返回多个列数据的子查询语句
单行子查询
where deptno = (单行数值)
多行子查询
where deptno in ( 多行数值 )
多列子查询：
where (job,deptno)=(select job,deptno from emp where ename='KING')
多列多行子查询
where (job,deptno) in (select job,deptno from emp where ename='KING')
单行子查询
在单行子查询的外部查询中可以使用=、>、<、>=、<=、<>等比较运算符。
内部查询返回的结果必须与外部查询条件中字段(DEPTNO)相匹配。
select ename from emp where deptno=(select deptno from emp where ename='SMITH');
查询和SMITH部门相同的员工的名字
select ename,job,sal from emp where deptno=(select deptno from dept where dname='SALES');
查询出销售部(SALES)下面的员工姓名，工作，工资
select emp.deptno,count(*) from emp,(select deptno,avg(sal) avgsal from emp group by deptno) a where emp.deptno=a.deptno and emp.sal>a.avgsal group by emp.deptno;
查询每个部门中工资高于该部门平均工资的员工人数
多行子查询
在WHERE子句中使用多行子查询时，可以使用多行比较运算符（IN,ALL,ANY）。
IN：等于任何一个。
ALL：和子查询返回的所有值比较。例如：sal>ALL(1,2,3)等价于sal>3，即大于所有。
ANY：和子查询返回的任意一个值比较。例如：sal>ANY(1,2,3)等价于sal>1，即大于任意一个就可以。
注：ANY运算符必须与单行比较运算符结合使用,并且返回行只要匹配子查询的任何一个结果即可。
select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);
如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
还可以：select ename,sal,deptno from emp where sal > （select min(sal) from emp where deptno=30）;
select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30);
显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select * from emp where job in(select job from emp where ename='MARTIN' or ename='SMITH');
查询emp表中工作和MARTIN和SMITH工作相同的员工的信息
select ename,hiredate from emp where deptno=(select deptno from emp where ename='BLAKE') and ename<> 'BLAKE';
创建一个查询，显示与blake在同一部门工作的雇员的姓名和受雇日期，black不包含在内
select ename,sal,mgr from emp where mgr=(select empno from emp where ename='KING');
显示被king直接管理的雇员的姓名以及工资
多列子查询
多列子查询和多行子查询相同，只是使用多列子查询的时候会有多列进行匹配。
如何查询与smith的部门和岗位完全相同的所有雇员
select ename,job,deptno from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
select ename,job,deptno from emp where (deptno,job) in (select deptno,job from emp where ename='SMITH');
（4）集合运算
为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus。
union：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行
select ename,sal,job from emp where sal>2500 union select enmae,sal,job from emp where job='manager';
union all：该操作与union相似，但是它不会取消重复行，而且不会排序
select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='manager';
intersect：使用该操作符用于取得两个结果集的交集
select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job='manager';
minus：使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据
select enmae,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job='manager';
总结为集合运算就是将两个或者多个结果集组合成一个结果集。
intersect 交集 返回两个查询共有的记录
union all 并集 返回各个查询的所有记录，包括重复的记录
union 交集 返回各个查询的所有记录，不包括重复的记录
MINUS 补集 返回第一个查询检查出的记录减去第二个查询检索出来的记录之后剩余的记录
注意：当使用集合操作的时候，查询所返回的列数以及列的类型必须匹配，列名可以不同。
附
（1）Distinct关键字
在Oracle中，可能出现若干相同的情况，那么可以用Distinct消除重复行
select distinct deptno from emp;
（2）多表查询与单行子查询可以实现相同的功能
查询出销售部（sales）下面的员工姓名，工作，工资
使用多表连接查询的方法：
select dname,ename,job,sal from emp,dept where emp.deptno=dept.deptno and dname='SALES';
使用单行子查询：
select ename,job,sal from emp where deptno=(select deptno from dept where dname='SALES');
（3）显示高于自己部门平均工资的员工信息
分析：
1.找到所有部门的平均工资
select deptno,avg(sal) from emp group by deptno;
2.找到所有人的工资信息
select ename,sal,deptno from emp;
3.把两个结果集使用多表连接组合组合起来
select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) damao where emp.deptno=damao.deptno;
4.去掉低于平均工资的那些数据即可：
select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) damao where emp.deptno=damao.deptno and sal>avgsal;
（4）emp表介绍
字段 类型 描述
empno NUMBER(4) 表示雇员编号，是唯一编号
ename VAECHAR2(10) 表示雇员姓名
job VARCHAR2(9) 表示工作职位
mgr NUMBER(4) 表示一个雇员的领导编号
hiredate DATE 表示雇佣日期
sal NUMBER(7,2) 表示月薪，工资
comm NUMBER(7,2) 表示奖金，或者称为佣金
deptno NUMBER(2) 部门编号
