如何在 SQL 中使用函数

作者选择了多样性在技术基金作为写给捐款计划的一部分接受捐款。

介绍

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

SQL 还可以通过使用函数进行计算和操纵数据,例如,您可以使用函数获取到最接近的美元的产品价格,计算产品购买的平均数量,或确定到特定购买的保修期限为止的天数。

在本教程中,您将使用不同的 SQL 函数来执行数学计算,操纵字符串和日期,并使用汇总函数计算总结。

前提条件

要遵循本指南,您需要运行基于 SQL 的关系数据库管理系统(RDBMS)的计算机。

运行 Ubuntu 20.04 的服务器,具有非 root 用户的管理权限和与 UFW 配置的防火墙,如我们在 [Ubuntu 20.04 的初始服务器设置指南] 所描述的。
MySQL 安装并保护在服务器上,如在 [Ubuntu 20.04 上如何安装 MySQL] 所描述的。

<$>[注] 注: 许多 RDBMS 使用自己的 SQL 实现程序. 虽然本教程中描述的命令将在大多数 RDBMS 上工作,但标准 SQL 语法指定了有限的函数。

您还需要一个数据库,其中有一些表上载有样本数据,以便您可以练习使用函数. 我们鼓励您通过下面的连接到MySQL和设置样本数据库部分,了解有关连接到MySQL服务器和创建在本指南中使用的测试数据库的详细信息。

连接到MySQL并设置样本数据库

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

如果您的 SQL 数据库系统在远程服务器上运行,则从本地计算机输入 SSH 到服务器:

1[environment local]
2ssh sammy@your_server_ip

然后打开MySQL服务器提示,用您的MySQL用户帐户的名称代替sammy:

1mysql -u sammy -p

创建一个名为书店的数据库:

1CREATE DATABASE bookstore;

如果数据库创建成功,您将收到这样的输出:

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)

若要選擇「書店」資料庫,請執行以下 USE 聲明:

1USE bookstore;

您将获得以下输出:

1[secondary_label Output]
2Database changed

选择数据库后,您可以在其内部创建样本表. 对于本指南,我们将使用销售不同作者书籍的想象书店。

库存表将包含书店书籍的数据,它将包含以下列:

此列将成为表的主要密钥,每个值将成为其相应行的唯一标识符。

作者:此列包含著作者的名称,用varchar数据类型表示最多50个字符。
title:此列包含购买书的标题,使用varchar数据类型表示最多200个字符。
introduction_date: 使用date数据类型,此列包含每本书由书店引入的日期。

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

1CREATE TABLE inventory (
2    book_id int,
3    author varchar(50),
4    title varchar(200),
5    introduction_date date,
6    stock int,
7    price decimal(5, 2),
8    PRIMARY KEY (book_id)
9);

如果下列输出打印,则已创建表:

1[secondary_label Output]
2Query OK, 0 rows affected (0.00 sec)

接下来,通过运行以下INSERT INTO操作来加载一些样本数据的购买表:

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

INSERT INTO 操作将为库存表添加指定的值的五个图书,以下输出表示已添加所有五行:

1[secondary_label Output]
2Query OK, 5 rows affected (0.00 sec)
3Records: 5 Duplicates: 0 Warnings: 0

有了它,您可以随时遵循其他指南,并开始使用 SQL 中的函数。

了解 SQL 函数

Functions 是指使用一个或多个值,对数据进行计算或转换并返回新的值的表达式,您可以以类似于数学中的函数的方式思考 SQL 函数。

通常情况下,要从关系数据库中获取信息(而不进行转换),您会使用 SELECT 查询,要求数据库通过在声明中指定列名来返回您感兴趣的个别列的值。

例如,如果您想要查找所有书籍标题的价格,从最贵到最便宜,您可以执行以下陈述:

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

