MySQL：常用系统函数及其应用（字符串处理、日期和时间、MySQL版本、条件判断、加密函数MD5,SHA）

---

1. 字符串处理函数

1.1 计算字符串字符数和长度

• char_length(str)：返回字符串str所包含的字符个数。
• length(str)：返回字符串的字节长度。一个汉字通常是3个字节，一个数字或字母是1个字节。

-- 计算字符串'CHINA'的字符数和字节长度
SELECT char_length('CHINA') AS 字符数, length('CHINA') AS 字节长度;

-- 计算字符串'中国'的字符数和字节长度
SELECT char_length('中国') AS 字符数, length('中国') AS 字节长度;

1.2 合并字符串函数

• CONCAT(s1, s2, ……)：返回s1, s2, ……连接成的字符串，如果任何一个参数为NULL，则返回值为NULL。
• CONCAT_WS(sep, s1, s2, ……)：返回s1, s2, ……连接成的字符串，并用sep字符间隔。

-- 合并字符串，显示MySQL版本信息
SELECT CONCAT('MySQL版本:', @@version) AS 版本信息1,
       CONCAT_WS('-', 'MySQL', '8.0.27') AS 版本信息2;

1.3 字符串大小写转换函数

• LOWER(str) 或 LCASE(str)：将字符串str中的字母字符全部转换成小写字母。
• UPPER(str) 或 UCASE(str)：将字符串str中的字母字符全部转换成大写字母。

-- 不区分大小写查询用户名为'redhOOk'的用户信息
SELECT * FROM customers
WHERE LOWER(c_name) = LCASE('redhOOk');

1.4 删除空格函数

• LTRIM(str)：返回删除前端空格的字符串str。
• RTRIM(str)：返回删除尾部空格的字符串str。
• TRIM(str)：返回删除前端和尾部空格的字符串str。

-- 查询用户名为'redhOOK'的用户信息，忽略前后空格
SELECT * FROM customers
WHERE UPPER(c_name) = TRIM(UPPER('redhOOK'));

1.5 取子串函数

• LEFT(str, length)：返回字符串str最左侧长度为length的子串。
• RIGHT(str, length)：返回字符串str最右侧长度为length的子串。
• SUBSTRING(str, start, length)：返回字符串str从start开始长度为length的子串。

-- 查询fruits表中f_name以'b'开头的水果信息
SELECT * FROM fruits
WHERE SUBSTRING(f_name, 1, 1) = 'b';

2. 日期和时间函数

2.1 获取当前系统的日期及取日期的年、月、日函数

• CURDATE() 或 CURRENT_DATE()：返回当前系统日期，格式为’YYYY-MM-DD’。
• YEAR(d)、MONTH(d)、DAY(d)：分别返回日期或日期时间d的年、月、日的值。

-- 查询2021年下订单的客户编号、姓名及所在的城市
SELECT DISTINCT c.c_id, c_name, c_city
FROM customers c, orders o
WHERE c.c_id = o.c_id AND YEAR(o_date) = 2021;

2.2 获取当前系统日期时间函数

• NOW()
• SYSDATE()
• CURRENT_TIMESTAMP()
• LOCALTIME()
  格式为’YYYY-MM-DD HH:MM:SS’。

-- 根据出生日期计算年龄
SELECT SYSDATE(), YEAR(SYSDATE()) - YEAR('2002/6/13') AS 年龄;

3. 系统信息函数

3.1 获取MySQL版本号、用户名和数据库名函数

• USER()：返回当前登录的用户名。
• DATABASE()：返回当前使用的数据库名。
• VERSION()：返回MySQL服务器版本号。

-- 显示当前MySQL版本号、登录的用户名和使用的数据库
SELECT VERSION() AS 版本号, USER() AS 登录名, DATABASE() AS 数据库名;

4. 条件判断函数

4.1 IF()函数

IF(条件表达式, v1, v2)：如果条件表达式是真则函数返回v1值，否则返回v2的值。

-- 显示customers表的c_id、c_name和c_email，当c_email字段值为NULL时，显示值为'none'
SELECT c_id, c_name, IF(c_email IS NULL, 'none', c_email) AS c_email
FROM customers;

4.2 IFNULL()函数

IFNULL(v1, v2)：返回参数v1或v2的值。假如v1不为NULL，则返回值为v1，否则返回值为v2。

-- 显示customers表的c_id、c_name和c_email，当c_email字段值为NULL时，显示值为'none'
SELECT c_id, c_name, IFNULL(c_email, 'none') AS c_email
FROM customers;

5. 加密函数

5.1 MD5和SHA函数

• MD5(str)：计算字符串str的MD5 128比特校验和。
• SHA(str)：计算字符串str的SHA检验和。SHA加密算法比MD5更加安全。
• SHA2(str, hash_length)：使用hash_length作为长度，加密str。Hash_length支持224、256、384、512和0，其中0等同于256。

-- 创建用户表，并为密码字段值进行加密
CREATE TABLE user (
    u_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    u_name VARCHAR(20),
    u_pass VARCHAR(255)
);

-- 插入加密后的密码数据
INSERT INTO user(u_name, u_pass)
VALUES ('mary', SHA2('123456', 0)),
       ('jack', MD5('jack12345'));

-- 查询用户表
SELECT * FROM user;