一.Oracle数据类型 1.字符数据类型 .> char:可以存储字母数字值，长度在1到2000个字节。 .> varchar2:存储可变长度的char类型字符串,大小在1到4000个字节范围内。 .> long:存储可变长度的字符数据,最多存储2GB。 long类型的使用限制： ************************************** *{ *.. 一个表中只有一列可以为long数据类型。 *.. long列不能定义为唯一约束或主键约束。 *.. long列上不能建立索引。 *.. 过程或存储过程不能接受long数据类型的参数。 *} *************************************** 2.数字数据类型 .> number：存储正数，负数，零，定点书和精度为38位的浮点数。 number类型的格式: number[(p[,s])] 其中p为精度,表示数字的总位数; s为范围，表示小数点右边的位数,它在-84至127之间。 3.日期时间数据类型 .> date：存储表的日期和时间数据，使用7个字节固定长度， 每个字节分别存储世纪，年，月，日，小时，分和秒；值从公元前4712年1月1日到公元9999年12月31日。 { Oracle中的sysdate函数功能是返回当前的日期和时间。 } .> timestamp：存储日期的年，月，日以及时间的小时，分和秒值。其中秒值精确到小数点后6位，同时包含时区信息。 { Oracle中的systimestamp函数功能是返回当前日期，时间和时区。 } 4.raw和long raw数据类型(二进制数据) .> raw: 存储基于字节的数据。最多存储2000个字节，使用需指定大小。raw数据类型可以建立索引。 .> long raw: 存储可变长度的二进制数据。最多能存储2GB；不能索引。与long类型的限制同效。 5.lob数据类型('大对象') *********************************** * lob类型可以存储多达4GB的非结构化信息。 * Oracle中的表可以有多个lob列，每个lob列可以是不同的lob类型。 *********************************** .> clob: 存储大量的单字节字符数据和多字节字符数据。例：非结构化的XML文档。 .> blob: 存储较大的二进制对象。例：图形，视频，音频等。 .> bfile: 文件定位器；指向位于服务器文件系统是的二进制文件(存储一个文件路径)。 ----------------------------------------------------------------------------------------------------------------------- 6.Oracle中伪列 .> rowid: 返回行地址，可用来定位表中的一行。可以唯一地标识数据库中的一行。 重要的用途 **************************** *.. 能一最快的方式访问表中的一行 *.. 能显示表的行是如何存储的 *.. 可以作为表中行的唯一标识 **************************** .> rownum: 对于一个查询返回的每一行进行标识；可用于限制查询返回的行数。(不是数据表的实际列) 二.Oracle中的sql操作符 .>算数操作符: 包括: +(加),-(减),*(乘),/(除) *和/具有相同的优先级，+和-j具有相同的优先级,*和/的优先级高于+和-。可用小括号来控制计算顺序。 .>比较操作符: 包括：=(相等),!=(不相等),<(小于),>(大于),<=(小于等于),>=(大于等于) between....and...(检查是否在两个值之间) in(与列表中的值相匹配) link(匹配字符模式<模糊匹配>) is null(检查是否为空) .>逻辑操作符： 包括： and(与),or(或),not(非) .>集合操作符： 包括： union(联合)：返回两个查询选定的所有不重复的行。 union all(联合所有):合并两个查询选定的所有行，包括重复的行。 intersect(交集):返回两个查询共有的行。 minux(减集):返回有第一个查询选定但是没有被第二个查询选定的行。 .>连接操作符： || : 将两个或多个字符串合并成一个字符串，或者将一个字符串与一个数值合并在一起。 操作符的优先级 ************************************************ *算数操作符 高 *连接操作符 | *比较操作符 | *not逻辑操作符 | *and逻辑操作符 | *or逻辑操作符 低 ************************************************ 三.SQL函数 .>日期函数 add_months(d,n)<d是日期，n是月数>：返回指定日期加上指定月数后的日期值。 months_between(d1,d2)<d1,d2都是两个日期>:返回两个日期之间的月数。 last_day(d)<d是日期>:返回指定日期当月的最后一天的日期值。 round(d,[fmt])<d是日期，fmt指定格式模型>:返回日期值，此日期四舍五入为格式模型指定的单位。 (fmt是一个选项，默认舍入为最靠近的那一天。 如果格式为‘year’则舍入到年的开始,即1月1日； 如果格式为‘month’则舍入到月的第一天； 如果格式为‘day’则舍入到最靠近的星期天。 ) next_day(d,day)<d是日期，day指定周内任何一天 如‘星期一’>：返回指定的下一个星期几的日期。 trunc(d,[fmt])<d是日期，fmt指定格式模型>:返回日期值，指定日期截断为格式模型指定的单位的日期。 (与round函数类似,不同的是它只舍不入。) extract(fmt from d)<fmt指定格式模型,d是日期>:提取日期时间类型中的特定部分。 (fmt取值可以是:year,month,day,hour,minute,second;注意此处的格式不能使用单引号。) .>字符函数 函数 说明 输入 输出结果 initcp(char) 首字母大写 select initcap('hell0') from dual Hello lower(char) 转换为小写 select lower('FUN') from dual fun upper(char) 转换为大写 select upper('sun') from dual SUN ltrim(char,set) 左剪裁 select ltrim('xyzadams','xyz') from dual adams rtrim(char,set) 右剪裁 select rtrim('xyzadams','ams')from dual xyzad translate(char,from,to) 按字符翻译 select translate('jack','abcd','1234') from dual j12k peplace(char,search_str,replace_str) 字符串替换 select replace('jack and jue','j','bl') from dual jack and blue instr(char,substr[,pos]) 查找子串位置 select instr('worldwide','d') from dual 5 substr(char,pos,len) 取子字符串 select substr('abcdefg',3,2) from dual cd concat(char1,char2) 连接字符串 select concat('Hello','world') from dual Helloworld ================================= .. chr(int)<int是ASCII码>:根据ASCII码返回对应的字符。 .. lpad(str,int,char)和rpad(str,int,char)<str是源字符串,int指定总长度,char是填充的字符>:用指定的字符(左|右)填充源字符串到指定长度。 .. trim([[leading|trailing] trim_char] from trim_sourse)<trim_char指定裁剪的字符,trim_sourse是源字符串> ( 此函数组合了ltrim和rtrim的功能。 leading选项时与ltrim相似,裁减与trim_char相等的开头字符。 trailing选项时与rtrim相似,裁减与trim_char相等的结尾字符。 ) .. length(str)<str是字符串>:返回字符串的长度。 .. decode(expr,search1,trsult1,search2,trult2...[,default])<expr是字符变量或数据表字段,search是expr的预期值,trsult是返回值,default是无匹配是返回的值> ( decode函数进行逐个值的替换。 ) .>数字函数 函数 说明 输入 输出结果 abs(n) 取绝对值 select abs(-15) from dual 15 ceil(n) 向上取整 select ceil(44.778) from dual 45 sin(n) 正弦 select sin(1.571) from dual 0.999999979 cos(n) 余弦 select cos(0) from dual 1 sign(n) 取符号 select sign(-32) from dual -1 floor(n) 向下取整 select fllor(100.2) from dual 100 power(n) m的n次幂 select power(4,2) from dual 16 mod(m,n) 取余数 select mod(10,3) from dual 1 round(m,n) 四舍五入 select round(100.256,2) from dual 100.26 trunc(m,n) 截断 select trunc(100.256,2) from dual 100.25 sqrt(n) 平放根 select sqrt(4) from dual 2 .>转换函数 to_char(d|n[,fmt])<d是日期，n是数字,fmt是指定日期或数字的格式>:将指定的日期或数字转换成字符串(varchar2)。 to_date(char[,fmt])<char是日期格式的字符串,fmt是日期的格式>:将char或varchar数据类型转换为日期类型。 to_number(char)<char是包含数字的字符串>:将包含数字的字符串转换为数字。 .>其他函数 nvl(expression1,expression2)<exprission1是变量或数据表字段，exprission2是一个值>:将空值替换为指定的值。 ( 如果expression1为NULL，则nvl返回expression2 如果expression1不为NULL，则nvl返回expression1 <sexpression2的类型将转换为expression1的类型> ) nvl2(expression1,expression2,expression3)<exprission1是变量或数据表字段，exprission2和exprission3是一个值>:与nvl类似 ( 如果expression1不为NULL，则nvl返回expression2 如果expression1为NULL，则nvl返回expression3 ) nullif(expr1,expr2)<expr1，expr1分别为表达式>:比较两个表达式，如果相等，则返回空值(null)，否则返回expr1。 .>分组函数 avg(column)<column是列名>：返回参数中指定列的平均值。 min(column)<column是列名>: 返回参数中指定列的最小值。 max(column)<column是列名>: 返回参数中指定列的最大值。 sum(column)<column是列名>: 返回记录集中值的总和。 count([distinct]*|column[,column..])<distinct选项指定去除重复项,column是列名>: 返回记录集中的行数。 <group by子句用于将信息表化分为组，having字句用来指定group by的检索条件> .>分析函数 row_number() over ([partition by column] order by clause[,多列] [desc|esc])<column指定分组列名，clause指定排列列名>:为有序组中的每一行(化分部分的行或查询返回的行)返回一个唯一的排序 值，序号由order by字句指定,从1开始。(值相同，而排位不相同) rank () over ([partition by column] order by clause[,多列] [desc|esc])<column指定分组列名，clause指定排列列名>:计算一个值在一组值中的排位，排位是以1开头的连续整数，具有相等值的行排 位相同，序数随后跳跃相应的数值。(值相同，排位相同,排位有可能不连续) dense_rank() over ([partition by column] order by clause[,多列] [desc|esc])<column指定分组列名，clause指定排列列名>:计算一个行在一组有序行中的排位，排位是以1开头的连续整数，具有相同 值的排位相同，并且排位是连续的。(值相同，排位相同,排位连续) 四.Oracle锁 锁定是数据库用来控制共享资源并发访问的机制。 .>行级锁(用于特定行) 行级锁是一中排他锁，防止其他事务修改此行，当是不会阻止读取此行的操作。 在使用insert,update,delete和select ...for update等语句时，Oralce会自动应用行级锁定。 select...for update语句允许用户每次选择多行记录进行更新，这些记录会被锁定，且只能有发起查询的用户进行编辑。只有在回滚或提交事务后，锁定才会释放，其他用户才可以编辑这些记录。 { select ...for update [of column_list] [wait n | nowait ]<column_list是列的列表，n是等待的秒数，nowait指定不等待> > of 子句用于指定即将更新的列，即锁定行上的特定列。 > wait 子句指定等待其他用户释放锁的秒数，防止无限制的等待。 } .>表级锁(用于整个表) 表级锁定将保护表数据，在事务处理过程中，表级锁会限制对整个表的访问。可以使用lock table语句显示地锁定表。表级锁用来限制对表执行添加，更新和删除等修改操作。 { lock table <table_name> in <lock_mode> mode [nowait];<table_name是锁定表的名称，lock_mode是锁定的模式，nowait指定不等待> >lock_mode是锁定的模式。 >nowait 关键字用于防止无限期的等待其他用户释放锁。 ( 表级锁的模式包括： >行共享(row share,rs)： 允许其他用户访问和锁定该表，但是禁止排他锁定整个表。 >行排他(row exclusive，rx)：与行共享相同，同时禁止其他用户在此表上用共享锁。 >共享(share, s)：仅允许其他用户查询表中的行，但不允许插入，更新和删除。 >共享行排他(share row exclusive,srx)：执行比共享锁更多的限制。防止其他事物在表上应用共享锁，共享排他锁以及排他锁。 >排他(exclusive,x)：对表执行最大限制。除了允许其他用户查询该表的记录，排他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。 ) * 执行commit或rollback命令可以释放锁定。 } 五.Oracle表分区 表分区的优点: .. 改善表的查询性能。 .. 表更容易管理。 .. 便于备份和恢复。 .. 提高数据安全性。 .>范围分区 范围分区根据表的某一列或一组列的值范围，决定数据存储在那个区上。 在create table语句中增加parition子句可以创建表分区。 语法： partition by rang (column_name) ( partition part1 value less than(range1) [tablespace tbs1], partition part2 value less than(range2) [tablespace tbs2], ...... partition partn value less than(MAXVALUE) [tablespace tbsN] ); 其中： column_name：是以其为基础创建范围分区的列，特定列的该列值称为分区键。 part1..partn：是分区的名称。 range1...MAXVALUE：是分区的边界值。 tbs1...tbsn：是分区所在的表空间(可选项)。 .>散列分区 散列分区通过分区键值上执行一个散列函数来决定数据的物理位置。(散列分区把记录平均分布到不同的分区，减少了磁盘I/O争用的可能性) 语法: partition by hash (column_name) partitions number_of_partitions [store in (tablespace_list)]; 或 partition by hash (column_name) ( partition part1 [tablespace tbs1], partition part2 [tablespace tbs2], ..... partition partn [tablespace tbsn] ) 其中: column_name：是以其为基础创建散列分区的列。 number_of_partitions：是散列分区的数目，使用这种方法系统会自动生成分区的名称。 tablespace_list：指定分区使用的表空间，如果分区数目比表空间的数目多，分区将会以循环的方式分配到表空间中。 .>复合分区 复合分区是范围分区和散列分区的结合。 语法: partition by rang (column_name1) subpartition by hash (column_name2) subpartitioins number_of_partitions [store in (tablespace_list)] ( partition part1 value less than(range1) [tablespace tbs1], partition part2 value less than(range2) [tablespace tbs2], ...... partition partn value less than(MAXVALUE) [tablespace tbsN] ) 其中: column_name1：是以其为基础创建范围分区的列。 column_name2：是以其为基础创建散列分区的列。 number_of_partitions：是散列分区的数目。 part1..partn：是分区的名称。 range1...MAXVALUE：是分区的边界值。 .>列表分区 列表分区允许用户明确地控制行到分区的映射。 语法: partition by list (column_name) ( partition part1 values (values_list1), partition part2 values (values_list2), .... partition partn values (DEFAULT) ) 其中: column_name：是以其为基础创建列表分区的列。 part1..partn：是分区的名称。 values_list：是对应分区键值的列表。 DEFATLT：关键字允许存储前面的分区不能存储的记录。 *************************************************************** *在分区表中插入记录：与在普通表中插入数据完全相同。 *在分区表中查询记录：select * from 表名 partition (分区名) *删除分区中的记录： delete from 表名 partitioin (分区名) *************************************************************** <*>分区维护操作 .>添加分区 alter table ... ADD partition语句用于在现所有的最后一个分区(称为'高'端)之后添加新的分区。 例： alter table sales add partitions p4 values less than (4000); .>删除分区 alter table ... drop partition 语句用于删除分区。(删除分区时，分区中的数据也随之删除) 例: alter table sales drop partition p4; .>截断分区 alter table ... truncate partition 语句用于截断分区,截断分区将删除分区中的所有记录。 例: alter table sales truncate partition p3; .>合并分区 合并分区可将范围分区表或复合分区表的两个相邻分连接起来。结果分区将继承被合并的两个分区的较高上界。 语法: alter table table_name merge partitions partitions1_name,partitions2_name into partition3_name; 其中： table_name：是表名。 partitions1_name,partitions2_name：是已有分区。 partitions3_name：合并到分区的名称。 .>拆分分区 使用split partition语句在表的开头或中间添加分区。拆分分区允许用户将一个分区拆分为两个分区。当分区过大，可以对分区进行拆分。 语法: alter table table_name split partition partiton_name at (value) into (partition partiton1,partition partiton2); 其中： table_name：是表名。 partiton_name：已有分区名。 value：拆分分隔值。 partition partiton1,partition partiton2：表示拆分后的新分区。 六.同义词 同义词是数据库对像的一个别名，这些对象可以是表，视图，序列，过程，函数，程序包，甚至其他同义词。 同义词用途: { .. 简化sql语句 .. 隐藏对象的名称和所有者 .. 为分布式数据库的远程对象提供了位置透明性 .. 提供对象的公共访问 } 同义词允许应用程序访问数据库对象，不论哪个用户或哪个数据库拥有该对象。但是同义词不能代替权限，在使用同义词前要确保用户已得到访问对象的权限。 可以通过同义词执行select,insert,update,delete,lock table,grant和revoke等语句。同义词只是表的一个别名，因此对它的所有操作都会影响到表。 .>私有同义词 私有同义词只能被当前模式的用户访问。 私有同义词名称不可与当前模式的对象名称相同。 要在自身的模式创建私有同义词，用户必须拥有create sysnonym系统权限。 要在其他用户模式创建同义词，用户必须拥有create any synonym系统权限。 语法: create [or replace] synony [schema.]synonym_name for [schema.]object_name 其中: or relaoce：表示在同义词存在的情况下替换该同义词。 synonym_name：表是要创建的同义词的名称。 object_name：指定要为之创建同义词的对象的名称。 .>公有同义词 公有同义词可被所有的数据库用户访问。 创建公有同义词，用户必须拥有greate public synonym系统权限。 create [or replace] public synonym synonym_name for [schema.]object_name 其中: or relaoce：表示在同义词存在的情况下替换该同义词。 synonym_name：表是要创建的同义词的名称。 object_name：指定要为之创建同义词的对象的名称。 <*>删除同义词 drop synonym语句用于从数据库中删除同义词。要删除同义词用户必须有相应的权限。 语法: drop [public] synonym [schema.]synonym_name; 七.序列 序列是用来生成唯一，连续的整数的数据库对象。 序列通常用来自动生成主键或唯一的值。 序列可以按升序排列，也可以按降序排列。 语法: create sequence sequence_name [stare with integer] [increment by integer] [maxvalue integer|nomaxvalue] [minvalue integer|nominvalue] [cycle|nocycle] [cache integer|nocache]; 其中: sequence_name：是创建的序列名称。 stare with：指定要生成的第一个序列号。 increment by：用于指定序列好之间的间隔。 maxvalue：指定序列可以生成的最大值。 nomaxvalue：如果指定了nomaxvalue，oracle将升序序列的最大值设为10的27次方;将降序序列的最大值设为-1。 minvalue：指定序列可以生成的最小值。 nominvalue：无最小值，oracle将升序序列的最小值设为1;将降序序列的最小值设为10的-26次方。 cycle：指定序列在达到最大值或最小值后，将继续从头开始生成值。 nocycle：指定序列在达到最大值或最小值后，将不能在继续生成值。这是默认选项。 cyche：使用cyche选项可以预先分配一组序列号,并将其保存在内存中。这样可以更快地访问序列号，但用完缓存中的所有序列号，Oralce将生成另一组数值，并将其保留在缓存中。 nocyche：不缓存序列号。 如果创建序列时忽略了cyche和nocyche选项,Oracle将默认缓存20个序列号。 <*>访问序列 语法; sequence_name . nextval|currval 其中: sequence_name：是已创建的序列名称。 nextvla：创建序列后第一次使用nextval时,将返回该序列的初始值。以后在引用nextval时，将使用increment by子句的值来增加序列值,并返回这个新值。 currval：返回序列的当前值。 <*>更改序列 alter sequence 命令用于修改序列的定义。 { .. 设置或删除minvalue或maxvale。 .. 修改增量值。 .. 修改缓存中的序列号的数目。 } 语法: alter sequence [schema.]sequence_name [increnment by integer] [maxvalue integer|nomaxvalue] [minvalue integer|nomaxvalue] [cycle|nocycle] [cache ingeter|nocache]; 注意:不能修改序列的start with参数。 <*>删除序列 drop sequence命令用于删除序列。 语法: drop sequence 序列名