您将获得以下输出:

 1[secondary_label Output]
 2+------------------------------------+-------+-------------------+
 3| title                              | price | introduction_date |
 4+------------------------------------+-------+-------------------+
 5| Pride and Prejudice                | 42.13 | 2022-10-04        |
 6| The Adventures of Huckleberry Finn | 23.15 | 2022-10-01        |
 7| The Time Machine                   | 21.99 | 2022-09-23        |
 8| The Picture of Dorian Gray         | 20.83 | 2022-10-01        |
 9| Frankenstein                       | 17.43 | 2022-07-23        |
10+------------------------------------+-------+-------------------+
115 rows in set (0.000 sec)

在本声明中,标题、价格和介绍_日期是列名称,在结果输出中,数据库为每本书提供了从这些列中获取的完整值:完整的书名称、价格和书籍进入书店的日期。

但是,您可能还希望在某种形式的处理或操纵后从数据库中获取值。您可能感兴趣的图书价格是到最接近的美元,图书标题显示在上方,或引入年份,而不包括月或日。

SQL 函数可以广泛分为几组,取决于它们运作的数据类型,这些是最常用的函数:

数学函数:运用数值并执行计算的函数,如圆形、逻辑、平方根或功率。
字符串操作函数:运用字符串和文本字段进行文本转换的函数,如将文本转换为上图、剪辑或代替值中的单词。
日期和时间函数:运作于持有日期的字段上的函数.这些函数执行计算和转换,例如将日期添加到给定日期或从完整日期取出仅一年。

<$>[注] **注:**大多数关系式数据库,包括MySQL,将SQL标准定义的标准函数集扩展到该数据库引擎的其他特定的操作中。在许多数据库中,许多 SQL 函数组外的许多函数在许多数据库中相似地工作,而其他函数仅限于一个单一的RDBMS及其独特功能。您可以参阅您所选择的数据库的文档,以了解更多关于数据库提供的函数。

下面的示例显示使用名为EXAMPLE的想象中的非存在函数以使用SELECT查询更改书店库存数据库中的价格值的结果的通用语法:

1SELECT EXAMPLE(price) AS new_price FROM inventory;

