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 '字符串时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf-8;

-- 插入测试数据
INSERT INTO `t_info` VALUES (1, '张三他爷爷的孙子', 18, 66, 76, '2022-09-01', '2022-09-01 09:01:10', '2022-09-01 09:01:10');
INSERT INTO `t_info` VALUES (2, '李四他爸爸的儿子', 21, 68, 86, '2022-10-13', '2022-10-13 09:01:58', '2022-10-13 09:01:58');
INSERT INTO `t_info` VALUES (3, '王五他自己的自己', 19, 70, 81, '2022-10-16', '2022-10-16 09:02:32', '2022-10-16 09:02:32');

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;

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.时间加减

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