一. 視圖,序列,索引
視圖
1. 什么是視圖
視圖(VIEW)也被稱作虛表,是一組數據的邏輯表示
視圖對應于一條SELECT語句,結果集被賦予一個名字,即視圖名字
視圖本身并不包含任何數據,它只包含映射到基表的一個查詢語句,當基于數據發生變化,視圖數據頁隨之變化
CREATE [OR REPLACE] VIEW view_name[alias[,alias…]] AS subquery;
視圖創建后,可以像操作表一樣操作視圖,主要是查詢
SUBQUERY是SELECT查詢語句,對應的表被稱為基表
— SELECT語句是基于單表建立的,且不包含任何函數運算,表達式或分組函數,叫簡單視圖,這時視圖是基表的子集
— SELECT語句基于單表,但包含了單行函數,表達式,分組函數或GROUP BY子句,叫做復雜視圖
— SELECT語句基于多個表,叫做連接視圖
2. 視圖的作用
簡化復雜查詢:若經常需要執行某項復雜查詢,可以基于這個復雜查詢建立視圖,此后查詢次視圖即可
限制數據訪問:當訪問視圖時只能訪問到對應的SELECT語句中涉及的列,對基表中其它列起到安全和保密的作用
3. 授權創建簡單視圖(單表)
創建視圖的語句是:CREATE VIEWER
用戶必須有CREATE VIEWER系統權限,才能創建視圖
管理員通過DCL語句授予用戶創建視圖的權限:GRANT CREATE VIEWER TO user;
eg: CREATE VIEWER v_emp_10 AS SELECT empno,name,sal,deptno FROM emp WHERE deptno = 10;
4. 查詢視圖
desc v_emp_10;
5. 對視圖進行INSERT操作
視圖本身并不包含數據,只是基表數據的邏輯映射
當對視圖執行DML操作時,實際上是對基表的DML操作
對視圖執行DML操作的基本原則:
— 簡單視圖能夠執行DML操作,下列情況除外:在基表中定義了非空列,但簡單視圖對應的SELECT語句并沒有包含這個非空列,導致這個非空列隊視圖不可見,這時無法對視圖執行INSERT操作
— 如果視圖定義中包含了函數,表達式,分組語句,DISTINCT關鍵字或ROWNUM偽列,不允許執行DML操作
— DML操作不能違反基表的約束條件
— 簡單視圖可以通過DML操作影響到基表數據
— 視圖進行DELETE操作時只能刪除基表中視圖中看得到的數據,不能基表中存在而視圖中看不到的數據
6. 創建具有CHECK OPTION約束的視圖
CREATE [OR REPLACE] VIEW view_name[(alias[, alias...])] AS subquery [WITH CHECK OPTION];
WITH CHECK OPTION短語表示,通過視圖所做的修改,必須在視圖的可見范圍
— 假設INSERT,新增的記錄在視圖仍可查看
— 假設UPDATE,修改后的結果必須能通過視圖查看到
7. 創建具有READ ONLY約束的視圖
CREATE [OR REPLACE] VIEW view_name[(alias[, alias...])] AS subquery [WITH READ ONLY];
如果沒有在視圖上執行DML操作的必要,在建立視圖時聲明為只讀來避免這種情況,保證視圖對應的基表數據不會被非法修改
8. 通過查詢user_viewers獲取相關信息
和視圖相關的數據字典:
— USER_OBJECTS
— USER_VIEWS
— USER_UPDATE_COLUMNS (查看哪些列允許增刪改)
eg:在數據字典USER_OBJECTS中查詢所有視圖名稱
SELECT object_name FROM user_objects WHERE object_type = ‘VIEW’;
9. 創建復雜視圖(多表關聯)
復雜視圖:在子查詢中包含了表達式,單行函數或分組函數的視圖
必須為子查詢中的表達式或函數定義別名
復雜視圖不允許DML操作(即INSERT,UPDATE,DELETE)
10. 刪除視圖
當不再需要視圖的定義,可以使用DROP VIEW語句刪除視圖
eg:DROP VIEW view_name;
視圖雖然時存放在數據字典中的獨立對象,但視圖僅僅是基于表的一個查詢定義,所以對視圖的刪除不會導致基表數據的丟失,不影響基表數據
SELECT sys_guid() FROM DUAL;//可以得到32位的uuid
JAVA中:
String uuid = UUID.randomUUID().toString();
序列
1. 什么是序列
序列(SEQUENCE)是一種用來生成唯一數字值的數據庫對象
序列的值由Oracle程序按遞增或遞減順序自動生成,通常用來自動生成表的主鍵值,是一種高效率獲得唯一鍵值的途徑
序列是獨立的數據庫對象,和表是對立的對象,序列并不依附于表
通常一個序列為一個表提供主鍵值,但一個序列也可以為多個表提供主鍵
2. 創建序列
CREATE SEQUENCE [schema.]sequence_name
[START WITH i] [INCREMENT BY j ]
[MAXVALUE m | NOMAXVALUE ]
[MINVALUE n | NOMINVALUE ]
[CYCLE | NOCYCLE ][ CACHE p | NOCACHE]
sequence_name是序列名,將創建在schema方案下,schema是用戶名,給自己用戶創建時可不寫
序列的第一個序列值是i,步進(即步長)是j
如果j是整數,表示遞增,如果是負數,表示遞減
序列可生成的最大值是m,最小值n
如果沒有設置任何可選參數,序列的第一個值是1,步進是1
CYCLE表示在遞增至最大值或遞減至最小值之后是否重用序列。若是遞減并有最大值,從最大值開始。若是遞增并有最小值,從最小值開始。若沒有從START WITH指定的值開始。默認是NOCYCLE
CACHE用來指定先預取p個數據在緩存中,以提高序列值的生成效率,默認是20
3. 使用序列
eg:序列起始數據:100,步進10,則序列號分別是:100,110,120,130...
CREATE SEQUENCE emp_seq START WITH 100 INCREMENT BY 10;
序列中有2個偽列:
— NEXTVAL:獲取序列的下個值
— CURRENT:獲取序列的當前值
當序列創建以后,必須先執行一次NEXTVAL,之后才能使用CURRENT
獲取序列的第一個值,并使用序列值為EMP表插入新的記錄
SELECT emp_seq.NEXTVAL FROM DUAL;//第一次執行:100;第二次執行:110
INSERT INTO emp(epmno, ename) VALUES(emp_seq.NEXTVAL, ‘DONNA’);
4. 刪除序列
DROP SEQUENCE sequence_name;
索引
1. 索引的原理
索引是一種允許值接訪問數據表中某一數據行的樹型結構,為了提高查詢效率而引入,是獨立于表的對象,可以存放在與表不同的表空間(TABLESPACE)中
索引記錄中存有索引關鍵字和指向表中數據的指針(地址)
對索引進行的I/O操作比對表進行操作要少很多
索引一旦被建立就將被Oracle系統自動維護,查詢語句中不用指定使用哪個索引
索引是一種提高查詢效率的機制
2. 創建索引
CREATE [UNIQUE] INDEX index_name ON table(column[, column…]);
— index_name表示索引名稱
— table表示表明
— column表示列名,可以建立單列索引或復合索引
— UNIQUE表示唯一索引
— 索引指定的列是在查詢時WHERE子句中經常出現的列
復合索引也叫多列索引,是基于多個列的索引
如果經常在ORDER BY子句中使用job,sal作為排序依據,則可以建立復合索引:
CREATE INDEX idx_emp_job_sal ON emp(job, sal);
當做下面查詢時會自動應用索引[idx_emp_job_sal]
SELECT empno, name, sal, job FROM amp ORDER BY job,sal;
3. 創建基于函數的索引
可以在列上建立一個基于UPPER函數的索引:
CREATE INDEX emp_name_upper_idx ON emp(UPPER(name));
當做下面查詢時會自動應用索引[emp_name_upper_idx]
SELECT empno FROM emp WHERE UPPER(ename) = ‘KING'
4. 修改和刪除索引
如果經常在索引列上執行DML操作,需要定期重建索引,提高索引的空間利用率:ALTER INDEX index_name REBUILD;
當一個表上有不合理的索引,會導致操作性能下降,刪除索引:DROP INDEX index_name;
5. 合理使用索引提升查詢效率
為經常出現在WHERE子句中的列創建索引
為經常出現在ORDER BY,DISTINCT后面的字段建立索引。如果建立的是復合索引,索引的字段順序要和這些關鍵字后面的字段順序一致
為經常作為表的連接條件的列上創建索引
不要在經常做DML操作的表上建立索引
不要在小表上建立索引
限制表上的索引數目,索引并不是越多越好
刪除很少被使用的,不合理的索引
二. 約束
約束概述
1. 約束的作用
約束(CONSTRAINT)的全稱是約束條件,也稱為完整性約束條件
約束條件可以保證表中數據的完整性,保證數據間的商業邏輯
2. 約束的類型
— 非空約束(Not Null),簡稱NN
— 唯一性約束(Unique),簡稱UK
— 主鍵約束(Primary Key),簡稱PK
— 外鍵約束(Foreign Key),簡稱FK
— 檢查約束(Check),簡稱CK
非空約束
1. 建表時添加非空約束
列級約束:在創建表指定列的同時聲明的約束
CREATE TABLE employees(id NUMBER(6),
name VARCHAR2(30) NOT NULL,//列級約束1
salary NUMBER(7,2),
hiredate DATE CONSTRAINT employees_hiredate_nn NOT NULL//列級約束2
);
NOT NULL只能是列級約束
2. 修改表時添加非空約束
ALTER TABLE employees MODIFY (id NUMBER(6) NOT NULL);
3. 取消非空約束
ALTER TABLE employees MODIFY (id NUMBER(6) null);
唯一性約束
1. 什么是唯一性約束
唯一性(Unique)約束條件用于保證字段或者字段的組合不出現重復值
當給表的某個列定義了唯一約束條件,該列的值不允許重復,但允許是NULL
2. 添加唯一性約束
CREATE TABLE employees(id NUMBER(6) UNIQUE,//列級約束
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE,
CONSTRAINT employees_email_uk UNIQUE(email)//表級約束
);
列級約束是在建表聲明某一列的同時指定約束條件,表級約束是所有列都聲明完畢后單獨加約束,括號里指定約束對象是哪一列。
唯一性約束即可以是列級約束也可以是表級約束
在建表之后增加唯一性約束條件:ALTER TABLE employees ADD CONSTRAINT employees_name_uk UNIQUE(name);//新增的唯一性約束條件只會對該增加之后的數據起作用
主鍵約束
1. 主鍵的意義
主鍵(Primary Key)約束條件從功能上看相當于非空且唯一的組合
主鍵字段可以是單字段或多字段組合
主鍵可用來確定表中唯一一行數據
一個表中只允許建立一個主鍵,而其它約束條件則沒有明確的個數限制
2. 主鍵選取的原則
主鍵應是對系統無意義的數據
永遠不要更新主鍵,讓主鍵除了唯一標識一行之外,再無其它用處
主鍵不應包含動態變化的數據,如時間戳
主鍵應自動生成,不要人為干預,以免它帶有了唯一標識以外的意義
主鍵盡量建立在單列上
3. 添加主鍵約束
在建表時添加主鍵約束條件:
CREATE TABLE employees (
id NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE
);
建表后創建主鍵約束條件,并自定義約束條件名稱:
CREATE TABLE employees (
id NUMBER(6),
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE
);
ALTER TABLE employees ADD CONSTRAINT employees ADD CONSTRAINT employees_id_pk PRIMARY KEY (id);
外鍵約束
1. 外鍵約束的意義
外鍵約束條件定義在兩個表的字段或一個表的兩個字段上,用于保證相關兩個字段的關系
dept表:主表或父表
emp表:從表或子表
deptno(PK) | name | loc |
10 | 研發部 | 北京 |
20 | 銷售部 | 上海 |
empno(PK) | name | deptno(FK) |
1001 | 劉心 | 10 |
1002 | 李蘇海 | 10 |
1. 添加外鍵約束
先建表,在建表后建立外鍵約束條件
CREATE TABLE employees (
id NUMBER(6),
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
deptno NUMBER(4)
);
ALTER TABLE employees ADD CONSTRAINT employees_deptno_fk FOREIGN KEY (deptno) REFERENCES dept(deptno);
2. 外鍵約束對一致性的維護
外鍵約束條件包含兩個方面的數據約束:
— 從表上定義的外鍵的列值,必須從主表被參照的列值中選取,或者為NULL
— 當主表參照列的值被從表參照時,主表的該行記錄不允許被刪除
3. 外鍵約束對性能的降低
若在一個頻繁DML操作的表上建立外鍵,每次DML操作都將導致數據庫自動對外鍵所關聯的對應表做檢查,產生開銷,如果已在程序中控制邏輯,這些判斷將增加額外負擔,可以省去
外鍵確定了主從表的先后生成關系,有時會影響業務邏輯
4. 關聯不一定需要外鍵約束
保證數據完整行可由程序或觸發器控制
簡化開發,維護數據時不用考慮外鍵約束
大量數據DML操作時不需要考慮外鍵耗費時間
檢查約束
1. 什么是檢查約束
檢查(Check)約束條件用來強制在字段上的每個值都要滿足Check中定義的條件
當定義了Check約束的列新增或修改數據時,數據必須符合Check約束中定義的條件
2. 添加檢查約束
eg:員工薪水必須大于2000
ALTER TABLE employees4 ADD CONSTRAINT employees4_salary_check CHECK (salary > 2000);
3. 刪除一個約束
ALTER TABLE employees DROP CONSTRAINT employees_salary_check;
查看約束的數據字典
SELECT constraint_name,constraint_type FROM user_constraints WHERE table_name = ‘EMPLOYEES’;
— constraint_type:約束類型
— constraint_name:約束名
SELECT * FROM user_constraints WHERE table_name = ‘EMPLOYEES’;
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。