SQL 实用函数详解：提升数据操作效率-51CTO.COM

一、概述

SQL 是一种功能强大的查询语言，在处理数据时，SQL 提供了许多内置函数，这些函数极大地提高了数据操作的效率和灵活性。本文将介绍一些常用且实用的 SQL 函数，并提供相应的示例代码来展示这些函数的使用方法。

二、常用SQL函数分类

SQL 函数可以大致分为以下几类：

字符串函数
数值函数
日期时间函数
聚合函数

三、字符串函数

1. CONCAT()

CONCAT() 用于将多个字符串连接在一起。

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

输出:

Greeting
---------
Hello World

说明: 以上示例中，CONCAT() 函数将 Hello、空格和 World 拼接成一个字符串。

2. SUBSTRING()

SUBSTRING() 用于从字符串中提取子字符串。

SELECT SUBSTRING('Hello World', 1, 5) AS Substring;

输出:

Substring
---------
Hello

说明: SUBSTRING() 从字符串 "Hello World" 的第 1 个字符开始，提取长度为 5 的子字符串。

3. LENGTH()

LENGTH() 用于计算字符串的长度。

SELECT LENGTH('Hello World') AS Length;

输出:

Length
------
11

说明: LENGTH() 函数返回字符串 Hello World 的长度。

4. UPPER() 和 LOWER()

UPPER() 函数将字符串中的所有字母字符转换为大写。非字母字符保持不变。

SELECT UPPER(name) AS upper_name FROM user;

输出:

upper
------
ZHY
UYII
ADMIN

说明: UPPER() 函数返回name字段的大写形式。

LOWER() 函数则相反，将字符串中的所有字母字符转换为小写。非字母字符保持不变。

5. REPLACE()

REPLACE() 函数用于在字符串中替换所有指定的子字符串为一个新字符串。

UPDATE employees
SET department = REPLACE(department, 'Sales', 'Marketing');

说明: 假设我们有一个名为 employees 的表，其中有一个名为 department 的列，我们想要将所有部门的名称中的Sales替换为Marketing。

四、数值函数

1. ROUND()

ROUND() 用于将数字四舍五入到指定的小数位数。

SELECT ROUND(123.4567, 2) AS RoundedValue;

输出:

RoundedValue
------------
123.46

说明: ROUND() 函数将 123.4567 四舍五入到 2 位小数，结果为 123.46。

2. CEIL() 和 FLOOR()

CEIL() 函数返回大于或等于指定数字的最小整数，而 FLOOR() 函数返回小于或等于指定数字的最大整数。

SELECT CEIL(123.4567) AS CeilValue, FLOOR(123.4567) AS FloorValue;

输出:

CeilValue | FloorValue
----------------------
124       | 123

说明: CEIL() 函数将 123.4567 向上取整，而 FLOOR() 函数将其向下取整。

五、日期时间函数

1. NOW()

NOW() 函数返回当前的日期和时间。

SELECT NOW() AS CurrentDateTime;

输出:

CurrentDateTime
-----------------------
2024-08-09 10:35:29

说明: NOW() 函数返回当前的日期和时间，格式为 YYYY-MM-DD HH:MM:SS。

2. DATE_FORMAT()

DATE_FORMAT() 函数用于以特定格式显示日期和时间。

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS FormattedDate;

输出:

FormattedDate
-------------
2024-08-09

说明: DATE_FORMAT() 函数将当前日期格式化为 YYYY-MM-DD 的格式。

3. DATEDIFF()

DATEDIFF() 函数用于计算两个日期之间的天数差。

SELECT DATEDIFF('2024-12-31', '2024-01-01') AS DaysDifference;

输出:

DaysDifference
---------------
365

说明: DATEDIFF() 函数计算两个日期之间的天数差。

4. CURDATE()

CURDATE() 函数用于返回当前的日期，格式为 YYYY-MM-DD。

SELECT CURDATE() AS CurrentDate;

输出:

CurrentDate
------------
2024-08-09

说明: CURDATE() 函数返回当前日期，不包含时间部分。

5. CURTIME()

