数据库基础MySQL（五 MYSQL常用函数）

        在之前的几篇中，对MySQL数据库的使用做出了讲解，但是在MySQL中，有很多很经常用到的函数。这次，就对这些函数进行一个大汇总，全程干货！！！！！上车啦！！！！！

1.字符函数

①length(str)函数                               :字符串长度
②concat(str1,str2,...)函数                     :字符串连接
③upper(str)、lower(str)函数                     :转大写
④substr(str,start,len)函数                      :从指定位置截取指定个数的字符串
⑤instr(str,要查找的子串)函数                      :判断是否存在 返回 索引值 或者 0
⑥trim(str)函数                                  : 去除两端空格
⑦lpad(str,len,填充字符)、rpad(str,len,填充字符)函数:左右两边指定个数填充.
⑧replace(str,子串,另一个字符串)函数                : 字符串替换.

1. LENGTH(str) 函数

• 功能：返回字符串str的长度（以字节为单位）。对于多字节字符集（如UTF-8），一个字符可能占用多个字节。
• 示例：SELECT LENGTH('hello world'); 返回 11（假设使用单字节字符集）或更多（如果使用UTF-8等多字节字符集）。

2. CONCAT(str1, str2, ...) 函数

• 功能：将两个或多个字符串值连接成一个字符串。
• 示例：SELECT CONCAT('Hello, ', 'world!'); 返回 Hello, world!。

3. UPPER(str) 和 LOWER(str) 函数

• 功能：UPPER(str) 将字符串str中的所有小写字母转换为大写字母；LOWER(str) 将字符串str中的所有大写字母转换为小写字母。
• 示例：SELECT UPPER('Hello World'); 返回 HELLO WORLD；SELECT LOWER('Hello World'); 返回 hello world。

4. SUBSTRING(str, pos, len) 函数（在某些上下文中也被简写为SUBSTR(str, pos, len)）

• 功能：从字符串str的pos位置开始，截取长度为len的子串。注意，pos的位置是从1开始的。
• 示例：SELECT SUBSTRING('Hello World', 7, 5); 返回 World。

5. INSTR(str, substr) 函数

• 功能：返回子串substr在字符串str中第一次出现的位置（索引值）。如果未找到子串，则返回0。索引值从1开始。
• 示例：SELECT INSTR('Hello World', 'World'); 返回 7。

6. TRIM([BOTH | LEADING | TRAILING] [remstr] FROM] str) 函数

• 功能：默认情况下，TRIM(str) 去除字符串str两端的空格。通过指定BOTH、LEADING或TRAILING以及可选的remstr，可以控制要去除的字符类型。
• 示例：SELECT TRIM(' Hello World '); 返回 'Hello World'（去除了两端的空格）；SELECT TRIM(BOTH 'x' FROM 'xxHello Worldxx'); 返回 'Hello World'（去除了两端的'x'）。

7. LPAD(str, len, padstr) 和 RPAD(str, len, padstr) 函数

• 功能：LPAD(str, len, padstr) 在字符串str的左边填充字符padstr，直到字符串的长度达到len；RPAD(str, len, padstr) 则在右边进行填充。
• 示例：SELECT LPAD('abc', 5, 'x'); 返回 'xxabc'；SELECT RPAD('abc', 5, 'x'); 返回 'abcxx'。

8. REPLACE(str, from_str, to_str) 函数

• 功能：在字符串str中查找所有出现的from_str，并将其替换为to_str。
• 示例：SELECT REPLACE('Hello World', 'World', 'MySQL'); 返回 'Hello MySQL'。

         下面来使用代码进行实现。

select length('hello') ;# 查询传入字符串的的长度.

# 连接商品名称和商拼价格中间,号分隔.
select concat('hello','123','world');#hello123world
select concat(pname,',',price)from product;# "pname，price"

# 把pid内容变大写.pname变小写
select upper(pid),lower(pname) from product;

# 把表bname截取前6个字符
# 开始位置必须从1开始
select substr(bname,1,6) from tb_book;# 包1包6 [1,6]

#判断商品名称是否包含 '水'
select instr(pname,'水') from product; # 存在返回1 ,不存在返回 0

#去掉空格.一般做数据清洗
select trim(pname) from product;

select lpad('aaa',4,'#');#: #aaa 左右两边指定字符填充.然后获取指定长度.
select rpad('aaa',4,'#');#: aaa#

select replace('hello','ll','xx');#hexxo

#把果改成水果
select replace(pname,'果','水果') from product ；

 2.数学函数

①round(x,保留位数)函数: 四舍五入保留小数位.
②ceil(x)函数        :天花板函数
③floor(x)函数       :地板函数
④truncate(x,D)函数  : 按照D的值截取小数部分.
⑤mod(被除数,除数)函数 : 求余数
⑥pow(x,D)函数       : x的D次方

1. 四舍五入保留小数位

        在MySQL中，四舍五入保留小数位的函数是ROUND(X, D)，其中X是要四舍五入的数值，D是小数点后保留的位数。如果D省略，则默认保留到整数。

SELECT ROUND(123.4567, 2); -- 结果: 123.46

2. 天花板函数

MySQL中没有直接名为ceil的函数，但CEIL()或CEILING()函数可以实现天花板函数的功能，即将数值向上舍入到最接近的整数。

