Oracle 表相關DDL

  Oracle 表：資料定義語言 (Data Definition Language)（DDL ） 1 、命名規則和約定：表名和列名的長度最多為30個字元表名和列名必須以字母打頭表名或者列名中不允許有空格和*Oracle伺服器的保留字也不能作為表名或列名 2 、資料類型：建立資料表時，要為表中的每一列分配一個資料類型。資料類型指定了將要儲存在此列中的資料的類型。通過為一列提供一種資料類型，可以避免在此列中儲存錯誤類型的資料 Varchar2 ：VARCHAR2
是一種字元型資料類型，用來在一列中儲存可變長度的字母數字混合的資料。VARCHAR2必須指定最大長度，最小長度和預設長度都是一個字元。指定的長度
要置於圓括弧當中，例如VARCHAR（20）。如果資料比指定的長度短，那麼只儲存資料值，並不在值的尾部添加空格。但是，如果輸入的值比指定的長度
長，則會產生錯誤，並不會將較長的值截短。如果某一列的值沒有固定的長度，那麼最適合使用VARCHAR2資料類型 Char ：CHAR
類型是一種字元型資料類型，用來在一列中儲存固定長度的字母數字混合的資料。預設長度和最小長度都是一個字元。如果輸入的值比指定的長度短，則在其尾部添
加空格，以使其長度等於指定的長度；如果輸入的值比指定的長度長，則會發生錯誤。CHAR類型適用於固定長度的值 Number ：NUMBER
資料類型用來儲存負數、正數、整數、定點數和浮點數。NUMBER資料類型可以用於任何要進行數學計算的列。當某一列使用Number類型時，可以指定其
精度和標度。精度是一個數位有意義數位總數，包括小數點左右兩邊的數字，但指定精度時小數點不計算在內。標度是小數點右邊數位總數整數類型全部是數字，沒有小數部分。如果一列定義為整數類型，則只需要提供精度的大小即可定點小數類型的小數點右邊的數字為數是指定的浮點小數類型的小數位為數是可變的。小數點前可以有任意位元字，或者也可以根本不出現小數點。將一列定義為這種類型時不用隨同NUMBER類型一起來指定標度和精度 Date ：DATE類型用於儲存日期和時間值。DATE類型的列中儲存著日、月、世紀、小時、分鐘和秒。對DATE類型不需要指定大小。 LONG ：LONG類型用於可變長度的字元資料，最大為2G Byte。一張資料表中只能有一列定義為LONG類型，定義LONG類型時，不需要指定其大小 NCHAR ：NCHAR類型與CHAR類型相似，但它對每一個字元都使用兩個位元組的二進位編碼，而CHAR類型則對每一個字元使用一個位元組的ACSII編碼，它只能表示256個不同的字元 CLOB ：這一字元大對象資料類型用於儲存單位元組字元資料，最大為4G個位元組 NCLOB ：這一字元大對象資料類型使用2 Byte的字元代碼 BLOB ：二進位大對象資料類型用於儲存位元據，最大為4G Byte 3 、約束約束是對資料表施加規則。約束有助於我們使自己的資料庫具有完整性。完整性規則：（1）實體完整性：主鍵中的列不能是空（null），主鍵提供了可以惟一地識別一行或一個實體的方法。（2）參考完整性：外索引值或者是空值，或者必須作為參考資料表的主索引值而存在Oracle使用約束來實現關聯式資料庫的完整性規則，並在單個列層級實現資料完整性。無論何時，當對資料表中的一行或一條記錄進行插入、更新或刪除操作時，都必須滿足約束條件，這樣其操作才能成功 3.1 、約束類型（1）完整性條件約束：定義資料表中的主鍵和外鍵，以及此外鍵所引用的主鍵（2）值約束：如果不允許值為空白，如果要求值是惟一的，以及如果一個列的值只允許是一組確定的值，那麼就需要定義值約束 3.2 、命名約束Oracle通過內部名稱或使用者自訂的名稱來識別約束，對於每一個使用者帳號，每一個約束名都必須是惟一的。一個使用者不能在兩張不同的資料表上建立具有相同名稱的約束。一般情況下，命名約束時採用如下的約定形式： <table name>_<column name>_<constraint type>這裡table name是要定義約束的表名，column name是要應用約束的列名，constraint type是用縮寫形式來表示的約束類型約束與縮寫的對應形式PRIMARY KEY：pkFOREIGN KEY：fkUNIQUE：ukCHECK：ckNOT NULL：nn如果使用者沒有命名一個約束，則Oracle伺服器會自動產生一個約束名，所用的格式是SYS_Cn，這裡n是任意一個惟一的數字 3.3 、定義約束可以在建立表時建立約束，也可以在表建立完成之後再添加約束。約束的定義有兩個層級：列層級：一個列層級的約束只涉及單獨一列，它的定義與列的定義同時進行。除外鍵約束和複合主鍵約束外，其他類型的約束均可以在列層級進行定義通用的文法格式是： Column datatype [CONSTRAINT constraint_name] constraint_type表層級：一個表層級的約束涉及一列或多列，它的定義與列的定義是分開進行的。一般來說，所有列的定義都完成之後才定義表層級約束。除非空約束，所有約束均可以在表層級進行定義。通用的文法格式是： [CONSTRAINT constraint_name] constraint_type （Column name ） 3.3.1 、主鍵約束主鍵約束也稱為實體完整性約束。它為表建立一個主鍵。一張表只能有一個主鍵約束。無論是一列用作主鍵還是多列複合起來用作主鍵，主鍵只能是非空值，而且也只能有惟一的值。例如，N2公司資料庫的DEPT表將DeptId列用作主鍵，則在列層級定義約束如下所示：DeptId NUMBER(2) CONSTRAINT dept_deptid_pk PRIMARY KEY在表層級定義約束如下所示：CONSTRAINT dept_deptid_pk PRIMARY KEY（DeptId） 3.3.2 、外鍵約束外鍵約束也稱為主鍵完整性條件約束。它使用一列或多列作為外鍵，建立了此外鍵與同一張表或者另一張表的主鍵之間的關係要確定一張資料表的外鍵，其他被引用的表及其主鍵必須已經存在。雖然外鍵與被引用的主鍵列不需要具有相同的名稱，但是外索引值必須與父表的主索引值相匹配，或者外索引值是空值CONSTRAINT student_facultyid_fk FOREIGN KEY（FacultyId）REFERENCES faculty(FacultyId)STUDENT標的FaultyId引用了FACULTY表的主鍵FacultyId在結束FOREIGN KEY之前，還可以添加ON DELETE
CASCADE子句，它不僅允許刪除父表中的記錄，而且還會同時刪除子表中依賴的記錄。在不使用ON DELETE
CASCADE子句的情況下，如果子表引用父表中的記錄，那麼就不能刪除父表中的這一行記錄 3.3.3 、非空約束非空約束確保一列具有一個值而且不是空值 3.3.4 、惟一性約束惟一性約束要求一列或一組列中的每一個值都是惟一的。如果惟一性約束應用於單一列，則此列只有惟一的值；如果惟一性約束應用於一組列，那麼這組列合起來具有惟一的值。惟一性約束允許空值，除非此列也應用了NOT NULL非空約束 3.3.5 、檢查約束檢查約束定義了每一行都必須滿足的條件。一列可以有多個檢查約束，檢查約束既可以在列層級定義，也可以在表層級定義CONSTRAINT dept_deptid_cc CHECK （（DeptId>=10）and（DeptId<=99）） 3.3.6 、非空檢查約束非空約束也能夠作為檢查約束來聲明，它可以在列層級定義，也可以在表層級定義：Name VARCHAR2(15) CONSTRAINT faculty_name_ck CHECK（Name IS NOT NULL） 3.3.7 、DEFAULT 預設值預設值確保當向表中插入新的一行時，某一特定的列總是具有一個值。如果使用者輸入另一個值，則預設值就會被覆蓋，如果插入空值，那麼就會使用預設值State CHAR(2) DEFAULT ‘NJ’ 4 、建立Oracle 資料表CREATE TABLE語句的通用文法格式是： CREATE TABLE [schema.] tablename (column1 datatype [CONSTRAINT constraint_name] constraint_type ， column2 datatype [CONSTRAINT constraint_name] constraint_type ，… [CONSTRAINT constraint_name] constraint_type （Column name ，… ），…)     schema是可選的，它與使用者的註冊名相同tablename是使用者指定的表名column是單個列的名字datatype是該列的資料類型和大小constraint_name是使用者提供的約束名constraint_type是指完整性條件約束或值約束每個列都可以有零個、一個或多個在列層級定義的約束，一般情況下，表層級約束是在所有列定義完成之後才聲明 4.1 、CREATE TABLE 語句中的STORAGE 子句CREATE
TABLE語句中可以包括STORAGE子句，但它是可選的。在建立資料庫時，使用該子句，由參數INITIAL定義為資料表分配的初始磁碟空間，另外還
可以使用另一個參數NEXT，它在資料表對分配的初始磁碟空間已經用完的情況下，定義再追加分配多少磁碟空間。CREATE TABLE sample（Id NUMBER（3），Name VARCHAR（25））TABLESPACE CIS_DATASTORAGE（INITIAL 1M NEXT 100K）PCTFREE 20；//空閑百分比 5 、顯示資料表資訊當使用者建立了資料庫中的一張表或多張表時，Oracle用自己的資料字典來跟蹤所有這些表。Oracle提供了SQL語句和SQL *Plus命令可以查看Oracle的資料字典表中的資訊 5.1 、查看使用者的表名SELECT TABLE_NAME FROM USER_TABLES;Oracle建立系統資料表來儲存使用者和使用者物件的有關資訊，USER_TABLES是一個Oracle系統資料表，TABLE_NAME是此表中的一個列 5.2 、查看錶結構DESCRIBE 表名 5.3 、查看約束資訊Oracle的資料字典表USER_CONSTRAINTS儲存著我們為每一列輸入的約束資訊SELECT CONSTRAINT_NAME, CONSTRAINT_TYPEFROM USER_CONSTRAINTSWHERE TABLE_NAME=’STUDENT’;表明必須採用大寫字母，因為Oracle是以大寫字母儲存表名的 5.4 、查看錶空間資訊一個資料表空間包括一個或多個物理資料檔案。我們可以獲得使用資料字典視圖USER_TABLESPACES所能得到的所有資料表空間的有關資訊。資料字典視圖USER_USERS為使用者提供了其帳號的有關資訊，以及永久的臨時的資料表空間 5.5 、注釋表和列建立資料表時可以為該表及其列添加註釋，添加註釋的目的是為編寫文檔的方便，添加註釋所使用的語句是COMMENT語句。例如：COMMENT ON TABLE student IS ‘Table holds students for INDO-S College’COMMENT ON COLUMN employee.Lname IS ‘last name’可以分別使用資料字典視圖ALL_TAB_COMMENTS 和ALL_COL_COMMENTS，查看錶和列的所有注釋資訊 6 、修改已存在的資料表要修改已經存在的表，某些修改是允許的，有些是不允許的 6.1 、向已存在的表添加新列ALTER TABLE tablename    ADD colname datatype； 6.2 、修改已存在的列ALTER TABLE tablename    MODIFY colname newdatatype； 6.3 、添加一個約束ALTER TABLE tablename    ADD [CONSTRAINT constraint_name] constraint_type（Column name）； 6.4 、刪除一列ALTER TABLE tablename        DROP COLUMN columnname； 6.5 、刪除一個約束ALTER TABLE tablenameDROP CONSTRAINT constraintname； 6.6 、啟動/ 禁用約束啟用或禁用約束的格式是：ALTER TABLE tablenameENABLE | DISABLE CONSTRAINT constraintname； 6.7 、重新命名一列ALTER TABLE tablename RENAME COLUMN oldname TO newname； 6.8 、重新命名一個約束ALTER TABLE tablename RENAME CONSTRAINT oldname TO newname； 6.9 、修改表的儲存屬性ALTER TABLE tablename STORAGE（NEXT nK） 7 、刪除資料表    當刪除一張資料表時，表中所有的資料以及表結構將被永久性地刪除DROP TABLE tablename； 8 、重新命名資料表RENAME oldtablename TO newtablename；