SQL函式

SQL函式分單行函式、多行函式(或叫組函式)。

一、字元函式

lower 轉小寫

upper 轉大寫

initcap 首字母大寫，其餘小寫，以單詞為單位

initcap('we-are-chinese') We Are Chinese

concat 兩個字串的連線， 多字串時可巢狀呼叫或直接用“||”

length 計算字串長度，無論單、雙位元組字元或數字

length('12345') 5

length('中國人') 3

length(123.45) 6

lpad 格式化字串，左填充(字串，寬度，填充符)，填充符預設為空格填充，字串寬度已經超出指定寬度時變為左擷取，擷取到指定寬度

lpad('12345',10, '0') 0000012345

lpad('12345',10) ' 12345'

lpad('12345',4,'0') 1234

lpad('12345',4) 1234

rpad 右填充，字串寬度已經超出指定寬度時變為左擷取，擷取到指定寬度

rpad('12345',10, '0') 1234500000

rpad('12345',4,'0') 1234

rpad('12345',4) 1234

該函式可以隱士轉換，如果不是字串

select lpad(sal,10,'*') from emp where empno=7934;

ltrim 刪除左側空格

rtrim 刪除右側空格

trim 刪除左右兩側空格

trim('*' from '***hello***') 刪除頭尾字元，或trim(both '*' from '***hello***') hello

trim (leading '*' from '***hello***') 刪除頭部字元 hello***

trim(trailing '*' from '***hello***') 刪除尾部字元 ***hello

trim中只能指定單個要刪除的字元，不能是字串

instr 獲取字串出現的位置，從指定位置開始，出現的第幾次，最後兩個引數預設為1，找不到時返回0

instr('HelloWorld','W') 6

instr('HelloWorld','W',1) 6

instr('HelloWorld','W',1,2) 0

substr 擷取字串，起始位置為1，為負時從反向位置向右擷取

substr('HelloWorld', 6) World

substr('HelloWorld',1,5) Hello

substr('HelloWorld',-5,5) World

replace 字串替換，沒有替換串時用空字元替換，也就是刪除查詢串，未找到被替換串時原樣返回

replace('jack and jue','j','bl') black and blue

replace('a#b#c','#') abc

replace函式也可以處理數字和日期資料，如

select replace(10000-3,'9','85') from dual; 8585857

select replace(sysdate, 'NOV','DEC') from dual; 25-DEC-16

translate 字串翻譯

translate('+-*/','1+2-3*4/','fajbocld') abcd

常用於去掉字串中的字母或數字

select translate('12fge3k4mab5z','#abcdefghijklmnopqrstuvwxyz',' ') from dual 12345

translate('12ab3c4de5f','#1234567890',' ') abcdef

其中單引號中為空格，不能為空，否則有NULL值做引數的表示式將直接返回NULL，

#號用於剔除跟第一個字元a或1符合的字元的翻譯，否則翻譯結果會多個空格。

reverse 反轉字串，一般只用於反轉單位元組字元，多位元組字元是亂碼

reverse('1234') 4321

wm_concat 欄位合併

create table t(a number, b varchar2(20));

insert into t values(1, 'a');

insert into t values(2, 'c');

insert into t values(3, 'b');

insert into t values(2, 'a');

insert into t values(1, 'd');

insert into t values(3, 'e');

col merge for a20

select a, wm_concat(b) merge from t group by a;

A MERGE

---------- --------------------

1 a,d

2 c,a

3 b,e

select a, replace(wm_concat(b), ',', '|') merge from t group by a;

A MERGE

---------- --------------------

1 a|d

2 c|a

3 b|e

regexp_substr 欄位拆分

select regexp_substr('a;b;c;d', '[^;]+', 1, level), level from dual connect by level < 5;

