1、基本查詢
特殊運(yùn)算符
|
運(yùn) 算
|
功 能
|
實(shí) 例
|
|
[NOT] BETWEEN…AND…
|
用于測試是否在范圍內(nèi)
|
Select * from emp Where sal between 1000 and 2000
|
|
[NOT] IN (…)
|
用于測試是否在列表中
|
Select*from emp Where job in('CLERK', 'SALESMAN','ANYLYST')
|
|
[NOT] LIKE
|
用于進(jìn)行模式匹配
|
Select * from emp Where ename like '%A%'
|
|
IS [NOT] NULL
|
用于測試是否為空值
|
Select * from emp Where comm is not null
|
|
ANY SOME
|
同列表或查詢中的每一個值進(jìn)行比較，測試是否有一個滿足，前面必須使用的運(yùn)算符包括=、!=、>=、<=、>、<等
|
Select * from emp Where sal<any(select sal from emp where deptno=10)
|
|
ALL
|
同列表或查詢中的每一個值進(jìn)行比較，測試是否所有的值都滿足，前面必須使用的運(yùn)算符包括=、!=、>=、<=、>、<等
|
Select*from emp Where sal<all(1000,1500,
2000)
|
|
[NOT] EXISTS
|
測試是否子查詢至少返回一行
|
Select?'存在雇員SCOTT' from dual where exists(select*from emp where ename='SCOTT');
|
運(yùn)算的優(yōu)先順序是NOT，AND，OR。如果要改變優(yōu)先順序，可以使用括號。
缺省中文日期格式為DD-MM月-YY，如2003年1月10日應(yīng)該表示為“10-1月-03”。
字符串和日期型數(shù)據(jù)的值是包含在單引號中的，如SALESMAN，需要用單引號引起。字符的值對大小寫敏感。
比 較 運(yùn) 算 符
|
運(yùn)算符
|
功 能
|
實(shí) 例
|
|
>,<
|
大于，小于
|
Select * from emp where sal>2000
|
|
>=.<=
|
大于等于，小于等于
|
Select * from emp where sal>=2000
|
|
=
|
等于
|
Select * from emp where deptno=10
|
|
!=,<>,^=
|
不等于
|
Select * from emp where deptno!=10
|
如果要對計算列排序，可以為計算列指定別名，然后按別名排序。
別名如果含有空格或特殊字符或大小寫敏感，需要使用雙引號將它引起來。
表頭的顯示默認(rèn)為全部大寫。對于日期和數(shù)值型數(shù)據(jù)，右對齊顯示，如deptno列。對于字符型數(shù)據(jù)，左對齊顯示，如dname列。
%：代表0個或多個任意字符。_ ：代表一個任意字符。
2、函數(shù)
數(shù)值型函數(shù)
|
函 數(shù)
|
功 能
|
實(shí) 例
|
結(jié) 果
|
|
abs
|
求絕對值函數(shù)
|
abs(−5)
|
5
|
|
sqrt
|
求平方根函數(shù)
|
sqrt(2)
|
1.41421356
|
|
power
|
求冪函數(shù)
|
power(2,3)
|
8
|
|
cos
|
求余弦三角函數(shù)
|
cos(3.14159)
|
−1
|
|
mod
|
求除法余數(shù)
|
mod(1600, 300)
|
100
|
|
ceil
|
求大于等于某數(shù)的最小整數(shù)
|
ceil(2.35)
|
3
|
|
floor
|
求小于等于某數(shù)的最大整數(shù)
|
floor(2.35)
|
2
|
|
round
|
按指定精度對十進(jìn)制數(shù)四舍五入
|
round(45.923, 1)
round(45.923, 0)
round(45.923,−1)
|
45.9
46
50
|
|
trunc
|
按指定精度截斷十進(jìn)制數(shù)
|
trunc(45.923, 1)
trunc(45.923)
trunc(45.923,−1)
|
45.9
45
40
|
字符型函數(shù)
|
函數(shù)名稱
|
功 能
|
實(shí) 例
|
結(jié) 果
|
|
ascii
|
獲得字符的ASCII碼
|
Ascii('A')
|
65
|
|
chr
|
返回與ASCII碼相應(yīng)的字符
|
Chr(65)
|
A
|
|
lower
|
將字符串轉(zhuǎn)換成小寫
|
lower ('SQL Course')
|
sql course
|
|
upper
|
將字符串轉(zhuǎn)換成大寫
|
upper('SQL Course')
|
SQL COURSE
|
|
initcap
|
將字符串轉(zhuǎn)換成每個單詞以大寫開頭
|
initcap('SQL course')
|
Sql Course
|
|
concat
|
連接兩個字符串
|
concat('SQL', ' Course')
|
SQL Course
|
|
substr
|
給出起始位置和長度，返回子字符串
|
substr('String',1,3)
|
Str
|
|
length
|
求字符串的長度
|
length('Wellcom')
|
7
|
|
instr
|
給出起始位置和出現(xiàn)的次數(shù)，求子字符串在字符串中出現(xiàn)的位置
|
instr('String', 'r',1,1)
|
3
|
|
lpad
|
用字符填充字符串左側(cè)到指定長度
|
lpad('Hi',10,'-')
|
--------Hi
|
|
rpad
|
用字符填充字符串右側(cè)到指定長度
|
rpad('Hi',10,'-')
|
Hi--------
|
|
trim
|
在一個字符串中去除另一個字符串
|
trim('S' FROM 'SSMITH')
|
MITH
|
|
replace
|
用一個字符串替換另一個字符串中的子字符串
|
replace('ABC', 'B', 'D')
|
ADC
|
SYSDATE是返回系統(tǒng)日期和時間的虛列函數(shù)。
使用日期的加減運(yùn)算，可以實(shí)現(xiàn)如下功能：
* 對日期的值加減一個天數(shù)，得到新的日期。
* 對兩個日期相減，得到相隔天數(shù)。
* 通過加小時來增加天數(shù)，24小時為一天，如12小時可以寫成12/24(或0.5)。
日期函數(shù)
|
函 數(shù)
|
功 能
|
實(shí) 例
|
結(jié) 果
|
|
months_between
|
返回兩個日期間的月數(shù)
|
months_between ('04-11月-05','11-1月-01')
|
57.7741935
|
|
add_months
|
返回把月份數(shù)加到日期上的新日期
|
add_months('06-2月-03',1)
add_months('06-2月-03',-1)
|
06-3月-03
06-1月-03
|
|
next_day
|
返回指定日期后的星期對應(yīng)的新日期
|
next_day('06-2月-03','星期一')
|
10-2月-03
|
|
last_day
|
返回指定日期所在的月的最后一天
|
last_day('06-2月-03')
|
28-2月-03
|
|
round
|
按指定格式對日期進(jìn)行四舍五入
|
round(to_date('13-2月-03'),'YEAR')
round(to_date('13-2月-03'),'MONTH')
round(to_date('13-2月-03'),'DAY')
|
01-1月-03
01-2月-03
16-2月-03
(按周四舍五入)
|
|
trunc
|
對日期按指定方式進(jìn)行截斷
|
trunc(to_date('06-2月-03'),'YEAR')
trunc(to_date('06-2月-03'),'MONTH')
trunc(to_date('06-2月-03'),'DAY')
|
01-1月-03
01-2月-03
02-2月-03
(按周截斷)
|
類型轉(zhuǎn)換函數(shù)
|
函 數(shù)
|
功 能
|
實(shí) 例
|
結(jié) 果
|
|
To_char
|
轉(zhuǎn)換成字符串類型
|
To_char(1234.5, '$9999.9')
|
$1234.5
|
|
To_date
|
轉(zhuǎn)換成日期類型
|
To_date('1980-01-01', 'yyyy-mm-dd')
|
01-1月-80
|
|
To_number
|
轉(zhuǎn)換成數(shù)值類型
|
To_number('1234.5')
|
1234.5
|
日期轉(zhuǎn)換格式字符
|
代 碼
|
代表的格式
|
例 子
|
|
AM、PM
|
上午、下午
|
08 AM
|
|
D
|
數(shù)字表示的星期(1～7)
|
1,2,3,4,5,6,7
|
|
DD
|
數(shù)字表示月中的日期(1～31)
|
1,2,3,…,31
|
|
MM
|
兩位數(shù)的月份
|
01,02,…,12
|
|
Y、YY、YYY、YYYY
|
年份的后幾位
|
3,03,003,2003
|
|
RR
|
解決Y2K問題的年度轉(zhuǎn)換
|
|
|
DY
|
簡寫的星期名
|
MON,TUE,FRI,…
|
|
DAY
|
全拼的星期名
|
MONDAY,TUESDAY,…
|
|
MON
|
簡寫的月份名
|
JAN,FEB,MAR,…
|
|
MONTH
|
全拼的月份名
|
JANUARY,FEBRUARY,…
|
|
HH、HH12
|
12小時制的小時(1～12)
|
1,2,3,…,12
|
|
HH24
|
24小時制的小時(0～23)
|
0,1,2,…,23
|
|
MI
|
分(0～59)
|
0,1,2,…,59
|
|
SS
|
秒(0～59)
|
0,1,2,…,59
|
|
,./-;:
|
原樣顯示的標(biāo)點(diǎn)符號
|
|
|
'TEXT'
|
引號中的文本原樣顯示
|
TEXT
|
查詢中插入中文的年月日，其中原樣顯示部分區(qū)別于外層的單引號，需要用雙引號引起。
SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual;
執(zhí)行結(jié)果為：
TO_CHAR(SYSDAT
-------------------------
2003年11月18日
說明：雙引號中的中文字“年”、“月”、“日”原樣顯示，單引號為字符串的界定標(biāo)記，區(qū)別于雙引號，不能混淆。
對于數(shù)字型的日期格式，可以用數(shù)字或全拼格式顯示，即在格式字符后面添加TH或SP。TH代表序列，SP代表全拼。
SELECT SYSDATE,to_char(SYSDATE,'yyyysp'),to_char(SYSDATE,'mmspth'),
to_char(SYSDATE,'ddth') FROM dual;
執(zhí)行結(jié)果為：
SYSDATE TO_CHAR(SYSDATE,'YYYYSP') TO_CHAR( TO_C
------------- -------------------------------------------------------------- --------------- --------
07-2月 -04 two thousand four second 07th
說明：“two thousand four”為全拼表示的2004年；“second”為全拼序列表示的2月；“07th”為用序列表示的7號。
數(shù)值轉(zhuǎn)換符
|
代 碼
|
代表的格式
|
例 子
|
|
9
|
代表一位數(shù)字，如果是正數(shù)，前面是空格，如果是負(fù)數(shù)，前面是-號
|
9999
|
|
0
|
代表一位數(shù)字，在相應(yīng)的位置上如果沒有數(shù)字則出現(xiàn)0
|
0000
|
|
,
|
逗號，用作組分隔符
|
99,999
|
|
.
|
小數(shù)點(diǎn)，分隔整數(shù)和小數(shù)
|
999.9
|
|
$
|
$貨幣符號
|
$999.9
|
|
L
|
本地貨幣符號
|
L999.99
|
|
FM
|
去掉前后的空格
|
FM999.99
|
|
EEEE
|
科學(xué)計數(shù)法
|
9.9EEEE
|
|
S
|
負(fù)數(shù)符號−放在開頭
|
S999.9
|
如果實(shí)際位數(shù)超過5位，則會填充為#號。
SQL> select to_char(1212121.2121,'99.99') from dual;
TO_CHA
------
######
其他常用函數(shù)
|
函 數(shù)
|
功 能
|
實(shí) 例
|
結(jié) 果
|
|
nvl
|
空值轉(zhuǎn)換函數(shù)
|
nvl(null, '空')
|
空
|
|
decode
|
實(shí)現(xiàn)分支功能
|
decode(1,1, '男',?2, '女')
|
男
|
|
userenv
|
返回環(huán)境信息
|
userenv('LANGUAGE')
|
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
|
|
greatest
|
返回參數(shù)的最大值
|
greatest(20,35,18,9)
|
35
|
|
least
|
返回參數(shù)的最小值
|
least(20,35,18,9)
|
9
|
在ASCII碼表中，排在后邊的字符大，小寫字母排在大寫字母之后。字符串的比較原則是，先比較第一位，如果相同，則繼續(xù)比較第二位，依此類推，直到出現(xiàn)大小關(guān)系。
常用的組函數(shù)
|
函 數(shù)
|
說 明
|
|
AVG
|
求平均值
|
|
COUNT
|
求計數(shù)值，返回非空行數(shù)，*表示返回所有行
|
|
MAX
|
求最大值
|
|
MIN
|
求最小值
|
|
SUM
|
求和
|
|
STDDEV
|
求標(biāo)準(zhǔn)偏差，是根據(jù)差的平方根得到的
|
|
VARIANCE
|
求統(tǒng)計方差
|
分組函數(shù)中SUM和AVG只應(yīng)用于數(shù)值型的列，MAX、MIN和COUNT可以應(yīng)用于字符、數(shù)值和日期類型的列。組函數(shù)忽略列的空值。
使用GROUP BY 從句可以對數(shù)據(jù)進(jìn)行分組。所謂分組，就是按照列的相同內(nèi)容，將記錄劃分成組，對組可以應(yīng)用組函數(shù)。
如果不使用分組，將對整個表或滿足條件的記錄應(yīng)用組函數(shù)。
在組函數(shù)中可使用DISTINCT或ALL關(guān)鍵字。ALL表示對所有非NULL值(可重復(fù))進(jìn)行運(yùn)算(COUNT除外)。DISTINCT 表示對每一個非NULL值，如果存在重復(fù)值，則組函數(shù)只運(yùn)算一次。如果不指明上述關(guān)鍵字，默認(rèn)為ALL。
在查詢列中，不能使用分組列以外的其他列，否則會產(chǎn)生錯誤信息。
HAVING從句過濾分組后的結(jié)果，它只能出現(xiàn)在GROUP BY從句之后，而WHERE從句要出現(xiàn)在GROUP BY從句之前。
HAVING從句的限定條件中要出現(xiàn)組函數(shù)。如果同時使用WHERE條件，則WHERE條件在分組之前執(zhí)行，HAVING條件在分組后執(zhí)行。
子查詢一般出現(xiàn)在SELECT語句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出現(xiàn)子查詢。子查詢比主查詢先執(zhí)行，結(jié)果作為主查詢的條件，在書寫上要用圓括號擴(kuò)起來，并放在比較運(yùn)算符的右側(cè)。子查詢可以嵌套使用，最里層的查詢最先執(zhí)行。子查詢可以在SELECT、INSERT、UPDATE、DELETE等語句中使用。
多列子查詢
如果子查詢返回多列，則對應(yīng)的比較條件中也應(yīng)該出現(xiàn)多列，這種查詢稱為多列子查詢。以下是多列子查詢的訓(xùn)練實(shí)例。
查詢職務(wù)和部門與SCOTT相同的雇員的信息。
執(zhí)行以下查詢：
SELECT empno, ename, sal FROM emp
WHERE (job,deptno) =(SELECT job,deptno FROM emp WHERE empno=7788);
集合運(yùn)算操作
|
操 作
|
描 述
|
|
UNION
|
并集，合并兩個操作的結(jié)果，去掉重復(fù)的部分
|
|
UNION ALL
|
并集，合并兩個操作的結(jié)果，保留重復(fù)的部分
|
|
MINUS
|
差集，從前面的操作結(jié)果中去掉與后面操作結(jié)果相同的部分
|
|
INTERSECT
|
交集，取兩個操作結(jié)果中相同的部分
|
查詢部門10和部門20的所有職務(wù)。
執(zhí)行以下查詢：
SELECT job FROM emp WHERE deptno=10
UNION（UNION ALL" MINUS" MINUS" INTERSECT）
SELECT job FROM emp WHERE deptno=20;