2024Mysql And Redis基础与进阶操作系列（7）作者——LJS[含MySQL 聚合、数学、字符创、日期、控制流函数等使用详解；注意点及常见报错问题所对应的解决方法]

MySQL函数

MySQL中，函数非常多，主要可以分为以下几类:

1. 聚合函数

聚合函数主要由：count,sum,min,max,avg,这些聚合函数我之前都写过，不再重叙。

这里我们学习另外一个函数:group_concat(），该函数用户实现行的合并

group_concat()函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果。

格式

group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])

补充

（1）使用distinct可以排除重复值；　　

（2）如果需要对结果中的值进行排序，可以使用order by子句；　　

（3）separator是一个字符串值，默认为逗号。

示例

create database mydb4;

use mydb4;

 
create table emp(

   emp_id int primary key auto_increment comment '编号',

   emp_name char(20) not null default '' comment '姓名',

   salary decimal(10,2) not null default 0 comment '工资',

   department char(20) not null default '' comment '部门'
);



 
insert into emp(emp_name,salary,department) values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),('刘云鹏',7800,'销售部');

将所有员工的名字合并成一行

select group_concat(emp_name) from emp;

指定分隔符合并

select department,group_concat(emp_name separator ';' ) from emp group by 
department; 

指定排序方式和分隔符

select department,group_concat(emp_name order by salary desc separator ';' ) from 
emp group by department;

2. 数学函数（即用即查，重在融会贯通与运用）

