如何在 SQL 中使用函数

介绍

在使用关系型数据库和结构化查询语言 (SQL) 时，您可以从关系型数据库管理系统中存储、管理和检索数据。SQL 可以完整地检索存储在数据库中的数据。.

SQL 还可以通过函数执行计算和处理数据。例如，您可以使用函数检索四舍五入到最接近的美元的产品价格、计算产品平均购买数量，或确定购买商品的保修期剩余天数。.

在本教程中，您将使用各种 SQL 函数来执行数学计算、操作字符串和日期，以及使用聚合函数计算汇总信息。.

先决条件

要遵循本指南，您需要一台运行基于 SQL 的关系数据库管理系统 (RDBMS) 的计算机。本指南中的说明和示例已在以下环境中验证：

• 一台运行 Ubuntu 20.04 的服务器，具有具有管理权限的非 root 用户，并且配置了 UFW 防火墙，如 Ubuntu 20.04 初始服务器设置指南中所述。.
• MySQL 已按照“如何在 Ubuntu 20.04 上安装 MySQL”指南中的说明，在服务器上安装并进行了安全配置。本指南假设使用按照步骤 3 中所述过程创建的非 root 用户。.
• 本文将对如何执行 SELECT 查询以从数据库中选择数据进行基本介绍，如“如何在 SQL 中从表中选择行”指南中所述。.

注意：许多关系数据库管理系统 (RDBMS) 使用各自的 SQL 实现。虽然本教程中描述的命令在大多数 RDBMS 中都能运行，但标准 SQL 语法仅定义了有限数量的函数。此外，不同数据库引擎对标准语法的支持也各不相同。如果您在 MySQL 以外的系统上测试这些命令，则具体的语法或输出可能会有所不同。.

您还需要一个数据库，其中包含加载了示例数据的表，以便使用这些函数。我们建议您查看“连接到 MySQL 并设置示例数据库”部分，了解有关连接到 MySQL 服务器以及创建本指南示例中使用的测试数据库的详细信息。.

连接到 MySQL 并设置示例数据库

在本节中，您将连接到 MySQL 服务器并创建一个示例数据库，以便您可以按照本指南中的示例进行操作。.

如果您的 SQL 数据库系统运行在远程服务器上，请通过 SSH 登录到您的服务器：

ssh sammy@your_server_ip

然后打开 MySQL 服务器环境，而不是 萨米 请输入您的 MySQL 用户名：

mysql -u sammy -p

创建一个名为“书店”的数据库：

CREATE DATABASE bookstore;

如果数据库创建成功，您将收到类似以下内容的输出：

Output
Query OK, 1 row affected (0.01 sec)

要选择书店数据库，请运行 USE 命令：

USE bookstore;

您将看到以下输出：

Output
Database changed

选择数据库后，即可在其中创建示例表。在本指南中，我们将使用一个虚构的图书馆，该图书馆出售不同作者的各种书籍。.

库存表包含书店现有书籍的信息。它包含以下列：

• book_id：此列包含每本书的 ID，数据类型为 int。此列将成为表的主键，每个值都成为对应行的唯一标识符。.
• 作者：本列包含书籍作者的姓名，使用 varchar 数据类型表示，最多 50 个字符。.
• 标题：此列包含所购书籍的标题，使用 varchar 数据类型表示，最多 200 个字符。.
• introduction_date：此列使用日期数据类型记录书店推出每本书的日期。.
• 库存：此列以整数数据类型保存书店库存书籍的数量。.
• 价格：此列存储书籍的零售价格，采用十进制数据类型，小数点前最多 5 个值，小数点后最多 2 个值。.

使用以下命令创建示例表：

CREATE TABLE inventory (
book_id int,
author varchar(50),
title varchar(200),
introduction_date date,
stock int,
price decimal(5, 2),
PRIMARY KEY (book_id)
);

如果打印出以下输出，则表示表格已创建：

