Mysql常用函數,難點,註意

一、數學函數

ABS(x)   返回x的絕對值

 

BIN(x)   返回x的二進制(OCT返回八進制,HEX返回十六進制)

CEILING(x)   返回大於x的最小整數值

EXP(x)   返回值e(自然對數的底)的x次方

FLOOR(x)   返回小於x的最大整數值

GREATEST(x1,x2,…,xn)返回集合中最大的值

LEAST(x1,x2,…,xn)      返回集合中最小的值

LN(x)                    返回x的自然對數

LOG(x,y)返回x的以y為底的對數

MOD(x,y)                 返回x/y的模(餘數)

PI()返回pi的值(圓周率)

RAND()返回0到1內的隨機值,可以通過提供一個參數(種子)使RAND()隨機數生成器生成一個指定的值。

ROUND(x,y)返回參數x的四舍五入的有y位小數的值

SIGN(x) 返回代表數字x的符號的值

SQRT(x) 返回一個數的平方根

TRUNCATE(x,y)            返回數字x截短為y位小數的結果

 

二、聚合函數(常用於GROUP BY從句的SELECT查詢中)

AVG(col)返回指定列的平均值

COUNT(col)返回指定列中非NULL值的個數

MIN(col)返回指定列的最小值

MAX(col)返回指定列的最大值

SUM(col)返回指定列的所有值之和

GROUP_CONCAT(col) 返回由屬於一組的列值連接組合而成的結果

 

三、字符串函數

ASCII(char)返回字符的ASCII碼值

BIT_LENGTH(str)返回字符串的比特長度

CONCAT(s1,s2…,sn)將s1,s2…,sn連接成字符串

CONCAT_WS(sep,s1,s2…,sn)將s1,s2…,sn連接成字符串,並用sep字符間隔

INSERT(str,x,y,instr) 將字符串str從第x位置開始,y個字符長的子串替換為字符串instr,返回結果

FIND_IN_SET(str,list)分析逗號分隔的list列表,如果發現str,返回str在list中的位置

LCASE(str)或LOWER(str) 返回將字符串str中所有字符改變為小寫後的結果

LEFT(str,x)返回字符串str中最左邊的x個字符

LENGTH(s)返回字符串str中的字符數

LTRIM(str) 從字符串str中切掉開頭的空格

POSITION(substr,str) 返回子串substr在字符串str中第一次出現的位置

QUOTE(str) 用反斜杠轉義str中的單引號

REPEAT(str,srchstr,rplcstr)返回字符串str重復x次的結果

REVERSE(str) 返回顛倒字符串str的結果

RIGHT(str,x) 返回字符串str中最右邊的x個字符

RTRIM(str) 返回字符串str尾部的空格

STRCMP(s1,s2)比較字符串s1和s2

TRIM(str)去除字符串首部和尾部的所有空格

UCASE(str)或UPPER(str) 返回將字符串str中所有字符轉變為大寫後的結果

 

四、日期和時間函數

CURDATE()或CURRENT_DATE() 返回當前的日期

CURTIME()或CURRENT_TIME() 返回當前的時間

DATE_ADD(date,INTERVAL int keyword)返回日期date加上間隔時間int的結果(int必須按照關鍵字進行格式化),如:SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);

DATE_FORMAT(date,fmt)  依照指定的fmt格式格式化日期date值

DATE_SUB(date,INTERVAL int keyword)返回日期date加上間隔時間int的結果(int必須按照關鍵字進行格式化),如:SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);

DAYOFWEEK(date)   返回date所代表的一星期中的第幾天(1~7)

DAYOFMONTH(date)  返回date是一個月的第幾天(1~31)

DAYOFYEAR(date)   返回date是一年的第幾天(1~366)

DAYNAME(date)   返回date的星期名,如:SELECT DAYNAME(CURRENT_DATE);

FROM_UNIXTIME(ts,fmt)  根據指定的fmt格式,格式化UNIX時間戳ts

HOUR(time)   返回time的小時值(0~23)

MINUTE(time)   返回time的分鐘值(0~59)

MONTH(date)   返回date的月份值(1~12)

MONTHNAME(date)   返回date的月份名,如:SELECT MONTHNAME(CURRENT_DATE);

NOW()    返回當前的日期和時間

QUARTER(date)   返回date在一年中的季度(1~4),如SELECT QUARTER(CURRENT_DATE);

WEEK(date)   返回日期date為一年中第幾周(0~53)

YEAR(date)   返回日期date的年份(1000~9999)

 

一些示例:

 

獲取當前系統時間:SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());

SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE);

SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE);

SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE);

返回兩個日期值之間的差值(月數):SELECT PERIOD_DIFF(200302,199802);

 

在Mysql中計算年齡:

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee;

這樣,如果Brithday是未來的年月日的話,計算結果為0。

 

下面的SQL語句計算員工的絕對年齡,即當Birthday是未來的日期時,將得到負值。

SELECT DATE_FORMAT(NOW(), '%Y') – DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee

 

五、加密函數

 

AES_ENCRYPT(str,key)  返回用密鑰key對字符串str利用高級加密標準算法加密後的結果,調用AES_ENCRYPT的結果是一個二進制字符串,以BLOB類型存儲

 

AES_DECRYPT(str,key)  返回用密鑰key對字符串str利用高級加密標準算法解密後的結果

 

DECODE(str,key)   使用key作為密鑰解密加密字符串str

 

ENCRYPT(str,salt)   使用UNIXcrypt()函數,用關鍵詞salt(一個可以惟一確定口令的字符串,就像鑰匙一樣)加密字符串str

 

ENCODE(str,key)   使用key作為密鑰加密字符串str,調用ENCODE()的結果是一個二進制字符串,它以BLOB類型存儲

 

MD5()    計算字符串str的MD5校驗和

 

PASSWORD(str)   返回字符串str的加密版本,這個加密過程是不可逆轉的,和UNIX密碼加密過程使用不同的算法。

 

SHA()    計算字符串str的安全散列算法(SHA)校驗和

示例:

SELECT ENCRYPT('root','salt');

SELECT ENCODE('xufeng','key');

SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起

SELECT AES_ENCRYPT('root','key');

SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');

SELECT MD5('123456');

SELECT SHA('123456');

 

六、控制流函數

 

MySQL有4個函數是用來進行條件操作的,這些函數可以實現SQL的條件邏輯,允許開發者將一些應用程式業務邏輯轉換到資料庫後臺。

 

MySQL控制流函數:

 

CASE WHEN[test1] THEN [result1]…ELSE [default] END如果testN是真,則返回resultN,否則返回default

 

CASE [test] WHEN[val1] THEN [result]…ELSE [default]END  如果test和valN相等,則返回resultN,否則返回default

 

IF(test,t,f)   如果test是真,返回t;否則返回f

 

IFNULL(arg1,arg2) 如果arg1不是空,返回arg1,否則返回arg2

 

NULLIF(arg1,arg2) 如果arg1=arg2返回NULL;否則返回arg1

 

這些函數的第一個是IFNULL(),它有兩個參數,並且對第一個參數進行判斷。如果第一個參數不是NULL,函數就會向調用者返回第一個參數;如果是NULL,將返回第二個參數。

 

如:SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,'false');

 

NULLIF()函數將會檢驗提供的兩個參數是否相等,如果相等,則返回NULL,如果不相等,就返回第一個參數。

 

如:SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1);

 

和許多腳本語言提供的IF()函數一樣,MySQL的IF()函數也可以建立一個簡單的條件測試,這個函數有三個參數,第一個是要被判斷的表達式,如果表達式為真,IF()將會返回第二個參數,如果為假,IF()將會返回第三個參數。

 

如:SELECTIF(1<10,2,3),IF(56>100,'true','false');

 

IF()函數在隻有兩種可能結果時才適合使用。然而,在現實世界中,我們可能發現在條件測試中會需要多個分支。在這種情況下,MySQL提供瞭CASE函數,它和PHP及Perl語言的switch-case條件例程一樣。

 

CASE函數的格式有些復雜,通常如下所示:

CASE [expression to be evaluated]

WHEN [val 1] THEN [result 1]

WHEN [val 2] THEN [result 2]

WHEN [val 3] THEN [result 3]

……

WHEN [val n] THEN [result n]

ELSE [default result]

END

 

這裡,第一個參數是要被判斷的值或表達式,接下來的是一系列的WHEN-THEN塊,每一塊的第一個參數指定要比較的值,如果為真,就返回結果。所有的WHEN-THEN塊將以ELSE塊結束,當END結束瞭所有外部的CASE塊時,如果前面的每一個塊都不匹配就會返回ELSE塊指定的默認結果。如果沒有指定ELSE塊,而且所有的WHEN-THEN比較都不是真,MySQL將會返回NULL。

 

CASE函數還有另外一種句法,有時使用起來非常方便,如下:

CASE

WHEN [conditional test 1] THEN [result 1]

WHEN [conditional test 2] THEN [result 2]

ELSE [default result]

END

 

