Oracle数据库开发知识点

Oracle数据库开发指南前言参考第一部分基础一、数据库对象表空间Tablespace：存储数据库对象的容器模式Schema表Table表名最长为30个字符，不能以数字开头。

一个表最多254个列。

CREATE TABLE tablename AS select_statement，通过子查询建立新表DESC tablename，查看表或其它数据库对象的定义ALTER TABLE tablename RENAME new_tablename,修改表名RENAME tablename TO new_tablename,修改表名，注意修改表名后，视图必须重建。

索引Index保证表中数据的唯一性；减少定位和检索数据所需要的I/O操作Oracle允许对列值创建索引，也可对表达式创建索引。

所以既可是UNIQUE，也可是UNIQUEOracle对DESC（降序）索引认为是基于函数的索引。

序列Sequence使用序列为表的码值生成唯一的序列值聚簇Cluster视图View由一个预定义的查询构成，可看作为数据库的逻辑表。

SELECT text_length FROM user_views WHERE view_name = …viewname‟Set long 5000SELECT text FROM user_views WHERE view_name = …viewname‟快照Snapshot快照与视图都是基于查询结果的。

但快照使用物理的存储空间，快照中的数据一般都是静态的，只有刷新时才改变。

快照的目的是提供某个时间点的数据。

二、数据类型CHAR(n)，255字符V ARCHAR2(n)：2000字符LONG，2G字符，只能用在列SELECT列表，UPDATE语句的SET子句或INSERT语句的V ALUES子句中，一个表中只能有一个LONG列，LONG列不能建索引，也不能有完整性约束，不能用于表达式。

DATENUMBER(p,s)，s可以小于0，最高有效位38位RAW，255个字节，存储二进制数据LONG RAW，存储大型二进制数据第二部分Oracle查询基本查询SELECT DISTINCT表别名列别名使用表达式数值型：+、-、*、/日期型：+、-字符串连接：||函数条件和组合查询逻辑操作符：=、!=、<>、<、>、<=、>=范围操作符：BETWEEN值集操作符：IN、NOT IN模式匹配：LIKE …_‟或…%‟组合逻辑连接符：AND、OR、NOT伪列实际上在表中不存在，但查询时能返回一个值。

Sequence.CURRV AL和Sequence.NEXTV ALSELECT sequence_name.NEXTV AL FROM dualSELECT sequence_name.CURRV AL FROM dual一旦选择了Sequence.NEXTV AL，该值便不能再重用ROWID，记录的物理存储位置ROWNUM，检索的结果数据的序列号SELECT userid,email FROM awoke WHERE rownum<20 ORDER BY useridSYSDA TE,当前日期和时间SELECT sysdate+3 FROM dualLEVEL，查询的深度，用于Oracle高级查询的树查询空值IS NULLOracle的NVL函数，通知查询用一个值来代替出现的空值多表连接查询笛卡儿连接：列出关联表的全部叉积内连接：抛弃不匹配的记录行外联接查询连接UNION操作符(+)(SELECT DISTINCT userid,email FROM awoke ) UNION (SELECT …libo‟,‟libo@‟)INTERSECT操作符(^)(SELECT userid,email FROM awoke WHERE userid LIKE '1') INTERSECT (SELECT '1','fds' FROM dual) MINUS操作符(-)(SELECT DISTINCT userid,email FROM awoke) MINUS (SELECT DINSTINCT userid,email FROM awoke)子查询和动态视图子查询用在条件WHERE子句动态视图用在FROM子句，类似普通表，但无法用组成动态视图的各原始表上的索引。

TRUNCATE TABLE tablename,快速删除表中所有记录，且不能回滚Dual内部表第二部分Oracle函数标准函数转换函数TO_CHAR(number[,format]),format类型TO_CHAR(date,format)，‟Q‟为季度y q m d h m sTO_DATE(string,format)TO_NUMBER(number[,format])日期函数ADD_MONTHS(date,number),输入日期加上指定的月数后产生的新日期，允许负数和小数LAST_DAY(date),输入日期的所在月份的最后一天MONTHS_BETWEEN（date1,date2）,返回两个日期之间的月份，小数部分为月中剩余部分，第二个日期若早则返回负数NEXT_DA T(date,day),返回输入日期指定若干天后的日期,day为字符型本周日SELECT NEXT_DAY(sysdate,'SUNDAY') FROM dual上周五SELECT NEXT_DAY(sysdate-7,'SUNDAY') FROM dualROUND(date,format)，把一个日期四舍五入至最接近格式元素指定的形式。

