文章目录
- 一、常用的系统函数
- 1.1 数学函数
- 1.2 字符串函数
- 1.2.1 计算字符串字符数的函数和计算字符串长度的函数
- 1.2.2 字符串合并函数
- 1.2.3 字符串大小写转换函数
- 1.2.4 删除空格函数
- 1.2.5 取子串函数
- 1.2.6 其他字符串函数
- 1.3 日期和时间函数
- 1.3.1 获取当前系统日期及指定日期年、月、日的函数
- 1.3.2 获取当前系统日期时间的函数
- 1.3.3 其他日期和时间函数
- 1.4 系统信息函数
- 1.4.1 获取MySQL服务器版本号、用户名和数据库名的函数
- 1.4.2 其他系统信息函数
- 1.5 条件判断函数
- 1.5.1 IF函数
- 1.5.2 IFNULL()函数
- 1.5.3 CASE函数
- 1.6 加密函数
一、常用的系统函数
1.1 数学函数
当然可以，下面是MySQL中一些常用的数学函数，按照不同的类别进行分类，并提供了简单的案例。每个函数的介绍和用法都在表格中，而示例则单独列出并换行。
| 函数名称 | 描述 | 用法 |
|---|---|---|
| ABS(x) | 返回x的绝对值 | SELECT ABS(x); |
| CEILING(x) | 返回大于或等于x的最小整数 | SELECT CEILING(x); |
| FLOOR(x) | 返回小于或等于x的最大整数 | SELECT FLOOR(x); |
| MOD(x, y) | 返回x除以y的余数 | SELECT MOD(x, y); |
| ROUND(x, y) | 返回x四舍五入到小数点后y位的结果 | SELECT ROUND(x, y); |
| RAND() | 返回一个0到1之间的随机浮点数 | SELECT RAND(); |
| POW(x, y) | 返回x的y次幂 | SELECT POW(x, y); |
示例：
SELECT ABS(-10); -- 结果为10
SELECT CEILING(3.14); -- 结果为4
SELECT FLOOR(3.14); -- 结果为3
SELECT MOD(10, 3); -- 结果为1
SELECT ROUND(3.145, 2); -- 结果为3.15
SELECT RAND(); -- 结果可能是0.12345
SELECT POW(2, 3); -- 结果为8
1.2 字符串函数
1.2.1 计算字符串字符数的函数和计算字符串长度的函数
CHAR_LENGTH(str)或CHARACTER_LENGTH(str): 返回字符串str的字符数。LENGTH(str): 返回字符串str的长度，以字节为单位。
示例：
SELECT CHAR_LENGTH('Hello World'); -- 返回11
SELECT LENGTH('Hello World'); -- 返回11（如果字符编码为单字节）
1.2.2 字符串合并函数
CONCAT(str1, str2, ...): 将多个字符串参数首尾相连。
示例：
SELECT CONCAT('Hello', ' ', 'World'); -- 返回Hello World
1.2.3 字符串大小写转换函数
LOWER(str)或LCASE(str): 将字符串str转换为小写。UPPER(str)或UCASE(str): 将字符串str转换为大写。
示例：
SELECT LOWER('Hello World'); -- 返回hello world
SELECT UPPER('Hello World'); -- 返回HELLO WORLD
1.2.4 删除空格函数
TRIM(str): 去除字符串str两端的空格。LTRIM(str): 去除字符串str左端的空格。RTRIM(str): 去除字符串str右端的空格。
示例：
SELECT TRIM(' Hello World '); -- 返回Hello World
SELECT LTRIM(' Hello World '); -- 返回Hello World
SELECT RTRIM(' Hello World '); -- 返回 Hello World
1.2.5 取子串函数
SUBSTRING(str, pos)或SUBSTR(str, pos): 从字符串str中提取从位置pos开始到字符串末尾的子串。SUBSTRING(str, pos, len): 从字符串str中提取从位置pos开始长度为len的子串。
示例：
SELECT SUBSTRING('Hello World', 1); -- 返回Hello World（从第一个字符开始）
SELECT SUBSTRING('Hello World', 1, 5); -- 返回Hello（从第一个字符开始提取5个字符）
1.2.6 其他字符串函数
REPLACE(str, search_str, replace_str): 在字符串str中替换search_str为replace_str。POSITION(search_str IN str)或INSTR(str, search_str): 返回search_str在str中的位置。LTRIM(str, char_set): 去除字符串str左端的指定字符集char_set中的字符。RTRIM(str, char_set): 去除字符串str右端的指定字符集char_set中的字符。REVERSE(str): 返回字符串str的反转字符串。
示例：
SELECT REPLACE('Hello World', 'World', 'MySQL'); -- 返回Hello MySQL
SELECT POSITION('World' IN 'Hello World'); -- 返回7（World在Hello World中的位置）
SELECT LTRIM('xxxHello World', 'x'); -- 返回Hello World
SELECT RTRIM('Hello Worldxxx', 'x'); -- 返回Hello World
SELECT REVERSE('Hello World'); -- 返回dlroW olleH
1.3 日期和时间函数
1.3.1 获取当前系统日期及指定日期年、月、日的函数
CURDATE()或CURRENT_DATE(): 返回当前的日期，格式为YYYY-MM-DD。YEAR(date): 返回指定日期的年份。MONTH(date): 返回指定日期的月份（1到12）。DAY(date)或DAYOFMONTH(date): 返回指定日期的天数（1到31）。
示例：
SELECT CURDATE(); -- 返回当前日期，例如2024-10-28
SELECT YEAR('2024-10-28'); -- 返回2024
SELECT MONTH('2024-10-28'); -- 返回10
SELECT DAY('2024-10-28'); -- 返回28
1.3.2 获取当前系统日期时间的函数
NOW(): 返回当前的日期和时间，格式为YYYY-MM-DD HH:MM:SS。CURRENT_TIME(): 返回当前的时间，格式为HH:MM:SS。UTC_TIMESTAMP(): 返回当前的UTC日期和时间。
示例：
SELECT NOW(); -- 返回当前日期和时间，例如2024-10-28 14:30:00
SELECT CURRENT_TIME(); -- 返回当前时间，例如14:30:00
SELECT UTC_TIMESTAMP(); -- 返回当前UTC时间，例如2024-10-28 06:30:00
1.3.3 其他日期和时间函数
DATEDIFF(date1, date2): 返回两个日期之间的天数差。DATE_ADD(date, INTERVAL expr type): 在指定日期上加上一个时间间隔。DATE_SUB(date, INTERVAL expr type): 从指定日期减去一个时间间隔。LAST_DAY(date): 返回指定日期所在月份的最后一天。WEEKDAY(date): 返回指定日期是星期几（0表示星期一，6表示星期日）。
示例：
SELECT DATEDIFF('2024-10-28', '2024-10-01'); -- 返回27（两个日期之间的天数差）
SELECT DATE_ADD('2024-10-28', INTERVAL 10 DAY); -- 返回2024-11-07（在当前日期上加10天）
SELECT DATE_SUB('2024-10-28', INTERVAL 10 DAY); -- 返回2024-10-18（从当前日期减去10天）
SELECT LAST_DAY('2024-10-15'); -- 返回2024-10-31（2024年10月的最后一天）
SELECT WEEKDAY('2024-10-28'); -- 返回0（表示2024年10月28日是星期一）
1.4 系统信息函数
1.4.1 获取MySQL服务器版本号、用户名和数据库名的函数
-
VERSION()或VERSION(): 返回MySQL服务器的版本号。SELECT VERSION(); -- 返回版本号，如8.0.32 -
CONNECTION_ID(): 返回当前连接的唯一ID。SELECT CONNECTION_ID(); -- 返回连接ID，如487032 -
DATABASE()或SCHEMA(): 返回当前选择的数据库名。SELECT DATABASE(); -- 返回当前数据库名 -
USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER(): 返回当前用户名称。SELECT USER(); -- 返回当前用户名称，如'username'@'host'
1.4.2 其他系统信息函数
-
CHARSET(str)或CHARACTER_SET(str): 返回字符串str的字符集。SELECT CHARSET('text'); -- 返回字符串的字符集 -
COLLATION(str): 返回字符串str的排序规则。SELECT COLLATION('text'); -- 返回字符串的排序规则 -
FOUND_ROWS(): 返回上一条SELECT语句符合条件的行数（受LIMIT语句影响）。SELECT FOUND_ROWS(); -- 返回上一条查询符合条件的行数 -
LAST_INSERT_ID(): 返回最后生成的AUTO_INCREMENT值。SELECT LAST_INSERT_ID(); -- 返回最后插入行的自增ID
1.5 条件判断函数
1.5.1 IF函数
IF(expr, val1, val2): 如果expr为真（非0，非NULL，非FALSE），则返回val1，否则返回val2。
示例：
SELECT IF(10 > 5, '真', '假'); -- 返回真
1.5.2 IFNULL()函数
IFNULL(val1, val2): 如果val1不为NULL，则返回val1，否则返回val2。
示例：
SELECT IFNULL(NULL, '默认值'); -- 返回默认值
SELECT IFNULL('非NULL值', '默认值'); -- 返回非NULL值
1.5.3 CASE函数
CASE expr WHEN condition THEN result [WHEN ... THEN ...] [ELSE result] END: 根据expr的值和condition的条件，返回相应的result。如果没有匹配的条件，且存在ELSE子句，则返回ELSE子句的结果。
示例：
SELECT CASEWHEN age < 18 THEN '儿童'WHEN age BETWEEN 18 AND 64 THEN '成人'ELSE '老年'
END
FROM users; -- 根据年龄返回儿童、成人或老年
1.6 加密函数
要创建一个用户表并对其密码字段进行加密，你可以按照以下步骤操作：
- 创建用户表（user）：
CREATE TABLE user (id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL,u_pass VARCHAR(255) NOT NULL
);
- 插入数据时，对密码进行加密。这里我们可以使用
SHA2()函数来进行加密，因为它提供了较强的安全性。以下是插入数据的示例：
-- 插入新用户
INSERT INTO user (username, u_pass)
VALUES ('newuser', SHA2('plainTextPassword', 256));
在这个例子中，'plainTextPassword'是你想要存储的明文密码，SHA2('plainTextPassword', 256)会生成一个SHA-256散列值，然后将这个散列值存储在u_pass字段中。
如果你想要使用MySQL的PASSWORD()函数来生成一个随机的、加盐的密码散列值，你需要确保你的MySQL版本支持这个函数（MySQL 5.7.6及以后版本）。以下是使用PASSWORD()函数的示例：
-- 插入新用户
INSERT INTO user (username, u_pass)
VALUES ('newuser', PASSWORD('plainTextPassword'));
请注意，PASSWORD()函数在不同版本的MySQL中可能有不同的行为，因此在实际应用中，你可能需要根据你的MySQL版本选择合适的加密方法。
在实际应用中，你还可能需要在用户登录时验证密码。这通常涉及到对用户输入的密码进行相同的散列处理，然后与数据库中存储的散列值进行比较。例如：
-- 验证用户登录
SELECT * FROM user
WHERE username = 'newuser' AND u_pass = SHA2('inputPassword', 256);
在这个例子中，'inputPassword'是用户输入的密码，SHA2('inputPassword', 256)会生成一个SHA-256散列值，然后与数据库中存储的散列值进行比较。