【第31天】Oracle的分页、联合查询的关键字、序列、视图、表空间

• 1 伪列与分页

  • 1.1 rowid和rownum

  • 1.2 分页

  • 1.3 分页在JSP中如何使用

• 2 联合查询语句的关键字

  • 2.1 求并集

  • 2.2 求交集

  • 2.3 求差集

• 3 序列

• 4 视图

• 5 表空间

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命令将用户的表空间指向一个有效的表空间位置。