SQL>SELECT TO_CHARr(sysdate,'YYYY-MM-DD HH24:MI:SS') cur_date , TO_CHAR(ROUND(sysdate,'MM'),'YYYY-MM-DD HH24:MI:SS') round_date from dual;结果：CUR_DATE：2003-04-26 12:00:01ROUND_DATE：2003-05-01 00:00:00TRUNC(date,format),按格式掩码输入日期后返回SQL>SELECT TO_CHARr(sysdate,'YYYY-MM-DD HH24:MI:SS') cur_date , TO_CHAR(TRUNC(sysdate,'MM'),'YYYY-MM-DD HH24:MI:SS') trunc_date from dual;结果：CUR_DATE：2003-04-26 11:59:11TRUNC_DATE：2003-04-01 00:00:00数值函数ABS(number)，返回绝对值CEIL(number)，返回大于等于输入值的下一个整数FLOOR(number)，返回小于等于输入值的最大整数MOD(n,m)，返回n除m的模ROUND(number,decimal_digits)，四舍五入，小数位可以为0和负数SIGN(number)，返回输入值的符号SQRT(number)，返回输入数据的平方根TRUNC(number[,decimal_pluces])，在指定的小数位上把数值截断。

字符函数字符和ASCII码转换ASCII(character)、CHR(number)CONCAT(string1,string2)，字符串连接函数，等同于‟||‟，尽可能使用‟||‟INITCAP(string),将字符串中每个单词的第一个字母变成大写，其余字符为小写SQL> SELECT INITCAP('WE'||' '||'are'||' '||'the world') FROM dual;结果：We Are The WorldINSTR(input_string,search_string[,n,[m]])，从字符串中的第n个字符开始查找搜索字符串底第m次出现。

注意是字节数。

SQL>SELECT INSTR('时光软件cicro时光宽带','时光',1,2) FROM dual;结果：14LENGTH(string)，返回字符串字节长度，大小写转换LOWER(string)，UPPER(string)删除串中字符LTRIM(string[,set_of_chars])，删除所有前导字符，默认为空格SQL> SELECT LTRIM('ccsc','c') FROM dual;结果：scRTRIM(string[,set_of_chars])，删除所有后继字符，默认为空格TRIM(string)，去掉左右空格TRIM([LEADING\TRAILING\BOTH] trim_char\trim_char FROM trim_source)SQL> SELECT TRIM( 'c' FROM 'ccsc') FROM dual;结果：s填充串中字符LPAD(string,n[,pad_chars])，在字符串左边填充上pad_chars指定的字符（不指定为空格），使其总长为n RPAD(string,n[,pad_chars])，在字符串右边填充。

字符排序NLSSORT(string[,nlsparms]),对字符串按字符集中的字符序列进行排序，缺省为ASCII。

SUBSTR(string,start[,length])，子串处理字符转换CONVERT(char_value,target_char_set,source_char_set)，将字符串从一个字符集转换到另一个字符集REPLCAE(string,search_string[,replace_string])，字符串替换，将所有出现在搜索字符集中的字符串转换成替换字符串。

TRANSLA TE(string,search_set[,replace_set])，字符替换，将所有出现在搜索字符集中的字符转换成替换字符集中的相应字符。

SQL>SELECT TRANSLA TE('haha','abcdefghijklmnopqrstuvwxyz','bcdefghijklmnopqrstuvwxyza') FROM dual;结果：Ibib单行函数GREATEST(list of values)，返回列表中项的最大值或最近日期LEAST(list of valuss) ，返回列表中项的最小值或最早日期NVL(expression,replacement_value)，空值转换NVL2(expression1, expression2, expression3)，空值转换，如果expression1非空，则返回expression2，为空则返回expression3多行函数A VG(expression)，平均值COUNT(expression)，个数MAX(expression)，最大值MIN(expression)，最小值SUM(expression)，求和STDDEV(expression)，统计标准偏差V ARIANCE(expression)，统计方差第三部分Oracle复杂查询递归查询树查询两个子句CONNECT BY定义表中的各行的关系START WITH定义数据行查询的初始起点向下浏览SELECT org_id,org_name,supervisor_org_id FROM orgCONNECT BY supervisor_org_id = PRIOR org_idSTART WITH supervisor_org_id IS NULL;PRIOR关键字指定当前选定行的supervisor_org_id必须等于前面已选择过的数据行的org_id。