4.MySQL常用函数

字符串函数

函数	功能
CONCAT(S1,S2,…Sn)	将S1,S2,…Sn练级额为一个字符串
INSERT(str,x,y,instr)	将字符串str,从x位置开始,y个字符长度的子串替换为instr
LOWER(str)	将字符串str所有字符转换为小写
UPPER(str)	将字符串str中所有字符转换为大写
LEFT(str,x)	返回字符串str左边的x个字符
RIGHT(str,x)	返回字符串str右边的x个字符
LPAD(str,n,pad)	用字符串pad对字符串左边进行填充,直到长度为n个字符长度
RPAD(str,n,pad)	用字符串pad对字符串右边进行填充,直到长度为n个字符长度
LTRIM(str)	去掉字符串左侧空格
RTRIM(str)	去掉字符串右侧空格
REPEAT(str,x)	将字符串str重复x次
REPLACE(str,a,b)	用字符串b替换字符串str中出现的所有a字符串
STRCMP(s1,s2)	比较字符串s1和s2
TRIM(str)	去掉字符串str行尾和行头的所有字符串
SUBSTRING(str,x,y)	返回字符串str x位置起y个长度的字符串

• CONCAT(s1,s2,...sn)将多个字符串连接为一个字符串,任何字符串和NULL连接的结果都为NULL

select CONCAT('ads','133'),CONCAT('abc',NULL);	

• INSERT(str,x,y,instr)将字符串str从x开始的y个字符替换为instr

select INSERT('Hello World',7,5,'MySQL');

• LOWER(str)将字符串str全部转换为小写

select LOWER('bHUFRhjH');

• UPPER(str)将字符串str转化位大写

select UPPER('jhssdgvTF');

• LEFT(str,x)和RIGHT(str,x)返回字符串str左边/右边的x个字符

select LEFT('BEIJIN',3),RIGHT('LOVE',2);

• LPAD(str,n,pad)和RPAD(str,n,pad)将字符串str左侧/右侧的字符用pad进行填充,直到长度为n个字符长度

select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');

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

select  LTRIM('   |b'),RTRIM('h|   ');

• REPEAT(str,n)将字符串str重复n次

select REPEAT('Hello ',4);

• REPLACE(str,a,b)将str中的a用b替换

select REPLACE('Hello World','World','MySQL');

• STRCMP(s1,s2)比较字符串s1,s2的ASCII码值的大写,如果s1比s2小,返回-1,如果s1和s2相等,返回0,如果s1比s2大返回1

select STRCMP('java','python');

• TRIM(str)去除str首尾空格

select TRIM('   |k|   ');

• SUBSTRING(str,x,y)返回字符串str从x开始的y个字符

select  SUBSTRING('Hello',1,2);

数值函数

函数	作用
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最大整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x,y)	返回x%y的值
RAND()	返回0~1的随机值
ROUND(x,y)	返回x四舍五入后有y个小数的值
TRUNCATE(x,y)	返回数字x截断为y个小数的结果

• ABS(x)返回x的绝对值

select ABS(-8),ABS(8);

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

select CEIL(2.6),CEIL(2.1);

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

select FLOOR(3.9),FLOOR(3.1);

• MOD(x,y)返回x%y的值

select MOD(5,2),5%2;

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

select RAND(),RAND();

• ROUND(x,y),返回对x四舍五入后包含y位小数的值

select ROUND(2.5674,3); 

日期和时间类型函数

函数	作用
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
UNIX_TIMESTAMP(date)	返回日期date的UNIX时间戳
FROM_UNIXTIME()	返回UNIX时间戳的日期值
WEEK(date)	返回日期date为一年中的第几周
YEAR(date)	返回日期date的年份
HOUR(time)	返回time的小时值
MIUNTE(time)	返回time的分钟值
MONTHNAME(date)	返回date的月份名
DATE_FORMAT(date,fmt)	返回按字符串fmt格式化的date日期值
DATE_ADD(date,INTERVAL expr type)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间expr和结束时间expr2之间的天数

• CURDATE()返回当前日期值,只包含年月日

select CURDATE();

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

select CURTIME();

• NOW()返回当前的日期和时间

select NOW();

• UNIX_TIMESTAMP(date)返回date的UNIX时间戳

select UNIX_TIMESTAMP(NOW());

• FROM_UNIUXTIME(unixtime)返回UNIX时间戳的日期值,与UNIX_TIMESTAMP(date)相反

select FROM_UNIXTIME(1597733300);

• WEEK(date)和YEAR(date)前者返回的是给定日期为一年中的第几周,后者返回的是给定日期是那一年

select WEEK(NOW()),YEAR(NOW());

• HOUR(time)和MINUTE(time)前者返回的是给定时间的小时,后者返回的是给定时间的分钟

