mysql常用函数
mysql 函数笔记
本章内容会用到的建表语句和表数据
-- 创建t_info表
CREATE TABLE `t_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(30) CHARACTER SET utf8 DEFAULT NULL COMMENT '姓名',
`age` int(2) DEFAULT NULL COMMENT '年龄',
`c_score` int(3) DEFAULT NULL COMMENT '语文分数',
`e_score` int(3) DEFAULT NULL COMMENT '英语分数',
`the_date` date DEFAULT NULL COMMENT '出分日期',
`the_time` datetime DEFAULT NULL COMMENT '出分时间',
`str_time` varchar(30) DEFAULT NULL COMMENT '字符串时间',
`level` int(1) DEFAULT NULL COMMENT '1优秀 2良好 3还行',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf-8;
-- 插入测试数据
INSERT INTO `t_info`(`id`, `user_name`, `age`, `c_score`, `e_score`, `the_date`, `the_time`, `str_time`, `level`) VALUES (1, '张三他爷爷的孙子', 18, 66, 76, '2022-09-01', '2022-09-01 09:01:10', '2022-09-01 09:01:10', 1);
INSERT INTO `t_info`(`id`, `user_name`, `age`, `c_score`, `e_score`, `the_date`, `the_time`, `str_time`, `level`) VALUES (2, '李四他爸爸的儿子', 21, 38, 86, '2022-10-13', '2022-10-13 09:01:58', '2022-10-13 09:01:58', 2);
INSERT INTO `t_info`(`id`, `user_name`, `age`, `c_score`, `e_score`, `the_date`, `the_time`, `str_time`, `level`) VALUES (3, '王五他自己的自己', NULL, 70, 81, '2022-10-16', '2022-10-16 09:02:32', '2022-10-16 09:02:32', 3);
1.日期时间字段相关函数
-- 1. now()获取当前日期和时间 current() CURRENT_DATE()获取当前日期 CURRENT_TIME()获取当前时间
select now(),CURRENT_DATE(),CURRENT_TIME() from dual;
-- 2. DATE()用来提取时间字段的日期 YEAR() 提取年 MONTH()提取月份，会省略前面的0 DAY()提取天，若日期前有0会忽略
select DATE(str_time), YEAR(the_time), MONTH(the_time), DAY(str_time) from t_info;
-- 3. UNIX_TIMESTAMP()函数，获取当前时间戳，还可以将时间字段转换成时间戳
select UNIX_TIMESTAMP();
select UNIX_TIMESTAMP(the_time) from t_info;
-- 4. 时间格式化函数 DATE_FORMAT()和TIME_FORMAT()功能相同
select DATE_FORMAT(the_time,'%Y/%m/%d %H:%i:%s') from t_info;
select DATE_FORMAT(the_time,'%Y/%m/%d') from t_info;
2. 字符串字段相关函数
-- 1.CONCAT(str1,str2,...,strn) 将str1,str2,...,strn连接为一个完整的字符串
select concat(user_name,age,the_date) from t_info;
select concat(user_name,age,the_date,null) from t_info; -- 拼接null的话，会将整体返回null
select * from t_info where user_name like concat('%','三','%'); -- mybatis 中使用模糊查询的常见用法
-- 2.CONCAT_WS(sep,str1,str2,...,strn) 拼接并使用tep隔开
select CONCAT_WS('-',user_name,age,'哈哈') from t_info;
-- 3.STRCMP(str1,str2); 如果传入的参数str1大于str2，则返回true；如果参数str1小于str2，则返回false；如果参数str1等于str2，则返回0
select strcmp('22','33') from dual;
select strcmp('33','33') from dual;
select strcmp('33','22') from dual;
select strcmp('33','3a') from dual; -- 是比较asscll码
-- 4.获取字符串长度函数LENGTH()和字符数函数CHAR_LENGTH()
select LENGTH('张三'),LENGTH('avfwa') from dual;
select CHAR_LENGTH('张三'),CHAR_LENGTH('avfwa') from dual;
-- 5.实现字母大小写转换函数UPPER()和LOWER()
select UPPER('AbCd') from dual;
select LOWER('AbCd') from dual;
-- 6.从现有字符串中截取子字符串 LEFT(str,num); RIGHT(str,num); SUBSTRING(str,num,len); substring_index(str,sep,num)
select LEFT('张三是一个大叔啊',2) from dual;
select RIGHT('张三是一个大叔啊',3) from dual;
select SUBSTRING('张三是一个大叔啊',4,5) from dual;
select substring_index('张三是一个大叔啊','-',-1) from dual;
select substring_index('张三是-一个大叔啊','-',-1) from dual;
select substring_index('张三是-一个-大叔啊','-',2) from dual;
-- 7.去除字符串开始的首位空格 LTRIM(str);去除首部空格 RTRIM(str);去除尾部空格 TRIM(str);去除首尾空格
select LTRIM(' mysql '),RTRIM(' mysql '),TRIM(' mysql ') from dual; -- 看不出效果
select concat('-',LTRIM(' mysql '),'-'),concat('-',RTRIM(' mysql '),'-'),concat('-',TRIM(' mysql '),'-') from dual;
-- 8.替换字符串 REPLACE(str,substr,newstr); INSERT(str,pos,len,newstr);
select REPLACE('1234567','56','aa') from dual;
select INSERT('1234567',2,3,'aaa') from dual;
-- 9.字符串截取 SUBSTRING(param1,param2,param3) param1代表要截取的字符串 ，param2代表开始截取的起始位置， param3代表要截取的长度
SELECT SUBSTRING('Hello World',7,5) FROM DUAL
-- 10.获取字符在字符串中第一次出现的位置 LOCATE(p1,p2) p1代表要查找的字符，p2代表字符串
SELECT LOCATE('o','Hello World');
3. 数值相关函数
-- 1. abs(num)返回num的绝对值 ceil(num)返回大于 num 的最小整数值 floor(num)返回小于 num 的最大整数值
select abs(-3), ceil(3.1), floor(3.1) from dual;
-- 2.rand() 返回 0 到 1 内的随机值。
select rand();
-- 3.round(num,n) 返回 num 的四舍五入的 n 位小数的值。
select round(16.1466,2);
-- 4.truncate(num,n) 返回数字 num 截断为 n 位小数的结果。
select truncate(3.1466,2) from dual;
4.流程控制函数
-- 1.IF(value,value1,value2) value结果是true，那返回value1，否则返回value2
select IF(1=1,'是','否');
select IF(c_score > 60,'牛啊','菜啊') from t_info;
-- 2.IFNULL(value1,value2) 如果value1不是null返回value1，否则返回value2
select IFNULL(age,'年龄哪去了？') from t_info;
-- 3.CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ... [ELSE 结果n] END 别名
select id , user_name,
(CASE
WHEN c_score > 60 THEN
'牛啊'
WHEN c_score = 60 THEN
'刚好'
ELSE
'菜啊'
END) as "result"
from t_info;
-- 4.CASE 目标字段 WHEN 数值1 THEN 结果1 WHEN 数值2 THEN 结果2 ... [ELSE 结果n] END 别名
select id, user_name,
(case level
when 1 then '优秀'
when 2 then '良好'
when 3 then '还行'
else '垃圾'
end) as '等级'
from t_info;
5.聚合函数，排序函数，分页
（常用，在此不举例子）
-- 1.AVG()求平均值， SUM()求和， MAX()最大值， MIN()最小值， COUNT()总条数
-- 2.GROUP BY 字段 分组， HAVING 分组过滤
-- 3.order by 字段 排序， limit v1, v2 分页
6.其他用到过的函数
1.时间加减
DATE_SUB()和DATE_ADD()函数，实现日期增减
DATE_SUB(NOW(),INTERVAL 30 MINUTE) 当前时间减30分钟
DATE_SUB(NOW(),INTERVAL 1 day) 当前时间减1天
DATE_SUB(NOW(),INTERVAL 1 hour) 当前时间减1小时
DATE_SUB(NOW(),INTERVAL 1 second) 当前时间减1秒
DATE_SUB(NOW(),INTERVAL 1 week) 当前时间减1星期
DATE_SUB(NOW(),INTERVAL 1 month) 当前时间减1个月
DATE_SUB(NOW(),INTERVAL 1 quarter) 当前时间减1季度
DATE_SUB(NOW(),INTERVAL 1 year) 当前时间减1年
相对DATE_ADD()是加时间
demo
获取最近10天的数据：
SELECT
*
FROM loit_net_option
WHERE
event_time > DATE_SUB(now(),INTERVAL 10 DAY)
2.判空函数
IFNULL(expression, alt_value)
解释:
expression 必须，要测试的值
alt_value 必须，expression 表达式为 NULL 时返回的值
demo
判断，如果region为null,那么返回值为 “其他”
SELECT IFNULL(region,'其他') as name FROM loit_bear_mine_metal
3.多表拼接总数求和
SELECT
sum(a)
FROM
( SELECT count(*) a FROM loit_bear_pub_school
UNION
SELECT count(*) a FROM loit_bear_pub_medical
UNION
SELECT count(*) a FROM loit_bear_pub_hotel
UNION
SELECT count(*) a FROM loit_bear_pub_sports
UNION
SELECT count(*) a FROM loit_bear_pub_culture
UNION
SELECT count(*) a FROM loit_bear_pub_religion
UNION
SELECT count(*) a FROM loit_bear_pub_scenic_spot
UNION
SELECT count(*) a FROM loit_bear_pub_supermarket
UNION
SELECT count(*) a FROM loit_bear_pub_social
) AS b
4.case判断
SELECT
( CASE warning_level WHEN 1 THEN '一级' WHEN 2 THEN '二级' WHEN 3 THEN '三级' ELSE '其他' END ) AS LEVEL,
count(*) AS count
FROM
loit_warning_info
5.时间格式的过滤条件
SELECT
a.area AS code,b.area as name,count(*) AS num
FROM
loit_warning_info a
LEFT JOIN loit_area b on a.area=b.`code`
where 1=1
<if test="type == 1">
and DATE_FORMAT(warning_time,'%Y') BETWEEN #{startDate} and #{endDate}
</if>
<if test="type == 2">
and DATE_FORMAT(warning_time,'%Y-%m-%d') BETWEEN #{startDate} and #{endDate}
</if>
<if test="type == 3">
and DATE_FORMAT(warning_time,'%Y-%m') BETWEEN #{startDate} and #{endDate}
</if>
GROUP BY a.area
ORDER BY num desc
6.查看大串包小串
FIND_IN_SET(str,strlist) ： str 要查询的字符串，strlist 需查询的字段，参数以”,”分隔，形式如 (1,2,6,8,10,22)；该函数的作用是查询字段(strlist)中是否包含(str)的结果，返回结果为null或记录。
浙公网安备 33010602011771号