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

使用DDL語句

創(chuàng)建和管理表目標通過本章學習，您將可以:

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

權限存儲空間必須指定:表名列名,數據類型,尺寸CREATETABLE

語句CREATETABLE[schema.]table(column

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

CREATETABLEhire_dates

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

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

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

可變長數值數據DATE日期型數據LONG可變長字符數據，最大可達到2GCLOB字符數據，最大可達到4GRAWandLONGRAW

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

andmonthsINTERVALDAYTOSECONDStoredasanintervalofdays,hours,minutes,andseconds日期數據類型TIMESTAMP

數據類型是對DATE

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

YEAR

TO

MONTH

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

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

數據類型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.內置約束約束是表級的強制規(guī)定約束拒絕刪除表中有關聯關系的數據以下有效的約束類型:NOTNULLUNIQUEPRIMARYKEYFOREIGNKEYCHECK約束注意事項如果不指定約束名Oracleserver自動按照SYS_Cn

的格式指定約束名在什么時候創(chuàng)建約束:建表的同時建表之后可以在表級或列級定義約束可以通過數據字典視圖查看約束定義約束語法:列級約束:表級約束: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

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

約束

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

約束…UNIQUE

約束EMPLOYEESUNIQUE

約束INSERTINTO不允許:已經存在允許…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已經存在)…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約束:

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

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

函數在查詢中涉及到其它列的值...,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不存在。違反約束在另一個表中為外鍵的主鍵行中的數據不能被刪除DELETEFROMdepartmentsWHEREdepartment_id=60;DELETEFROMdepartments*ERRORatline1:ORA-02292:integrityconstraint(HR.EMP_DEPT_FK)violated-childrecordfound使用子查詢創(chuàng)建表時候用ASsubquery

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