MySQL常用函数

在MySQL数据库中，函数可以用在SELECT语句以及其子句（WHERE、ORDER BY、HAVING）中，也可以用UPDATE、DELETE语句及其子句中。

字符串函数

函数	功能
CONCAT(S1,S2,...Sn)	连接S1,S2,...Sn为一个字符串
INSERT(str,n,m,instr)	将字符串str从第n(从1计数)位开始，m个字符串长的字串替换为instr
LOWER(str)	将字符串str转为小写
UPPER(str)	将字符串str转为大写
LEFT(str,n)	返回字符串str最左侧的n个字符
RIGHT(str,n)	返回字符串str最右侧的n个字符
LPAD(str,n,pad)	用字符串pad对str从左侧进行填充，直到长度为n个字符
RPAD(str,n,pad)	用字符串pad对str从右侧进行填充，直到长度为n个字符
LTRIM(str)	去掉字符串str左侧的空格
RTRIM(str)	去掉字符串str右侧的空格
TRIM(str)	去掉字符串str两侧的空格
REPEAT(str,n)	返回字符串str重复n次的结果
REPLACE(str,a,b)	用字符串b替换字符串str中所有的字符串a
STRCMP(s1,s2)	比较字符串s1和s2
SUBSTR(str,n[,m])	将字符串从第n(从1开始计数)位开始截取（m个长度）

mysql> select concat('a','',123);-- a//123
mysql> select concat('a',1,null); -- NULL

mysql> select insert('abc',2,3,'++');-- a++
mysql> select insert('abcde',2,3,123) from dual; -- a123e
mysql> select insert('abcde',2,5,123) from dual; -- a123

mysql> select lower('AdjB'); -- adjb
mysql> select upper('AdjB'); -- ADJB

mysql> select left(12345,3); -- 123
mysql> select left(12345,7); -- 12345

mysql> select lpad('abc',6,0); -- 000abc
mysql> select lpad('abc',2,0); -- ab
mysql> select rpad('abc',2,0); -- ab

mysql> select substr('abc',2,1); -- b
mysql> select substr('abc',2); -- bc
mysql> select substr('abc',5,5); -- ''

mysql> select repeat('abc',3); -- abcabcabc

mysql> select strcmp('abc','sdk'); -- -1
mysql> select strcmp('abc','abc'); -- 0
mysql> select strcmp('A','a'); -- 0
mysql> select strcmp('C','b'); -- 1

• 任何字符串与null进行连接，结果都是null。
• 使用lpad()和rpad()可实现字符串截取。

数值函数

函数	功能
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位小数的结果

mysql> select abs(-2.35); -- 2.35
mysql> select ceil(-2.35); -- -2
mysql> select floor(-2.35); -- -3
mysql> select mod(7,2); -- 1
mysql> select rand(); -- 0.4138457564766014 16位小数
mysql> select round(0.1235,3); -- 0.124
mysql> select truncate(0.1235,3); -- 0.123

日期函数

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

mysql> select curdate(); -- 2021-07-15
mysql> select curtime(); -- 12:06:54
mysql> select now(); -- 2021-07-15 12:07:25
mysql> select unix_timestamp(now()); -- 1626322115
mysql> select from_unixtime(1626322115); -- 2021-07-15 12:08:35
mysql> select week(now()); -- 28
mysql> select hour(now()); -- 12
mysql> select minute(now()) from dual; -- 11
mysql> select monthname(now()); --  July
mysql> select date_format(now(),'%M,%D,%Y'); -- July,15th,2021
mysql> select date_add(now(),interval 3 day); -- 2021-07-18 12:18:20
mysql> Select datediff(now(),'2020-12-25'); -- 202

流程函数

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

函数	功能
IF(value,t f)	如果value是真，返回t；否则返回f
IFNULL(value1,value2)	如果value1不为空，返回value1；否则返回value2
CASE WHEN [value] THEN [result] ... ELSE [default] END	如果value1是真，返回result1；否则返回default
CASE [expr] WHEN [value1] THEM [result1] ... ELSE [default] END	如果expr等于value1，返回result1；否则返回default

--创建表sal，包含字段userid（int）,sal（decimal(9,2)）
mysql> create table sal (userid int,sal decimal(9,2));
--向表中插入一些数据
mysql> insert into sal values(1,1100),(2,2200),(3,3300),(4,4400),(5,5500),(1,null);

--以3000为界，高于为'high'，其他为'low'，查询工资水平
mysql> select userid,sal,if(sal>3000,'hign','low') level from sal;

---

--查询工资，如果是null，则显示为0。
mysql> select ifnull(sal,0) from sal;

因为null不能参与算数运算，所以可以通过这个函数将null转换为其他值。

---

--使用CASE WHEN ... THEN... 实现高薪低薪判断
mysql> select userid,sal, case when sal>3000 then 'high' else 'low'end level from sal;

---

--使用CASE expr WHEN ... THEN ... 实现将工资划分为多档
mysql> select userid,sal,case sal when 1100 then 'low' when 2200 then 'mid' when 3300 then 'hign' else 'great'end level from sal;

mysql> SELECT userid, sal, CASE WHEN sal IS NULL THEN 'null' WHEN sal BETWEEN 1000 AND 2000 THEN 'low' WHEN sal BETWEEN 2000 AND 3000 THEN 'mid' WHEN sal BETWEEN 3000 AND 4000 THEN 'hign' ELSE	'great' END LEVEL FROM sal;

高阶函数

函数	功能
BIN(x)	返回x的二进制编码
BINARY(str)	将字符串str转换为二进制编码
CAST(x AS type)	将x的类型转换为type
CONV(x,f1,f2)	将f1进制的x转为f2进制

mysql> select bin(6); -- 110
mysql> select binary('abc'); -- abc
mysql> select cast(16 as char); -- 16
mysql> select cast('2016-12-06' as datetime); -- 2016-12-06 00:00:00
mysql> select cast(161206 as datetime); -- 2016-12-06 00:00:00
mysql> select cast(now() as char); -- 2021-07-15 15:05:23
mysql> select conv(23,8,10); -- 19
mysql> select conv(12,10,2); -- 1100
mysql> select conv(12,10,16); -- C

其它函数

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

mysql> select database(); -- test
mysql> select version(); -- 5.7.30-log
mysql> select user(); -- root@localhost
mysql> select inet_aton('192.168.0.1'); -- 3232235521
mysql> select inet_ntoa(12346854); --  0.188.101.230
mysql> select password('123456'); -- *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
mysql> select md5(123456); -- e10adc3949ba59abbe56e057f20f883e