oracle多表关联查询创建视图排序,【第31天】Oracle中的分页、联合查询的关键字、序列、视图、表空间...

1 伪列与分页

1.1 rowid和rownum

类似之前说过的伪表(dual)(Oracle中一个不存在的表，只用于测试)，引入一个伪列的概念，意义为假列，根本不存在的列，只在数据库查询表时添加。

伪列中的rownum，意为行号(每一行的在表中的序号)，分页的依据。这是Oracle根据查询动态结果动态的编号，用来实现分页查询。是一个有序的整数列，每多一条rownum自动加1。注意，rownum不能和order by在一个查询语句中，因为order by会打乱select的查询顺序；不能使用表名.rownum的写法；如果rownum在where之后则只能出现rownum>0、rownum>=1或rownum<= 任意正整数。

每一条数据都有一个唯一的rowid，映射每一行数据的物理地址的唯一标识，确定一条数据的唯一性，适用于删除完全重复的数据。

例：删除员工姓名重复的数据(删的是添加相对较早的一条)

delete from emp where rowid not in (select min(rowid) from emp group by ename);

1.2 分页

要对emp表实现查询前10条、第11-20条、第21-30条数据，因rownum的限制，无法设定除0、1以外的数字设为下限，只能设置上限，所以要取非从0开始的数据，需要嵌套子查询。

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

select rownum, empno, ename, deptno from emp where rownum between 1 and 10;

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

select rownum, a.* from (select rownum rn, empno, ename, deptno from emp where rownum <=20) a where rn >= 11;

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

select rownum,a.* from (select rownum rn, empno, ename, deptno from emp where rownum <=30) a where rn >= 21;

排序效果的分页

select rownum,a.* from (select rownum rn, empno, ename, deptno from (select * from emp order by content) where rownum <= 30) a where rn >= 21;

应用：删除表内第五条到第十条数据(为了防止删除完全一样的数据，要使用rowid唯一性来删除)

delete from emp where rowid in (select ri from (select rownum rn, rowid ri from emp where rownum <=10) where rn>=5);

第一个select使用ri；第二个select中的rn、ri是原本具有特殊含义的rownum，rowid加了别名，加别名之后的rn、ri之后都不具有特殊含义了，变为输出的结果集，是普通的字段数据。

1.3 分页在JSP中如何使用

curpage：当前页

pagesize：每页行数

当前页的开始数：int startpage = (curpage-1) * pagesize + 1

当前页的截止数：int endpage = curpage * pagesize

2 联合查询语句的关键字

2.1 求并集

union 返回结果唯一，并对结果去重

查询学校表中校名以“大”结尾和以“北”开头的数据，过滤重复数据

select * from school where name like ‘%大’ union select * from school where name like ‘北%’;

union all 返回结果不唯一

查询学校表中校名以“大”结尾和以“北”开头的数据，全显示

select * from school where name like ‘%大’ union all select * from school where name like ‘北%’;

2.2 求交集

intersect

查询学校表中校名以“大”结尾并以“北”开头的数据

select * from school where name like ‘%大’ intersect select * from school where name like ‘北%’;

2.3 求差集

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

select * from school where name like ‘%大’ minus select * from school where name like ‘北%’;

3 序列

sequence，一个单独的数据对象，是一个能够生成有序的整数列值的对象。Oracle通过调用序列来实现主键自增，在一个新的会话中，必须调用下一个值(序列名.nextval)才能获得下一个添加的值。

一个序列对象，可以让多个表共用，但需要注意多个表操作一个序列会使序列的序号错乱。跟集合的迭代器的.next一样，只要调用一次nextVal，序号就会自增1，再insert id就会出现断层，慎用。

创建序列：

create sequence 序列名

increment by 1 --增长，一次增1

start with 1 --从1开始

minvalue 1 --最小值

maxvalue 100 --最大值

cycle --循环 [默认 nocycle]

nocache; --不缓存

如果表中有主键约束且位于加序列的这个字段上，循环就不要再使用cycle了，因为序列超过最大值之后会自动变为最小值，这可能会使主键重复。start with只是确定第一次循环从哪一个序号开始，如果使用了循环且序号超过了最大值，序号会从最小值开始而不是start with的值。

在创建表后插入数据时使用

create table 表名(id number(3),name varchar2(20));

insert into 表名 values(序列名.nextval,‘苹果’);

查看序列当前值：

select 序列名.currval from dual;

获取序列的下一个值：

select 序列名.nextval from dual;

查看当前用户下有多少序列：

select * from user_sequences;

如何修改序列(修改序列的序号一次增长多少)：

alter sequence 序列名 increment by 50;

如何删除序列：

drop sequence 序列名;

S1 消除序列延迟创建*特性()

alter system set deferred_segment_creation = false;

S2 创建表

create table 表名(id number(3),name varchar2(20));

S3 创建序列

create sequence 序列名;

S4.增加数据

insert into 表名 values(序列名.nextval,‘苹果’);

S5.查询数据

select * from 表名;

*序列延迟创建：11G新增的序列延迟的新特性，自动帮着创建好1，在创建时赋一个较小的内存，在插入新的数据后再将分配予的内存扩大，此时插入的序列值nextval从2开始增长，但是我们还是习惯序号从1开始，需要关闭新特性，此时系统默认先分配20个缓存，超过20时再新增20个缓存，不会再灵活变大。

4 视图

是一张假表，用查询结果动态生成一张表。视图是编译后将查询结果保存在数据库中，下次调用视图，可以不用编译，直接获取数据。

创建方式：

create view 视图名 as select语句(用于想要在视图中显示的结果集);

查看方式：

select * from 视图名;

为什么要使用视图？

节省编译时间，提高查询效率

屏蔽原表中的字段，避免没有权限的用户查看其他字段

视图的数据能够动态来源于原表(当原表数据改变，使用alter view 视图名 compile更新视图中的数据)

5 表空间

tablespace，Oracle当中每个用户都有属于自己的默认的空间。在一段内存中多数存储的是表，所以称为表空间。

分类

用户的表空间

系统用户的表空间

普通用户的表空间

为什么要给普通用户分配属于自己的表空间？

项目中很可能多个系统使用同一个数据库，多个用户在访问同一个数据库的时候会产生资源争用问题，给不同的用户指定不同的表空间，让他们使用不同的文件，就会解决资源争用问题。

创建表空间

create tablespace 表空间名

datafile 'D:\table_user.dbf' --表空间存放文件位置，文件位置目录若不存在，不会自动创建

size 5M --表空间的初始大小

autoextend on next 5M --表空间满时下一次扩展多少

maxsize 100M; --表空间最大值

创建用户并指定默认的表空间：

create user 用户名 identified by 用户密码 default tablespace 被指定的表空间名;

创建用户时未指定默认的表空间：

create user 用户名 identified by 用户密码;

创建用户后修改表空间：

alter user 用户名 default tablespace 修改后指定的表空间名;

删除表空间：

drop tablespace 表空间名 including contents and datafiles;

删除表空间后，原先指向该表空间的用户仍然默认指向该表空间的位置，需要通过alter user命令将用户的表空间指向一个有效的表空间位置。