A07_DDL与Oracle数据库对象

DDL与数据库对象课程代码：AMPC0103V20课程介绍目的：了解DDL指令与常规数据库对象操作。内容：数据库表及相关DDL操作、使用约束、视图操作、索引操作、使用序列、使用同义词。重点：表、约束难点：索引数据库对象表约束视图索引序列同义词常用数据库对象•命名规则：必须以字母开头可以包含字母、数字、_、$、和#同一方案（用户）下的对象不能重名不能使用Oracle的保留字对象描述表存放数据的基本数据库对象，由行（记录）和列（字段）组成约束条件执行数据校验，保证数据完整性的系列规则视图表中数据的逻辑显示索引根据表中指定的字段建立起来的顺序，用于提高查询性能序列一组有规律的整数值同义词对象的别名一.表创建表修改表结构删除表重命名表数据字典创建表•基本语法：•举例：•说明：在向表中添加记录时如未显式指定某个字段的值，则该字段会被赋为此缺省值，如果在定义表时未设置过该字段的缺省值，则会被赋值为NULL。CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][,...]);CREATETABLEscott.test1(eidNUMBER(10),nameVARCHAR2(20),hiredateDATEDEFAULTSYSDATE,salaryNUMBER(8,2)DEFAULT1500);使用子查询创建表•在创建表的同时，可以将子查询的结果直接插入其中。•语法：•举例：•说明：子查询中查询字段列表中如果出现了表达式，则应指定其表达式别名，否则就必须显式指定新建表的字段名。CREATETABLE[schema.]table(column[,...])ASsubquery;CREATETABLEmyemp(编号,姓名,年薪)ASSELECTempno,ename,sal*12FROMemp;CREATETABLEmyemp2ASSELECTempno,ename,sal*12annsalFROMemp;修改表结构•使用ALTERTABLE语句可修改表的结构，包括：添加字段修改字段删除字段禁用字段•ALTER语句为DDL指令，一经执行不可撤消添加字段•在ALTERTABLE语句中，使用ADD子句指定新添加的字段列表，新字段只能被加到整个表的最后。•语法：•举例：ALTERTABLEtableADD(columndatatype[DEFALUTexpr][,columndatatype]...);ALTERTABLEtest1ADD(gradeNUMBER(3),phoneVARCHAR2(20)DEFAULT'无');修改字段•在ALTERTABLE语句中，使用MODIFY子句可修改现有字段属性，包括字段的数据类型、大小和默认值等。•语法：•举例：ALTERTABLEtableMODIFY(columndatatype[DEFALUTexpr][,columndatatype]...);ALTERTABLEtest1MODIFY(gradeNUMBER(2),phoneVARCHAR2(15)DEFAULT'010-12345678');删除字段•在ALTERTABLE语句中，使用DROP子句可删除现字段、并释放相应的存储空间。•语法：•举例：ALTERTABLEtableDROPCOLUMNcolumn;ALTERTABLEtableDROP(column[,column]);ALTERTABLEtest1DROPCOLUMNsalary;ALTERTABLEtest1DROP(grade,phone);禁用字段•在业务高峰期，可使用SETUNUSED子句可以将表中字段设为“无用”状态、待稍后再做删除。•语法：•举例：ALTERTABLEtableSETUNUSEDCOLUMNcolumn;ALTERTABLEtableSETUNUSED(column,...);ALTERTABLEtest1SETUNUSEDCOLUMNhiredate;ALTERTABLEtest1SETUNUSED(name,salary);删除表•丢弃表：使用DROP语句，销毁表结构、释放空间语法：举例：•清除表中数据：使用TRUNCATE语句，保留表结构语法：举例：DROPTABLEtable;DROPTABLEtest1;TRUNCATETABLEtable;TRUNCATETABLEmyemp1;重命名表•使用RENAME语句可以改变现有表的名称也可修改其他数据库对象（视图、序列、同义词等）的名称执行重命名操作的必须是对象的所有者为DDL语句，一经执行不可撤消•语法：•举例：RENAMEobject_nameTOnew_object_name;RENAMEmyemp2TOmm2;数据字典•Oracle数据库中的表可分为两类：用户表：由用户自己创建并维护，其中保存用户数据；数据字典表：由Oracle数据库自动创建并维护的一组表，其中保存的是数据库相关自身信息，由一系列只读的表和视图组成。•数据字典内容：数据库的物理和逻辑结构对象的定义和空间分配完整性约束条件用户角色权限审计记录数据字典分类及访问•数据字典主要可分为三类dba-所有方案包含的对象信息all-用户可以访问的对象信息user-用户方案的对象信息•访问数据字典：--查看当前用户拥有的所有表的名字SELECTtable_nameFROMuser_tables;--查看当前用户可以访问的所有表的名字SELECTtable_nameFROMall_tables;--查看当前用户拥有的所有对象的类型SELECTDISTINCTobject_typeFROMuser_objects;--查看所有用户拥有的所有对象的类型（执行本指令需具备DBA身份）SELECTtable_nameFROMdba_tables;二.约束约束概述非空约束唯一性约束主键约束外键约束检查约束约束管理约束概述•约束（Constraint）是在表上强制执行的数据校验规则，用于保护数据的完整性。Oracle使用SYS_Cn格式自动命名约束，也可以由用户命名。可在建表的同时创建约束，也可以在建表后单独添加约束。可以在表级或列级定义约束。可以通过数据字典视图查看约束。•约束分类：notnull（非空）uniquekey（唯一键）primarykey（主键）foreignkey（外键）check（检查）建表的同时创建约束•语法：CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][column_constraints],…[table_constraints]);非空约束•非空（NOTNULL）约束用于确保字段值不能为空（NULL），非空约束只能在字段级定义。•举例：CREATETABLEstudent(sidNUMBER(3)NOTNULL,nameVARCHAR2(20),birthDATECONSTRAINTstudent_birth_nnNOTNULL);唯一性约束•唯一性（UNIQUE）约束用于确保其所约束的字段或字段组合不出现重复的值。唯一性约束的字段允许出现空值。Oracle会自动为唯一性约束创建对应的唯一性索引唯一性约束既可以在字段级定义、也可以在表级定义CREATETABLEstudent(sidNUMBER(3)UNIQUE,nameVARCHAR2(20));CREATETABLEstudent(sidNUMBER(3),nameVARCHAR2(20),CONSTRAINTstudent_sid_unUNIQUE(sid));主键约束•主键（PRIMARYKEY）用于唯一标识表中的某一行记录，其功能上相当于非空且唯一。一个表中只允许一个主键，可以是单个字段或多字段的组合。Oracle会自动为主键字段创建对应的唯一性索引。主键约束既可以在字段级定义、也可以在表级定义。CREATETABLEstudent(sidNUMBER(3)PRIMARYKEY,nameVARCHAR2(20));CREATETABLEstudent(sidNUMBER(3),nameVARCHAR2(20),CONSTRAINTstudent_sid_pkPRIMARYKEY(sid));联合主键•由多个字段组合而成的主键称联合主键。联合主键中每一个字段都不能为空。联合主键字段组合的值不能出现重复。联合主键只能定义为表级约束。CREATETABLEscores(sidNUMBER(3),subjectVARCHAR2(20),scoreNUMBER(3),CONSTRAINTscore_sid_subject_pkPRIMARYKEY(sid,subject));INSERTINTOscoresVALUES(101,'语文',88);--合法INSERTINTOscoresVALUES(101,'数学',99);--合法INSERTINTOscoresVALUES(101,'语文',66);--非法INSERTINTOscoresVALUES(NULL,'语文',77);--非法外键约束•外键（FOREIGNKEY）约束用于确保相关的两个字段之间的参照关系。子表外键字段的值必须在主表被参照字段值的范围内，或者为空值NULL；外键参照的必须是主表的主键或者唯一键；主表主键/唯一键值被子表参照时，主表相应记录不允许被删除。外键约束（续）CREATETABLEempinfo(eidNUMBER(3)PRIMARYKEY,enameVARCHAR2(20),jobVARCHAR2(20),birthDATE);CREATETABLEsalary(eidNUMBER(3),basic_salaryNUMBER(8,2),job_allowanceNUMBER(8,2),travelling_allowanceNUMBER(8,2),personal_income_taxNUMBER(8,2),CONSTRAINTsalary_eid_fkFOREIGNKEY(eid)REFERENCESempinfo(eid));CREATETABLEsalary(eidNUMBER(3)REFERENCESempinfo(eid)，...);外键约束（续）•外键约束可以和主键约束同时使用。CREATETABLEsalary(eidNUMBER(3)PRIMARYKEY,...CONSTRAINTsalary_eid_fkFOREIGNKEY(eid)REFERENCESempinfo(eid));CREATETABLEsalary(eidNUMBER(3)PRIMARYKEYREFERENCESempinfo(eid),...);外键约束（续）•外键约束也可以构建于同一个表内部的两个字段之间--定义测试表，在其内部字段间定义参照完整性约束CREATETABLEmyemp(empnoNUMBER(4)PRIMARYKEY,enameVARCHAR2(20),jobVARCHAR2(9),mgrNUMBER(4)REFERENCESmyemp(empno),salNUMBER(7,2));--插入测试数据，操作合法（符合完整约束条件）INSERTINTOmyemp(empno,ename,mgr)VALUES(101,'Billy',NULL);INSERTINTOmyemp(empno,ename,mgr)VALUES(102,'John',101);INSERTINTOmyemp(empno,ename,mgr)VALUES(103,'Nancy',101);INSERTINTOmyemp(empno,ename,mgr)VALUES(104,'Tom',102);--操作非法（违反完整约束条件-未找到父项关键字）INSERTINTOmyemp(empno,ename,