A07-DDL与Oracle数据库对象

1、DDL与数据库对象课程代码：AMPC0103V20课程介绍绍目的：了了解DDL指令与常常规数据据库对象象操作。内容：数数据库表表及相关关DDL操作、使使用约束束、视图图操作、索引操操作、使使用序列列、使用用同义词词。重点：表表、约束束难点：索索引数据库对对象表约束视图索引序列同义词常用数据据库对象象命名规则则：必须以字字母开头头可以包含含字母、数字、_、$、和#同一方案案（用户户）下的的对象不不能重名名不能使用用Oracle的保留字字 对象描 述 表存放数据的基本数据库对象，由行（记录）和列（字段）组成 约束条件执行数据校验，保证数据完整性的系列规则 视图表中数据的逻辑显示 索引根据表中指定的

2、字段建立起来的顺序，用于提高查询性能 序列一组有规律的整数值 同义词对象的别名一.表表创建表修改表结结构删除表重命名表表数据字典典创建表基本语法法：举例：说明：在在向表中中添加记记录时如如未显式式指定某某个字段段的值，则该字字段会被被赋为此此缺省值值，如果果在定义义表时未未设置过过该字段段的缺省省值，则则会被赋赋值为NULL。CREATETABLEschema.table(columndatatypeDEFAULTexpr,.);CREATETABLEscott.test1(eidNUMBER(10),name VARCHAR2(20),hiredateDATE DEFAULTSYSDATE,

3、salaryNUMBER(8,2)DEFAULT1500);使用子查查询创建建表在创建表表的同时时，可以以将子查查询的结结果直接接插入其其中。语法：举例：说明：子子查询中中查询字字段列表表中如果果出现了了表达式式，则应应指定其其表达式式别名，否则就就必须显显式指定定新建表表的字段段名。CREATETABLEschema.table(column,.) AS subquery;CREATETABLEmyemp(编号,姓名,年薪)ASSELECTempno, ename,sal*12FROMemp;CREATETABLEmyemp2ASSELECTempno, ename,sal*12annsal

4、 FROMemp;修改表结结构使用ALTER TABLE语句可可修改表表的结构构，包括括：添加字段段修改字段段删除字段段禁用字段段ALTER语句句为DDL指令令，一经经执行不不可撤消消添加字段段在ALTERTABLE语句中，使用ADD子子句指定定新添加加的字段段列表，新字段段只能被被加到整整个表的的最后。语法：举例：ALTERTABLEtableADD(columndatatypeDEFALUTexpr,columndatatype .);ALTERTABLEtest1ADD(gradeNUMBER(3),phoneVARCHAR2(20)DEFAULT无);修改字段段在ALTERTABLE语

