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

绝地灬酷狼 2022-12-10 10:16 158阅读 0赞

# 一、子查询 #

**子查询**：一个查询作为另一个查询的一部分，可出现在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排序**
 *  而该函数时再得到了最终**得结果后再排序的**