1，限制
- 1.1， 什麼是限制？
- 1.2， 表級限制和列級限制？
- 1.3， NOT NULL 限制
- 1.4， UNIQUE 限制
- 1.5， PRIMARY KEY 限制
- 1.6， FOREIGN KEY 限制
- 1.7， 添加限制
- 1.8， 删除限制
- 1.9， 無效化限制
- 1.10，激活限制
- 1.11，查詢限制
2，視圖
- 2.1， 視圖的概念
- 2.2， 為什麼使用視圖
- 2.3， 簡單視圖和複雜視圖
- 2.4， 建立，查詢，修改，删除視圖
- 2.5， 視圖中使用DML的規定
- 2.6， 屏蔽 DML 操作
- 2.7， Top-N 分析
3，視圖
- 3.1， 什麼是序列?
- 3.2， 建立序列
- 3.3， 查詢序列
- 3.4， 删除序列
4，索引
- 4.1， 建立索引
- 4.2， 什麼時候需要建立索引
- 4.3， 什麼時候不要建立索引
- 4.4， 查詢索引
- 4.5， 删除索引
- 4.6， 同義詞-synonym
- 4.7， 建立和删除同義詞
限制
1，什麼是限制？
- 限制是表級的強制規定
-
有以下五種限制:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
- 如果不指定限制名 ，Oracle server 自動按照 SYS_Cn 的格式指定限制名
2，表級限制和列級限制？
-
作用範圍：
①，列級限制隻能作用在一個列上
②，表級限制可以作用在多個列上（當然表級限制也 可以作用在一個列上）
- 定義方式：列限制必須跟在列的定義後面，表限制不與列一起，而是單獨定義。
- 非空(not null) 限制隻能定義在列上
3，NOT NULL 限制
CREATE TABLE employees(
employee_id NUMBER(6),
first_name VARCHAR2(20) NOT NULL,//系統命名
job_id CONSTRAINT ji_nn NOT NULL VARCHAR2(10),//使用者命名
CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID)
);
4， UNIQUE 限制
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) UNIQUE,--系統命名
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
CONSTRAINT emp_email_uk UNIQUE(email) --使用者命名
);
5，PRIMARY KEY 限制
CREATE TABLE departments(
department_id NUMBER(4),
department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4),
CONSTRAINT dept_id_pk PRIMARY KEY(department_id));
6，FOREIGN KEY 限制
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25) UNIQUE,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id) on delete cascade
);
- FOREIGN KEY: 在表級指定子表中的列
- REFERENCES: 标示在父表中的列
- ON DELETE CASCADE(級聯删除): 當父表中的列被删除時，子表中相對應的列也被删除
- ON DELETE SET NULL(級聯置空): 子表中相應的列置空
7，添加限制
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk
FOREIGN KEY(manager_id) REFERENCES employees(employee_id);
8，删除限制 9，無效化限制
- 在ALTER TABLE 語句中使用 DISABLE 子句将限制無效化。
10，激活限制
- ENABLE 子句可将目前無效的限制激活
- 當定義或激活UNIQUE 或 PRIMARY KEY 限制時系統會自動建立UNIQUE 或 PRIMARY KEY索引
11，查詢限制
- 查詢資料字典視圖 USER_CONSTRAINTS
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
視圖
1，視圖的概念
- 視圖是一種虛表。
- 視圖建立在已有表的基礎上, 視圖賴以建立的這些表稱為基表。
- 向視圖提供資料内容的語句為 SELECT 語句, 可以将視圖了解為存儲起來的 SELECT 語句.
- 視圖向使用者提供基表資料的另一種表現形式
2，為什麼使用視圖
- 控制資料通路
- 簡化查詢
- 避免重複通路相同的資料
3，簡單視圖和複雜視圖
4，建立，查詢，修改，删除視圖
- 建立視圖
create or replace view empview
as
select employee_id emp_id,last_name name,department_name
from employees e,departments d
Where e.department_id = d.department_id
- 查詢視圖
- 修改視圖（使用CREATE OR REPLACE VIEW 子句修改視圖）
CREATE OR REPLACE VIEW empview
(emp_id, name, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
department_id
FROM employees
WHERE department_id = 80;
- 删除視圖（删除視圖隻是删除視圖的定義，并不會删除基表的資料）
drop view empview
5， 視圖中使用DML的規定
- 可以在簡單視圖中執行 DML 操作
-
當視圖定義中包含以下元素之一時不能使用delete：
組函數
GROUP BY 子句
DISTINCT 關鍵字
ROWNUM 僞列
- 例如：
create or replace view sal_view
as select
avg(salary) avg_sal from employees
group by department_id
-
當視圖定義中包含以下元素之一時不能使用update：
組函數
GROUP BY子句
DISTINCT 關鍵字
ROWNUM 僞列
列的定義為表達式
-
當視圖定義中包含以下元素之一時不能使insert：
組函數
GROUP BY 子句
DISTINCT 關鍵字
ROWNUM 僞列
列的定義為表達式
表中非空的列在視圖定義中未包括
6， 屏蔽 DML 操作
- 可以使用 WITH READ ONLY 選項屏蔽對視圖的DML 操作
- 任何 DML 操作都會傳回一個Oracle server 錯誤
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = 10
WITH READ ONLY;
7， Top-N 分析 `
- 查詢最大的幾個值的 Top-N 分析：
SELECT ROWNUM, e.empno, e.ename
FROM emp e
WHERE ROWNUM <= 5
ORDER BY e.empno;
-
注意：
對 ROWNUM （僞列）隻能使用 < 或 <=, 而用 =, >, >= 都将不能傳回任何資料。
序列
1，什麼是序列?
-
序列：
可供多個使用者用來産生唯一數值的資料庫對象
自動提供唯一的數值
共享對象
主要用于提供主鍵值
将序列值裝入記憶體可以提高通路效率
- 建立序列CREATE SEQUENCE 語句
CREATE SEQUENCE sequence
[INCREMENT BY n] --每次增長的數值
[START WITH n] --從哪個值開始
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}] --是否需要循環
[{CACHE n | NOCACHE}]; --是否緩存登入
2， 建立序列
- 建立序列EMP_SEQ為表 emp提供主鍵
CREATE SEQUENCE EMP_SEQ
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
3，查詢序列
- 查詢資料字典視圖 USER_SEQUENCES 擷取序列定義資訊
SELECT sequence_name, min_value, max_value,
increment_by, last_number
FROM user_sequences;
- 如果指定NOCACHE 選項，則列LAST_NUMBER 顯示序列中下一個有效的值
- NEXTVAL 和 CURRVAL 僞列
- NEXTVAL 傳回序列中下一個有效的值，任何使用者都可以引用
- CURRVAL 中存放序列的目前值
- NEXTVAL 應在 CURRVAL 之前指定 ，否則會報CURRVAL 尚未在此會話中定義的錯誤。
4，删除序列
- 使用 DROP SEQUENCE 語句删除序列
- 删除之後，序列不能再次被引用
索引
- 一種獨立于表的模式對象, 可以存儲在與表不同的磁盤或表空間中
- 索引被删除或損壞, 不會對表産生影響, 其影響的隻是查詢的速度
- 索引一旦建立, Oracle 管理系統會對其進行自動維護, 而且由 Oracle 管理系統決定何時使用索引。使用者不用在查詢語句中指定使用哪個索引
- 在删除一個表時,所有基于該表的索引會自動被删除
- 通過指針加速 Oracle 伺服器的查詢速度
- 通過快速定位資料的方法，減少磁盤 I/O
1，建立索引
- 自動建立: 在定義 PRIMARY KEY 或 UNIQUE 限制後系統自動在相應的列上建立唯一性索引
- 手動建立: 使用者可以在其它列上建立非唯一的索引，以加速查詢
- 在表 EMP的列 LAST_NAME 上建立索引
CREATE INDEX emp_last_name_idx
ON emp(last_name);
2，什麼時候需要建立索引
-
以下情況可以建立索引：
列中資料值分布範圍很廣
列經常在 WHERE 子句或連接配接條件中出現
表經常被通路而且資料量很大 ，通路的資料大概占資料總量的2%到4%
3，什麼時候不要建立索引
-
下列情況不要建立索引：
表很小
列不經常作為連接配接條件或出現在WHERE子句中
查詢的資料大于2%到4%
表經常更新
- 索引不需要用，隻是說我們在用name進行查詢的時候，速度會更快。當然查的速度快了，插入的速度就會慢。因為插入資料的同時，還需要維護一個索引。
4，查詢索引
- 可以使用資料字典視圖 USER_INDEXES 和 USER_IND_COLUMNS 檢視索引的資訊
SELECT ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'EMP';
5，删除索引
- 使用DROP INDEX 指令删除索引
- 隻有索引的擁有者或擁有DROP ANY INDEX 權限的使用者才可以删除索引，删除操作是不可復原的
6，同義詞-synonym
-
使用同義詞通路相同的對象:
友善通路其它使用者的對象
縮短對象名字的長度
CREATE SYNONYM e FOR emp;
select * from e;
7，建立和删除同義詞
- 為視圖DEPT_SUM_VU 建立同義詞
CREATE SYNONYM d_sum
FOR dept_sum_vu;
- 删除同義詞
下一章，（5）Oracle——————控制使用者權限，SET 操作符和進階子查詢
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)