CURTIME() 函数用于返回当前的时间，格式为 HH:MM:SS。

SELECT CURTIME() AS CurrentTime;

输出:

CurrentTime
------------
10:35:29

说明: CURTIME() 函数返回当前时间，不包含日期部分。

6. DATE_ADD() 和 DATE_SUB()

DATE_ADD() 函数用于在指定的日期上添加一个时间间隔（如天、月、年等），并返回新的日期。

DATE_SUB() 函数用于从指定的日期上减去一个时间间隔（如天、月、年等），并返回新的日期。

SELECT DATE_ADD(date, INTERVAL expr unit) AS NewDate;
SELECT DATE_SUB(date, INTERVAL expr unit) AS NewDate;

说明:

date: 基准日期。
expr: 需要减去的时间间隔。
unit: 时间单位（如 DAY、MONTH、YEAR 等）。

7. EXTRACT()

EXTRACT() 函数用于从日期或时间中提取指定的部分，如年份、月份、天、小时等。

SELECT EXTRACT(YEAR FROM '2024-08-09') AS YearPart;
# 需要提取的部分（如 YEAR、MONTH、DAY、HOUR、MINUTE 等）。

输出:

YearPart
---------
2024

说明: EXTRACT(YEAR FROM '2024-08-09') 提取日期中的年份部分，结果为 2024。

六、聚合函数

1. COUNT()

COUNT() 函数用于返回满足条件的行数。

SELECT COUNT(*) AS TotalRows FROM employees;

输出:

TotalRows
---------
100

说明: COUNT(*) 函数计算 employees 表中的总行数。

2. SUM()

SUM() 函数用于返回指定列的数值总和。

SELECT SUM(salary) AS TotalSalary FROM employees;

输出:

TotalSalary
-----------
500000

说明: SUM() 函数计算 employees 表中所有员工的工资总和。

3. AVG()

AVG() 函数用于返回指定列的平均值。

SELECT AVG(salary) AS AverageSalary FROM employees;

输出:

AverageSalary
-------------
5000

说明: AVG() 函数计算 employees 表中员工工资的平均值。

七、条件表达式函数

1. IF()

IF() 函数用于在 SQL 查询中实现简单的条件判断。根据条件的真假，返回不同的结果。

SELECT IF(condition, true_value, false_value);

# 示例
SELECT name, salary, IF(salary > 10000, '高薪', '普通薪资') AS SalaryLevel
FROM employees;

说明:

condition: 需要判断的条件表达式。
true_value: 如果条件为真 (TRUE)，返回的值。
false_value: 如果条件为假 (FALSE)，返回的值。

示例: 判断某员工工资是否大于 10000，如果大于返回"高薪"，否则返回"普通薪资"。

name      | salary | SalaryLevel
---------------------------------
John Doe  | 12000   | 高薪
Jane Smith| 5000   | 普通薪资

2. CASE WHEN

CASE WHEN 函数用于在 SQL 查询中实现更复杂的条件判断逻辑。它类似于编程语言中的 switch-case 语句，可以根据多个条件返回不同的结果。

SELECT 
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
  END AS result_alias;

#示例：
SELECT name, salary, 
  CASE
    WHEN salary > 10000 THEN '高薪'
    WHEN salary BETWEEN 4000 AND 10000 THEN '中等薪资'
    ELSE '低薪'
  END AS SalaryLevel
FROM employees;

说明：

condition1, condition2, ...: 需要判断的条件表达式。
result1, result2, ...: 当条件为真时返回的结果。
default_result: 当所有条件都不满足时，返回的默认结果。
result_alias: 结果的别名。

示例: 根据员工的工资水平，划分为高薪、中等薪资和低薪三个等级。

name | salary | SalaryLevel
---------------------------------
张三  | 12000  | 高薪
李四  | 7000   | 中等薪资
王五  | 2500   | 低薪

结语

本文介绍了一些常用的 SQL 函数，这些函数可以极大地提高 SQL 查询的效率和灵活性。通过结合实际应用中的场景，我们可以更好地理解并运用这些函数来处理数据。