【18.0】MySQL进阶知识之函数

【一】什么是函数

• 跟存储过程是有区别的，存储过程是自定义函数，函数就类似于内置函数
• 注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！

【二】字符串函数

【0】介绍

• CONCAT(str1, str2, ...): 将多个字符串连接成一个字符串。
• SUBSTRING(str, start, length): 返回字符串的子串。
• UPPER(str): 将字符串转换为大写。
• LOWER(str): 将字符串转换为小写。
• LENGTH(str): 返回字符串的长度。
• Trim、LTrim、RTrim: 移除指定字符
• Left、Right: 获取左右起始指定个数字符
• Soundex: 返回读音相似值(对英文效果)

【1】数据准备

• 创建表

CREATE TABLE Customers (
    Name VARCHAR(100),
    Username VARCHAR(100)
);

• 插入数据

INSERT INTO Customers (Name, Username) VALUES 
    ('John Doe', 'JDoe'),
    ('Jane Smith', 'JSmith'),
    ('Jim Brown', 'JBrown'),
    ('Joseph Lee', 'JLee');

【2】CONCAT

• CONCAT()函数可以将两个或更多的字符串连接在一起。

SELECT CONCAT('Hello', ' ', 'World') AS Greeting;
-- 输出：Hello World

【3】SUBSTRING

• SUBSTRING()函数可以从字符串中提取部分字符。

SELECT SUBSTRING('Hello World', 1, 5) AS Substring;
-- 输出：Hello

【4】UPPER/LOWER

• UPPER()函数将字符串转换为大写
• LOWER()函数将字符串转换为小写。

SELECT UPPER('hello world') AS Uppercase;
-- 输出：HELLO WORLD

SELECT LOWER('HELLO WORLD') AS Lowercase;
-- 输出：hello world

【5】LENGTH

• LENGTH()函数返回字符串的长度。

SELECT LENGTH('Hello World') AS Length;
-- 输出：11

【6】Trim/LTrim/RTrim

• TRIM()函数可以删除字符串开头和结尾处的所有指定字符。
• LTRIM()和RTRIM()分别只删除开头和结尾的指定字符。

SELECT TRIM('   hello world   ') AS TrimmedString;
-- 输出：hello world

SELECT LTRIM('   hello world   ') AS LeftTrimmedString;
-- 输出：hello world

SELECT RTRIM('   hello world   ') AS RightTrimmedString;
-- 输出：   hello world

【7】Left/Right

• LEFT()函数返回从左侧开始指定数量的字符
• RIGHT()函数返回从右侧开始指定数量的字符。

SELECT LEFT('Hello World', 5) AS Left;
-- 输出：Hello

SELECT RIGHT('Hello World', 5) AS Right;
-- 输出：orld

【8】Soundex

• SOUNDEX()函数返回读音相似值(对英文效果)，可以用来查找同音异形词。

SELECT SOUNDEX('John Doe') AS Soundex;
-- 输出：J460

• eg:客户表中有一个顾客登记的用户名为J.Lee
• 但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的

SELECT * FROM Customers WHERE SOUNDEX(Username) = SOUNDEX('J.Lie');

【二】数值函数

【1】介绍

• ROUND(num, decimals): 对数值进行四舍五入。
• FLOOR(num): 返回不大于给定数值的最大整数。
• CEILING(num): 返回不小于给定数值的最小整数。
• ABS(num): 返回给定数值的绝对值。

【2】示例

SELECT ROUND(3.14159, 2) AS RoundedNum; -- 3.14
SELECT FLOOR(3.99999) AS FloorNum; -- 3
SELECT CEILING(3.00001) AS CeilingNum; -- 4
SELECT ABS(-123.45) AS AbsNum; -- 123.45

【三】日期和时间函数

【1】介绍

• NOW(): 返回当前日期和时间。
• CURDATE(): 返回当前日期。
• CURTIME(): 返回当前时间。
• DATE_FORMAT(date, format): 格式化日期。

【2】示例

SELECT NOW() AS CurrentDateTime; -- 当前日期和时间
SELECT CURDATE() AS CurrentDate; -- 当前日期
SELECT CURTIME() AS CurrentTime; -- 当前时间
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS FormattedDateTime; -- 格式化后的日期和时间

【3】实例

• 创建表

CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);

• 插入数据

INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');

• 查看数据

select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

select 8 from blog where Date(sub_time) = '2015-03-01'

select 8 from blog where Year(sub_time)=2016 AND Month(sub_time)=07;

【四】聚合函数

【1】介绍

• COUNT(column): 计算符合条件的行数。
• SUM(column): 对指定列的值求和。
• AVG(column): 计算指定列的平均值。
• MIN(column): 找到指定列的最小值。
• MAX(column): 找到指定列的最大值。

【2】示例

SELECT COUNT(*) AS RowCount FROM Employees;
SELECT SUM(Salary) AS SalarySum FROM Employees;
SELECT AVG(Salary) AS AverageSalary FROM Employees;
SELECT MIN(Salary) AS MinSalary FROM Employees;
SELECT MAX(Salary) AS MaxSalary FROM Employees;

【五】条件函数

【1】介绍

• IF(condition, value_if_true, value_if_false): 如果条件成立，则返回value_if_true，否则返回value_if_false。
• CASE WHEN condition THEN value END: 根据满足的条件返回相应的值。

【2】示例

SELECT IF(Age > 18, 'Adult', 'Child') AS AgeGroup FROM Employees;
SELECT CASE WHEN Salary > 5000 THEN 'High' ELSE 'Low' END AS SalaryGroup FROM Employees;