如何在SQL中使用函数

引言

当使用关系数据库和结构化查询语言（SQL）时，您可以从关系数据库管理系统中存储、管理和检索数据。SQL可以从数据库中检索数据，保持其原样。

SQL也可以通过使用函数进行计算和操作数据。例如，您可以使用函数获取产品价格取整到最接近的美元，计算平均购买产品数量，或确定给定购买的保修期到期前的天数。

在本教程中，您将使用不同的SQL函数进行数学计算，操作字符串和日期，并使用聚合函数计算摘要信息。

先决条件 jié

要按照这个指南的步骤进行操作，你需要使用一台安装了基于SQL的关系型数据库管理系统（RDBMS）的计算机。本指南中的指令和示例是在以下环境下验证的：

• A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
• MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a non-root MySQL user, created using the process described in Step 3.
• Basic familiarity with executing SELECT queries to select data from the database, as described in our How To SELECT Rows FROM Tables in SQL guide.

您还需要一个数据库，其中包含一些加载了示例数据的表格，以便您可以练习使用函数。我们鼓励您查阅以下《连接到MySQL并设置示例数据库》部分，详细了解如何连接到MySQL服务器以及如何创建本指南中示例所使用的测试数据库。

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

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

如果你的SQL数据库系统运行在远程服务器上，那么从你的本地机器通过SSH登录到你的服务器。

1. ssh sammy@your_server_ip

 

然后打开MySQL服务器提示符，将sammy替换为您的MySQL用户账户的名称。

1. mysql -u sammy -p

 

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

1. CREATE DATABASE bookstore;

 

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

Query OK, 1 row affected (0.01 sec)

要选择图书店数据库，请运行以下USE语句：

1. USE bookstore;

 

您将收到以下输出结果：

Database changed

选择数据库后，您可以在其中创建示例表。在本指南中，我们将使用一个虚构的书店作为例子，该书店销售不同作者的图书。

图书馆存货表将包含关于书店图书的数据。它将包含以下列：

• book_id: This column holds the identifier for each book, represented by the int data type. This column will become the table’s primary key, with each value becoming a unique identifier for its respective row.
• author: This column holds the book author’s name, expressed using the varchar data type with a maximum of 50 characters.
• title: This column holds the purchased book’s title, expressed using the varchar data type with a maximum of 200 characters.
• introduction_date: Using the date data type, this column holds the date each book was introduced by the bookstore.
• stock: This column holds the number of books the bookstore has in its inventory using the int integer data type.
• price: This column stores the book’s retail price using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it.

用以下命令创建示例表格：创建样本表格。

1. CREATE 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. );

 

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

Query OK, 0 rows affected (0.00 sec)

随后，通过运行以下INSERT INTO操作，用一些样本数据加载购买表。

1. INSERT INTO inventory
2. VALUES
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);

 

插入操作将使用指定的值向库存表中添加五本书。下面的输出表示已经添加了所有五行。

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

随后，您就可以按照剩下的指南，开始在SQL中使用函数了。

理解SQL函数

函数是具有名称的表达式，它们接受一个或多个值，在数据上执行计算或转换，并返回一个新值作为结果。您可以将SQL函数类比于数学中的函数。函数log(x)接受一个x并返回x的对数值。

通常，从关系数据库中检索信息（不进行转换），您会使用SELECT查询，通过在语句中指定列名来要求数据库返回您感兴趣的各个列的值。

例如，如果您想检索所有书籍的标题和价格，并按照从最贵到最便宜的顺序排列，您可以执行以下语句：

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

 

您将会收到以下输出：

+————————————+——-+——————-+ | 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函数可以大致分为几个类别。以下是最常用的函数：

• Mathematical Functions: Functions operating on numerical values and performing computations, such as rounding, logarithms, square roots, or powers.
• String Manipulation Functions: Functions operating on strings and text fields that perform text transformations, such as converting the text to uppercase, trimming, or replacing words within the values.
• Date and Time Functions: Functions operating on fields holding dates. These functions perform computations and transformations, such as adding a number of days to the given date or taking just a year from the full date.
• Aggregate Functions: A special case of mathematical functions that operate on values coming from multiple rows, such as calculating an average price for all rows.

以下示例展示了使用一个名为EXAMPLE的虚构且不存在的函数来改变书店库存数据库中价格值的SELECT查询的一般语法。

1. SELECT EXAMPLE(price) AS new_price FROM inventory;

 

函数（EXAMPLE）以括号括起来的列名（price）作为参数。查询的这部分告诉数据库执行函数EXAMPLE在列price的值上，并返回此操作的结果。 AS new_price告诉数据库在查询期间为计算的值分配一个临时名称（new_price）。通过这样做，您可以在输出中区分函数的结果，并可以使用WHERE和ORDER BY子句引用计算的值。

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

使用数学函数

数学函数对数字值进行操作，比如样本数据库中的书价或库存量。它们可用于在数据库中执行计算，以便使结果符合您的需求。

四捨五入是SQL中最常用的數學函數之一。想像一下，你需要檢索所有書籍的價格，但你只關注被四捨五入到最接近的整數美元的價值。為了達到這個目的，你可以使用ROUND函數來執行四捨五入操作。

尝试执行以下声明。 xià

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

 

以下内容将打印到屏幕上：

+————————————+——-+—————+ | 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)

查询从标题（title）和价格（price）列中选取值，同时选择一个临时的 rounded_price 列，该列包含使用 ROUND(price) 函数返回的结果。该函数接受一个参数，即列名（在此情况下是 price），并返回表中该列的值四舍五入到最近的整数。

取整函数还可以接受额外的参数，该参数定义舍入的小数位数，也可以用于进行算术运算，而不仅仅是单个列名。例如，尝试运行以下查询：

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

 

