SQL数据分析之MySQL数字、字符、日期函数-可用于where和select【精心整理】

一、数字函数

1、round四舍五入
1）用法示例

# 保留整数位四舍五入
select round(1.49);

#指定保留小数点后几位
select round(3.14159,3);

执行结果：1 3.142

2）案例练习：查询国家名称及其首都名称都以相同的字母开头的国家名及其首都，且不能包括国家名称和首都名称相同的情况

select name,capital
from world
where left(name,1)=left(capital,1)
and name!=capital

2、 ceil向上取整，>= 该参数的最小整数

select ceil(3.1415)

select ceil(-3.1415)

执行结果：
4
-3

3、floor向下取整，<=该参数的最大整数

select floor(3.1415);

select floor(-3.1415);

执行结果：
3
-4

4、truncate截断，直接选取小数点后几位（无四舍五入）

select truncate(1.5678,2)

select truncate(3.14159,4)

执行结果：
1.56
3.1415

5、mod取余，x/y的余数，即x%y

#mod用法
select mod(8,4);
select mod(4,8);
select mod(7,3);
select mod(8,3);

#%用法
select 7%3;
select 8%3;

执行结果：
0
4
1
2
1
2

二、字符函数

1、length字符长度：一个字母1字节，一个汉字3字节

select length('Chung');

select length('钟h');

执行结果：
5
4

2、upper大写与lower小写

select upper('Chung');

select lower('ZHONG');

执行结果：
CHUNG
zhong

3、substr和substring截取字符串

#substr
#截取从指定索引处后面所有字符
select substr('我是将要成为海贼王的男人',7);
#截取从指定索引处指定字符长度的字符
select substr('我是将要成为海贼王的男人',7,3);

#substring
select substring('我是将要成为海贼王的男人',7);
select substring('我是将要成为海贼王的男人',7,3);

4、substring_inde按分割符截取字符串
1）格式：substring_index(参数str,参数delim,参数count)

• str :要处理的字符串
• delim:分隔符
• count:计数，取的个数

也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容；
相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容

2）用法示例
例如profile字段下面有这个按照逗号分割的字符串，我们想取年龄

profile
178cm,70kg,27,male

则可以用两次substring_index()将其取出来：

substring_index(substring_index(profile, ',', '3'), ',', -1) 

5、concat拼接字符串
1）用法示例

select concat('Hawk',' · ','Chung');

select concat('我要成为','海泽王','的男人');

2）案例练习：查询首都名称，其中首都需要是国家名称的扩展

select capital,name 
from world
where capital like concat('%',name,'%') and capital!=name

6、 instr 返回字串第首字符一次出现的索引，若未找到则0

#首字符为“海”
select instr('我是将要成为海贼王的男人','海贼王');

#首字符为“贼”
select instr('我是将要成为海贼王的男人','贼王');

执行结果：
7
8

7、trim去除字符：空格或指定字符(前后)

select length('       海贼王    ');

select Trim('       海贼王    ');

select Trim('b' from 'bbbb海贼王bbb');

select Trim('儿' from '我是将要成为海贼王的男人儿');

执行结果：

8、lpad左填充、rpad右填充
1） lpad 用指定的字符实现左填充指定字符数，原本的字符字符数超过指定字符数，会截掉超过的字符

2） rpad 用指定的字符实现右填充指定字符数，原本的字符数超过指定字符数，会截掉超过的字符

3）用法示例：

select length('Hawk Chung');
select lpad('Hawk Chung',15,'*');

select length('一破苍穹');
select rpad('一破苍穹',10,'&')

4）执行结果分析：

9、replace替换：（母串，原子串，替换子串）

#正常替换
select replace('我是将要成为海贼王的男人儿','男人儿','人');

#应用于去除空格：类似Trim
select replace('       海贼王    ',' ','');

执行结果：
我是将要成为海贼王的人
海贼王

10、left左边几个字符、right右边几个字符

select left('我要成为海贼王的男人',4);

select right('我要成为海贼王的男人',2);

执行结果：
我要成为
男人

三、日期函数

1、 now() 返回当前系统日期+时间（美国标准）：select now();

2、 curdate() 返回当前系统日期，不包含时间：select curdate();

3、 curtime() 返回当前系统时间，不包含日期：select curtime();

4、获取指定的部分，年、月、日、小时、分钟、秒

#返回 年月日 的关键字
SELECT YEAR('2022-05-20')  AS 年,MONTH('2022-05-20') AS 月,DAY('2022-05-20') AS 日;

#返回 时分秒 的关键字
select now(), hour(now()) as 时, minute(now()) as 分, second(now()) as 秒;

5、 str_to_date(字符串，日期格式) 将日期格式的字符转换成指定格式的日期

#规则的日期字符串
select str_to_date ('5-20-2022','%m-%d-%Y');

#不规则的日期字符串，需要将格式与字符顺序对应好，如第一个20表示日，因此这里是%d在前
select str_to_date ('20-5-2022','%d-%m-%Y');

6. data_format(日期, 日期格式) 将日期转换成指定格式的字符串

select Date_format('2022/5/20','%Y年%m月%d日');

select Date_format('2022-5-20','%m月%d日%Y年');

select Date_format('2022-5-20','%M %D  %Y');

执行结果：

7、date_add、date_sub在日期的基础上进行加减日期
1）date_add(date, INTERVAL 值 Type) 加日期，负数表示减

#加日期
select date_add('2022-5-17',INTERVAL 3 day);
select date_add('2022-1-20',INTERVAL 4 month);

#减日期
select date_add('2022-5-27',INTERVAL -7 day);
select date_add('2022-5-27',INTERVAL -1 week);

2）date_sub(date, INTERVAL 值 Type) 减日期，负数表示加

#减日期
select date_sub('2022-5-27',INTERVAL 7 day);
select date_sub('2022-5-27',INTERVAL 1 week);

#加日期
select date_sub('2022-5-17',INTERVAL -3 day);
select date_sub('2022-1-20',INTERVAL -4 month);

8、时间Type

9、datediff(date1,date2) 两个日期相差多少天

select datediff('2022-5-20','2021-5-20')

执行结果：date1-date2
365天