Output
Query OK, 0 rows affected (0.00 sec)

然后，通过执行以下 INSERT INTO 操作，将一些示例数据加载到 purchases 表中：

INSERT INTO inventory
VALUES
(1, 'Oscar Wilde', 'The Picture of Dorian Gray', '2022-10-01', 4, 20.83),
(2, 'Jane Austen', 'Pride and Prejudice', '2022-10-04', 12, 42.13),
(3, 'Herbert George Wells', 'The Time Machine', '2022-09-23', 7, 21.99),
(4, 'Mary Shelley', 'Frankenstein', '2022-07-23', 9, 17.43),
(5, 'Mark Twain', 'The Adventures of Huckleberry Finn', '2022-10-01', 14, 23.15);

INSERT INTO 操作将五本具有指定值的书籍添加到库存表中。以下输出显示所有五行数据均已添加：

Output
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

至此，您就可以继续阅读本指南的其余部分，并开始在 SQL 中使用函数了。.

了解 SQL 函数

函数是命名表达式，它接受一个或多个值，对数据执行计算或转换，并返回一个新值作为结果。您可以将 SQL 函数理解为类似于数学函数。例如，log(x) 函数接受一个值 x，并返回 x 的对数。.

例如，如果您想检索所有书籍名称及其价格，并按价格从高到低排序，您可以运行以下命令：

SELECT title, price, introduction_date FROM inventory ORDER BY price DESC;

您将得到类似如下的输出：

Output
+------------------------------------+-------+-------------------+
| title | price | introduction_date |
+------------------------------------+-------+-------------------+
| Pride and Prejudice | 42.13 | 2022-10-04 |
| The Adventures of Huckleberry Finn | 23.15 | 2022-10-01 |
| The Time Machine | 21.99 | 2022-09-23 |
| The Picture of Dorian Gray | 20.83 | 2022-10-01 |
| Frankenstein | 17.43 | 2022-07-23 |
+------------------------------------+-------+-------------------+
5 rows in set (0.000 sec)

在这个命令中， 标题， 价格 和 介绍日期 是列名，在输出中，数据库显示了从这些列中检索到的每本书的完整值：书的完整标题、价格和书籍到达图书馆的日期。.

但是，您可能需要对数据库进行一些处理或操作后再检索值。例如，您可能想要获取四舍五入到最接近的美元的书籍价格、全大写的书籍标题，或者不包含月份或日期的出版年份。这时就需要使用函数了。.

SQL 函数可以根据其操作的数据类型大致分为几类。以下是最常用的函数：

• 数学函数：对数值进行运算并执行计算的函数，例如四舍五入、对数、平方根或幂运算。.
• 字符串操作函数：对字符串和文本字段进行操作的函数，执行文本转换，例如将文本转换为大写、截断或替换值中的单词。.
• 日期和时间函数：对日期字段进行操作的函数。这些函数执行计算和转换，例如向给定日期加上天数或从完整日期中减去一年。.
• 聚合函数：一种特殊的数学函数，它对从多行获得的值进行操作，例如计算所有行的平均价格。.

注意：大多数关系型数据库（包括 MySQL）都会扩展 SQL 标准定义的标准函数集，添加一些特定于数据库引擎的操作。许多标准 SQL 函数集之外的函数在许多数据库中都能以类似的方式运行，而另一些函数则特定于某个关系型数据库管理系统 (RDBMS) 及其独特功能。您可以参考所选数据库的文档，了解更多关于该数据库提供的函数的信息。以 MySQL 为例，您可以了解更多关于内置函数和运算符参考的信息。.

以下示例展示了使用名为 EXAMPLE 的虚构函数（该函数并不存在）来修改书店库存数据库中价格值结果的一般语法，该函数通过 SELECT 查询实现：

SELECT EXAMPLE(price) AS new_price FROM inventory;

