Loading... ## 1、数值函数 ### 1.1 基本函数 | 函数 | 注释 | | --- | --- | | ABS(x) | 返回x的绝对值 | | SIGN(X) | 返回X的符号。正数返回1,负数返回-1,0返回0 | | PI() | 返回圆周率的值 | | CEIL(x), CEILING(x) | 返回大于或等于某个值的最小整数 | | FLOOR(x) | 返回小于或等于某个值的最大整数 | | LEAST(e1,e2,e3…) | 返回列表中的最小值 | | GREATEST(e1,e2,e3…) | 返回列表中的最大值 | | MOD(x,y) | 返回X除以Y后的余数 | | RAND() | 返回0~1的随机值 | | RAND(x) | 返回0~1的随机值,其中x的值用作种子值,相同的X值会产生相同的随机数 | | ROUND(x) | 返回一个对x的值进行四舍五入后,最接近于X的整数 | | ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值,并保留到小数点后面Y位 | | TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 | | SQRT(x) | 返回x的平方根。当X的值为负数时,返回NULL | **举例验证:** <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-0ee38f0c34cea03edb35e250ae433d6324" aria-expanded="true"><div class="accordion-toggle"><span style="">SQL举例</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-0ee38f0c34cea03edb35e250ae433d6324" class="collapse collapse-content"><p></p> ```sql SELECT ABS( 3.14 ), ABS(- 3.14 ), ABS( 0 ), SIGN(- 1 ), SIGN( 1 ), SIGN( 0 ), SIGN( "1" ), PI(), CEIL( 3.14 ), CEILING(- 3.14 ), FLOOR( 3.14 ), FLOOR(- 3.14 ), LEAST( 1, 3.14, 2, 3 ), GREATEST( 1, 3.14, 2.1212, 3 ) # PI() 函数保留6位小数且四舍五入了 # LEAST(),GREATEST() 函数保留小数位取决于比较数中最长的小数位 ``` ![结论](https://cdn.ganhua.work/blog_static/images/2022/1/011701.png) ```sql SELECT MOD( 2, 3 ), MOD ( 7, 4 ), RAND(), RAND( 2 ), RAND( 2 ), ROUND( 3.142 ), ROUND( 3.1415926, 2 ), TRUNCATE ( 3.1415926, 4 ), SQRT( 9 ), SQRT(- 3 ) ``` ![结论](https://cdn.ganhua.work/blog_static/images/2022/1/011702.png) <p></p></div></div></div> ### 1.2 角度与弧度互换函数 | 函数 | 注释 | | --- | --- | | RADIANS(x) | 将角度转化为弧度,其中,参数x为角度值 | | DEGREES(x) | 将弧度转化为角度,其中,参数x为弧度值 | **举例验证:** <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-9ccbe8260c60085eae2f0067121b567016" aria-expanded="true"><div class="accordion-toggle"><span style="">SQL举例</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-9ccbe8260c60085eae2f0067121b567016" class="collapse collapse-content"><p></p> ```sql SELECT RADIANS(180),DEGREES(PI()),DEGREES(RADIANS(90)); ``` ![结论](https://cdn.ganhua.work/blog_static/images/2022/1/011703.png) <p></p></div></div></div> ### 1.3 三角函数 | 函数 | 注释 | | --- | --- | | SIN(x) | 返回x的正弦值,其中,参数x为弧度值 | | ASIN(x) | 返回x的反正弦值,即获取正弦为x的值。如果x的值不在-1到1之间,则返回NULL | | COS(x) | 返回x的余弦值,其中,参数x为弧度值 | | ACOS(x) | 返回x的反余弦值,即获取余弦为x的值。如果x的值不在-1到1之间,则返回NULL | | TAN(x) | 返回x的正切值,其中,参数x为弧度值 | | ATAN(x) | 返回x的反正切值,即返回正切值为x的值 | | ATAN2(m,n) | 返回两个参数的反正切值 | | COT(x) | 返回x的余切值,其中,X为弧度值 | ### 1.4 指数与对数 | 函数 | 注释 | | --- | --- | | POW(x,y),POWER(X,Y) | 返回x的y次方 | | EXP(X) | 返回e的X次方,其中e是一个常数,EXP(1) | | LN(X),LOG(X) | 返回以e为底的X的对数,当X <= 0 时,返回的结果为NULL | | LOG10(X) | 返回以10为底的X的对数,当X <= 0 时,返回的结果为NULL | | LOG2(X) | 返回以2为底的X的对数,当X <= 0 时,返回NULL | ### 1.5 进制间的转换 | 函数 | 注释 | | --- | --- | | BIN(x) | 返回x的二进制编码 | | HEX(x) | 返回x的十六进制编码 | | OCT(x) | 返回x的八进制编码 | | CONV(x,f1,f2) | 返回f1进制数变成f2进制数 | **举例验证:** <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-caa31f87d80681f879510e817ff4be4646" aria-expanded="true"><div class="accordion-toggle"><span style="">SQL举例</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-caa31f87d80681f879510e817ff4be4646" class="collapse collapse-content"><p></p> ```sql SELECT BIN(10),HEX(10),OCT(10),CONV('A',16,10) ``` ![结论](https://cdn.ganhua.work/blog_static/images/2022/1/011704.png) <p></p></div></div></div> ## 2、字符串函数 | 函数 | 注释 | | --- | --- | | ASCII(S) | 返回字符串S中的第一个字符的ASCII码值 | | CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 | | LENGTH(s) | 返回字符串s的字节数,和字符集有关 | | CONCAT(s1,s2,......,sn) | 连接s1,s2,......,sn为一个字符串 | | CONCAT_WS(x,s1,s2,......,sn) | 同CONCAT(s1,s2,...)函数,但是每个字符串之间要加上x | | INSERT(str,idx,len,replacestr) | 将字符串str从第idx位置开始,len个字符长的子串替换为字符串replacestr | | REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a | | UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 | | LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 | | LEFT(str,n) | 返回字符串str最左边的n个字符 | | RIGHT(str,n) | 返回字符串str最右边的n个字符 | | LPAD(str, len, pad) | 用字符串pad对str最左边进行填充,直到str的长度为len个字符 | | RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充,直到str的长度为len个字符 | | LTRIM(s) | 去掉字符串s左侧的空格 | | RTRIM(s) | 去掉字符串s右侧的空格 | | TRIM(s) | 去掉字符串s开始与结尾的空格 | | TRIM(s1 FROM s) | 去掉字符串s开始与结尾的s1 | | TRIM(LEADING s1 FROM s) | 去掉字符串s开始处的s1 | | TRIM(TRAILING s1 FROM s) | 去掉字符串s结尾处的s1 | | REPEAT(str, n) | 返回str重复n次的结果 | | SPACE(n) | 返回n个空格 | | STRCMP(s1,s2) | 比较字符串s1,s2的ASCII码值的大小 | | SUBSTR(s,index,len) | 返回从字符串s的index位置其len个字符,作用与SUBSTRING(s,n,len)、MID(s,n,len)相同| | LOCATE(substr,str) | 返回字符串substr在字符串str中首次出现的位置,作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到,返回0 | | ELT(m,s1,s2,…,sn) | 返回指定位置的字符串,如果m=1,则返回s1,如果m=2,则返回s2,如果m=n,则返回sn | | FIELD(s,s1,s2,…,sn) | 返回字符串s在字符串列表中第一次出现的位置 | | FIND_IN_SET(s1,s2) | 返回字符串s1在字符串s2中出现的位置。其中,字符串s2是一个以逗号分隔的字符串 | | REVERSE(s) | 返回s反转后的字符串 | | NULLIF(value1,value2) | 比较两个字符串,如果value1与value2相等,则返回NULL,否则返回value1 | <div class="tip inlineBlock warning"> MySQL中,字符串的位置是从1开始的。 </div> **举例验证:** <div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-f9973888f1482638b20e63d461714ce136" aria-expanded="true"><div class="accordion-toggle"><span style="">SQL举例</span> <i class="pull-right fontello icon-fw fontello-angle-right"></i> </div> </div> <div class="panel-body collapse-panel-body"> <div id="collapse-f9973888f1482638b20e63d461714ce136" class="collapse collapse-content"><p></p> ```sql SELECT ASCII( 'Ab' ), CHAR_LENGTH( 'ganhua' ), CHARACTER_LENGTH( 'ganhua' ), LENGTH( 'ganhua' ), CONCAT( 'a', 'b' ), CONCAT_WS( '-', 'a', 'b', 'c' ), INSERT ( 'http://baidu.com/a/b', 1, 16, 'https://ganhua.work' ), REPLACE ( 'http://baidu.com/a/b', 'http://baidu.com', 'https://ganhua.work' ), LPAD( 'b', 10, 'a' ), FIND_IN_SET( 'a', 'b,c,a' ), FIND_IN_SET( 'a', 'bca' ), NULLIF( 'a', 'a' ), NULLIF( 'a', 'b' ) ``` ![结论](https://cdn.ganhua.work/blog_static/images/2022/1/011705.png) <p></p></div></div></div> ## 3、日期和时间函数 ### 3.1 获取日期、时间 | 函数 | 注释 | | --- | --- | | CURDATE(), CURRENT_DATE() | 返回当前日期,只包含年、月、日 | | CURTIME() , CURRENT_TIME() | 返回当前时间,只包含时、分、秒 | | NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期和时间 | | UTC_DATE() | 返回UTC(世界标准时间)日期 | | UTC_TIME() | 返回UTC(世界标准时间)时间 | ### 3.2 日期与时间戳的转换 | 函数 | 注释 | | --- | --- | | UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - 1642408217 | | UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回。 | | FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 | ```sql SELECT FROM_UNIXTIME(1642408217); # 2022-01-17 16:30:17 ``` ### 3.3 获取月份、星期、星期数、天数等函数 | 函数 | 注释 | | --- | --- | | YEAR(date) / MONTH(date) / DAY(date) | 返回具体的日期值 | | HOUR(time) / MINUTE(time) / SECOND(time) | 返回具体的时间值 | | MONTHNAME(date) | 返回月份:January,... | | DAYNAME(date) | 返回星期几:MONDAY,TUESDAY.....SUNDAY | | WEEKDAY(date) | 返回周几,注意,周1是0,周2是1,。。。周日是6 | | QUARTER(date) | 返回日期对应的季度,范围为1~4 | | WEEK(date) , WEEKOFYEAR(date) | 返回一年中的第几周 | | DAYOFYEAR(date) | 返回日期是一年中的第几天 | | DAYOFMONTH(date) | 返回日期位于所在月份的第几天 | | DAYOFWEEK(date) | 返回周几,注意:周日是1,周一是2,。。。周六是7 | | EXTRACT(unit FROM date) | 返回指定日期中特定的部分,unit 指定返回的值 | **EXTRACT(unit FROM date)函数中unit的取值与含义:** | Unit 值 | 注释 | | --- |--- | | MICROSECOND| 微秒 | | SECOND | 秒 | | MINUTE | 分钟 | | HOUR | 小时 | | DAY | 日 | | WEEK | 周 | | MONTH | 月 | | QUARTER | 刻 | | YEAR | 年 | | SECOND_MICROSECOND | 秒和微妙 | | MINUTE_MICROSECOND | 分钟和微妙 | | MINUTE_SECOND | 分钟和秒 | | HOUR_MICROSECOND | 小时和微妙 | | HOUR_SECOND | 小时和秒 | | HOUR_MINUTE| 小时和分钟 | | DAY_MICROSECOND | 日和微妙 | | DAY_SECOND | 日和秒 | | DAY_MINUTE| 日和分钟 | | DAY_HOUR | 日和小时 | | YEAR_MONTH | 年和月 | ### 3.4 时间和秒钟转换的函数 | 函数 | 注释 | | --- | --- | | TIME_TO_SEC(time) | 将 time 转化为秒并返回结果值。转化的公式为: 小时*3600+分钟*60+秒 | | SEC_TO_TIME(seconds) | 将 seconds 描述转化为包含小时、分钟和秒的时间 | ```sql SELECT TIME_TO_SEC(NOW()); # 61492 SELECT SEC_TO_TIME(61492); # 17:04:52 ``` ### 计算日期和时间的函数 | 函数 | 注释 | | --- | --- | | DATE_ADD(datetime, INTERVAL expr type),ADDDATE(date,INTERVAL expr type)| 返回与给定日期时间相差INTERVAL时间段的日期时间 | | DATE_SUB(date,INTERVAL expr type),SUBDATE(date,INTERVAL expr type) | 返回与date相差INTERVAL时间间隔的日期 | **举例验证:** ```sql SELECT DATE_SUB( '2022-01-18', INTERVAL - 31 DAY ) AS col1, SUBDATE( '2022-01-18', INTERVAL 31 DAY ) AS col2, DATE_SUB( '2021-01-18 09:01:01', INTERVAL '1 1' DAY_HOUR ) AS col3, # 必须要单引号 DATE_ADD('2021-01-18 09:01:01', INTERVAL '1 1' DAY_HOUR) AS col4 # ADD + FROM DUAL; # 2022-02-18 # 2021-12-18 # 2021-01-17 08:01:01 # 2021-01-19 10:01:01 ``` #### **第二组:** | 函数 | 注释 | | --- | --- | | ADDTIME(time1,time2) | 返回time1加上time2的时间。当time2为一个数字时,代表的是`秒`,可以是负数 | | SUBTIME(time1,time2) | 返回time1减去time2后的时间。当time2为一个数字时,代表的是 `秒` ,可以为负数 | | DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔天数 | | TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔 | | FROM_DAYS(N) | 返回从0000年1月1日起,N天以后的日期 | | TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 | | LAST_DAY(date) | 返回date所在月份的最后一天的日期 | | MAKEDATE(year,n) | 针对给定年份与所在年份中的天数返回一个日期 | | MAKETIME(hour,minute,second) | 将给定的小时、分钟和秒组合成时间并返回 | | PERIOD_ADD(time,n) | 返回time加上n后的时间 | **举例验证:** ```sql SELECT ADDTIME( '2022-01-18 10:30:10', 10 ), DATEDIFF( '2022-01-18 10:30:10', '2022-01-20 10:31:10' ), TIMEDIFF( '2022-01-18 10:30:10', '2022-01-20 10:31:10' ), FROM_DAYS( 10 ), LAST_DAY( '2022-01-20 10:31:10' ), MAKEDATE( '2022', 20 ), PERIOD_ADD( '202208', 10 ) // time的格式为:YYMM或YYYYMM) FROM DUAL; 2022-01-18 10:30:20 -2 -48:01:00 0000-00-00 2022-01-31 2022-01-20 202306 ``` 举例:查询7天内新增的用户数 ```sql SELECT count(1) FROM user where TO_DAYS(now())-TO_DAYS(create_time)<=7 ``` ### 3.5 日期的格式化与解析 | 函数 | 注释 | | --- | --- | | DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 | | TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 | | GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 | | STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析,解析为一个日期 | ## 4、流程控制函数 > 这个就没啥好说的流程处理函数主要包括:IF()、IFNULL()和CASE()函数。 > 下面是我写的一个小系统的sql,其中包含了所有的流程控制函数 ```sql SELECT ucc.id, ucc.wallet_address, ucc.usable_amount, ucc.frozen_amount, ucc.currency_contract_id, cc.tech_fee AS techFee, CASE WHEN cc.tech_fee < 1 THEN CONCAT( 0+cast( cc.tech_fee * 100 AS CHAR ), '%' ) ELSE CONCAT(0+cast(cc.tech_fee AS CHAR), ' ', cll.`name` ) END AS techFeeStr, cn.token_name AS tokenName, cc.net_id AS netId FROM pen_user_contract_capital ucc LEFT JOIN pen_currency_contract cc ON cc.id = ucc.currency_contract_id LEFT JOIN pen_currency_logo_library cll ON cc.library_id = cll.id LEFT JOIN pen_currency_net cn ON cc.net_id = cn.id WHERE ucc.currency_id = 6 AND ucc.user_id = 321 SELECT IF(1 > 0,'正确','错误') ->正确 SELECT IFNULL(null,'Hello Word') ->Hello Word ``` ## 5、加密与解密函数 | 函数 | 注释 | | --- | --- | | PASSWORD(str) | 返回字符串str的加密版本,41位长的字符串。加密结果 `不可逆` ,常用于用户的密码加密 | | MD5(str) | 返回字符串str的md5加密后的值,也是一种加密方式。若参数为NULL,则会返回NULL | | SHA(str) | 从原明文密码str计算并返回加密后的密码字符串,当参数为NULL时,返回NULL。 | | ENCODE(value,password_seed) | 返回使用password_seed作为加密密码加密value | | DECODE(value,password_seed) | 返回使用password_seed作为加密密码解密value | ## 6、MySQL信息函数 > MySQL中内置了一些可以查询MySQL信息的函数,这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。 | 函数 | 注释 | | --- | --- | | VERSION() | 返回当前MySQL的版本号 | | CONNECTION_ID() | 返回当前MySQL服务器的连接数,也就是到现在为止MySQL服务的连接次数 | | USER(),CURRENT_USER()、SYSTEM_USER(),SESSION_USER() | 返回当前连接MySQL的用户名,返回结果格式为“主机名@用户名” | | CHARSET(value) | 返回字符串value自变量的字符集 | | COLLATION(value) | 返回字符串value的比较规则 | ## 7、其他函数 | 函数 | 注释 | | --- | --- | | FORMAT(value,n) | 返回对数字value进行格式化后的结果数据。n表示 `四舍五入` 后保留到小数点后n位 | | INET_ATON(ipvalue) | 将以点分隔的IP地址转化为一个数字 | | INET_NTOA(value) | 将数字形式的IP地址转化为以点分隔的IP地址 | | BENCHMARK(n,expr) | 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间 | | CONVERT(value USING char_code) | 将value所使用的字符编码修改为char_code | ```sql SELECT INET_ATON('172.16.1.1') -> 2886729985 SELECT INET_NTOA(2886729985) -> 172.16.1.1 ``` 最后修改:2022 年 01 月 19 日 © 允许规范转载 打赏 赞赏作者 支付宝微信 赞 1 社会很单纯~复杂滴是人呐~谁能在乎我呀