MySQL常用函数

目录

• 常用函数
  • COUNT()加条件
  • COUNT()去重加条件
  • CAST 强转
  • 排序相关
  • ORDER BY RAND()
  • ORDER BY IF()条件排序
  • ORDER BY FIELD() 自定义排序
  • 字符串相关
  • LOCATE 定位
  • SUBSTRING_INDEX
  • FIND_IN_SET()
  • GROUP_CONCAT()
  • LENGTH() / CHAR_LENGTH()
  • 时间日期相关
  • DATEDIFF 日期间隔
  • CONVERT_TZ() 转换时区

常用函数

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()

原文链接：Mysql中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