该函数(EXAMPLE)将列名(price)作为列表中包含的参数。该查询部分告知数据库在列 price 的值上执行函数 EXAMPLE,并返回此操作的结果。 AS new_price 告知数据库在查询期间为计算值分配临时名称 ('new_price`)。

在下一节中,您将使用数学函数来执行常用的计算。

使用数学函数

数学函数是基于数值的,例如图书价格或样本数据库中库存的图书数量,可以用来在数据库中进行计算,以使结果符合您的要求。

圆形是SQL中最常用的数学函数应用程序之一. 想象一下,您需要查找所有书籍的价格,但您对圆形到最近的整个美元的值感兴趣。

尝试执行以下陈述:

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

以下输出将打印到屏幕上:

 1[secondary_label Output]
 2+------------------------------------+-------+---------------+
 3| title                              | price | rounded_price |
 4+------------------------------------+-------+---------------+
 5| The Picture of Dorian Gray         | 20.83 |            21 |
 6| Pride and Prejudice                | 42.13 |            42 |
 7| The Time Machine                   | 21.99 |            22 |
 8| Frankenstein                       | 17.43 |            17 |
 9| The Adventures of Huckleberry Finn | 23.15 |            23 |
10+------------------------------------+-------+---------------+
115 rows in set (0.000 sec)

查询将从标题和价格列中选取值,并与圆圆价函数结果的临时圆价列一起使用。

圆形函数还可以接受定义圆形应发生的十进制位数的额外参数,以及算术操作,而不是单个列名称。

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

您将收到以下输出:

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

執行「ROUND(價格 * 股票, 1)」會先將單一書籍價格乘以存儲的書籍數量,然後將結果的價格圓到第一個十字位。

MySQL 内置的其他数学函数包括三角形函数、平方根、功率、算数和指数,您可以在如何在 SQL 中使用数学表达式和汇总函数教程中了解有关使用 SQL 中的数学函数的更多信息。

在下一节中,您将使用 SQL 函数操作数据库中的文本。

使用 String 操纵功能

SQL 中的字符串操纵函数允许您在处理 SQL 查询时更改存储在包含文本的列中的值,这些函数可用于转换案例,连接来自多个列的数据,或执行搜索和替换操作。

您将开始使用字符串函数,以获取已转换为 lowercase 的所有书籍标题。

1SELECT LOWER(title) AS title_lowercase FROM inventory;

以下输出将打印到屏幕上:

 1[secondary_label Output]
 2+------------------------------------+
 3| title_lowercase                    |
 4+------------------------------------+
 5| the picture of dorian gray         |
 6| pride and prejudice                |
 7| the time machine                   |
 8| frankenstein                       |
 9| the adventures of huckleberry finn |
10+------------------------------------+
115 rows in set (0.001 sec)

名为LOWER的 SQL 函数采用单个参数,并将其内容转换为 lowercase. 通过列名为AS title_lowercase,结果的数据在名为title_lowercase的临时列中呈现。

现在检索所有作者,这一次转换为 uppercase. 尝试运行以下 SQL 查询:

1SELECT UPPER(author) AS author_uppercase FROM inventory;

您将收到以下输出:

 1[secondary_label Output]
 2+----------------------+
 3| author_uppercase     |
 4+----------------------+
 5| OSCAR WILDE          |
 6| JANE AUSTEN          |
 7| HERBERT GEORGE WELLS |
 8| MARY SHELLEY         |
 9| MARK TWAIN           |
10+----------------------+
115 rows in set (0.000 sec)

您使用了UPPER函数,而不是LOWER函数,该函数以类似的方式工作,但会将文本转换为上面的函数,如果您想要在获取数据时保证字符案例一致性,可以使用这两个函数。

另一个有用的字符串操纵函数是CONCAT,该函数采用包含文本值的多个参数并将它们合并在一起。

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

此语句返回以下输出:

 1[secondary_label Output]
 2+------------------------------------------------+
 3| full_title                                     |
 4+------------------------------------------------+
 5| Oscar Wilde: The Picture of Dorian Gray        |
 6| Jane Austen: Pride and Prejudice               |
 7| Herbert George Wells: The Time Machine         |
 8| Mary Shelley: Frankenstein                     |
 9| Mark Twain: The Adventures of Huckleberry Finn |
10+------------------------------------------------+
115 rows in set (0.001 sec)

CONCAT函数连接了多个字符串,并用三个参数执行。第一个,作者,指的是作者列持有作者名称的字符串。第二个,:,是一个任意的字符串值,用一个字符串划分作者和书籍标题。

在此查询的结果中,作者和标题返回一个名为full_title的单一临时列,由数据库引擎直接连接。

其他嵌入 MySQL 的字符串函数包括搜索和替换字符串的函数,获取字符串的子串,插入和剪切字符串值,以及应用常规表达式,等等。您可以了解更多关于使用 SQL 函数来连接多个值的详细信息,请参阅如何在 SQL 中使用 CAST 函数和连接表达式来操纵数据教程。

在下一节中,您将使用 SQL 函数来操纵数据库中的日期。

使用日期和时间函数

SQL 中的日期和时间函数允许您在处理 SQL 查询时操纵存储在持有日期和时间印记的列中的值,可用于提取日期信息的部分,执行日期算法,或将日期和时间印记格式化为所需的输出格式。

假设您需要将书籍介绍日期单独划分为年、月和日期,而不是输出中有一个单一的日期列。

尝试执行以下陈述:

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

您将收到此输出:

 1[secondary_label Output]
 2+-------------------+------+-------+------+
 3| introduction_date | year | month | day  |
 4+-------------------+------+-------+------+
 5| 2022-10-01        | 2022 |    10 |    1 |
 6| 2022-10-04        | 2022 |    10 |    4 |
 7| 2022-09-23        | 2022 |     9 |   23 |
 8| 2022-07-23        | 2022 |     7 |   23 |
 9| 2022-10-01        | 2022 |    10 |    1 |
10+-------------------+------+-------+------+
115 rows in set (0.000 sec)

此 SQL 语句使用了三个单独的函数:年度,月和日期。每个函数采用列名称,其中日期作为一个参数存储,并提取完整日期的一部分:一年,一个月或一天。

另一个有用的日期操纵功能是DATEDIFF,它允许您检索两个日期之间的天数。

运行以下查询:

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

以下输出将打印到屏幕上:

 1[secondary_label Output]
 2+-------------------+------------+
 3| introduction_date | days_since |
 4+-------------------+------------+
 5| 2022-10-01        |        -30 |
 6| 2022-10-04        |        -27 |
 7| 2022-09-23        |        -38 |
 8| 2022-07-23        |       -100 |
 9| 2022-10-01        |        -30 |
10+-------------------+------------+
115 rows in set (0.000 sec)

DATEDIFF函数需要两个参数:开始日期和结束日期. DATEDIFF函数计算了分离这两个时间点的天数。如果结束日期较早,结果可能是负数。在本示例中,第一个参数是introduction_date列名,包含在库存表中的日期。第二个参数是另一个函数,即CURRENT_DATE,代表当前系统日期。

<$>[注] 注: DATEDIFF 不是官方 SQL 标准函数集的一部分.虽然许多数据库支持此功能,但不同的数据库引擎之间的语法往往不同。

MySQL 内置的其他日期操纵功能包括添加和扣除日期和时间间隔,为不同的语言格式格式格式化日期,检索日期和月号,或创建新的日期值。

在下一节中,您将学习如何使用汇总函数。

使用聚合函数

在之前的所有示例中,您使用 SQL 函数将转换或计算应用于单行中的单个列值,即图书店中的书籍。

SQL 中的主要聚合函数包括:

AVG 表示计算所执行的值的平均值。
COUNT 表示计算所执行的值的数量。
`MAX' 表示最大值。

假设你想检查书店中列出的书籍数量、任何可用的书的最大价格以及整个目录的平均价格。

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

此语句返回以下输出:

1[secondary_label Output]
2+-------+-----------+-----------+
3| count | max_price | avg_price |
4+-------+-----------+-----------+
5|     5 |     42.13 | 25.106000 |
6+-------+-----------+-----------+
71 row in set (0.001 sec)

上面的查询同时使用三个汇总函数。数函数计算查询所查看的行。在本示例中,标题作为参数传递,但由于每个检查的列的行数将是相同的,您也可以使用任何其他列名作为函数的参数。MAX函数计算价格列中的最大值:这里,列名很重要,因为计算是对该列的值进行的。

通过这种方式使用汇总函数,数据库将返回一个单行,其中包含代表汇总计算值的临时列。源行用于内部计算,但不会通过查询返回。

使用 SQL,还可以将表中的行分为组,然后单独计算这些组的总值。例如,您可以计算不同作者书籍的平均价格,以了解哪位作者发布了最昂贵的标题。您可以在如何使用 SQL 中的 GROUP BY 和 ORDER BY 教程中了解更多有关该计算的组合行。

结论

通过遵循本指南,您了解了什么是 SQL 函数,以及如何使用它们来操纵数字、字符串和日期使用函数. 您已经使用ROUND来圆形数字值,CONCAT来连接多个列成一个,DATEDIFF来计算两个时间点之间的天数。

您可以使用函数将一些数据操纵和计算卸载到数据库引擎中。本教程仅涵盖了为此目的使用函数的基本知识。为了以可靠的方式获取和分析数据,您可以使用 WHERE 条款和在 How To Use GROUP BY and ORDER BY in SQL中描述的组合来组合函数与条件查询。

虽然这里显示的命令应该在大多数关系数据库上工作,但请注意,每个 SQL 数据库都使用自己的语言实现,您应该查看您的 DBMS 的官方文档,以获得每个命令的完整描述和其完整的选项集。

如果您想了解更多有关 SQL 语言的不同概念并使用它的工作,我们鼓励您查看如何使用 SQL 系列中的其他指南。