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