SELECT CEIL(123.01); -- 结果: 124 
SELECT CEILING(123.99); -- 结果: 124

3. 地板函数

在MySQL中，FLOOR()函数用于实现地板函数的功能，即将数值向下舍入到最接近的整数。

SELECT FLOOR(123.99); -- 结果: 123 
SELECT FLOOR(-123.01); -- 结果: -124

4. 截取小数部分

MySQL中没有直接名为truncate但专门用于截取小数部分的函数，但你可以使用TRUNCATE(X, D)函数来达到类似的效果，其中X是要处理的数值，D是小数点后保留的位数（如果D为0，则结果将没有小数部分）。

SELECT TRUNCATE(123.4567, 2); -- 结果: 123.45 
SELECT TRUNCATE(123.4567, 0); -- 结果: 123

5. 求余数

在MySQL中，MOD(N, M)函数用于求N除以M的余数。

SELECT MOD(10, 3); -- 结果: 1

6. x的D次方

在MySQL中，POW(X, Y)或POWER(X, Y)函数用于计算X的Y次方。注意，这里我使用了Y而不是D，但功能相同。

SELECT POW(2, 3); -- 结果: 8 
SELECT POWER(2, 3); -- 结果: 8

 3.时间与日期

日期格式
①now()函数                          :当前日期和时间
②curdate()函数                      :当前日期
③curtime()函数                      :当前时间
④获取日期和时间中的年、月、日、时、分、秒
⑤weekofyear()函数                   :一年的第几周
⑥ quarter()函数                     :一年中的季度
⑦ str_to_date()函数                 :字符串转时间类型
⑧date_format()函数                  :格式化时间字符串
⑨date_add(日期,interval num 时间)函数 :添加日期
⑩last_day()函数                      :月度最后一天
⑪datediff(end_date,start_date)函数   : 时间差
⑫timestampdiff(unit,start_date,end_date)函数计算两个时间返回的年/月/天数；
unix_timestamp(date) : 把日期转毫秒值
from_unixtime(int): 把毫秒值转 日期

1. NOW() 函数

返回当前的日期和时间，格式为 'YYYY-MM-DD HH:MM:SS' 或 'YYYYMMDDHHMMSS.uuuuuu'（取决于是否启用了微秒支持）。

SELECT NOW();

2. CURDATE() 函数

返回当前的日期，格式为 'YYYY-MM-DD' 或 'YYYYMMDD'（取决于NO_ZERO_DATE和NO_ZERO_IN_DATE SQL模式）。

SELECT CURDATE();

3. CURTIME() 函数

返回当前的时间，格式为 'HH:MM:SS' 或 'HHMMSS.uuuuuu'（取决于是否启用了微秒支持）。

SELECT CURTIME();

4. 获取日期和时间中的年、月、日、时、分、秒

MySQL提供了YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()等函数来分别提取日期和时间的各个部分。

SELECT YEAR('2023-04-01 12:34:56'); -- 结果: 2023 
SELECT MONTH('2023-04-01 12:34:56'); -- 结果: 4 
SELECT DAY('2023-04-01 12:34:56'); -- 结果: 1 
SELECT HOUR('2023-04-01 12:34:56'); -- 结果: 12 
SELECT MINUTE('2023-04-01 12:34:56'); -- 结果: 34 
SELECT SECOND('2023-04-01 12:34:56'); -- 结果: 56

5. WEEKOFYEAR() 函数

返回给定日期是一年中的第几周（1-53），基于ISO 8601周日期系统。

SELECT WEEKOFYEAR('2023-01-01'); -- 结果可能因年份而异

6. QUARTER() 函数

返回给定日期是一年中的哪个季度（1-4）。

SELECT QUARTER('2023-04-01'); -- 结果: 2

7. STR_TO_DATE() 函数

将字符串转换为日期。需要指定格式字符串。

SELECT STR_TO_DATE('01,5,2023','%d,%m,%Y'); -- 结果: '2023-05-01'

8. DATE_FORMAT() 函数

根据指定的格式字符串格式化日期或时间值。

SELECT DATE_FORMAT('2023-04-01 12:34:56', '%Y-%m-%d %H:%i:%s'); -- 结果: '2023-04-01 12:34:56'

9. DATE_ADD() 函数

向日期添加指定的时间间隔。

SELECT DATE_ADD('2023-04-01', INTERVAL 1 MONTH); -- 结果: '2023-05-01'

10. LAST_DAY() 函数

返回给定日期所在月份的最后一天。

SELECT LAST_DAY('2023-04-15'); -- 结果: '2023-04-30'

11. DATEDIFF() 函数

返回两个日期之间的天数差。

SELECT DATEDIFF('2023-04-30', '2023-04-01'); -- 结果: 29

12. TIMESTAMPDIFF() 函数

返回两个时间戳之间的差异，以指定的单位表示（如年、月、日等）。

SELECT TIMESTAMPDIFF(MONTH, '2023-01-01', '2023-04-01'); -- 结果: 3

UNIX_TIMESTAMP() 和 FROM_UNIXTIME() 函数

• UNIX_TIMESTAMP(date)：将日期或时间值转换为UNIX时间戳

 这些函数要好好了解，以后大数据生产中会非常需要这些函数的使用。