REGEXP_SUBSTR( LEVEL

-------------- ----------

a 1

b 2

c 3

d 4

二、數值函式

round 四捨五入

round(1601.916,1) 1601.9

round(1601.916,2) 1601.92

round(1601.916,-3) 2000

round(1601.916) 1602

trunc 截斷，不四捨五入，小數部分按指定位數截斷，整數部分的截斷不改變整數位數

trunc(1601.916,1) 1601.9

trunc(1601.916,2) 1601.91

trunc(1601.916,-3) 1000

trunc(160,-1) 160

trunc(1601.916) 1601

mod 模運算，即取餘數，mod(被除數，除數)，常用於區分奇數和偶數，除數可以為0

mod(5,2) 1

mod(5,0) 5

mod(3,5) 3

mod(5.2,2) 1.2

ceil 向上取整，取大值整數

ceil(23.45) 24

ceil(-23.45) -23

floor 向下取整，取小值整數

floor(23.45) 23

floor(-23.45) -24

sign 取正負性

sign(-10) -1

sign(10) 1

sign(0) 0

abs 取絕對值

sqrt 求平方根，引數不能為負

power 乘方運算，power(底數，指數)

greatest 求多個值中的最大值

least 求多個值中的最小值

三、日期函式

YY格式

與系統日期處於同一個世紀

RR格式

接近系統日期的世紀

預設是RR格式

DD-MM-RR

nls引數的設定：國家語言支援

alter session set nls_language=english;

修改會話日期時間格式顯示：

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

alter session set nls_date_format='yyyy-mm-dd hh:mi:ss am';

sysdate 系統時間

sessiontimezone 當前會話時區

current_date 當前會話時區的當前時間

systimestamp 系統時間戳

current_timestamp 當前會話時區的當前時間戳

日期的加減計算

一個日期加一個數字表示增加天數後的新日期

兩個日期相減表示相差的天數

有兩個日期資料START_DATE，END_DATE，欲得到這兩個日期的時間差(以天，小時，分鐘，秒，毫秒)：?

天：?

ROUND(TO_NUMBER(END_DATE - START_DATE))?

小時：?

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)?

分鐘：?

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)?

秒：?

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)?

毫秒：?

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 60)

months_between 兩個日期相差的月份數，離現在近的日期寫在前面

add_months 加幾個月後的日期，數字為負時反向推算

當日期為最後一天時，返回下一個月的最後一天，超出月份的最後一天時也返回最後一天

select add_months(to_date('2013-11-30','yyyy-mm-dd'), 1) from dual; 返回2013-12-31 00:00:00

select add_months(to_date('2013-1-31','yyyy-mm-dd'), 1) from dual; 返回2013-02-28 00:00:00

select add_months(to_date('2013-1-30','yyyy-mm-dd'), 1) from dual; 返回2013-02-28 00:00:00

函式中日期會隱式轉換

select add_months('2013-1-30', 1) from dual; 返回2013-02-28 00:00:00

next_day 下個星期的那個日期，從星期日開始為1

select next_day(sysdate,2) from dual; 返回下個星期一的日期

last_day 指定日期所在月的最後一天

select last_day(to_date('2012-2-11','yyyy-mm-dd')) from dual; 返回2012-02-29 00:00:00

函式中日期會隱式轉換

select last_day('2012-2-11') from dual; 返回2012-02-29 00:00:00

返回下個月的第一個星期一

select next_day(last_day(sysdate),'mon') from dual;

日期的四捨五入和截斷

round 按年yy、月mm、日dd、時hh、分mi

select round(to_date('2013-11-22','yyyy-mm-dd'),'mm') from dual; 舍入到月 2013-12-01 00:00:00

select round(to_date('2013-11-12','yyyy-mm-dd'),'mm') from dual; 舍入到月 2013-11-01 00:00:00

select round(to_date('2013-11-12 21:29:35','yyyy-mm-dd hh24:mi:ss'),'dd') from dual; 舍入到日 2013-11-13 00:00:00

trunc 按年、月、日截斷

select trunc(to_date('2013-11-12 21:29:35','yyyy-mm-dd hh24:mi:ss'),'dd') from dual; 截斷到日 2013-11-12 00:00:00

extract 提取日期中的某個域，extract(域名 from 日期)

extract(month from sysdate) 11

只能提取年、月、日，不能從日期型中提取小時、分鐘、秒

可提取到零時區的時間域extract(hour from systimestamp)，實際結果還要加上時區sessiontimezone，北京時間為東八區，即要+8

四、轉換函式和格式串

chr 將ASCII碼轉換為字元，與ascii互為反函式

to_char 將數字格式化為字串，to_char(原數值，格式串，NLS引數)

0 有數字填充數字，沒有強制填充0

9 整數部分有數字填充數字，沒有不填充，小數部分沒有填充0

. 小數點的位置

D 小數分隔符的位置，預設為句點(D和G配合使用，不能D和,或者.和G混合使用)

, 逗號千分符的位置

G 組分隔符的位置，預設為逗號

FM 遮蔽掉沒有必要的空格和0

$ 顯示美元符號

L 顯示系統環境的本地貨幣符號，如人民幣符號“￥”

C 顯示系統環境的本地貨幣種類符號，如中國元“CNY”，“C”不能與“L”或“$”並用

X 轉換為十六進位制顯示，用大寫字母，x表示用小寫字母，如to_char(255,'XXX')返回FF

MI 表示負數的減號的位置

PR 包圍在括號內的負數

EEEE 科學計數法

V 乘以10的n次方，n是V之後9的數量

S 前面加上+ 或-

