oracle_sql 基础 （1）

oracle_sql 基础
============================================
提交数据有三种类型：显式提交、隐式提交及自动提交。
(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT；
2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；
空值无法和数字型的列和常量进行计算，但是可以和字符串类型的列和常量进行连接。
在对空值进行加减乘除操作之后，返回的是空值或者未知。对空值进行连接字符串的操作之后，返回为被连接的字符串。
在一个SQL语句中，一个列别名既能用在SELECT子句也能用在ORDER BY子句中。你不能在WHERE子句中使用列别名。
日期和字符文字 必须放在单但引号 (' ') 中；数字不需要。
BETWEEN … AND …实际上是由Oracle服务器转变为一对AND条件：(a >= 下限) AND (a <= 上限)，所以使用BETWEEN … AND … 并没有性能的提高，只是逻辑上简单。
使用IN ( ... )条件，实际上是由Oracle服务器转变为一组OR条件：a = value1 OR a =value2 OR a = value3，所以使用 IN ( ... ) 并没有得到性能的提高，只是逻辑上简单。
如果你想要搜索包含‘SA_’的字符串，可以用下面的SQL语句：
Select Employee_Id, Last_Name, Job_Id From Employees Where Job_Id Like '%SA\_%AN' Escape '\';
NULL不能等于或不等于任何值。

默认的优先顺序：
1 算术运算
2 连字操作
3 比较操作
4 IS[NOT]NULL, LIKE, [NOT]IN5
5 [NOT] BETWEEN
6 NOT逻辑条件
7 AND逻辑条件
8 OR逻辑条件

ORDER BY子句在查询完成后执行，除非使用了FOR UPDATE子句，ORDER BY子句应放在最后。

对于空值，升序排序时显示在最后，降序排序时显示在最前面。
在内部，SELECT语句的执行顺序如下：FROM 子句、WHERE 子句、SELECT 子句、ORDER BY 子句。

+++++++++++++++++++
ORACLE SQL单行函数

字符函数包括：大小写处理函数和字符处理函数
大小写处理函数：LOWER、UPPER、INITCAP
字符处理函数：CONCAT、SUBSTR、LENGTH、INSTR、LPAD|RPAD、TRIM 、REPLACE
LOWER(c) 、UPPER(c)将指定字符串内字符变为小写，支持CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,NCLOB类型
INITCAP(c) 将字符串中单词的第一个字母转换为大写，其它则转换为小写

CONCAT(c1,c2) 连接字符串，等同于||
SUBSTR(c1,n1[,n2]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。
n1=开始长度；
n2=截取的字符串长度，如果为空，默认截取到字符串结尾；
如果n1=0 then n1=1
如果n1>0，则oracle从左向右确认起始位置截取
如果n1<0，则oracle从右向左数确认起始位置
如果n1>c1.length则返回空
如果n2<1，则返回空

LENGTH(c) 返回指定字符串的长度

INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置
c1:原字符串
c2:要寻找的字符串
n1:查询起始位置，正值表示从左到右，负值表示从右到左 (大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。黑黑，如果为0的话，则返回的也是0
n2:第几个匹配项。大于0

LPAD(c1,n[,c2]) 返回指定长度=n的字符串，需要注意的有几点：
如果n<c1.length则从右到左截取指定长度返回；
如果n>c1.length and c2 is null，以空格从左向右补充字符长度至n并返回；
如果n>c1.length and c2 is not null，以指定字符c2从左向右补充c1长度至n并返回；

RPAD(c1,n[,c2]) 返回指定长度=n的字符串，基本与上同，不过补充字符是从右向左方向正好与上相反；

TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1)
如果没有指定任何参数则oracle去除c1头尾空格
如果指定了c2参数，则oracle去掉c1头尾c2
如果指定了leading参数则会去掉c1头部c2
如果指定了trailing参数则会去掉c1尾部c2
如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗？没区别！)

REPLACE(c1,c2[,c3]) 将c1字符串中的c2替换为c3，如果c3为空，则从c1中删除所有c2。

数字函数包括ROUND、TRUNC、MOD。
ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，
如果n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数，事实上执行时此处的判断并不严谨，即使n2为非整数，
它也会自动将n2取整后做处理，但是我文档中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的)。

TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。

MOD(n1,n2) 返回n1除n2的余数，如果n2=0则返回n1的值。

Oracle数据库以内部数字格式存储日期，表示世纪、年、月、日、小时、分和秒。
对于任何日期默认显示和输入格式是DD-MON-RR。有效的Oracle日期在公元前4712年1月1日和公元9999年12月31日之间。
完整的日期可能是1994年7月7日 5:10:43 p.m （June 7th, 1994 5:10:43 p.m）。
该日期在内部存储如下：
CENTURY YEAR MONTH DAY HOUR MINUTE SECOND
19 94 06 07 5 10 43
Oracle服务器是2000年兼容的。当一个带有日期字段的记录被插入到表中时，世纪信息可从SYSDATE函数获得，可是，当日期字段显示到屏幕上时，默认情况下世纪部分不显示。
DATE数据类型总是以4位内部数字存储年信息：两位数字代表世纪，两位数字代表年。

CURRENT_DATE 返回当前session所在时区的默认时间
例如：
SQL> alter session set nls_date_format = 'mm-dd-yyyy' ;
SQL> select current_date from dual;
SYSDATE 功能与上相同，返回当前session所在时区的默认时间。但是需要注意的一点是，如果同时使用sysdate与current_date获得的时间不一定相同，某些情况下current_date会比sysdate快一秒。
可能current_date是将current_timestamp中毫秒四舍五入后的返回，虽然没有找到文档支持，但是想来应该八九不离十。
同时，仅是某些情况下会有一秒的误差，一般情况下并不会对你的操作造成影响，所以了解即可。