你将收到以下的输出结果。

+————————————+——-+——-+————-+ | 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（price * stock, 1）将首先将单本书的价格与库存中的书籍数量相乘，然后将结果四舍五入至小数点后一位。结果将呈现在“stock_price”临时列中。

其他嵌入到MySQL中的数学函数包括三角函数、平方根、幂函数、对数和指数函数。您可以在《如何在SQL中使用数学表达式和聚合函数》教程中了解更多关于在SQL中使用数学函数的内容。

在接下来的部分中，您将使用SQL函数来操作数据库中的文本。

使用字符串操作函数

SQL中的字符串操作函数可以在处理SQL查询时改变存储在文本列中的值。它们可以用于转换大小写、将多个列的数据连接起来或执行搜索和替换操作等。

你将通过检索所有转换为小写的书名来开始使用字符串函数。执行以下语句：

1. SELECT LOWER(title) AS title_lowercase FROM inventory;

 

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

+————————————+ | 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_lowercase，结果数据以临时列title_lowercase的形式呈现。

现在检索所有作者，这次将它们转换为大写字母。尝试运行以下 SQL 查询：

1. SELECT UPPER(author) AS author_uppercase FROM inventory;

 

您将收到以下输出：

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

你用了UPPER函数代替LOWER函数，它们的功能相似，都将文本转换为大写。如果你想在检索数据时确保字符的大小写一致性，两个函数都可以使用。

另一个有用的字符串操作函数是CONCAT，它接受多个包含文本值的参数并将它们合并在一起。尝试在一个列中检索书籍的作者和标题的组合。要执行此操作，请执行以下语句：

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

 

这个声明返回以下输出：

+————————————————+ | 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中还内置了其他一些字符串函数，用于搜索和替换字符串、提取子字符串、填充和修剪字符串值、应用正则表达式等等。您可以通过在SQL教程中查找如何使用CAST函数和连接表达式来了解有关使用SQL函数连接多个值的更多信息。您还可以参考MySQL文档中的字符串函数和运算符。

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

使用日期和时间函数

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

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

请尝试执行以下命令:

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

 

你将会收到这个输出：

+——————-+——+——-+——+ | 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，它允许您获取两个日期之间的天数。现在，请尝试检查每本书的介绍日期与当前日期之间已经过去了多少天。

运行以下查询：

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

 

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

+——————-+————+ | 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 临时列中。

MySQL中还有其他的日期操作函数，包括添加和减去日期和时间间隔，为不同语言格式化日期，获取日期和月份的名称，或者创建新的日期值。您可以在《如何在SQL中处理日期和时间》教程中了解更多有关在SQL中处理日期的信息。您还可以参考MySQL文档中的日期和时间函数部分。

在接下来的部分，您将学习如何使用聚合函数。

使用聚合函数

在所有之前的例子中，你使用了SQL函数来对单行中的每个列值应用转换或计算，该行代表书店中的一本书。SQL提供了一种方法，可以在多行之间执行数学计算，以帮助你找到关于整个数据集的聚合信息。

SQL中的主要聚合函数包括以下几种：

• AVG for the average of the values the calculations are performed on.
• COUNT for the number of values the calculations are performed on.
• MAX for the maximum value.
• MIN for the minimum value.
• SUM for the sum of all values.

你可以在你的SELECT查询中结合多个聚合函数。想象一下，你想要查看书店中列出的书籍数量、任何一本书的最高价格以及整个目录中的平均价格。要实现这一点，执行以下语句：

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

 

这个陈述返回以下的输出结果。

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

以上查询同时使用了三个聚合函数。COUNT函数用于计算查询所查找的行数。在本例中，title被传递为参数，但由于每个列的行数是相同的，您也可以使用任何其他列名作为该函数的参数。MAX函数计算了price列中的最大值：在这里，列名很重要，因为计算是在该列的值上进行的。最后一个函数是AVG函数，它计算了price列中所有价格的平均值。

以这种方式使用聚合函数会导致数据库返回一行临时列，表示聚合计算的结果值。源行在内部用于计算，但不通过查询返回。在此示例中，您使用聚合函数一次从整个库存表计算统计值，考虑了所有行的摘要信息。

使用SQL，还可以将表中的行分成不同的组，然后分别计算这些组的合计值。例如，你可以计算不同作者的图书平均价格，以了解哪个作者出版的书最贵。你可以在SQL教程中的《如何使用GROUP BY和ORDER BY》中了解更多关于分组行进行计算的内容。你还可以通过《如何使用数学表达式和聚合函数的SQL教程》深入了解使用聚合方式的更多细节。

结论

通过遵循这个指南，你学会了什么是SQL函数以及如何使用它们来使用函数操作数值、字符串和日期。你使用了ROUND函数来将数字值四舍五入，使用CONCAT函数将多个列连接成一个，使用DATEDIFF函数计算两个时间点之间的天数。最后，你还使用了如COUNT、SUM或AVG等聚合函数来生成跨多行的摘要信息。

你可以使用函数将一些数据操作和计算任务转移到数据库引擎中。本教程仅涵盖了使用函数进行这些操作的基础知识。为了以强大的方式检索和分析数据，你可以将函数与使用WHERE子句进行条件查询和使用GROUP BY和ORDER BY进行分组的方法相结合。

虽然这里展示的命令在大多数关系数据库上都可以使用，但请注意每个SQL数据库都有自己的语言实现。您应该参考您的数据库管理系统的官方文档，以获取每个命令的完整描述和全部选项。

如果你想更多了解SQL语言的不同概念以及处理方法，我们鼓励你查看“How To Use SQL”系列中的其他指南。