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