mysql函数基本使用

字符串函数

MySQL中提供了大量的处理自字符串来实现字符串的处理，实现大量的常用需求，这样只需要进行简单的函数调用即可完成字符串的处理，而不用我们手动的书写复杂的处理逻辑。mysql中提供的常用的字符串函数大致包括以下这些。

ASCII(s)	返回字符串 s 的第一个字符的 ASCII 码。
CHAR_LENGTH(s)	返回字符串 s 的字符数，也就是长度	计算长度
CONCAT(s1,s2...sn)	连接多个字符串
CONCAT_WS(x, s1,s2...sn)	连接多个字符串，中间添加x 作为间隔符
FORMAT(x,n)	将数字 x 进行格式化 "#,###.##"的格式, 并 x 保留到小数点后 n 位，最后一位四舍五入。
INSERT(s1,x,len,s2)	s2 覆盖 s1 在 [x，x+len] 位置的字符，索引1开始，两端均为闭区间。
LCASE(s)、 LOWER(s)	将字符串 s 的所有字母变成小写字母
UCASE(s) UPPER(s)	将字符串转换为大写	转大写
LEFT(s,n)	返回字符串 s 的前 n 个字符
RIGHT(s,n)	返回字符串 s 的后 n 个字符
MID(s,n,len)	返回字符串s 在区间 [n, n+len] 的子串， 索引1开始，两端闭区间
LTRIM(s) RTRIM(s)	去掉字符串 s 开始处的空格 去掉字符串 s 尾部的空格	去首尾空格
TRIM(s)	去掉字符串 s 开始和结尾处的空格
REPEAT(s,n)	将字符串 s 重复 n 次
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1
REVERSE(s)	将字符串s的顺序反过来
RPAD(s1,len,s2)	字符串 s1结尾处添加 s2，使长度达到 len
STRCMP(s1,s2) （s1 - s2）	比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1	字符串比较

数值类型

数值类型提供了取整，取圆整，四则运算，指数，对数计算，三角函数等等运算的函数，需要使用查询使用方式即可：https://www.runoob.com/mysql/mysql-functions.html

常用的几个函数

AVG(expression)	返回一个表达式的平均值，expression 是一个字段
COUNT(expression)	返回查询的记录总数，expression 参数是一个字段或者 * 号
MAX(expression)	返回字段 expression 中的最大值
MIN(expression)	返回字段 expression 中的最小值
SUM(expression)	返回指定字段的总和

日期处理函数

日期处理函数通常更加的常用，通常用来做数据统计方面的需求， 例如本月的总销售额，本月每天的销售额，本周过生日的用户等等，如果没有时间函数，完成这些需求将比较复杂。

时间占位符

由于各个场景中我们使用该日期的格式并不统一，例如可以使用2020年1月1日，2020-01-01，1 Jan 2020，各种形式来表示日期或者时间，为了提取时间字符串的各部分内容的含义，使用了占位符的方式对时间的各个部分逐一匹配提取，得到标准的时间对象，方便进行时间的运算。MySQL中展示时间的标准字符串格式为：yyyy-mm-dd xx:xx:xx.mmm 分别对应了年月日 时分秒.毫秒信息。与时间对象各个部分匹配的占位符的如下

%Y   4位年   匹配2010  表示 2010年
%y   2位年   匹配19    本世纪的19年 就是2019年

%M   英文的月份名字  january - February
%m   数值月份 00 - 12
%d   数值天(00-31)
%H   小时(00-23)
%h   小时(01-12)
%I   小时(01-12)
%i   分钟(00-59)
%S   或者 %s 秒 00 - 59
%a   英文缩写的星期名字
%b   英文缩写的月份名字
%c   月份数值
%f   微秒
%p   显示 AM 或者 PM
%r   显示12小时制的时分秒时间，格式为hh:mm:ss AM  的格式，直接使用该占位符即可匹配
%T   显示24小时制的时分秒时间，格式为hh:mm:ss  时刻是24小时制的，就没有AM或者PM
%j   显示该日期是今年的第几天  匹配001 - 366 的数值

str_to_date

使用上面的占位符，例如我们需要将字符串。"2010/12/30 5:23:23 PM" 这个时刻点转化为标准的时间对象，使用函数 str_to_date(时间字符串，格式化字符串)即可，例如

str_to_date("2010/12/30 5:23:23 PM", "%Y/%m/%d %h:%i:%s %p")
每一个部分占位符会根据格式匹配字符串中的对应值，返回一个mysql标准的时间对象。

str_to_date 是将一个时间字符串根据格式转化为一个时间对象，这个时间对象可以作为mysql内置函数的参数来进行时间计算。如果是提取部分日期内容，还可以使用这样使用。

str_to_date(now(), "%Y-%m")          // now()会得到当前时刻的时间，这样实现格式化输出
str_to_date("2020-12-23", "%Y-%m")   // 只提取了年和月份信息

str_to_date("2020-12-23", "%Y-%m-%d %H:%i:%S")   // 时间字符串格式化

