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(columndatatype[DEFAULTexpr];1.3创建表(DDL)SQLCREATETABLEdept12(deptnoNUMBER(2),3dnameVARCHAR2(14),4locVARCHAR2(13));表已创建。创建表验证是否创建成功(查看表结构)SQLDESCRIBEdept1NameNull?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数据类型最多能存储2GBLOBLOB称为“大对象”数据类型，可以存储多达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查询数据字典列出当前用户的表列出当前用户不同的对象类型查询当前用户的表、视图、同义词及序列SQLSELECTtable_name2FROMuser_tables;SQLSELECTDISTINCTobject_type2FROMuser_objects;SQLSELECT*2FROMuser_catalog;1.7用子查询创建表--利用现有的表创建表在CREATETABLE语句的AS子句中使用子查询可以在创建表的同时插入数据所指定列的数量和类型必须和子查询中列的数量和类型相匹配–由列名和默认值定义列CREATETABLEtable[column(,column...)]ASsubquery;1.7使用子查询创建表NameNull?Type-----------------------------------------EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)ANNSALNUMBERHIREDATEDATESQLDESCRIBEdept30SQLCREATETABLEdept302AS3SELECTempno,ename,sal*12ANNSAL,hiredate4FROMemp5WHEREdeptno=30;表已创建。1.8ALTERTABLE语句使用ALTERTABLE语句可以：¯添加或删除一个列¯修改已存在的列¯为新列定义默认值ALTERTABLEtableADD(columndatatype[DEFAULTexpr][,columndatatype]...);ALTERTABLEtableMODIFY(columndatatype[DEFAULTexpr][,columndatatype]...);添加新列DEPT30EMPNOENAMEANNSALHIREDATE------------------------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...“在DEPT30表中添加新列JOB”DEPT30EMPNOENAMEANNSALHIREDATE------------------------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...JOBJOB新列添加新列使用ADD子句添加新列columns.EMPNOENAMEANNSALHIREDATEJOB-----------------------------------------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...6rowsselected.SQLALTERTABLEdept302ADD(jobVARCHAR2(9));表已更改。新列成为表的最后一列修改列可以修改列的数据类型、长度及默认值改变列的默认值仅仅影响后来插入的新记录ALTERTABLEdept30MODIFY(enameVARCHAR2(15));--原来是VARCHAR2(10)表已更改。删除一列(Oracle8i以上版本)从数据表中删除一列至少还必须剩下一列ALTERTABLEdept30DROPCOLUMNename;--一次只允许删除一列表已更改。1.9删除表表中的所有数据和结构都被删除任何与该表相关的视图或同义词依然存在，但是却是不可用的任何悬而未决的事务都将被提交所有的索引被删除.不能回滚该语句只有表的创建者或具有DROPANYTABLE权限的用户才能删除表SQLDROPTABLEdept30;表已删除。1.10修改对象的名称要修改表、视图、序列或同义词的名称，可以使用RENAME语句您必须拥有对象的所有权.SQLRENAMEdept1TOdepartment;表已重命名。RENAMEold_nameTOnew_name;1.11截断表TRUNCATETABLE语句：¯删除表中所有记录¯释放该表的存储空间使用TRUNCATE移除后不能回滚–DELETE语句同样可以删除表中记录，但是它并不释放存储空间。SQLTRUNCATETABLEdepartment;表被截断。1.12给表或列添加注释使用COMMENT语句，可以为表或列添加注释(不超过2000字节)注释可以通过以下字典表查询¯ALL_COL_COMMENTS¯USER_COL_COMMENTS¯ALL_TAB_COMMENTS¯USER_TAB_COMMENTSSQLCOMMENTONTABLEemp2IS'EmployeeInformation';注释已创建。2约束描述主要的约束类型建立表的同时创建约束对已存在的表添加、删除约束禁用、启用约束什么是约束阻止无效数据进入表中约束在表一级实施强制规则约束可以阻止删除和其它表具有相关性的表以下是Oracle中有效的约束类型：¯NOTNULL¯UNIQUEKey¯CHECK¯PRIMARYKEY¯FOREIGNKEY值约束完整性约束约束准则给约束命名或者由Oreacle服务器产生一个形式为SYS_Cn的名称创建约束的方式:¯创建表的同时便创建约束¯表创建以后再创建约束在列或者表一级定义约束在数据字典中可以查询约束命名约束对于一个帐号，每个约束名必须是惟一的命名约束的约定格式:tablename_columnname_constrainttype如：emp_deptno_fk约束缩写PRIMARYKEYpkFOREIGNKEYfkUNIQUEukCHECKck或ccNOTNULLnn图普遍采用的约束的缩写表示定义约束CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][column_constraint],…[table_constraint]);CREATETABLEemp(empnoNUMBER(4),enameVARCHAR2(10),…deptnoNUMBER(7,2)NOTNULL,CONSTRAINTemp_empno_pkPRIMARYKEY(EMPNO));定义约束列约束级表约束级column[CONSTRAINTconstraint_name]constraint_type,column,...[CONSTRAINTconstraint_name]constraint_type(column,...),NOTNULL约束确保该列不允许存放空值EMPEMPNOENAMEJOB...COMMDEPTNO7839KINGPRESIDENT107698BLAKEMANAGER307782CLARKMANAGER107566JONESMANAGER20...NOTNULL约束(该列没有记录为NUILL)没有NOTNULL约束(任何一个记录该列可以为空值)NOTNULL约束NOTNULL约束在