函数 (EXAMPLE) 接受一个用括号括起来的参数，参数为列名 (price)。查询的这一部分指示数据库对列中的 price 值运行 EXAMPLE 函数，并返回运算结果。AS new_price 指示数据库为查询过程中计算出的值分配一个临时名称 (new_price)。通过这个临时名称，您可以在输出中识别函数的运算结果，并使用 WHERE 和 ORDER BY 子句引用这些计算值。.

在接下来的部分中，你将使用数学函数进行常见计算。.

利用数学函数

数学函数处理的是数值，例如示例数据库中书籍的价格或库存数量。它们可用于在数据库中执行计算，从而根据您的需求定制结果。.

在 SQL 中，四舍五入是数学函数最常见的用途之一。假设你需要检索所有书籍的价格，但你只想获取四舍五入到最接近的整数美元的价格。为此，你可以使用 `for` 函数。 圆形的 使用执行舍入操作的工具。.

运行以下命令：

SELECT title, price, ROUND(price) AS rounded_price FROM inventory;

您将看到类似以下内容的输出：

Output
+------------------------------------+-------+---------------+
| title | price | rounded_price |
+------------------------------------+-------+---------------+
| The Picture of Dorian Gray | 20.83 | 21 |
| Pride and Prejudice | 42.13 | 42 |
| The Time Machine | 21.99 | 22 |
| Frankenstein | 17.43 | 17 |
| The Adventures of Huckleberry Finn | 23.15 | 23 |
+------------------------------------+-------+---------------+
5 rows in set (0.000 sec)

上述命令返回各列的值。 标题 和 价格 临时柱 四舍五入价格 这是由该函数产生的 ROUND(价格) 此函数接受一个参数，即列名（此处为： 价格) 并将表格中该列的值四舍五入到最接近的整数。.

ROUND 函数还可以接受其他参数，用于指定舍入的小数位数，以及使用算术运算代替列名。例如，运行以下查询：

SELECT title, price, ROUND(price * stock, 1) AS stock_price FROM inventory;

您将得到以下输出：

Output
+------------------------------------+-------+-------+-------------+
| title | stock | price | stock_price |
+------------------------------------+-------+-------+-------------+
| The Picture of Dorian Gray | 4 | 20.83 | 83.3 |
| Pride and Prejudice | 12 | 42.13 | 505.6 |
| The Time Machine | 7 | 21.99 | 153.9 |
| Frankenstein | 9 | 17.43 | 156.9 |
| The Adventures of Huckleberry Finn | 14 | 23.15 | 324.1 |
+------------------------------------+-------+-------+-------------+
5 rows in set (0.000 sec)

执行 ROUND(价格 * 库存, 1) 它首先将单本书的价格乘以现有书籍的数量，然后将结果四舍五入到小数点后一位。结果将显示在临时列 Stock_Price 中。.

MySQL 内置的其他数学函数包括三角函数、平方根、幂运算、对数和指数函数。您可以在教程“如何在 SQL 中使用数学表达式和聚合函数”中了解更多关于在 SQL 中使用数学函数的信息。.

下一节中，您将使用 SQL 函数处理数据库中的文本。.

使用文本功能

SQL 中的文本函数可用于操作和更改字符串值。例如，您可能希望通过删除书名开头和结尾的多余空格，以更有序的方式检索书名。.

为此，您可以使用该函数。 修剪 使用此函数可删除字符串开头和结尾的所有空格和多余字符。.

请看以下示例：

SELECT LOWER(title) AS title_lowercase FROM inventory;

屏幕上会输出以下内容：

Output
+------------------------------------+
| title_lowercase |
+------------------------------------+
| the picture of dorian gray |
| pride and prejudice |
| the time machine |
| frankenstein |
| the adventures of huckleberry finn |
+------------------------------------+
5 rows in set (0.001 sec)

SQL 函数 LOWER 接受一个参数，并将其内容转换为小写。通过别名列 AS title_downcase，转换后的数据会存储在名为 title_downcase 的临时列中。.