這種條件下,返回的結果取決於相應的條件測試是否為真。

示例:

mysql>SELECT CASE 'green'

        WHEN 'red' THEN 'stop'

      WHEN 'green' THEN 'go' END;

SELECT CASE 9 WHEN 1 THEN 'a' WHEN 2 THEN 'b' ELSE 'N/A' END;

SELECT CASE WHEN (2+2)=4 THEN 'OK' WHEN(2+2)<>4 THEN 'not OK' END ASSTATUS;

SELECT Name,IF((IsActive = 1),'已激活','未激活') AS RESULT FROMUserLoginInfo;

SELECT fname,lname,(math+sci+lit) AS total,

CASE WHEN (math+sci+lit) < 50 THEN 'D'

WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN 'C'

WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN 'B'

ELSE 'A' END

AS grade FROM marks;

SELECT IF(ENCRYPT('sue','ts')=upass,'allow','deny') AS LoginResultFROM users WHERE uname = 'sue';#一個登陸驗證

 

七、格式化函數

DATE_FORMAT(date,fmt)  依照字符串fmt格式化日期date值

FORMAT(x,y)   把x格式化為以逗號隔開的數字序列,y是結果的小數位數(四舍五入保留)

INET_ATON(ip)   返回IP地址的數字表示

INET_NTOA(num)   返回數字所代表的IP地址

TIME_FORMAT(time,fmt)  依照字符串fmt格式化時間time值

 

其中最簡單的是FORMAT()函數,它可以把大的數值格式化為以逗號間隔的易讀的序列。

示例:

SELECT FORMAT(34234.34323432,3);

SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

SELECT DATE_FORMAT(19990330,'%Y-%m-%d');

SELECT DATE_FORMAT(NOW(),'%h:%i %p');

SELECT INET_ATON('10.122.89.47');

SELECT INET_NTOA(175790383);

 

八、類型轉化函數

為瞭進行數據類型轉化,MySQL提供瞭CAST()函數,它可以把一個值轉化為指定的數據類型。類型有:BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED

 示例:

SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;

SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY);

 

九、系統信息函數

DATABASE()   返回當前資料庫名

BENCHMARK(count,expr)  將表達式expr重復運行count次

CONNECTION_ID()   返回當前客戶的連接ID

FOUND_ROWS()   返回最後一個SELECT查詢進行檢索的總行數

USER()或SYSTEM_USER()  返回當前登陸用戶名

VERSION()   返回MySQL伺服器的版本

示例:

SELECT DATABASE(),VERSION(),USER();

SELECTBENCHMARK(9999999,LOG(RAND()*PI()));#該例中,MySQL計算LOG(RAND()*PI())表達式9999999次。

十、Where條件

常用的比較運算符如下表所示:

運算符 名稱 示例
= 等於 id=10
> 大於 id>10
< 小於 id<10
>= 大於等於 id>=10
<= 小於等於 id<=10
!=或<> 不等於 id!=10
is null n/a id is null
is not null n/a id is not null
between n/a id between 1 and 15
in n/a id in(3,4,5)
not in n/a xuesheng not in(shi,li)
like 模式匹配 xuesheng like('shi%')
not like 模式匹配 xuesheng not like('shi%')
regexp 常規表達式 xuesheng正則表達式

 

提示:where子句的作用是在對查詢結果進行分組前,將不符合where條件的行去掉,即在分組之前過濾數據。因此,在使用where子句時,條件中不能包含聚合函數來顯示特定的行。

 

十一、GROUP BY對結果分組

 

在與AVG()函數或SUM()函數等聚合函數一起使用時,GROUP BY子句能夠發揮最大的作用。GROUP BY子句可以將查詢結果分組,並返回行的匯總信息。在帶有GROUP BY子句的查詢語句中,在SELECT列表中指定的列要麼是GROUP BY子句中指定的列,要麼就包含在瞭聚合函數中。查詢語句的SELECT和GROUP BY、HAVING子句是聚合函數唯一出現的地方,因此,在where子句中不能使用聚合函數。 就可以使用GROUP BY子句,按照xuesheng進行分組,求出每個學生的平均成績。代碼如下:

 

select xuesheng,avg(fenshu) from tb_chengji02 group by xuesheng;

 

group by xuesheng是按學生分組,那麼在xuesheng列中,相同名字的學生就會分為一組,然後計算出它們的平均成績。

 

十二、 HAVING子句

 

HAVING子句的作用是篩選滿足條件的組,即在分組之後過濾數據。在HAVING子句中經常包含聚合函數。可以使用HAVING條件顯示特定的組,也可以使用多個分組標準進行分組。當在GROUP BY子句中使用HAVING子句時,查詢結果中隻返回滿足having條件的組。

 

