mysql数据库常用函数

  在mysql数据库中，函数可以用在SELECT语句及其子句（WHER,ORDER BY,HAVING等），也可以用在UPDATE,DELETE语句及其子句中。

函数分为：字符串函数，数值函数，日期和时间的函数，流程函数，其他常用函数

1.字符串函数

CONCAT(S1,S2,...,Sn)            连接S1,S2,...,Sn为一个字符串

注：任何字符串与NULL进行连接的结果都将是NULL

例：select concat('aaa','bbb','ccc'),concat('aaa',null);

INSERT(str,x,y,instr)               将字符串str从第x位置开始，y个字符替换为instr

例：select insert('beijing',3,4,'fang');

LOWER(str)和UPPER(str)        把字符串转换成小写或大写

例：select lower('BEIJING'),upper('beijing');

LEFT(str,x)                                   返回字符串最左边的x个字符

注：如果第二个参数是NULL，那么将不返回任何字符串

RIGHT(str,x)                                  返回字符串最右边的x个字符

注：如果第二个参数是NULL，那么将不返回任何字符串

LPAD(str,n,pad)                            用字符串pad对str最左边进行填充，直到长度为n个字符

例：select lpad('2022',20,'beijing');

RPAD(str,n,pad)                            用字符串pad对str最右边进行填充，直到长度为n个字符

例：select rpad('2022',20,'beijing');

LTRIM(str),RTRIM(str)                  去掉字符串str左侧和右侧的空格

REPEAT(str,x)                                返回str重复x次的结果

例：select repeat('mysql',3);

REPLACE(str,a,b)                          用字符串b替换字符串str中所有出现的字符串a

STRCMP(s1,s2)                            比较字符串s1和s2的ASCII值的大小

注：s1<s2 返回-1 ， s1=s2 返回0 ， s1>s2 返回1 

TRIM(str)                                       去掉str字符串的开头和结尾的空格

SUBSTRING(str,x,y)                    返回从字符串str中第x位置起y字符长度的字符串

注：此函数经常用来对给定字符串进行字符串的提取

2. 数值函数

ABS(x)                                         返回x的绝对值

CEIL(x)                                         返回大于x的最小整数

FLOOR(x)                                     返回小于x的最大整数

MOD(x,y)                                      返回x/y的模

注：和x%y的结果相同，模数和被模数任何一个为NULL结果都为NULL

RAND()                                          返回0~1内的随机值

注：rand()每次执行结果都不一致

ROUND(x,y)                                  返回参数x的四舍五入的有y位小数的值

TRUNCATE(x,y)                            返回数字截断为y位小数的结果

注：truncate函数仅仅是截断，不会进行四舍五入

3.日期和时间的函数

CURDATE()                                     返回当前日期，只包含年月日

CURTIME()                                      返回当前时间，只包含时分秒

NOW()                                              返回当前时间和日期，包含年月日时分秒

UNIX_TIMESTAMP(date)                返回日期date的UNIX时间戳

例：select unix_timestamp(now())

FROM_UNIXTIME(unixtime)             返回unixtime时间戳的日期值

注：from_unixtime和unix_timestamp互为逆操作

例：SELECT UNIX_TIMESTAMP('2023-01-05 17:30:40'),FROM_UNIXTIME(1672911040);  

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

YEAR(date)                                         返回所给日期是哪一年

HOUR(time)                                        返回所给时间的小时

MINUTE(tine)                                      返回所给时间的分钟

MONTHNAME(date)                           返回date的英文月份名称

DATE_FORMAT(date,fmt)                 按字符串fmt格式化日期date值

注：此函数能够按指定的格式显示日期

 例：将当前时间显示为“月，日，年”

select date_format(now(),'%M,%D,%Y');

DATE_ADD(date,INTERVAL expr type)  返回与所给日期date相差INTERVAL时间段的日期

注：interval是间隔类型关键字，expr是一个表达式，type是间隔类型

例：返回距离当前日期31天后的日期时间

select date_add(now(),interval 31 day) after31days

例：返回距离当前日期一年两个月后的日期 

select date_add(now(),interval '1_2' year_month) after_oneyear_twomonth

DATEDIFF(date1,date2)          用来计算两个日期之间相差的天数

4.流程函数

IF(value,t f)

注：如果value是真，返回t，否则返回f

例：select if(salary>2000,'high','low')from salary;

IFNULL(value1,value2)

注：如果value1不为空，返回value1，否则返回value2

        这个函数一般用来替换null，因为null不能参与数值运算

例：select ifnull(salary,0) from salary;

CASE WHEN [value1] THEN [result1] ... ELSE [default] END

注：如果value1是真，返回result1否则返回default

例： select case when salary<=2000 then 'low' else 'high' end from salary;

CASE[expr] WHEN [value1] THEN[result1] ... ELSE [default] END

注：如果expr等于value1，返回result1，否则返回default

例：select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;

5.其他常用函数

DATABASE()                                        返回当前数据库名

VERSION()                                           返回当前数据库版本

USER()                                                 返回当前登录的用户名

INET_ATON(IP)                                    返回IP地址的网络字节序表示

INET_NTOA(num)                                返回网络字节序代表的IP地址

注：该函数用途是将字符串的ip地址转换为数字表示的网络字节序，方便进行ip或网段比较

例：

 select * from t where inet_aton(ip)>=inet_aton('192.168.1.3') and inet_aton(ip)<=inet_aton('192.168.1.20');

PASSWORD(str)                              返回字符串str的加密版本，41位长的字符串

注：此函数只能设置系统用户的密码，不能用来对应用的数据加密，如有加密需求可用MD5

MD5(str)                                               返回字符串str的MD5值，用来对应用中的数据加密