【mysql的編程專題③】內置函數 · Mysql的進階與高可用架構

[toc] ## 數學函數 **常用** abs(x) 返回x的絕對值 floor(x) 返回小于x的最大整數值 mod(x,y) 返回x/y的模（余數） rand() 返回０到１內的隨機值,可以通過提供一個參數(種子)使rand()隨機數生成器生成一個指定的值。 truncate(x,y) 返回數字x截短為y位小數的結果 round(x,y) 返回參數x的四舍五入的有y位小數的值 ceiling(x) 返回大于x的最小整數值 greatest(x1,x2,...,xn) 返回集合中最大的值 ```sql select greatest(88,11122,4526,2); ``` least(x1,x2,...,xn) 返回集合中最小的值 **不常用** sqrt(x) 返回一個數的平方根 bin(x) 返回x的二進制（oct返回八進制，hex返回十六進制） exp(x) 返回值e（自然對數的底）的x次方 ln(x) 返回x的自然對數 log(x,y) 返回x的以y為底的對數 pi() 返回pi的值（圓周率） sign(x) 返回代表數字x的符號的值 ## 聚合函數 avg(col) 返回指定列的平均值 count(col) 返回指定列中非null值的個數 min(col) 返回指定列的最小值 max(col) 返回指定列的最大值 sum(col) 返回指定列的所有值之和 group_concat(col) 返回由屬于一組的列值連接組合而成的結果 ```sql mysql> SELECT user_id,nickname FROM `users` where email = '0'; +---------+------------+ | user_id | nickname | +---------+------------+ | 7 | 張三 | | 13 | 阿菲肉嘟嘟 | | 14 | 多大 | +---------+------------+ mysql> SELECT group_concat(nickname) FROM `users` where email = '0'; -- 以逗號分隔 +------------------------+ | group_concat(nickname) | +------------------------+ | 張三,阿菲肉嘟嘟,多大 | +------------------------+ 1 row in set ``` ## 字符串函數 **常用** concat(s1,s2...,sn) 將s1,s2...,sn連接成字符串 insert(str,x,y,instr) 將字符串str從第x位置開始，y個字符長的子串替換為字符串instr，返回結果 ```sql mysql> SELECT insert(nickname,1,2,user_id) as test FROM `users` where email = '0'; -- 把前面兩個字符替換為id號; +----------+ | test | +----------+ | 7 | | 13肉嘟嘟 | | 14 | +----------+ 3 rows in set ``` replace(str,from_str,to_str) 在字符串 str 中所有出現的字符串 from_str 均被 to_str替換，然后返回這個字符串 ```sql UPDATE BBSTopic SET tcontents = replace(replace(tcontents,'共產黨','') ,'找死','') where tcontents like '%共產黨%' or tcontents like '%找死%'; UPDATE typetable SET type_description=REPLACE(type_description,'360','http://www.cnblogs.com/nixi8/'); ``` **該函數是多字節可靠的** ```sql mysql> update goods set gname = replace(gname,'你好','好個屁'); Query OK, 1 row affected Rows matched: 6 Changed: 1 Warnings: 0 mysql> select * from goods; +-----+------------------------+-------+ | gid | gname | stock | +-----+------------------------+-------+ | 5 | 電腦 | 35 | | 6 | 自行車 | 35 | | 7 | 汽車 | 111 | | 8 | 手機 | 500 | | 9 | 舊的 | 0 | | 10 | 好個屁helloworld好個屁 | 2222 | +-----+------------------------+-------+ ``` > 索引是從1開始的,如果為0就返回原字符; > 如果結束索引大于本身的字段值長度,那將會被全部替換; > 在select后,字符串如果不帶引號將被認為是mysql字段; concat_ws(sep,s1,s2...,sn) 將s1,s2...,sn連接成字符串，并用sep字符間隔 ```sql mysql> SELECT concat_ws(',',user_id,nickname) as test FROM `users` where email = '0'; -- 將user_id與nickname拼接起來; +---------------+ | test | +---------------+ | 7,張三 | | 13,阿菲肉嘟嘟 | | 14,多大 | +---------------+ ``` find_in_set(str,list) 分析逗號分隔的list列表，如果發現str，返回str在list中的位置 ```sql mysql> select find_in_set('z','a,d,b,z,d,g') as test ; +------+ | test | +------+ | 4 | +------+ ``` lcase(str)或lower(str) 返回將字符串str中所有字符改變為小寫后的結果 ucase(str)或upper(str) 返回將字符串str中所有字符轉變為大寫后的結果 left(str,x) 返回字符串str中最左邊的x個字符 right(str,x) 返回字符串str中最右邊的x個字符 mid(str,pos,len) 從字符串str返回一個len個字符的子串，從位置pos開始 > tips: 不分中英文,可作為字符串截取用 substring(str, pos, length) -- substring（被截取字段，從第幾位開始截取，截取長度） ```sql mysql> select substring('白日依山盡',1,2) as t; -- 該函數也是多字節可靠的,與服務器端的語言有所不同的是該函數是從1開始計算的。 +------+ | t | +------+ | 白日 | +------+ 1 row in set ``` length(str)返回字符串str中的字符數 ```sql mysql> select length('白日依山盡') as test -- 注意中文字符,一個頂三個; ; +------+ | test | +------+ | 15 | +------+ mysql> select length('zhouzhou') as test2; +-------+ | test2 | +-------+ | 8 | +-------+ 1 row in set ``` char_length("白日依山盡") 不分中英文的字符串計算； ```sql mysql> select char_length("白日依山盡") as length; +--------+ | length | +--------+ | 5 | +--------+ 1 row in set ``` ltrim(str) 從字符串str中切掉開頭的空格 rtrim(str) 返回字符串str尾部的空格 trim(str)去除字符串首部和尾部的所有空格 ```sql -- trim不僅僅能去除空格,還能去除首部和尾部指定的字符 /* 完整格式：TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) 簡化格式：TRIM([remstr FROM] str) */ mysql> select trim(both '?' from '??曉???剛??') as t; -- 對中間的字符是無奈的; +---------+ | t | +---------+ | 曉???剛 | +---------+ 1 row in set mysql> select trim(leading '?' from '??曉???剛??') as t; +-----------+ | t | +-----------+ | 曉???剛?? | +-----------+ 1 row in set ``` position(substr,str) 返回子串substr在字符串str中第一次出現的位置 ```sql mysql> select LOCATE('bar', 'foobarbar') as test; +------+ | test | +------+ | 4 | +------+ 1 row in set mysql> select LOCATE('日', '白日依山盡') as test; -- 該函數是多字節可靠的。 +------+ | test | +------+ | 2 | +------+ 1 row in set ``` reverse(str) 返回顛倒字符串str的結果 repeat(str,count) 返回字符串str重復count次的結果 **常用** ascii(char)返回字符的ascii碼值 bit_length(str)返回字符串的比特長度 quote(str) 用反斜杠轉義str中的單引號 strcmp(s1,s2)比較字符串s1和s2 ## 日期和時間函數 **常用** curdate()或current_date() 返回當前的日期 ```sql mysql> select curdate(); +------------+ | curdate() | +------------+ | 2015-04-25 | +------------+ ``` curtime()或current_time() 返回當前的時間 ```sql mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 11:57:52 | +-----------+ ``` now 返回當前的時間和日期 ```sql mysql> select now(); +---------------------+ | now() | +---------------------+ | 2015-04-25 12:14:21 | +---------------------+ ``` unix_timestamp 返回當前的時間戳 ```sql mysql> select unix_timestamp(); +------------------+ | unix_timestamp() | +------------------+ | 1429935568 | +------------------+ ``` date_add(date,interval int keyword)返回日期date加上間隔時間int的結果 date_sub(date,interval int keyword)返回日期date減去間隔時間int的結果 > int必須按照[關鍵字](www.cnblogs.com/crazyant007/p/4220066.html "查看關鍵字列表")(day,month,year)進行格式化,如：``select date_add(current_date,interval 6 month)``; ```sql mysql> select date_sub(current_date,interval 6 month) as sub; +------------+ | sub | +------------+ | 2014-10-25 | +------------+ select date_add(current_date,interval 6 month) as dateadd; +------------+ | dateadd | +------------+ | 2015-10-25 | +------------+ ``` date_format(date,fmt) 依照指定的fmt格式[格式化](www.w3school.com.cn/sql/func_date_format.asp "查看格式化列表")日期date值 time_format(time,fmt) 只想轉換時間可以使用time_format ```sql SELECT `nid`,`title`,time_format(add_time,"%H時%i分%s秒")as time FROM news; ``` from_unixtime(ts,fmt) 根據指定的fmt格式，格式化unix時間戳ts ```sql mysql> select from_unixtime(unix_timestamp()); +---------------------------------+ | from_unixtime(unix_timestamp()) | +---------------------------------+ | 2015-04-25 12:20:30 | +---------------------------------+ 1 row in set ``` STR_TO_DATE 字符串格式化為標準時間格式; ```sql mysql> SELECT STR_TO_DATE("2013/3/14日","%Y/%m/%d日") as date; +------------+ | date | +------------+ | 2013-03-14 | +------------+ ``` dayofweek(date) 返回date是一星期中的第幾天(1~7) weekday(now()); 返回date是一星期中的第幾天(0~6)周一為0; dayofmonth(date) 返回date是一個月的第幾天(1~31) dayofyear(date) 返回date是一年的第幾天(1~366) dayname(date) 返回date的星期名 ```sql mysql> select dayname(now()); +----------------+ | dayname(now()) | +----------------+ | Saturday | +----------------+ ``` date(now()) 返回2015-04-26; hour(time) 返回time的小時值(0~23) minute(time) 返回time的分鐘值(0~59) month(date) 返回date的月份值(1~12) monthname(date) 返回date的月份名 quarter(date) 返回date在一年中的季度(1~4) week(date) 返回日期date為一年中第幾周(0~53) year(date) 返回日期date的年份(1000~9999) day(date) 返回date的日數; to_days(now()) 時間轉換為天數 ```sql mysql> select to_days(now()) as days; +--------+ | days | +--------+ | 736078 | +--------+ ``` from_days(days) 天數轉換為date; ```sql mysql> select from_days(736078) as date; +------------+ | date | +------------+ | 2015-04-25 | +------------+ ``` extract(unit FROM date) 返回日期/時間的單獨部分，比如年、月、日、小時、分鐘等等。[unit列表](www.w3school.com.cn/sql/func_extract.asp "查看unit列表") ```sql mysql> select extract(year_month from current_date) as test1; +--------+ | test1 | +--------+ | 201504 | +--------+ 1 row in set mysql> select extract(day_second from now()) test2; -- now():2015-4-25 12:26:51 +----------+ | test2 | +----------+ | 25122651 | +----------+ 1 row in set ``` **其它示例** ```sql mysql> select period_diff(200302,199802) as test; -- 返回月份差; +------+ | test | +------+ | 60 | +------+ 1 row in set mysql> select user_id,nickname,birthday,date_format(from_days(to_days(now())-to_days(birthday)),'%y')+0 as age from users;-- 如果brithday是未來的年月日（包括今年）的話，計算結果為00 + 0 = 0。當birthday是未來的日期時，將得到負值。 +---------+--------------+---------------------+-----+ | user_id | nickname | birthday | age | +---------+--------------+---------------------+-----+ | 154546 | 兔小寶要加油 | 2018-05-01 00:00:00 | 0 | | 169638 | 梅梅0919 | 1987-09-19 00:00:00 | 27 | | 169699 | 小刀叨叨 | 1989-05-10 00:00:00 | 25 | +---------+--------------+---------------------+-----+ 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； -- 計算員工的絕對年齡，即當birthday是未來的日期時，將得到負值。 ``` ## 控制流函數 > 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 ```sql 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函數還有另外一種句法，有時使用起來非常方便，如下： ```sql case when [conditional test 1] then [result 1] when [conditional test 2] then [result 2] else [default result] end ``` 這種條件下，返回的結果取決于相應的條件測試是否為真。 Example： ```sql SELECT gname, stock, CASE stock -- 當待分析的值放在case后,下面的when就不能用表達式了,只能直接根據其值來進行判斷; WHEN 500 THEN '庫存充足' WHEN 0 THEN '庫存為空' ELSE '其它情況' END AS discription FROM goods; +--------+-------+-------------+ | gname | stock | discription | +--------+-------+-------------+ | 汽車 | 100 | 其它情況 | | 手機 | 500 | 庫存充足 | | 舊的 | 0 | 庫存為空 | +--------+-------+-------------+ ``` Example: ```sql SELECT gid, gname, stock, CASE WHEN stock > 200 THEN '庫存充足' WHEN stock < 200 and stock > 50 THEN '庫存正常' WHEN stock < 50 and stock > 0 THEN '庫存不足' WHEN stock = 0 THEN '庫存為零' ELSE NULL END AS discription FROM goods; +-----+--------+-------+-------------+ | gid | gname | stock | discription | +-----+--------+-------+-------------+ | 6 | 自行車 | 35 | 庫存不足 | | 7 | 汽車 | 111 | 庫存正常 | | 8 | 手機 | 500 | 庫存充足 | | 9 | 舊的 | 0 | 庫存為零 | +-----+--------+-------+-------------+ 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; ``` ``if(test,t,f)`` 如果test是真，返回t；否則返回f ``ifnull(arg1,arg2)`` 如果arg1不是空，返回arg1，否則返回arg2 ``nullif(arg1,arg2)`` 如果arg1=arg2返回null；否則返回arg1 ## 格式化函數 date_format(date,fmt) 依照字符串fmt格式化日期date值 time_format(time,fmt) 依照字符串fmt格式化時間time值 format(x,y) 把x格式化為以逗號隔開的數字序列，y是結果的小數位數 inet_aton(ip) 返回ip地址的數字表示 inet_ntoa(num) 返回數字所代表的ip地址 > 其中最簡單的是format()函數，它可以把大的數值格式化為以逗號間隔的易讀的序列。 ```sql 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 ```sql mysql> select cast(now() as signed integer) as cast,now() as now; -- 把當前時間轉換為一個帶符號的整數; +----------------+---------------------+ | cast | now | +----------------+---------------------+ | 20150426163210 | 2015-04-26 16:32:10 | +----------------+---------------------+ select cast(now() as signed integer),curdate()+0; select 'f'=binary 'f','f'=cast('f' as binary); ``` ## 加密函數 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)校驗和 **Example** ```sql 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'); ``` ## 系統信息函數 database() 返回當前數據庫名 benchmark(count,expr) 將表達式expr重復運行count次 connection_id() 返回當前客戶的連接id found_rows() 返回最后一個select查詢進行檢索的總行數 user()或system_user() 返回當前登陸用戶名 version() 返回mysql服務器的版本 ```sql select database(),version(),user(); selectbenchmark(9999999,log(rand()*pi()));#該例中,mysql計算log(rand()*pi())表達式9999999次。 ```