• 数学函数
• 函数名
• 实例
• 实例
• ABS(x)
• 返回 x 的绝对值
• 返回 -1 的绝对值：SELECT ABS(-1) -- 返回1
• CEIL(x)
• 返回大于或等于 x 的最 小整数
• SELECT CEIL(1.5) -- 返回2
• FLOOR(x)
• 返回小于或等于 x 的最 大整数
• 小于或等于 1.5 的整数：SELECT FLOOR(1.5) -- 返回1
• GREATEST(expr1, expr2, expr3, ...)
• 返回列表中的最大值
• 返回以下数字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); -- 34返回以下字 符串列表中的最大值：SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob
• LEAST(expr1, expr2, expr3, ...)
• 返回列表中的最小值
• 返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); -- 3返回以下字符串 列表中的最小值：SELECT LEAST("Google", "Runoob", "Apple"); -- Apple
• MAX(expression)
• 返回字段 expression 中的最大值
• 返回数据表 Products 中字段 Price 的最大 值：SELECT MAX(Price) AS LargestPrice FROM Products;
• MIN(expression)
• 返回字段 expression 中的最小值
• 返回数据表 Products 中字段 Price 的最小 值：SELECT MIN(Price) AS MinPrice FROM Products;
• MOD(x,y)
• 返回 x 除以 y 以后的余 数
• 5 除于 2 的余数：SELECT MOD(5,2) -- 1
• PI()
• 返回圆周率 (3.141593）
• SELECT PI() --3.141593
• POW(x,y)
• 返回 x 的 y 次方
• 2 的 3 次方：SELECT POW(2,3) -- 8
• RAND()
• 返回 0 到 1 的随机数
• SELECT RAND() --0.93099315644334
• ROUND(x)
• 返回离 x 最近的整数 （遵循四舍五入）
• SELECT ROUND(1.23456) --1
• ROUND(x,y)
• 返回指定位数的小数 （遵循四舍五入）
• SELECT ROUND(1.23456,3) –1.235
• TRUNCATE(x,y)
• 返回数值 x 保留到小数 点后 y 位的值（与 ROUND 最大的区别是 不会进行四舍五入
• SELECT TRUNCATE(1.23456,3) -- 1.234
• 3. 字符串函数（即用即查，重在融会贯通与运用）

• 字符串函数
• 函数
• 实例
• 实例
• CHAR_LENGTH(s)
• 返回字符串 s 的字符数
• 返回字符串 RUNOOB 的字符数SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
• CHARACTER_LENGTH(s)
• 返回字符串 s 的字符数
• 返回字符串 RUNOOB 的字符数SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;
• CONCAT(s1,s2...sn)
• 字符串 s1,s2 等多个字 符串合并为一个字符串
• 合并多个字符串SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;
• CONCAT_WS(x, s1,s2...sn)
• 同 CONCAT(s1,s2,...) 函 数，但是每个字符串之 间要加上 x，x 可以是分 隔符
• 合并多个字符串，并添加分隔符： SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
• FIELD(s,s1,s2...)
• 返回第一个字符串 s 在 字符串列表(s1,s2...)中的 位置
• 返回字符串 c 在列表值中的位置： SELECT FIELD("c", "a", "b", "c", "d", "e");
• LTRIM(s)
• 去掉字符串 s 开始处的 空格
• 去掉字符串 RUNOOB开始处的空格： SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB
• MID(s,n,len)
• 从字符串 s 的 n 位置截 取长度为 len 的子字符 串，同 SUBSTRING(s,n,len)
• 去掉字符串 s 开始处的 空格
• POSITION(s1 IN s)
• 从字符串 s 中获取 s1 的 开始位置
• 返回字符串 abc 中 b 的位置：SELECT POSITION('b' in 'abc') -- 2
• REPLACE(s,s1,s2)
• 将字符串 s2 替代字符串 s 中的字符串 s1
• 将字符串 abc 中的字符 a 替换为字符 x：SELECT REPLACE('abc','a','x') --xbc
• REVERSE(s)
• 将字符串s的顺序反过来
• 将字符串 abc 的顺序反过来：SELECT REVERSE('abc') -- cba
• RIGHT(s,n)
• 返回字符串 s 的后 n 个 字符
• 返回字符串 runoob 的后两个字符： SELECT RIGHT('runoob',2) -- ob
• RTRIM(s)
• 去掉字符串 s 结尾处的 空格
• 去掉字符串 RUNOOB 的末尾空格： SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB
• STRCMP(s1,s2)
• 比较字符串 s1 和 s2， 如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1返回 -1
• 比较字符串：SELECT STRCMP("runoob", "runoob"); -- 0
• SUBSTR(s, start, length)
• 从字符串 s 的 start 位置 截取长度为 length 的子 字符串
• 从字符串 RUNOOB 中的第 2 个位置截 取 3个 字符：SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO
• TRIM(s)
• 去掉字符串 s 开始和结 尾处的空格
• 去掉字符串 RUNOOB 的首尾空格： SELECT TRIM(' RUNOOB ') AS TrimmedString;
• UCASE(s)
• 将字符串转换为大写
• 将字符串 runoob 转换为大写：SELECT UCASE("runoob"); -- RUNOOB
• UPPER(s)
• 将字符串转换为大写
• 将字符串 runoob 转换为大写：SELECT UPPER("runoob"); -- RUNOOB
• LCASE(s)
• 将字符串 s 的所有字母 变成小写字母
• 字符串 RUNOOB 转换为小写：SELECT LCASE('RUNOOB') -- runoob
• LOWER(s)
• 将字符串 s 的所有字母 变成小写字母
• 字符串 RUNOOB 转换为小写：SELECT LOWER('RUNOOB') -- runoob
• 4. 日期函数（即用即查，重在融会贯通与运用）

• 日期函数——用法+举例
• 函数名
• 描述
• 举例
• UNIX_TIMESTAMP()
• 返回从1970-01-01 00:00:00到当前毫秒值
• select UNIX_TIMESTAMP() -> 1632729059
• UNIX_TIMESTAMP(DATE_STRING)
• 将制定日期转为毫秒值时间戳
• SELECT FROM_UNIXTIME(1598079966,'%Y-%m-%d %H:%i:%s'); (1598079966,'%Y-%m-%d %H:%i:%s'); -> 2020-08-22 15-06-06
• FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT])
• 将毫秒值时间戳转为指定格式日期
• SELECT CURDATE();-> 2024-06-19
• CURDATE()
• 返回当前日期
• SELECT CURRENT_DATE();-> 2022-02-19
• CURRENT_DATE()
• 返回当前日期
• SELECT CURRENT_TIME();-> 19:33:03
• CURRENT_TIME
• 返回当前时间
• SELECT CURTIME();-> 19:59:02
• CURTIME()
• 返回当前时间
• SELECT CURRENT_TIMESTAMP()-> 2013-09-19 22:57:42
• CURRENT_TIMESTAMP()
• 返回当前日期和时间
• SELECT DATE("2017-06-15"); -> 2017-06-15
• CURRENT_TIMESTAMP()
• 从日期或日期时间表达式中提取日期值
• SELECT DATEDIFF('2001-01-01','2001-02-02')-> -32
• DATEDIFF(d1,d2)
• 计算日期 d1->d2 之间相隔的天数
• SELECT TIMEDIFF("13:10:11", "13:10:10");-> 00:00:01
• TIMEDIFF(time1, time2)
• 计算时间差值
• SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')-> 2011-11-11 11:11:11 AM
• DATE_FORMAT(d,f)
• 按表达式 f的要求显示日期 d
• SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");-> 2017- 08-10
• STR_TO_DATE(string, format_mask)
• 将字符串转变为日期
• Orders 表中 OrderDate 字段减去 2 天：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDateFROM Orders
• DATE_SUB(date,INTERVAL expr type)
• 函数从日期减去指定的时间间隔。
• Orders 表中 OrderDate 字段减去 2 天：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDateFROM Orders
• ADDDATE/DATE_ADD(d， INTERVAL expr type)
• 计算起始日期 d 加上一个时间段后的日期，type 值可以是：MICROSECONDSECONDMINUTEHOURDAYWEEKMON
• THQUARTER
• YEARDAY
• _MINUTEDAY_HOURYEAR_MONTH
• SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); -> 2017-06- 25 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);-> 2017-06-15 09:49:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017-06-15 06:34:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017- 04-15
• DATE_ADD(d，INTERVAL expr type)
• 计算起始日期 d 加上一个时间段后的日期，type 值可以是： SECOND_MICROSECONDMINUTE
• _MICROSECONDMINUTE
• _SECONDHOUR_
• MICROSECONDHOUR_SECONDHOUR
• _MINUTEDAY_
• MICROSECONDDAY_SECONDDAY
• _MINUTEDAY_HOURYEAR
• _MONTH
• SELECT EXTRACT(MINUTE FROM '201-11-11 11:11:11') -> 11
• EXTRACT(type FROM d)
• 从日期 d 中获取指定的值，**type** 指定返回的值。
• type可取值为：
• MICROSECONDSECONDMINUTEHOUR…..**
• SELECT LAST_DAY("2017-06-20");-> 2017-06-30
• EXTRACT(type FROM d)
• 返回给给定日期的那一月份的最后一天
• SELECT MAKEDATE(2016, 3);-> 2016-01-03
• EXTRACT(type FROM d)
• 基于给定参数年份 year 和所在年中的天数序号
• day-of-year 返回一个日期
• SELECT YEAR("2015-7-8");-> 2015
• EXTRACT(type FROM d)
• 返回年份
• SELECT MONTH('2071-7-15 11:11:11')->11
• MONTH(d)
• 返回日期d中的月份值，1 到 12
• SELECT DAY("2015-08-15"); -> 15
• DAY(d)
• 返回日期值 d 的日期部分
• SELECT HOUR('1:2:3')-> 1
• HOUR(t)
• 返回 t 中的小时值
• SELECT MINUTE('1:2:3')-> 2
• MINUTE(t)
• 返回 t 中的分钟值
• SELECT SECOND('1:2:3')-> 3
• SECOND(t)
• 返回 t 中的秒钟值
• SELECT QUARTER('2021-7-18 13:13:14')-> 4
• QUARTER(d)
• 返回日期d是第几季节，返回 1 到 4
• SELECT MONTHNAME('2011-11-11 11:11:11')-> November
• QUARTER(d)
• 返回日期当中的月份名称，如 November
• SELECT MONTH('2022-1-12 2:21:31')->11
• MONTH(d)
• 返回日期d中的月份值，1 到 12
• SELECT DAYNAME('2012-11-11 11:11:11')->Friday
• DAYNAME(d)
• 返回日期 d 是星期几，如 Monday,Tuesday
• SELECT DAYOFMONTH('2001-3-5 5:6:17')->11
• DAYOFMONTH(d)
• 计算日期 d 是本月的第几天
• SELECT DAYOFWEEK('2013-1-12 13:21:11')->6
• DAYOFWEEK(d)
• 日期 d 今天是星期几，1 星期日，2 星期一，以此类推
• SELECT DAYOFYEAR('2013-4-1 1:12:51')->315
• DAYOFYEAR(d)
• 计算日期 d 是本年的第几天
• SELECT DAYOFYEAR('2011-11-11 11:11:11')->315
• WEEK(d)
• 计算日期 d 是本年的第几个星期，范围是 0 到 53
• SELECT WEEK('2011-11-11 11:11:11')-> 45
• WEEKDAY(d)
• 日期 d 是星期几，0 表示星期一，1 表示星期二
• SELECT WEEKDAY("2017-06-15");-> 3
• 5. 控制流函数（即用即查，重在融会贯通与运用）
• if逻辑判断语句

