请免费分享！严禁谋利！ Oracle学习笔记
常彦博
十五、排名分页问题
15.1什么是rownum
rownum是一个伪列，对查询返回的行编号即行号，由1开始依次递增。
? 注意事项：关键点：Oracle的rownum数值是在获取每行之后才赋予的！
15.2 where rownum<=5的执行过程
1）Oracle获取第一个符合条件的1行，将它叫做第一行。
2）有5行了吗？如果没有，Oracle就再返回行，因为它要满足行号小于等于5的条件。如果到了5行，那么Oracle就不再返回行。 3）Oracle获取下一行，并递增行号（从2到3再到4再到5?）。 4）返回到第2步。 15.3 where rownum=5的执行过程 1）由于Oracle没有获取到第一个符合条件的1行，即第一行。 2）所以Oracle无法获取下一行，即无法从编号为1的第一行开始递增行号（从2到3再到4再到5?）。 3）最终结果为空。 eg1：找出帐务信息表的前三条记录 select rownum,id,real_name,create_date from account where rownum <= 3; eg2：找出帐务信息表的第四条到第六条记录？ select rn,real_name from (select rownum rn,real_name from account where rownum<=6) where rn>=4; ? 注意事项：此时rownum必须有别名，否则结果将按照子查询后的表进行伪列查找，结果就为空了。 eg3：最晚开通系统的前三个客户？ select rownum,real_name,create_date from (select real_name,create_date from account order by create_date desc) where rownum<=3; ? 注意事项：要先排序后过滤，注意where、order by的执行顺序。 eg4：最晚开通系统的第四到第六名客户？ select rn,real_name,create_date from (select rownum rn,real_name,create_date from (select real_name,create_date from account order by create_date desc) where rownum<=6) where rn>=4; 30
请免费分享！严禁谋利！ Oracle学习笔记
常彦博
十六、约束constraint
面临的问题：某列必须有值而且唯一；某列的取值受到另一列的限制。
数据库提供的解决方法：限制无效的数据进入到表中；数据库层面的“安检”。
16.1约束的类型
primary key、not null、unique key、references foreign key、check
16.2 primary key：主键约束
一张表只能有一个主键约束，其他约束没有这个限制！ 1）创建主键约束
①列级约束：
create table test(
c1 number(2) constraint test_c1_pk primary key, c2 number); ②表级约束：
create table test( c1 number(2), c2 number,
constraint test_c1_pk primary key(c1)); 2）主键约束解决的问题是：不允许表中有null记录，不允许表中有重复的记录 3）增加删除操作
①增加一列：alter table test add(同建表时的列定义方法，表级约束语法); ②删除一列：alter table test drop(列名); ③删除约束：alter table test drop constraint 约束名; ④删除主键约束：alter table test drop primary key; ⑤增加主键约束：alter table test add constraint 约束名 primary key(列名); ? 注意事项：
? 约束一定要有名字，自己不写，系统也会加上的“SYS_C+一堆数字”。 ? 约束名是在同一用户下的，因此不能有相同的名称，建议“表名_列名_约
束类型”。 16.3 not null：非空约束
不允许将该列的值置为空，只有列级约束形式。
1）创建非空约束
create table test(
c1 number constraint test_c1_pk primary key, c2 number not null); 2）修改非空约束：
alter table test modify(c1 null);not null->null
alter table test modify(c1 default 1 not null);null->not null 默认、缺省值为1
16.4 unique key：唯一建约束
一张表可有多个唯一建约束！
31
请免费分享！严禁谋利！ Oracle学习笔记
常彦博
1）创建唯一建约束
①列级约束：
create table test(
c1 number constraint test_c1_pk primary key, c2 number constraint test_c2_uk unique,
c3 number constraint test_c3_uk unique);注意，unique后没有key单词 ②表级约束：
create table test(
c1 number constraint test_c1_pk primary key, c2 number , c3 number , constraint test_c3_uk unique(c2,c3)); ? 注意事项： ? 唯一性约束和主键约束的共同点是都能保证列值的唯一性，都可以被外键列引用。 ? 联合唯一键约束与在两列上分别定义唯一键约束不一样。 2）唯一建的列值不允许重复。 3）唯一建上的任意一列的取值允许为空，并且可以是多个null值。 4）primary key = unique key + not null，表示主键列要求非空，而唯一键列允许为null，并且可以是多个null值。 eg：用DDL语句创建一张表，有三列，每列的值都要求唯一且非空。 create table test( c1 number constraint test_c1_pk primary key, c2 number not null constraint test_c2_uk unique, c3 number not null constraint test_c3_uk unique); 16.5 references foreign key：外键约束 1）创建外键约束 ①列级约束： create table child( c1 number(2) constraint child_c1_pk primary key, c2 number(3) constraint child_c2_fk references parent(c1));注意references有“s” ②表级约束1： create table child( c1 number(2) constraint child_c1_pk primary key, c2 number(3) constraint child_c2_fk foreign key(c2) references parent(c1));
表级定义外键约束时用foreign key
③表级约束2：
create table child1(
c1 number(2) constraint child1_c1_pk primary key,
c2 number(3) constraint child1_c2_fk references parent(c1) on delete cascade)； ④表级约束3：
create table child2(
c1 number(2) constraint child1_c1_pk primary key,
c2 number(3) constraint child1_c2_fk references parent(c1) on delete set null)；
32
请免费分享！严禁谋利！ Oracle学习笔记
常彦博
2）外键约束的作用：外键约束是用来解决一对多关系的。 3）外键约束下父子表的联系：
①建立外键约束：首先先创建父表，并且引用的列必须唯一键或主键，至于被引用
的列本身是否为null，外键约束并没有要求。然后子表再去创建外键约束。
②插入外键约束列中的值：首先先向父表中被引用列插入值，然后再向子表中外键
约束列插入值。
③删除外键约束列中的值：首先先删除子表中外键约束列的值，然后再删除父表中
被引用列的值。
④删除表：首先先删除子表，然后再删除父表 ? 注意事项：
? 通过外键FK可以与同一张表的主键PK或唯一键UK建立引用关系，也可
以与不同表的主键PK或唯一键UK建立引用关系。 ? 外键的取值必须匹配父表中已有的值或空值。 4）删除外键约束：alter table child drop constraint child_c2_fk; 5）cascade constraints：级联约束
通过级联约束删除表：drop table parent cascade constraints purge;通过级联约束删表不用管父子表的约束，直接删表。否则要先删子表，再删父表。
? 注意事项：
? 此语句constraints有“s”！
? 等价于执行两个动作：alter table child（子表） drop constraint child_c2_fk（子表中的约束）; drop table parent（父表） purge; 6）on delete cascade：级联删除
即在删除父表记录的时候，系统会将子表的记录先删除，再删除父表的记录。 7）on delete set null：将子表中的记录某列置空，再删除父表中的记录。
insert into parent values (1); insert into child2 values (1,1); delete from parent where c1 = 1; 相当于以下两条语句：
update child2 set c2 = null where c2 = 1; delete from parent where c1 = 1; 8）外键约束关键字： ①foreign key：用表级约束定义外键时使用该关键字。 ②references：表示引用父表中的某列。
③on delete cascade：级联删除，删除父表的记录前，先删除子表里的相关记录。 ④on delete set null：删除父表的记录前，先将子表中的外键列的相关值置空。 9）update语句：更新表中已经存在的记录，即修改记录的某列的值。
update tabname set colname=value[,colname=value] [where condition] delete语句：删除已经存在的记录。 delete [from] tabname [where condition]; ? 注意事项：
? integrity constraint：看到这个就是违反了外键约束。 ? 被引用的记录应先放入表中。
33
请免费分享！严禁谋利！ Oracle学习笔记
常彦博
16.6 check：检查约束
1）创建检查约束
①列级约束：
create table test(
c1 number(3) constraint test_c1_pk primary key,
c2 number(3) constraint test_c2_ck check ( c2 > 100 ));
②表级约束：
create table test(
c1 number(3) constraint test_c1_pk primary key, c2 number(2) ,
c3 number(2) ,constraint test_c2_ck check (( c2 + c3 ) > 100 )); 2）定义条件表达式，每个列值必须满足该条件。 3）以下表达式不允许
①伪列：currval、nextval、level、rownum ②函数：sysdate、uid、user、userenv ③引用其他记录的其他值
4）在create table时定义约束，表已经存在，用alter table追加约束。
34