1.
distinct關(guān)鍵字去掉重復(fù)行。

2.空值的處理

3.列的別名
直接在列名后面跟隨別名，或者使用as關(guān)鍵字。
select auth_id author_id from authors;
等價(jià)于
select auth_id?as author_id from authors;

oracle執(zhí)行結(jié)果的列名默認(rèn)都是大寫的，如果希望寫的是什么結(jié)果顯示就是什么的話，可以使用""來(lái)引起來(lái)。
例如：
select last_name as "Name",
?????????salary*12 "Annual Salary"
from employees;

4.字符串的處理
字符串的連接：使用"||"
例如：
select first_name||' '||last_name "Name" form employees;

5.iSQL-Plus

6.between and
select salary ,lname from employees where salary between 3000 and 5000
注意between是包含端點(diǎn)的，between 3000 and 5000 等價(jià)于 >=3000 and <=5000

7.通配符:
oracle中通配符是%和_,%號(hào)匹配一個(gè)或多個(gè)字母，_匹配一個(gè)
如果要匹配的表達(dá)式中本身有%和_，可以使用escape語(yǔ)句
例如要檢索JOB_ID中包含字符串SA_的記錄
select employee_id, last_name,job_id
from employees
where job_id like '%SA\_%' escape '\';

8.not and or
優(yōu)先級(jí)：not最高，其次是and，最低是or

9.排序order by
默認(rèn)是升序，如果降序需要使用desc關(guān)鍵字，可以以別名排序
select salary *12 annsal from employees order by annsal;

10.單行函數(shù)

1)字符函數(shù)
LOWER：轉(zhuǎn)換為小寫
UPPER：轉(zhuǎn)換為大寫
INITCAP：首字母變?yōu)榇髮?br />
concat('Hello', 'World')?????? HelloWord
substr('HelloWorld', 1, 5)??? Hello
substr中第二位決定從左還是從右取
instr('HelloWorld', 'W')???????? 6
LPAD(salary ,10 '*')??????????? *****24000
RPAD(salary ,10 '*')??????????? 24000*****
TRIM('H' FROM 'HelloWorld')?? elloWorld

2)數(shù)字函數(shù)
round(45.926,2)??? 45.93???????? 四舍五入
trunc(45.926,2)???? 45.92????????? 舍不入，直接截取
mod(1600,300)???? 100???????????? 取模
round(45.926, -2)? 0
round(55,926,-2)? 100

3)日期型函數(shù)
sysdate
MONTH_BETWEEN('01-SEP-95','11-JAN-94')?????????? = 19.6674194
ADD_MONTHS('11-JAN-94', 6)?????????????????????????????????? = ? '11-JUL-94'
NEXT_DAY('01-SEP-95','FRIDAY')???????????????????????????? = '08-SEP-95'
LAST_DAY('01-FEB-95')???????????????????????????????????????????????= '28-FEB-95'

Assume sysdate='25-JUL-95';
round(sysdate, 'MONTH')????????? = 01-AUG-95
round(sysdate.'YEAR')?????????????? =01-JAN-96
trunc(sysdate, 'MONTH')????????? = 01-JUL-95
trunc(sysdate.'YEAR')?????????????? =01-JAN-95


4)類型轉(zhuǎn)換
oracle中，下面是可以自動(dòng)進(jìn)行轉(zhuǎn)換的
從???????????????????????????????????????????????????????? 到
varchar2 or char??????????????????????????????? NUMBER
varchar2 or char??????????????????????????????? DATE
number????????????????????????????????????????????? varchar2
date???????????????????????????????????????????????????varchar2

顯式轉(zhuǎn)換“
TO_CHAR

日期型：
TO_CHAR(date, 'format_model')