to_number 將字串格式化為數字，to_number(字串，格式串，NLS引數)

掩碼格式串類似to_char函式

to_number('$1,000.55', '$999,999.00') 1000.55

進位制轉換

to_number('ff', 'xxx') 255

to_char 將日期格式格式化為字串，to_char(日期，格式串，NLS引數)

YEAR、Year、year 區分大小寫的年份全拼

yyyy 四位年

yy 兩位年

RR 兩位已知世紀年

q 季度

mm 兩位月份

MONTH、Month、month 區分大小寫的月份全拼

MON、Mon、mon 區分大小寫的三字母月份縮寫

ww 一年中的第幾周

w 一月中的第幾周

d 一週中的第幾天，週日為一週中的第一天

dd 一月中的第幾天

ddd 一年中的第幾天

DAY、Day、day 區分大小寫的星期全拼

DY、Dy、dy 區分大小寫的三字母星期縮寫

th 後面帶上th表示第幾天

sp 第幾天的英文全拼

AM、PM、A.M.、P.M. 指示上下午

hh、hh12 12時制小時

hh24 24時制小時

mi 分鐘

ss 秒

sssss 午夜之後的秒

ff 毫秒

以下是兩個日期表達的例子

當天日期的全拼表示

select to_char(sysdate, 'fmDdthsp "of" Month, Year') from dual;

TO_CHAR(SYSDATE,'FMDDTHSP"OF"MONTH,YEAR')

--------------------------------------------------------------------

Seventh of 9月, Twenty Seventeen

下個月的第一個星期一

select to_char(next_day(last_day(sysdate), 2), 'dd "is the first Monday for" fmmonth rrrr') from dual;

TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),2),

--------------------------------------

02 is the first Monday for 10月 2017

顯示NLS會話引數的檢視：nls_session_parameters

show parameter nls_territory;

修改地區

alter session set nls_territory=america;

修改日期語言

alter session set nls_date_language=american;

alter session set nls_date_language='simplified chinese';

語句級別的NLS控制

select to_char(sysdate, 'mm-dd-yyyy hh:mi:ss am', 'nls_date_language = american') from dual;

select to_char(sysdate, 'mm-dd-yyyy hh:mi:ss am', 'nls_date_language = japanese') from dual;

select to_char(sysdate, 'mm-dd-yyyy hh:mi:ss am', 'nls_date_language = ''simplified chinese''') from dual;

注意第三行語句中需要追加兩個單引號

to_date 將字串格式化為日期，to_date(字串，格式串，NLS引數)

掩碼格式串類似to_char函式

fx 類似於to_char函式使用的fm修飾符，指定該修飾符時，不完全匹配格式掩碼的字元項會產生錯誤。

應避免資料型別的隱式轉換，出現隱式轉換會不使用索引

強制資料型別轉換

cast(原資料 as 新的資料型別)

利用原表重建一個新表，並更改列的資料型別

create table salary2 as

select cast(s.salary_id as varchar2(20)) salary_id,

cast(employee_id as varchar2(20)) employee_id,

cast(salary as varchar2(20)) salary

from salary s;

五、條件函式

nvl(original, ifnull)，判斷原值original是否為空，為空返回ifnull的值，不為空原樣返回

ifnull資料型別應與original一致，否則要求能向original轉換，函式返回型別與original一致

如

select nvl(1, 'a') from dual; 錯誤

select nvl('a', 1) from dual; 正確

另外注意該函式的合理使用，如下查詢不能返回'00000'

select nvl(dummy, '00000') from dual where 1=2;

改為如下形式則能返回'00000'

select nvl(max(dummy), '00000') from dual where 1=2;

nvl2(original, ifnotnull, ifnull) 不為空返回ifnotnull的值，為空返回ifnull的值

ifnull資料型別應與ifnotnull一致，否則要向ifnotnull轉換，不能是long型別，函式返回型別與ifnotnull一致

nvl和nvl2的返回位置概念是反的

nullif(expr1, expr2) 相等返回空，否則返回第一個表示式值

coalesce(expr1, expr2, ...) 返回第一個非空的表示式，否則返回空值

所有非空引數的資料型別必須與第一個非空引數的資料型別一致

decode函式

decode(expr, 值1, 結果1,

值2, 結果2,

...

否則結果)

decode函式認為兩個空值是相等的

後面各個值的資料型別應當與值1的資料型別一致，否則要求能向值1轉換

函式返回的資料型別轉換到與結果1一致

如

select decode(sysdate, null, 1, 'a') from dual; 錯誤

select decode(sysdate, null, 'a', 1) from dual; 正確

函式也可以只包含三個引數，當不滿足值1時返回空的結果

