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