MySQL实用篇小知识，大挑战！本文正在参与“程序员必备小知识”创作活动内置函数 MySQL为我们提供了许多内置函数，

小知识，大挑战！本文正在参与“程序员必备小知识”创作活动

内置函数

MySQL为我们提供了许多内置函数，能够满足我们的大部分查询需求，下面只列出常用的一些内置函数及其对应的使用。MySQL的内置函数主要可以分为数值型函数、日期函数、字符串函数、聚合函数、高级函数这几种。

数值型函数

1.ABS(x) 求绝对值

select ABS(-1) 返回 1

2.SQRT(x) 求平方根

平方根函数SQRT(x)返回非负数x的二次方根，如果x为负数，则没有平方根，返回结果为NULL

select SQRT(2) 返回 4
select SQRT(-2) 返回 NULL

3.MOD(x,y) 求余函数

求余函数 MOD(x,y) 返回 x 被 y 除后的余数，MOD() 对于带有小数部分的数值也起作用，它返回除法运算后的余数

select MOD(25,4) 返回 1
SELECT MOD(25.5,5.5) 返回 3.5

4.CEIL(x)、CEILING(x) 向上取整

返回不小于x的最小整数值(即向上取整)，返回值转化为一个 BIGINT

SELECT CEIL(3.1),CEIL(3.5),CEIL(3.9) 返回4，4，4

5.FLOOR(x) 向下整数

返回小于x的最大整数值

SELECT FLOOR(3.1),FLOOR(3.13),FLOOR(3.9) 返回3，3，3

6.ROUND(x) 四舍五入

ROUND(x) 函数返回最接近于参数 x 的整数，ROUND(x,d) 函数对参数x进行四舍五入的操作，返回值保留小数点后面指定的d位，x值的小数位不够d位的补0。如果d为负数，则保留小数点左边d位，先进行四舍五入操作，再将相应的位数值取零。

SELECT ROUND(3.1),ROUND(3.5),ROUND(3.9),ROUND(3.1234,2) 返回3 , 4 , 4 , 3.12
SELECT ROUND(93.12,-2), 93.12向进行四舍五入等于93，再将相应的位数值取0，返回100

日期函数

1.CURDATE()、CURRENT_DATE() 返回当前系统日期

CURDATE() 和 CURRENT_DATE() 函数的作用相同，将当前系统日期按照YYYY-MM-DD或YYYYMMDD格式的值返回

SELECT CURDATE(),CURRENT_DATE() 返回当前系统日期2021-09-20
SELECT CURRENT_DATE()+0 以YYYYMMDD格式的返回当前系统日期20210920

2.CURTIME()、CURRENT_TIME() 返回当前系统时间

CURTIME() 和 CURRENT_TIME() 函数的作用相同，将当前时间以HH：MM：SS、HHMMSS格式返回

SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME()+0 返回09:54:26，09:54:26，095426

3.NOW()、SYSDATE() 返回当前日期和时间值

NOW() 和 SYSDATE() 函数的作用相同，都是返回当前日期和时间值，格式为YYYY-MM-DD HH：MM：SS或YYYYMMDDHHMMSS

SELECT NOW(),SYSDATE(),NOW()+0 返回2021-09-20 09:57:25，2021-09-20 09:57:25，20210920095725
🚦NOW()和SYSDATE() 的一点区别，NOW() 函数始终获取的是 SQL 语句开始执行的时间，而 SYSDATE() 函数则是动态获取的实时时间。

4.MONTH(date)获取指定日期中的月份

MONTH(date)函数为获取指定日期中的月份，范围为 1～12

SELECT MONTH(NOW()) 返回当前的月份9
SELECT MONTH('2021-11-11') 返回月份11

5.MONTHNAME(date) 函数返回日期date对应月份的英文全名

SELECT MONTHNAME('2021-11-11') 返回11月英文全面November

6.DAYNAME(date) 返回日期date对应的工作日英文名称

SELECT DAYNAME('2021-09-20') 返回星期一Monday

7.DAYOFWEEK(date) 返回日期对应在一周中的索引（位置）

DAYOFWEEK(date) 返回日期对应在一周中的索引（位置），1代表星期日、2代表星期一...7代表星期六

SELECT DAYOFWEEK('2021-09-20') 返回2，代表星期一

