《MySQL數(shù)據(jù)庫(kù)應(yīng)用與管理項(xiàng)目化教程》課件-項(xiàng)目7-2 常用函數(shù)

項(xiàng)目七--2

MySQL常用函數(shù)任務(wù)目標(biāo)任務(wù)一數(shù)值函數(shù)任務(wù)二字符串函數(shù)任務(wù)三 日期與時(shí)間函數(shù)任務(wù)四 條件判斷函數(shù)任務(wù)五 JSON函數(shù)任務(wù)六 其他函數(shù)運(yùn)算符

主要內(nèi)容:數(shù)值函數(shù)應(yīng)用字符串函數(shù)應(yīng)用日期與時(shí)間函數(shù)應(yīng)用條件判斷函數(shù)應(yīng)用JSON函數(shù)應(yīng)用其他函數(shù)應(yīng)用重點(diǎn)難點(diǎn):函數(shù)應(yīng)用；基本查詢查閱語(yǔ)法：官網(wǎng)文檔：/doc/refman/8.0/en/functions.html語(yǔ)法：SELECT

[ALL|DISTINCT]

select_expr[,select_expr...]

[FROMtable_references[WHEREwhere_condition][GROUPBY{col_name|expr|position},...[WITHROLLUP]][HAVINGwhere_condition][ORDERBY{col_name|expr|position}[ASC|DESC][LIMIT{[offset,]row_count|row_countOFFSEToffset}]]數(shù)值函數(shù)函

數(shù)功

能ABS(x)返回?cái)?shù)值x的絕對(duì)值MOD(x,y)返回?cái)?shù)值x除以數(shù)值y后的余數(shù)CEIL(x)返回大于數(shù)值x的最小整數(shù)值FLOOR(x)返回小于數(shù)值x的最大整數(shù)值RAND()返回0～1內(nèi)的隨機(jī)數(shù)ROUND(x)返回對(duì)參數(shù)x進(jìn)行四舍五入后的值，ROUND(x)返回整數(shù)值，ROUND(x,y)返回參數(shù)x四舍五入后保留y位小數(shù)的值TRUNCATE(x,y)對(duì)數(shù)值x進(jìn)行截取，保留小數(shù)點(diǎn)后y位數(shù)字?jǐn)?shù)值函數(shù)應(yīng)用mysql>SELECTABS(5),ABS(-5);mysql>SELECTMOD(6,4),MOD(6,-4),MOD(NULL,6),MOD(6,0),MOD(0,2.5);mysql>SELECTCEIL(2.45),CEIL(-2.45);mysql>SELECTFLOOR(2.45),FLOOR(-2.45);mysql>SELECTRAND(),RAND(),RAND();#請(qǐng)執(zhí)行幾次，觀察結(jié)果，返回值是0～1內(nèi)的小數(shù)，并且每次的運(yùn)行結(jié)果都不同mysql>SELECTROUND(100.144),ROUND(100.568),ROUND(100.144,2),ROUND(100.568,2);#四舍五入mysql>SELECTTRUNCATE(1.42,1),TRUNCATE(1.58,1);#直接截取值，并不進(jìn)行四舍五入字符串函數(shù)函

數(shù)功

