4-Oracle数据定义语言.ppt

数据库开发技术第4racle数据定义语言（DDL）,本章要点,创建和管理Oracle表约束Oracle数据库表的各种类型,4.1创建和管理表,描述主要的数据库对象表和列名的命名规则创建表描述可用于定义列的数据类型修改表定义删除、更名、截断表,4.1.1数据库对象,对象描述表存储数据的基本单元，由行和列组成视图来自一个或多个表的数据的子集序列产生主关键字值索引提高某些查询的性能的数据结构同义词赋予对象另外的名字,4.1.2命名规则和约定(表和列名),必须以字母开头长度为130个字符只能包含AZ,az,09,_,$和#对于同一个用户不能使用和其他对象相同的名字不能是Oracle的保留字,注：1.名字不区分大小写，例如：EMP、eMP或eMp指的是同一个表。2.在不同的表中命名相同的实体采用保持一致的原则，例如：部门编号在EMP和DEPT中都使用相同的名字DEPTNO。,4.1.3CREATETABLE语句,您必须具备:CREATETABLE的权限一定的存贮空间您需要指定:表名列名、列的数据类型及列的宽度,CREATETABLEschema.table(columndatatypeDEFAULTexpr;,4.1.3创建表(DDL),SQLCREATETABLEdept12(deptnoNUMBER(2),3dnameVARCHAR2(14),4locVARCHAR2(13);表已创建。,创建表,验证是否创建成功(查看表结构),SQLDESCRIBEdept1,NameNull?Type-DEPTNONOTNULLNUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13),4.1.3创建表:DEFAULT选项,指定在插入数据时，列的默认值,hiredateDATEDEFAULTSYSDATE,合法的值可以是字面值、表达式或SQL函数非法的值是另一个列的名称或虚拟列默认值的数据类型必须和列的数据类型匹配,4.1.4引用其他用户的表,属于其他用户而不属于当前用户的表需使用用户名作为表的前缀,4.1.5Oracle的数据类型,以下是Oracle数据类型的类别：,数据类型,字符,数值,日期时间,RAW/LONGRAW,LOB,字符数据类型,CHAR,VARCHAR2,LONG,当需要固定长度的字符串时，使用CHAR数据类型。CHAR数据类型存储字母数字值。CHAR数据类型的列长度可以是1到2000个字节。,VARCHAR2数据类型支持可变长度字符串VARCHAR2数据类型存储字母数字值VARCHAR2数据类型的大小在1至4000个字节范围内,LONG数据类型存储可变长度字符数据LONG数据类型最多能存储2GB,4.1.5Oracle的数据类型,数值数据类型可以存储整数、浮点数和实数最高精度为38位数值数据类型的声明语法：NUMBER(p,s)P表示精度，S表示小数点的位数,日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒主要的日期时间类型有：DATE-存储日期和时间部分，精确到整个的秒TIMESTAMP-存储日期、时间和时区信息，秒值精确到小数点后6位,4.1.5Oracle的数据类型,RAW数据类型用于存储二进制数据RAW数据类型最多能存储2000字节LONGRAW数据类型用于存储可变长度的二进制数据LONGRAW数据类型最多能存储2GB,LOB,LOB称为“大对象”数据类型，可以存储多达4GB的非结构化信息，例如声音剪辑和视频文件等LOB数据类型允许对数据进行高效、随机、分段的访问,BLOB,CLOB,BFILE,CLOB即CharacterLOB（字符LOB），它能够存储大量字符数据,BLOB即BinaryLOB（二进制LOB），可以存储较大的二进制对象，如图形、视频剪辑和声音文件,BFILE即BinaryFile（二进制文件），它用于将二进制数据存储在数据库外部的操作系统文件中,4.1.5Oracle的数据类型,4.1.5数据类型P50,类型描述VARCHAR2(size)变长字符串(字符族):14000个字符CHAR(size)固定长度字符串(字符族):12000个字符NUMBER(p,s)定点小数值,p为精度,s为标度(数字族)DATE日期和时间值(日期族)LONG可存放高达2GB的变长字符数据(字符族)CLOB字符大对象可存放高达4GB的变长单字节字符数据RAWandLONGRAW原始二进制数据BLOB二进制大对象可存放高达4GB二进制数据BFILE引用一个外部的二进制文件;可存放高达4GB数据P50表3-1,4.1.6查询数据字典,列出当前用户的表,列出当前用户不同的对象类型,查询当前用户的表、视图、同义词及序列,SQLSELECTtable_name2FROMuser_tables;,SQLSELECTDISTINCTobject_type2FROMuser_objects;,SQLSELECT*2FROMuser_catalog;,4.1.7用子查询创建表-利用现有的表创建表,在CREATETABLE语句的AS子句中使用子查询可以在创建表的同时插入数据所指定列的数量和类型必须和子查询中列的数量和类型相匹配由列名和默认值定义列,CREATETABLEtablecolumn(,column.)ASsubquery;,4.1.7使用子查询创建表,NameNull?Type-EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)ANNSALNUMBERHIREDATEDATE,SQLDESCRIBEdept30,SQLCREATETABLEdept302AS3SELECTempno,ename,sal*12ANNSAL,hiredate4FROMemp5WHEREdeptno=30;表已创建。,4.1.8ALTERTABLE语句,使用ALTERTABLE语句可以：添加或删除一个列修改已存在的列为新列定义默认值,ALTERTABLEtableADD(columndatatypeDEFAULTexpr,columndatatype.);,ALTERTABLEtableMODIFY(columndatatypeDEFAULTexpr,columndatatype.);,添加新列,DEPT30,EMPNOENAMEANNSALHIREDATE-7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81.,DEPT30,EMPNOENAMEANNSALHIREDATE-7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81.,JOB,新列,添加新列,使用ADD子句添加新列columns.,EMPNOENAMEANNSALHIREDATEJOB-7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81.6rowsselected.,SQLALTERTABLEdept302ADD(jobVARCHAR2(9);表已更改。,新列成为表的最后一列,修改列,可以修改列的数据类型、长度及默认值改变列的默认值仅仅影响后来插入的新记录,ALTERTABLEdept30MODIFY(enameVARCHAR2(15);-原来是VARCHAR2(10)表已更改。,删除一列(Oracle8i以上版本),从数据表中删除一列至少还必须剩下一列,ALTERTABLEdept30DROPCOLUMNename;-一次只允许删除一列表已更改。,4.1.9删除表,表中的所有数据和结构都被删除任何与该表相关的视图或同义词依然存在，但是却是不可用的任何悬而未决的事务都将被提交所有的索引被删除.不能回滚该语句只有表的创建者或具有DROPANYTABLE权限的用户才能删除表,SQLDROPTABLEdept30;表已删除。,4.1.10修改对象的名称,要修改表、视图、序列或同义词的名称，可以使用RENAME语句您必须拥有对象的所有权.,SQLRENAMEdept1TOdepartment;表已重命名。,RENAMEold_nameTOnew_name;,4.1.11截断表,TRUNCATETABLE语句：删除表中所有记录释放该表的存储空间使用TRUNCATE移除后不能回滚DELETE语句同样可以删除表中记录，但是它并不释放存储空间。,SQLTRUNCATETABLEdepartment;表被截断。,4.1.12给表或列添加注释,使用COMMENT语句，可以为表或列添加注释(不超过2000字节)注释可以通过以下字典表查询ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTS,SQLCOMMENTONTABLEemp2ISEmployeeInformation;注释已创建。,4.2约束,描述主要的约束类型建立表的同时创建约束对已存在的表添加、删除约束禁用、启用约束,什么是约束,阻止无效数据进入表中约束在表一级实施强制规则约束可以阻止删除和其它表具有相关性的表以下是Oracle中有效的约束类型：NOTNULLUNIQUEKeyCHECKPRIMARYKEYFOREIGNKEY,值约束,完整性约束,约束准则,给约束命名或者由Oreacle服务器产生一个形式为SYS_Cn的名称创建约束的方式:创建表的同时便创建约束表创建以后再创建约束在列或者表一级定义约束在数据字典中可以查询约束,命名约束,对于一个帐号，每个约束名必须是惟一的命名约束的约定格式:_如：emp_deptno_fk,图普遍采用的约束的缩写表示,定义约束,定义约束,列约束级表约束级,columnCONSTRAINTconstraint_nameconstraint_type,column,.CONSTRAINTconstraint_nameconstraint_type(column,.),NOTNULL约束,确保该列不允许存放空值,EMP,EMPNOENAMEJOB.COMMDEPTNO7839KINGPRESIDENT107698BLAKEMANAGER307782CLARKMANAGER107566JONESMANAGER20.,NOTNULL约束,在列一级上定义约束,SQLCREATETABLEemployee(2empnoNUMBER(4),3enameVARCHAR2(10)NOTNULL,4jobVARCHAR2(9),5mgrNUMBER(4),6hiredateDATE,7salNUMBER(7,2),8commNUMBER(7,2),9deptnoNUMBER(7,2)NOTNULL);,UNIQUE约束,DEPT,DEPTNODNAMELOC-10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON,UNIQUE约束,UNIQUE约束,可以定义在表或列一级,SQLCREATETABLEdept(2deptnoNUMBER(2),3dnameVARCHAR2(14),4locVARCHAR2(13),5CONSTRAINTdept_dname_ukUNIQUE(dname);,注:Oracle服务器是通过隐含地在唯一关键字上创建唯一索引的方式实施UNIQUE约束的。,PRIMARYKEY约束,DEPT,DEPTNODNAMELOC-10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON,PRIMARYKEY,PRIMARYKEY约束,可以定义在表或列一级,SQLCREATETABLEdept(2deptnoNUMBER(2),3dnameVARCHAR2(14),4locVARCHAR2(13),5CONSTRAINTdept_dname_ukUNIQUE(dname),6CONSTRAINTdept_deptno_pkPRIMARYKEY(deptno);,注:Oracle服务器会自动为主关键字列创建一个唯一索引。,FOREIGNKEY约束,DEPT,DEPTNODNAMELOC-10ACCOUNTINGNEWYORK20RESEARCHDALLAS.,PRIMARYKEY,EMP,EMPNOENAMEJOB.COMMDEPTNO7839KINGPRESIDENT107698BLAKEMANAGER30.,FOREIGNKEY,FOREIGNKEY约束,可以定义在表或列一级,SQLCREATETABLEemployee(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约束,可以定义在表或列一级,SQLCREATETABLEemployee(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如果删除父表中的记录则允许级联删除子表中相关连的记录或用ONDELETESETNULL,CHECK约束,定义每条记录都必须满足的条件表达式不允许使用:对伪列的引用，例如：CURRVAL,NEXTVAL,LEVEL及ROWNUM对其它记录其它值的查询可以调用SYSDATE,UID,USER及USERENV函数,.,deptnoNUMBER(2),CONSTRAINTemp_deptno_ckCHECK(DEPTNOBETWEEN10AND99),.,添加约束,添加、删除但不是修改约束启用一个已经禁用的约束使用MODIFY子句增加NOTNULL约束,ALTERTABLEtableADDCONSTRAINTconstrainttype(column);,添加约束,为EMP表添加一个FOREIGNKEY约束，指定雇员的管理员编号必须是在EMP表中已经存在的雇员的雇员编号。,SQLALTERTABLEemp2ADDCONSTRAINTemp_mgr_fk3FOREIGNKEY(mgr)REFERENCESemp(empno);表已更改。,删除约束,从EMP表删除管理员约束,SQLALTERTABLEemp2DROPCONSTRAINTemp_mgr_fk;表已更改。,从DEPT表删除主键约束PRIMARYKEY，同时删除也删除了与该约束关联的EMP表DEPTNO上的外键FOREIGNKEY约束。,SQLALTERTABLEdept2DROPPRIMARYKEYCASCADE;表已更改。,禁用约束,在ALTERTABLE语句中使用DISABLE子句可以使约束失效使用CASCADE选项可以使与该约束是从属关系的约束同时失效,SQLALTERTABLEdeptment2DISABLECONSTRAINTdept_deptno_pkCASCADE;表已更改。,启用约束,使用ENABLE子句可以激活已经禁用的约束如果您启用唯一UNIQUE或主键PRIMARYKEY约束，唯一UNIQUE或主键PRIMARYKEY索引便自动创建,SQLALTERTABLEdeptment2ENABLECONSTRAINTdept_deptno_pk;表已更改。,查询约束,查询USER_CONSTRAINTS表可以检索出所有约束名称及定义,CONSTRA