MySQL 函数学习笔记

MySQL 函数学习笔记

这里主要是记录 MySQL 常用的一些函数及示例，方便以后查阅！！！

一、单行处理函数

（一 ）函数列表

特点：一个输入对应一个输出

1. upper
2. lower
3. substr(name,1,1)：截取字符串，起始下标从1开始
4. concat（str1,str2）：字符串拼接
5. length
6. trim
7. round（）:四舍五入
8. rand():生成随机数
9. ifnull(字段，值)：空处理函数，可以将null转为一个具体值；null参与运算一定为null；
10. UNIX_TIMESTAMP()：将日期转为时间戳 ；
11. find_in_set()：在类似逗号分割的字符串中，查找指定字符串；

（二）case…when 函数——重要

1.简单使用

case…when …then…when…then…else…end

例子：

SELECT id,`name`,sex,
CASE WHEN sex = "MAN" THEN "男"
WHEN sex = 'WOMAN' THEN "女"
ELSE '变态'
END AS 性别
FROM `hp_sys_user` 

结果：

2.作为条件使用

例子：

SELECT id,`name`,sex,create_time
FROM `hp_sys_user` 
WHERE UNIX_TIMESTAMP(
CASE WHEN `create_time` = '2022-03-07 10:13:16' THEN DATE_ADD(`create_time`,INTERVAL 1 WEEK) 
END) > UNIX_TIMESTAMP(create_time)  

结果：

（三）date_add() 函数——重要

增加几天、几个月、几年的计算函数

DATE_ADD(日期,INTERVAL 1 DAY) AS sys

减少几天、几个月、几年的计算函数

DATE_SUB(日期,INTERVAL 1 DAY) AS sys

例子：

SELECT id,`name`,sex ,
DATE_ADD(`create_time`,INTERVAL 1 DAY) AS sys 
FROM `hp_sys_user`

（四）date_format()函数

参数解析：

1、date：代表具体时间字段，也可以为now()查询当前时间;
2、format：DATE_FORMAT将传来的Date类型数据转为自己需要的格式,如%Y-%m-%d %H:%i:%s会将传来的Time数据转为"yyyy-MM-dd HH:mm:ss"格式%Y-%m-%d %H:%i:%s 与 yyyy-MM-dd HH:mm:ss 相对应，也是最常用的格式，这里举几个简单的例子如下；

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');  -- 结果：2020-12-07 22:18:58
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i');   -- 结果：2020-12-07 22:18
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H');     -- 结果：2020-12-07 22
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');      -- 结果：2020-12-07
SELECT DATE_FORMAT(NOW(),'%H:%i:%s');      -- 结果：22:18:58
SELECT DATE_FORMAT(NOW(),'%H');         -- 结果：22

用DATE_FORMAT函数的查询方式如下：

SELECT name as '用户名',DATE_FORMAT(createTime,'%Y-%m-%d %H') as '时间/小时',count(*) as '订单量' from t_order 
 where DATE_FORMAT(createTime,'%Y-%m-%d') = '2020-11-11'
  GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d %H');

（五）if()函数

语法结构

IF(condition, value_if_true, value_if_false)

参数说明

condition: 判断条件
value_if_true: 如果condition的结果为TRUE，返回该值
value_if_false: 如果condition的结果为FALSE，返回该值

实例

-- YES
SELECT IF(500<1000, "YES", "NO");
 
-- 测试两个字符串是否相同，如果是，则返回“YES”，否则返回“NO”：
-- NO
SELECT IF(STRCMP("hello","world") = 0, "YES", "NO");
-- YES
SELECT IF(STRCMP("hello","Hello") = 0, "YES", "NO");
-- YES
SELECT IF(STRCMP("hello","hello") = 0, "YES", "NO");
-- NO
SELECT IF(STRCMP("hello","helloo") = 0, "YES", "NO");

（六）find_in_set() 函数

特点：在类似逗号分割的字符串中，查找指定字符串，返回指定位置索引

语法结构

FIND_IN_SET(str,strlist) ;
如：FIND_IN_SET(‘b’, ‘a,b,c,d’);

参数说明

