oracle从入门到精通第四篇(伪表|伪列|Rownum|联合关键字|序列)

伪表（DUAL 表）

该表主要目的是为了保证在使用SELECT语句中的语句的完整性而提供的。

一般用于验证函数。例如：

select sysdate,to_char(sysdate,'yyyy-mm-dd HH24:mm:ss') from dual
1

伪列（RowID）

rowid = 是映射数据库中每一条数据物理地址的唯一标识

适用于删除完全重复的数据

delete from lyric where rowid not in (select min(rowid) from lyric group by content);
1

行号（Rownum）

这是Oracle根据查询结果动态的编号，用来实现分页查询
1）有序的整数列，每多一条自动加1
2）不能和order by在同一个查询语句中，order by会打乱查询的顺序
3）不能使用表名.rownum的写法
4）如果rownum用在where之后，rownum >= 1 rownum<= 任意值

1.实现第一页数据，编号1-10

SELECT
	rownum,
	substr( content, 1, 10 ) || '...' 
FROM
	lyric 
WHERE
	rownum BETWEEN 1 
	AND 10;
1
2
3
4
5
6
7
8

select rownum,content from lyric where rownum between 1 and 10;
1

2.实现第二页数据，编号11-20

SELECT
	* 
FROM
	( SELECT rownum rn, content FROM lyric WHERE rownum <= 20 ) a 
WHERE
	a.rn >= 11;
1
2
3
4
5
6

3.实现第三页数据，编号21-30

SELECT
	* 
FROM
	( SELECT rownum rn, content FROM lyric WHERE rownum <= 30 ) a 
WHERE
	a.rn >= 21;
1
2
3
4
5
6

4.实现带有排序效果的分页

SELECT
	* 
FROM
	( SELECT rownum rn, content FROM ( SELECT * FROM lyric ORDER BY content ) WHERE rownum <= 20 ) a 
WHERE
	a.rn >= 11;
1
2
3
4
5
6

5.我们常见的分页是如何实现的：
curpage：当前页 2 3
pagesize：每页行数 10 25
当前页的开始数：(curpage -1) * pagesize +1 11 51
当前页的截止数：curpage * pagesize 20 75

联合关键字：

union：结果唯一

select name from school where name like '%大'
union
select name from school where name like '山%';
1
2
3

union all：结果不唯一

select name from school where name like '%大'
union all
select name from school where name like '山%';
1
2
3

intersect：求交集

select * from school where name like '%大'
intersect
select * from school where name like '山%';
1
2
3

minus：从第一个查询结果集中减去第二个查询结果中重复的数据

select * from school where name like '%大'
minus
select * from school where name like '山%';
1
2
3

in：表示符合查询结果中的任意一个值

条件 in (值1，值2…)
相当于：条件 = 值1 or 条件 = 值2 or …

select * from school where id in (select sid from student);
1

not in：表示条件不能是查询结果中的任意一个值

条件 not in (值1，值2…)
相当于：条件 <> 值1 and 条件 <> 值2 and …

select * from school where id not in (select sid from student);
1

some/any：这个和in用法相同

in用在无符号的情况
some/any用在有符号的情况

 select * from school where id = some (select sid from student);
 select * from school where id < any (select sid from student);
1
2

all：表示比所有值都大，比所有值都小

select * from school where id > all (select sid from student);
1

***：

any >min

练习
查询工资高于20号部门某个员工工资的员工的信息

*：因为in的效率较低，所以用exists代替
exists：存在

select * from school where exists (select sid from student where student.sid = school.id);
1

not exists：不存在

select * from school where not exists (select sid from student where student.sid = school.id);
1

序列：sequence

一个单独的数据对象，每多一条自动加一
*：Oracle通过调用序列来实现主键自增
*：在一个新的会话中，必须调用下一个值才能查看当前值
*：序列调用下一个值，会作为下次调用的初始值

创建序列：

create sequence seq_test – 序列名
increment by 1 – 一次增长1
start with 1 – 从1开始
minvalue 1 – 最小值
maxvalue 100 – 最大值
cycle – 循环 默认不循环
nocache; --不缓存 默认缓存20

查看序列的下一个值：

select seq_test.nextval from dual;
1

查看序列的当前值：

select seq_test.currval from dual;
1

1.消除延迟段创建特性

alter system set deferred_segment_creation = false;
1

2.创建表

create table test2(id number(3),name varchar2(20));
1

3.创建序列

create sequence seq_test2;
1

4.新增数据

insert into test2 values(seq_test2.nextval,'橘子');
insert into test2 values(seq_test2.nextval,'苹果');
1
2

5.查询数据

select * from test2;
1

查看当前用户下有哪些序列：

select * from user_sequences;
1

如何修改序列：

alter sequence seq_test increment by 50;
1

如何删除序列：

drop sequence seq_test;
1