《MySQL数据库应用与管理项目化教程》课件-项目7-2 常用函数

项目七--2

MySQL常用函数任务目标任务一数值函数任务二字符串函数任务三 日期与时间函数任务四 条件判断函数任务五 JSON函数任务六 其他函数运算符

主要内容:数值函数应用字符串函数应用日期与时间函数应用条件判断函数应用JSON函数应用其他函数应用重点难点:函数应用；基本查询查阅语法：官网文档：/doc/refman/8.0/en/functions.html语法：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}]]数值函数函

数功

能ABS(x)返回数值x的绝对值MOD(x,y)返回数值x除以数值y后的余数CEIL(x)返回大于数值x的最小整数值FLOOR(x)返回小于数值x的最大整数值RAND()返回0～1内的随机数ROUND(x)返回对参数x进行四舍五入后的值，ROUND(x)返回整数值，ROUND(x,y)返回参数x四舍五入后保留y位小数的值TRUNCATE(x,y)对数值x进行截取，保留小数点后y位数字数值函数应用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();#请执行几次，观察结果，返回值是0～1内的小数，并且每次的运行结果都不同mysql>SELECTROUND(100.144),ROUND(100.568),ROUND(100.144,2),ROUND(100.568,2);#四舍五入mysql>SELECTTRUNCATE(1.42,1),TRUNCATE(1.58,1);#直接截取值，并不进行四舍五入字符串函数函

数功

能LENGTH(str)，CHAR_LENGTH(str)返回字符串长度或字符个数CONCAT(str1,str2…strn)，CONCAT_WS(x,str1,str2…strn)合并字符串INSERT(str,x,y,instr)，REPLACE(str,a,b)替换字符串LOWER(str)，UPPER(str)字符大小写转换LEFT(str,x)，RIGHT(str,x)，SUBSTRING(str,x,y)获取字符串的一部分LPAD(str1,n,str2)，RPAD(str1,n,str2)填充字符串LTRIM(str)，RTRIM(str)，TRIM(str)删除字符串左侧、右侧或两侧空格REPEAT(str,n)返回字符串str重复n次的结果LOCATE(str1,str)返回子字符串的开始位置REVERSE(str)反转字符串字符串函数应用mysql>SELECTLENGTH('abcdef'),LENGTH('字符长度');#UTF-8和utf8mb4：一个汉字=3个字节，英文是一个字节;GBK：一个汉字=2个字节，英文是一个字节mysql>SELECTCHAR_LENGTH('abcdef'),CHAR_LENGTH('字符个数');mysql>SELECTCONCAT('abcd','efg'),CONCAT('abcd',NULL,'efg');#连接字符串mysql>SELECTCONCAT_WS('_','ab','cd','ef'),CONCAT_WS('_','gh',NULL,'ij');#用第一个分隔符拼接字符串mysql>SELECTINSERT('beijinglvyoushichang66666',13,8,'gonglue');#INSERT(str,x,y,instr)的作用是将字符串str从第x位置开始，y个字符长的子串替换为字符串instr。字符串函数应用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位置开始，后面y个字符长度的子串mysql>SELECTLPAD('abcdefg',5,'km'),LPAD('abcdefg',10,'km');#使用字符串str2对字符串str1最左边进行填充，直到字符串str1总长度达到n个字符长度。mysql>SELECTRPAD('abcdefg',5,'km'),RPAD('abcdefg',10,'km');#最右边进行填充，直到字符串str1总长度达到n个字符长度。字符串函数应用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)用于删除字符串开头和结尾的空格，另外还可以删除字符串两侧指定字符mysql>SELECTREPEAT('abc',3);#返回字符串str重复n次的结果mysql>SELECTLOCATE('abc','ababcabd'),LOCATE('efg','ababcabd');#返回子串str1在字符串str中的开始位置mysql>SELECTREVERSE('abcdefg');#字符反转结果日期与时间函数函

数功

