数据分析——SQL基础知识之常见函数（三）

原创已于 2025-08-11 18:17:01 修改 · 1.9k 阅读

26 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#数据分析 #sql #笔记 #python #mysql

于 2025-06-04 09:05:52 首次发布

数据分析专栏收录该内容

4 篇文章

订阅专栏

该文章已生成可运行项目，

学习网站：菜鸟教程–SQL
b站视频链接：黑马程序员 MySQL数据库入门到精通
前几节内容：
数据分析——SQL基础知识（一）
数据分析——SQL基础知识（二）

三、常见函数

3.1 常见的函数

1. round(x,y)：四舍五入函数

round函数对x值进行四舍五入，精确到小数点后y位
y为负值时，保留小数点左边相应的位数为0，不进行四舍五入
例如：round(3.15,1)返回3.2,round(14.15,-1)返回10

2. concat(s1,s2…)：连接字符串函数

concat函数返回连接参数s1、s2等产生的字符串
任一参数为null时，则返回null
例如：concat("My; “SQL”)返回My SQL, concat("My’,null;'SQL)返回null
计算百分比，添加百分号可以用这个方法添加‘%’

3. replace(s,s1,s2)：替换函数

replace函数使用字符串s2代替s中所有的s1
例如：replace(‘MySQLMySQL;SQL’;'sql)返回MysqIMysql

4. left(s,n)、right(s,n)&substring(s,n,len)：截取字符串一部分的函数

left函数返回字符串s最左边n个字符
right函数返回字符串s最右边n个字符
substring函数返回字符串s从第n个字符起取长度为len的子字符串，n也可以为负值，则从倒数第n个字符起取长度为len的子字符串，没有len值则取从第n个字符起到最后一位
例如:
left(‘abcdefg’,3) 返回 abc
right('abcdefg;,4) 返回 defg,
substring(‘abcdefg;2,3) 返回 bcd,
substring("abcdefg’;-2,3) 返回 fg,
substring('abcdefg’2) 返回 bcdefg。

5. cast(x as type)：转换数据类型的函数
cast函数将一个类型的x值转换为另一个类型的值。type参数可以填写char(n)、date、time、datetime、decimal等转换为对应的数据类型

6. year(date)、month(date)&day(date)：获取年月日的函数

date可以是年月日组成的日期，也可以是年月日时分秒组成的日期时间
year(date)返回日期格式中的年份
month(date)返回日期格式中的月份
weekday(date)返回日期格式中的星期
day(date)返回年日期格式中的日份
month_to_date(date)返回从月初到当前日期的累计数据，用于评估一个月内某个时间段内的业绩、销售、流量等数据。
例如：year(‘2021-08-03’) 返回 2021, month(‘2021-08-03’) 返回 8, day(‘2021-08-03’) 返回 3

其他日期函数参考：菜鸟教程

7. if(expr,v1,v2)

如果表达式expr是true返回值v1，否则返回v2
例如:if(1<2,‘Y’,‘N’) 返回 Y, if(1>2,‘Y’,‘N’) 返回 N

8. ifnull(value1,value2)

如果value1不为空,返回value1,否则返回value2
ifnull仅接受两个参数用于替换NULL值

9.coalesce(value1,value2, …)

用于从一系列的参数中返回第一个非空值
可接受任意数量参数，返回第一个非NULL参数，若全为NULL则返回NULL。‌
表连接中一般用coalesce，不用ifnull

3.2 字符串函数

在这里插入图片描述
1. concat(S1,S2,…,Sn)：字符串拼接

Select concat('Hello','MySQL');     --> Hello MySQL

2. lower(str)：将字符串str全部转为小写

Select lower('Hello');              --> hello

3. upper(str)：将字符串str全部转为大写

Select upper(Hello);                --> HELLO

4. lpad(str,n,pad)：左填充，用字符串pad对str对左边进行填充，达到n个字符串长度

Select lpad('01',5,'.');            --> ...01

5. rpad(str,n,pad)：右填充，用字符串pad对str对右边进行填充，达到n个字符串长度

Select rpad('01',5,'.');             --> 01...

6. trim(str)：去除字符串头部和尾部的空格

Select trim(' Hello MySQL ');        --> Hello MySQL

7. substring(str,start,len)：返回从字符串str从start位置起到len个长度的字符串

Select substring('Hello MySQL',1,5); --> Hello

练习1：由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如：1号员工的工号应该为00001。

update emp set workno = lpad(workno, 5,'0');  //更新emp表的工号数据

8.substring_index（“待截取有用部分的字符串”，“截取数据依据的字符”，截取字符的位置N）

// 取第1个逗号前的字符串 ：
substring_index('衣服,green,12,hc',',','2')  -->衣服
// 取最后1个逗号后的字符串 ：
substring_index('衣服,green,12,hc',',','-1')  -->hc
// 取第2个逗号前那部分字符串里，最后逗号后面的部分
substring_index(substring_index('衣服,green,12,hc',',','2'),',','-1')  -->green

注意：N可以为负数，表示倒数第N个索引字符后面的字符串。（有负号的时候，可以将整个字符倒过来看，依旧是第N个字符前面的部分）

9.length()：计算字符串的字节长度

select length('hello');  -- 结果为5（每个字符1字节）
select length('hello ');  -- 结果为6（空格占1字节）
select length('你好');  -- 结果为6（每个中文字符3字节）
select length('￥');  -- 结果为3（特殊字符如'￥'，3字节）

10.char_length()：计算字符串的字符长度

select char_length('hello');  -- 结果为5
select char_length('hello ');  -- 结果为6
select char_length('你好');  -- 结果为2
select char_length('￥');  -- 结果为1

注意：length()和char_length()的区别

计算方式：

length()函数计算的是字符串的字节长度。
char_length()函数计算的是字符串的字符长度。

适用场景：

如果你需要知道字符串在存储或传输时占用的字节数，应该使用length()。
如果你需要知道字符串的实际字符数量（例如统计文章的字数），应该使用char_length()。

3.3 数值函数

在这里插入图片描述
1. ceil(x)：向上取整

Select ceil(1.2);                    --> 2

2. floor(x)：向下取整

Select floor(1.2);                   --> 1

3. mod(x,y)：返回x/y的值

Select mod(6,4);                     --> 2

4.rand()：返回0~1内的随机数

select rand();                       --> 0.001860450954977843

5.round(x,y) 求参数x的四舍五入的值，保留y为小数

select round(2.67869,2);             --> 2.68

6.abs(x)求参数x的绝对值

select abs(-2.4);                    --> 2.4

练习2：通过数据库的函数，生成一个六位数的随机验证码

select rpad(round(rand()*1000000,0),6,'0');

3.4 日期函数

在这里插入图片描述
1. curdate()：返回当前日期

select curdate();                  --> 2025-06-03

2. curtime()：返回当前时间

select curtime();                  --> 21:34:54

3. now()：返回当前日期和时间

select now();                      --> 2025-06-03 21:34:54

4. year(data)：获取指定date的年份

select year(now());                --> 2025

5. month(data)：获取指定date的月份

select month(now());               --> 6

6. weekday(data)：获取指定date的星期

select day(now());                --> 2

7. day(data)：获取指定date的日期

select day(now());                --> 3

8. date_add(date,inteval exper type)：返回一个日期/时间值加上一个时间间隔expr后的时间值

//在当前时间的基础上添加70天后的时间          --> 2025-08-12 21:43:05
select date_add(now(),interval 70 day); 
//在当前时间的基础上添加70个月后的时间
select date_add(now(),interval 70 month);   --> 2031-04-03 21:43:25

9.datediff(date1,date2)：返回起始时间date1和结束时间date2之间的天数

select datediff('2024-09-21','2024-08-26'); --> 26

10.month_to_date(date)：返回从月初到当前日期的累计数据，用于评估一个月内某个时间段内的业绩、销售、流量等数据。

select month_to_date(date); --> 显示这个date所处这个月份的第一天

11. date_format(date, format)：用于根据指定的格式字符串来格式化日期和时间值。DATE_FORMAT函数的详细用法

date_format(2024-10-15 13:45:12,'%Y-%m');  -->2024-10

12. timestampdiff(unit, datetime_expr1, datetime_expr2)：计算两个日期或时间之间的差异，并以指定的单位返回结果。timestampdiff函数介绍

unit：指定返回差异的时间单位。常见单位包括：SECOND、MINUTE、HOUR、DAY、MONTH、YEAR 等。
datetime_expr1：第一个日期或时间表达式，通常是较早的时间。
datetime_expr2：第二个日期或时间表达式，通常是较晚的时间。

timestampdiff(DAY, '2023-01-01', '2024-01-01');  -->365
timestampdiff(HOUR, '2023-10-21 08:00:00', '2023-10-21 16:30:00')  -->8

练习3：查询所有员工的入职天数，并根据入职天数倒序排序

select name, datediff(curdata(),entrydate) as date from emp order by date desc

12.date_sub(date, interval expr unit)：从指定日期中减去一个时间间隔

date_sub('2023-01-11', interval 8 day)  --> 2023-01-03

unit：指定返回差异的时间单位。常见单位包括：SECOND、MINUTE、HOUR、DAY、MONTH、YEAR 等。

3.5 流程控制函数

在这里插入图片描述
1. if(value,t,f)：如果value为ture，则返回t,否则返回f

select if(true,'Ok','Error');           --> ok
select if(false,'Ok','Error');          --> Error

2. ifnull(value1,value2)：如果value1不为空,返回value1,否则返回value2

select ifnull('Ok','Default');          --> ok
select ifnull('','Default');            --> 
select ifnull(null,'Default');          --> Default

3. case when [val1] then [res1]…else [default] end：如果val1为true,返回res1,…否则返回default默认值(有范围)

4. case [expr] when [val1] then [res1]…else [default] end：如果expr的值等于val1,返回res1,…否则返回default默认值(具体值)

练习题4：查询emp表的员工姓名和工作地址(北京/上海------>一线城市, 其他------>二线城市)

select name,(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址' from emp;

练习题：统计班级各个学员的成绩，展示的规则如下: （有范围）
x >= 85，展示优秀
x >= 60，展示及格
否则，展示不及格

select id,name,(case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end) as 'math_score',
               (case  when english>=85 then '优秀' when english>=60 then '及格' else '不及格' end) as 'english_score',
               (case when chinese>=85 then '优秀' when chinese>=60 then '及格' else '不及格' end) as 'chinese_score'
       from score;