作者选择了 多样性在技术基金作为 写给捐款计划的一部分接受捐款。
介绍
许多数据库根据其含义和背景在不同表中分布信息. 通常,当获取数据库中存储的信息时,您需要一次参考多个表。
Structured Query Language (SQL)提供了从不同的表中获取数据的多种方法,例如 set operations。 更具体地说,在大多数 关系数据库系统中广泛支持 set operator `UNION)。
在本指南中,您将使用UNION操作同时从多个表中获取数据,然后将结果合并。
前提条件
要遵循本指南,您需要运行基于 SQL 的关系数据库管理系统(RDBMS)的计算机。
- 运行 Ubuntu 20.04 的服务器,具有非 root 用户的管理权限和与 UFW 配置的防火墙,如我们在 [Ubuntu 20.04 的初始服务器设置指南] 所描述的。
- MySQL 安装并保护在服务器上,如在 [Ubuntu 20.04 上如何安装 MySQL] 所描述的。
<$>[注] 注: 请注意,许多RDBMS使用自己的独特的SQL实现,虽然本教程中描述的命令将在大多数RDBMS上工作,并且是标准SQL语法的一部分,但如果您在MySQL以外的系统上测试它们,精确的语法或输出可能会有所不同。
您还需要一个数据库,其中有一些表载有样本数据,以便您可以练习使用 UNION 操作. 我们鼓励您通过以下部分, 连接到 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)
要选择书店数据库,请运行以下使用语句:
1USE bookstore;
您将获得以下输出:
1[secondary_label Output]
2Database changed
选择数据库后,您可以在数据库中创建示例表. 为本指南的目的,您将使用提供书籍购买和租赁的想象书店. 两种服务分别进行管理;因此,关于购买和租赁的数据存储在单独的表中。
<$>[注] **注:**本示例的数据库方案为教育目的而简化。在现实情况下,表结构会更为复杂,涉及 主键和 外钥。
第一个表,即book_buyes,将包含有关购买的书籍和购买的客户的数据。
此列将成为表的主要密钥,每个值将成为其相应行的唯一标识符。
customer_name: 此列将包含客户的名称,以最多 30 个字符的varchar数据类型表示。book_title: 此列将包含使用最多 200 个字符的varchar数据类型表示的购买书的标题。
使用以下命令创建示例表:
1CREATE TABLE book_purchases (
2 purchase_id int,
3 customer_name varchar(30),
4 book_title varchar(40),
5 date date,
6 PRIMARY KEY (purchase_id)
7);
如果下列输出打印,则创建了第一个表:
1[secondary_label Output]
2Query OK, 0 rows affected (0.00 sec)
第二个表将被称为book_leases,并将存储有关借来的书籍的信息。它与前一种表格类似,但租赁的特点是两个不同的日期:租赁日期和租赁期限。
lease_id:本列包含租赁标识符,由int数据类型表示。本列将成为表的主要密钥,每个值将成为其相应行的唯一标识符。customer_name:本列将包含使用varchar数据类型表示的客户名称,最多为 30 个字符。book_title:本列将包含使用varchar数据类型表示的借记书的标题,最多为 200 个字符。
使用以下命令创建第二个表:
1CREATE TABLE book_leases (
2 lease_id int,
3 customer_name varchar(30),
4 book_title varchar(40),
5 date_from date,
6 date_to date,
7 PRIMARY KEY (lease_id)
8);
下面的输出证实了第二个表的创建:
1[secondary_label Output]
2Query OK, 0 rows affected (0.00 sec)
接下来,通过运行以下INSERT INTO操作来加载一些样本数据的购买表:
1INSERT INTO book_purchases
2VALUES
3(1, 'sammy', 'The Picture of Dorian Gray', '2022-10-01'),
4(2, 'sammy', 'Pride and Prejudice', '2022-10-04'),
5(3, 'sammy', 'The Time Machine', '2022-09-23'),
6(4, 'bill', 'Frankenstein', '2022-07-23'),
7(5, 'bill', 'The Adventures of Huckleberry Finn', '2022-10-01'),
8(6, 'walt', 'The Picture of Dorian Gray', '2022-04-15'),
9(7, 'walt', 'Frankenstein', '2022-10-13'),
10(8, 'walt', 'Pride and Prejudice', '2022-10-19');
INSERT INTO 操作将为 book_purchases 表添加指定的值的 8 个购买,以下输出表示已添加所有 8 行:
1[secondary_label Output]
2Query OK, 8 rows affected (0.00 sec)
3Records: 8 Duplicates: 0 Warnings: 0
然后将一些样本数据插入到book_leases表中:
1INSERT INTO book_leases
2VALUES
3(1, 'sammy', 'Frankenstein', '2022-09-14', '2022-11-14'),
4(2, 'sammy', 'Pride and Prejudice', '2022-10-01', '2022-12-31'),
5(3, 'sammy', 'The Adventures of Huckleberry Finn', '2022-10-01', '2022-12-01'),
6(4, 'bill', 'The Picture of Dorian Gray', '2022-09-03', '2022-09-18'),
7(5, 'bill', 'Crime and Punishment', '2022-09-27', '2022-12-05'),
8(6, 'kim', 'The Picture of Dorian Gray', '2022-10-01', '2022-11-15'),
9(7, 'kim', 'Pride and Prejudice', '2022-09-08', '2022-11-17'),
10(8, 'kim', 'The Time Machine', '2022-09-04', '2022-10-23');
您将收到以下输出,确认已添加样本数据:
1[secondary_label Output]
2Query OK, 8 rows affected (0.00 sec)
3Records: 8 Duplicates: 0 Warnings: 0
租赁和购买涉及类似的客户和书籍标题,这将有助于展示联盟运营者的行为。
有了它,您可以随时遵循本指南的其余部分,并开始在 SQL 中使用UNION操作。
理解UNION操作员语法
SQL中的UNION运算符告诉数据库将通过单个SELECT查询(LINK0)获取的两个单独的结果集合到一个包含从两个查询返回的行的结果集。
<$>[注]
注: 数据库不会限制与UNION一起使用的SELECT查询的复杂性。 数据检索查询可能包括JOIN陈述(https://andsky.com/tech/tutorials/how-to-use-joins-in-sql),`agregations`(https://andsky.com/tech/tutorials/how-to-use-mathematical-expressions-in-sql)或subqueries(https://andsky.com/tech/tutorials/how-to-use-nested-queries)。 通常,UNION用于合并复杂陈述的结果。 为了教育目的,本指南中的示例将使用SELECT查询来关注UNION操作员的行为。
下面的示例显示包含UNION运算符的 SQL 语句的通用语法:
1SELECT column1, column2 FROM table1
2UNION
3SELECT column1, column2 FROM table2;
此 SQL 片段始于一个 SELECT 语句,从 table1 返回两个列,然后是 UNION 操作员和第二个 SELECT 语句。 第二个 SELECT 查询也返回两个列,但来自 table2。 UNION 关键字告诉数据库采取前面的和接下来的查询,单独执行它们,然后将他们的结果组合成一个。 整个代码片段,包括两个 SELECT 查询和它们之间的 UNION 关键字,是一个单一的 SQL 语句。
举个例子,假设您想要列出所有购买或租赁书籍的客户. 购买的记录存储在book_purchases表中,而租赁记录存储在book_leases表中。
1SELECT customer_name FROM book_purchases
2UNION
3SELECT customer_name FROM book_leases;
以下是这个查询的结果:
1[secondary_label Output]
2+---------------+
3| customer_name |
4+---------------+
5| sammy |
6| bill |
7| walt |
8| kim |
9+---------------+
104 rows in set (0.000 sec)
此结果表明Sammy,Bill,Walt和Kim在某个时候购买或租用书籍. 要了解该结果集是如何生成的,请尝试单独执行两个SELECT陈述:一次用于购买和一次用于租赁。
运行以下查询以返回购买书籍的客户:
1SELECT customer_name FROM book_purchases;
以下输出将打印到屏幕上:
1[secondary_label Output]
2+---------------+
3| customer_name |
4+---------------+
5| sammy |
6| sammy |
7| sammy |
8| bill |
9| bill |
10| walt |
11| walt |
12| walt |
13+---------------+
148 rows in set (0.000 sec)
塞米,比尔和沃尔特买了书,但金没有。
接下来,运行查询以返回租借书籍的客户:
1SELECT customer_name FROM book_leases;
以下输出将打印到屏幕上:
1[secondary_label Output]
2+---------------+
3| customer_name |
4+---------------+
5| sammy |
6| sammy |
7| sammy |
8| bill |
9| bill |
10| kim |
11| kim |
12| kim |
13+---------------+
148 rows in set (0.000 sec)
租赁表指的是Sammy,Bill和Kim,但沃尔特从不借书籍。
使用UNION和单独执行两个查询之间的重要区别在于UNION除了合并结果之外,还删除重复值:结果中没有重复客户名。
若要使用UNION来正确合并两个单独查询的结果,两个查询都应该以相同的格式返回结果. 有些差异会导致数据库引擎错误,而其他则会产生不符合查询的意图的结果。
考虑以下两个例子:
** 与不匹配列计数的UNION**
尝试在返回单个列的SELECT语句和返回两个列的另一个语句之间执行UNION:
1SELECT purchase_id, customer_name FROM book_purchases
2UNION
3SELECT customer_name FROM book_leases;
数据库服务器会响应一个错误:
1[secondary_label Output]
2The used SELECT statements have a different number of columns
在具有不同列计数的结果集上执行UNION操作是不可能的。
** UNION 与不匹配列顺序**
尝试在两个返回相同值但以不同的顺序的SELECT语句之间执行UNION:
1SELECT customer_name, book_title FROM book_purchases
2UNION
3SELECT book_title, customer_name FROM book_leases;
数据库服务器不会返回错误,但结果集不会正确:
1[secondary_label Output]
2+------------------------------------+------------------------------------+
3| customer_name | book_title |
4+------------------------------------+------------------------------------+
5| sammy | The Picture of Dorian Gray |
6| sammy | Pride and Prejudice |
7| sammy | The Time Machine |
8| bill | Frankenstein |
9| bill | The Adventures of Huckleberry Finn |
10| walt | The Picture of Dorian Gray |
11| walt | Frankenstein |
12| walt | Pride and Prejudice |
13| Frankenstein | sammy |
14| Pride and Prejudice | sammy |
15| The Adventures of Huckleberry Finn | sammy |
16| The Picture of Dorian Gray | bill |
17| Crime and Punishment | bill |
18| The Picture of Dorian Gray | kim |
19| Pride and Prejudice | kim |
20| The Time Machine | kim |
21+------------------------------------+------------------------------------+
2216 rows in set (0.000 sec)
在本示例中,UNION操作将第一个查询的第一列与第二个查询的第一列合并,并对第二个查询执行相同操作,将客户名和书籍标题混合在一起。
使用WHERE条款并与UNION一起订购
在上面的示例中,您合并了代表两个相应表中的所有行的结果集. 通常,您需要在合并结果之前过滤行。
假设你想知道Sammy在你的书店的帮助下阅读哪些书籍,无论是通过购买还是租赁。
1SELECT book_title FROM book_purchases
2WHERE customer_name = 'Sammy'
3UNION
4SELECT book_title FROM book_leases
5WHERE customer_name = 'Sammy';
两个SELECT查询都包含WHERE条款,从两个单独的表中过滤行,以便仅包含Sammy的购买和租赁。
1[secondary_label Output]
2+------------------------------------+
3| book_title |
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.000 sec)
再一次,UNION确保结果列表中不会有重复件。您可以使用WHERE条款来限制在SELECT查询中返回的行或仅返回其中一个条款。
通过联合操作返回的结果不遵循任何特定的顺序. 要更改这个顺序,您可以使用订单按条款(https://andsky.com/tech/tutorials/how-to-use-groupby-and-orderby-in-sql)。
要按字母顺序排序书籍的标题,在查找Sammy购买或租赁的所有书籍的列表后,请执行以下查询:
1SELECT book_title FROM book_purchases
2WHERE customer_name = 'Sammy'
3UNION
4SELECT book_title FROM book_leases
5WHERE customer_name = 'Sammy'
6ORDER BY book_title;
以下输出将打印到屏幕上:
1[secondary_label Output]
2+------------------------------------+
3| book_title |
4+------------------------------------+
5| Frankenstein |
6| Pride and Prejudice |
7| The Adventures of Huckleberry Finn |
8| The Picture of Dorian Gray |
9| The Time Machine |
10+------------------------------------+
115 rows in set (0.001 sec)
这次,结果将以基于包含两个SELECT查询的合并结果的book_title列的顺序返回。
使用UNION ALL来保留重复件
正如之前的示例所示,UNION操作器会自动从结果中删除重复的行,但有时这种行为不是您预期或打算通过查询实现的行为,例如,假设您对2022年10月1日购买或租用的书籍感兴趣。
1SELECT book_title FROM book_purchases
2WHERE date = '2022-10-01'
3UNION
4SELECT book_title FROM book_leases
5WHERE date_from = '2022-10-01'
6ORDER BY book_title;
您将获得以下结果:
1[secondary_label Output]
2+------------------------------------+
3| book_title |
4+------------------------------------+
5| Pride and Prejudice |
6| The Adventures of Huckleberry Finn |
7| The Picture of Dorian Gray |
8+------------------------------------+
93 rows in set (0.001 sec)
返回的书籍标题是正确的,但结果不会告诉你这些书是否只购买,仅租赁,或两者之间。在某些书籍既购买和租赁的情况下,他们的标题将出现在买书和租书表中。
幸运的是,SQL 有方法可以改变这种行为并保留重复的行. 您可以使用UNION ALL运算器将两个查询的结果合并而不删除重复的行。
运行相同的查询,但将UNION更改为UNION ALL:
1SELECT book_title FROM book_purchases
2WHERE date = '2022-10-01'
3UNION ALL
4SELECT book_title FROM book_leases
5WHERE date_from = '2022-10-01'
6ORDER BY book_title;
这一次,结果的列表将更长:
1[secondary_label Output]
2+------------------------------------+
3| book_title |
4+------------------------------------+
5| Pride and Prejudice |
6| The Adventures of Huckleberry Finn |
7| The Adventures of Huckleberry Finn |
8| The Picture of Dorian Gray |
9| The Picture of Dorian Gray |
10+------------------------------------+
115 rows in set (0.000 sec)
兩本書──『Huckleberry Finn的冒險』和『Dorian Gray的畫面』──在結果集中出現兩次,這意味著這些題目出現在『book_purchases』和『book_leases』表中。
根据您想要删除或保留复制品,您可以选择UNION和UNION ALL操作员,这些操作员可以互换使用。
<$>[注]
**注:**执行UNION ALL比执行UNION更快,因为数据库不需要扫描对重复的结果。
结论
通过遵循本指南,您使用UNION和UNION ALL操作从多个表中获取了数据,您还使用了WHERE条款来过滤结果,并使用ORDER BY条款来排序结果。
虽然这里包含的命令应该适用于大多数关系数据库,但请注意,每个SQL数据库都使用其独特的语言实现(有关这些差异的更多信息,请参阅我们的指南,SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems)。
如果您想了解更多有关 SQL 语言的不同概念并使用它的工作,我们鼓励您查看 如何使用 SQL 系列中的其他指南。