深入MySQL的宝库:内置函数实战手册

字符串函数：

• CONCAT(): 连接两个或多个字符串。

SELECT CONCAT('Hello', ' ', 'World');

• LENGTH() 或 CHAR_LENGTH(): 返回字符串的长度。

SELECT LENGTH('Hello');
SELECT CHAR_LENGTH('Hello');

• LOWER() 和 UPPER(): 将字符串转换为小写或大写。

SELECT LOWER('Hello World');
SELECT UPPER('Hello World');

• TRIM(): 去除字符串前后的空格。

SELECT TRIM(' Hello World ');

• REPLACE(): 替换字符串中的指定字符或子串。

SELECT REPLACE('Hello World',
               'World', 'Universe');

• SUBSTRING() 或 SUBSTR(): 提取字符串的子串。

-- 返回 "Hello"
SELECT SUBSTRING('Hello World', 1, 5); 
-- 返回 "Hello" (在某些数据库中,SUBSTR 是 SUBSTRING 的同义词)
SELECT SUBSTR('Hello World', 1, 5); 

• LOCATE() 或 INSTR(): 查找子串在字符串中的位置。

-- 返回 7
SELECT LOCATE('World', 'Hello World'); 
-- 返回 7 (INSTR 是 LOCATE 的同义词)
SELECT INSTR('Hello World', 'World'); 

• LEFT() 和 RIGHT(): 分别返回字符串的左侧或右侧指定数量的字符。

-- 返回 "Hello"
SELECT LEFT('Hello World', 5); 
-- 返回 "World"
SELECT RIGHT('Hello World', 5); 

• LPAD() 和 RPAD(): 在字符串的左侧或右侧填充指定的字符。

-- 返回 "*****Hello"
SELECT LPAD('Hello', 10, '*'); 
-- 返回 "Hello*****"
SELECT RPAD('Hello', 10, '*'); 

• CONVERT() 或 CAST(): 将一个数据类型的值转换为另一个数据类型。可以用于字符串和数字之间的转换。

-- 将字符串转换为无符号整数 (123)
SELECT CONVERT('123', UNSIGNED);
-- 将整数转换为字符串 ("123")
SELECT CAST(123 AS CHAR); 

数值函数：

• ROUND(): 将数值四舍五入到指定的小数位数。

SELECT ROUND(123.4567, 2); -- 返回 123.46

• FLOOR() 和 CEIL() 或 CEILING(): 将数值向下或向上取整。

SELECT FLOOR(123.4567); -- 返回 123
SELECT CEIL(123.4567); -- 返回 124

• ABS(): 返回数值的绝对值。

-- 返回 123
SELECT ABS(-123); 

• TRUNCATE(): 将数值截断到指定的小数位数。

-- 返回 123.45
SELECT TRUNCATE(123.4567, 2); 

• RAND(): 返回一个随机浮点数。可以指定一个可选参数来设置随机数生成器的种子。

-- 返回一个随机浮点数
SELECT RAND(); 
-- 使用种子值 10 返回一个随机浮点数
SELECT RAND(10); 

• POW() 或 POWER(): 返回数值的指定次方。

 -- 返回 8 (因为 2 的 3 次方等于 8)
SELECT POW(2, 3);
 -- 返回 8 (因为 2 的 3 次方等于 8) (POW 和 POWER 是同义词)
SELECT POWER(2, 3);

• SQRT(): 返回数值的平方根。

-- 返回 4 (因为 4 的平方等于 16)
SELECT SQRT(16); 

• MOD(): 返回两个数值相除的余数。

-- 返回 1 (因为 7 除以 3 的余数是 1)
SELECT MOD(7, 3); 

日期和时间函数：

• NOW(): 返回当前的日期和时间。

SELECT NOW();

• CURDATE() 或 CURRENT_DATE: 返回当前的日期。

SELECT CURDATE();
SELECT CURRENT_DATE;

• CURTIME() 或 CURRENT_TIME: 返回当前的时间。

SELECT CURTIME();
SELECT CURRENT_TIME;

• DATE(): 提取日期部分。

 -- 返回 '2023-07-19'
SELECT DATE('2023-07-19 12:34:56');

• TIME(): 提取时间部分。

 -- 返回 '12:34:56'
