視圖、序列、索引、限制

視圖

視圖(VIEW)也被稱作虛表，即虛拟的表，是一組資料的邏輯表示。

視圖對應于一條SELECT語句，結果集被賦予一個名字，即視圖名字。

         視圖本身并不包含任何資料，它隻包含映射到基表的一個查詢語句，當基表資料發生變化，視圖資料也随之變化。

文法：

         CREATE [OR REPLACE]  VIEW  view_name[(alias[,alias…])]  AS  subquery;

·視圖建立後，可以像操作表一樣操作視圖，主要是查詢

·Subquery是SELECT查詢語句，對應的表被稱為基表

·根據視圖所對應的子查詢種類分為幾種類型：

         --SELECT語句是基于單表建立的，且不包含任何函數運算、表達式或分組函數，叫做簡單視圖，此時視圖是基表的子集。

         --SELECT語句同樣是基于單表，但包含了單行函數、表達式、分組函數或GROUP BY子句，叫做複雜視圖。

         --SELECT語句是基于多個表的，叫做連接配接視圖。

視圖的作用：

         ·如果需要經常執行某項複雜查詢，可以基于這個複雜查詢建立視圖，此後查詢此視圖即可，簡化複雜查詢。

         ·視圖本質上就是一條SELECT語句，是以當通路視圖時，隻能通路到所對應的SELECT語句中涉及到的列，對基表中的其他列起到安全和保密的作用，限制資料通路。

授權建立視圖

         ·建立視圖的語句是CREATE VIEW，使用者必須擁有CREATE VIEW系統權限，才能建立視圖，如果沒有權限，會顯示權限不足。管理者可以通過DCL語句授予使用者建立視圖的權限：

         GRANT CREATE VIEW TO 使用者名;

對視圖進行DML操作

·視圖本身并不包含資料，隻是基表資料的邏輯映射

·當對視圖執行DML操作時，實際上是對基表的DML操作

·對視圖執行DML操作的基本原則：

---簡單視圖能夠執行DML操作，下列情況除外：在基表中定義了非空列，但簡單視圖對應的SELECT語句并沒有包含這個非空列，導緻這個非空列對視圖不可見，這時無法對視圖執行INSERT操作。

---DELETE操作時，隻能删除現有視圖裡能查到的記錄。

---如果視圖定義中包含了函數、表達式、分組語句、DISTINCT關鍵字或ROWNUM僞列，不允許執行DML操作。

---DML操作不能違反基表的限制條件。

建立具有CHECK OPTION限制的視圖

文法：CREATE[OR REPLACE]  VIEW  view_name[(alias[,alias…])] 

AS  Subquery  WITH CHECK OPTION;

WITH CHECK OPTION語句表示：通過視圖所做的修改。

假設INSERT，新增的記錄在視圖仍可檢視。

假設UPDATE，修改後的結果必須能通過視圖檢視到。

建立具有READ ONLY限制的視圖

文法：CREATE[OR REPLACE]  VIEW  view_name[(alias[,alias…])] 

                   AS Subquery WITH READ ONLY;

·對簡單視圖進行DML操作是合法的，但是不安全。

·如果沒有在視圖上執行DML操作的必要，在建立視圖時聲明為隻讀來避免這種情況，保證視圖對應的基表資料不會被非法修改。

通過查詢user_views擷取相關資訊

·和視圖相關的資料字典

---USER_OBJECTS

---USER_VIEWS

---USER_UPDATE_COLUMNS

·在資料字典USER_OBJECTS中查詢所有視圖名稱

SELECT object_name FROM user_objects WHERE object_type =’VIEW’;

建立複雜視圖

·複雜視圖指在子查詢中包含了表達式、單行函數或分組函數的視圖。

·必須為子查詢中的表達式或函數定義别名。

·複雜視圖不允許DML操作。

序列

·序列是一種用來生成唯一數字值的資料庫對象。

·序列的值由Oracle程式按遞增或遞減順序自動生成，通常來自動産生表的主鍵值，是一種高效率獲得唯一鍵值的途徑。

·序列是獨立的資料庫對象，和表是獨立的對象，序列并不依附于表。

·通常情況下，一個序列為一個表提供主鍵值，但一個序列也可以為多個表提供主鍵值。

建立序列

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方案下

·序列的第一個序列值是i，步進是j

·如果j是正數，表示遞增，如果是負數，表示遞減

·序列可生成的最大值是m，最小值是n

·如果沒有設定任何可選參數，序列的第一個值是1，步進是1

·CYCLE表示在遞增至最大值或遞減至最小值之後是否重用序列。若是遞減并有最大值，從最大值開始。若是遞增并有最小值，從最小值開始。若沒有從START WITH指定的值開始。預設是NOCYCLE

