介绍
结构化查询语言 (SQL) 数据库可以存储和管理大量数据在众多表中. 使用大数据集,了解如何排序数据很重要,特别是用于分析结果集或为报告或外部通信组织数据。
SQL 中有两种常见的语句有助于对数据进行分类是GROUP BY和ORDER BY。一个GROUP BY语句通过根据您在查询中指定的列(s)来分类数据,并与 聚合函数一起使用。
在本教程中,您将使用GROUP BY和ORDER BY语句在SQL中排序查询结果,您还将练习在查询中执行汇总函数和WHERE条款,以进一步排序结果。
前提条件
要遵循本指南,您需要运行某种类型的关系数据库管理系统(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
创建一个名为movieDB的数据库:
1CREATE DATABASE movieDB;
如果数据库创建成功,您将收到以下输出:
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
要选择movieDB数据库,运行以下USE语句:
1USE movieDB;
1[secondary_label Output]
2Database changed
选择数据库后,在其中创建一个表. 对于本教程的示例,我们将创建一个表,存储有关当地电影院的节目信息。
剧院': 存储每个剧场放映室的int`数据类型值,并将作为表格的 [prime key (https://www.digitalocean.com/community/conceptual_articles/understanding-sql-constraints#primary-key ),意思是本列中的每个值将作为各自行的唯一标识符。- " 日期 " :使用 " DATE " 数据类型,按年份、月份和放映电影之日存储具体日期。 该数据类型坚持以下参数:当年四位数,月份和一天最多为两位数("YYYY-MM-DD"). *'时间':代表电影预定以"TIME"的数据类型以小时,分,秒("HH:MM:SS")显示. *'movie_name':使用"varchar"数据类型存储电影名,最多可有40个字符. *'movie_genre':使用最多为30个字符的"varchar"数据类型,保存每部电影各自流派的信息.
客人-总计' : 显示参加 " int " 数据类型电影放映的嘉宾总数。 *ticket_cost': 使用"小数"数据类型,精度为4个而比例为1个,指本列中的数值可以有4个位数,而小数点右侧有2个位数. 本栏代表特定电影上映的票价. (英语)
通过运行以下命令,创建一个名为movie_theater的表,其中包含每个列:
1CREATE TABLE movie_theater (
2theater_id int,
3date DATE,
4time TIME,
5movie_name varchar(40),
6movie_genre varchar(30),
7guest_total int,
8ticket_cost decimal(4,2),
9PRIMARY KEY (theater_id)
10);
接下来,将一些样本数据插入空表中:
1INSERT INTO movie_theater
2(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
3VALUES
4(1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),
5(2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),
6(3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
7(4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),
8(5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),
9(6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),
10(7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
11(8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),
12(9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),
13(10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),
14(11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
15(12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);
1[secondary_label Output]
2Query OK, 12 rows affected (0.00 sec)
3Records: 12 Duplicates: 0 Warnings: 0
一旦您输入了数据,您就可以开始在 SQL 中对查询结果进行排序。
使用Group By
一个GROUP BY语句的功能是将记录与共享值组合起来。一个GROUP BY语句总是与查询中的集合函数一起使用。正如您可能记得的那样,一个集合函数汇总信息并返回单个结果,例如,您可以查询列的总数或总数,从而在结果中产生单个值。
「GROUP BY」有助于返回您所指定的组(s)所排序的多个所需结果,而不是单独一个列。 此外,如果您选择使用一个,「GROUP BY」和聚合函数的查询必须始终在「FROM」陈述和「WHERE」条款之后出现。
1[label GROUP BY syntax]
2SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;
为了说明您如何使用GROUP BY声明,假设您正在领导多个电影发布的活动,并希望评估您的营销努力的成功。您要求当地剧院在周五和周六分享他们从客人那里收集的数据。
1SELECT * FROM movie_theater;
1[secondary_label Output]
2+------------+------------+----------+-------------------------+-------------+-------------+-------------+
3| theater_id | date | time | movie_name | movie_genre | guest_total | ticket_cost |
4+------------+------------+----------+-------------------------+-------------+-------------+-------------+
5| 1 | 2022-05-27 | 10:00:00 | Top Gun Maverick | Action | 131 | 18.00 |
6| 2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama | 90 | 18.00 |
7| 3 | 2022-05-27 | 10:00:00 | Men | Horror | 100 | 18.00 |
8| 4 | 2022-05-27 | 10:00:00 | The Bad Guys | Animation | 83 | 18.00 |
9| 5 | 2022-05-28 | 09:00:00 | Top Gun Maverick | Action | 112 | 8.00 |
10| 6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama | 137 | 8.00 |
11| 7 | 2022-05-28 | 09:00:00 | Men | Horror | 25 | 8.00 |
12| 8 | 2022-05-28 | 09:00:00 | The Bad Guys | Animation | 142 | 8.00 |
13| 9 | 2022-05-28 | 05:00:00 | Top Gun Maverick | Action | 150 | 13.00 |
14| 10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama | 118 | 13.00 |
15| 11 | 2022-05-28 | 05:00:00 | Men | Horror | 88 | 13.00 |
16| 12 | 2022-05-28 | 05:00:00 | The Bad Guys | Animation | 130 | 13.00 |
17+------------+------------+----------+-------------------------+-------------+-------------+-------------+
1812 rows in set (0.00 sec)
虽然这些数据是有用的,但您想要进行更深入的评估,并将结果分类为某些特定列。
因为你在几种不同类型的电影中工作过,所以你很想知道他们是如何被电影观众接收的。 具体来说,你想知道每种类型的电影观众的平均数量。 使用SELECT从movie_genre列中获取各种类型的电影。 然后在guest_total列中应用累计函数AVG,使用AS创建一个名为average的字符串,并将GROUP BY声明添加到movie_genre组成结果中。 这样组合将为你提供每个电影类型的平均结果:
1SELECT movie_genre, AVG(guest_total) AS average
2FROM movie_theater
3GROUP BY movie_genre;
1[secondary_label Output]
2+-------------+----------+
3| movie_genre | average |
4+-------------+----------+
5| Action | 131.0000 |
6| Drama | 115.0000 |
7| Horror | 71.0000 |
8| Animation | 118.3333 |
9+-------------+----------+
104 rows in set (0.00 sec)
此输出为movie_genre组中的每个类别提供了四个平均值. 基于此信息,Action电影吸引了每场演出的平均客人次数最高。
接下来,假设您想在两天内测量剧院的收入。下面的查询会返回来自日期列的值,以及由总数组合函数返回的值。 具体来说,组合函数总数将包含一个数学方程式,以使(使用*运算器)乘以票价的客人总数,表示为:总数(guest_total * ticket_cost)。 此查询包含AS条款,以便为组合函数返回的列提供代号总数_收入。
1SELECT date, SUM(guest_total * ticket_cost)
2AS total_revenue
3FROM movie_theater
4GROUP BY date;
1[secondary_label Output]
2+------------+---------------+
3| date | total_revenue |
4+------------+---------------+
5| 2022-05-27 | 7272.00 |
6| 2022-05-28 | 9646.00 |
7+------------+---------------+
82 rows in set (0.00 sec)
由于您使用GROUP BY来组合日期列,因此您的输出为每一天的票价销售总收入提供了结果,在这种情况下,5月27日星期五的7272美元和5月28日的星期六的9646美元。
现在想象一下,你想集中精力分析一个电影:《坏男孩》 在这种情况下,你想弄清楚时间和价格点如何影响家庭观看动画电影的选择。 对于这个查询,使用MAX汇总函数来检索最大票价,确保包含AS来创建price_data列的代名词。 然后,使用WHERE条款将结果缩小为movie_name到The Bad Guys,并使用AND来确定基于guest_total数字的最受欢迎的电影时间,这些数字超过了100个,然后用GROUP BY声明完成查询,并将其组合为时间:
1SELECT time, MAX(ticket_cost) AS price_data
2FROM movie_theater
3WHERE movie_name = "The Bad Guys"
4AND guest_total > 100
5GROUP BY time;
1[secondary_label Output]
2+----------+------------+
3| time | price_data |
4+----------+------------+
5| 09:00:00 | 8.00 |
6| 05:00:00 | 13.00 |
7+----------+------------+
82 rows in set (0.00 sec)
根据这一结果,更多客人参加了早晨9点的坏男孩电影,这意味着每张门票价格更便宜,每张门票价格为8美元,但这些结果也显示,电影客人在下午5点支付了较高的13点的门票价格,这表明家庭更喜欢在白天不太晚的节目,并且会为门票付出更高的费用。这似乎是一个公平的估计,与下午10点相比,当坏男孩电影只有83名客人,每张门票的价格为18美元时,这可能是有益的信息,以提供电影院经理的证据,即打开更多的早晨和早晚时间插槽可以增加基于偏好时间和价格点的家庭的参与率。
請注意,儘管「GROUP BY」幾乎總是用於集合函數,但可能會有例外,儘管不太可能。但是,如果您想要組合您的結果而沒有組合函數,您可以使用「DISTINCT」聲明來達到相同的結果。一個「DISTINCT」條款會通過返回列中的獨特值來刪除任何重複的結果,並且只能用「SELECT」聲明來組合。
1SELECT DISTINCT movie_name FROM movie_theater;
1[secondary_label Output]
2+-------------------------+
3| movie_name |
4+-------------------------+
5| Top Gun Maverick |
6| Downton Abbey A New Era |
7| Men |
8| The Bad Guys |
9+-------------------------+
104 rows in set (0.00 sec)
正如你从查看表中的所有数据中记得的那样,由于有多个显示,电影名称出现了重复,因此DISTINCT删除了这些重复,并在单一列movie_name下有效地组合了独特值。
1SELECT movie_name FROM movie_theater GROUP BY movie_name;
现在你已经练习了使用GROUP BY的汇总函数,接下来你将学习如何使用ORDER BY语句对查询结果进行排序。
使用命令
按查询中所指定的列(s)来排序结果以上升或下降顺序。 根据您指定后列存储的数据类型,Ordin BY 将以字母或数字顺序排序结果。 默认情况下,Ordin BY 将以上升或下降顺序排序结果; 但是,如果您更喜欢下降顺序,您必须在查询中包含关键字DESC。 您还可以使用Ordin BY命令与GROUP BY命令,但它必须以正确的方式运行。 类似于GROUP BY,Ordin BY也必须在FROM命令和WERE条款之后出现。 使用ERORDIN BY的通用语法如下:
1[label ORDER BY syntax]
2SELECT column_1, column_2 FROM table ORDER BY column_1;
让我们继续使用电影院的样本数据,并练习使用订单按来排序结果,开始使用以下查询,该查询从客人_总列中获取值,并以订单按语句来排序这些数值:
1SELECT guest_total FROM movie_theater
2ORDER BY guest_total;
1[secondary_label Output]
2+-------------+
3| guest_total |
4+-------------+
5| 25 |
6| 83 |
7| 88 |
8| 90 |
9| 100 |
10| 112 |
11| 118 |
12| 130 |
13| 131 |
14| 137 |
15| 142 |
16| 150 |
17+-------------+
1812 rows in set (0.00 sec)
由于您的查询指定了含有数值的列,因此订单按陈述将结果按数值和上升顺序排序,从客人总数列下方开始为25。
如果你更喜欢以下行顺序排序列,你会添加DESC关键字在查询的末尾。此外,如果你想按字符值排序数据在movie_name下,你会指定在你的查询中。让我们使用ORDER BY来执行这个类型的查询,以排序movie_name列的字符值,以下行顺序排序。将结果更进一步排序,包括一个WHERE条款,以从时间列中获取显示在上午10点的电影的数据:
1SELECT movie_name FROM movie_theater
2WHERE time = '10:00:00'
3ORDER BY movie_name DESC;
1[secondary_label Output]
2+-------------------------+
3| movie_name |
4+-------------------------+
5| Top Gun Maverick |
6| The Bad Guys |
7| Men |
8| Downton Abbey A New Era |
9+-------------------------+
104 rows in set (0.01 sec)
这个结果集列出了下午10点的四个不同的电影节目,按字母顺序下降,从Top Gun Maverick到Downtown Abbey A New Era。
对于下一个查询,将订单按和组按语句与总数函数相结合,以生成每部电影所收到的总收入的结果。
在此查询中,请使用SUM并在每个电影中添加额外的12位客人,通过执行加+的操作员,然后将12添加到guest_total中。 请确保将此数字列为组合。 然后,将此总数以ticket_cost倍增到*操作员,并完成数学方程式,通过在结尾上关闭组合。 添加AS条款以创建名为total_revenue的新列的代名词。 然后,使用GROUP BY将每个电影的total_revenue结果按从moviename_name列中获取的数据组合在一起。 最后,使用ERORD BY在新列total_revenue下组织结果以
1SELECT movie_name, SUM((guest_total + 12) * ticket_cost)
2AS total_revenue
3FROM movie_theater
4GROUP BY movie_name
5ORDER BY total_revenue;
1[secondary_label Output]
2+-------------------------+---------------+
3| movie_name | total_revenue |
4+-------------------------+---------------+
5| Men | 3612.00 |
6| Downton Abbey A New Era | 4718.00 |
7| The Bad Guys | 4788.00 |
8| Top Gun Maverick | 5672.00 |
9+-------------------------+---------------+
104 rows in set (0.00 sec)
这个结果集告诉我们每部电影的总收入,加上额外的12张客票销售,并组织了总票销售以从最低到最高的上升顺序。 从此,我们了解到Top Gun Maverick获得了最多的票销售,而男性获得了最少的。
在本节中,您练习了实施订单按陈述的各种方法,以及如何指定您喜欢的顺序,例如对于字符和数值的上升和下降顺序,您还了解了如何包括WHERE条款来缩小结果,并使用GROUP BY和ORDER BY陈述执行查询,使用累积函数和数学方程式。
结论
了解如何使用GROUP BY和ORDER BY语句对于分类结果和数据至关重要,无论您是否希望在一个组中组织多个结果,以字母顺序和下降顺序组织您的一个列,或者同时使用两者;这取决于您和您想要的结果(s)。