oracle培训第三天

1.约束

约束是数据库能够实现业务规则以及保证数据遵循ER模型的一种手段。

约束的语法

列级约束：只能引用一个列，表中可以有多个列级约束

表级约束：引用一个或多个列，通常用来定义主键

追加约束:建表后，再通过alter table追加约束

select * from user_constraints；

select * from user_cons_columns；

五种约束的语法

非空约束

列级定义

create table student(no int not null,name varchar(40));

追加非空约束

alter table emp1 modify ename not null;

select * from user_constraints

alter table emp1 modify ename null

alter table emp1 modify ename constraint c_emp1_ename not null;

唯一性约束

drop table student;

create table student(no int unique,name varchar(40));

insert into student

values(null,'zzl');

values(null,'z');

values(1,'zzl');

values(1,'z');

表级定义

create table student(no int ,name varchar(40),constraint c_uno unique(no));

追加定义

create table student(no int ,name varchar(40));

alter table student add constraint c_uno unique(no);

主键约束

每个表只能建立一个主键约束 primary key = unique + not null

主键列上需要索引，如果该列没有索引，则自动创建unique索引，

主键约束和唯一约束不能同时建立在一个列上

主键约束的六种写法；

drop table student

create table student(no int primary key,name varchar(40));

create table student(no int constraint pk_student primary key,name varchar(40));

create table student(no int,name varchar(40),primary key (no));

create table student(no int,name varchar(40),constraint pk_studnet primary key (no));

create table student(no int,name varchar(40));

alter table student add primary key (no);

alter table student add constraint pk_student primary key (no);

主键和索引关联的问题

create index index_student_no on student(no);

alter table student add constraint pk_no primary key (no);

alter table student add constraint pk_no primary key (no) using index index_student_no;

alter table student drop constraint pk_no;

select * from user_indexes where table_name ='STUDENT';

create table student(no int,name varchar(40),constraint pk_studnet primary key (no) using index(create index index_student_no on student(no)

));

外键约束

作用，是为了和同一个表或其他表的主键建立连接关系，外键值必须和父表中的值匹配或者为空

外键约束和unique约束都可以有空值

外键需要参考主键的约束，但也可以参考唯一键约束

外键和主键一般分别在两个不同的表中，但也可以同处在一个表中

drop table emp1;

create table emp1 as select * from emp;

create table dept1 as select * from dept;

列级定义：

alter table dept1 add constraint pk_dept1 primary key (deptno);

create table emp2 (empno int ,deptno int references dept1(deptno),deptno2 int);

create table emp3(empno int ,deptno int,foreign key (deptno) references dept1(deptno));

alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno);

insert into emp3 values(1,99);

insert into emp3 values(1,null);

delete from dept1 where deptno =10;

alter table emp1 drop constraint fk_emp1;

alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) on delete cascade;

select * from emp1;

alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) on delete set null;

check 约束

create table emp4(empno int ,sal int check(sal>0),comm int)

create table emp5(empno int ,sal int,comm int， check(sal>5000))

alter table emp5 add constraint e_no_ck check(empno is not null)

insert into emp5

values(null,1,1);

check约束中的表达式中不能使用变量日期函数

alter table emp1 add constraint emp_chk_date check (hiredate < sysdate);

alter table emp1 add constraint emp_chk_date check (hiredate < to_date('2016.08.16','yyyy.mm.dd'))

级联约束

drop table test;

create table test(

pk number primary key,

fk number,

col1 number,

col2 number,

constraint fk_constraint foreign key (fk) references test,

constraint ck1 check(pk >0 and col1 >0),

constraint ck2 check(col2 > 0)

)

alter table test drop column col2;

alter table test drop column fk;

alter table test drop column pk;

alter table test drop column col1;

alter table test drop column pk cascade constraint//级联

alter table test drop column col1 cascade constraint

约束的四种状态

enable validate :无法输入违反约束的行，而且表中所有行都要符合约束

