第9章表和约束课件

CREATETABLE语句必须有：–CREATETABLE权限指定：–表名–列名、列数据类型和列的大小CREATETABLE[schema.]table (column

datatype[DEFAULTexpr][,...]);创建表

DESCRIBEdeptCREATETABLEDOSSIER(IDNUMBER(4),CNAMEVARCHAR2(20),BIRTHDAYDATE,STATURENUMBER(3),WEIGHTNUMBER(5,2),COUNTRY_CODECHAR(2)DEFAULT‘01’);

默认值应用插入默认值insertintodossier(ID,CNAME,BIRTHDAY,STATURE,WEIGHT)values(2,'姚明',to_date('1980.9.12','yyyy.mm.dd'),226,134);已创建1行。使用默认值修改updatedossiersetcountry_code=defaultwhereid=2;使用子查询创建表使用子查询创建表的语法CREATETABLEtable [(column,column...)]ASsubquery;新表的字段列表必须与子查询中的字段列表匹配字段列表可以省略引用另一个用户的表SELECT*FROMscott.emp;如果一个表不属于当前用户，如果引用它，必须把方案名放在表名的前面。例如，scott.emp

Oracle中表的分类Oracle数据库中的表分为下面两类：–用户表：由用户创建和维护的表的集合，它包含用户所使用的数据。–数据字典：由Oracle服务器创建和维护的表的集合，它包含数据库信息，比如是表的定义，数据库结构的信息等，可以把它理解为表的表，由Oracle服务器创建和维护。如user_tables。使用数据字典查询数据字典SELECTtable_nameFROMuser_tables;察看数据字典结构DESCuser_tables;数据类型DataType DescriptionVARCHAR2(size)

可变长度的字符数据CHAR(size) 固定长度的字符数据NUMBER(p,s)

数字数据DATE

日期和时间值LONG

最大2G的可变长度字符数据

CLOB 最大4G的字符数据RAWandLONGRAW

原始二进制数据BLOB

最大4G的二进制数据BFILE 最大4G的，存储在外部文件中的二进制数据ROWID

一个64进制的数制系统，表示表中一行的唯一地址

ALTERTABLE语句用ALTERTABLE语句来：•添加一个新列•修改一个已存在的列•为新列定义一个默认值•删除一个列

ALTERTABLE语句用ALTERTABLE语句添加、修改或删除列ALTERTABLEtableADD (columndatatype[DEFAULTexpr] [,columndatatype]...);ALTERTABLEtableMODIFY (columndatatype[DEFAULTexpr] [,columndatatype]...);ALTERTABLEtableDROP (column);添加列DEPT80“添加一个新列到DEPT80表”DEPT80Newcolumn添加新列•用ADD字句添加列•新列成为最后的列ALTERTABLEdept80ADD (job_idVARCHAR2(9));Tablealtered.修改列•可以改变列的数据类型、大小和默认值•对默认值的改变只影响后来插入表中的数据ALTERTABLE dept80MODIFY (last_nameVARCHAR2(30));Tablealtered.删除列用DROPCOLUMN子句从表中删除列ALTERTABLEdept80DROPCOLUMNjob_id;Tablealtered.删除表•在表中的所有数据和结构都被删除•任何未决的事务都被提交•所有的索引被删除•你不能回退DROPTABLE语句DROPTABLEdept80;Tabledropped.改变一个对象的名字•执行RENAME语句，改变一个表、视图、序列或同义词Youmustbetheowneroftheobject.RENAMEdeptTOdetail_dept;Tablerenamed.截断表•TRUNCATETABLE语句：–删除表中所有的行–释放该表所使用的存储空间•不能回退用TRUNCATE删除的行•作为选择，可以用DELETE语句删除行TRUNCATETABLEdetail_dept;Tabletruncated.添加注释到表中•用COMMENT语句添加注释到一个表或列中•注释能够通过数据字典视图查看：–ALL_COL_COMMENTS–USER_COL_COMMENTS–ALL_TAB_COMMENTS–USER_TAB_COMMENTSCOMMENTONTABLEemployeesIS'EmployeeInformation';Commentcreated.Oracle中约束类型•约束强制规则在表级•如果有从属关系，约束防止表的删除•下面的约束类型是有效的：NOTNULLUNIQUEPRIMARYKEYFOREIGNKEYCHECK约束原则•命名一个约束，或者由Oracle用SYS_Cn格式产生一个名字•创建一个约束：–在创建表的同时，或者–在创建表之后•在列或者表级定义一个约束•在数据字典中查看约束

定义约束CREATETABLE[schema.]table (column

datatype[DEFAULTexpr] [column_constraint], ... [table_constraint][,...]);CREATETABLEemployees( employee_idNUMBER(6), first_nameVARCHAR2(20), ... job_idVARCHAR2(10)NOTNULL, CONSTRAINTemp_emp_id_pk PRIMARYKEY(EMPLOYEE_ID));定义约束•列级约束

•表级约束column[CONSTRAINTconstraint_name]constraint_type,column,...

[CONSTRAINTconstraint_name]constraint_type(column,...),NOTNULL约束确保某些列不允许空值：NOTNULL约束(对于该列来说没有行能够包含一个空值)缺少NOTNULL约束(对于该列来说任何行都能包含空值)NOTNULL约束…CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATECONSTRAINTemp_hire_date_nnNOTNULL,...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已经存在)…CREATETABLEdepartments(department_idNUMBER(4),department_nameVARCHAR2(30)CONSTRAINTdept_name_nnNOTNULL,manager_idNUMBER(6),location_idNUMBER(4),CONSTRAINTdept_id_pkPRIMARYKEY(department_id));PRIMARYKEY约束既可以定义在表级也可以定义在列级：

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),...添加约束语法用ALTERTABLE语句：•添加或删除约束，但不修改它的结构•启用或禁用约束•用MODIFY子句添加一个NOTNULL约束

ALTERTABLE tableADD[CONSTRAINTconstraint]type(column);添加约束添加一个FOREIGNKEY约束到EMPLOYEES表，指示经理必须已经是EMPLOYEES表中的ALTERTABLEemployeesADDCONSTRAINTemp_manager_fkFOREIGNKEY(manager_id)REFERENCESemployees(employee_id);Tablealtered.删除约束•从EMPLOYEES表中删除经理约束•删除DEPARTMENTS表上的PRIMARYKEY约束，并且删除相关联的在EMPLOYEES.DEPARTMENT_ID列上的FOREIGNKEY约束ALTERTABLEemployeesDROPCONSTRAINTemp_manager_fk;Tablealtered.ALTERTABLE departmentsDROPPRIMARYKEYCASCADE;Tablealtered.禁用约束•执行ALTERTABLE语句的DISABLE子句来禁用完整性约束•应用CASCADE选项禁用相依赖的完整性约束ALTERTABLE employeesDISABLECONSTRAINT emp_emp_id_pkCASCADE;Tablealtered.启用约束•用ENABLE字句启用一个在表中定义的当前禁用的完整性约束

•如果启用一个UNIQUE键或PRIMARYKEY约束一个UNIQUE或PRIMARYKEY索引被自动创建ALTERTABLE employeesENABLECONSTRAINT emp_emp_id_pk;Tablealtered.SELECT constraint_name,constraint_type, search_conditionFROM user_constraintsWHERE table_name='EMPLOYEES';查看约束查询USER_CONSTRAINTS表来查看所有约束定义和命名…在约束类型中，C代表CHECK，P代表PRIMARYKEY，R代表FORE