三oracle数据定义ddl和dml

ORACLE数据定义DDL

和数据操纵DML本章重点ORACLE数据类型表创建/变动/删除表分区的使用同义词对象创建/删除数据操纵语言DMLORACLE中常见函数ORACLE主要的数据类型Character数据类型Number数据类型Date数据类型RAW和LongRaw数据类型LOB数据类型Character数据类型char类型固定大小，范围为1~2000varchar2类型可变长字符串,范围为1~4000long类型可变长的字符数据，最大可达2GB；特殊要求如下：1）表中只有一列为long类型2）long列不能定义为主键或者唯一约束3）不能在该列上建立索引4）过程或者存储过程不能使用该类型作为参数Number数据类型存储精度为38的浮点数、正数、负数等等使用格式:1)number 2)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范围分区区的语法法：createtable<table_name>(column_namedatatype,column_namedatatype,….)partitionbyrange(column_name)(partition<partitionname>valueslessthan<value>,partition<partitionname>valueslessthan<value>);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散列分区区的语法法：CREATETABLE<table_name>(...)PARTITIONBYHASH(column_list)PARTITIONS<NOOFPARTITIONS>(PARTITION<partition_name>TABLESPACE<tablespace_name>,PARTITION<partition_name>TABLESPACEtablespace_name>);例子Createtabledept1(Deptnonumber2(2),Dnamevarchar2(14),LOCVARCHAR(13))Partitionbyhash(deptno)(partitionp1,partitionp2);表分区7复合分区区使用范围围分区方方法对数数据进行行分区，，并在每每一个分分区内使使用散列列分区方方法将其其划分为为子分区区允许命名名子分区区允许将子子分区存存储在特特定的表表空间兼具范围围分区和和散列分分区的优优点表分区8复合分区区的语法法：CREATETABLE<table_name>(………)PARTITIONBYRANGE(column_list)SUBPARTITIONBYHASH(column_list)SUBPARTITIONS<number_of_subpartitions>STOREIN(<tablespace_name_list>,<>,<>,………..)(PARTITION<parition_name>VALUESLESSTHAN(<VALUE>)(SUBPARTITION<partition_name>TABLESPACE<tablespace_name>,...SUBPARTITION<partition_name>TABLESPACE<tablespace_name>,SUBPARTITION<partition_name>TABLESPACE<tablespace_name>)PARTITION<partition_name>VALUESLESSTHAN(<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将记录插插入已分分区表记录被存存储到表表的分区区中基于指定定的分区区键执行行与定义的的分区边边界进行行比较示例：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分区维护护操作修改已分分区表的的一个分分区划分为两两类事件件：计划事件件-定定期删删除最旧旧分区的的操作非计划事事件––解决决应用程程序或系系统问题题单独查询询分区Select*fromord_mastpartition(om2);数字字典典partition表分区12维护分区区各种分区区维护操操作如下下：添加分区区移动删除结合截断拆分合并交换表分分区增添分区区：AlterTableordersAddpartitionorders200403Aaluesless(to_date(’2004-04-0100:00:00’’，’yyyy-mm-ddhh24:mi:ss’))TablespaceTsp_Orders;删删除除分区：：AlterTabletable_nameDropPartitionpartition_name;裁裁断分分区:AlterTabletable_nameTruncatePartitionpartition_nameStorage;表分区指指南a、表的的大小对对于大大表进行行分区，，将有益益于大表表操作的的性能和和大表的的数据维维护。通通常当表表的大小小超过1.5GB－2GB，，或对于于OLTP系统统，表的的记录超超过1000万万，都应应考虑对对表进行行分区。。

b、、数据访访问特性性

基于于表的大大部分查查询应用用，只访访问表中中少量的的数据。。对于这这样表进进行分区区，可充充分利用用分区排排除无关关数据查查询的特特性。c、数数据维护护

某些些表的数数据维护护，经常常按时间间段删除除成批的的数据，，例如按按月删除除历史数数据。对对于这样样的表需需要考虑虑进行分分区，以以满足维维护的需需要。因因为删除除（Delete）大大量的数数据，对对系统开开销很大大，有时时甚至是是不可接接受的。。

d、、只读数数据如如果一个个表中大大部分数数据都是是只读数数据，通通过对表表进行分分区，可可将只读读数据存存储在只只读表空空间中，，对于数数据库的的备份是是非常有有益的。。

e、、并行数数据操作作（ParallelDML）对对于经经常执行行并行操操作（如如ParallelInsert,ParallelUpdate等））的表应应考虑进进行分区区。f、表的的可用性性

当对对表的部部分数据据可用性性要求很很高时，，应考虑虑进行表表分区。。同义词synonym为已有模模式对象象(包括括表、视视图、存存储过程程等)起起一个别别名，以以方便访访问语法格式式：CREATE[PUBLIC]SYNONYM[schma.]synonymFOR[schema.]object[@dblink]说明：PUBLIC表表示共有有同义词词，所有有的用户户都可以以访问该该同义词词，如果果没有public，，则为私私有；一一般同义义词都创创建为共共有的，，则需要要createpublicsynonym的系统统权限。。删除同义义词语法格式式：DROP[PUBLIC]SYNONYM[schma.]synonym说明：只要是public同同义词，，则删除除时要加加上public关键键字序列Sequence为所有的的用户生生成统一一的序列列号，也也可以给给某些主主键列自自动生成成编号值值语法格式式：CREATESEQUENCE[schema.]sequence[INCREMENTBYinteger][STARTWITHinteger][MAXVALUEinteger|NOMAXVALUE][MINVALUEinteger|NOMINVALUE][CYCLE|NOCYCLE][CACHEinteger|NOCACHE][ORDER|NOORDER]示例-序序列创建建和使用用1、创建建对象createsequenceseq_snoincrementby1startwith1maxvalue99999nocycle;2、生成同同义词createpublicsynonymsyn_seq_snoforsystem.seq_sno3、取序列列对象中的的当前值selectsyn_seq_sno.currvalfromdual;4、取序列列对象中的的下一个值值selectsyn_seq_sno.nextvalfromdual;--此时对对象中当前前序列值自自动增长了了数据操纵DMLSQL*PLUS常常用的命令令DESC命命令显示表或者者视图结构构的命令save和和get命命令将sqlplus中中缓冲区的的内容保存存到文件，，或者将文件件的内容装装载到缓冲冲区并显示示start或@sql文文件执行一个脚脚本文件spool文件/spooloff从spool文件件名开始，，一直到spooloff命令期间间，缓冲区区内所有的的内容都保保存到文件件课堂演示各各个命令的的使用数据查询命命令单表查询多表联表查查询分组聚合查查询嵌套子查询询查询集合操操作单表查询1、空表dualselect*fromdual;2、连接符符号‘||’selectsname||’tt’fromtab_student3、列或者者表的别名名selectsnamenmfromtab_student或selectsnameasnmfromtab_student4、“虚虚”列rownum，rowid使使用rownum是一个个虚列，在在表中并不不存在，oracle为每个个查询后的的结果集的的记录分别别编号，该该编号就是是rownum1)限制制返回的结结果集只能能是10行行select*fromtab_studentwhererownum<102)将编编号保存到到实际的列列中updatetab_studentsetcol_num=rownum;3)不能能用rownum对对比更大的的数select*fromtab_studentwhererownum>10--该该条件返回回false多表查询查询结果集集合操作交集INTERSECT并集UNION/UNIONALL差集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])从左侧用字符串char