SQL函数深度解析：从基础到高级应用

3 年 ago
清, 宇
5 minutes

引言

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

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

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

先决条件

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

注意：许多关系型数据库管理系统都使用自己的SQL实现。尽管本教程中提到的命令在大多数关系型数据库中都能正常运行，但标准SQL语法只指定了有限数量的函数。而且，对标准语法的支持在不同的数据库引擎中会有所不同。如果您在与MySQL不同的系统上进行测试，确切的语法或输出可能会有所不同。您还需要一个数据库，其中包含一些加载了示例数据的表格，以便您可以练习使用函数。我们鼓励您查阅以下“连接到MySQL并设置示例数据库”部分，详细了解如何连接到MySQL服务器以及如何创建本指南中示例所使用的测试数据库。

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

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

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

ssh sammy@your_server_ip

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

mysql -u sammy -p

创建一个名为bookstore的数据库。

CREATE DATABASE bookstore;

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

输出
Query OK, 1 row affected (0.01 sec)

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

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

 

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

Output

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);

 

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

Output

这是文章《如何在SQL中使用函数》的第3部分（共6部分）。

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;
    2.

您将会收到以下输出：

输出
+------------------------------------+-------+-------------------+
| 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）： 对数值进行操作并执行计算的函数，例如四舍五入、对数、平方根或幂运算。
  • 字符串操作函数（String Manipulation Functions）： 对字符串和文本字段进行操作的函数，执行文本转换，例如将文本转换为大写、修剪或替换值中的单词。
  • 日期和时间函数（Date and Time Functions）： 对包含日期的字段进行操作的函数。这些函数执行计算和转换，例如将天数添加到给定日期或仅从完整日期中提取年份。
  • 聚合函数（Aggregate Functions）： 一种特殊的数学函数，对来自多行的数据进行操作，例如计算所有行的平均价格。

注意：大多数关系数据库，包括MySQL，在SQL标准定义的基本函数集之外，还添加了特定于该数据库引擎的额外操作。许多SQL标准之外的函数在许多数据库中的工作方式相似，而其他函数则限于特定的关系数据库管理系统（RDBMS）及其独特功能。您可以查阅所选择数据库的文档以了解数据库提供的函数。对于MySQL，您可以在“内置函数及运算符参考”中获得更多信息。以下示例展示了使用一个名为EXAMPLE的虚构且不存在的函数来改变书店库存数据库中价格值的SELECT查询的一般语法。

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

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

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

使用数学函数

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

四舍五入是SQL中最常用的数学函数之一。想象一下，你需要检索所有书籍的价格，但你只关注被四舍五入到最接近的整美元的价值。为了达到这个目的，你可以使用ROUND函数来执行四舍五入操作。

尝试执行以下声明：

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

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

输出

这是文章《如何在SQL中使用函数》的第4部分（共6部分）。

+------------------------------------+-------+---------------+
| 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;

 

这个声明返回以下输出：

输出

这是文章《如何在SQL中使用函数》的第5部分（共6部分）。

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

注意：DATEDIFF不是官方SQL标准函数集的一部分。尽管许多数据库支持此函数，但其语法在不同的数据库引擎之间常常存在差异。此示例采用了符合MySQL本地语法的写法。MySQL中还有其他的日期操作函数，包括添加和减去日期和时间间隔，为不同语言格式化日期，获取日期和月份的名称，或者创建新的日期值。您可以在《如何在SQL中处理日期和时间》教程中了解更多有关在SQL中处理日期的信息。您还可以参考MySQL文档中的日期和时间函数部分。

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

使用聚合函数

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

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

  • AVG：计算值的平均值。
  • COUNT：计算值的数量。
  • MAX：获取最大值。
  • MIN：获取最小值。
  • SUM：计算所有值的总和。

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

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

这个语句返回以下的输出结果。

输出

这是文章《如何在SQL中使用函数》的第6部分（共6部分）。

+-------+-----------+-----------+
| 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语言的不同概念以及处理方法，我们鼓励您查看“如何使用SQL”系列中的其他指南。

bannerAds