Oracle数据定义语言DDL

数据库开发技术

Oracle数据定义语言（DDL）福建工程学院本章要点创建和管理Oracle表约束Oracle数据库表的各种类型1创建和管理表

描述主要的数据库对象表和列名的命名规则创建表描述可用于定义列的数据类型修改表定义删除、更名、截断表1.1数据库对象对象 描述表 存储数据的基本单元，由行和列组成视图 来自一个或多个表的数据的子集序列 产生主关键字值索引 提高某些查询的性能的数据结构同义词 赋予对象另外的名字1.2命名规则和约定(表和列名)必须以字母开头长度为1–30个字符只能包含A–Z,a–z,0–9,_,$和#对于同一个用户不能使用和其他对象相同的名字不能是Oracle的保留字注：1.名字不区分大小写，例如：EMP、eMP或eMp指的是同一个表。

2.在不同的表中命名相同的实体采用保持一致的原则，例如：部门编号在EMP和DEPT中都使用相同的名字DEPTNO。1.3CREATETABLE语句您必须具备:CREATETABLE的权限一定的存贮空间您需要指定:表名列名、列的数据类型及列的宽度CREATETABLE[schema.]table (column

datatype[DEFAULTexpr];1.3创建表(DDL)

SQL>CREATETABLEdept1

2 (deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13));表已创建。创建表验证是否创建成功(查看表结构)

SQL>DESCRIBEdept1

NameNull?Type--------------------------------------------DEPTNONOTNULLNUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13)1.3创建表:DEFAULT选项指定在插入数据时，列的默认值…hiredateDATEDEFAULTSYSDATE,…

合法的值可以是字面值、表达式或SQL函数非法的值是另一个列的名称或虚拟列默认值的数据类型必须和列的数据类型匹配1.4引用其他用户的表属于其他用户而不属于当前用户的表需使用用户名作为表的前缀1.5Oracle的数据类型以下是Oracle数据类型的类别：数据类型字符数值日期时间RAW/LONGRAWLOB字符符数数据据类类型型CHARVARCHAR2LONG当需需要要固固定定长长度度的的字字符符串串时时，，使使用用CHAR数据据类类型型。。CHAR数据据类类型型存存储储字字母母数数字字值值。。CHAR数据据类类型型的的列列长长度度可可以以是是1到2000个字字节节。。VARCHAR2数据据类类型型支支持持可可变变长长度度字字符符串串VARCHAR2数据据类类型型存存储储字字母母数数字字值值VARCHAR2数据据类类型型的的大大小小在在1至4000个字字节节范范围围内内LONG数据据类类型型存存储储可可变变长长度度字字符符数数据据LONG数据据类类型型最最多多能能存存储储2GB1.5Oracle的数数据据类类型型数值值数数据据类类型型可以以存存储储整整数数、、浮浮点点数数和和实实数数最高高精精度度为为38位数值值数数据据类类型型的的声声明明语语法法：：NUMBER[(p[,s])]P表示示精精度度，，S表示示小小数数点点的的位位数数日期期时时间间数数据据类类型型存存储储日日期期和和时时间间值值，，包包括括年年、、月月、、日日，，小小时时、、分分钟钟、、秒秒主要要的的日日期期时时间间类类型型有有：：DATE-存储储日日期期和和时时间间部部分分，，精精确确到到整整个个的的秒秒TIMESTAMP-存储储日日期期、、时时间间和和时时区区信信息息，，秒秒值值精精确确到到小小数数点点后后6位1.5Oracle的数数据据类类型型RAW数据据类类型型用用于于存存储储二二进进制制数数据据RAW数据据类类型型最最多多能能存存储储2000字节节LONGRAW数据据类类型型用用于于存存储储可可变变长长度度的的二二进进制制数数据据LONGRAW数据据类类型型最最多多能能存存储储2GBLOBLOB称为为““大大对对象象””数数据据类类型型，，可可以以存存储储多多达达4GB的非非结结构构化化信信息息，，例例如如声声音音剪剪辑辑和和视视频频文文件件等等LOB数据据类类型型允允许许对对数数据据进进行行高高效效、、随随机机、、分分段段的的访访问问BLOBCLOBBFILECLOB即CharacterLOB（字字符符LOB），，它它能能够够存存储储大大量量字字符符数数据据BLOB即BinaryLOB（二二进进制制LOB），，可可以以存存储储较较大大的的二二进进制制对对象象，，如如图图形形、、视视频频剪剪辑辑和和声声音音文文件件BFILE即BinaryFile（二二进进制制文文件件）），，它它用用于于将将二二进进制制数数据据存存储储在在数数据据库库外外部部的的操操作作系系统统文文件件中中1.5Oracle的数数据据类类型型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-11.6查询数据字典典列出当前用户户的表列出当前用户户不同的对象象类型查询当前用户户的表、视图图、同义词及及序列SQL>SELECT table_name2 FROM user_tables;SQL>SELECT DISTINCTobject_type2 FROM user_objects;SQL>SELECT *2 FROM user_catalog;1.7用子查询创建建表--利用现有的表表创建表在CREATETABLE语句的AS子句中使用子子查询可以在在创建表的同同时插入数据据所指定列的数数量和类型必必须和子查询询中列的数量量和类型相匹匹配由列名和默认认值定义列CREATETABLEtable[column(,column...)]ASsubquery;1.7使用子查询创创建表NameNull?Type-----------------------------------------EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)ANNSALNUMBERHIREDATEDATESQL>DESCRIBEdept30SQL>CREATETABLEdept302 AS3SELECTempno,ename,sal*12ANNSAL,hiredate4 FROMemp5WHEREdeptno=30;表已创建。1.8ALTERTABLE语句使用ALTERTABLE语句可以：添加或删除一一个列修改已存在的的列为新列定义默默认值ALTERTABLEtableADD(columndatatype[DEFAULTexpr][,columndatatype]...);ALTERTABLEtableMODIFY(columndatatype[DEFAULTexpr][,columndatatype]...);添加新列DEPT30EMPNOENAMEANNSALHIREDATE---------------- --------7698BLAKE34200 01-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...“在DEPT30表中添加新列JOB”DEPT30EMPNOENAMEANNSALHIREDATE---------------- --------7698BLAKE34200 01-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...

