MySQL函數(shù)概述?

MySQL提供了很多功能強大狰闪、方便易用的函數(shù)，在進行數(shù)據(jù)庫管理以及數(shù)據(jù)的查詢和操作時，幫助我們提高對數(shù)據(jù)庫的管理效率

?單行函數(shù)語法

–語法:

函數(shù)名[(參數(shù)1，參數(shù)2,…)]

–其中的參數(shù)可以是以下之一：

?變量

?列名

?表達式

?單行函數(shù)特征

–單行函數(shù)對單行操作

–每行返回一個結(jié)果

–有可能返回值與原參數(shù)數(shù)據(jù)類型不一致

–單行函數(shù)可以寫在SELECT筹误、WHERE、ORDER

BY子句中

–有些函數(shù)沒有參數(shù)癣缅，有些函數(shù)包括一個或多個參數(shù)

–函數(shù)可以嵌套

?常用函數(shù)分類

–數(shù)學函數(shù)

–字符串函數(shù)

–日期和時間函數(shù)

–流程控制函數(shù)

–其他函數(shù)

數(shù)學函數(shù)（線上）

?CEIL(x)厨剪、CEILING(x)：（天花板）向上到整

select CEIL(-3.5),ceil(3.5)

from dual;

select CEIL(-3.5),ceil(3.5)?

?FLOOR(x)：（地板）向下取整

?ROUND(x,y):四舍五入到第y位

select round(-1.23),round(-1.523),round(1.23),round(1.51)

select round(-1.234,2),round(-1.235,2),round(1.234,2)

round函數(shù)如果只輸入一個參數(shù)，四舍五入保留整數(shù)部分

round（x,y）如果是兩個參數(shù)友存，表示四舍五入精確到小數(shù)點后第y位

兩種方式都可以祷膳，mysql這個函數(shù)可以省略from,oracle不可省略

?TRUNCATE(x,y):截取到小數(shù)點后y位

select TRUNCATE(-1.234,2),TRUNCATE(-1.235,2)

## 練習1 ##

-- ?1.寫一個查詢，分別計算100.456四舍五入到小數(shù)點后第2位屡立，第1位钾唬，整數(shù)位的值。

SELECT ROUND(100.456,2)

,ROUND(100.456,1)

,ROUND(100.456)

-- ?2.寫一個查詢侠驯，分別計算100.456從小數(shù)點后第2位抡秆，第1位，整數(shù)位截斷的值吟策。

SELECT TRUNCATE(100.456,2),

TRUNCATE(100.456,1),

TRUNCATE(100.456,0);

注意：ROUND（x,y）如果保留整數(shù)時儒士，y可以省略，TRUNCATE（x,y）保留整數(shù)時檩坚，y不可省略着撩，寫成(x,0)的形式

?SIGN(x)：返回參數(shù)的符號；?

x>0? 返回1

x=0 返回0

x<0 返回-1

select sign(-2.3333),sign(222222),sign(0);分別返回-1匾委，1拖叙，0

-- 生成≥a且≤b的隨機數(shù)

-- x=a

-- y=(b-a)+1

select floor(x+rand()*y);

1

-- 例如生成≥2且≤10的隨機數(shù)

select rand()*9;

select floor(2+rand()*9);

字符串函數(shù)（線上）

?CHAR_LENGTH(str)：返回字符個數(shù)

無論何種編碼方式，1個漢字=1個字符

?LENGTH(str):返回字節(jié)個數(shù)

英文1=1? ?中文1=2赂乐，3薯鳍，4

常用中文字符用utf-8編碼占用3個字節(jié)（大約2萬多字），但超大字符集中的更大多數(shù)漢字要占4個字節(jié)（在unicode編碼體系中挨措，U+20000開始有5萬多漢字）挖滤。

GBK、GB2312收編的漢字占2個字節(jié)浅役，嚴格地用iso8859-1無法表示漢字斩松，只能轉(zhuǎn)為問號。

