SQL基礎(chǔ)：使用 DDL 語句創(chuàng)建和管理表

使用DDL語句

創(chuàng)建和管理表目標(biāo)通過本章學(xué)習(xí)，您將可以:

描述主要的數(shù)據(jù)庫對象回顧表結(jié)構(gòu)描述各種數(shù)據(jù)類型。創(chuàng)建簡單的表了解隨表創(chuàng)建而生成的約束描述數(shù)據(jù)庫對象的工作數(shù)據(jù)庫對象對象描述表基本的數(shù)據(jù)存儲集合，由行和列組成。視圖從表中抽出的邏輯上相關(guān)的數(shù)據(jù)集合。序列提供有規(guī)律的數(shù)值。索引提高查詢的效率同義詞給對象起別名命名規(guī)則表名和列名:必須以字母開頭必須在1–30個字符之間必須只能包含A–Z,a–z,0–9,_,$,和#必須不能和用戶定義的其他對象重名必須不能是Oracle的保留字必須具備:CREATETABLE

權(quán)限存儲空間必須指定:表名列名,數(shù)據(jù)類型,尺寸CREATETABLE

語句CREATETABLE[schema.]table(column

datatype[DEFAULTexpr][,...]);引用其他用戶的表其他用戶定義的表不在當(dāng)前用戶的方案中應(yīng)該使用用戶名座位前綴，引用其他用戶定義的對象USERBUSERASELECT*FROMuserB.employees;SELECT*FROMuserA.employees;插入時為一個列指定默認(rèn)值字符串,表達(dá)式,或SQL函數(shù)都是合法的其它列的列名和偽列是非法的默認(rèn)值必須滿足列的數(shù)據(jù)類型定義DEFAULT選項...hire_dateDATEDEFAULTSYSDATE,...

CREATETABLEhire_dates

(idNUMBER(8),hire_dateDATEDEFAULTSYSDATE);Tablecreated.創(chuàng)建表創(chuàng)建表.確認(rèn)DESCRIBEdeptCREATETABLEdept

(deptnoNUMBER(2),dnameVARCHAR2(14),locVARCHAR2(13),

create_dateDATEDEFAULTSYSDATE);Tablecreated.數(shù)據(jù)類型數(shù)據(jù)類型描述VARCHAR2(size)可變長字符數(shù)據(jù)CHAR(size)定長字符數(shù)據(jù)NUMBER(p,s)

可變長數(shù)值數(shù)據(jù)DATE日期型數(shù)據(jù)LONG可變長字符數(shù)據(jù)，最大可達(dá)到2GCLOB字符數(shù)據(jù)，最大可達(dá)到4GRAWandLONGRAW

裸二進(jìn)制數(shù)據(jù)BLOB二進(jìn)制數(shù)據(jù)，最大可達(dá)到4GBFILE存儲外部文件的二進(jìn)制數(shù)據(jù)，最大可達(dá)到4GROWID行地址日期數(shù)據(jù)類型可以使用集中日期類的數(shù)據(jù)類型:DataTypeDescriptionTIMESTAMPDatewithfractionalsecondsINTERVALYEARTOMONTHStoredasanintervalofyears

andmonthsINTERVALDAYTOSECONDStoredasanintervalofdays,hours,minutes,andseconds日期數(shù)據(jù)類型TIMESTAMP

數(shù)據(jù)類型是對DATE

數(shù)據(jù)類型的擴(kuò)展。按DATE數(shù)據(jù)類型存放年,月,日,小時,分鐘,秒以及微秒甚至納秒?？梢噪S意的指定時區(qū)。TIMESTAMP[(fractional_seconds_precision)]TIMESTAMP[(fractional_seconds_precision)]WITHTIMEZONETIMESTAMP[(fractional_seconds_precision)]WITHLOCALTIMEZONE日期數(shù)據(jù)類型INTERVAL

YEAR

TO

MONTH

存放若干年和若干月的一個時間段:INTERVALDAYTOSECOND

存放若干天到若干秒的一個時間段：INTERVALYEAR[(year_precision)]TOMONTHINTERVALDAY[(day_precision)]TOSECOND[(fractional_seconds_precision)]INTERVALDAYTOSECOND

數(shù)據(jù)類型INTERVALDAYTOSECOND存放若干天到若干秒的一個時間段。INTERVAL'45:12:10.222'DAYTOSECOND(3)Indicates4days,5hours,12minutes,10seconds,and222thousandthsofasecond.INTERVAL'45:12'DAYTOMINUTEIndicates4days,5hoursand12minutes.INTERVAL'4005'DAY(3)TOHOURIndicates400days5hours.INTERVAL'11:12:10.2222222'HOURTOSECOND(7)indicates11hours,12minutes,and10.2222222seconds.內(nèi)置約束約束是表級的強(qiáng)制規(guī)定約束拒絕刪除表中有關(guān)聯(lián)關(guān)系的數(shù)據(jù)以下有效的約束類型:NOTNULLUNIQUEPRIMARYKEYFOREIGNKEYCHECK約束注意事項如果不指定約束名Oracleserver自動按照SYS_Cn