JOBJOB新列添加新列使用ADD子句添加新列列columns.EMPNOENAMEANNSALHIREDATEJOB-----------------------------------------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...6rowsselected.SQL>ALTERTABLEdept302ADD(jobVARCHAR2(9));表已更改。新列成为表的的最后一列修改列可以修改列的的数据类型、、长度及默认认值改变列的默认认值仅仅影响响后来插入的的新记录ALTERTABLE dept30MODIFY (enameVARCHAR2(15));--原来是VARCHAR2(10)表已更改。删除一列(Oracle8i以上版本)从数据表中删删除一列至少还必须剩剩下一列ALTERTABLE dept30DROPCOLUMNename;--一次只允许删删除一列表已更改。1.9删除表表中的所有数数据和结构都都被删除任何与该表相相关的视图或或同义词依然然存在，但是是却是不可用用的任何悬而未决决的事务都将将被提交所有的索引被被删除.不能回滚该语语句只有表的创建建者或具有DROPANYTABLE权限的用户才才能删除表SQL>DROPTABLEdept30;表已删除。1.10修改对象的名名称要修改表、视视图、序列或或同义词的名名称，可以使使用RENAME语句您必须拥有对对象的所有权权.SQL>RENAMEdept1TOdepartment;表已重命名。。RENAMEold_nameTOnew_name;1.11截断表TRUNCATETABLE语句：删除表中所有有记录释放该表的存存储空间使用TRUNCATE移除后不能回回滚DELETE语句同样可以删除表中记录，但但是它并不释释放存储空间间。SQL>TRUNCATETABLEdepartment;表被截断。1.12给表或列添加加注释使用COMMENT语句，可以为为表或列添加加注释(不超过过2000字节)注释可可以通通过以以下字字典表表查询询ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTSSQL>COMMENTONTABLEemp2IS'EmployeeInformation';注释已已创建建。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...COMMDEPTNO7839KINGPRESIDENT107698BLAKEMANAGER307782CLARKMANAGER107566JONESMANAGER20...NOTNULL约束(该列没有记录为NUILL)没有NOTNULL约束(任何一个记录该列可以为空值)NOTNULL约束NOTNULL约束在列一一级上上定义义约束束SQL>CREATETABLEemployee(2empnoNUMBER(4),3enameVARCHAR2(10)NOTNULL,4jobVARCHAR2(9),5mgrNUMBER(4),6hiredateDATE,7salNUMBER(7,2),8commNUMBER(7,2),9deptnoNUMBER(7,2)NOTNULL);UNIQUE约束DEPTDEPTNODNAMELOC------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONUNIQUE约束

50 SALES DETROIT

60 BOSTONInsertinto不允许插入

(SALES

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

20 MARKETING DALLAS

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

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

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

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

FOREIGNKEY约束可以定定义在在表或列一一级SQL>CREATETABLEemployee(2empnoNUMBER(4),3enameVARCHAR2(10)NOTNULL,4jobVARCHAR2(9),5mgrNUMBER(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,4jobVARCHAR2(9),5mgrNUMBER(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>ALTERTABLEdept2DROPPRIMARYKEYCASCADE;表已更更改。。禁用约约束在ALTERTABLE语句中中使用用DISABLE子句可可以使使约束束失效效使用CASCADE选项可可以使使与该该约束束是从从属关关系的的约束束同时时失效效SQL>ALTERTABLEdeptment2DISABLECONSTRAINTdept_deptno_pkCASCADE;表已更更改。。启用约约束使用ENABLE子句可可以激激活已已经禁禁用的的约束束如果您您启用用