8.WEEKDAY(date) 获取指定日期在一周内的索引位置

WEEKDAY(data) 返回指定日期对应的工作日索引。与DAYOFWEEK(data)不同的是，WEEKDAY(data)返回的0 表示周一，1 表示周二，……，6 表示周日。

SELECT WEEKDAY('2021-09-20')返回0，表示周一

9.WEEK(date) 返回日期date是一年中的第几周

SELECT WEEK('2021-09-20') 返回38，表示2021-09-20是一年中的第38周

10.DAYOFYEAR(d) 返回日期date是一年中的第几天

返回日期date是一年中的第几天,范围为 1～366

SELECT DAYOFYEAR('2021-09-20') 返回263，表示2021-09-20是一年中的第263天

11.DAYOFMONTH(date) 返回日期date是一个月中的第几天

SELECT DAYOFMONTH('2021-09-20') 返回20，表示2021-09-20是9月的第20天

12.ADDTIME(time,expr) 时间加法运算

ADDTIME(time,expr) 函数用于执行时间的加法运算，添加expr到原始时间time并返回结果

SELECT ADDTIME('09:30:20','01:05:20') 返回时间10：35：40

13.SUBTIME(time,expr) 时间减法运算

SELECT SUBTIME('10:28:30','01:05:20') 返回时间9:23:10

14.DATEDIFF(date1，date2) 返回两个日期之间的间隔天数

DATEDIFF(date1，date2) 返回起始时间date1和结束时间 date2之间的天数。date1 和 date2 为日期，计算时只用到这些值的日期部分。

SELECT DATEDIFF(NOW(),'1999-03-26') 返回8214天

15.DATE_FORMAT(data,format) 格式化指定的日期

DATE_FORMAT() 函数的两个参数：

date：是要格式化的有效日期值
format：所有转换的格式。

format格式

%a 工作日的缩写(Sun~Sat)
%b 月份的缩写名称（Jan…Dec)
%c 月份，数字形式（0~12)
%D 带有英语后缀的该月日期(0th, 2st, 3nd,…）
%d 该月日期，数字形式（00~31)
%e 该月日期，数字形式（(0~31)
%f 微秒（000000 …999999)
%H 以 2 位数表示 24 小时（00~23)
%h 以 2 位数表示 12 小时（01~12)
%i 分钟，数字形式(00~59)
%j —年中的天数（001~366)
%k 以 24 小时（0~23)表示
%l 以12小时（1~12)表示
%M 月份名称（January~December)
%m 月份，数字形式（00~12)
%p 上午（AM) 或下午（PM)
%r 时间，12小时制（小时 (hh): 分钟 (mm) : 秒数 (ss) 后加 AM 或 PM)
%S 以 2 位数形式表示秒（00~59)
%T 时间，24 小时制（小时 (hh): 分钟 (mm): 秒数 (ss))
%U 周（00~53)，其中周日为每周的第一天
%u 周（00~53)，其中周一为每周的第一天
%V 周（01~53)，其中周日为每周的第一天，和％X同时使用
%v 周（01~53)，其中周一为每周的第一天，和%x同时使用
%W 星期标识（周日、周一、周二…周六）
%w —周中的每日（0= 周日…6= 周六）
%X 该周的年份，其中周日为每周的第一天，数字形式，4 位数，和％V同时使用
%x 该周的年份，其中周一为每周的第一天，数字形式，4位数，和%v同时使用
%Y 4 位数形式表示年份
%y 2 位数形式表示年份
%% %一个文字字符

例子

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s') 返回格式化好的日期2021-09-20 10:34:48

16.TO_DAYS(data) 计算日期data距离0000 年 1 月 1 日的天数

SELECT TO_DAYS(NOW())

17.DATE_SUB(date,INTERVAL expr type) 将日期data减去指定的时间间隔expr

SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) 返回7天前的日期
SELECT DATE_SUB('2021-09-20', INTERVAL 1 YEAR) 返回1年前的日期

18. YEARWEEK(data,model)

YEARWEEK(data,model)函数，返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一

SELECT YEARWEEK(NOW()) 返回202138，表示当前是2021年的第38周中的一天

例子