SELECT TIME('2023-07-19 12:34:56');

• YEAR() 和 MONTH() 和 DAY(): 提取日期或时间的年、月、日部分。

-- 返回 2023
SELECT YEAR('2023-07-19'); 
-- 返回 7
SELECT MONTH('2023-07-19'); 
-- 返回 19
SELECT DAY('2023-07-19'); 

• DATEDIFF(): 返回两个日期之间的天数差。

-- 返回 18
SELECT DATEDIFF('2023-07-19', '2023-07-01'); 

• ADDDATE() 或 DATE_ADD(): 在日期上添加指定的时间间隔。

-- 返回 '2023-07-24'
SELECT ADDDATE('2023-07-19', INTERVAL 5 DAY); 
-- 返回 '2023-07-24' (ADDDATE 和 DATE_ADD 是同义词)
SELECT DATE_ADD('2023-07-19', INTERVAL 5 DAY); 

• SUBDATE() 或 DATE_SUB(): 从日期减去指定的时间间隔。

-- 返回 '2023-07-14'
SELECT SUBDATE('2023-07-19', INTERVAL 5 DAY); 
-- 返回 '2023-07-14' (SUBDATE 和 DATE_SUB 是同义词)
SELECT DATE_SUB('2023-07-19', INTERVAL 5 DAY); 

• WEEK(): 函数返回一个日期是一年中的第几周。它接受两个参数：要返回周数的日期和一个可选的周起始日。默认情况下，周的起始日是星期天（0 表示星期天，1 表示星期一，以此类推）

WEEK(date, mode)
-- 返回 27，假设周的起始日是星期天  
SELECT WEEK('2023-07-19'); 
-- 返回 28，假设周的起始日是星期一
SELECT WEEK('2023-07-19', 1); 

• WEEKOFYEAR(): 这个函数与 WEEK() 函数的行为相同，只是函数名不同。它也返回一个日期是一年中的第几周。它不接受任何可选参数，默认周的起始日是星期天。

WEEKOFYEAR(date)
-- 返回 27，假设周的起始日是星期天
SELECT WEEKOFYEAR('2023-07-19'); 

聚合函数：

COUNT()

• 描述：计算行数。
• 语法：COUNT(column) 或 COUNT(*)
• 示例：SELECT COUNT(id) FROM users;

SUM()

• 描述：计算列的总和。
• 语法：SUM(column)
• 示例：SELECT SUM(price) FROM products;

AVG()

• 描述：计算列的平均值。
• 语法：AVG(column)
• 示例：SELECT AVG(score) FROM exams;

MIN() 和 MAX()

• 描述：分别返回列的最小值和最大值。
• 语法：MIN(column) 和 MAX(column)
• 示例：SELECT MIN(age) FROM users; 和 SELECT MAX(price) FROM products;

GROUP_CONCAT()

• 描述：将多个行的值连接成一个字符串。
• 语法：GROUP_CONCAT(column [DISTINCT] [ORDER BY order_expression ASC/DESC])
• 示例：SELECT GROUP_CONCAT(name) FROM users;

STD() 和 VARIANCE()

• 描述：分别返回列的标准差和方差。
• 语法：STD(column) 和 VARIANCE(column)
• 示例：SELECT STD(score) FROM exams; 和 SELECT VARIANCE(price) FROM products;

FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()