的格式指定約束名在什么時候創(chuàng)建約束:建表的同時建表之后可以在表級或列級定義約束可以通過數(shù)據(jù)字典視圖查看約束定義約束語法:列級約束:表級約束:CREATETABLE[schema.]table(column

datatype[DEFAULTexpr]

[column_constraint],...[table_constraint][,...]);column,...

[CONSTRAINTconstraint_name]constraint_type(column,...),column

[CONSTRAINTconstraint_name]constraint_type,定義約束列級約束:

表級約束:CREATETABLEemployees(employee_idNUMBER(6)

CONSTRAINTemp_emp_id_pkPRIMARYKEY,first_nameVARCHAR2(20),...);CREATETABLEemployees(employee_idNUMBER(6),first_nameVARCHAR2(20),...job_idVARCHAR2(10)NOTNULL,

CONSTRAINTemp_emp_id_pkPRIMARYKEY(EMPLOYEE_ID));12NOTNULL約束保證列值不能為空:NOTNULL

約束(這一列不允許出現(xiàn)空值.)無NOTNULL

約束

(這一列的每一行都可以出現(xiàn)空值.)NOTNULL

約束…UNIQUE

約束EMPLOYEESUNIQUE

約束INSERTINTO不允許:已經(jīng)存在允許…UNIQUE約束可以定義在表級或列級:CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,emailVARCHAR2(25),salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATENOTNULL,...CONSTRAINTemp_email_ukUNIQUE(email));PRIMARYKEY約束DEPARTMENTS

PRIMARYKEYINSERTINTO不允許(空值)不允許(50已經(jīng)存在)…FOREIGNKEY約束DEPARTMENTS

EMPLOYEESFOREIGN

KEYINSERTINTO不允許(9不存在)允許PRIMARY

KEY……FOREIGNKEY約束可以定義在表級或列級:CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,emailVARCHAR2(25),salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATENOTNULL,...department_idNUMBER(4),CONSTRAINTemp_dept_fkFOREIGNKEY(department_id)REFERENCESdepartments(department_id),CONSTRAINTemp_email_ukUNIQUE(email));FOREIGNKEY約束:

關(guān)鍵字FOREIGNKEY:在表級指定子表中的列REFERENCES:標(biāo)示在父表中的列ONDELETECASCADE:當(dāng)父表中的列被刪除是，子表中相對應(yīng)的列也被刪除ONDELETESETNULL:子表中相應(yīng)的列置空CHECK約束定義每一行必須滿足的條件以下的表達(dá)式是不允許的:出現(xiàn)CURRVAL,NEXTVAL,LEVEL,和ROWNUM

偽列使用SYSDATE,UID,USER,和USERENV

函數(shù)在查詢中涉及到其它列的值...,salary NUMBER(2)CONSTRAINTemp_salary_minCHECK(salary>0),...創(chuàng)建表:示例CREATETABLEemployees(employee_idNUMBER(6)CONSTRAINTemp_employee_idPRIMARYKEY,first_nameVARCHAR2(20),last_nameVARCHAR2(25)CONSTRAINTemp_last_name_nnNOTNULL,emailVARCHAR2(25)CONSTRAINTemp_email_nnNOTNULLCONSTRAINTemp_email_ukUNIQUE,phone_numberVARCHAR2(20),hire_dateDATECONSTRAINTemp_hire_date_nnNOTNULL,job_idVARCHAR2(10)CONSTRAINTemp_job_nnNOTNULL,salaryNUMBER(8,2)CONSTRAINTemp_salary_ckCHECK(salary>0),commission_pctNUMBER(2,2),manager_idNUMBER(6),department_idNUMBER(4)CONSTRAINTemp_dept_fkREFERENCESdepartments(department_id));UPDATEemployees*ERRORatline1:ORA-02291:integrityconstraint(HR.EMP_DEPT_FK)violated-parentkeynotfoundUPDATEemployeesSETdepartment_id=55WHEREdepartment_id=110;違反約束Department55不存在。違反約束在另一個表中為外鍵的主鍵行中的數(shù)據(jù)不能被刪除DELETEFROMdepartmentsWHEREdepartment_id=60;DELETEFROMdepartments*ERRORatline1:ORA-02292:integrityconstraint(HR.EMP_DEPT_FK)violated-childrecordfound使用子查詢創(chuàng)建表時候用ASsubquery

選項，將創(chuàng)建表和插入數(shù)據(jù)