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>SELECTtable_name2FROMuser_tables;SQL>SELECTDISTINCTobject_type2FROMuser_objects;SQL>SELECT*2FROMuser_catalog;1.7用子查查询创创建表表--利用现现有的的表创创建表表在CREATETABLE语句的的AS子句中中使用用子查查询可可以在在创建建表的的同时时插入入数据据所指定定列的的数量量和类类型必必须和和子查查询中中列的的数量量和类类型相相匹配配由列名名和默默认值值定义义列CREATETABLEtable[column(,column...)]ASsubquery;1.7使用子子查询询创建建表NameNull?Type-----------------------------------------EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)ANNSALNUMBERHIREDATEDATESQL>DESCRIBEdept30SQL>CREATETABLEdept302AS3SELECTempno,ename,sal*12ANNSAL,hiredate4FROMemp5WHEREdeptno=30;表已创创建。。1.8ALTERTABLE语句使用ALTERTABLE语句可以：：添加或删除除一个列修改已存在在的列为新列定义义默认值ALTERTABLEtableADD(columndatatype[DEFAULTexpr][,columndatatype]...);ALTERTABLEtableMODIFY(columndatatype[DEFAULTexpr][,columndatatype]...);添加新列DEPT30EMPNOENAMEANNSAL HIREDATE---------------- --------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...“在DEPT30表中添加新列JOB”DEPT30EMPNOENAMEANNSAL HIREDATE---------------- --------7698BLAKE3420001-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约束确保该列不不允许存放放空值EMPEMPNO ENAMEJOB...COMMDEPTNO7839KING PRESIDENT107698BLAKE MANAGER307782CLARK MANAGER107566JONES MANAGER20...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------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONS BOSTONUNIQUE约束

50 SALES DETROIT

60 BOSTONInsertinto不允许插入

(SALES

已经存在)允许插入UNIQUE约束可以定义义在表或或列一级级SQL>CREATETABLEdept(2deptnoNUMBER(2),3 dnameVARCHAR2(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),3 dnameVARCHAR2(14),4 locVARCHAR2(13),5 CONSTRAINTdept_dname_ukUNIQUE(dname),6 CONSTRAINTdept_deptno_pkPRIMARYKEY(deptno));注:Oracle服务器会自动动为主关键字字列创建一个个唯一索引。。FOREIGNKEY约束DEPTDEPTNODNAMELOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS...PRIMARY

KEYEMPEMPNOENAME JOB...COMMDEPTNO7839KINGPRESIDENT107698BLAKEMANAGER30...FOREIGN

KEY

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

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

FOREIGNKEY约束可以定义在表或列一级SQL>CREATETABLEemployee(2 empnoNUMBER(4),3 ename VARCHAR2(10)NOTNULL,4 jobVARCHAR2(9),5 mgrNUMBER(4),6 hiredateDATE,7 salNUMBER(7,2),8 comm NUMBER(7,2),9 deptno NUMBER(7,2)NOTNULL,10 CONSTRAINTemp_deptno_fkFOREIGNKEY(deptno)11REFERENCESdept(deptno));FOREIGNKEY约束可以定义在表表或列一级SQL>CREATETABLEemployee(2 empnoNUMBER(4),3 ename VARCHAR2(10)NOTNULL,4 jobVARCHAR2(9),5 mgrNUMBER(4),6 hiredateDATE,7 salNUMBER(7,2),8 comm NUMBER(7,2),9 deptno NUMBER(7,2)10constraintemp_deptno_fkreferencesdept(deptno));FOREIGNKEY约束中的关键键字FOREIGNKEY在表或列一级级上定义外键键REFERENCES标识父表及相相应的列ONDELETECASCADE如果删除父表表中的记录则则允许级联删删除子表中相相关连的记录录或用ONDELETESETNULLCHECK约束定义每条记录录都必须满足足的条件表达式不允许许使用:对伪列的引用用，例如：CURRVAL,NEXTVAL,LEVEL及ROWNUM对其它记录其其它值的查询询可以调用SYSDATE,UID,USER及USERENV函数...,deptno NUMBER(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子句可以以激活已已经禁用用的约束束如果您启启用唯一一UNIQUE或主键PRIMARYKEY约束，唯唯一UNIQUE或主键PRIMARYKEY索引便自自动创建建SQL>ALTERTABLEdeptm