如以下查詢如果日期不符合將返回空

select decode(to_char(sysdate, 'yyyy-mm-dd'), '2017-08-15', 'b') from dual;

case語句

case expr when 條件1 then 結果1

when 條件2 then 結果2

else 否則結果

end

引數的資料型別必須一致

六、分組函式(聚合函式)

avg 分組求平均值

sum 分組求和

min 分組最小值

max 分組最大值

count 分組計數

count(*)、count(1) 未指定列，返回記錄數量包含重複項和空值

count(列名) 指定了列，返回記錄數量包含重複項，但排除空值

count(distinct 列名) 返回記錄數量不包含重複項和空值

組函式的計算忽略空值

group by 分組顯示

查詢中包含正常列和分組函式的列時，需要對正常列使用group by子句，否則報錯。

group by的表示式內容必須與select中的表示式保持一致，如下表達不一致是錯誤的

select to_char(hiredate, 'rr'), avg(sal) from emp group by to_char(hiredate, 'yyyy');

並且group by的表示式不可以用列的別名，如下是錯誤的

select to_char(hiredate, 'rr') year, avg(sal) from emp group by year;

巢狀分組函式需要group by子句，如以下是錯誤的

select max(count(*)) from emp;

但加入group by後可以執行，雖然這裡的巢狀分組並沒有意義

select max(count(*)) from emp group by 1;

group by支援多分組，以下是兩種多分組模式rollup和cube

rollup(a, b, c)的分組模式為(a,b,c)，(a,b)，(a)，(全表)

select department_id, job_id, sum(salary)

from employees

group by rollup(department_id, job_id);

cube(a, b, c)的分組模式為(a,b,c)，(a,b)，(a,c)，(b,c)，(a)，(b)，(c)，(全表)

select department_id, job_id, sum(salary)

from employees

group by cube(department_id, job_id);

having 分組後的篩選

篩選條件不一定需要存在於select的列表示式中，如以下查詢是可以的

select deptno from emp where comm is null group by deptno having count(*)>2;

組函式不能用於where子句中，如果要用，必需放在having子句中。但組函式可以在where子句的子查詢中構建。

七、SQL函式的幾個練習

以下將日期“19-Mar-2001”表示為“Nineteenth of March 2001 12:00:00 AM”的形式

alter session set nls_date_language=american;

alter session set nls_date_format='DD-Mon-YYYY';

select to_char(to_date('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') new_date from dual;

或

select to_char(to_date('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdthsp "of" Month YYYY fmHH:MI:SS AM') new_date from dual;

以下將日期“11-Mar-2007”表示為“Eleventh of October, Two Thousand Seven”的形式：

select to_char(to_date('11-oct-2007', 'DD-Mon-YYYY'), 'fmDdthsp "of" Month, Year') new_date from dual;

此處如果沒有sp：

select to_char(to_date('11-oct-2007', 'DD-Mon-YYYY'), 'fmDdth "of" Month, Year') new_date from dual;

則查詢結果為：

11th of October, Two Thousand Seven

查詢hr使用者的employees表

顯示幹了多少年零多少個月

LAST_NAME YEARS MONTHS 按年和月降序

select last_name,

trunc(months_between(sysdate, hire_date) / 12) years,

trunc(mod(months_between(sysdate, hire_date), 12)) month

from employees

order by years desc, months desc;

查詢scott使用者下的emp表

顯示總人數和入職日期為以下各年的人數

total 1980 1981 1982 1987

select count(*) total,

sum(decode(to_char(hiredate, 'yyyy'), '1980', 1, 0)) "1980",

sum(decode(to_char(hiredate, 'yyyy'), '1981', 1, 0)) "1981",

sum(decode(to_char(hiredate, 'yyyy'), '1982', 1, 0)) "1982",

sum(decode(to_char(hiredate, 'yyyy'), '1987', 1, 0)) "1987"

from emp;

將一列的查詢分開在兩列裡顯示

select a.id, b.id from t1 a, t1 b where a.id = b.id - 5;

select a.id, b.id from t1 a, t1 b where a.id + (select count(*) from t1) / 2 = b.id;

select a.id, b.id

from t1 a, t1 b

where a.id = b.id(+) - round((select count(*) from t1) / 2)

and a.id <= round((select count(*) from t1) / 2);

查員工工資在其所在部門平均工資之上的員工，列出其所在部門及該部門的平均工資

select a.last_name, a.salary, a.department_id, round(b.salavg, 2)

from employees a,

(select avg(t1.salary) salavg, t2.department_id depid

from employees t1, departments t2

where t1.department_id = t2.department_id

group by t2.department_id) b

where a.department_id = b.depid

and a.salary > b.salavg;