能CURDATE()获取当前日期CURTIME()获取当前时间NOW()获取当前的日期和时间UNIX_TIMESTAMP(date)获取日期date的UNIX时间戳YEAR(d)，MONTH(d)，WEEK(d)，DAY(d)，HOUR(d)，MINUTE(d)，SECOND(d)返回指定日期的年份、月份、星期、日、时、分和秒DATE_FORMAT(d,format)按format指定的格式显示日期d的值ADDDATE(date,INTERVALexprunit)，SUBDATE(date,INTERVALexprunit)获取一个日期或时间值加上一个时间间隔的时间值TIME_TO_SEC(d),SEC_TO_TIME(d)获取将“HH:MM:SS”格式的时间换算为秒，或将秒数换算为“HH:MM:SS”格式的值日期与时间函数应用mysql>SELECTCURDATE()AS当前日期,CURDATE()AS当前时间,NOW()AS当前日期时间;mysql>SELECTYEAR('07-01-15'),YEAR(now());mysql>SELECTNOW(),UNIX_TIMESTAMP(NOW());#获取UNIX格式的当前日期和时间;#UNIX时间戳是从1970年1月1日（UTC/GMT的午夜）开始到当前时间所经过的秒数（不考虑闰秒）。一分钟表示为UNIX时间戳为60秒，一小时表示为UNIX时间戳为3600秒，一天表示为UNIX时间戳为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;#获取时间的时、分、秒的值日期与时间函数应用日期格式化函数DATE_FORMAT(d,format)按字符串format格式化日期d的值，其中的format格式符及其作用见下表：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');日期与时间格式符及其意义参数值意

义%Y四位数形式的年份%y两位数形式的年份%c数字形式（0～12）的月份%M英文形式（January～December）的月份名%m数字形式（00～12）的月份%W一周中每天为周几，用英文表示（Sunday,Monday,…,Saturday）%D月中的第几天，英文后缀形式，如0th，1st，2nd，3rd…%d两位数字表示月中的第几天，形式为00～31%j一年的第几日（001～366）%H24小时形式的小时（00～23）%h12小时形式的小时（01～12）%r12小时形式的小时，后缀为上午（AM）或下午（PM）%i两位数字形式的分（00～59）%S两位数字形式的秒（00～59）日期与时间函数应用计算日期和时间的函数ADDDATE(date,INTERVALexprunit)对日期时间执行加运算例如：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)对日期时间减运算例如：mysql>SELECTSUBDATE('2009-01-01',INTERVAL2year)asdate1,SUBDATE('2009-01-0106:20:20',INTERVAL7hour)asdate2;MySQL中的日期与时间间隔类型间隔类型值描

述格

式YEAR年YYMONTH月MMDAY日DDYEAR_MONTH年和月YY-MMDAY_HOUR日和小时DDhhDAY_MINUTE日和分钟DDhh:mmDAY_SECOND日和秒DDhh:mm:ssHOUR小时hhMINUTE分mmSECOND秒ssHOUR_MINUTE小时和分hh:mmHOUR_SECOND小时和秒hh:ssMINUTE_SECOND分钟和秒mm:ss日期与时间函数应用DATEDIFF()函数用于计算两个日期之间相隔天数。

例如：mysql>SELECTDATEDIFF('2022-02-04',NOW());mysql>SELECTDATEDIFF('2022-02-04','2022-02-01');TIME_TO_SEC(d)可将指定d的分秒的时间换算为秒SEC_TO_TIME(d)将指定的秒换算为“HH:MM:SS”形式的时间格式例如：mysql>SELECTTIME_TO_SEC('05:30:30'),SEC_TO_TIME(19830);mysql>SELECTTIME_TO_SEC('2000-01-0205:30:30'),SEC_TO_TIME(19830);条件判断函数函

数功

能IF(expr,v1,v2)如果expr为真，返回v1，否则返回v2IFNULL(v1,v2)如果v1不为NULL，返回v1，否则返回v2CASEWHENexpr1THENr1[WHENexpr2THENr2][ELSErn]END根据条件表达式，将数据分为几个档次CASEexprWHENv1THENr1[WHENv2THENr2][ELSErn]END按等值比较条件，将数据分为几个档次条件判断函数IF（）函数例：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;条件判断函数CASE表达式使用条件表达式语法：例：mysql>SELECTstaff_name,CASE

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

