MySQL的常用函数+示例代码，小白建议收藏！

最新推荐文章于 2024-07-11 21:41:59 发布

haj6468

最新推荐文章于 2024-07-11 21:41:59 发布

阅读量222

点赞数 3

文章标签： mysql 数据库 sql 面试大数据

本文链接：https://blog.csdn.net/haj6468/article/details/139870555

版权

MySQL的函数

聚合函数

group_concat

语法

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

概念

group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [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,'销售部');

-- 将所有员工的名字合成一行
# 将整张表当做是一组，将emp_name这一列的值进行拼接
select  group_concat(emp_name) from emp;

# 按照department进行分组，将同一组的emp_name按照_进行拼接
select  department,group_concat(emp_name separator '_') from emp group by department ;

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
# 查询学号为01的学生学习的科目，对科目进行拼接
select  Sid,group_concat(CId order by CId   separator '_') as c1 from SC where Sid = '01'  group by Sid ;
# 查询所有的学生学习的科目，对科目进行拼接
select  Sid,group_concat(CId order by CId   separator '_') as c2 from SC group by Sid ;

# 将拼接后的结果进行对比
select
 t2.SId
from
        (select  Sid,group_concat(CId order by CId   separator '_') as c1 from SC where Sid = '01'  group by Sid ) t1
         join
         (select  Sid,group_concat(CId order by CId   separator '_') as c2 from SC group by Sid ) t2
        on t1.c1 = t2.c2
where t2.SId != '01';

数学函数

-- 1、求绝对值 abs
select abs(-5);

-- 查询财务部最低工资和人事部最低工资之间的差值
select abs((select  min(salary) from emp where department = '财务部') - (select  min(salary) from emp where department = '人事部'));

-- 2、向上取整 ceil
select ceil(5.1);  -- 6

-- 3、向下取整 floor
select floor(5.9); -- 5

-- 4、求列表的最大值
select greatest(1,2,3);  -- 3

-- 5、求列表的最小值
select least(1,2,3);  -- 1

-- 6、求余数
select mod(10,3);  -- 取10 除以 3的余数

-- 7、返回圆周率
select pi();

-- 8、求x的y次方
select pow(2,3); -- 2的3次方  8
select pow(2,10); -- 2的10次方  1024

-- 9、获取随机数
-- 获取0-1之间的随机数 ，包含0，不包含1
select rand();

-- 获取1-100之间的随机数
-- [0,1) * 100 ---> [0,99.99] + 1 ----> [1,100.99] -->向下取整 --->[1,100]
select floor(rand() * 100 + 1);


-- 10、四舍五入函数 - 取整
select round(5.49999); # 5
select round(5.50000); # 6

-- 11、四舍五入函数 - 带小数
select round(5.44999,2);  # 5.45

select round(5.444,2);  # 5.44

-- 12、截取指定的数字，不会四舍五入
select truncate(5.44999,2);

字符串函数

#----------字符串函数------------
-- 1、获取字符串长度
select char_length('我爱你中国');  # 5
select character_length('我爱你中国');  # 5 同上

-- 2、字符串拼接
select concat('我爱你','中国','广州');
select concat(emp_name,'_',department) from emp;
select concat('2023','-','12','-','23');

-- 3、字符串拼接,指定固定的分隔符,第一个参数就是分隔符
select concat_ws('-','2023','12','23');

-- 4、去除空格
select trim(' 中国  ');  # 去除两端空格
select ltrim(' 中国  ');  # 去除左边空格
select rtrim(' 中国  ');  # 去除右边边空格

-- 5、replace替换
select replace('我爱你中国and中国','中国','广州'); -- 将字符串中的中国全部替换为广州

-- 6、字符串翻转
select reverse('我爱你中国');

-- 7、获取字符串的后几个字符
select right('17812345678',4); # 获取手机号的后四位
select left('17812345678',3); # 获取手机号的前四位

-- 8、字符串截取
# 第一个数字表示从哪个字符(从1开始)开始，第二个数字表示截取的长度
select substr('2023-12-23',1,4);  # 2023
select substring('2023-12-23',1,4);  # 2023

# 如果只填写一个数字，表示从这个字符开始，截取到最后
select substr('2023-12-23',2); # 023-12-23

select substr('2023-12-23',6,2);  # 12

--  统计emp表中，每一种姓氏的人数
select substr(emp_name,1,1),count(*) from emp group by  substr(emp_name,1,1)