能LENGTH(str)，CHAR_LENGTH(str)返回字符串長(zhǎng)度或字符個(gè)數(shù)CONCAT(str1,str2…strn)，CONCAT_WS(x,str1,str2…strn)合并字符串INSERT(str,x,y,instr)，REPLACE(str,a,b)替換字符串LOWER(str)，UPPER(str)字符大小寫轉(zhuǎn)換LEFT(str,x)，RIGHT(str,x)，SUBSTRING(str,x,y)獲取字符串的一部分LPAD(str1,n,str2)，RPAD(str1,n,str2)填充字符串LTRIM(str)，RTRIM(str)，TRIM(str)刪除字符串左側(cè)、右側(cè)或兩側(cè)空格REPEAT(str,n)返回字符串str重復(fù)n次的結(jié)果LOCATE(str1,str)返回子字符串的開(kāi)始位置REVERSE(str)反轉(zhuǎn)字符串字符串函數(shù)應(yīng)用mysql>SELECTLENGTH('abcdef'),LENGTH('字符長(zhǎng)度');#UTF-8和utf8mb4：一個(gè)漢字=3個(gè)字節(jié)，英文是一個(gè)字節(jié);GBK：一個(gè)漢字=2個(gè)字節(jié)，英文是一個(gè)字節(jié)mysql>SELECTCHAR_LENGTH('abcdef'),CHAR_LENGTH('字符個(gè)數(shù)');mysql>SELECTCONCAT('abcd','efg'),CONCAT('abcd',NULL,'efg');#連接字符串mysql>SELECTCONCAT_WS('_','ab','cd','ef'),CONCAT_WS('_','gh',NULL,'ij');#用第一個(gè)分隔符拼接字符串mysql>SELECTINSERT('beijinglvyoushichang66666',13,8,'gonglue');#INSERT(str,x,y,instr)的作用是將字符串str從第x位置開(kāi)始，y個(gè)字符長(zhǎng)的子串替換為字符串instr。字符串函數(shù)應(yīng)用mysql>SELECTREPLACE('abc33ab2c','abc','you');mysql>SELECTREPLACE('abcabc','abc','you');mysql>SELECTLOWER('aBcD'),UPPER('aBcD');mysql>SELECTLEFT('beijinglvyougonglue',7),RIGHT('beijinglvyougonglue',7);mysql>SELECTSUBSTRING('beijinglvyougonglue',8,5);#獲取字符串str中從x位置開(kāi)始，后面y個(gè)字符長(zhǎng)度的子串mysql>SELECTLPAD('abcdefg',5,'km'),LPAD('abcdefg',10,'km');#使用字符串str2對(duì)字符串str1最左邊進(jìn)行填充，直到字符串str1總長(zhǎng)度達(dá)到n個(gè)字符長(zhǎng)度。mysql>SELECTRPAD('abcdefg',5,'km'),RPAD('abcdefg',10,'km');#最右邊進(jìn)行填充，直到字符串str1總長(zhǎng)度達(dá)到n個(gè)字符長(zhǎng)度。字符串函數(shù)應(yīng)用mysql>SELECTCONCAT('ab','cd','ef')ASstr1,CONCAT('ab',LTRIM('cd'),'ef')ASstr2,CONCAT('ab',RTRIM('cd'),'ef')ASstr3;mysql>SELECTCONCAT('ab','cd','ef')ASstr1,CONCAT('ab',TRIM('cd'),'ef')ASstr2,TRIM('a'from'aabacaa')ASstr3;#TRIM(str)用于刪除字符串開(kāi)頭和結(jié)尾的空格，另外還可以刪除字符串兩側(cè)指定字符mysql>SELECTREPEAT('abc',3);#返回字符串str重復(fù)n次的結(jié)果mysql>SELECTLOCATE('abc','ababcabd'),LOCATE('efg','ababcabd');#返回子串str1在字符串str中的開(kāi)始位置mysql>SELECTREVERSE('abcdefg');#字符反轉(zhuǎn)結(jié)果日期與時(shí)間函數(shù)函

數(shù)功

能CURDATE()獲取當(dāng)前日期CURTIME()獲取當(dāng)前時(shí)間NOW()獲取當(dāng)前的日期和時(shí)間UNIX_TIMESTAMP(date)獲取日期date的UNIX時(shí)間戳YEAR(d)，MONTH(d)，WEEK(d)，DAY(d)，HOUR(d)，MINUTE(d)，SECOND(d)返回指定日期的年份、月份、星期、日、時(shí)、分和秒DATE_FORMAT(d,format)按format指定的格式顯示日期d的值A(chǔ)DDDATE(date,INTERVALexprunit)，SUBDATE(date,INTERVALexprunit)獲取一個(gè)日期或時(shí)間值加上一個(gè)時(shí)間間隔的時(shí)間值TIME_TO_SEC(d),SEC_TO_TIME(d)獲取將“HH:MM:SS”格式的時(shí)間換算為秒，或?qū)⒚霐?shù)換算為“HH:MM:SS”格式的值日期與時(shí)間函數(shù)應(yīng)用mysql>SELECTCURDATE()AS當(dāng)前日期,CURDATE()AS當(dāng)前時(shí)間,NOW()AS當(dāng)前日期時(shí)間;mysql>SELECTYEAR('07-01-15'),YEAR(now());mysql>SELECTNOW(),UNIX_TIMESTAMP(NOW());#獲取UNIX格式的當(dāng)前日期和時(shí)間;#UNIX時(shí)間戳是從1970年1月1日（UTC/GMT的午夜）開(kāi)始到當(dāng)前時(shí)間所經(jīng)過(guò)的秒數(shù)（不考慮閏秒）。一分鐘表示為UNIX時(shí)間戳為60秒，一小時(shí)表示為UNIX時(shí)間戳為3600秒，一天表示為UNIX時(shí)間戳為86400秒。mysql>SELECTMONTH('07-01-15'),MONTH(NOW()),WEEK('07-01-15'),WEEK(NOW());#WEEK(d)返回所給的日期d是一年中的第幾周。mysql>SELECTHOUR('07-01-1505:16:21')ASHOUR,MINUTE('07-01-1505:16:21')ASMINUTE,SECOND('07-01-1505:16:21')ASSECOND;#獲取時(shí)間的時(shí)、分、秒的值日期與時(shí)間函數(shù)應(yīng)用日期格式化函數(shù)DATE_FORMAT(d,format)按字符串format格式化日期d的值，其中的format格式符及其作用見(jiàn)下表：mysql>SELECTDATE_FORMAT('2008-01-0514:30:30','%y%M%D%r');mysql>SELECTDATE_FORMAT('2008-01-0514:30:30','%y');mysql>SELECTDATE_FORMAT('2008-01-0514:30:30','%M');mysql>SELECTDATE_FORMAT('2008-01-0514:30:30','%r');日期與時(shí)間格式符及其意義參數(shù)值意

