oracle資料庫資料表條件約束、視圖、索引—該記錄為本人以前微博的文章

標籤：ada   dna   結果   自訂   new   std   enc   不同的   提高   

一、Oracle 資料庫常用操作續關於建立表時建立約束
1、建立表的時候增加約束
----約束是定義表中的資料應該遵循的規則或者滿足的條件
----約束是建立在列上的，讓某一列或者某幾列資料之間有約束
----約束可以在建立的表的同時建立約束，也可以在建立表之後再建立約束
----約束分為表級約束和資料行層級條件約束，定義的方式不同，但是效果是相同的
----資料行層級條件約束：列名  資料類型 【default 預設值】 【資料行層級條件約束1  資料行層級條件約束2……】
----表級約束：constraint 約束名稱 約束類型（列名）
----表級約束可以自訂約束的名稱，資料行層級條件約束的名稱由系統自動分配SYS_CN
----如果是多列之間的資料約束，只能使用表級約束定義
----not  null 非空約束只能定義列級，不能定義表級約束
/*
主鍵約束——primary key
外鍵約束——foreign key
非空約束——not  null
唯一約束——unique
檢查約束——check
2、資料行層級條件約束和表級約束
----外鍵的資料行層級條件約束：references 表名（列名）
----外鍵的表級約束： constraint 約束名稱 foreign key (列名） references 表名（列名）
//testdeptno number(3) constraint adsfa foreign key (testdeptno) references dept(deptno)
---建立表之後再建立或刪除約束的方法：
----alter table 表名 modify 列名 not  null;  ---- 添加非空約束
----alter table 表名 add constraint 約束名稱 約束類型（列名）；----添加其他約束
----alter table 表名 modify 列名 null；-----刪除非空約束
----alter  table 表名 drop constraint 約束名稱； ----刪除其他約束
例子：
學生表：
列名                     資料類型
st_id（主鍵）                              number(8) 
st_name（非空）                        varchar2(14) 
st_age（18歲以上）                        number(2) 
st_phno（不能重複）                        number(11) 
st_date（預設為當前系統時間）               date 
st_gender（非空，只能為男或女，預設為男）    char(2) 
st_grade（外鍵，關聯班級表主鍵）             number(4)
create table student
(
st_id number(8) primary key,
st_name varchar2(14) not null,
st_age number(2) check (st_age>=18),
st_phone number(11) unique,
st_date date default sysdate,
st_gender char(2) default ‘M‘ not null check (st_gender in (‘M‘,‘F‘)),
st_grade number(4) references grade(grade_id)
/*constraint 約束名稱 foreign key (列名） references 表名（列名）
st_grade number(4) constraint  adsad foreign key (st_grade) references grade(grade_id)

 

 

二、Orcale資料庫操作續視圖索引觸發器
1、建立視圖
create view empview as
select * from emp where comm is null;
建立視圖時提示沒有許可權進行操作故進入isqlplus使用sys dba賬戶進行登入對scott使用者進行授權：grant ada to scott,將scott授權為dba；
grant create view to scott;也是可以的；
2、修改視圖
update empview set comm=comm+200 where ename=‘tengjiang‘;基本的操作和修改表是一樣的；
註：對視圖中的資料進行修改等同於對源表中的資料進行修改；
3、刪除視圖
delete empview where empno=1001;
4、修改視圖結構操作同修改表結構，見上面內容，在此就不在贅述了；
5、聯集查詢
（1）、多表查詢 參見笛卡爾積
select conut(e.ename) from emp e,dept d
where e.deptno=d.deptno amd d.dname=‘tengjiang‘;
(2)、子查詢將查詢結果做為一個子表，再在子表中進行查詢
select count(ename) from emp where deptno=(select deptno from dept where dname=‘SALES‘);
(3)、多表查詢和子表查詢的差別：
a、查詢次數:多表查詢比子表查詢查詢次數多：
多表查詢：m*n次
子表查詢：m+n次
(4)、建立視圖在查詢
a、建立視圖：
create view emp_dept as
select e.*,d.deptno dno,d.dname,d.loc from emp e,dept d 
where e.deptno=d.deptno;
建立了一個emp_dept 的視圖：將emp表和dept表外連結起來；
b、再在視圖中查詢
select count(ename) from emp_dept where dname=‘SALES‘;將部門名稱為‘SALES‘的人數統計出來；
6、建立索引；
----索引（index），是建立在某一列資料上的目錄，可以提高查詢的效率
----經常作為查詢條件的列，建立索引可以大幅度的提高查詢效率
----索引分為兩部分：rowid 和索引值
----rowid是每行資料存放區的物理地址，索引值就是具體資料的取值

----索引主要有兩種：B-樹索引和位元影像索引
---（1）、B-樹索引：基於二叉樹結構的。B-樹索引結構有3個基本組成部分：根結構、分支節點和葉子節點。
---其中根節點位於索引結構的最頂端，而葉子節點位於索引結構的最低端，中間為分支節點。
----B-樹索引是oracle中預設的索引類型，是一個樹狀結構的目錄
----create index 索引名稱 on 表名（列名）；
create index index_ename on emp(ename);
----確認索引的建立可以到這裡查看
select * from user_indexes;
----索引建立好之後，當使用相關列查詢資料時，索引會自動協助查詢語句提高效率
---當表中的資料發生變化時，索引會自動更新，不需要手動更新維護
---當資料大量的變化後，索引會產生一些片段檔案，佔用空間並且影響效率
---可以定期對索引進行重建，清除片段檔案
（2）、位元影像索引
----B-樹索引適合某列中有大量不同的資料
----當某一列中有大量重複的資料時，應該用位元影像索引
----位元影像索引不適合資料變化過於頻繁的列
----create bitmap index 索引名稱 on 表名（列名）
create bitmap index index_deptno on emp(deptno);
7、觸發器
----觸發器 trigger，與某些特定操作綁定的操作，不會主動實施，
----但當條件滿足時，會在綁定的操作觸發時同步操作。
/*
create (or replace) trigger 觸發器名稱
before/after
insert or update or delete
(of 列） on 表
（for each row)
(when (條件範圍））
begin
觸發操作（insert or update or delete）；
end;
  
before和after定義觸發的時間是在操作之前還是操作之後
insert or update or delete定義觸發的操作
(of 列） on 表：定義對哪張表的哪一列操作時觸發
for each row：定義是否每行資料都觸發一次
when：中間作為條件的列，必須使用old或者new來限定，表示
修改前的列或者修改後的列
*/

-----先建立一張表用來儲存相應的記錄資訊
create table record_dept30
(
ename varchar2(20),
old_job varchar2(20),
new_job varchar2(20),
rec date
);
select * from record_dept30

create trigger tri1
after
update
of job on emp
for each row
when (old.deptno=30)
begin
insert into record_dept30
values(:old.ename,:old.job,:new.job,sysdate);
end;

----在begin部分如果要調用表中的資料，使用:old和:new 來限定

8、阻止操作
----不允許對manager的工資進行修改
/*
觸發時間：before、after
觸發操作：update、delete、insert
觸發對象：emp表中sal列
觸發的範圍：職位是manager
*/
/*
阻止操作使用raise_application_error(作為id,錯誤提示） 方法，強制提交一個錯誤
錯誤id是自訂的，推薦使用-20000~29999之間的數字，錯誤提示自訂文本
*/
create or repaley trigger tri2
before 
update 
of sal on emp
for each row 
when (old.job=‘MANAGER‘)
begin
raise_application_error(-20001,‘ddssfadfadsfas‘);

oracle資料庫資料表條件約束、視圖、索引—該記錄為本人以前微博的文章