本节介绍MySQL函数,包括聚合函数、数学函数、字符串函数、日期函数、控制流函数。窗口函数在下一节介绍。

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

  • 聚合函数

  • 数学函数

  • 字符串函数

  • 日期函数

  • 控制流函数

  • 窗口函数

聚合函数

Ø在MySQL中,聚合函数主要由:countsumminmaxavg,这些聚合函数我们之前都学过,不再重复。

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

格式:

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,'销售部');


-- GROUP_CONCAT(expr)的用法:列 转 行

-- 将所有员工的名字合并成一行
-- 结果:张晶晶,王飞飞,赵刚,刘小贝,王大鹏,张小斐,刘云云,刘云鹏,刘云鹏  分隔符默认是逗号
select GROUP_CONCAT(emp_name) from emp;
-- 指定分隔符,例如顿号
-- 返回结果:张晶晶、王飞飞、赵刚、刘小贝、王大鹏、张小斐、刘云云、刘云鹏、刘云鹏
select GROUP_CONCAT(emp_name SEPARATOR '、' ) from emp;

-- 指定排序方式和分隔符
select department,GROUP_CONCAT(emp_name SEPARATOR '、') from emp GROUP BY department;
-- 返回结果:
-- 人事部  刘小贝、王大鹏、张小斐
-- 财务部  张晶晶、王飞飞、赵刚
-- 销售部  刘云云、刘云鹏、刘云鹏
-- 下面让拼接的按照薪资降序排列,注意SEPARATOR 必须放在括号的最后面
select department,GROUP_CONCAT(emp_name  ORDER BY salary desc  SEPARATOR '、') from emp GROUP BY department;
-- 结果:每一个部门都是按照薪资的降序排列
-- 人事部  王大鹏、刘小贝、张小斐
-- 财务部  赵刚、王飞飞、张晶晶
-- 销售部  刘云鹏、刘云云、刘云鹏

数学函数

函数名 描述 实例
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

字符串函数

函数 描述 实例
LTRIM(s) 去掉字符串 s 开始处的空格 去掉字符串 RUNOOB开始处的空格: SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB
MID(s,n,len) 从字符串 s 的 n 位置截取长度为 len 的子字符串,同 SUBSTRING(s,n,len) 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符: SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO
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
函数 描述 实例
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");
函数 描述 实例
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<s2 返回 -1 比较字符串: SELECT STRCMP("runoob", "runoob"); -- 0
SUBSTR(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符: SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符: SELECT SUBSTRING("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

日期函数

函数名 描述 实例
UNIX_TIMESTAMP() 返回从1970-01-01 00:00:00到当前毫秒值 select UNIX_TIMESTAMP() -> 1632729059
UNIX_TIMESTAMP(**DATE_STRING**) 将制定日期转为毫秒值时间戳 SELECT UNIX_TIMESTAMP('2011-12-07 13:01:03');
FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT]) 将毫秒值时间戳转为指定格式日期 SELECT FROM_UNIXTIME(1598079966,'%Y-%m-%d %H:%i:%s'); (1598079966,'%Y-%m-%d %H:%i:%s'); -> 2020-08-22 15-06-06
CURDATE() 返回当前日期 SELECT CURDATE(); -> 2018-09-19
CURRENT_DATE() 返回当前日期 SELECT CURRENT_DATE(); -> 2018-09-19
函数名 描述 实例
CURRENT_TIME 返回当前时间 SELECT CURRENT_TIME(); -> 19:59:02
CURTIME() 返回当前时间 SELECT CURTIME(); -> 19:59:02
CURRENT_TIMESTAMP() 返回当前日期和时间 SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43
DATE() 从日期或日期时间表达式中提取日期值 SELECT DATE("2017-06-15"); -> 2017-06-15
DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数 SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32
函数名 描述 实例
TIMEDIFF(time1, time2) 计算时间差值 SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01
DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -> 2011-11-11 11:11:11 AM
STR_TO_DATE(string, format_mask) 将字符串转变为日期 SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10
DATE_SUB(date,INTERVAL expr type) 函数从日期减去指定的时间间隔。 Orders 表中 OrderDate 字段减去 2 天: SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders

![adddata/data_add函数](http://my.52txr.cn/image-20220107160530285.png

type 值还可以是:

·SECOND_MICROSECOND

·MINUTE_MICROSECOND

·MINUTE_SECOND

·HOUR_MICROSECOND

·HOUR_SECOND

·HOUR_MINUTE

·DAY_MICROSECOND

·DAY_SECOND

·DAY_MINUTE

·DAY_HOUR

·YEAR_MONTH

EXTRACT(type FROM d) 从日期 d 中获取指定的值,type指定返回的值。 type可取值为:·MICROSECOND ·SECOND ·MINUTE ·HOUR ….. SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') > 11
LAST_DAY(d) 返回给给定日期的那一月份的最后一天 SELECT LAST_DAY("2017-06-20"); -> 2017-06-30
MAKEDATE(year, day-of-year) 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 SELECT MAKEDATE(2017, 3); -> 2017-01-03
函数名 描述 实例
YEAR(d) 返回年份 SELECT YEAR("2017-06-15"); -> 2017
MONTH(d) 返回日期d中的月份值,1 到 12 SELECT MONTH('2011-11-11 11:11:11') ->11
DAY(d) 返回日期值 d 的日期部分 SELECT DAY("2017-06-15"); -> 15
HOUR(t) 返回 t 中的小时值 SELECT HOUR('1:2:3') -> 1
MINUTE(t) 返回 t 中的分钟值 SELECT MINUTE('1:2:3') -> 2
SECOND(t) 返回 t 中的秒钟值 SELECT SECOND('1:2:3') -> 3
QUARTER(d) 返回日期d是第几季节,返回 1 到 4 SELECT QUARTER('2011-11-11 11:11:11') -> 4
函数名 描述 实例
MONTHNAME(d) 返回日期当中的月份名称,如 November SELECT MONTHNAME('2011-11-11 11:11:11') -> November
MONTH(d) 返回日期d中的月份值,1 到 12 SELECT MONTH('2011-11-11 11:11:11') ->11
DAYNAME(d) 返回日期 d 是星期几,如 Monday,Tuesday SELECT DAYNAME('2011-11-11 11:11:11') ->Friday
DAYOFMONTH(d) 计算日期 d 是本月的第几天 SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11
DAYOFWEEK(d) 日期 d 今天是星期几,1 星期日,2 星期一,以此类推 SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6
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
WEEKOFYEAR(d) 计算日期 d 是本年的第几个星期,范围是 0 到 53 SELECT WEEKOFYEAR('2011-11-11 11:11:11') -> 45
YEARWEEK(date, mode) 返回年份及第几周(0到53),mode 中 0 表示周天,1表示周一,以此类推 SELECT YEARWEEK("2017-06-15"); -> 201724
NOW() 返回当前日期和时间 SELECT NOW() -> 2018-09-19 20:57:43

太多了。。。。需要用到自己查。。。。。。

控制流函数

IF 语句

格式 解释 案例
IF(expr,v1,v2) 如果表达式 expr 成立,返回结果 v1;否则,返回结果 v2。 SELECT IF(1 > 0,'正确','错误') ->正确
[IFNULL(v1,v2)](https://www.runoob.com/mysql/mysql-func-ifnull.html 如果 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); ->

演示IF(expr,v1,v2)

case when语句

有点像c语言里的switch...case...句子

语法:

CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END

CASE 表示函数开始,END 表示函数结束。如果 condition1 成立,则返回 result1, 如果 condition2 成立,则返回 result2,当全部不成立则返回 result,而当有一个成立之后,后面的就不执行了。

示例:

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;

声明:内容来源于B站视频《2022黑马程序员最新MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程》,博客内容仅作学习参考使用。



博主个人公众号
版权声明 ▶ 本网站名称:陶小桃Blog
▶ 本文链接:https://www.52txr.cn/2022/mysql16.html
▶ 本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长进行核实删除。
▶ 转载本站文章需要遵守:商业转载请联系站长,非商业转载请注明出处!!

最后修改:2022 年 06 月 05 日
如果觉得我的文章对你有用,请随意赞赏