现在检索所有作者，这次全部转换为大写。尝试运行以下 SQL 查询：

SELECT UPPER(author) AS author_uppercase FROM inventory;

您将得到以下输出：

Output
+----------------------+
| author_uppercase |
+----------------------+
| OSCAR WILDE |
| JANE AUSTEN |
| HERBERT GEORGE WELLS |
| MARY SHELLEY |
| MARK TWAIN |
+----------------------+
5 rows in set (0.000 sec)

您使用了 UPPER 函数而不是 LOWER 函数，两者功能类似，但 UPPER 函数会将文本转换为大写。如果您希望在检索数据时确保大小写一致性，可以使用这两个函数。.

另一个有用的字符串操作函数是 CONCAT，它接受多个包含文本值的参数，并将它们连接起来。尝试将作者和书名提取到同一列中。为此，请运行以下语句：

SELECT CONCAT(author, ': ', title) AS full_title FROM inventory;

该命令返回以下输出：

Output
+------------------------------------------------+
| full_title |
+------------------------------------------------+
| Oscar Wilde: The Picture of Dorian Gray |
| Jane Austen: Pride and Prejudice |
| Herbert George Wells: The Time Machine |
| Mary Shelley: Frankenstein |
| Mark Twain: The Adventures of Huckleberry Finn |
+------------------------------------------------+
5 rows in set (0.001 sec)

CONCAT 函数用于连接多个字符串，它接受三个参数。第一个参数 author 指的是包含作者姓名的作者列。第二个参数 :, 是一个任意字符串值，用于用冒号分隔作者和书名。最后一个参数 title 指的是包含书名的列。.

查询结果为：作者和标题被返回到一个名为 full_title 的临时列中，该列由数据库引擎直接连接。.

MySQL 内置的其他字符串函数包括用于搜索和替换字符串、检索子字符串、填充和修剪字符串值以及应用正则表达式等的函数。您可以在教程“如何使用 CAST 函数和连接表达式在 SQL 中操作数据”中了解更多关于使用 SQL 函数连接多个值的信息。您还可以参考 MySQL 文档中的“字符串函数和运算符”部分。.

下一节中，您将使用 SQL 函数来操作数据库中的日期。.

使用日期和时间函数

SQL 中的日期和时间函数允许您在处理 SQL 查询时操作存储在包含日期和时间戳的列中的值。它们可用于提取部分日期信息、执行日期计算或将日期和时间戳格式化为所需的输出格式。.

假设你需要将书籍的出版日期拆分为年、月、日，而不是在输出中只保留一个日期列。.

请尝试运行以下命令：

SELECT introduction_date, YEAR(introduction_date) as year, MONTH(introduction_date) as month, DAY(introduction_date) as day FROM inventory;

您将看到类似以下内容的输出：

Output
+-------------------+------+-------+------+
| introduction_date | year | month | day |
+-------------------+------+-------+------+
| 2022-10-01 | 2022 | 10 | 1 |
| 2022-10-04 | 2022 | 10 | 4 |
| 2022-09-23 | 2022 | 9 | 23 |
| 2022-07-23 | 2022 | 7 | 23 |
| 2022-10-01 | 2022 | 10 | 1 |
+-------------------+------+-------+------+
5 rows in set (0.000 sec)

这条 SQL 语句使用了三个独立的函数：YEAR、MONTH 和 DAY。每个函数都以存储日期的列名作为参数，并分别提取完整日期的一部分：年、月或日。利用这些函数，您可以在 SQL 查询中访问日期的各个组成部分。.

另一个用于处理日期的实用函数是 DATEDIFF，它可以计算两个日期之间的天数。现在，请检查每本书的出版日期与当前日期之间相隔了多少天。.

运行以下查询：

SELECT introduction_date, DATEDIFF(introduction_date, CURRENT_DATE()) AS days_since FROM inventory;

屏幕上会输出以下内容：

