MySQL常用函数
目录
常用函数
COUNT()加条件
原文链接：Mysql中使用count加条件统计
使用 COUNT() 函数实现条件统计的基础是对于值为 NULL 的记录不计数，常用的有以下三种方式，假设统计 num 大于 200 的记录
1. select count(num > 200 or null) from a;
2. select count(if(num > 200, 1, null)) from a
3. select count(case when num > 200 then 1 end) from a
COUNT()去重加条件
count(DISTINCT case when ss.status = 40 then ss.id end) as leave_total
语法:
count(DISTINCT case when 条件 then 去重的字段 end)
也就是对满足条件的字段再次去重。
count 单纯的条件不加去重语法：
count(ss.status = 50 or null) as seats_total
只统计status = 50的
CAST 强转
原文链接：MYSQL中，CAST函数的使用规则
CAST函数语法规则是：Cast(字段名 as 转换的类型 )，其中类型可以为：
CHAR[(N)] 字符型
DATE 日期型
DATETIME 日期和时间型
DECIMAL float型
SIGNED int
TIME 时间型
实例：
实例1：
表table1
date：2015-11-03 15:31:26
select cast(date as signed) as date from table1;
结果如下：
date：20151103153126
select cast(date as char) as date from table1;
结果如下：
date：2015-11-03 15:31:26
实例2：
select cast(date as datetime) as date from table1;
结果如下：
date：2015-11-03 15:31:26
实例3：
select cast(date as date) as date from table1;
结果如下：
date：2015-11-03
实例4：
select cast(date as time) as date from table1;
结果如下：
date：15:31:26
这里date对应日期，time对应时间
实例5：
表table2
num:20
select cast(num as decimal(10, 2)) as num from table2
结果如下：
num：20.00
解释：decimal 数据类型最多可存储 38 个数字,所有数字都能够放到小数点的右边.decimal 数据类型存储了一个准确（精确）的数字表达法；不存储值的近似值.其中10是小数点左边和右边的数字个数之和(不包括小数点),2代表小数点右边的小数位数或数字个数.decimal(10,2)可以存储8位整数2位小数的数字.
排序相关
ORDER BY RAND()
对结果集进行随机排序
ORDER BY IF()条件排序
原文链接：MYSQL ORDER BY 使用 if 以及使用 IN
将结果按特定顺序排序，相当于附加一个隐藏属性，满足条件的结果进行相应排序。
test表数据：
+----+------+
| id | type |
+----+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 3 |
| 7 | 3 |
+----+------+
IF：
SELECT * FROM test ORDER BY IF(type=3,0,1);
+----+------+
| id | type |
+----+------+
| 6 | 3 |
| 7 | 3 |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+----+------+
IN:
SELECT * FROM ORDER BY type IN(2,3) DESC
+----+------+
| id | type |
+----+------+
| 4 | 2 |
| 5 | 2 |
| 6 | 3 |
| 7 | 3 |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
+----+------+
ORDER BY FIELD() 自定义排序
语法：FIELD(str,str1,str2,str3,...)，str与str1、str2、str3...比较，返回1、2、3，如遇到null或者不结果集中的数据则返回0，然后根据升序进行排序。
test表数据：
+----+------+
| id | type |
+----+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 3 |
| 7 | 3 |
+----+------+
查询
SELECT * FROM test ORDER BY FIELD(type,3,1,2);// 类型按照给定3-1-2顺序排序
+----+------+
| id | type |
+----+------+
| 6 | 3 |
| 7 | 3 |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+----+------+
字符串相关
LOCATE 定位
判断字符串（string）中是否包含另一个字符串（subStr）
locate(subStr,string) ：函数返回subStr在string中出现的位置
// 如果字符串 string 包含 subStr
locate(subStr,string) > 0
// 如果字符串 string 不包含 subStr
locate(subStr,string) = 0
// 返回 3
SELECT LOCATE('q', 'asqdfasdfser')
//返回 8
SELECT LOCATE('q', 'asqdfasqdfser',4)
SUBSTRING_INDEX
原文链接：mysql处理字符串的两个绝招：substring_index,concat
语法：
SUBSTRING_INDEX(str,delim,count)
str:要处理的字符串
delim:分隔符
count:计数
案例：
str=www.google.com
substring_index(str,'.',1)
结果是：www
substring_index(str,'.',2)
结果是：www.google
也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容
相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如：
substring_index(str,'.',-2)
结果为：google.com
有人会为，如果我呀中间的的google怎么办？
很简单的，两个方向：
1、从右数第二个分隔符的右边全部，再从左数的第一个分隔符的左边：
substring_index(substring_index(str,'.',-2),‘.’,1);
FIND_IN_SET()
语法：
FIND_IN_SET(str,strlist)
str 要查询的字符串
strlist 参数以,分隔的字段名 如 (1,2,6,8,10,22)
查询字段(strlist)中包含(str)的结果，返回结果为null或记录
案例：
SELECT FIND_IN_SET('b', 'a,b,c,d'); 结果为2
SELECT FIND_IN_SET('6', '1'); 返回0 strlist中不存在str，所以返回0
GROUP_CONCAT()
语法：
group_concat([DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )
案例：
test表数据：
+----+------+
| id | type |
+----+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 3 |
| 7 | 3 |
+----+------+
select type, group_concat(id) from test group by type;
+----+------+
|type| id |
+----+------+
| 1 | 1,2,3|
| 2 | 4,5 |
| 3 | 6,7 |
+----+------+
乱码问题
group_concat子查询返回数字是乱码，既不是utf8也不是gbk，后来看了下子表的字段编码是gbk的，但sql整体返回的是utf8，group_concat前把字段转换成utf8的，convert（fieldvale using utf8）
GROUP_CONCAT(DISTINCT CONVERT(srtc.class_id USING utf8) SEPARATOR '-') AS class_id
LENGTH() / CHAR_LENGTH()
原文链接：java和mysql的length()区别及char_length()
介绍：
mysql里面的有length和char_length两个长度函数，区别在于：
length: 一个汉字是算三个字符，一个数字或字母算一个字符。
char_length: 不管汉字还是数字或者是字母都算是一个字符。
案例:
select length('办公室零食m') ---> 字节数16
select char_length('办公室零食m') ---> 字符数6
一般判断是否纯英文时：length(str)=char_length(str)
Java中的字节和字符数：
System.out.println("办公室零食m".getBytes("utf-8").length); -->字节数 16
System.out.println("办公室零食m".length()); ---> 字符数 6
时间日期相关
DATEDIFF 日期间隔
DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日期或 datetime 表达式。计算时只用到这些值的日期部分。
案例：
SELECT DATEDIFF('2021-01-25','2021-01-20') 返回5
SELECT DATEDIFF('2021-01-20','2021-01-25 23:59:59') 返回-5
CONVERT_TZ() 转换时区
原文链接：MySQL UTC时间转北京时间 | convert_tz()函数
CONVERT_TZ(dt,from_tz,to_tz)
转换datetime值dt，从from_tz时区转到to_tz时区，并返回结果。参数无效返回NULL。
案例：
-- UTC时间转东八区时间
SELECT CONVERT_TZ('2020-05-20 00:00:00','+00:00',"+08:00")
返回：2020-05-20 08:00:00