5、句中，使用MODIFY子句可修修改现有有字段属属性，包包括字段段的数据据类型、大小和和默认值值等。语法：举例：ALTERTABLEtableMODIFY(columndatatypeDEFALUTexpr,columndatatype .);ALTERTABLEtest1MODIFY(gradeNUMBER(2),phoneVARCHAR2(15)DEFAUL;删除字段段在ALTERTABLE语句中，使用DROP子句可删删除现字字段、并并释放相相应的存存储空间间。语法：举例：ALTERTABLEtableDROP COLUMNcolumn;ALTERTABLEtab

6、leDROP (column,column);ALTERTABLEtest1DROP COLUMNsalary;ALTERTABLEtest1DROP (grade,phone);禁用字段段在业务高峰峰期，可可使用SETUNUSED子句可以以将表中中字段设设为“无无用”状状态、待待稍后再再做删除除。语法：举例：ALTERTABLEtableSETUNUSED COLUMNcolumn;ALTERTABLEtableSETUNUSED (column,.);ALTERTABLEtest1SETUNUSED COLUMNhiredate;ALTERTABLEtest1SETUNUSED (name

7、,salary);删除表丢弃表：使用DROP语句，销毁表表结构、释放空空间语法：举例：清除表中中数据：使用TRUNCATE语句句，保留留表结构构语法：举例：DROP TABLE table;DROP TABLE test1;TRUNCATETABLEtable;TRUNCATETABLEmyemp1;重命名表表使用RENAME语句句可以改改变现有有表的名名称也可修改改其他数数据库对对象（视视图、序序列、同同义词等等）的名名称执行重命命名操作作的必须须是对象象的所有有者为DDL语句，一经执执行不可可撤消语法：举例：RENAMEobject_nameTOnew_object_name;RENAME

8、myemp2 TO mm2;数据字典典Oracle数据库中中的表可可分为两两类：用户表：由用户户自己创创建并维维护，其其中保存存用户数数据；数据字典典表：由由Oracle数据库自自动创建建并维护护的一组组表，其其中保存存的是数数据库相相关自身身信息，由一系系列只读读的表和和视图组组成。数据字典典内容：数据库的的物理和和逻辑结结构对象的定定义和空空间分配配完整性约约束条件件用户角色权限审计记录录数据字典典分类及及访问数据字典典主要可可分为三三类dba-所有方案案包含的的对象信信息all-用户可以以访问的的对象信信息user -用户方案案的对象象信息访问数据据字典：-查看当前前用户拥拥有的所所有表

9、的的名字SELECTtable_nameFROM user_tables;-查看当前前用户可可以访问问的所有有表的名名字SELECTtable_nameFROM all_tables;-查看当前前用户拥拥有的所所有对象象的类型型SELECTDISTINCTobject_typeFROMuser_objects;-查看所有有用户拥拥有的所所有对象象的类型型（执行行本指令令需具备备DBA身份）SELECTtable_nameFROM dba_tables;二.约约束约束概述述非空约束束唯一性约约束主键约束束外键约束束检查约束束约束管理理约束概述述约束（Constraint）是在在表上强强制执行行的

10、数据据校验规规则，用用于保护护数据的的完整性性。Oracle使使用SYS_Cn格式式自动命命名约束束，也可可以由用用户命名名。可在建表表的同时时创建约约束，也也可以在在建表后后单独添添加约束束。可以在表表级或列列级定义义约束。可以通过过数据字字典视图图查看约约束。约束分类类：notnull（非空）uniquekey（唯一键）primary key（主键）foreign key（外键）check（检查）建表的同同时创建建约束语法：CREATETABLEschema.table(columndatatypeDEFAULTexprcolumn_constraints,table_constraint

11、s);非空约束束非空（NOT NULL）约束用于于确保字字段值不不能为空空（NULL），非空约约束只能能在字段段级定义义。举例：CREATETABLEstudent(sidNUMBER(3) NOTNULL,name VARCHAR2(20),birthDATE CONSTRAINTstudent_birth_nnNOTNULL);唯一性约约束唯一性（UNIQUE）约束束用于确确保其所所约束的的字段或或字段组组合不出出现重复复的值。唯一性约约束的字字段允许许出现空空值。Oracle会会自动为为唯一性性约束创创建对应应的唯一一性索引引唯一性约约束既可可以在字字段级定定义、也也可以在在表级定定义C

12、REATETABLEstudent(sidNUMBER(3) UNIQUE,name VARCHAR2(20);CREATETABLEstudent(sidNUMBER(3),name VARCHAR2(20),CONSTRAINT student_sid_un UNIQUE(sid);主键约束束主键（PRIMARY KEY）用用于唯一一标识表表中的某某一行记记录，其其功能上上相当于于非空且且唯一。一个表中中只允许许一个主主键，可可以是单单个字段段或多字字段的组组合。Oracle会会自动为为主键字字段创建建对应的的唯一性性索引。主键约束束既可以以在字段段级定义义、也可可以在表表级定义义。CRE

13、ATETABLEstudent(sidNUMBER(3) PRIMARYKEY,name VARCHAR2(20);CREATETABLEstudent(sidNUMBER(3),name VARCHAR2(20),CONSTRAINT student_sid_pk PRIMARYKEY(sid);联合主键键由多个字字段组合合而成的的主键称称联合主主键。联合主键键中每一一个字段段都不能能为空。联合主键键字段组组合的值值不能出出现重复复。联合主键键只能定定义为表表级约束束。CREATETABLEscores(sidNUMBER(3),subject VARCHAR2(20),scoreNUMBE

14、R(3),CONSTRAINT score_sid_subject_pk PRIMARYKEY(sid,subject);INSERTINTOscoresVALUES(101,语文,88);-合法INSERTINTOscoresVALUES(101,数学,99);-合法INSERTINTOscoresVALUES(101,语文,66);-非法INSERTINTOscoresVALUES(NULL, 语文,77);-非法外键约束束外键（FOREIGN KEY）约约束用于于确保相相关的两两个字段段之间的的参照关关系。子表外键键字段的的值必须须在主表表被参照照字段值值的范围围内，或或者为空空值NUL

15、L；外键参照照的必须须是主表表的主键键或者唯唯一键；主表主键键/唯一键值值被子表表参照时时，主表表相应记记录不允允许被删删除。外键约束束（续）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_tax NUMBER(8,

16、2),CONSTRAINT salary_eid_fkFOREIGNKEY(eid)REFERENCES empinfo(eid);CREATETABLEsalary(eidNUMBER(3) REFERENCESempinfo(eid)，.);外键约束束（续）外键约束束可以和和主键约约束同时时使用。CREATETABLEsalary(eidNUMBER(3) PRIMARYKEY,.CONSTRAINT salary_eid_fkFOREIGNKEY(eid)REFERENCES empinfo(eid);CREATETABLEsalary(eidNUMBER(3) PRIMARYKEY R

17、EFERENCESempinfo(eid),.);外键约束束（续）外键约束束也可以以构建于于同一个个表内部部的两个个字段之之间-定义测试试表，在在其内部部字段间间定义参参照完整整性约束束CREATETABLEmyemp(empnoNUMBER(4)PRIMARYKEY,enameVARCHAR2(20),jobVARCHAR2(9),mgrNUMBER(4) REFERENCESmyemp(empno),salNUMBER(7,2);-插入测试试数据，操作合合法（符符合完整整约束条条件）INSERTINTOmyemp(empno,ename, mgr)VALUES(101,Billy,NULL

18、);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, mgr)VALUES(106,Billy,108);检查约束束检查（CHECK）约束用用于定义义数据字字段必须须要满足足的条件件，包括括数据的的取值范范

19、围、文文本内容容格式等等，具体体以条件件表达式式的形式式给出。检查约束束的条件件表达式式中不允允许出现现如下内内容：currval,nextval, level,rownum等伪列sysdate,uid, user,userenv等函数对其它字字段值的的引用CREATETABLEtest1(name VARCHAR2(20),ageNUMBER(3) CHECK(age=0ANDage =120);查看约束束查询用户户字典视视图user_constrains，可得到到用户的的所有约约束。查询用户户字典视视图user_cons_columns，可获知知约束建建立在哪哪些字段段上。SELECT*F

20、ROM user_constraints;SELECT*FROM user_cons_columns;建表后添添加约束束语法：举例：ALTERTABLEtableADDCONSTRAINTconstraint_nameconstraint_type(column,.);CREATETABLEstudent(sidNUMBER(10),name VARCHAR2(20);ALTERTABLEstudentADDCONSTRAINTstudent_sid_pkPRIMARY KEY(sid);删除约束束删除主键键约束的的另一种种方式ALTERTABLEtableDROP CONSTRAINTcon

21、straint_name CASCADE;CREATETABLEstudent(sidNUMBER(10),name VARCHAR2(20)CONSTRAINT student_name_nnNOTNULL,CONSTRAINT student_sid_pk PRIMARYKEY(sid);ALTERTABLEstudent DROPCONSTRAINTstudent_sid_pk;ALTERTABLEstudent DROPCONSTRAINTstudent_name_nn;ALTERTABLEtableDROP PRIMARYKEY;ALTERTABLEstudent DROPPRIMA

22、RYKEY;删除级连连约束在删除约约束时，如果还还存在与与该约束束相关联联的其它它约束，则删除除操作会会失败，此时可可使用CASCADE子句将其其它关联联约束一一并删除除。CREATETABLEempinfo(eidNUMBER(3) CONSTRAINTempinfo_eid_pkPRIMARYKEY,enameVARCHAR2(20),-);CREATETABLEsalary(eidNUMBER(3) REFERENCESempinfo(eid),-);ALTERTABLEempinfoDROP CONSTRAINTempinfo_eid_pkCASCADE;删除级连连约束（续）在删除表表

23、中字段段时，如如果该字字段处于于多字段段联合约约束条件件（联合合主键、联合唯唯一键、存在参参照当前前字段的的外键）中，则则删除会会失败，此时可可使用CASCADE CONSTRAINT子句将与与该字段段相关的的约束一一并删除除。CREATETABLErecord(student_id NUMBER(3),subject_id VARCHAR2(20),recordNUMBER(3),CONSTRAINT record_stuId_subId_pkPRIMARY KEY(student_id,subject_id);ALTERTABLErecordDROP (student_id)CASCADE

24、 CONSTRAINT;禁用/启用约束束ALTERTABLE语句中可可使用DISABLE CONSTRAINT子句禁用用已有约约束，还还可使用用CASCADE选项将相相关联的的约束一一并禁用用。相应地，可以使使用ENABLECONSTRAINT子句启用用先前被被禁用的的约束，但此时时无法再再使用CASCADE选项一并并启用相相关联的的其它约约束。ALTERTABLEtableDISABLE CONSTRAINTconstraint_name CASCADE;ALTERTABLEstudentDISABLE CONSTRAINTstudent_sid_pkCASCADE;ALTERTABLEst

25、udentENABLECONSTRAINTstudent_sid_pk;三.视视图什么是视视图基本视图图操作创建视图图查询视图图删除视图图重新编译译视图强制创建建视图创建复杂杂视图在视图上上执行更更新操作作添加数据据更新数据据删除数据据什么是视视图？视图（View）是从数数据表（或者其其它视图图）中提提取数据据而成的的一种虚虚拟表。视图优点点及分类类视图优点点：简化复杂杂数据查查询提高运行行效率屏蔽数据据库表结结构，实实现数据据逻辑独独立性限制数据据库访问问在相同数数据上提提供不同同的视图图，便于于数据共共享视图分类类： 特征项目简单视图复杂视图 基表数目一个一个或多个 使用函数无有 使用分组

26、无有 支持DML操作支持不一定创建视图图使用嵌入入子查询询的CREATEVIEW语句创创建视图图。语法：举例：CREATEOR REPLACE VIEWschema.view(alias,.)ASsubquery;WITHREAD ONLYCREATEORREPLACEVIEW myview1(编号,姓名,职位,工资)ASSELECTempno, ename,job, salFROM empWHEREdeptno=20;查询视图图DESC myview1;SELECT*FROM myview1;SELECT编号,工资FROM myview1WHERE职位=ANALYST;SELECT*FROM

27、 user_views;删除视图图语法：举例：DROP VIEWview;DROP VIEWmyview1;重新编译译视图如果视图图的基表表结构发发生变化化，比如如添加或或删除了了字段，则视图图状态会会被标记记为无效效，此后后当再有有用户访访问该视视图时，Oracle会自动动重新编编译该视视图，以以避免可可能的运运行错误误。此外外，用户户也可以以执行ALTERVIEW语句手手工编译译视图。语法：举例：ALTERVIEW viewCOMPILE;ALTERVIEW myview1COMPILE;强制创建建视图如果视图图定义中中所涉及及的基表表不存在在、或者者字段有有问题，则视图图创建操操作会失失

28、败。此此时可使使用FORCE选项强强制创建建视图，然后再再定义基基表或修修改基表表结构。语法：举例：CREATEOR REPLACE FORCE| NO FORCEVIEW schema.view(alias,.)ASsubquery;CREATEORREPLACEFORCEVIEW myview2ASSELECTempno, ename,job, salFROM emp2WHEREdeptno =20;创建复杂杂视图视图定义义中涉及及到了多多个基表表，或者者在视图图定义的的子查询询中使用用了函数数、复合合表达式式或数据据分组等等。CREATEORREPLACEVIEW v_sal(deptn

29、o,maxsal,minsal, avgsal)AS SELECTdeptno,max(sal),min(sal), avg(sal)FROM empGROUPBYdeptno;CREATEORREPLACEFORCEVIEW v_emp(工号,姓名,职位,年薪, 工龄(月),部门编号号,部门名称称)AS SELECTempno, ename,job, sal*12,months_between(SYSDATE,hiredate),emp.deptno,dnameFROM emp,deptWHEREemp.deptno =dept.deptno;在视图上上执行更更新操作作在可更新新视图上上进行

30、DML操作，可可以修改改基表中中数据。在视图上上执行数数据更新新操作，应遵循循如下原原则：用户还需需要拥有有对视图图基表进进行更新新操作的的权限；视图定义义的子查查询中不不能使用用分组函函数、GROUPBY子句、DISTINCT关键字、rownum伪列，其其查询字字段不能能为表达达式；由两个以以上基表表中导出出的视图图上不允允许进行行DELETE操作；基表中非非空的字字段在视视图定义义中未包包括、该该字段也也未曾设设置过缺缺省值，则不可可在视图图上进行行INSERT操作；在视图上上进行DML操作，语语法与在在表上操操作相同同。四.索索引索引概述述创建索引引查看索引引删除索引引索引概述述索引（I

31、ndex）一种用用于提升升查询效效率的数数据库对对象，使使用索引引可以快快速定位位数据、减少磁磁盘I/O操作次数数。常规索引引分类唯一性索索引和非非唯一性性索引单字段索索引和联联合索引引普通索引引和函数数索引B树索引和和位图索索引创建索引引的两种种方式自动创建建：在定定义主键键或唯一一键约束束时系统统会自动动在相应应的字段段上创建建唯一性性索引。手动创建建：用户户可在其其它列上上创建非非唯一性性索引以以加速查查询。创建索引引CREATEUNIQUE| BITMAPINDEXschema.indexONtable(column,column.);-创建测试试数据表表myempCREATETABL

32、EmyempASSELECT empno,ename,job,sal, deptnoFROM emp;-创建基于于ename字段的普普通索引引CREATEINDEXmyindexONmyemp(ename);-创建基于于deptno和sal字段的联联合索引引CREATEINDEXmyindex2ONmyemp(deptno,sal);-创建基于于empno字段的唯唯一性索索引CREATEUNIQUE INDEX myindex3 ON myemp(empno);-创建基于于job字段的位位图索引引CREATEBITMAP INDEX myindex4 ON myemp(job);-创建基于于l

33、ower(ename)、sal*12的函数索索引CREATEINDEXmyindex5ONmyemp(lower(ename);CREATEINDEXmyindex6ONmyemp(sal*12);查看索引引查询用户户字典视视图user_indexes，可获取取索引名名称、类类型、所所在表名名、表拥拥有者等信息。查询用户户字典视视图user_ind_columns，可获取取索引具具体建立立在哪个个字段上上。SELECT*FROMuser_indexes;SELECT*FROMuser_ind_columnsWHEREtable_name=myemp;删除索引引索引一经经创建、不可以以修改，如果

34、要要修改则则应先删删除掉再再重新创创建。如果不再再需要，可以手手工删除除索引。在删除数数据表和和字段时时系统会会自动删删除相关关索引。语法：举例：DROP INDEXindex_name;DROP INDEX myindex1;索引优缺缺点优点：提升查询询效率缺点建立索引引会增加加物理区区域开销销插入、修修改、删删除数据据时效率率变低原则很少做查查询条件件的字段段不要建建索引逻辑字段段(性别别)不要要建索引引其他索引引函数、全全局、分分区、唯唯一、非非唯一、位图、反向五.序序列创建序列列使用序列列修改序列列删除序列列序列概述述序列（Sequence）实际上上是一种种“序列列号”生生成器，它可以

35、以自动生生成一组组唯一的的整型数数字，其其典型用用途是为为数据表表的主键键字段赋赋值（代代替应用用程序编编号）。序列是是用户创创建的数数据库对对象，可可以被多多个用户户共享。创建序列列Oracle数数据库使使用CREATESEQUENCE语句句创建序序列。语法：举例：CREATESEQUENCEschema.sequenceINCREMENT BY nSTARTWITHnMAXVALUEn|NOMAXVALUEMINVALUEn|NOMINVALUECYCLE|NOCYCLE-是否循环环CACHEn|NOCACHE-是否预设设值ORDER|NOORDER;-并发读取取CREATESEQUENCEmysequence1INCREMENT BY 1STARTWITH1 NOMAXVALUENOCYCLE;CREATESEQUENCEmysequence2使用序列列相关伪列列：nextval伪列用用于从指指定的序序列数值值中取出出下一个个值currval伪列引引用的是是指定序序列的“当前值值”用法举例例：SELECTmysequence1.currvalFROM dual;SELECTmyse