MYSQL---常用函数大全

字符串函数：

一、concat()函数

功能：将返回结果的多个字符串进行连接合并一起返回

SELECT CONCAT(id,'-',parent_id) as ids FROM u_department WHERE parent_id=0;  
中间可以添加一个参数'-'进行分隔

结果:

SELECT CONCAT(id,'-',parent_id) as ids FROM u_department WHERE parent_id=0;
同时我们也可以添加as来控制返回表头的格式

结果:

二、concat_ws()函数

功能：使用指定分隔符进行字符串连接

SELECT CONCAT('-',id,parent_id,name) as ids FROM u_department WHERE parent_id=0;
可以看到我们只需要设置一次分隔符样式，MYSQL就会自动帮我们分隔不再像concat需要一一加入

结果:

三、format()函数

功能：用于格式化数字控制小数保留位数
结果:

SELECT FORMAT(18942.35489,4)
结果：18.942,3549

SELECT FORMAT(18942.35489,3)
结果：18,942.355

SELECT FORMAT(18942.35489,0)
结果：18,942

四、lower()函数

功能：将字符串统一转换为小写

SELECT LOWER('ChIHA');

结果:

五、upper()函数

功能：将字符串统一转换为大写

SELECT UPPER('chIHai');

结果:

六、left()函数

功能：从左侧开始对字符串进行截取

SELECT LEFT('chihai1996',6);

结果:

七、right()函数

功能：从右侧开始对字符串进行截取

SELECT RIGHT('chihai1996',4);

结果:

字符函数：

一、length()函数

功能：返回字符长度包括空格

SELECT LENGTH('chihai');

结果:

SELECT LENGTH('chihai  ');

二、ltrim()函数

功能：去除字符串左侧空格

SELECT LTRIM('  chihai');

结果:

三、rtrim()函数

功能：去除字符串右侧空格

SELECT LENGTH(RTRIM('chihai  '));
为了方便观察用length打印一下

结果:

四、trim()函数

功能：去除指定字符，默认去除空字符，可指定前后位置。leanding前方，trailing后方，both全部

SELECT LENGTH(TRIM(' chihai '));

结果:

SELECT TRIM(LEADING '?' FROM '????chihai????');

结果:

SELECT TRIM(TRAILING '?' FROM '????chihai????');

结果:

SELECT TRIM(BOTH '?' FROM '????chihai????');

结果:

五、replace()函数

功能：替换指定字符

SELECT REPLACE('????chihai????','?','x');

结果:

六、subString()函数

功能：自定义位置截取指定字符串长度，默认从左截取，设置负值则为右侧。注意设定数值时不要误以为1就是从第一个字符开始截取。-3则代表只保留右侧三个字符。

SELECT SUBSTRING('chihai',0);

SELECT SUBSTRING('chihai',1);

SELECT SUBSTRING('chihai',2);

SELECT SUBSTRING('chihai',-3);

七、[not] like

功能：模糊匹配

SELECT 'chihai' LIKE 'c%';
以c开头

SELECT 'chihai' LIKE '_h%';
下划线用来占位代表h前还有一个字符第二个字符要以h开始

SELECT 'chihai' LIKE '%i%';
只要出现i即可

SELECT '池%海' LIKE '%0%%' ESCAPE '0';
如果出现字符串中也出现%需要匹配，我们只需要在%前随便加一个字符借助ESCAPE关键字告诉MYSQL避开当前%不需要解析即可

数值运算符与函数：

一、ceil()

功能：进一取整，只要小数点后有非0的数就会进行进1

SELECT CEIL(3.141592);

结果:

SELECT CEIL(13.11);

结果:

二、div

功能：整数除法运算

SELECT 8 DIV 3;

结果:

三、floor()函数

功能：省略小数点后的数值

SELECT FLOOR(3.14);

结果:

四、mod()函数

功能：进行取余

SELECT MOD(10,4);
SELECT 10 MOD 4;

结果:

五、power()函数

功能：幂运算

SELECT POWER(2,3);

结果:

六、round()函数

功能：四舍五入，如果不给第二个参数默认保留整数

SELECT ROUND(3.14159);

结果:

SELECT ROUND(3.14159,3);

结果:

七、truncate()函数

功能：数字截取。第二个参数给负数代表从个位开始像左截取，截取位置用0填充

SELECT TRUNCATE(3.1415,3);

结果:

SELECT TRUNCATE(123456.1415,-2);

结果:

比较运算符与函数：

一、[not]between and

功能：判定数字在不在范围内，左右都是闭合的

SELECT 100 BETWEEN 100 AND 200;

结果：

SELECT 99 BETWEEN 100 AND 200;

结果：

二、[not]in()

功能：判定集合中是否存在指定数值

SELECT 0 IN(1,2,3,4,5);

结果：

SELECT 1 IN(1,2,3,4,5);

结果：

三、is [not]null

功能：判断值是否为null,’’,‘null’,等都不为null

SELECT '' IS NULL;

结果：

日期时间函数：

一、now()

功能：获取当前系统日期和时间（存在8小时时差）

SELECT NOW();

结果：

二、curdate()

功能：获取当前日期（年月日）

SELECT CURDATE();

结果：

三、curtime()

功能：获取当前时间

SELECT CURDATE();

结果：

四、date_add()

功能：控制日期变化，参数正负都可,week，month,year都是可选参数

SELECT DATE_ADD('2019-3-22',INTERVAL 1 month);

结果：

SELECT DATE_ADD('2019-3-22',INTERVAL -1 year);

结果：

五、datediff()

功能：计算两个日期之间的差值

SELECT DATEDIFF('1996-5-7','2020-3-22');

结果：

六、date_format()

功能：日期格式化

SELECT DATE_FORMAT('1996-5-7','%m-%d-%y');

结果：

信息函数：

一、connection_id()

功能：返回当前连接ID

SELECT CONNECTION_ID();

结果：

二、database()

功能：返回当前数据库名

SELECT DATABASE();

结果：

三、last_insert_id()

功能：返回最后插入记录的ID，只适用与自增主键

INSERT INTO u_user(username,password,name,status) VALUES ('xiyangxyang','xxxxxxxxx','喜洋洋',1);
SELECT LAST_INSERT_ID();

结果：

四、user()

功能：返回当前用户

SELECT USER();

结果：

五、version()

功能：返回当前数据库版本信息

SELECT VERSION();

结果：

聚合函数：

一、avg()

功能：求平均数

SELECT AVG(payment_amount) FROM u_user_order;

结果：

二、count()

功能：返回当前字段总个数,同时Count函数还可以加满足表达式的统计：express

SELECT COUNT(CASE WHEN parent_id=0 THEN 1 ELSE NULL END) FROM u_department;

结果：

三、max()

功能：返回当前字段最大值,在与group by使用时需要注意不要出现数据不一致的问题
注意：对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。对于日期时间类型的数据也可以求其最大/最小值，其大小排列就是日期时间的早晚，越早认为其值越小。

SELECT MAX(payment_amount) FROM u_user_order;

结果：

四、min()

同上。。。

五、sum()

功能：求和

SELECT SUM(payment_amount) FROM u_user_order;

结果：

加密函数

一、md5()

password() 8.0.11版本中已删除