?CONCAT簡單字符串拼接

select CONCAT("我是","彭一峰")

?CONCAT_WS(x,s1,s2,…sn):用x做分隔符觉既，把s1到sn拼接起來

select CONCAT_WS(";","彭一峰","王振生","張興業(yè)","寒")? ? ? ---彭一峰;王振生;張興業(yè);寒

??INSERT(str,pos,len,newstr)? 替換字符串的函數(shù)：str從第pos位置開始的len個字符惧盹，用newstr代替

select INSERT("我是王秋菊",3,3,"王振生")

-- 我是王振生

select INSERT("我是王秋菊",3,1,"王振生")

-- 我是王振生秋菊

?left從左側(cè)截取字符串到n個長度

select LEFT("我是王秋菊",2)? ? --我是

select LEFT("ent",2)? ? ? --en

?right從右側(cè)截取字符串到n個長度

select right("我是王秋菊",2)

select right("ent",2)

??LPAD(str,len,padstr)

-- 把字符串str擴充到len長度乳幸，不足的部分用padstr代替

select LPAD(str,len,padstr)

select LPAD("mysql",10,"*")? *****mysql

select lpad(ename,20,"*") from emp;

??TRIM去掉前后空格

select TRIM(" fgagag gggggg? ? ")

?? lTRIM去掉左側(cè)空格

select lTRIM(" fgagag gggggg? ? ")

?? rTRIM去掉右側(cè)空格

select rTRIM(" fgagag gggggg? ? ")

? 比較字符串是否相等 相等返回0

select STRCMP("ab","ab")

?如果str1>str2,返回1

select STRCMP("abc","ab")

?如果str1<str2,返回-1

select STRCMP("ab","abc")

?SUBSTR(s,n,len) 截取到s中從第n個字符開始的len長度的字符串,如果省略了len,就截取到末尾

select SUBSTR("abcdefagagag",3,3)

select SUBSTR("abcdefagagag",3)

## 練習2 ##

1.顯示所有員工姓名的前三個字符

select substring(ename,1,3)

from emp;

select left(ename,3) from emp;

2.顯示姓名正好為5個字符的員工的姓名，工資钧椰，部門號

select ename, sal, deptno

from emp

where char_length(ename)=5;

日期和時間函數(shù)

以下為重點

TIMEDIFF

?TIMEDIFF(expr1, expr2)：返回兩個日期相減相差的時間數(shù)反惕；

注意事項：時間可以寫成18:32:00 形式也可寫成183200形式

如select?TIMEDIFF('183200', '60000')? from dual;

select TIMEDIFF("18:32:59","06:00:00")

返回結(jié)果為:12:32:00

DATEDIFF

?DATEDIFF(expr1, expr2)：返回兩個日期相減相差的天數(shù)；

注意：

實際與時間無太大關(guān)系演侯，如果寫上時間，要注意寫法

以下幾種都為合法

select empno,ename,hiredate,DATEDIFF(hiredate,"2019-07-09") from emp;

select DATEDIFF('2018-09-14 11:30:20', '2018-09-01') from dual;

select DATEDIFF('2018-09-14', '2018-09-01') from dual;

select DATEDIFF('2018-09-14','20180901') from dual;

select DATEDIFF('2018-09-14 11:30:20', '20180901') from dual;

DATE_ADD*重點

?DATE_ADD(date,INTERVAL expr unit):日期加上一個時間間隔值背亥；

注意：其中的INTERVAL 為關(guān)鍵字秒际，固定寫法

? ? ? ? ? ?expr是一個表達式，對應(yīng)后面的類型

? ? ? ? ? ? unit是時間間隔的單位(間隔類型)狡汉，如下：

注意：YEAR_MONTH中的year和month值用點分隔

select empno,ename,HIREDATE,DATE_ADD(hiredate,INTERVAL 1.2 YEAR_MONTH) from emp;

如：