enable novalidate：表中可以存在不符合约束的状态，但对新加入数据必须符合约束条件

disable novalidate：可以输入任何数据，表中或已存在不符合约束条件的数据

disable validate：不能对表进行插入，更新，删除等操作，相当于对整个表的read only 设定。

update emp1 set empno =NULL where empno =7900

select * from emp1

alter table emp1 add constraint ck_emp1 check (empno is not null);

alter table emp1 add constraint ck_emp1 check (empno is not null) enable novalidate;

insert into emp1(empno)

values(null);

将disable novalidate,enable novalidate,enable validate 三种状态组合起来使用

这种组合，可以避免因有个别不符合条件的数据，而导致大数据量的传输失败

假设a表示源数据，其中有空值，b表示a表的归档表，设有非空约束，现将a表插入到b表中

alter table b modify constraint b_nnl disable ,novalidate;

insert into b select * from a;

alter table b modify constraint b_nnl enable,novalidate;

update b set channel = 'NOT KNOWN' where channel is null;

alter table b modify constraint b_nnl enable ,validate;

延迟约束

alter table emp1 add constraint chk_sal check(sal > 500) deferrable;//延迟约束

insert into emp1(empno,sal)

values(3030,100);

set constraint chk_sal immediate;

values(3030,100)

set constraint chk_sal deferred;//延迟

alter table emp1 drop constraint chk_sal;

alter table emp1 add constraint chk_sal check(sal > 500) deferrable initially immediate;

alter table emp1 add constraint chk_sal check(sal > 500) deferrable initially deferred;

2.视图

为什么使用视图

1）限制数据的存取

用户只能看到基表的部分信息。方法：赋予用户访问视图对象的权限，而不是表的对象权限

2）使得复杂的查询变得容易

对于多表连接等复杂语句的映射，或内联视图的使用

3）提供数据的独立性

基表的多个独立子集的映射

简单视图

视图与基表的记录一对一，故可以通过视图修改表

复杂视图

视图与基表的记录一对多，无法修改视图

特性          简单视图        复杂视图

表的个数      一个            一个或多个

含函数        无              有

含组函数     无               有

含distinct   无               有

DML操作     可以             不一定

语法

create [or replace] [force|noforce] view view_name

[(alias[,alias],...)]

as subquery

[with check option [constraint constraint]]

[with read only];

create view v1 as select empno,sal,deptno from emp1 where deptno =10;

select * from user_views

create view v2 as select empno,sal,sal+100,deptno from emp1 where deptno =10;

create view v2 as select empno,sal,sal+100 as sal2,deptno from emp1 where deptno =10;

create view v3(a1,a2,a3,a4) as select empno,sal,sal+100 ,deptno from emp1 where deptno =10;

drop view v1;

create force view v3(a1,a2,a3,a4) as select empno,sal,sal+100 ,deptno from emp1 where deptno =10;

select * from v3

flashback table emp1 to before drop

update v3 set a4=99

select * from v3;

--with check option

create or replace view v3(a1,a2,a3,a4) as select empno,sal,sal+100 ,deptno from emp1 where deptno =10 with check option;

update emp1 set deptno =10 where deptno =99

update v3 set a1=a1+100

grant connect,resource to zzl identified by bsoft

grant select on v3 to zzl

--zzl用户

select * from scott.v3

select * from scott.emp1;

drop table dept1;

create or replace view v5 as select empno,dname from emp1,dept1 where emp1.deptno = dept1.deptno and emp1.deptno =10;

select * from v5

update v5 set dname =3030

3.同义词

同义词通常是数据库对象的别名

公有同义词

公有同义词一般有DBA创建，使所有用户都可使用

create user zzl identified by bsoft;

select * from session_privs

grant create session to zzl;

创建者需要有create public synonym权限

create public synonym syn1 for scott.v3

--grant select on syn1 to public

--grant public to zzl

zzl用户执行

select * from syn1;

select * from scott.v3;

--revoke public from zzl;

私有同义词

