Oracle数据库4-序列，视图，索引，分页查询

Oracle的序列，视图，索引

序列

创建序列
使用 create sequence 序列名
　　特点1：默认开始是没有值的，也就是指针指在了没有值的位置。
　　特点2：序列名.nextval每次执行都会自增一次，默认步长为1
　　特点3：序列名.currval查看当前序列的值。开始是没有的。
作用：作为主键使用,动态的获取之间的值，这样新增数据的时候极大的避免了主键冲突
　　　使用的是 序列名.nextval作为主键
注意：主键是非空唯一就可以，不需要主键的值是连续的值。
　　创建默认序列
　　　　create sequence cc;--创建序列cc
　　　　select cc.currval from dual--查看序列当前值
　　　　select cc.nextval from dual--查看序列的自增后的值。
　　创建自定义序列
　　　　create sequence aa--创建序列
　　　　start with 5      --设置开始位置
　　　　increment by 2    --设置步长
　　创建测试表
　　　　create table teacher(
　　　　　　tid number(10) primary key,
　　　　　　tname varchar(100) not null
　　　　)
　　　　insert into teacher values(cc.nextval,'张三');
删除序列
　　drop sequence 序列名
　　drop sequence aa

索引

作用：提升查询效率
使用索引：
　　创建
　　　　create index 索引名 on 表名(字段名)

　　　　create index index_teacher_tname on teacher(tname)--创建索引
　　删除索引
　　　　drop index 索引名

　　　　drop index index_teacher_tname--删除索引
　　特点：
　　　　显示的创建，隐式的执行
注意：
　　oracle会自动给表的主键创建索引

视图

使用视图：
　　创建视图
　　　　create view 视图名 as select 对外提供的内容 from 真实表名

　　　　create view stu as select sno,sname,sage from  su.student
　　删除视图
　　　　drop view 视图名

　　　　drop view stu
　　视图特点：
　　　　特点1：保护真实表，隐藏重要字段的数据。保护数据。
　　　　特点2：在视图中的操作会映射执行到真实表中
　　　　特点3：可以手动开启只读模式 使用关键字 with read only

　　　　create view stu2 as select sno,sname,sage from  student with read only

　　　　update stu2 set sname='wollo' where sno=1;　　--开启只读模式不可对视图进行修改
注意：视图的创建必须拥有dba权限
　　grant dba to su

Oracle的分页查询

当一个表中的数据量特别大的时候，如果一次性全部显示给用户，则造成页面过于庞大，体验极差。
解决：使用分页查询
使用：
　　rownum关键字：oracle对外提供的自动给查询结果编号的关键字，与每行的数据没有关系。
注意：rownum关键字只能做< <=的判断，不能进行> >=的判断
　　select rownum ,e.* from emp e;
　　查询员工信息的前5条数据 第一页数据
　　select rownum r,e.* from emp e where rownum <=5;
　　select * from (select rownum r,e.* from emp e where rownum <=5) t where r>0;
　　查询员工信息的6-10条数据 第二页数据
　　select rownum r,e.* from emp e where rownum <=10;
　　select rownum,t.* from (select rownum r,e.* from emp e where rownum <=10) t where r>5;
　　查询员工信息的11-15条数据 第三页数据
　　select rownum r,e. * from emp e where rownum<=15;
　　select * from (select rownum r,e. * from emp e where rownum<=15) t where r>10;
分页规律总结：每页显示m条数据，查询第n页数据
　　select * from (select rownum r,e. * from 要分页的表 e where rownum<=m*n) t where r>m*n-m ;
　　要分页的表既可以是真实的表，也可以是一个查询语句
　　分页查询员工信息按照工资排序
　　select * from (select rownum r,t.* from (select * from emp  order by sal) t where rownum<=10 ) where r>5

Oracle的其他语句

oracle的 listagg() WITHIN GROUP () 行转列函数的使用

1.使用条件查询：查询部门为20的员工列表

-- 查询部门为20的员工列表
SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20' ;

2.使用  listagg() WITHIN GROUP ()  将多行合并成一行(比较常用)

SELECT
    T .DEPTNO,
    listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) names
FROM
    SCOTT.EMP T
WHERE
    T .DEPTNO = '20'
GROUP BY
    T .DEPTNO

 3. 使用 listagg() within GROUP () over  将多行记录在一行显示

SELECT
    T .DEPTNO,
    listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME)  over(PARTITION BY T .DEPTNO)
FROM
    SCOTT.EMP T
WHERE
    T .DEPTNO = '20'