oracle学习篇七：约束

级联删除：如果希望在删除主表时，关联删除子表级联

drop table person;
drop table book;
create table person(
pid varchar2(20),
name varchar2(50) not null,
age number(3),
birthday date not null,
sex varchar2(2) default ‘男‘,
constraint person_id_pk primary key(pid),
constraint person_name_uk unique(name),
constraint person_age_ck check(age between 0 and 100)
);
create table book(
bid number primary key,
bname varchar2(20) unique not null,
bprice number(5,2),
pid varchar(20),
constraint person_book_pid_fk foreign key(pid) references person(pid) on delete cascade --on delete cascade
);
insert into person(pid,name,age,birthday,sex)
values(1,‘zhangsan‘,20,to_date(‘1988-10-11‘,‘yyyy-mm-dd‘),‘男‘);
insert into book(bid,bname,bprice,pid)
values(1001,‘J2SE‘,30,1);

select * from person;
select * from book;

delete from person where pid=1;--对应子表book中pid 为1的也会删除。

6.修改约束
drop table person;
create table person(
pid varchar2(20),
name varchar2(50) not null,
age number(3),
birthday date not null,
sex varchar2(2) default ‘男‘
);

alter table person add constraint person_pid_pk primary key(pid);
alter table person add constraint person_name_uk unique(name);
alter table person add constraint person_age_ck check(age between 0 and 150);
alter table person add constraint person_age_ck check(sex in(‘男‘,‘女‘));

--增加主外键约束
drop table book;
create table book(
bid number,
bname varchar2(20),
bprice namber(5,2),
pid varchar(20)
);
alter table book add constraint book_bid_pk primary key(bid);
alter table book add constraint person_book_pid_fk foreign key(pid) references person(pid)

7.rownum 表示行号，每张表中均存在的伪列。

select rownum,ename from emp where rownum <=2;

select rownum,ename from emp where rownum between 2 and 5;--不能直接用rownum进行中间查询

--正确写法，通常用于分页查询
select * from (select rownum rn,ename from emp where rownum <5) temp where temp.rn>2;

8.集合操作
create table emp20 as select * from emp where rownum<5;
1》union 并（返回结果之和，没有重复）
select * from emp
union
select * from emp20 --返回emp中所有的14条记录

2》union all 并（返回结果之和，包含重复）
select * from emp
union all
select * from emp20 --返回18条记录

3》intersect 交（返回结果相同的部分）
select * from emp
intersect
select * from emp20 --返回4条记录

4》minus 差（返回结果差集）
select * from emp
minus
select * from emp20 --返回10 条记录

oracle学习篇七：约束

标签：