• 描述：返回指定列在聚合结果中的第一个、最后一个或第 n 个值。
• 语法：FIRST_VALUE(column) OVER (PARTITION BY partition_expression ORDER BY sort_expression ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
• 示例：SELECT FIRST_VALUE(name) OVER (ORDER BY age) AS youngest_name FROM users;

CUME_DIST(), NTH_RANK(), LEAST_RANK(), RANK(), ROW_NUMBER() 等窗口函数

• 描述：这些函数允许你在结果集的窗口中对行进行操作，如计算行在窗口中的相对位置、计算行之间的距离等。
• 语法：每种窗口函数具有特定的语法，具体可以参考 MySQL 官方文档。
• 示例：例如，使用 RANK() OVER (ORDER BY score DESC) 可以对成绩进行排名。

BIT_AND()

• 描述：返回所有非 NULL 值的列的 AND 运算结果。
• 语法：BIT_AND(column)
• 示例：SELECT BIT_AND(status) FROM users;

BIT_OR()

• 描述：返回所有非 NULL 值的列的 OR 运算结果。
• 语法：BIT_OR(column)
• 示例：SELECT BIT_OR(status) FROM users;

BIT_XOR()

• 描述：返回所有非 NULL 值的列的 XOR 运算结果。
• 语法：BIT_XOR(column)
• 示例：SELECT BIT_XOR(status) FROM users;

STDDEV() 和 VARIANCE()

• 描述：分别返回列的标准偏差和方差。
• 语法：STDDEV(column) 和 VARIANCE(column)
• 示例：SELECT STDDEV(score) FROM exams; 和 SELECT VARIANCE(price) FROM products;

GROUP_UNIQUE_MEMBER()

• 描述：返回在分组中具有唯一值的成员。
• 语法：GROUP_UNIQUE_MEMBER(column, column_list)
• 示例：SELECT GROUP_UNIQUE_MEMBER(name, 'John,Jane,Doe') FROM users;

GROUPING() 和 GROUPING_ID()

• 描述：用于在 GROUP BY 子句中标识聚合的组。
• 语法：GROUPING(column), GROUPING_ID(column)
• 示例：SELECT GROUPING(status) FROM users GROUP BY status; 和 SELECT GROUPING_ID(status) FROM users GROUP BY status;

SESSION_USER() 和 SYSTEM_USER()

• 描述：返回当前会话的用户或系统用户。
• 语法：SESSION_USER() 和 SYSTEM_USER()
• 示例：SELECT SESSION_USER() AS current_user; 和 SELECT SYSTEM_USER() AS current_user;

STRING_AGG()

• 描述：将多个字符串值连接成一个字符串。
• 语法：STRING_AGG(expression, separator)
• 示例：SELECT STRING_AGG(name, ', ') AS names FROM users;

JSON_ARRAY_AGG() 和 JSON_OBJECT_AGG()

• 描述：将多行数据聚合为 JSON 数组或对象。
• 语法：JSON_ARRAY_AGG(value) 和 JSON_OBJECT_AGG(key, value)
• 示例：SELECT JSON_ARRAY_AGG(score) AS scores FROM exams; 和 SELECT JSON_OBJECT_AGG(name, score) AS result FROM users;

XMLAGG()

• 描述：将多行数据聚合为 XML 格式。
• 语法：XMLAGG(expression)
• 示例：SELECT XMLAGG(XMLELEMENT(e, name || ' ' || age)) AS result FROM users;

GROUP_CONCAT_MAX() 和 GROUP_CONCAT_MIN()

• 描述：返回指定列的最大值或最小值，并将其他值连接成一个字符串。
• 语法：GROUP_CONCAT_MAX(column) 和 GROUP_CONCAT_MIN(column)
• 示例：SELECT GROUP_CONCAT_MAX(name) AS max_name FROM users; 和 SELECT GROUP_CONCAT_MIN(age) AS min_age FROM users;

控制流函数：

IF()

• 描述：IF 函数用于在 SQL 查询中执行条件判断。
• 语法：IF(condition, value_if_true, value_if_false)
• 示例：SELECT IF(age > 18, 'Adult', 'Minor') AS status FROM users;

CASE

• 描述：CASE 语句用于在 SQL 查询中执行多条件判断。
• 语法：CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 ... ELSE resultN END
• 示例：SELECT name, CASE status WHEN 'A' THEN 'Active' WHEN 'I' THEN 'Inactive' ELSE 'Unknown' END AS status_description FROM users;

NULLIF()

• 描述：NULLIF 函数用于比较两个表达式是否相等，如果相等则返回 NULL，否则返回第一个表达式的值。
• 语法：NULLIF(expression1, expression2)
• 示例：SELECT price / NULLIF(quantity, 0) AS unit_price FROM products;

COALESCE()

• 描述：COALESCE 函数返回其参数中的第一个非 NULL 值。
• 语法：COALESCE(value1, value2, ...)
• 示例：SELECT COALESCE(first_name, last_name, 'N/A') AS full_name FROM users;

GREATEST() 和 LEAST()

• 描述：GREATEST 和 LEAST 函数返回其参数中的最大值和最小值。
• 语法：GREATEST(value1, value2, ...) 和 LEAST(value1, value2, ...)
• 示例：SELECT GREATEST(price1, price2, price3) AS max_price FROM products;

IFNULL() 和 COALESCE()

• 描述：IFNULL 函数用于检查字段是否为 NULL，如果是则返回指定的值，否则返回字段的值。它相当于 COALESCE(column, value) 当 column 不为 NULL 时。
• 语法：IFNULL(column, value)
• 示例：SELECT IFNULL(email, 'N/A') AS contact_email FROM users;

信息函数：

• DATABASE(): 返回当前数据库的名称。

SELECT DATABASE();

• VERSION(): 返回MySQL服务器的版本。

SELECT VERSION();

• TABLE_ROWS(): 返回表中行的估计数量。

SELECT TABLE_ROWS FROM information_schema.TABLES WHERE 
TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table';

• ENGINE(): 返回存储引擎的名称。

SELECT ENGINE FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database' 
AND TABLE_NAME = 'your_table';

• ROW_COUNT(): 返回上一条SQL语句影响的行数。

SELECT ROW_COUNT();

• LAST_INSERT_ID(): 返回最后一个INSERT语句生成的AUTO_INCREMENT值。

INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2');  
SELECT LAST_INSERT_ID();

• CONNECTION_ID(): 返回当前连接的唯一ID。

SELECT CONNECTION_ID();

• INFO() 或 INFORMATION(): 返回关于当前连接的信息。

SELECT INFORMATION();

• SCHEMA(): 返回当前数据库的名称（与DATABASE()函数类似）。

SELECT SCHEMA();

• USER() 和 CURRENT_USER(): 返回当前连接的用户和主机信息。

SELECT USER();

• SHOW DATABASES(): 显示所有数据库的列表。

SHOW DATABASES;

• SHOW TABLES(): 显示当前数据库中的所有表名。

SHOW TABLES FROM your_database;

• SHOW COLUMNS FROM table_name(): 显示表中的列信息。

SHOW COLUMNS FROM your_table;

• SHOW INDEX FROM table_name(): 显示表中的索引信息。

SHOW INDEX FROM your_table;

• SHOW PROCESSLIST(): 显示当前MySQL服务器进程的列表。

SHOW PROCESSLIST;

• STATISTICS() 或 STATUS(): 返回关于当前连接的状态信息。

SHOW STATUS;

• VERSION() 或 VERSIONING(): 返回当前MySQL服务器的版本（与VERSION()函数类似）。

SELECT VERSION();

• DATABASE_NAME() 或 DATABASENAME(): 返回当前数据库的名称（与DATABASE()函数类似）。

SELECT DATABASE();

• ISNULL() 或 NULLIFNULL() 或 NULLIFZERO(): 检测一个值是否为NULL，或者两个值是否相等。如果条件为真，则返回一个指定的值或表达式。这些函数可以帮助你在查询中处理NULL值和特定条件下的值。

SELECT ISNULL(column_name) FROM table_name;

加密函数：

• AES_ENCRYPT() 和 AES_DECRYPT():

AES_ENCRYPT(str, key): 使用AES算法和提供的密钥对字符串进行加密。

AES_DECRYPT(enc_str, key): 使用AES算法和提供的密钥对加密的字符串进行解密。

• DES_ENCRYPT() 和 DES_DECRYPT():

DES_ENCRYPT(str, key): 使用DES算法和提供的密钥对字符串进行加密。

DES_DECRYPT(enc_str, key): 使用DES算法和提供的密钥对加密的字符串进行解密。

• MD5():

MD5(str): 返回字符串的MD5哈希值。

• SHA1() 和 SHA2():

SHA1(str): 返回字符串的SHA-1哈希值。

SHA2(str, hash_length): 返回字符串的SHA-2哈希值，其中hash_length可以是224、256、384或512中的一个。

• PASSWORD():

PASSWORD(str): 返回MySQL专用的密码哈希值。通常用于mysql.user表中的密码字段。

• ENCODE() 和 DECODE():

ENCODE(str, password): 使用提供的密码对字符串进行编码。

DECODE(enc_str, password): 使用提供的密码对加密的字符串进行解码。