• if逻辑判断语句
• 格式
• 解释
• 举例
• IF(expr,v1,v2)
• 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。
• SELECT IF(1 > 0,'正确','错误') - >正确
• IFNULL(v1,v2)
• 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2
• SELECT IFNULL(null,'Hello Word')->Hello Word
• ISNULL(expression)
• 判断表达式是否为 NULL
• SELECT ISNULL(NULL);->1
• NULLIF(expr1, expr2)
• 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返 回 expr1
• SELECT NULLIF(25, 25);->
• case when语句 （类似于C语言的if（condition）语句——case）

• case when语句
• 格式
• 解释
• 操作
• CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END
• CASE 表示函数开始，END 表示 函数结束。如果 condition1 成 立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后 面的就不执行了。
• select case 100 when 50 then 'tom' when 100 then 'mary'else 'tim' end ; select case when 1=2 then 'tom' when 2=2 then 'mary' else'tim' end ;

举例实操

use mydb4

创建订单表

create table orders(
 
    oid int primary key, -- 订单id
 
    price double, -- 订单价格
 
    payType int -- 支付类型(1:微信支付 2:支付宝支付 3:银行卡支付 4：其他)
);


insert into orders values(1,1200,1);

insert into orders values(2,1000,2);

insert into orders values(3,200,3);

insert into orders values(4,3000,1);

insert into orders values(5,1500,2);

方法1

select *  ,
case 
    when payType=1 then '微信支付' 

    when payType=2 then '支付宝支付'
 
    when payType=3 then '银行卡支付' 

    else '其他支付方式' 

end  as payTypeStr

from orders;

方法2

select *  ,

case payType

  when 1 then '微信支付' 

    when 2 then '支付宝支付'
 
    when 3 then '银行卡支付' 

    else '其他支付方式' 

end  as payTypeStr

from orders;