既然数据库以数字方式存储日期，你就可以用算术运算符进行计算，例如，加或减。你可以加或减数字常数以及日期。从日期加或者减一个数，结果是一个日期值，两个日期相减，得到两个日期之间的天数，用小时数除以24，可以加小时到日期上。
运算
结果
说明
date + number 日期 加一个天数到一个日期上
date - number 日期 从一个日期上减一个天数
date - date   天数 用一个日期减另一个日期
date + number/24 日期 加一个小时数到一个日期上
Select Last_Name, (Sysdate - Hire_Date) / 7 As Weeks
From Employees
Where Department_Id = 90;

MONTHS_BETWEEN(d1,d2) 返回d1与d2间的月份差，视d1,d2的值大小，结果可正可负，当然也有可能为0

ADD_MONTHS() 返回指定日期月份+n之后的值，n可以为任何整数。

NEXT_DAY(d,n) 返回指定日期后第一个n的日期，n为一周中的某一天。但是，需要注意的是n如果为字符的话，它的星期形式需要与当前session默认时区中的星期形式相同。
本文的例子nls_language值为SIMPLIFIED CHINESE
Select Next_Day(Sysdate, 5) From Dual;
Select Next_Day(Sysdate, '星期四') From Dual;
两种方式都可以取到正确的返回，但是：
Select Next_Day(Sysdate, 'Thursday') From Dual;
则会执行出错，提供你说周中的日无效，就是这个原因了。

LAST_DAY(d) 返回指定时间所在月的最后一天

ROUND(d[,fmt]) 前面讲数值型函数的时候介绍过ROUND，此处与上功能基本相似，不过此处操作的是日期。如果不指定fmt参数，则默认返回距离指定日期最近的日期。

TRUNC(d[,fmt]) 与前面介绍的数值型TRUNC原理相同，不过此处也是操作的日期型。

尽管隐式数据类型转换是可用的，但建议你做显式数据类型转换以确保SQL语句的可靠性。
对于直接赋值，ORACLE SQL可以进行以下的隐式转换：
从                    到
VARCHAR2 or CHAR     NUMBER
VARCHAR2 or CHAR    DATE
NUMBER                VARCHAR2
DATE                VARCHAR2
对于表达式赋值，ORACLE SQL可以进行以下的隐式转换：
从                    到
VARCHAR2 or CHAR    NUMBER
VARCHAR2 or CHAR    DATE
SQL 提供三种函数来从一种数据类型转换值到另一种：TO_CHAR、TO_NUMBER、 TO_DATE。

TO_CHAR() 函数又可以分三小类，分别是
转换字符->字符TO_CHAR(c)：将nchar,nvarchar2,clob,nclob类型转换为char类型；
例如：
Select To_Char('AABBCC') From Dual;
转换时间->字符TO_CHAR(d[,fmt])：将指定的时间(data, timestamp, timestamp with time zone)按照指定格式转换为varchar2类型；
必须加单引号，并且区分大小写，能够包含任一有效的日期格式元素，有一个fm元素用来删除填补的空，或者前导零，用一个逗号与日期值分开。
例如：
Select To_Char(Sysdate, 'yyyy-mm-dd hh24:mi:ss') From Dual;
转换数值->字符TO_CHAR(n[,fmt])：将指定数值n按照指定格式fmt转换为varchar2类型并返回；
例如：
Select To_Char(-100, 'L99G999D99MI') From Dual;

TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型，如果fmt参数不为空，则按照fmt中指定格式进行转换。
注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换，c则必须为大于0并小于5373484的正整数。
例如：
Select To_Date(2454336, 'J') From Dual;
Select To_Date('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') From Dual;

TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。
例如：
Select To_Number('-100.00', '9G999D99') From Dual;

日期格式模板
YYYY    数字全写年
YEAR     年的拼写
MM       月的两数字值
MONTH     月的全名
MON       月的三字母缩写
DY        周中天的三字母缩写
DAY        周中天的全名
DD        月的数字天

时间格式模板
元素            说明
AM或PM            正午指示
A.M.或P.M.        带句点的正午指示
HH或HH12或HH24    天的小时，或小时(1–12)，或小时(0–23)
MI                分钟 (0–59)
SS                秒 (0–59)
SSSSS            午夜之后的秒 (0–86399)

数字格式模板
9                表示一个数
0                强制显示为零
$                放置一个浮动美元符号
L                使用浮动本地货币符号
.                打印一个小数点
,                打印一个千位指示
MI                右边减号 (负值)
PR                将负数加上括号
EEEE            科学计数法 (格式化必须指定四个E)
V                乘10，n次 (n = V 后面 9 的个数)
B                将0显示为空格

NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同，则oracle会自动将c2转换为c1的类型。
Select Nvl(Null, '12') From Dual;

NVL2(c1,c2,c3) 如果c1非空则返回c2,如果c1为空则返回c3
例如：
Select Nvl2('a', 'b', 'c') , Nvl2(Null, 'b', 'c') From Dual;

NULLIF(c1,c2) 逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END
例如：
SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;

COALESCE(n1,n2,....n) 返回序列中的第一个非空值
例如：
Select Coalesce(Null, 5, 6, Null, 9) From Dual;

CASE表达式可以让你在SQL语句中使用IF-THEN-ELSE逻辑，而不必调用过程。
DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的if else，只不过它并不通过多行语句，而是在一个函数内实现if else的功能。
exp做为初始参数。s做为对比值，相同则返回r，如果s有多个，则持续遍历所有s，直到某个条件为真为止，否则返回默认值def(如果指定了的话)，如果没有默认值，并且前面的对比也都没有为真，则返回空。
毫无疑问，decode是个非常重要的函数，在实现行转列等功能时都会用到，需要牢记和熟练使用。