三、oracle数据定义ddl和dml

1、ORACLE数据定义DDL和数据操纵DML本章重点点ORACLE数数据类型型表创建/变动/删除表分区的的使用同义词对对象创建建/删除除数据操纵纵语言DMLORACLE中中常见函函数ORACLE主主要的数数据类型型Character数数据类型型Number数数据类型型Date数据类类型RAW和和LongRaw数据据类型LOB数数据类型型Character数数据类型型char类型固定大小小，范围围为12000varchar2类型型可变长字字符串,范围为为14000long类型可变长的的字符数数据，最最大可达达2GB；特殊殊要求如如下：1）表表中只有有一列为为long类型型2）long列列不能定定义

2、为主主键或者者唯一约约束3）不能能在该列列上建立立索引4）过程程或者存存储过程程不能使使用该类类型作为为参数Number数数据类型型存储精度度为38的浮点点数、正正数、负负数等等等使用格式式:1)number2)number(p)3)number(p,s)说明：p是精度度即总位位数,s为右边边的小数数位数Date数据类类型Raw和和LongRaw数据据类型Raw数数据类型型存储面向向字节的的数据，如：二二进制数数和字符符串,最最大范围围为2000个字字节，使使用时要要指定大大小LongRaw数据类类型存储二进进制数据据，最大大为2GB，跟跟long类型型的限制制一样LOB数数据类型型CLOB存

3、储字符符串Charactor的LOB类类型BLOB存储二进进制数据据Binary的LOB类型型，一般般来存储储大型的的二进制对对象BFILE存储二进进制的文文件存储非结结构化的的数据，最大的的范围是是4GB表的创建建CREATETABLEschema.table( column datatype DEFAULT expr column_constraint.| table_constraint) PCTFREEintegerPCTUSEDintegerINITRANSintegerMAXTRANS integerTABLESPACEtablespaceSTORAGEstorage_clause

4、CACHE| NOCACHE| CLUSTER cluster(column,column.) ENABLEenable_clause| DISABLEdisable_clause.ASsubquery说明：1、TABLESPACE指指定了该该表使用用的表空空间2、AS subquery表示使使用子查查询创建建表，只只需要指指定列名名和列上上的约束束，不需要指指定数据据类型（或者列列名也不不指定）3、Enable|Disable表示是是否使用用完整性性约束4、如果果需要指指定schema，即即为其他他用户创创建表，则需要要有CreateanyTable的系系统权限限示例-表表创建(1)crea

