Oracle查询语句（三）

一、多表查询

多表查询   --合并 union   --交集 intersect   --减集 minus INTERSECT 操作符只返回两个查询的公共行 例如：

SELECT orderno FROM order_master
INTERSECT
SELECT orderno FROM order_detail;

MINUS 操作符返回从第一个查询结果中排除第二个查询中出现的行 例如：

SELECT orderno FROM order_master
MINUS
SELECT orderno FROM order_detail;

二、关联查询

关联查询   --内、外连接   --内连接inner join   --外连接right / left / full outer join

内连接

select x.emp, x.ena, y.dep, y.dna
from em x
join de y
on x.dep = y.dep;

from 后直接接两张表名 系统会将两张表做笛卡尔乘积操作 会生成一张笛卡尔积中间表（这张表相当于两个集合做乘操作 参考两个集合相乘） 如果两张表都很大的话中间表就会很大 ,会占用很大的内存空间

内链接只能查询到符合条件的结果 左外连接可以使某一张表或两张表的数据完全显示出来

外连接 left join 和right join还可以用（+）来代替 (+)应放在缺少相应信息的一边

左连接的主表是join左边的表为主表，内容会全部显示

select x.emp, x.ena, y.dep, y.dna from em x left join de y on x.dep = y.dep; 

左外链接的加号写法

select x.emp, x.ena, y.dep, y.dna from em x, de y where x.dep = y.dep(+);

右连接的主表是join右边的表为主表，内容会全部显示

select x.emp, x.ena, y.dep, y.dna from em x right join de y on x.dep = y.dep ;

右外链接的加号写法

select x.emp, x.ena, y.dep, y.dna from em x, de y where x.dep(+) = y.dep

三、Case语句

CASE用法1

CASE 表达式/字段/变量...
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
...
WHEN 值N THEN 结果N
ELSE 默认结果
END

CASE用法2

CASE
WHEN 条件表达式1 THEN 结果1
WHEN 条件表达式2 THEN 结果2
...
WHEN 条件表达式N THEN 结果N
ELSE 默认结果
END

四、别名

使用列别名   --为列表达式提供不同的名称   --该别名指定了列标题

例如：

SELECT c1,c2,c3*2 AS x FROM t;

SELECT c1,c2,c3*2 "x" FROM t;

五、唯一性

唯一数据 distinct / unique

伪列rowid

例如： 选择无重复的行,使用DISTINCT关键字

SELECT DISTINCT c1 FROM t;

给每行增加一个唯一标识：rowid

SELECT rowid,c1 FROM t;

六、行号

oracle中没有像MySQL中limit函数以及SQLServer中的top关键字等，所以只能通过伪列的方式去满足分页功能

rownum伪列是Oracle首先进行查询获取到结果集之后在加上去的一个伪列，这个伪列对符合条件的结果添加一个从1开始的序列号ROWNUM是动态的，也就是必须先有查询到的结果集，然后再给这个结果集加上一个列，比如给结果集中的第一条数据的ROWNUM的值为1

select rownum as "行号",tname from tab;

七、排名

按字段排序后给出连续排名号 row_number() over(order by 字段 desc/asc)

例如：

select ename,job,deptno,sal,
row_number( ) over(order by sal desc) as "排名"
from scott.emp;

按字段排序，对重复数据给出并列排名号，排名有跳号不连续 rank() over(order by 字段 desc/asc) 例如：

select ename,deptno,sal,
rank( ) over(order by sal desc) as "排名"
from scott.emp;

按字段排序，对重复数据给出并列排名号，排名连续 dense_rank() over(order by 字段 desc/asc) 例如：

select ename,deptno,sal,
dense_rank( ) over(order by sal desc) as "排名"
from scott.emp;

八、序列

Sequence 是oracle提供的用于产生一系列唯一数字的数据库对象。由于oracle中没有设置自增列的方法，所以我们在oracle数据库中主要用序列来实现主键自增的功能

创建序列：

CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默认是 1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值  
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中

使用序列：

//序列调用 产生一个新的序列
select seq_test.nextval from dual
//查看当前序列的值
select seq_test.currval from dual