SQL 基礎之單行函數（七）

單行 SQL 函數有如下幾個分類：

字元函數

數字函數

使用日期

日期函數

函數的使用原來說白了就是在函數使用過程中加入符合定義的參數，然後執行函數就能輸出結果值了。

單行函數和多行函數的差別：

單行函數每行輸出為一個結果

多行函數每組行輸出為一個結果

單行函數：

操作資料對象

接受函數傳回一個結果

隻對一行進行變換

每行傳回一個結果

可以轉換資料類型,計算,格式化

可以嵌套

參數可以是列、值或表達式

function_name [(arg1, arg2,...)]

字元函數：

1、大小寫轉換函數：LOWER、UPPER、INITCAP

這些函數轉換字元串的大小寫：

函數

結果

LOWER('SQL Course')

結果全部是小寫

UPPER('SQL Course')

結果全部是大寫

INITCAP('SQL Course')

結果首字元大寫

1.1、例如顯示員工Walsh的員工編号，并且名字為小寫：

select lower(last_name),employee_id from employees where last_name='Walsh';

1.2、例如顯示員工Walsh的工資，并且名字為大寫：

select upper(first_name),salary from employees where first_name='Kevin';

1.3、顯示Kevin的last_name 、工資job_id 首字母大寫

select initcap(job_id), last_name,salary from employees where first_name='Kevin';

1.4、從員工表中檢索FIRST_NAME和LAST_NAME值，并且FIRST_NAME包含字元串“li”

select first_name,last_name from employees where lower(first_name) like '%li%';

2、字元處理函數：CONCAT、SUBSTR、LENGTH、INSTR、LPAD | RPAD、TRIM、REPLACE

名字

函數

結果

連接配接函數

CONCAT('Hello', 'World')

HelloWorld

字元截取函數

SUBSTR('HelloWorld',1,5)截取從1-5個字元

Hello

字元串統計長度

LENGTH('HelloWorld')

查找字元位置函數

INSTR('HelloWorld', 'W')

前填充函數

LPAD(salary,10,'*')

*****24000

後填充函數

RPAD(salary, 10, '*')

24000*****

替換函數

REPLACE ('JACK and JUE','J','BL')

BLACK and BLUE

字元剪切函數

TRIM('H' FROM 'HelloWorld')

elloWorld

2.1、将員工的first_name和last_name拼接在一起顯示，并查找工資大于5000的員工資訊

select concat(first_name,last_name) name, employee_id,salary from employees where salary>5000 order by salary desc;

2.1.1、單獨制造一個列資訊，顯示為****** infor的顯示員工工資大于10000的資訊

select concat('*******','information') infor , job_id ,salary from employees where salary > 10000;

2.1.2、由下圖可知，concat函數隻能拼接兩列字元資訊，到第三列就開始報錯了

2.2.1、截取員工名字前3個字母，并找到工資大于10000的員工名字

select substr(first_name,1,3) fname, salary from employees where salary >10000;

2.2.2、檢視工資大于10000員工的名字長度

select length(last_name),salary from employees where salary >10000;

2.3.1、查找Ellen使用者 l在第幾個位置。

select instr(first_name,'l'),salary from employees where first_name='Ellen';

2.3.2、查找job_id 從第4位截取為REP的。

select instr(first_name,'a'),salary,job_id from employees where substr(job_id,'4') ='REP';

2.4.1、将工資設定為5位長度，不夠的用*号前填充

select first_name,lpad(salary,5,'*') from employees;

2.5.1、查找工資顯示10位，不足的用*号後填充

select first_name,rpad(salary,10,'*') from employees;

2.6.1、将員工名字開頭為B的變成JC開頭。

select replace(first_name,'B','JC'),salary from employees order by first_name;

2.7.1、将員工姓名首字母為A的，去掉A

select trim('A' from first_name),salary from employees order by first_name;

2.8 檢視job_id第四位開始是REP的，檢視first_name a在第幾位置

select employee_id,concat(first_name,last_name) Name,job_id,length(first_name),instr(first_name,'a') "contains 'a'?"

from employees where substr(job_id,4) = 'REP';

2.9、公司計劃給每名員工印制含有員工姓名的紀念品，為保持整體美觀程度，現在要求員工的全名在

16個字元内。當員工的FIRST_NAME和LAST_NAME的合并長度超過15個字元時，顯示員工學名，。員