Output
+-------------------+------------+
| introduction_date | days_since |
+-------------------+------------+
| 2022-10-01 | -30 |
| 2022-10-04 | -27 |
| 2022-09-23 | -38 |
| 2022-07-23 | -100 |
| 2022-10-01 | -30 |
+-------------------+------------+
5 rows in set (0.000 sec)

DATEDIFF 函数接受两个参数：起始日期和结束日期。DATEDIFF 函数计算这两个日期之间的天数差。如果结束日期早于起始日期，则结果可能为负数。在本例中，第一个参数是 introduction_date 列的名称，该列存储库存表中的日期。第二个参数是另一个名为 CURRENT_DATE 的函数，它表示当前系统日期。运行此查询将检索这两个时间点之间的天数，并将结果存储在名为 days_since 的临时列中。.

注意：DATEDIFF 函数并非 SQL 标准函数集的一部分。虽然许多数据库都支持此函数，但不同数据库引擎的语法往往有所不同。本示例遵循 MySQL 的原生语法。.

MySQL 内置的其他日期操作函数包括：添加和减去时间间隔和日期、为不同语言格式格式化日期、检索日和月名称，以及创建新的日期值。您可以在“如何在 SQL 中处理日期和时间”教程中了解更多关于在 SQL 中处理日期的信息。您还可以参考 MySQL 文档中的“日期和时间函数”部分。.

下一节，您将学习如何使用聚合函数。.

使用聚合函数

在前面的所有示例中，您都使用 SQL 函数对一行中的各个列值进行转换或计算，这行代表书店中的一本书。SQL 提供了一种对多行执行数学计算的方法，可以帮助您找到有关整个数据集的汇总信息。.

• SQL 中的基本聚合函数包括：
• AVG 表示进行计算所用数值的平均值。.
• COUNT 用于统计进行计算的值的数量。.
• MAX 表示最大值。.
• MIN 表示最小值。.
• SUM 表示所有值的总和。.

您可以在 SELECT 查询中包含多个聚合函数。假设您想查看书店中列出的书籍数量、每本书的最高价格以及整个书店的平均价格。为此，请运行以下语句：

SELECT COUNT(title) AS count, MAX(price) AS max_price, AVG(price) AS avg_price FROM inventory;

该命令返回以下输出：

Output
+-------+-----------+-----------+
| count | max_price | avg_price |
+-------+-----------+-----------+
| 5 | 42.13 | 25.106000 |
+-------+-----------+-----------+
1 row in set (0.001 sec)

上述查询同时使用了三个聚合函数。COUNT 函数用于统计查询结果的行数。在本例中，标题列作为参数传递，但由于每个标记列的行数相同，因此您可以使用任何其他列名作为函数参数。MAX 函数用于计算价格列中的最大值：此处列名很重要，因为计算是针对该列中的值进行的。最后一个函数是 AVG 函数，它用于计算价格列中所有价格的平均值。.

以这种方式使用聚合函数，会向数据库返回一行数据，其中包含表示聚合计算结果的临时列。源行在内部用于计算，但不会通过查询返回。在本例中，您使用聚合函数一次性计算了整个库存表中的统计值，并在汇总时考虑了所有行。.

使用 SQL，您可以将表中的行分组，然后分别计算每组的总和。例如，您可以计算不同作者书籍的平均价格，从而找出哪位作者出版的书籍价格最高。您可以在教程“如何在 SQL 中使用 GROUP BY 和 ORDER BY”中了解更多关于如何对行进行分组以进行此类计算的信息。您还可以在教程“如何在 SQL 中使用数学表达式和聚合函数”中了解更多关于如何使用聚合的信息。.

结果

在本指南中，您学习了如何在 SQL 中使用函数来对数据进行计算并创建更复杂的查询。这些函数可以帮助您以不同的方式处理数据并获得更好的结果。请注意，SQL 的具体语法可能因您使用的关系数据库管理系统 (RDBMS) 的类型而异。.