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)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范围分区根据列值的范围将行映射到分区由表的分区
规范
编程规范下载gsp规范下载钢格栅规范下载警徽规范下载建设厅规范下载
进行定义: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])从左侧用字符串char2补齐字符串char1至长度nLTRIM(char[,SET])把char中最左侧的若干个字符去掉,以使其首字符不在SET中RPAD(char1,n[,char2])从右侧用char2补齐char1至长度n.RTRIM(char[,SET])把char中最右侧的若干个字符去掉,以使其尾字符不在SET中TO_DATE(char[,fmt])将字符型的日期转换为日期型TO_NUMBER(char[,fmt])将字符串转换为数字算术函数函数返回值ABS(n)返回n的绝对值CELL(n)返回大于或等于n的最小整数FLOOR(n)返回小于或等于n的最大整数MOD(m,n)返回m除以n的余数POWER(m,n)返回m的n次方ROUND(m[,n])对m进行四舍五入(n大于0时,将m四舍五入到小数点右边n位,n等于零时,表示对m进行取整,n小于0时,表示将m四舍五入到小数点左边n位。)SIGN(n)判断n的正负(n大于0返回,n等于0返回0,n小于0返回-1SQRT(n)返回n的平方根TRUNC(m[,n])对m进行截断操作(n截断到小数点后第n位,如果n未给出,则系统默认为0,n也可以为负数,表示保留到小数点前第n位。日期函数函数返回值ADD_MONTHS(d,n)返回d加上n个月的日期LAST_DAY(d)返回d所在月份最后一天的日期MONTHS_BETWEEN(d1,d2)返回d1和d2之间相隔的月数NEXT_DAY(d,s)返回晚于d的第一个s的日期(s要求是Sunday,Monday,Tuesday,Wednsday,Thursday,Friday,SaturdaySYSDATE返回系统当前日期和时间。其他函数函数返回值GREATEST(参数1[,参数2]…)返回参数1,参数2…中的最大值LEAST(参数1[,参数2]…)返回参数1,参数2…中的最小值DECODE(e,s1,t1[,s2,t2]…[,def])若e等于s1,函数返回t1,若e等于s2,函数返回t2,…否则,函数返回def。表达式e允许是任何数据类型,但要求被比较的各个s具有相同的数据类型。def被默认时,表示默认值是NULLUSER返回当前Oracle用户的用户名NVL(参数1,参数2)如果参数1非空则返回参数1,反之则返回参数2。演讲完毕,谢谢观看!