4-Oracle数据定义语言(DDL)

数据库开发技术

第4章Oracle数据定义语言（DDL）福建工程学院本章要点点创建和管管理Oracle表约束Oracle数据库表表的各种种类型4.1创建和管管理表描述主要要的数据据库对象象表和列名名的命名名规则创建表描述可用用于定义义列的数数据类型型修改表定定义删除、更更名、截截断表4.1..1数据库对对象对象描描述表 存储储数据的的基本单单元，由由行和列列组成视图来来自一一个或多多个表的的数据的的子集序列产产生主主关键字字值索引提提高某些些查询的的性能的的数据结结构同义词赋赋予予对象另另外的名名字4.1..2命名规则则和约定定(表和列名名)必须以字字母开头头长度为1–30个字符只能包含含A–Z,,a––z,0–9,__,$$和#对于同一一个用户户不能使使用和其其他对象象相同的的名字不能是Oracle的保留字字注：1.名字不区区分大小小写，例如：EMP、eMP或eMp指的是同同一个表表。2.在不同的的表中命命名相同同的实体体采用保保持一致致的原则则，例如如：部门门编号在在EMP和DEPT中都使用用相同的的名字DEPTNO。4.1..3CREATETABLE语句您必须具具备:CREATETABLE的权限一定的存存贮空间间您需要指指定:表名列名、列列的数据类型及列列的宽度度CREATETABLE[[schema.]table(columndatatype[DEFAULTexpr];4.1..3创建表(DDL)

SQL>>CREATETABLEdept12 (deptnoNUMBER((2),,3dnameVARCHAR2(14),,4locVARCHAR2((13)));表已创建建。创建表验证是否否创建成成功(查看表结结构)

SQL>>DESCRIBEdept1NameNull?Type-----------------------------------------------------------DEPTNONOTNULLNUMBER(2))DNAMEVARCHAR2((14))LOCVARCHAR2((13))4.1..3创建表:DEFAULT选项指定在插插入数据据时，列列的默认认值…hiredateDATEDEFAULTSYSDATE,…合法的值值可以是是字面值值、表达达式或SQL函数非法的值值是另一一个列的的名称或或虚拟列列默认值的的数据类类型必须须和列的的数据类类型匹配配4.1..4引用其他他用户的的表属于其他他用户而而不属于于当前用用户的表表需使用用用户名作作为表的的前缀4.1..5Oracle的数据类类型以下是Oracle数据类型型的类别别：数据类型型字符数值日期时间间RAW//LONGRAWLOB字符数据据类型CHARVARCHAR2LONG当需要固固定长度度的字符符串时，，使用CHAR数据类型型。CHAR数据类型型存储字字母数字字值。CHAR数据类型型的列长长度可以以是1到2000个字节。。VARCHAR2数据类型型支持可可变长度度字符串串VARCHAR2数据类型型存储字字母数字字值VARCHAR2数据类型型的大小小在1至4000个字节范范围内LONG数据类型型存储可可变长度度字符数数据LONG数据类型型最多能能存储2GB4.1..5Oracle的数据类类型数值数据据类型可以存储储整数、、浮点数数和实数数最高精度度为38位数值数据据类型的的声明语语法：NUMBER[[(p[,,s]])]P表示精度度，S表示小数数点的位位数日期时间间数据类类型存储储日期和和时间值值，包括括年、月月、日，，小时、、分钟、、秒主要的日日期时间间类型有有：DATE-存储日期期和时间间部分，，精确到到整个的的秒TIMESTAMP--存储日期期、时间间和时区区信息，，秒值精精确到小小数点后后6位4.1..5Oracle的数据类类型RAW数据类型型用于存存储二进进制数据据RAW数据类型型最多能能存储2000字节LONGRAW数据类型型用于存存储可变变长度的的二进制制数据LONGRAW数据类型型最多能能存储2GBLOBLOB称为“大大对象””数据类类型，可可以存储储多达4GB的非结构构化信息息，例如如声音剪剪辑和视视频文件件等LOB数据类型型允许对对数据进进行高效效、随机机、分段段的访问问BLOBCLOBBFILECLOB即CharacterLOB（字符LOB），它能能够存储储大量字字符数据据BLOB即BinaryLOB（二进制制LOB），可以以存储较较大的二二进制对对象，如如图形、、视频剪剪辑和声声音文件件BFILE即BinaryFile（二进制制文件）），它用用于将二二进制数数据存储储在数据据库外部部的操作作系统文文件中4.1..5Oracle的数据类类型4.1..5数据类型型P50类型描描述VARCHAR2(size)变长字符符串(字符族):1~~4000个字符CHAR(size)固定长度度字符串串(字符族))::1~2000个字符NUMBER((p,s)定点小数数值,p为精度,s为标度(数字族)DATE日期和时时间值(日期族)LONG可存放高高达2GB的变长字符符数据(字符族)CLOB字符大对对象可存放高高达4GB的变长单字字节字符数据RAWandLONGRAW原始二进进制数据据BLOB二进制大对象可存放高高达4GB二进制数数据BFILE引用一个个外部的的二进制制文件;可存放高高达4GB数据P50表3-14.1..6查询数据据字典列出当前前用户的的表