查询本月的销售总额

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE STATUS = 3 
  AND DATE_FORMAT(payment_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') ;

查询今日销售总额

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE STATUS = 3 
  AND TO_DAYS(payment_time) = TO_DAYS(NOW())

查询昨日销售总额

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE STATUS = 3 
  AND TO_DAYS(NOW()) - TO_DAYS(payment_time) <= 1

查询近7天销售总额

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE STATUS = 3 
  AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(payment_time) ;

查询去年销售总额

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE YEAR(payment_time) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR))

查询上月销售总额

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE DATE_FORMAT(payment_time, '%Y-%m') = DATE_FORMAT(
    DATE_SUB(CURDATE(), INTERVAL 1 MONTH),
    '%Y-%m') ;

查询本周销售总额

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE STATUS = 3 
  AND YEARWEEK(
    DATE_FORMAT(create_time, '%Y-%m-%d'),
    1
  ) = YEARWEEK(NOW(), 1)

字符串函数

1.CONCAT(s1,s2...sn) 字符串拼接

CONCAT(s1,s2...sn)函数实现将指定的s1,s2等多个字符串合并为一个字符串。

SELECT CONCAT('不','喝','奶','茶','的','Programmer') AS userName，返回不喝奶茶的Programmer

2.CONCAT_WS(x,s1,s2...sn) 字符串拼接

该函数同样跟CONCAT(s1,s2,...)函数实现字符串拼接，但是每个字符串之间要加上 x，x 可以是分隔符。

SELECT CONCAT_WS('-','1','2','3','4') AS s ，返回1-2-3-4

3.TRIM(s) 去掉字符串 s 开始和结尾处的空格

SELECT TRIM(' abc ')

4.UPPER(s)、UCASE(s)将字符串转换为大写

SELECT UPPER('abc') ,UCASE('abc')

5.LOWER(s)、LCASE(s) 将字符串s的所有字母变成小写字母

SELECT LOWER('AbC'),LCASE('DeF')

6.STRCMP(s1,s2) 字符串比较

比较字符串s1和s2，如果s1与s2相等返回0，如果s1>s2返回 1如果 s1<s2 返回 -1

SELECT STRCMP('123','234') ,返回-1，str1 < str2
SELECT STRCMP('abc','abc') ,返回0，str == str2
SELECT STRCMP('321','123') ，返回1，str1 > str2

7.SUBSTR(s, start, length) 字符串截取

SUBSTR(s, start, length)实现从字符串 s 的 start 位置截取长度为 length 的子字符串。

SELECT SUBSTR('abcdefghijklmn',2,3) 返回bcd,从字符串abcdefghijklmn中的第2个位置开始截取长度为3的子字符串。

聚合函数

1.MAX()查询指定列的最大值

2.MIN()查询指定列的最小值

3.COUNT()统计查询结果的行数

COUNT(*) 计算表中总的行数，无论某列有数值或者为空值。
COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行

4.SUM()求和，返回指定列的总和

SUM()函数中如果连一条记录都没有匹配到，则SUM()函数返回的结果为null，而不是0

5.AVG()求平均值，返回指定列数据的平均值

高级函数

1.CAST(x AS type) 转换数据类型

SELECT CAST('2021-9-20' AS DATE) 将字符串所表示的日期转换为DATE类型的日期

2.COALESCE(expr1, expr2, ...., expr_n)

返回参数中的第一个非空表达式（从左向右）

SELECT COALESCE(NULL,NULL,'a','b',NULL) 返回a

3.IF(expr,v1,v2)

如果表达式expr为true，则返回结果 v1，否则，返回结果v2

SELECT IF(1>2,'true','false')

4.IFNULL(v1,v2)

如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。

SELECT IFNULL(NULL,'false')

5.ISNULL(expression)

判断表达式是否为 NULL,返回1表示不为null，返回0表示为null

SELECT ISNULL(NULL)

6.LAST_INSERT_ID()

返回最近生成的 AUTO_INCREMENT 值，在存储过程中经常用到。比如在存储过程中，当前执行的语句需要使用到上一条语句执行产生的AUTO_INCREMENT 值，这时就可以通过该函数来获取。

🏁以上就是对MySQL一些常用的内置函数的简单介绍，如果有错误的地方，还请留言指正，如果觉得本文对你有帮助那就点个赞👍吧😋😻😍

默认标题_动态分割线_2021-07-15-0.gif