義%Y四位數(shù)形式的年份%y兩位數(shù)形式的年份%c數(shù)字形式（0～12）的月份%M英文形式（January～December）的月份名%m數(shù)字形式（00～12）的月份%W一周中每天為周幾，用英文表示（Sunday,Monday,…,Saturday）%D月中的第幾天，英文后綴形式，如0th，1st，2nd，3rd…%d兩位數(shù)字表示月中的第幾天，形式為00～31%j一年的第幾日（001～366）%H24小時(shí)形式的小時(shí)（00～23）%h12小時(shí)形式的小時(shí)（01～12）%r12小時(shí)形式的小時(shí)，后綴為上午（AM）或下午（PM）%i兩位數(shù)字形式的分（00～59）%S兩位數(shù)字形式的秒（00～59）日期與時(shí)間函數(shù)應(yīng)用計(jì)算日期和時(shí)間的函數(shù)ADDDATE(date,INTERVALexprunit)對(duì)日期時(shí)間執(zhí)行加運(yùn)算例如：mysql>SELECTADDDATE('2009-01-01',INTERVAL2year)asdate1,ADDDATE('2009-01-0106:20:20',INTERVAL2hour)asdate2,ADDDATE('2009-01-0106:20:20',INTERVAL'10:10'minute_second)asdate3;SUBDATE(date,INTERVALexprunit)對(duì)日期時(shí)間減運(yùn)算例如：mysql>SELECTSUBDATE('2009-01-01',INTERVAL2year)asdate1,SUBDATE('2009-01-0106:20:20',INTERVAL7hour)asdate2;MySQL中的日期與時(shí)間間隔類型間隔類型值描

述格

式Y(jié)EAR年YYMONTH月MMDAY日DDYEAR_MONTH年和月YY-MMDAY_HOUR日和小時(shí)DDhhDAY_MINUTE日和分鐘DDhh:mmDAY_SECOND日和秒DDhh:mm:ssHOUR小時(shí)hhMINUTE分mmSECOND秒ssHOUR_MINUTE小時(shí)和分hh:mmHOUR_SECOND小時(shí)和秒hh:ssMINUTE_SECOND分鐘和秒mm:ss日期與時(shí)間函數(shù)應(yīng)用DATEDIFF()函數(shù)用于計(jì)算兩個(gè)日期之間相隔天數(shù)。

例如：mysql>SELECTDATEDIFF('2022-02-04',NOW());mysql>SELECTDATEDIFF('2022-02-04','2022-02-01');TIME_TO_SEC(d)可將指定d的分秒的時(shí)間換算為秒SEC_TO_TIME(d)將指定的秒換算為“HH:MM:SS”形式的時(shí)間格式例如：mysql>SELECTTIME_TO_SEC('05:30:30'),SEC_TO_TIME(19830);mysql>SELECTTIME_TO_SEC('2000-01-0205:30:30'),SEC_TO_TIME(19830);條件判斷函數(shù)函

數(shù)功