列出当前前用户不不同的对对象类型型查询当前前用户的的表、视视图、同同义词及及序列SQL>>SELECTtable_name2 FROMuser_tables;SQL>>SELECTDISTINCTobject__type2 FROMuser_objects;SQL>>SELECT**2 FROMuser_catalog;4.1..7用子查询询创建表表--利用现有有的表创创建表在CREATETABLE语句的AS子句中使使用子查查询可以以在创建建表的同同时插入入数据所指定列列的数量量和类型型必须和和子查询询中列的的数量和和类型相相匹配由列名和和默认值值定义列列CREATETABLEtable[column(,column...))]ASsubquery;4.1..7使用子查查询创建建表NameNull??Type-------------------------------------------------------EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)ANNSALNUMBERHIREDATEDATESQL>>DESCRIBEdept30SQL>>CREATETABLEdept302 AS3SELECTempno,ename,sal*12ANNSAL,hiredate4FROMemp5WHEREdeptno=30;表已创建建。4.1..8ALTERTABLE语句使用ALTERTABLE语句可以以：添加或删删除一个个列修改已存存在的列列为新列定定义默认认值ALTERTABLEtableADD((columndatatype[DEFAULTexpr][,columndatatype]....);ALTERTABLEtableMODIFY((columndatatype[DEFAULTexpr][,columndatatype]....);添加新列列

DEPT30EMPNOENAMEANNSALHIREDATE--------------------------------7698 BLAKE3420001-MAY--817654 MARTIN1500028--SEP-817499 ALLEN1920020-FEB--817844 TURNER1800008--SEP-81...“在DEPT30表中添加新列JOB”

DEPT30EMPNOENAMEANNSALHIREDATE--------------------------------7698 BLAKE3420001-MAY--817654 MARTIN1500028--SEP-817499 ALLEN1920020-FEB--817844 TURNER1800008--SEP-81...

JOB

JOB新列添加新列列使用ADD子句添加加新列columns.EMPNOENAMEANNSALHIREDATEJOB-------------------------------------------------------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...6rowsselected..SQL>>ALTERTABLEdept302ADD((jobVARCHAR2((9)));表已更改改。新列成为为表的最最后一列列修改列可以修改改列的数数据类型型、长度度及默认认值改变列的的默认值值仅仅影影响后来来插入的的新记录录ALTERTABLEdept30MODIFY((enameVARCHAR2(15)));--原来是VARCHAR2(10)表已更改改。删除一列列(Oracle8i以上版本本)从数据表表中删除除一列至少还必必须剩下下一列ALTERTABLEdept30DROPCOLUMNename;--一次只允允许删除除一列表已更改改。4.1..9删除表表中的所所有数据据和结构构都被删删除任何与该该表相关关的视图图或同义义词依然然存在，，但是却却是不可可用的任何悬而而未决的的事务都都将被提提交所有的索索引被删删除.不能回滚滚该语句句只有表的的创建者者或具有有DROPANYTABLE权限的用用户才能能删除表表SQL>>DROPTABLEdept30;表已删除除。4.1..10修改对象象的名称称要修改表表、视图图、序列列或同义义词的名名称，可可以使用用RENAME语句您必须拥拥有对象象的所有有权.SQL>>RENAMEdept1TOdepartment;表已重命命名。RENAMEold__nameTOnew__name;4.1..11截断表TRUNCATETABLE语句：删除表中中所有记记录释放该表表的存储储空间使用TRUNCATE移除后不不能回滚滚DELETE语句同样可以以删除表中记录录，但是是它并不不释放存存储空间间。SQL>>TRUNCATETABLEdepartment;;表被截断断。4.1..12给表或列列添加注注释使用COMMENT语句，可可以为表表或列添添加注释释(不超过2000字节)注释可以以通过以以下字典典表查询询ALL__COL_COMMENTSUSER_COL_COMMENTSALL__TAB_COMMENTSUSER_TAB_COMMENTSSQL>>COMMENTONTABLEemp2IS''EmployeeInformation'';注释已创创建。4.2约束描述主要要的约束束类型建立表的的同时创创建约束束对已存在在的表添添加、删删除约束束禁用、启启用约束束什么是约约束阻止无效效数据进进入表中中约束在表表一级实实施强制规则约束可以以阻止删删除和其其它表具具有相关关性的表表以下是Oracle中有效的的约束类类型：NOTNULLUNIQUEKeyCHECKPRIMARYKEYFOREIGNKEY值约束完整性约约束约束准则则给约束命命名或者者由Oreacle服务器产产生一个个形式为为SYS__Cn的名称创建约束束的方式式:创建表的的同时便便创建约约束表创建以以后再创创建约束束在列或者者表一级级定义约约束在数据字字典中可可以查询询约束命名约束束对于一个个帐号，，每个约约束名必必须是惟惟一的命名约束束的约定定格式:<tablename>_<columnname>_<constrainttype>如：emp__deptno_fk约束缩写PRIMARYKEYpkFOREIGNKEYfkUNIQUEukCHECKck或ccNOTNULLnn图普普遍采采用的约约束的缩缩写表示示定义约束束CREATETABLE[schema.]table (column

datatype[DEFAULTexpr] [column_constraint],

… [table_constraint]);CREATETABLEemp( empnoNUMBER(4), enameVARCHAR2(10),

… deptnoNUMBER(7,2)NOTNULL,

CONSTRAINTemp_empno_pk PRIMARYKEY(EMPNO)); 定义约束束列约束级级表约束级级column[CONSTRAINTconstraint_name]constraint_type,column,,....[CONSTRAINTconstraint_name]constraint_type(column,....),,NOTNULL约束确保该列列不允许许存放空空值

EMPEMPNOENAMEJOB.....COMMDEPTNO7839 KINGPRESIDENT107698 BLAKE MANAGER307782 CLARK MANAGER107566 JONES MANAGER20...NOTNULL约束(该列没有记录为NUILL)没有NOTNULL约束(任何一个记录该列可以为空值)NOTNULL约束NOTNULL约束在列一级级上定义义约束SQL>>CREATETABLEemployee(2empnoNUMBER((4),,3enameVARCHAR2(10)NOTNULL,,4 jobVARCHAR2(9),5 mgrNUMBER((4),,6hiredateDATE,7salNUMBER((7,2),8commNUMBER((7,2),9deptnoNUMBER((7,2)NOTNULL);;UNIQUE约束

DEPTDEPTNODNAMELOC--------------------------------10ACCOUNTING NEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONS BOSTONUNIQUE约束

50 SALES DETROIT

60 BOSTONInsertinto不允许插入

(SALES

已经存在)允许插入UNIQUE约束可以定义义在表或或列一级级SQL>>CREATETABLEdept((2deptnoNUMBER((2),,3dnameVARCHAR2(14),,4 locVARCHAR2(13),5 CONSTRAINTdept_dname_ukUNIQUE((dname));注:Oracle服务器是是通过隐隐含地在在唯一关关键字上上创建唯唯一索引引的方式式实施UNIQUE约束的。。PRIMARYKEY约束

DEPTDEPTNODNAMELOC--------------------------------10ACCOUNTING NEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONS BOSTONPRIMARYKEYInsertinto

20 MARKETING DALLAS

FINANCE NEWYORK不允许(DEPTNO-20已经存在)不允许

(DEPTNO不允许为空)PRIMARYKEY约束可以定义义在表或或列一级级SQL>>CREATETABLEdept((2deptnoNUMBER((2),,3dnameVARCHAR2(14),,4 locVARCHAR2(13),5 CONSTRAINTdept_dname_ukUNIQUE((dname),6 CONSTRAINTdept_deptno_pkPRIMARYKEY((deptno));注:Oracle服务器会会自动为为主关键键字列创创建一个个唯一索索引。FOREIGNKEY约束

DEPTDEPTNODNAMELOC--------------------------------10ACCOUNTING NEWYORK20RESEARCHDALLAS...PRIMARYKEY

EMPEMPNOENAMEJOB.....COMMDEPTNO7839 KINGPRESIDENT107698 BLAKE MANAGER30...FOREIGNKEY

7571 FORD MANAGER ...200 97571 FORD MANAGER ...200Insertinto不允许插入

(DEPTNO-9在DEPT表中不存在)允许

FOREIGNKEY约束可以定义义在表或列一级级SQL>>CREATETABLEemployee(2empnoNUMBER((4),,3enameVARCHAR2(10)NOTNULL,,4 jobVARCHAR2(9),5 mgrNUMBER((4),,6hiredateDATE,7salNUMBER((7,2),8commNUMBER((7,2),9deptnoNUMBER((7,2)NOTNULL,10CONSTRAINTemp__deptno_fkFOREIGNKEY((deptno)11REFERENCESdept((deptno));FOREIGNKEY约束可以定义义在表或或列一级SQL>>CREATETABLEemployee(2empnoNUMBER((4),,3enameVARCHAR2(10)NOTNULL,,4 jobVARCHAR2(9),5 mgrNUMBER((4),,6hiredateDATE,7salNUMBER((7,2),8commNUMBER((7,2),9deptnoNUMBER((7,2)10constraintemp__deptno_fkreferencesdept(deptno));FOREIGNKEY约束中的的关键字字FOREIGNKEY在表或列列一级上上定义外外键REFERENCES标识父表表及相应应的列ONDELETECASCADE如果删除除父表中中的记录录则允许许级联删删除子表表中相关关连的记记录或用ONDELETESETNULLCHECK约束定义每条条记录都都必须满满足的条条件表达式不不允许使使用:对伪列的的引用，，例如：：CURRVAL,NEXTVAL,LEVEL及ROWNUM对其它记记录其它它值的查查询可以调用用SYSDATE,UID,,USER及USERENV函数...,,deptnoNUMBER((2),,CONSTRAINTemp__deptno_ckCHECK((DEPTNOBETWEEN10AND99),....添加约束束添加、删删除但不不是修改改约束启用一个个已经禁禁用的约约束使用MODIFY子句增加加NOTNULL约束ALTERTABLEtableADD[[CONSTRAINTconstraint]type(column);添加约束束为EMP表添加一一个FOREIGNKEY约束，指指定雇员员的管理理员编号号必须是是在EMP表中已经经存在的的雇员的的雇员编编号。SQL>>ALTERTABLEemp2ADDCONSTRAINTemp__mgr_fk3FOREIGNKEY((mgr)REFERENCESemp((empno);表已更改改。删除约束束从EMP表删除管管理员约约束SQL>>ALTERTABLEemp2DROPCONSTRAINTemp__mgr_fk;表已更改改。从DEPT表删除主主键约束束PRIMARYKEY，同时删删除也删删除了与与该约束束关联的的EMP表DEPTNO上的外键键FOREIGNKEY约束。SQL>>ALTERTABLE dept2DROPPRIMARYKEYCASCADE;;表已更改改。禁用约束束在ALTERTABLE语句中使使用DISABLE子句可以以使约束束失效使用CASCADE选项可以以使与该该约束是是从属关关系的约约束同时时失效SQL>>ALTERTABLEdeptment2DISABLECONSTRAINTdept_deptno_pkCASCADE;表已更改改。启用约束束使用ENABLE子句可以以激活已已经禁用用的约束束如果您启启用唯一一UNIQUE或主键PRIMARYKEY约束，唯唯一UNIQUE或主键PRIMARYKEY索引便自自动创建建SQL>>ALTERTABLEdeptment2ENABLECONSTRAINTdept_deptno_pk;表已更改改。查询约束束查询USER_CONSTRAINTS表可以检检索出所所有约束束名称及