工的名稱由FIRST_NAME的首字母和LAST_NAME的前14個字元組成。

【解題思路】首先确定where條件限制FIRST_NAME和LAST_NAME的合并長度超過15的員工進行過

濾，然後在SELECT 子句後面使用字元函數進行處理。

select first_name,last_name,substr(first_name,1,1)||' '||substr(last_name,1,14) format_name from employees where length(first_name)+length(last_name) >15;

數值函數

示例

ROUND：四舍五入到到指定的十進制值

ROUND(45.926, 2)

45.93

TRUNC：将數字截尾取整

TRUNC(45.926, 2)

45.92

MOD：傳回餘數

MOD(1600, 300)

100

這裡使用DUAL來測試，這是一個‘僞表’，可以用來測試函數和表達式

1、測試round函數三種方式

select round(45.9234,2),round(45.923,0),round(45.923,-1) from dual;

select round(45.9259,2),round(45.623,0),round(45.923,-1) from dual;

通過上面兩個語句測試可知：

第一個為在小數點右側截取2位，如果第三位是5就四舍五入

第二個為标準小數點右側第一位進行四舍五入

第三個為小數點左側進行四舍五入，不夠進變成0，比如round(43.923,-1)，結果變成40

2、測試trunc函數三種方式

SELECT TRUNC(45.923,2), TRUNC(45.923),TRUNC(45.923,-1) FROM DUAL;

SELECT TRUNC(45.923,2), TRUNC(45.923),TRUNC(39.923,-1) FROM DUAL;

SELECT TRUNC(45.923,2), TRUNC(45.923),TRUNC(39.923,-2) FROM DUAL;

指令分别為指定截取位數2位，預設截取小數點位右側删除掉，删除掉小數點左側第一位變成0

當為-3的時候就表示截取3位，直接變成0

3、測試mod函數查詢銷售部門的工資與5000取餘數

select last_name,salary,mod(salary,5000) from employees where job_id='SA_REP';

Oracle内部使用數字存儲日期: 世紀,年,月,日,小時,分鐘,秒

預設的日期顯示格式為DD-MON-RR

– 可以隻指定年的後兩位，允許存儲21世紀日期在21世紀中

– 同樣，允許存儲20世紀的日期在21世紀中

RR 日期格式

目前年

指定日期

RR格式

YY格式

1995

27-OCT-95

27-OCT-17

2017

1917

2001

2095

使用 SYSDATE 函數

SYSDATE 函數傳回：

日期

時間

select sysdate from dual;

日期的算術運算

在日期上加上或減去一個數字結果仍為日期。

兩個日期相減傳回日期之間相差的天數。

可以用小時數除以24，可以加小時到日期上。

1、查詢90部門的員工都入職多少個星期了

select last_name,(sysdate-hire_date)/7 as weeks from employees where department_id=90;

2、查詢30部門的員工都入職多少個月了

select first_name,(sysdate-hire_date)/30 month from employees where department_id=30;

日期操作函數

含義

MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')

兩個日期相差的月數

19.6774194

ADD_MONTHS ('06-MAR-17',1)

向指定日期中加上若幹月數

06-APR-17

NEXT_DAY ('01-SEP-95','FRIDAY')

指定日期的下一個日期

08-SEP-95

LAST_DAY ('01-SEP-95')

本月的最後一天

30-SEP-95

ROUND (SYSDATE,'MONTH')

日期四舍五入

01-MAR-17

ROUND (SYSDATE ,'YEAR')

01-JAN-17

TRUNC (SYSDATE ,'MONTH')

日期截斷

TRUNC (SYSDATE ,'YEAR')

感覺上面這幾個不太對，現在還沒明白，回頭再說

1、查詢截至01-JAN-2012已入職100月的員工資訊包含EMPLOYEES,LAST_NAME和HIRE_DATE字段

select last_name,hire_date,months_between('01-JAN-2012',hire_date) day from employees where months_between('01-JAN-2012',hire_date) >100;

本文轉自 yuri_cto 51CTO部落格，原文連結：http://blog.51cto.com/laobaiv1/1903488，如需轉載請自行聯系原作者

SQL 基礎之單行函數（七）

繼續閱讀

壓縮編碼M-JPEG、MPEG4、H.264

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

轉詳解C#資料庫存取圖檔三大方式

Oracle的基本操作

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離