·CACHE用來指定先預取p個資料在緩存中，以提高序列值的生成效率，預設是20

使用序列

·序列中有兩個僞列

---NEXTVAL：擷取序列的下個值

---CURRVAL：擷取序列的目前值

·當序列建立之後，必須先執行一次NEXTVAL，之後才能使用CURRVAL

删除序列

·文法：DROP  SEQUENCE  sequence_name；

索引

·索引是一種允許直接通路資料表中某一資料行的樹型結構，為了提高查詢效率而引入，是獨立于表的對象，可以存放在與表不同的表空間(TABLESPACE)中

·索引記錄中存有索引關鍵字和指向表中資料的指針(位址)

·對索引進行的I/O操作比對标進行操作要少很多

·索引一旦被建立就被Oracle系統自動維護，查詢語句中不用指定使用哪個索引

·索引是一種提高查詢效率的機制

建立索引

·文法：

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,ename,sal,job  FROM  emp  ORDER BY  job,sal;

建立基于函數的索引

·如果需要在emp表的ename列上執行大小寫無關搜尋，可以在此列建立一個基于UPPER函數的索引：

CREATE  INDEX  emp_ename_upper_idx  ON emp(Upper(uname));

修改和删除索引

·如果經常在索引列上執行DML操作，需要定期重建索引，提高索引的空間使用率；

         ALTER  INDEX  index_name  REBUILD;

·當一個表上有不合理的索引，會導緻操作性能下降，删除索引的文法：

         DROP  INDEX  index_name;

合理使用索引提升查詢效率

·為經常出現在WHERE子句中的列建立索引

·為經常出現在ORDER BY、DISTINCT後面的字段建立索引。如果建立的是複合索引，索引的字段順序要和這些關鍵字後面的字段順序一緻

·為經常作為表的連接配接條件的列上建立索引

·不要在經常做DML操作的表上建立索引

·不要在小表上建立索引

·限制表上的索引數目，索引并不是越多越好

·删除很少被使用的、不合理的索引

限制

作用：

·限制(CONSTRAINT)的全稱是限制條件，也稱作完整性限制條件

·限制是在資料表上強制執行的一些資料校驗規則，當執行DML操作時，資料必須符合這些規則，如果不符合則無法執行

·限制條件可以保證表中資料的完整性，保證資料間的商業邏輯

限制條件包括：

---非空限制(Not Null)，簡稱NN(列級限制)

---唯一性限制(Unique)，簡稱UK(列級限制、表級限制)

·唯一性(Unique)限制條件用于保證字段或者字段的組合不出現重複值

·當給表的某個列定義了唯一性限制，該列的值不允許重複，但允許是NULL值

·唯一性限制條件可以在建表同時建立，也可以在建表以後再建立

---主鍵限制(Primary Key)，簡稱PK

·主鍵(Primary Key)限制條件從功能上看相當于非空(NOT NULL)且唯一(UNIQUE)的組合

·主鍵字段可以是單字段或多字段組合，即：在主鍵限制下的單字段或者多字段組合上不允許有空值，也不允許有重複值

·主鍵可以用來在表中唯一的确定一行資料

·一個表上隻允許建立一個主鍵，而其他限制條件則沒有明确的個數限制

主鍵選取的原則：

·主鍵應是對系統無意義的資料

·永遠也不要更新主鍵，讓主鍵除了唯一辨別一行之外，再無其他的用途

·主鍵不應包含動态變化的資料，如時間戳

·主鍵應自動生成，不要人為幹預，以免使它帶有除了唯一辨別一行以外的意義

·主鍵盡量建立在單列上

---外鍵限制(Foreign Key)，簡稱FK

·意義：定義在兩個表的字段或一個表的兩個字段上，用于保證相關兩個字段的關系

外鍵限制對唯一性的維護

·從表上定義的外鍵的列值，必須從主表被參照的列值中選取，或者為NULL

·當主表參照列的值被從表參照時，主表的該行記錄不允許被删除

外鍵限制對性能的降低

·如果在一個頻繁DML操作的表上建立外鍵，每次DML操作，都将導緻資料庫自動對外鍵所關聯的對應表作檢查，産生開銷，如果已在程式中控制邏輯，這些判斷将增加額外負擔，可以省去

·外鍵确定了主從表的先後生成關系，有時會影響業務邏輯

關聯不一定需要外鍵限制

·保證資料完整性可由程式或觸發器控制

·簡化開發，維護資料時不用考慮外鍵限制

·大量資料DML操作時不需要考慮外鍵耗費時間                         

---檢查限制(Check)，簡稱CK

·檢查(Check)限制條件用來強制在字段上的每個值都要滿足Check中定義的條件

·當定義了Check限制的列新增或修改資料時，資料必須符合Check限制中定義的條件