oracle的学习Ⅲ（子查询、连接查询、别名、row_number()over()）

一、子查询

子查询：一个查询作为另一个查询的一部分，可出现在where、where、having子句中
子查询分为：单行子查询和多行子查询

单行子查询

查询结果为单行
例如：

// 查询工资大于7788号员工工资的雇员
select ename,sal from emp where sal>(select sal from emp where empno=7788);

多行子查询

查询结果为多行，需使用in、any、all，且any和all需要和>，<等搭配使用（例如：=any，<any，>any），最后该查询查询效率低，不过可以使用max，min函数提高效率
例如：

// 查询比20号部门任意员工工资高的员工信息。
select * from emp where sal >all (select sal from emp where deptno = 20);  	// 效率低
select * from emp where sal > (select max(sal) from emp where deptno = 20);	// 效率高

二、连接查询（多表查询）

连接查询一定要写连接条件（不然的化就是笛卡尔积了），且建议在连接条件中给属性添加归属对象（这样可以减少属性列对所属对象表的分配带来的消耗，来高效率）
连接查询分为：内连接和外连接

内连接

对查询结果只输出满足条件的记录
内连接分为：等值连接、自连接、不等连接

等值连接

两个表通过公共列（数据类型，数据长度一致）进行连接。列名不同也可以，不过其取值需要来自同一域内
例如：

select ename,dname from emp,dept;		// 笛卡儿积

// 查询每个员工的姓名及其所在部门名称
select ename,dname from emp,dept where emp.deptno=dept.deptno;	// 等值连接

自连接

一个表自己连接自己
例如：

// 查询每一个员工的姓名和其经理名。
select e.ename,m.ename from emp e,emp m  where e.mgr=m.empno;

不等连接

两个表通过非等值的连接
例如：

// 查询每一个员工的工资等级。（between and）
select emp.ename,emp.sal,salgrade.grade from emp ,salgrade  where emp.sal between salgrade.losal and salgrade.hisal;	

外连接

对查询结果不知输入满足条件的记录，还将输出部分不满足条件的记录。外连接一共有左外连接、右外连接、全外连接。
**外连接运算符"（+）"，运算符添加到缺少信息的一方，就可以完成左、右外连接，但不可完成全外连接，**全外连接需要使用SQL99来完成
例如：

// 查询每一个员工的姓名和其经理名。
select e.ename,m.ename from emp e,emp m  where e.mgr=m.empno(+); 左外连接。
select e.ename,m.ename from emp e,emp m  where m.empno(+)=e.mgr;  右外连接

事例

查询哪些员工的工资比其梭子啊部门的平均工资高？

// 子查询方法：
 select ename from emp outer where sal>(select avg(sal) from emp inner where inner.deptno=outer.deptno);

// 连接查询法
select emp.ename
from（select deptno,avg(sal) avgsal from emp group by deptno）a,emp
where a.deptno=emp.deptno and emp.sal>a.avgsal;

三、别名

• 表别名： 表名 空格 别名 。一经定义，在该查询中只能使用别名，表原名不可再用。
• 列别名： 列名 空格 别名 列名 as 别名 。 列别名只能出现在 select、 order by子句 中。

四、row_number()over()

row_number() over(partition by col1 order by col2)：
简单来说row_number()是从1开始，并对每条记录都返回一个数字，而上面的那个函数表示先根据col1分组，在分组内部进行col2的排列，而此函数计算的值表示每组内部排序后的编号，且该函数晚于where、group by、order by执行
例如：

// 查询每个部门中薪水最高得前两名雇员
select * from 			// 正确的
(select ename, row_number() over(partition by deptno order by sal desc) rownums from emp) 
where rownums < 3;

select ename, row_number() over(partition by deptno order by sal desc） rownums from emp)
where rownums < 3;		// 错误的，因为row_number() over()函数是在where执行后执行的

与rownum的区别：

• rownum先对结果集加入伪列rownum，再进行order by排序
• 而该函数时再得到了最终得结果后再排序的