能IF(expr,v1,v2)如果expr為真，返回v1，否則返回v2IFNULL(v1,v2)如果v1不為NULL，返回v1，否則返回v2CASEWHENexpr1THENr1[WHENexpr2THENr2][ELSErn]END根據(jù)條件表達(dá)式，將數(shù)據(jù)分為幾個(gè)檔次CASEexprWHENv1THENr1[WHENv2THENr2][ELSErn]END按等值比較條件，將數(shù)據(jù)分為幾個(gè)檔次條件判斷函數(shù)IF（）函數(shù)例：mysql>USEdb_shop;mysql>DESCstaffer;mysql>SELECTstaff_name,salary,IF(salary>5500,'high','low')FROMstaffer;mysql>SELECT*FROMstaffer;mysql>SELECTstaff_name,salary,IF(salary>5500,'high',IF(salary>=5000,'mid','low'))FROMstaffer;mysql>SELECT*FROMorders;mysql>DESCorders;mysql>SELECT

order_id,customer_id,IFNULL(order_total_price,0)FROMorders;條件判斷函數(shù)CASE表達(dá)式使用條件表達(dá)式語(yǔ)法：例：mysql>SELECTstaff_name,CASE

WHENsalary>6000THEN'high'WHENsalary>5000THEN'mid'ELSE'low'

ENDFROMstaffer;條件判斷函數(shù)使用等值比較語(yǔ)法：例：mysql>SELECTstaff_name,CASEsalaryWHEN6000.1THEN'high'WHEN5000THEN'mid'ELSE'unknow' ENDFROMstaffer;JSON函數(shù)創(chuàng)建JSON值的函數(shù)有兩個(gè)：一個(gè)用于創(chuàng)建數(shù)組形式的JSON值；另一個(gè)用于創(chuàng)建對(duì)象形式的JSON值。函

數(shù)功

能JSON_ARRAY()創(chuàng)建JSON數(shù)組JSON_OBJECT()創(chuàng)建JSON對(duì)象JSON_ARRAY_APPEND()向JSON數(shù)組中追加數(shù)據(jù)JSON_SET()修改JSON對(duì)象中的數(shù)據(jù)JSON_REMOVE()刪除JSON數(shù)組和JSON對(duì)象中的數(shù)據(jù)JSON_EXTRACT()返回JSON數(shù)組中KEY所對(duì)應(yīng)的數(shù)據(jù)JSON_SEARCH()返回JSON數(shù)組中給定數(shù)據(jù)的路徑JSON函數(shù)---創(chuàng)建對(duì)象.JSON_ARRAY創(chuàng)建JSON數(shù)組JSON_ARRAY(val1,val2,……,valn)例如：創(chuàng)建JSON數(shù)組mysql>SELECTJSON_ARRAY(1,"abc",null,true,CURTIME());--[1,"abc",null,true,"10:37:08.000000"]JSON_OBJECT創(chuàng)建JSON對(duì)象JSON_OBJECT(key1:val1,key2:val2,……,keyn:valn)例如：創(chuàng)建JSON對(duì)象mysql>SELECTJSON_OBJECT('id',8,'name','Tom');#鍵值對(duì)--{"id":8,"name":"Tom"}JSON函數(shù)--添加或修改JSON_SET(json_doc,key,val[,key,val]…...)：插入或修改JSON文檔，并返回文檔結(jié)果。--設(shè)置指定路徑的數(shù)據(jù)（不管是否存在）。如果有參數(shù)為NULL，則返回NULL。mysql>SET@j7='{"a":1,"b":[2,3]}';--單引號(hào)擴(kuò)住的字符用雙引號(hào)mysql>SELECTJSON_SET(@j7,'$.a',10,'$.c','[true,false]');--{"a":10,"b":[2,3],"c":"[true,false]"}#若項(xiàng)已有則修改，否則是添加mysql>SET@js_arr='["a",["b","c"],"d"]';#采用直接數(shù)值賦值方式建立數(shù)值mysql>SET@js_arr=JSON_SET(@js_arr,'$[2]',5,'$[3]',8);#若項(xiàng)有則修改，沒(méi)有則添加mysql>SELECT@js_arr;JSON函數(shù)--添加數(shù)組元素JSON_ARRAY_APPEND(json_doc,key,val[,key,val]......)值附加到JSON文檔中指示數(shù)組的結(jié)尾并返回結(jié)果--在指定path的jsonarray尾部追加val。如果指定path是一個(gè)jsonobject，則將其封裝成一個(gè)jsonarray再追加。如果有參數(shù)為NULL，則返回NULL。mysql>SET@j4='["a",["b","c"],"d"]';mysql>