ENDFROMstaffer;条件判断函数使用等值比较语法：例：mysql>SELECTstaff_name,CASEsalaryWHEN6000.1THEN'high'WHEN5000THEN'mid'ELSE'unknow' ENDFROMstaffer;JSON函数创建JSON值的函数有两个：一个用于创建数组形式的JSON值；另一个用于创建对象形式的JSON值。函

数功

能JSON_ARRAY()创建JSON数组JSON_OBJECT()创建JSON对象JSON_ARRAY_APPEND()向JSON数组中追加数据JSON_SET()修改JSON对象中的数据JSON_REMOVE()删除JSON数组和JSON对象中的数据JSON_EXTRACT()返回JSON数组中KEY所对应的数据JSON_SEARCH()返回JSON数组中给定数据的路径JSON函数---创建对象.JSON_ARRAY创建JSON数组JSON_ARRAY(val1,val2,……,valn)例如：创建JSON数组mysql>SELECTJSON_ARRAY(1,"abc",null,true,CURTIME());--[1,"abc",null,true,"10:37:08.000000"]JSON_OBJECT创建JSON对象JSON_OBJECT(key1:val1,key2:val2,……,keyn:valn)例如：创建JSON对象mysql>SELECTJSON_OBJECT('id',8,'name','Tom');#键值对--{"id":8,"name":"Tom"}JSON函数--添加或修改JSON_SET(json_doc,key,val[,key,val]…...)：插入或修改JSON文档，并返回文档结果。--设置指定路径的数据（不管是否存在）。如果有参数为NULL，则返回NULL。mysql>SET@j7='{"a":1,"b":[2,3]}';--单引号扩住的字符用双引号mysql>SELECTJSON_SET(@j7,'$.a',10,'$.c','[true,false]');--{"a":10,"b":[2,3],"c":"[true,false]"}#若项已有则修改，否则是添加mysql>SET@js_arr='["a",["b","c"],"d"]';#采用直接数值赋值方式建立数值mysql>SET@js_arr=JSON_SET(@js_arr,'$[2]',5,'$[3]',8);#若项有则修改，没有则添加mysql>SELECT@js_arr;JSON函数--添加数组元素JSON_ARRAY_APPEND(json_doc,key,val[,key,val]......)值附加到JSON文档中指示数组的结尾并返回结果--在指定path的jsonarray尾部追加val。如果指定path是一个jsonobject，则将其封装成一个jsonarray再追加。如果有参数为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函数--删除JSON数据删除JSON数据JSON_REMOVE(json_doc,key,val[,key,val]…...)--移除指定路径的数据，如果某个路径不存在则略过此路径。如果有参数为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函数查找所有指定JSON数据JSON_EXTRACT(json_doc,key1[,key2]…...)--从json文档里抽取数据。如果有参数有NULL或path不存在，则返回NULL。如果抽取出多个path，则返回的数据封闭在一个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函数查询JSON数据的位置JSON_SEARCH(json_doc,one_or_all,str)--查询包含指定字符串的paths，并作为一个jsonarray返回。如果有参数为NUL或path不存在，则返回NULL。--one_or_all："one"表示查询到一个即返回；"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函数应用（记录操作）1.创建有json字段的表--创建表mysql>CREATETABLEt_json(idINTPRIMARYKEY,snameVARCHAR(20),infoJSON);

2.插入记录--插入含有json数组的记录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函数应用（记录操作）--插入含有json对象的记录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函数应用（记录操作）3.查询记录--查询记录mysql>SELECTsname,JSON_EXTRACT(info,'$.age')FROMt_json;mysql>SELECTsname,info->'$.age'FROMt_json;--查询keymysql>SELECTid,json_keys(info)FROMt_json;JSON函数应用（记录操作）4.修改记录--增加键mysql>UPDATEt_jsonSETinfo=json_set(info,'$.ip','')WHEREid=3;mysql>SELECT*FROMt_json;--变更值mysql>UPDATEt_jsonSETinfo=json_set(info,'$.ip','')WHEREid=3;--变更记录的整个json值mysql>

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

*FROMt_json;JSON函数应用（记录操作）5.删除键mysql>UPDATEt_jsonSETinfo=json_remove(info,'$.ip')WHEREid=2;mysql>SELECT*FROMt_json