select HOUR(CURTIME()),MINUTE(CURTIME());

• MONTHNAME(date)返回date对应的月份名称

select MONTHNAME(NOW());

• DATE_FORMAT(date,fmt),按照字符串fmt格式化日期date,可以使用的格式符如下

格式符	格式说明
%S,%s	两位数字格式的秒(00,01,…,59)
%i	两位数字格式的分(00,01,…,59)
%H	两位数字的小时,24小时(0,1,…,24)
%h,%l	两位数字的小时,12小时(0,1,…,12)
%k	数字形式的小时,24小时(0,1,…,24)
%l	数字形式的小时,12小时(0,1,…,12)
%T	24小时的形式(hh:mm:ss)
%r	12小时的形式(hh:mm:ssAM 或 hh:mm:ssPM)
%p	AM或PM
%W	一周中每一天的名称(Sunday,Monday,…,Saturday)
%a	一周中每一天名称的缩写(Sun,Mon,…,Sat)
%d	两位数字表示月中的天数(00,01,…,31)
%e	数字表示月中的天数(1,2,…,31)
%D	英文后缀表示月中的天数(1st,2nd,3rd…)
%w	艺术字形式表示周中的天数(0=Sunday,1=Monday,…,6=Saturday)
%j	以三位数字表示年中的天数(001,002,…336)
%U	周(0,1,52),其中Sunday为周中的第一天
%u	周(0,1,52),其中Monday为周中的第一天
%M	月名,(January,February,…,December)
%b	缩写的月份(Jan,Feb,…Dec)
%m	两位数字表示的月份(01,02,…,12)
%c	数字表示的月份(1,2,…12)
%Y	4位数字表示的年
%y	两位数字表示的年
%%	直接值

select DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时%i分%S秒');

• DATE_ADD(date,INTERVAL expr type)返回与给定日期相差INTERVAL时间段的时间,其中INTERVAL是时间间隔类型,expr是一个表达式,type是间隔类型,MySQL提供了13中间隔类型.

表达式类型	描述	格式
HOUR	小时	hh
MINUTE	分钟	mm
SECOND	秒	ss
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
YEAR_MONTH	年月	YY-MM
DAY_HOUR	日和时	DD hh
DAY_MINUTE	日和分钟	DD hh:mm
DAY_SECOND	日和秒	DD hh:mm:ss
HOUR_MIUNTE	时和分钟	hh:mm
HOUR_SECOND	时和秒	hh:ss
MINUTE_SECOND	分钟和秒	mm:ss

select NOW() CURRENT,DATE_ADD(NOW(),INTERVAL 31 DAY) AFTER_31_DAYS,DATE_ADD(NOW(),INTERVAL '1_2' YEAR_MONTH) AFTER_1YEAR_2MONTH;

• DATEDIFF(date1,date2)计算两个日期相差的天数

select DATEDIFF('20210101',now());

流程函数

流程函数也是很常用的一类函数，用户可以使用这类函数在一个 SQL 语句中实现条件选择，这样做能够提高语句的效率

函数	作用
IF(value,t ,f)	如果value为真,返回t,否则返回f
IFNULL(value1,value2)	如果value1不为空返回value1,否则返回value2
CASE WHEN [value1] THEN[result1]…ELSE[default] END	如果value1是真,返回result1,否则返回default
CASE[expr] WHEN[value1] THEN[result1] …ELSE[default]END	如果expr等于value1,返回result1,否则返回default

创建员工薪水表

 create table salary (userid int,salary decimal(9,2));

插入测试数据

 insert into salary values(1,1000),(2,2000), (3,3000),(4,4000),(5,5000), (1,null);

• IF(value,t,f)如果员工薪水大于3000显示为’high’,否则显示为’low’

select IF(salary>3000,'high','low') from salary;	

• IFNULL(value1,value2)常用来替换NULL的

select IFNULL(salary,0) from salary;

• CASE WHEN 来实现高底薪

select CASE WHEN salary >=2000 then 'higt' ELSE 'low' END  from salary;

其他常用函数

函数名	作用
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登录用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA(num)	返回数字代表的IP地址
PASSWORD(str)	返回字符串str的加密版本
MD5(str)	返回字符串str的MD5值

• DATABASE

select DATABASE();

• VERSION()

select VERSION();

• USER()

select USER();

• INET_ATON(ip)

select INET_ATON('192.168.31.51');

• INET_NTOA(num)

select INET_NTOA(3232243507);

• PASSWORD(str)

select PASSWORD('ihgyugjui4564');

• MD5(str)

select MD5('sfcsf4778x');

• INET_ATON(ip)

select INET_ATON('192.168.31.51');

• INET_NTOA(num)

select INET_NTOA(3232243507);