Oracle---排序、分组、表连接

一、排序查询

使用ORDER BY子句排序
ASC：升序(默认情况下)
DESC：降序
ORDER BY 子句在SELECT语句的结尾
例1：要求按照工资由低到高排序

select *
from emp
order by sal;

例2：检索emp表中所有的数据，并按照部门编号，员工编号来排序

select deptno,empno,ename
from emp
order by deptno,empno desc;

select *
from emp
order by deptno asc,empno desc;

二、分组查询

格式：

select 列名,聚合函数
from 表名
group by 列名;

注意：select后面可以写列名，但是写的这个列，必须是分组的列

例1：
在emp表中，通过分组的方式计算每个部门的平均工资

select deptno as 部门编号,avg(sal) as 平均工资
from emp
group by deptno;

例2：在emp表中，先通过分组的方式计算出每个部门的平均工资，然后通过having过滤掉平均工资大于2000的记录信息

select deptno as 部门编号,avg(sal) 平均工资
from emp
group by deptno 
having avg(sak)<=2000;

完整的查询结构：

select 列名
from 表名
where 条件
group by 分组的列
having 分组的条件
order by 列名;

三、多表关联查询—表连接

1、内连接

和MySQL的写法要求是一模一样的。

在多表关联查询时，如果多个表之间存在同名的列，则必须使用表别名来限定列的引用
格式：

• where

select 表名.列名
from 表名1,表名2,表名3...
where 表名1.列=表名2.列 and 表名2.列=表名3.列...

• on

select 表名.列名
from 表名1 inner join 表名2
on 表名1.列=表名2.列 inner join 表名3 on表名2.列=表名3.列...

内连接的查询特征：只有两个表中的数据互相匹配到的情况下，才会有查询结果

例：在Scott模式下，通过deptno列来关联emp和dept表，并检索这两个表中相关字段的信息。

select e.empno as 员工编号,e.ename as 员工名称,d.dname as 部门
from emp e,dept d
where e.deptno=d.deptno;

select empno,ename,dname
from emp inner join dept
on emp.deptno=dept.deptno;

2、外连接

至少会返回一个表的所有行，outer可选可写

• 左外连接：left outer join
  返回的是左表的所有行
• 右外连接：right outer join
  返回的是左表的所有行
• 完全链接：full outer join
  返回的是两个表的所有行

例：实现emp和dept表的外部连接
左连接

select empno,ename,dname
from emp left outer join dept
on emp.deptno=dept.deptno;

右连接

select empno,ename,dname
from emp right outer join dept
on emp.deptno=dept.deptno;

完全链接

select empno,ename,dname
from emp full outer join dept
on emp.deptno=dept.deptno;

3、自然连接

自然连接是指在检索多个表的时候，Oracle会将第一个表中的列与第二个表中具有相同名称的列进行自动连接，在自然连接中，用户不需要明确指定进行连接的列，这个任务由Oracle系统自动完成，自然连接用“natural join”关键字

例：在emp表中检索工资大于2000的记录，并实现emp与dept的自然连接

select empno,ename,job,dname
from emp natural join dept
where sal>2000;

由于自然连接强制要求表之间具有相同的列名称，所以在设计表的时候会出现不可预知的错误，所以在实际中很少用到。