create synonym abc for emp1;

grant select on abc to zzl;

select * from scott.abc

select * from scott.emp1

revoke select on abc from zzl

grant select on emp1 to zzl

4.序列

create sequence seq1

select seq1.currval from dual;

select seq1.nextval from dual;

create sequence seq2 start with 10 increment by 2 maxvalue 20 cycle nocache;

select seq2.nextval from dual;

alter sequence seq2 increment by 1;

select seq2.nextval from dual

alter sequence seq2 start with 19

select * from user_sequences

drop sequence seq2

5.insert 总结

一次插入一行

create table test(id int,name varchar(10) default 'zzl');

insert into test values(1,'abc');

insert into test values(2,default);

insert into test values(3,null);

insert into test(id) values(4);

insert into (select id from test) values(5);

insert into test values(6,(select dname from dept where deptno =10))

insert with check option

insert into (select id from test where id <10 with check option ) values(10);

insert into (select id from test where id <10 with check option ) values(9);

insert into (select name from test where id <10 with check option ) values('zzl');

insert into (select name from test where id <10  ) values('zzl');

insert into (select id,name from test where id <10 with check option ) values(9,'zzl');

update (select sal from emp1) set sal=(select sal from emp1 where empno =7788);

update (select ename from emp1) set sal=(select sal from emp1 where empno =7788);

delete (select sal from emp1) where sal >3000;

一次插入多行

create table emp1 as select * from emp

insert into emp1 select * from emp where deptno =10;

multiable insert

insert all 

create table test(x number(10),y varchar2(10));

insert into test values(1,'a');

insert into test values(2,'b');

insert into test values(3,'c');

insert into test values(4,'d');

insert into test values(5,'e');

insert into test values(6,'f');

commit;

create table test1 as select * from test where 0=1;

create table test2 as select * from test where 0=1;

insert all into test1 into test2 select * from test;

select * from test1;

select * from test2;

delete from test1;

delete from test2;

insert all when x >= 3 then into test1 when x >=2 then into test2 select * from test;

insert first

insert first when x >= 3 then into test1 when x >=2 then into test2 select * from test;

旋转insert

create table test(empno number(10),week_id number(2),

sal_mon number(8,2),sal_tun number(8,2),

sal_wed number(8,2),sal_thur number(8,2),sal_fri number(8,2))

insert into test values(3030,56,1000,2000,3000,4000,5000)

create table test2(empno number(10),week number(2),sales number(8,2))

insert all

into test2 values(empno,week_id,sal_mon)

into test2 values(empno,week_id,sal_tun)

into test2 values(empno,week_id,sal_wed)

into test2 values(empno,week_id,sal_thur)

into test2 values(empno,week_id,sal_fri)

select empno,week_id,sal_mon,sal_tun,sal_wed,sal_thur,sal_fri from test;

merge

create table emp1 as select * from emp where empno =7788

update emp1 set ename = 'aaa'

insert into emp1(empno,ename)

values(7900,'zzl')

merge into emp1

using emp

on (emp1.empno = emp.empno)

when matched  then

update set emp1.ename = emp.ename

delete where emp1.empno =7900

when not matched then

insert values(emp.empno,emp.ename,emp.job,emp.mgr,emp.hiredate,emp.sal,emp.comm,emp.deptno)

6.with 语句

我们可以用一个关键字with给一个查询块起一个别名。然后在后面的查询中引用这个查询块的别名。

好处：

1.使用with语句，可以避免在select语句中重复书写相同的语句块

2.with语句将该子句中的语句块执行一次并存储到用户的临时表空间中

3.使用with语句可以避免重复解析，提高查询效率

with

dept_costs as(

select d.dname,sum(e.sal) as dept_total

from emp e,dept d

where e.deptno = d.deptno

group by d.dname),

avg_cost as(

select sum(dept_total)/count(*) as dept_avg from dept_costs)

select * from dept_costs 

where dept_total < (select dept_avg from avg_cost)

order by dname