例如,在tb_chengji02表中查詢總成績大於等於280的記錄。代碼如下:

 

select xuesheng,sum(fenshu) from tb_chengji02 group by xuesheng having sum(fenshu)>=280; 

 

我們也可以在tb_chengji02表中查詢張冬雪或者李麗的總成績大於等於270的記錄是否存在,如果存在,則顯示出來。代碼如下:

 

select xuesheng,sum(fenshu) from tb_chengji02 where xuesheng='張冬雪' or xuesheng='李麗' group by xuesheng having sum(fenshu)>=270;

 

十三、 DISTINCT在結果中去除重復行

 

使用DISTINCT關鍵字,可以去除結果中重復的行。

 

DISTINCT語句中SELECT顯示的字段隻能是DISTINCT指定的字段,其他字段是不能出現的。

 

例如,查詢tb_chengji02表,並在結果中去掉xuesheng字段中的重復數據。代碼如下:

 

select distinct xuesheng from tb_chengji02; 

 

十四、 ORDER BY對結果進行排序

 使用ORDER BY可以對查詢的結果進行升序(ASC)或降序(DESC)排列,在默認情況下,ORDER BY按升序輸出結果。如果要按降序排列可以使用DESC來實現。

 

在對含有null值的列進行排序時,如果按升序排列,null值將出現在最前面;如果按降序排列,null值將出現在最後面。

 

例如,查詢tb_chengji表中的所有信息,按照“id”進行降序排列。代碼如下:

 

select * from tb_chengji order by id desc; 

 

十五、 LIKE模糊查詢

LIKE屬於比較常用的比較運算符,通過它可以實現模糊查詢。它有兩種通配符:“%”和下劃線“_”。“%”可以匹配一個或多個字符,而“_”隻能匹配一個字符。

 

例如,在tb_chengji表中查找所有第二個字符是“冬”的學生。代碼如下:

 

select * from tb_chengji where xuesheng like('_冬%'); 

 

十六、CONCAT聯合多列

 使用CONCAT函數可以聯合多個字段,構成一個總的字符串。

 

 例如,把tb_chengji表中的yuwen、shuxue和yingyu字段合並到一起,構成一個新的字符串。代碼如下:

 

select id,xuesheng,concat(yuwen,":",shuxue,":",yingyu) as fenshu from tb_chengji; 

 

十七、LIMIT限定結果行數

LIMIT子句可以對查詢結果的記錄條數進行限定,控制它輸出的行數。

 

例如,查詢tb_chengji表,按照id字段降序排列,顯示3條記錄。代碼如下:

 

select * from tb_chengji order by id desc limit 3; 

 

使用LIMIT還可以從查詢結果的中間部分取值。首先要設定兩個參數,參數1是開始讀取的第一條記錄的編號(在查詢結果中,第一個結果的記錄編號是0,而不是1),參數2是要查詢記錄的個數。

 

select * from tb_chengji order by id desc limit 1,2; 

 

十八、使用函數和表達式

在MySQL中,還可以使用表達式來計算各列的值,作為輸出結果。表達式還可以包含一些函數。

 

例如,計算tb_chengji表中學生的總分數。代碼如下:

 

select id,xuesheng,yuwen,shuxue,yingyu,sum(yuwen+shuxue+yingyu) as zongfen from tb_chengji group by id;

 

或者:

 

select *,sum(yuwen+shuxue+yingyu) as zongfen from tb_chengji group by id; 

 

計算tb_chengji表中學生的三門平均分數。代碼如下:

 

select *,format(avg(yuwen+shuxue+yingyu)/3),1) as pingjun feng from tb_chengji group by id; 

 

在對MySQL資料庫進行操作時,有時候需要對資料庫中的記錄進行統計,例如求平均值、最小值或最大值等,這時可以使用MySQL中的統計函數。

 

常用的統計函數如下表所示:

名稱

說明

avg(字段名)

獲取指定列的平均值。

count(字段名)

如果指定瞭一個字段,則會統計出該字段中的非空記錄。如果在前面增加DISTINCT,則會統計不同值的記錄,相同的值當做一條記錄。如果使用count(*),則統計包含空值的所有記錄數。

min(字段名)

獲取指定字段的最小值。

max(字段名)

獲取指定字段的最大值。

std(字段名)

指定字段的標準背離值。

stdtev(字段名)

與std相同。

sum(字段名)

指定字段所有記錄的總和。

You May Also Like