# 9、将字母转为大写
select  upper('hello');

# 10、将字母转为小写
select  lower('HeLLo');

# 应用场景：不区分大小写来进行字符串比较 ,则可以把字符串都转为小写或者大写，再来比较

日期函数

#-------------日期函数-----------------
-- 1、获取当前时间
select current_time(); # 16:35:52
select curtime();      # 16:36:07
select current_timestamp(); # 2024-04-05 16:36:17
select curdate()      # 2024-04-05
select now();               # 2024-04-05 16:36:27


-- 2、获取从1970年到此时此刻的秒值
-- 这个毫秒值可以用来做时间计算
-- select  UNIX_TIMESTAMP() -  UNIX_TIMESTAMP()  / 3600 / 24;
select  UNIX_TIMESTAMP() ;

-- 3、将一个日期转为毫秒值
select UNIX_TIMESTAMP('2008-08-08'); # 1218124800 --->1970年到2008年一共过了1218124800秒

-- 需求假如一个商品的的下单日期是：2023-12-23， 我想统计最近3个月的下单量
select * from emp where (UNIX_TIMESTAMP() - UNIX_TIMESTAMP('2023-12-23'))/3600/24/30 <= 3;
select (UNIX_TIMESTAMP() - UNIX_TIMESTAMP('2000-01-05'))/3600/24;


-- 4、从一个日期中获取年月日
select date('2000-10-12 12:34:56');  # 获取年月日 2000-10-12
select year('2000-10-12 12:34:56');  # 获取年 2000
select month('2000-10-12 12:34:56');  # 获取月 10
select day('2000-10-12 12:34:56');  # 获取日 12
select hour('2000-10-12 12:34:56');  # 获取时 12
select minute('2000-10-12 12:34:56');  # 获取分 34
select second('2000-10-12 12:34:56');  # 获取秒 56

select WEEK('2024-04-05 12:34:56');     # 13  获取今年的第几周

# 0 表示星期一，  1 表示星期二 2 表示星期三 3表示星期四.... 6表示星期日
select weekday('2024-04-07 12:34:56');  # 4 获取今天是周几，从0开始

# 获取季度
select quarter('2024-04-07 12:34:56'); -- # 2 第二季度

# 日期的格式化 2024-1-1 1:1:1 ----> 2024-01-01 01:01:01
select date_format('2024-1-1 1:1:1','%Y-%m-%d %H:%i:%s');
-- 2024-01-01 01:01:01
select date_format('2024-1-1 1:1:1','%Y-%m-%d %H:%i:%s');
-- 2024-01-01 01:01:01 ->2024/01/01
select date_format('2024-1-1 1:1:1','%Y/%m/%d');

-- 2024-01-01 01:01:01 ->2024年01月01日
select date_format('2024-1-1 1:1:1','%Y年%m月%d日');
select date_format('2024-1-1 1:1:1','%m月%d日');

#----------------------------方式1------------------------------------
# 计算两个时间之间的差值,相差了多少天
select datediff('2024-04-05', '2024-04-01');
select datediff(curdate(), '2000-01-05');

select abs(datediff('2000-01-05',curdate()));

#----------------------------方式2------------------------------------
# 计算两个时间之间相差多少天
SELECT TIMESTAMPDIFF(YEAR, '2020-01-01', '2023-01-01') AS years_difference;
# 计算两个时间之间相差多少月
SELECT TIMESTAMPDIFF(MONTH , '2020-01-01', '2023-01-01') AS years_difference;
# 计算两个时间之间相差多少天
SELECT TIMESTAMPDIFF(DAY , '2020-01-01', '2023-01-01') AS years_difference;


#日期向前或者向后推几天
SELECT date_add('2017-06-15', INTERVAL 10 DAY);     -- 2017-06-25 向后推10天

SELECT date_add('2017-06-15', INTERVAL -10 DAY); -- 2017-06-05 向前推10天
select date_sub('2017-06-15', INTERVAL 10 DAY);  -- 2017-06-05 向前推10天  作用同上

SELECT date_add('2017-06-15 09:34:21', INTERVAL 15 MINUTE);  -- 时间向后推15分钟

SELECT date_add('2017-06-15 09:34:21', INTERVAL -3 HOUR); -- 时间向前推3个小时

SELECT date_add('2017-06-15 09:34:21', INTERVAL -3 year); -- 时间向前推3年