介绍
结构化查询语言 (SQL) 用于管理关系数据库管理系统(RDBMS)中的数据。 SQL 中的一个有用的功能是创建查询,也称为 subquery 或 _nested 查询。
在本教程中,您将使用嵌入式查询与SELECT、INSERT和DELETE语句,您还将使用嵌入式查询中的汇总函数来比较数据值与您指定的分类数据值与WHERE和LIKE条款。
前提条件
要遵循本指南,您需要运行某种类型的关系数据库管理系统(RDBMS)的计算机,该系统使用SQL。
- 运行 Ubuntu 20.04 的服务器,具有
sudo管理权限和启用防火墙的非root用户。 按照我们的 Ubuntu 20.04 初始服务器设置来启动。 - MySQL 安装在服务器上并安全。 按照我们的 如何在 Ubuntu 20.04 上安装 MySQL指南来设置此设置。 此指南假定您还已设置了一个非 root MySQL 用户,如本指南的 步骤 3所述。
<$>[注] 注: 请注意,许多关系数据库管理系统使用自己的 SQL 独特实现,尽管本教程中描述的命令将在大多数 RDBMS 上工作,但如果您在 MySQL 以外的系统上测试它们,精确的语法或输出可能会有所不同。
要练习使用本教程中的嵌入式查询,您将需要一个数据库和表载有样本数据. 如果您没有一个准备好插入,您可以阅读以下 连接到MySQL和设置样本数据库部分,以了解如何创建数据库和表。
连接到MySQL并设置样本数据库
如果您的 SQL 数据库在远程服务器上运行,则从本地计算机输入 SSH 到您的服务器:
1[environment local]
2ssh sammy@your_server_ip
接下来,打开MySQL提示,用您的MySQL用户帐户信息代替sammy:
1mysql -u sammy -p
创建一个名为zooDB的数据库:
1CREATE DATABASE zooDB;
如果数据库创建成功,您将收到以下输出:
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
要选择zooDB数据库,运行以下USE语句:
1USE zooDB;
1[secondary_label Output]
2Database changed
选择数据库后,在其中创建一个表. 对于本教程的示例,我们将创建一个表,存储有关参观动物园的客人的信息。
贵宾-id':为参观动物园的来宾存储值,并使用int'数据类型。 这也作为表格的 [_ 主要密钥] (https://www.digitalocean.com/community/conceptual_articles/understanding-sql-constraints#primary-key) , 意思是本列中的每个值将作为各自行的独特标识符发挥作用 。 *`first_name':使用"varchar"数据类型持有每位嘉宾的首名,最多可有30个字符. *'last_name':再用"varchar"数据类型,最多30个字符,来存储每个客人的姓氏.客人_类型' : 包含每名嘉宾使用 " varchar " 数据类型的最多15个字符的嘉宾类型(成人或儿童)。 *成员_类型':代表每位嘉宾持有的会员类型,使用"varchar"数据类型最多持有30个字符. *会员资格-成本 ' :储存各类会员的费用。 本列使用"十进制"数据类型,精度为5个而比例为2个,指本列中的值可以有5个位数,并有2个位数到小数点右边. *总-访问':使用"int"数据类型来记录每个来宾的访问总数. (英语)
创建一个名为客人的表,其中包含每一个这些列,运行以下命令CREATE TABLE:
1CREATE TABLE guests (
2guest_id int,
3first_name varchar(30),
4last_name varchar(30),
5guest_type varchar(15),
6membership_type varchar(30),
7membership_cost decimal(5,2),
8total_visits int,
9PRIMARY KEY (guest_id)
10);
接下来,将一些样本数据插入空表中:
1INSERT INTO guests
2(guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)
3VALUES
4(1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),
5(2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),
6(3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),
7(4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),
8(5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),
9(6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),
10(7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),
11(8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),
12(9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),
13(10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),
14(11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173),
15(12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2),
16(13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1),
17(14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),
18(15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);
1[secondary_label Output]
2Query OK, 15 rows affected (0.01 sec)
3Records: 15 Duplicates: 0 Warnings: 0
一旦您输入了数据,您就可以开始使用 SQL 中的嵌入式查询。
使用选择的嵌入式查询
在 SQL 中,一个查询是从数据库中的表中获取数据的操作,并且总是包含一个SELECT语句。一个 nested 查询是嵌入在另一个操作中的完整查询。一个嵌入式查询可以包含常规查询中使用的所有元素,任何有效的查询都可以嵌入到另一个操作中,以成为嵌入式查询。例如,一个嵌入式查询可以嵌入到INSERT和DELETE操作中。根据操作,一个嵌入式查询应该通过将该陈述嵌入到正确的序列中,以遵循特定的顺序操作。一个嵌入式查询在你想要在一个查询陈述中执行多个命令的情况下也很有用,而不是写出多个以返回你想要的结果。
为了更好地理解嵌入式查询,让我们通过使用上一步的样本数据来说明它们如何有用,例如,假设您希望在客人表中找到访问动物园的所有客人,他们的频率高于平均数目。
1SELECT first_name, last_name, total_visits
2FROM guests
3WHERE total_visits > AVG(total_visits);
但是,使用此语法的查询会返回错误:
1[secondary_label Output]
2ERROR 1111 (HY000): Invalid use of group function
此错误的原因是,如AVG()等聚合函数(https://andsky.com/tech/tutorials/how-to-use-mathematical-expressions-in-sql#analyzing-data-with-aggregate-functions)不起作用,除非它们在SELECT条款中执行。
获取此信息的一个选项是先运行查询以查找平均访客访问次数,然后运行另一个查询以查找基于该值的结果,例如在以下两个示例中:
1SELECT AVG(total_visits) FROM guests;
1[secondary_label Output]
2+-----------------+
3| avg(total_visits) |
4+-----------------+
5| 57.5333 |
6+-----------------+
71 row in set (0.00 sec)
1SELECT first_name, last_name, total_visits
2FROM guests
3WHERE total_visits > 57.5333;
1[secondary_label Output]
2+----------+---------+------------+
3| first_name | last_name | total_visits |
4+----------+---------+------------+
5| Judy | Hopps | 168 |
6| Idris | Bogo | 79 |
7| Gideon | Grey | 100 |
8| Nangi | Reddy | 241 |
9| Calvin | Roo | 173 |
10+----------+---------+------------+
115 rows in set (0.00 sec)
但是,您可以通过在第二个查询中嵌入第一个查询(SELECT AVG(total_visits) FROM guests;)来获得单个查询的相同结果。
1SELECT first_name, last_name, total_visits
2FROM guests
3WHERE total_visits >
4(SELECT AVG(total_visits) FROM guests);
1[secondary_label Output]
2+------------+-----------+--------------+
3| first_name | last_name | total_visits |
4+------------+-----------+--------------+
5| Judy | Hopps | 168 |
6| Idris | Bogo | 79 |
7| Gideon | Grey | 100 |
8| Nangi | Reddy | 241 |
9| Calvin | Roo | 173 |
10+------------+-----------+--------------+
115 rows in set (0.00 sec)
根据这一结果,五位访客的访问量超过了平均水平,这些信息可以为思考创造性方法提供有用的见解,以确保现有会员经常访问动物园,并每年更新其会员证件。
使用插入的嵌入式查询
使用嵌入式查询,您不仅限于将其嵌入到其他SELECT陈述中,实际上,您还可以使用嵌入式查询将数据嵌入到现有表中,通过嵌入嵌入式查询到INSERT操作中。
举个例子,假设一个关联的动物园请求一些关于您的客人的信息,因为他们有兴趣向在其所在地购买居民会员资格的客人提供15%的折扣。 要做到这一点,请使用CREATE TABLE创建一个名为upgrade_guests的新表,其中包含六个列。 请注意数据类型,如int和varchar,以及他们可以持有的最大字符。 如果它们不与您在设置示例数据库部分(https://andsky.com/tech/tutorials/how-to-use-nested-queries#connecting-to-mysql-and-setting-up-a-sample-database)创建的客人表中的原始数据类型一致,那么当您尝试使用嵌入式查询输入客人表的数据时,您将收到错误,并且数据不会正确传输。 创建您的表,包含以下信息:
1CREATE TABLE upgrade_guests (
2guest_id int,
3first_name varchar(30),
4last_name varchar(30),
5membership_type varchar(30),
6membership_cost decimal(5,2),
7total_visits int,
8PRIMARY KEY (guest_id)
9);
为了保持一致性和准确性,我们保留了本表中的大多数数据类型信息与客人表相同。
在此操作中,键入INSERT INTO和新的upgrade_guests表,以便显示数据被插入位置的明确方向。
此外,将15%的折扣应用到任何居民会员中,包括数学操作*以0.85倍,在嵌入的查询声明中(membership_cost * 0.85).然后使用WHERE条款来排序在membership_type列中的值。您可以使用LIKE条款(LINK0)将其缩小到仅为居民会员会员会员会员会员会员会员会员会员会员会员会员会员的结果,并在单个引用中将居民单词前和之后的百分比%符号放入单个引文中,以选择遵循相同模式的任何会员会员会员,或者在这种情况下使用相同的语法。您的查询将如下
1INSERT INTO upgrade_guests
2SELECT guest_id, first_name, last_name, membership_type,
3(membership_cost * 0.85), total_visits
4FROM guests
5WHERE membership_type LIKE '%resident%';
1[secondary_label Output]
2Query OK, 5 rows affected, 5 warnings (0.01 sec)
3Records: 5 Duplicates: 0 Warnings: 5
输出显示,新upgrade_guests表中添加了五个记录. 若要确认您请求的数据已成功从guests表转移到您创建的空的upgrade_guests表中,并按照您用嵌入式查询和WHERE条款指定的条件运行下列操作:
1SELECT * FROM upgrade_guests;
1[secondary_label Output]
2+----------+------------+------------+-----------------------+-----------------+--------------+
3| guest_id | first_name | last_name | membership_type | membership_cost | total_visits |
4+----------+------------+------------+-----------------------+-----------------+--------------+
5| 1 | Judy | Hopps | Resident Premium Pass | 93.50 | 168 |
6| 3 | Duke | Weaselton | Resident Pass | 72.25 | 4 |
7| 6 | Jenny | Bellwether | Resident Premium Pass | 93.50 | 20 |
8| 10 | Octavia | Otterton | Resident Pass | 72.25 | 11 |
9| 11 | Calvin | Roo | Resident Premium Pass | 93.50 | 173 |
10+----------+------------+------------+-----------------------+-----------------+--------------+
115 rows in set (0.01 sec)
根据您的新upgrade_guests表的输出,来自guest表的Resident相关的客人会员信息被正确插入。此外,新的membership_cost已重新计算,应用了15%的折扣。
使用DELETE的嵌入式查询
要练习使用DELETE声明的嵌入式查询,假设您想删除任何经常访问的客人,因为您只想专注于向目前不经常访问动物园的会员推广升级的优质通票折扣。
在此情况下,从upgrade_visits表中删除数据的位置就清楚了,然后使用WHERE条款来排序任何total_visits超过嵌入式查询中指定的数量。在嵌入式嵌入式查询中,使用SELECT来找到total_visits的平均值,因此前面的WHERE条款具有相应的数据值来比较。最后,使用FROM从guests表中获取这些信息。
1DELETE FROM upgrade_guests
2WHERE total_visits >
3(SELECT AVG(total_visits) FROM guests);
1[secondary_label Output]
2Query OK, 2 rows affected (0.00 sec)
确认这些记录已成功从upgrade_guests表中删除,并使用ORDER BY以total_visits的数值和上升顺序排列结果:
<$>[注]
注: 使用DELETE语句从新表中删除记录,不会将其从原始表中删除。
1SELECT * FROM upgrade_guests ORDER BY total_visits;
1[secondary_label Output]
2+----------+------------+------------+-----------------------+-----------------+--------------+
3| guest_id | first_name | last_name | membership_type | membership_cost | total_visits |
4+----------+------------+------------+-----------------------+-----------------+--------------+
5| 3 | Duke | Weaselton | Resident Pass | 72.25 | 4 |
6| 10 | Octavia | Otterton | Resident Pass | 72.25 | 11 |
7| 6 | Jenny | Bellwether | Resident Premium Pass | 93.50 | 20 |
8+----------+------------+------------+-----------------------+-----------------+--------------+
93 rows in set (0.00 sec)
正如此输出所示,DELETE声明和嵌入式查询在删除指定的数据值方面起到了正确的作用. 此表现在为三位访问人数低于平均次数的客人提供信息,这对于动物园代表来说是一个很好的出发点,以便他们以折扣的价格升级到优质通票,并希望鼓励他们更频繁去动物园。
结论
嵌入式查询是有用的,因为它们允许您获得高度细微的结果,否则您只能通过运行单独的查询来获取。 此外,使用输入和删除嵌入式查询的陈述为您提供了在一个步骤中插入或删除数据的另一种方法。