SELECTJSON_ARRAY_APPEND(@j4,'$[1][0]',3);mysql>SET@j5='{"a":1,"b":[2,3],"c":4}';mysql>SELECTJSON_ARRAY_APPEND(@j5,'$.b','x');mysql>SELECTJSON_ARRAY_APPEND(@j5,'$.c','y');mysql>SELECTJSON_ARRAY_APPEND(@j5,'$','z');JSON函數(shù)--刪除JSON數(shù)據(jù)刪除JSON數(shù)據(jù)JSON_REMOVE(json_doc,key,val[,key,val]…...)--移除指定路徑的數(shù)據(jù)，如果某個(gè)路徑不存在則略過(guò)此路徑。如果有參數(shù)為NULL，則返回NULL。mysql>SET@j8='["a",["b","c"],"d"]';mysql>SELECTJSON_REMOVE(@j8,'$[1]');--["a","d"]mysql>SET@j7='{"a":1,"b":[2,3]}';mysql>SELECTJSON_REMOVE(@j7,'$.b');

JSON函數(shù)查找所有指定JSON數(shù)據(jù)JSON_EXTRACT(json_doc,key1[,key2]…...)--從json文檔里抽取數(shù)據(jù)。如果有參數(shù)有NULL或path不存在，則返回NULL。如果抽取出多個(gè)path，則返回的數(shù)據(jù)封閉在一個(gè)jsonarray里。mysql>set@j2='[10,20,[30,40]]';mysql>SELECTJSON_EXTRACT(@j2,'$[1]');--20mysql>SELECTJSON_EXTRACT('[10,20,[30,40]]','$[1]','$[0]');--[20,10]mysql>SELECTJSON_EXTRACT('[10,20,[30,40]]','$[2][*]');--[30,40]JSON函數(shù)查詢JSON數(shù)據(jù)的位置JSON_SEARCH(json_doc,one_or_all,str)--查詢包含指定字符串的paths，并作為一個(gè)jsonarray返回。如果有參數(shù)為NUL或path不存在，則返回NULL。--one_or_all："one"表示查詢到一個(gè)即返回；"all"表示查詢所有。--search_str：要查詢的字符串。可以用LIKE里的'%'或‘_’匹配。--path：在指定path下查。mysql>SET@j3='["abc",[{"k":"10"},"def"],{"x":"abc"},{"y":"bcd"}]';mysql>SELECTJSON_SEARCH(@j3,'one','abc');--"$[0]"mysql>SELECTJSON_SEARCH(@j3,'all','abc');--["$[0]","$[2].x"]JSON函數(shù)應(yīng)用（記錄操作）1.創(chuàng)建有json字段的表--創(chuàng)建表mysql>CREATETABLEt_json(idINTPRIMARYKEY,snameVARCHAR(20),infoJSON);

2.插入記錄--插入含有json數(shù)組的記錄mysql>INSERTINTOt_json(id,sname,info)VALUES(1,'name1',JSON_ARRAY(1,"abc",NULL,TRUE,CURTIME()));

mysql>SELECT*FROMt_json;mysql>INSERTINTOt_json(id,sname,info)VALUES(2,'name2','["a",["b","c"],"d"]');mysql>SELECT*FROMt_json;JSON函數(shù)應(yīng)用（記錄操作）--插入含有json對(duì)象的記錄mysql>INSERTINTOt_json(id,sname,info)VALUES(3,'name3',JSON_OBJECT("age",20,"time",now()));mysql>SELECT*FROMt_json;mysql>INSERTINTOt_json(id,sname,info)VALUES(4,'name4','{"age":20,"time":"2018-07-1410:52:00"}');mysql>SELECT*FROMt_json;JSON函數(shù)應(yīng)用（記錄操作）3.查詢記錄--查詢記錄mysql>SELECTsname,JSON_EXTRACT(info,'$.age')FROMt_json;mysql>SELECTsname,info->'$.age'FROMt_json;--查詢keymysql>SELECTid,json_keys(info)FROMt_json;JSON函數(shù)應(yīng)用（記錄操作）4.修改記錄--增加鍵mysql>UPDATEt_jsonSETinfo=json_set(info,'$.ip','')WHEREid=3;mysql>SELECT*FROMt_json;--變更值mysql>UPDATEt_jsonSETinfo=json_set(info,'$.ip','')WHEREid=3;--變更記錄的整個(gè)json值mysql>

UPDATEt_jsonSETinfo='{"age":23,"time":"2018-07-19"}'WHEREid=4mysql>SELECT

*FROMt_json;JSON函數(shù)應(yīng)用（記錄操作）5.刪除鍵mysql>UPDATEt_jsonSETinfo=json_remove(info,'$.ip')WHEREid=2;mysql>SELECT*FROMt_json