5、tetabletab_student(snochar(11)primary key,sname varchar2(30)notnull,sbirthdate,sageintdefault0,ssexchar(2) notnull,remark varchar2(60),constraintck_tab_student_sagecheck(ssex in (男,女)tablespace ts_stock说明：1、表创创建时，就跟约约束一起起创建，该例中中使用到到的约束束有：primarykey,not null,default,check(未未出现的的还有foreign key，unique等等

6、)2、思考考建表后后是如何何建立约约束呢？示例-表表创建(2)1、建表表和外键键约束createtabletab_score(snochar(11)primarykey,scno char(3)not null,scorenumber(8,3)default 0,constraint fk_tab_score_tab_student_snoforeignkey(sno)referencestab_student(sno)2、用as子句句建表createtabletab_dup(aa,bb)asselectsno,sname fromtab_student示例-表表变动1、增加加/改动动/删除除

7、列altertabletab_studentadd(col1varchar2(10)not null,col2int);altertabletab_studentmodify(ssexchar(1) notnull,remarkvarchar2(100)altertabletab_studentdrop(remark)2、启用用/禁止止/删除除表约束束altertabletab_studentdisable constraintck_tab_student_sagealtertabletab_studentdisable primarykey;altertabletab_studentenab

8、leprimarykey;altertabletab_studentdrop constraintck_tab_student_sage;示例-表表删除Drop table tab_student cascadeconstraints说明：当表中建建立了完完整性约约束，则则需要使使用cascadeconstraints选选项，表表示在删删除表时时，将表表中的所所有约束束一起删删除；表改名：rename tab_student to new_student表/列注注释COMMENT ON TABLE schema.table| view|snapshot| COLUMNschema.table|

9、view |snapshot.columnIStext示例如下下：comment on table tab_student is 学学生表;comment on columntab_student.snois学号号表分区1分区的概概念是指指可以将将表拆分分为几个个部分在数据仓仓库应用用程序中中非常有有用根据键值值范围对对表进行行拆分表的每个个部分称称为“分分区”分区被独独立存储储和管理理通过引用用逻辑表表名称进进行查询询和更新新表分区2分区方法法分区类型型范围分区区散列分区区复合分区区列表分区区表分区3范围分区区根据列值值的范围围将行映映射到分分区由表的分分区规范范进行定定义：PARTITIO

10、NBYRANGE(column_list)由每个单单独分区区的分区区规范进进行定义义：VALUESLESSTHAN (value_list)其中column_list是是列的有有序列表表value_list 是column_list中中列值值的有序序列表表分区4范围分区区的语法法：createtable(column_namedatatype,column_namedata type,.)partitionbyrange(column_name)(partition values lessthan,partitionvalueslessthan);Createtableemp1(Empnonu

11、mber(4),Enamevarchar2(10),Jobvarchar2(9),Mgrnumber(4),Hibernatedate,Salnumber(7,2),Comm number(7,2),Deptonumber(2)Partitionbyrange(empno)(partition p1 valuesless than(250),Partitionp2values lessthan(500);表分区5散列分区区在分区列列上使用用 hash函函数是比范围围分区更更好的选选择如果不知知道将有有多少数数据映射射到指定定的范围围，散列列分区非非常有用用分区的数数目应是是 2的的幂可以对散散

12、列分区区进行命命名，并并将其存存储在特特定的表表空间中中表分区6散列分区区的语法法：CREATETABLE(.)PARTITIONBYHASH(column_list)PARTITIONS( PARTITIONTABLESPACE ,PARTITIONTABLESPACEtablespace_name);例子Createtabledept1(Deptnonumber2(2),Dnamevarchar2(14),LOCVARCHAR(13)Partitionbyhash(deptno)(partition p1,partitionp2);表分区7复合分区区使用范围围分区方方法对数数据进行行分区，

13、并在每每一个分分区内使使用散列列分区方方法将其其划分为为子分区区允许命名名子分区区允许将子子分区存存储在特特定的表表空间兼具范围围分区和和散列分分区的优优点表分区8复合分区区的语法法：CREATETABLE()PARTITIONBYRANGE(column_list)SUBPARTITIONBYHASH(column_list)SUBPARTITIONSSTOREIN(,.)(PARTITIONVALUES LESSTHAN()( SUBPARTITION TABLESPACE,.SUBPARTITIONTABLESPACE ,SUBPARTITIONTABLESPACE )PARTITION

14、VALUES LESSTHAN();复合分区区举例Createtablesalgrade1(grade number,losalnumber,hisalnumber)Partitionbyrange(losal,hisal)(partition p1 valuesless than(10)(subpartition sp1,subpartitionsp2).Partitionp2values lessthan(20)(subpartition sp3,subparationsp4);表分区9列表分区区基于PARTITIONBYLIST中中指定的的分区键键对表进进行分区区明确地控控制行到到分区的

15、的映射指定分区区键的离离散值不同于范范围分区区和散列列分区Createtablesales_details(sales_idnumber(6),sman_namevarchar(20),sales_statevarchar2(15),sales_datedate,amountnumber(10)Partitionbylist(sman_name)(partition sales_eastvalues(antony,henry,jack),Partitionsales_westvalues(peter,serena,venus)表分区10将记录插插入已分分区表记录被存存储到表表的分区区中基于指定

16、定的分区区键执行行与定义的的分区边边界进行行比较示例：SQL INSERTINTOord_mastVALUES(o001,12-jan-96,V001,p,12-jan-97);SQL INSERTINTOord_mastVALUES(o010,12-feb-99,V002,c,15-mar-99);SQL INSERTINTOord_mastVALUES(o012,12-feb-99,V002,c,15-mar-99);表分区11分区维护护操作修改已分分区表的的一个分分区划分为两两类事件件：计划事件件-定期删除除最旧分分区的操操作非计划事事件解决应用用程序或或系统问问题单独查询询分区Sele

17、ct*from ord_mast partition(om2);数字字典典partition表分区12维护分区区各种分区区维护操操作如下下：添加分区区移动删除结合截断拆分合并交换表分分区增添分区区：Alter Table ordersAddpartition orders200403Aalues less(to_date(2004-04-0100:00:00，yyyy-mm-dd hh24: mi:ss)Tablespace Tsp_Orders;删删除除分区：AlterTabletable_nameDrop Partitionpartition_name;裁裁断分分区:AlterTablet

18、able_name Truncate Partitionpartition_nameStorage;表分区指指南a、表的的大小对对于大大表进行行分区，将有益益于大表表操作的的性能和和大表的的数据维维护。通通常当表表的大小小超过1.5GB2GB，或对于于OLTP系统统，表的的记录超超过1000万万，都应应考虑对对表进行行分区。b、数据访访问特性性基于于表的大大部分查查询应用用，只访访问表中中少量的的数据。对于这这样表进进行分区区，可充充分利用用分区排排除无关关数据查查询的特特性。c、数数据维护护某些些表的数数据维护护，经常常按时间间段删除除成批的的数据，例如按按月删除除历史数数据。对对于这样样的

19、表需需要考虑虑进行分分区，以以满足维维护的需需要。因因为删除除（Delete）大大量的数数据，对对系统开开销很大大，有时时甚至是是不可接接受的。d、只读数数据如如果一个个表中大大部分数数据都是是只读数数据，通通过对表表进行分分区，可可将只读读数据存存储在只只读表空空间中，对于数数据库的的备份是是非常有有益的。e、并行数数据操作作（Parallel DML）对对于经经常执行行并行操操作（如如ParallelInsert,Parallel Update等）的表应应考虑进进行分区区。f、表的的可用性性当对对表的部部分数据据可用性性要求很很高时，应考虑虑进行表表分区。同义词synonym为已有模模式对

20、象象(包括括表、视视图、存存储过程程等)起起一个别别名，以以方便访访问语法格式式：CREATEPUBLICSYNONYMschma.synonymFORschema.objectdblink说明：PUBLIC表表示共有有同义词词，所有有的用户户都可以以访问该该同义词词，如果果没有public，则为私私有；一一般同义义词都创创建为共共有的，则需要要create publicsynonym的系统统权限。删除同义义词语法格式式：DROP PUBLIC SYNONYMschma.synonym说明：只要是public同同义词，则删除除时要加加上public关键键字序列Sequence为所有的的用户生生

21、成统一一的序列列号，也也可以给给某些主主键列自自动生成成编号值值语法格式式：CREATESEQUENCEschema.sequenceINCREMENT BY integerSTARTWITHintegerMAXVALUEinteger| NOMAXVALUEMINVALUEinteger| NOMINVALUECYCLE|NOCYCLECACHEinteger| NOCACHEORDER|NOORDER示例-序序列创建建和使用用1、创建建对象createsequenceseq_snoincrementby1startwith 1maxvalue99999nocycle;2、生成成同义词词cr

22、eatepublic synonymsyn_seq_snofor system.seq_sno3、取序序列对象象中的当当前值selectsyn_seq_sno.currvalfrom dual;4、取序序列对象象中的下下一个值值selectsyn_seq_sno.nextvalfrom dual;-此时时对象中中当前序序列值自自动增长长了数据操纵纵DMLSQL*PLUS常用用的命令令DESC命令显示表或或者视图图结构的的命令save和get命令令将sqlplus中缓缓冲区的的内容保保存到文文件，或者将文文件的内内容装载载到缓冲冲区并显显示start或或 sql文文件执行一个个脚本文文件spoo

23、l文文件/spooloff从spool文文件名名开始，一直到到spooloff命令期期间，缓缓冲区内内所有的的内容都都保存到到文件课堂演示示各个命命令的使使用数据查询询命令单表查询询多表联表表查询分组聚合合查询嵌套子查查询查询集合合操作单表查询询1、空表表dualselect*from dual;2、连接接符号|selectsname|ttfromtab_student3、列或或者表的的别名selectsnamenmfromtab_student或select sname as nm fromtab_student4、“虚”列列rownum，rowid使用用rownum是是一个虚虚列，在在表中

24、并并不存在在，oracle为每每个查询询后的结结果集的的记录分分别编号号，该编编号就是是rownum1)限限制返回回的结果果集只能能是10行select*from tab_student where rownum10-该条条件返回回false多表查询询查询结果果集合操操作交集INTERSECT并集UNION/UNION ALL差集MINUSORACLE中中常见系系统函数数字符串函函数算术函数数日期函数数其他函数数字符串函函数函数返回值ASCII(char)字符串首字符的ASC码值CHAR(n)ASC码值为n的字符INITCAP(char)将字符串中每个单词的首字母大写INSTR(char1,char2,m,n)给出字符串char1中从起始位置m开始第n次出现子串char2的位置LENGTH(char)计算字符串的长度LOWER(char)、UPPER(char)将字符串中所有的大写字母变为小(大)写LPAD(char1,n,char2)从左侧用字符串char2补齐字符串char1至长度nLTRIM(char,SET)把char中最左侧的若干个字符去掉，以使其首字符不在SET中RPAD(char1,n,char2)从右侧用char2补齐char1至长度n.RTRIM(char,S