(假如字符串str在由N个子链组成的字符串列表strlist 中, 则返回值的范围在 1 到 N 之间)；
1、意思就是看str这个字符在不在或者可以说成是否包含这个strlist字符列表中, 如果有存在或包含在strlist这个字符列表中就返回一个位置的数字, 并且这个数字一定是大于0 的数字；
2、这里解释一下strlist字符串列表就是一个由一些被 逗号‘,’ 分开的单一字符串；
3、注意:如果str不在strlist 或strlist 为空字符串，则返回值为 0 , 并且如任意一个参数为空，则返回值为 0 也可以说返回NULL

示例

 SELECT FIND_IN_SET('b', 'a,b,c,d'); 
 #--结果为2 , 因为b 在strlist集合中放在2的位置 并且起始数是从1开始计算起的!

二、多行处理函数（分组函数）

特点：多个输入对应一个输出

1. count：计数
2. sum：求和
3. avg：平均值
4. max：最大值
5. min：最小值

count(*）和count(字段）的区别：

• count(字段）：表示统计该字段下所有不为null的元素总数；
• count(*)：统计表当中的总行数；（只要有一行数据count则++，因为每一行不可能都为null，一行数据中有一列不为null，则这行数据就是有效的。）

注意：

• 分组函数使用时，先分组，后计数；不分组，默认整张表为一组；
• 分组函数自动忽略null，不需要对null进行处理；
• 分组函数不能够直接使用在where子句中；
• 所有分组函数可以组合起来使用；

日期处理函数

三、常用函数——重要

（一）基础函数

1、函数列表

1.coalesce(expression,value1,value2……,valuen）：如果expression不为空值则返回expression；否则判断value1是否是空值，如果value1不为空值则返回value1；否则判断value2是否是空值；
2. with rollup：在group分组字段的基础上再进行统计数据；
3. day(): day(‘2021-08-02 11:41:01’)返回的是2；
4. dayofmonth(）:返回本月的天数；day(‘2021-08-02 11:41:01’)返回的是2；这个两个函数作用一样的效果；
5. last_day(): last_day(“2021-09-02 19:30:01”):返回本月的最后一天2021-09-30 ；last_day(‘2021-08-02 11:41:01’)返回的是2021-08-31；
6. date_formate(): date_formate(“2021-09-02 19:30:01”, “%Y%m” ) 返回的是202109；
7. union all 汇总的数据，多个表union all时不会去重记录，字段名、字段顺序、字段类型要一致。
8. using()：等价于join操作中的on，例如a和b根据id字段关联，那么以下等价using(id)on a.id=b.id

2、FIND_IN_SET() 函数——常用

应用场景：

例如：数据库中有个user表，用户角色类型有多个，role_type字段存了多个角色，如果根据角色类型条件查询的话，就可以使用，FIND_IN_SET（）函数，只要字段中包含这个值，就会查询出来；

SQL语句：

SELECT * FROM `hp_sys_user` WHERE FIND_IN_SET('1499305438955704322',`role_type`);

3、concat() 函数

1. concat(str1, str2,…): 将多个字符串连接成一个字符串,返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。
2. concat_ws()函数：concat_ws(separator, str1, str2, …)，和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator）；
   注意：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。
3. group_concat()函数：
   1、功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
   2、语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
   说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

注意：

coalesce()函数： 取别名

SELECT coalesce(name, '总金额'),name, SUM(money) as money FROM  test GROUP BY name WITH ROLLUP;

四、where、聚合函数、having 的执行顺序：

where、聚合函数、having 在from后面的执行顺序：

where>聚合函数(sum,min,max,avg,count)>having

例子： 如果想查询平均分高于80分的学生记录可以这样写：

SELECT id, COUNT(course) as numcourse, AVG(score) as avgscore
FROM student
GROUP BY id
HAVING AVG(score)>=80;

在这里，如果用WHERE代替HAVING就会出错。

注意事项 ：

1. where 后不能跟聚合函数，因为where执行顺序大于聚合函数。
2. where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
3. having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

---

五、MySQL常用表达式

1. if(): if(表达式, val1, val2),若为真则1，否则为val2

关注林哥，持续更新哦！！！★,°:.☆(￣▽￣)/$:.°★ 。