假設(shè)員工入職半年后轉(zhuǎn)正娄徊，請查詢出員工轉(zhuǎn)正日期：

select DATE_ADD(hiredate,INTERVAL 6 month) from emp;

入職一天后的日期

select DATE_ADD(hiredate,INTERVAL 1 day) from emp;

DATE_SUB(略)

?DATE_SUB(date,INTERVAL expr unit):日期減去一個時間間隔值；

與加類似盾戴，不同處是做減運算

?日期加減第二種方式

不使用函數(shù),也可以寫表達式進行日期的加減：

date + INTERVAL? expr unit

date - INTERVAL? expr unit

假設(shè)員工入職半年后轉(zhuǎn)正寄锐，請查詢出員工轉(zhuǎn)正日期：

select hiredate+INTERVAL 6 month from emp;

入職一天后的日期

select hiredate,hiredate+interval 1 DAY 一天后

from emp

前一天

select hiredate-INTERVAL 1 day from emp;

?DATE_FORMAT(date,format)：格式化日期；

–date?參數(shù)是合法的日期

–format?規(guī)定日期/時間的輸出格式

?TIME_FORMATE(time,formate）：格式化時間尖啡；

-- H24小時制 h12小時制

-- Y 4位年 y 2位年

-- M是英文月份 m是數(shù)字月份

-- %d來定義日時橄仆，不足兩位的補0 %e來定義日時，不足兩位的不補0

-- H補0 k不補0

-- h補0 l不補0

24小時制：

select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%S') FROM DUAL;

12小時制：

select DATE_FORMAT(now(),'%Y-%m-%d %h:%i:%S') FROM DUAL;

select DATE_FORMAT(now(),"%Y-%M-%D %H:%I:%S") 當前時間

-- 2019-July-9th 11:11:50

select DATE_FORMAT(now(),"%Y-%m-%d %H:%I:%S") 當前時間

-- 2019-07-09 11:11:38

select DATE_FORMAT(now(),"%Y-%m-%d %H:%i:%s") 當前時間

-- 2019-07-09 11:37:04

簡單日期時間函數(shù)衅斩，按課件上的例子試一下

?CURDATE()和CURRENT_DATE()：獲取當前日期函數(shù);

?NOW()：返回服務(wù)器的當前日期和時間盆顾；

?CURTIME(）：返回當前時間，只包含時分秒畏梆；

?UTC_DATE(）：返回世界標準時間日期函數(shù)您宪；

?UTC_TIME()：返回世界標準時間函數(shù)；

?DATE(date)奠涌、TIME(date)宪巨、YEAR（date）：選取日期時間的各個部分：

select DATE(now()),TIME(now()),

YEAR(now()),MONTH(now()),DAY(now()),HOUR(now()),MINUTE(now()),SECOND(now())

from dual

?DAYOFWEEK(date) 、DAYOFMONTH(date) 溜畅、DAYOFYEAR(date):返回日期在一周捏卓、一月、一年中是第幾天

select DAYOFWEEK(now())? ?星期日為1 慈格。天吓。。周五返回6

?DAYNAME峦椰、MONTHNAME：返回日期的星期和月份名稱龄寞；

?EXTRACT(unit? FROM date)：從日期中抽取出某個單獨的部分或組合；

? ? SELECT now(),extract(YEAR FROM now()); -- 年

? ? ?SELECT now(),extract(QUARTER FROM now()); -- 季度

? ? ?SELECT now(),extract(MONTH FROM now()); -- 月

? ? ?SELECT now(),extract(WEEK FROM now()); -- 周

? ? ?SELECT now(),extract(DAY FROM now()); -- 日

? ? ? SELECT now(),extract(HOUR FROM now()); -- 小時

? ? ? SELECT now(),extract(MINUTE FROM now()); -- 分鐘

? ? ? SELECT now(),extract(SECOND FROM now()); -- 秒

? ? ? SELECT now(),extract(YEAR_MONTH FROM now()); -- 年月

? ? ? SELECT now(),extract(HOUR_MINUTE FROM now()); -- 時分

?個性化顯示時間日期

–dayofweek(date)

–dayofmonth(date)

–dayofyear(date)

流程控制函數(shù)

## casewhen ##

?CASE

value WHEN [compare-value] THEN result

? ? ? ? ? ?[WHEN [compare-value] THEN result ...]

[ELSE result]?

END

?CASE

WHEN [condition] THEN result?

[WHEN [condition] THEN result ...]?

[ELSE result]

END

例子：

1）SELECT ename,CASE deptno WHEN 10 THEN '10號部門'

when 20 then '20號部門'

ELSE '其他部門'?

END as 部門

from emp;

2）select case when sal<1500 then 'low' else 'high' end

from emp;

3）select empno,sal,

case when sal<1500 then 'low'

when sal? BETWEEN 1500 and 2000 then 'middle'

ELSE 'high'

end as 工資水平

from emp

select ename,sal,

case

when sal<1000 then '工資低需要漲一下'

when sal BETWEEN 1000 and 1500? then '工資中等汤功，討論一下'

when sal>1500 then '工資高物邑，不用漲'

END as 是否需要漲工資

from emp;

ifnull

詳見第4章null部分

ifnull(expr1,expr2) 如果expr1為null,則返回expr2的值，否則返回expr1的值

if

?IF(expr1,expr2,expr3)

–如果expr1 是TRUE 則 IF()的返回值為expr2;否則返回值則為 expr3。

–IF()的返回值為數(shù)字值或字符串值色解，具體情況視其所在語境而定茂嗓。

?SELECT IF(1>2,2,3);

?SELECT IF(1<2,'yes ','no');

select empno,comm,if(comm is null,0,comm) as 獎金

from emp;

-- 判斷該員工是不是大boss

-- 其實是判斷員工有沒有上級領(lǐng)導(dǎo) mgr？null

-- mgr=null 我是大boss,否則我不是

select ename,mgr,if(mgr is null,'我是大boss','我不是')

from emp;

練習題答案

作業(yè)：

## 練習3,4,課后作業(yè)##

練習3

?1.寫一個查詢,用首字母大寫科阎，其它字母小寫顯示雇員的 ename述吸，顯示名字的長度，并給每列一個適當?shù)臉撕灺啾浚瑮l件是滿足所有雇員名字的開始字母是J蝌矛、A 或 M 的雇員，并對查詢結(jié)果按雇員的ename升序排序错英。（提示：使用length入撒、substr）

select concat(upper(left(ename,1)),lower(right(ename,length(ename)-1))) 名稱,length(ename) 長度

from emp

where upper(left(ename,1)) in ('J','A','M')

order by ename

?2.查詢員工姓名中中包含大寫或小寫字母A的員工姓名。

select ename

from emp

where locate('A',upper(ename))>0

?3.顯示所有員工的姓名,用a替換所有"A"

select replace(ename,'A','a')

from emp

?4.查詢部門編號為10或20椭岩，入職日期在81年5月1日之后茅逮，并且姓名中包含字母A的員工姓名，員工姓名長度

select ename,length(ename)

from emp

where deptno in(10,20)

and hiredate>'1981-05-01'

and locate('A',upper(ename))>0

?5.查詢每個職工的編號,姓名判哥，工資

–要求將查詢到的數(shù)據(jù)按照一定的格式合并成一個字符串.

–前10位：編號,不足部分用*填充,左對齊

–中間10位：姓名献雅，不足部分用*填充,左對齊

–后10位：工資，不足部分用*填充,右對齊

select concat(rpad(empno,10,'*'),rpad(ename,10,'*'),lpad(sal,10,'*'))

from emp

練習4

1.查詢服務(wù)器當前時間

SELECT now()

FROM DUAL

2.查詢部門10,20的員工截止到2000年1月1日塌计，工作了多少周惩琉，入職的月份。

select ceil(DATEDIFF('2000-1-1',hiredate)/7),

monthname(hiredate)

from emp

3.如果員工試用期6個月夺荒，查詢職位不是MANAGER的員工姓名瞒渠，入職日期，轉(zhuǎn)正日期技扼，入職日期是第多少月伍玖，第多少周

select ename,

hiredate,

date_format(date_add(hiredate,interval 6 month),'%y-%m-%d')轉(zhuǎn)正日期,

month(hiredate),

WEEKOFYEAR(hiredate)

from emp

where job <> 'MANAGER'

3.如果員工試用期6個月，查詢職位不是MANAGER的員工姓名剿吻，入職日期窍箍，轉(zhuǎn)正日期，入職日期是第多少月丽旅，第多少周椰棘，星期幾

week一周的開始默認是從周日開始算的，可以通過第二個參數(shù)來指定榄笙，比如周一為第一天邪狞，則第二個參數(shù)為1

SELECT ename 員工姓名 ,hiredate 入職日期, hiredate+INTERVAL 6 MONTH 轉(zhuǎn)正日期,

month(hiredate) 月,week(hiredate,1) 周,dayofweek(hiredate) 星期

FROM emp

WHERE job != 'MANAGER';

WEEKOFYEAR一周的開始是從周一開始算的

SELECT ename 員工姓名 ,hiredate 入職日期, hiredate+INTERVAL 6 MONTH 轉(zhuǎn)正日期,

month(hiredate) 月,WEEKOFYEAR(hiredate) 周,dayofweek(hiredate) 星期

FROM emp

WHERE job != 'MANAGER';

課后作業(yè)

1.計算2000年1月1日到現(xiàn)在有多少周（四舍五入）。

select round(datediff(curdate(),'2000-01-01')/7)

from dual;

2.查詢員工ENAME的第三個字母是A的員工的信息(使用2個函數(shù))茅撞。

select *

from emp

where ename like '__A%'

select *

from emp

where substring(ename,3,1) = 'A'

3.使用trim函數(shù)將字符串‘hello’帆卓、‘? Hello ’巨朦、‘bllb’、‘ hello? ? ’

分別處理得到下列字符串ello剑令、Hello糊啡、ll、hello吁津。

-- SELECT RIGHT('hello',4),TRIM(' Hello '),SUBSTRING('bllb',2,2),RTRIM('hello? ')

-- FROM DUAL

select trim('聽' from '聽說你要請我吃鍋包肉')

from dual

select trim('h' from 'hello'),trim('? Hello '),trim('b' from 'bllb'),trim(' hello? ? ')

from dual

4.查詢員工的姓名及其經(jīng)理編號棚蓄，要求對于沒有經(jīng)理的顯示“No Manager”字符串。

select ename,ifnull(mgr,'no manager')

from emp

5.將員工的參加工作日期按如下格式顯示：月份/年份碍脏。

select date_format(hiredate,'%m/%Y')

from emp

6.在員工表中查詢出員工的工資梭依，并計算應(yīng)交稅款：如果工資小于1000,稅率為0，如果工資大于等于1000并小于2000潮酒，

稅率為10％，如果工資大于等于2000并小于3000邪蛔，稅率為15％急黎，如果工資大于等于3000，稅率為20％侧到。

select sal,case when sal<1000 then 0*sal

when sal<2000 then 0.1*sal

when sal<3000 then 0.15*sal

else 0.2*sal end

from emp

SELECT sal,

CASE

WHEN sal<1000 THEN sal*0

WHEN 1000<=sal<2000 then sal*0.1

WHEN 2000<=sal<3000 THEN sal*0.15

WHEN sal>=3000 THEN sal*0.2

END?

FROM emp

7.創(chuàng)建一個查詢顯示所有雇員的 ename和 sal勃教。格式化sal為 15 個字符長度，用 $ 左填充匠抗，列標簽 SALARY故源。

select ename,lpad(sal,15,'$') SALARY

from emp