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

原创

wx66e1aa24afc0e 2024-09-13 17:55:36 ©著作权

文章标签 mysql 数据库 redis linux sql 文章分类 Python 后端开发

©著作权归作者所有：来自51CTO博客作者wx66e1aa24afc0e的原创作品，请联系作者获取转载授权，否则将追究法律责任

目录

MySQL函数1. 聚合函数格式补充示例将所有员工的名字合并成一行指定分隔符合并指定排序方式和分隔符
2. 数学函数（即用即查，重在融会贯通与运用）
3. 字符串函数（即用即查，重在融会贯通与运用）
4. 日期函数（即用即查，重在融会贯通与运用）
5. 控制流函数（即用即查，重在融会贯通与运用）if逻辑判断语句case when语句（类似于C语言的if（condition）语句——case）举例实操创建订单表方法1方法1

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;