Oracle中的伪列.约束.视图和序列

一.伪列rownum

1.理解

表中实际没有该列，但是可以查出来

2.概念

每次查询时，返回结果集的顺序号，这个顺序号是在记录输出时才一步一步产生的，第一行显示为1，第二行为2，以此类推 

注：ROWNUM和ORDER BY一起使用时，因为ROWNUM在记录输出时生成，而ORDER BY子句在最后执行，所以当两者一起使用时，需要注意ROWNUM实际是已经被排了序的ROWNUM

3.TOP-N查询

Top-N查询主要是实现表中按照某个列排序，输出最大或最小的N条记录功能

如取工资最低的前5名信息

select d.*,rownum 
from (select ename,sal from emp order by sal) d 
where rownum<=5

4.分页功能

1.单表分页

select d.*
from (select e.*,rownum r from emp e) d
where r>5 and r<10

2.多表分页

select b.*
from (select e.ename,e.sal,d.dname,rownum r
from emp e,dept d
where e.deptno=d.deptno and e.sal>2000) b
where r>0 and r<=5

二.约束 Constraint

1.定义

是定义在表上的一种强制规则。

2.作用

当为某个表定义约束后，对该表做的所有SQL操作都必须满足约束的规则要求，否则操作将失败

3.约束的类型

1.not null 非空约束

只能添加在列级别

create table student2(
id number(4),
name varchar2(9) not null
);

2.unique 唯一性约束

指定列或者列的组合的所有行数据必须唯一

--列级别创建
create table student2(
id number(4),
name varchar2(9)  constraint student2_name_uqe unique
);
--表级别创建
create table student2(
id number(4),
name varchar2(9),  
constraint student2_name_uqe unique(name)
 );

3.PRIMARY KEY 主键约束

表的每行的唯一性标识，指定列或者列的组合的所有行数据必须唯一 

--列级别创建
create table student2(
id number(4),
name varchar2(9)  constraint student2_name_pk primary key
);
--表级别创建
create table student2(
id number(4),
name varchar2(9),
constraint student2_name_pk primary key(id)  
 );

4.FOREIGN KEY 外键约束

1.在列及引用列上建立的一种强制依赖关系

2.特点

外键列的值必须在引用列值的范围内，或者为空；
外键参照的是列必须是主键或者唯一键；
主键表主键值被外键表参照时，主键表记录不允许被删除。

主表中的数据被从表引用，必须先删除从表中的数据才能删除主表中的数据
如果要删除主表那么要先删除从表

3.创建学生表  cid在班级表是主键才能在学生表中当作外键

create table student22(
id number(4),
cid number(4) constraint student22_cid_fk references class22(cid) 
--references引用
  )  

5.CHECK  

检查性约束，在列上指定一个必须满足的条件

create table student2(
id number(4) constraint student2_id_ck check(id between 10 and 20),
name varchar2(9)，sex char(2)  
constraint student2_sex_ck check(sex in('男','女'))
);

三.视图

1.定义

是一个命名的查询，用于改变基表数据的显示，通过视图能简化查询；

2.特点

1.访问方式与表相同
2.视图是逻辑上来自一个或多个表的数据集合

3.可以以别名的方式创建视图

4.创建视图时，如果子查询中有聚合函数，那么要给聚合函数起别名

注：可能会没有权限 可以用普通管理员用户赋予其权限
                         

   grant create view to scott;

3.创建一个视图

create view empz
as 
select empno,ename,job
from emp
where deptno=10;

4.修改视图  or replace

create or replace view empa
as 
select empno,ename
from emp

5.可以通过视图删除和修改数据，但是视图中不能包含分组函数和distnct

5.with read only:只读功能

视图只能执行select，不能增删改

四.序列 sequence

1.通过序列实现主键自增

2.序列的创建

         --increment by;每次增加多少
          --start with：起始值
          --maxvalue最大值
          --minvalue最小值
          --cycle是否循环
          --cache 分配内存 

   create sequence test_seq
          start with 1
          increment by 2
          maxvalue 100
          minvalue 1;

3.序列的两个基本属性

1.currval

查询当前序列值

select test_seq.currval from dual;

2.nextval

产生下一个序列值

select test_seq.nextval from dual;

注 刚创建的序列不能使用.currval属性查询，必须要通过nextval 产生下一个序列值才能使用​​​​​​​

3.通过序列实现主键递增然后为其添加数据 

insert into student10 values(test_seq.nextval,'张三',21);