该函数是将一个时间字符串（也可以是时间对象）按照格式提取数据转化为一个时间的对象，从而支持MySQL对其进行处理。

date_format

date_format的功能与str_to_date相反，将一个时间对象按照指定的格式输出，返回的是一个指定格式的时间字符串。

内置函数

上面两个函数可以简单的理解为实现了时间对象的和时间字符串的方式，为了实现时间之间方便计算，mysql提供了下列的函数。

当前时刻：

CURDATE() CURRENT_DATE()	返回当前日期
CURRENT_TIME CURTIME()	返回当前时间
LOCALTIME()	返回当前日期和时间
CURRENT_TIMESTAMP()	返回当前日期和时间
LOCALTIMESTAMP()	返回当前日期和时间
NOW()	返回当前日期和时间	sql开始执行时就获取时间，之后不会变
SYSDATE	返回当前日期和时间	每次执行到该函数时，才会获取时间

示例：

 

 

定义一个时刻

MAKETIME(hour, minute, second)	组合时间，参数分别为小时、分钟、秒	SELECT MAKETIME(11, 35, 4); -> 11:35:04
MAKEDATE(year, day-of-year)	基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期

获取单个值

在部分场景中，我们需要获取某个月份，某周等这类数据，mysql提供了一系列函数来实现。例如：

HOUR(t)	返回 t 中的小时值
MINUTE(t)	返回 t 中的分钟值
MONTHNAME(d)	返回日期当中的月份名称，如 November
MONTH(d)	返回日期d中的月份值，1 到 12
QUARTER(d)	返回日期d是第几季节，返回 1 到 4
SECOND(t)	返回 t 中的秒钟值
MONTHNAME(d)	返回日期当中的月份名称，如 November

例如如果有需求是获取生日为5月份的用户，便可以使用 month(birth) == 5 这样的条件尽心删选。除了这些单个实现的函数，还有一个extract函数，通过不同的参数实现了以上函数全部的功能。

EXTRACT(type FROM d)        获取时间d 中获取指定类型（type）的数据。

extract(MONTH from now())    获取当前时刻所处的月份

type 的类型包括以下：
    MICROSECOND
    SECOND
    MINUTE
    HOUR
    DAY
    WEEK
    MONTH
    QUARTER
    YEAR v 
    SECOND_MICROSECOND
    MINUTE_MICROSECOND
    MINUTE_SECOND
    HOUR_MICROSECOND
    HOUR_SECOND
    HOUR_MINUTE
    DAY_MICROSECOND
    DAY_SECOND
    DAY_MINUTE
    DAY_HOUR
    YEAR_MONTH

这样我们可以通过这个函数提取时间对象中的任意部分内容。

计算年龄

根据用户输入出生日期计算年龄，有一个方便的函数timestampdiff(unit, begin, end)，只需要将unit值指定为year，begin指定为用户的出生日期， end指定为当前的日期，即可得到用户的年龄，并且该函数计算时，如果本年度未过生日，将会按照上一次生日年纪计算年龄，也就是说。出身日期为2019年8月1日在2020年8月1日之后年龄才为1岁，在之前为0岁。也可以将unit指定为month。

时间差的计算

计算两个时刻的时间差，以年，月，日等为单位，都有相应的函数实现，同时也可以在某个时刻上加上一个时间对象而得到另一个时间对象。

 

DATEDIFF(d1,d2)	计算日期 d1->d2 之间相隔的天数
DATE_ADD(d，INTERVAL expr type)	计算起始日期 d 加上一个时间段后的日期(默认为天，其他需要指定单位)	date_add("2020-1-1", "3")    三天后   date_add("2020-1-1", interval 30 minute)    30分钟后   date_add(now(), interval "01:30:30" hour_second)      hour_second表示从小时到秒，然后指定时间间隔为 01:30:30后。
ADDDATE(d,n)	计算起始日期 d 加上 n 天的日期	adddate("2020-10-1", interval 10 day)
ADDTIME(t,n)	时间 t 加上 n 秒的时间
SUBDATE(d,n)	日期 d 减去 n 天后的日期

高级函数

MySQL还提供了可以做简单的逻辑判断的函数，以及进行类型以及字符集转化

CAST(x AS type)	转化数据类型，将x的类型转化为type
COALESCE(expr1, expr2, ...., expr_n)	返回参数中的第一个非空表达式（从左向右）
CONV(x, f1, f2)	x 从进制 f1 转化为 f2
CONVERT(s  USING cs )	更改s 的字符集 为cs	例如 CONVERT("abc" USING gbk)  改为GBK字符集
CURRENT_USER USER() SYSTEM_USER()	当前用户
IF(expr, v1, v2)	if 判断，如果expr为真，则返回v1，否则返回v2
IFNULL(v1, v2)	如果v1为null，返回v2，否则返回v1
LAST_INSERT_ID()	返回最近生成的AUTO_INCREMENT 值
NULLIF(expr1, expr2)	比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1
DATABAES()	当前数据库名