當前位置:成語大全網 - 新華字典 - oraclede中數據定義語言

oraclede中數據定義語言

Oracle:數據定義語言(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:pk

FOREIGN KEY:fk

UNIQUE:uk

CHECK:ck

NOT 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_DATA

STORAGE(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_TYPE

FROM USER_CONSTRAINTS

WHERE 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 tablename

DROP CONSTRAINT constraintname;

6.6、啟動/禁用約束

啟用或禁用約束的格式是:

ALTER TABLE tablename

ENABLE | 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;