MySQL常用函数指南
MySQL提供了丰富的内置函数,可以帮助我们更高效地处理数据。本文将从基础到高级,介绍MySQL中常用的函数,并提供相应的示例。
1. 字符串函数
CONCAT()
连接两个或多个字符串。
SELECT CONCAT('Hello', ' ', 'World') AS greeting;
-- 结果: Hello World
LENGTH() 和 CHAR_LENGTH()
LENGTH()返回字符串的长度(以字节为单位),而CHAR_LENGTH()返回字符串的长度(以字符为单位)。
SELECT
LENGTH('MySQL') AS byte_length,
CHAR_LENGTH('MySQL') AS char_length,
LENGTH('你好') AS chinese_byte_length,
CHAR_LENGTH('你好') AS chinese_char_length;
-- 结果: 5, 5, 6, 2
UPPER() 和 LOWER()
将字符串转换为大写或小写。
SELECT UPPER('hello') AS uppercase, LOWER('WORLD') AS lowercase;
-- 结果: HELLO, world
LEFT() 和 RIGHT()
从字符串的左侧或右侧提取指定数量的字符。
SELECT LEFT('MySQL Database', 5) AS left_substring, RIGHT('MySQL Database', 8) AS right_substring;
-- 结果: MySQL, Database
SUBSTRING()
从字符串中提取子串。
SELECT SUBSTRING('Hello World', 1, 5) AS sub_string;
-- 结果: Hello
TRIM()
去除字符串两端的空格或指定字符。
SELECT TRIM(' MySQL ') AS trimmed_string;
-- 结果: MySQL
2. 数值函数
ABS()
返回数字的绝对值。
SELECT ABS(-15) AS absolute_value;
-- 结果: 15
ROUND()
四舍五入到指定的小数位。
SELECT ROUND(3.14159, 2) AS rounded_number;
-- 结果: 3.14
CEILING() 和 FLOOR()
分别返回不小于或不大于参数的最小整数。
SELECT CEILING(3.1) AS ceil, FLOOR(3.9) AS floor;
-- 结果: 4, 3
3. 日期和时间函数
NOW()
返回当前日期和时间。
SELECT NOW() AS current_datetime;
-- 结果: 2024-08-16 12:34:56
DATE()
从日期或日期时间表达式中提取日期部分。
SELECT DATE('2024-08-16 12:34:56') AS date_only;
-- 结果: 2024-08-16
DATEDIFF()
计算两个日期之间的天数差。
SELECT DATEDIFF('2024-08-16', '2024-08-01') AS days_difference;
-- 结果: 15
DATE_FORMAT()
根据指定的格式显示日期和时间。这个函数非常灵活,可以根据需要自定义输出格式。
基本语法:
DATE_FORMAT(date, format)
让我们使用日期时间 “2021-05-31 11:11:11” 来展示各种格式化选项:
SET @datetime = '2021-05-31 11:11:11';
SELECT
DATE_FORMAT(@datetime, '%Y-%m-%d %H:%i:%s') AS 'Standard Format',
DATE_FORMAT(@datetime, '%d/%m/%Y') AS 'DD/MM/YYYY',
DATE_FORMAT(@datetime, '%Y年%m月%d日 %H时%i分%s秒') AS 'Chinese Format',
DATE_FORMAT(@datetime, '%b %d, %Y at %h:%i %p') AS 'Readable Format',
DATE_FORMAT(@datetime, '%W, %M %e, %Y') AS 'Full Text Format',
DATE_FORMAT(@datetime, '%Y%m%d%H%i%s') AS 'Compact Format',
DATE_FORMAT(@datetime, '%j') AS 'Day of Year',
DATE_FORMAT(@datetime, '%U') AS 'Week Number',
DATE_FORMAT(@datetime, '%w') AS 'Day of Week (0-6)',
DATE_FORMAT(@datetime, '%k:%i') AS '24-hour Time',
DATE_FORMAT(@datetime, '%h:%i %p') AS '12-hour Time';
结果:
Standard Format: 2021-05-31 11:11:11
DD/MM/YYYY: 31/05/2021
Chinese Format: 2021年05月31日 11时11分11秒
Readable Format: May 31, 2021 at 11:11 AM
Full Text Format: Monday, May 31, 2021
Compact Format: 20210531111111
Day of Year: 151
Week Number: 21
Day of Week (0-6): 1
24-hour Time: 11:11
12-hour Time: 11:11 AM
常用的格式说明符:
-
年份:
- %Y: 四位数年份 (2021)
- %y: 两位数年份 (21)
-
月份:
- %m: 月份,数字格式,带前导零 (01-12)
- %c: 月份,数字格式,不带前导零 (1-12)
- %M: 月份名称 (January-December)
- %b: 缩写的月份名称 (Jan-Dec)
-
日期:
- %d: 月份中的天数,带前导零 (01-31)
- %e: 月份中的天数,不带前导零 (1-31)
- %D: 带有英文后缀的日期 (1st, 2nd, 3rd, …)
-
星期:
- %W: 星期名称 (Sunday-Saturday)
- %a: 缩写的星期名称 (Sun-Sat)
- %w: 星期几,数字格式 (0=Sunday, 6=Saturday)
-
小时:
- %H: 小时,24小时格式,带前导零 (00-23)
- %k: 小时,24小时格式,不带前导零 (0-23)
- %h: 小时,12小时格式,带前导零 (01-12)
- %I: 小时,12小时格式,带前导零 (01-12)
- %l: 小时,12小时格式,不带前导零 (1-12)
-
分钟和秒:
- %i: 分钟,带前导零 (00-59)
- %s: 秒,带前导零 (00-59)
-
其他:
- %p: AM 或 PM
- %j: 一年中的天数 (001-366)
- %U: 一年中的第几周 (00-53),星期日为一周的第一天
- %u: 一年中的第几周 (00-53),星期一为一周的第一天
4. 条件函数
IF()
根据条件返回不同的值。
SELECT IF(10 > 5, 'True', 'False') AS result;
-- 结果: True
IFNULL()
如果第一个参数不为NULL,则返回第一个参数,否则返回第二个参数。
SELECT IFNULL(NULL, 'Default') AS result;
-- 结果: Default
CASE
多条件判断。
SELECT
CASE
WHEN grade >= 90 THEN 'A'
WHEN grade >= 80 THEN 'B'
WHEN grade >= 70 THEN 'C'
ELSE 'D'
END AS letter_grade
FROM students;
5. 聚合函数
COUNT()
计算行数或非NULL值的数量。
SELECT COUNT(*) AS total_rows FROM users;
SUM()
计算一组值的总和。
SELECT SUM(price) AS total_price FROM products;
AVG()
计算一组值的平均值。
SELECT AVG(age) AS average_age FROM employees;
MAX() 和 MIN()
返回一组值中的最大值或最小值。
SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary FROM employees;
6. 高级函数
GROUP_CONCAT()
将组中的字符串连接成一个字符串。
SELECT department, GROUP_CONCAT(name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department;
JSON函数
处理JSON数据。
SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') AS name;
-- 结果: "John"
全文搜索函数
在全文索引中执行搜索。
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('MySQL functions' IN NATURAL LANGUAGE MODE);
窗口函数
执行跨行操作。
SELECT
name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
以上是MySQL中一些常用函数的介绍和示例。这些函数涵盖了从基本的字符串和数值操作到高级的JSON处理和窗口函数。在实际应用中,这些函数可以大大提高数据处理的效率和灵活性。