三、oracle数据定义ddl和dml

ORACLE数据定义DDL和数据操纵DML本章重点•ORACLE数据类型•表创建/变动/删除•表分区的使用•同义词对象创建/删除•数据操纵语言DML•ORACLE中常见函数ORACLE主要的数据类型•Character数据类型•Number数据类型•Date数据类型•RAW和LongRaw数据类型•LOB数据类型Character数据类型char类型固定大小，范围为1~2000varchar2类型可变长字符串,范围为1~4000long类型可变长的字符数据，最大可达2GB；特殊要求如下：1）表中只有一列为long类型2）long列不能定义为主键或者唯一约束3）不能在该列上建立索引4）过程或者存储过程不能使用该类型作为参数Number数据类型存储精度为38的浮点数、正数、负数等等使用格式:1)number2)number(p)3)number(p,s)说明：p是精度即总位数,s为右边的小数位数Date数据类型Raw和LongRaw数据类型Raw数据类型存储面向字节的数据，如：二进制数和字符串,最大范围为2000个字节，使用时要指定大小LongRaw数据类型存储二进制数据，最大为2GB，跟long类型的限制一样LOB数据类型CLOB存储字符串Charactor的LOB类型BLOB存储二进制数据Binary的LOB类型，一般来存储大型的二进制对象BFILE存储二进制的文件存储非结构化的数据，最大的范围是4GB表的创建CREATETABLE[schema.]table({columndatatype[DEFAULTexpr][column_constraint]...|table_constraint})[[PCTFREEinteger][PCTUSEDinteger][INITRANSinteger][MAXTRANSinteger][TABLESPACEtablespace][STORAGEstorage_clause][CACHE|NOCACHE]|[CLUSTERcluster(column[,column]...)]][ENABLEenable_clause|DISABLEdisable_clause]...[ASsubquery]说明：1、TABLESPACE指定了该表使用的表空间2、ASsubquery表示使用子查询创建表，只需要指定列名和列上的约束，不需要指定数据类型（或者列名也不指定）3、Enable|Disable表示是否使用完整性约束4、如果需要指定schema，即为其他用户创建表，则需要有CreateanyTable的系统权限示例-表创建(1)createtabletab_student(snochar(11)primarykey,snamevarchar2(30)notnull,sbirthdate,sageintdefault0,ssexchar(2)notnull,remarkvarchar2(60),constraintck_tab_student_sagecheck(ssexin('男','女')))tablespacets_stock说明：1、表创建时，就跟约束一起创建，该例中使用到的约束有：primarykey,notnull,default,check(未出现的还有foreignkey，unique等)2、思考建表后是如何建立约束呢？示例-表创建(2)1、建表和外键约束createtabletab_score(snochar(11)primarykey,scnochar(3)notnull,scorenumber(8,3)default0,constraintfk_tab_score_tab_student_snoforeignkey(sno)referencestab_student(sno))2、用as子句建表createtabletab_dup(aa,bb)asselectsno,snamefromtab_student示例-表变动1、增加/改动/删除列altertabletab_studentadd(col1varchar2(10)notnull,col2int);altertabletab_studentmodify(ssexchar(1)notnull,remarkvarchar2(100))altertabletab_studentdrop(remark)2、启用/禁止/删除表约束altertabletab_studentdisableconstraintck_tab_student_sagealtertabletab_studentdisableprimarykey;altertabletab_studentenableprimarykey;altertabletab_studentdropconstraintck_tab_student_sage;示例-表删除Droptabletab_studentcascadeconstraints说明：当表中建立了完整性约束，则需要使用cascadeconstraints选项，表示在删除表时，将表中的所有约束一起删除；表改名：renametab_studenttonew_student表/列注释COMMENTON{TABLE[schema.]{table|view|snapshot}|COLUMN[schema.]{table|view|snapshot}.column}IS'text'示例如下：commentontabletab_studentis‘学生表’;commentoncolumntab_student.snois‘学号’表分区1•分区的概念是指可以将表拆分为几个部分•在数据仓库应用程序中非常有用•根据键值范围对表进行拆分•表的每个部分称为“分区”•分区被独立存储和管理•通过引用逻辑表名称进行查询和更新表分区2•分区方法―分区类型―范围分区―散列分区―复合分区―列表分区表分区3•范围分区―根据列值的范围将行映射到分区―由表的分区规范进行定义：―PARTITIONBYRANGE(column_list)―由每个单独分区的分区规范进行定义：―VALUESLESSTHAN(value_list)―其中column_list是列的有序列表―value_list是column_list中列值的有序列表表分区4范围分区的语法：createtabletable_name(column_namedatatype,column_namedatatype,….)partitionbyrange(column_name)(partitionpartitionnamevalueslessthanvalue,partitionpartitionnamevalueslessthanvalue);Createtableemp1(Empnonumber(4),Enamevarchar2(10),Jobvarchar2(9),Mgrnumber(4),Hibernatedate,Salnumber(7,2),Commnumber(7,2),Deptonumber(2))Partitionbyrange(empno)(partitionp1valueslessthan(250),Partitionp2valueslessthan(500));表分区5•散列分区―在分区列上使用hash函数―是比范围分区更好的选择―如果不知道将有多少数据映射到指定的范围，散列分区非常有用―分区的数目应是2的幂―可以对散列分区进行命名，并将其存储在特定的表空间中表分区6散列分区的语法：•CREATETABLEtable_name(...)•PARTITIONBYHASH(column_list)•PARTITIONSNOOFPARTITIONS•(PARTITIONpartition_name•TABLESPACEtablespace_name,•PARTITIONpartition_name•TABLESPACEtablespace_name);例子•Createtabledept1(•Deptnonumber2(2),•Dnamevarchar2(14),•LOCVARCHAR(13))•Partitionbyhash(deptno)•(partitionp1,partitionp2);表分区7•复合分区―使用范围分区方法对数据进行分区，并在每一个分区内使用散列分区方法将其划分为子分区―允许命名子分区―允许将子分区存储在特定的表空间―兼具范围分区和散列分区的优点表分区8复合分区的语法：CREATETABLEtable_name(……)PARTITIONBYRANGE(column_list)SUBPARTITIONBYHASH(column_list)SUBPARTITIONSnumber_of_subpartitionsSTOREIN(tablespace_name_list,,,……..)(PARTITIONparition_nameVALUESLESSTHAN(VALUE)(SUBPARTITIONpartition_nameTABLESPACEtablespace_name,•...•SUBPARTITIONpartition_nameTABLESPACEtablespace_name,•SUBPARTITIONpartition_nameTABLESPACEtablespace_name)•PARTITIONpartition_nameVALUESLESSTHAN(VALUE)•…•………);复合分区举例•Createtablesalgrade1(gradenumber,losalnumber,hisalnumber)•Partitionbyrange(losal,hisal)•(partitionp1valueslessthan(10)•(subpartitionsp1,subpartitionsp2).•Partitionp2valueslessthan(20)•(subpartitionsp3,subparationsp4));表分区9•列表分区―基于PARTITIONBYLIST中指定的分区键对表进行分区―明确地控制行到分区的映射―指定分区键的离散值―不同于范围分区和散列分区•Createtablesales_details•(sales_idnumber(6),sman_namevarchar(20),sales_statevarchar2(15),sales_datedate,amountnumber(10))•Partitionbylist(sman_name)•(partitionsales_eastvalues(‘antony’,’henry’,jack’),•Partitionsales_westvalues(‘peter’,’serena’,’venus’))表分区10•将记录插入已分区表―记录被存储到表的分区中―基于指定的分区键执行―与定义的分区边界进行比较―示例：―SQLINSERTINTOord_mastVALUES('o001','12-jan-96','V001','p','12-jan-97');―SQLINSERTINTOord_mastVALUES('o010','12-feb-99','V002','c','15-mar-99');―SQLINSERTINTOord_mastVALUES('o012','12-feb-99','V002','c','15-mar-99');表分区11•分区维护操作―修改已分区表的一个分区―划分为两类事件：―计划事件-定期删除最旧分区的操作―非计划事件–解决应用程序或系统问题单独查询分区•Select*fromord_mastpartition(om2);•数字字