介绍
数据库是许多网站和应用程序的关键组成部分,并且是数据存储和交换在互联网上的核心。数据库管理的最重要的方面之一是从数据库中获取数据的做法,无论是在具体基础上还是在应用程序中编码的过程的一部分。
在关系数据库管理系统中, query 是用于从表中获取数据的任何命令. 在结构化查询语言(SQL)中,查询几乎总是使用SELECT语句进行。
在本指南中,我们将讨论 SQL 查询的基本语法以及一些更常用的函数和操作器,我们还将练习使用 PostgreSQL 数据库中的某些样本数据来创建 SQL 查询。
PostgreSQL,常被缩写为Postgres,是一种面向对象的方法的关系数据库管理系统,这意味着信息可以在PostgreSQL方案中作为对象或类表示。
前提条件
一般来说,本指南中介绍的命令和概念可以在运行任何SQL数据库软件的任何基于Linux的操作系统上使用,但它是专门用运行PostgreSQL的Ubuntu 18.04服务器编写的。
- 具有 sudo 特权的非 root 用户的 Ubuntu 18.04 机器,可以使用我们的 Ubuntu 18.04 初始服务器设置指南设置。
- 在机器上安装了 PostgreSQL. 有关此设置的帮助,请遵循我们关于 Ubuntu 18.04 上如何安装和使用 PostgreSQL 的指南的
安装 PostgreSQL部分 。
有了这个设置,我们可以开始教程。
创建一个样本数据库
在我们开始在 SQL 中创建查询之前,我们首先会创建一个数据库和几个表,然后将这些表填充一些样本数据,这将允许您在稍后开始创建查询时获得一些实用经验。
对于我们将在本指南中使用的样本数据库,想象下面的场景:
你和你的几个朋友都一起庆祝生日,每一次,团体成员都前往当地的球场,参加一个友好的比赛,然后每个人都前往你的地方,在那里你准备生日人最喜欢的食物。
现在这个传统已经持续了一段时间,你已经决定开始跟踪这些锦标赛的记录。 此外,为了让计划晚餐更容易,你决定创建朋友的生日和他们最喜欢的入场日,派对和甜点的记录。
首先,打开一个 PostgreSQL 提示作为您的 postgres 超级用户:
1sudo -u postgres psql
<$>[注] 注: 如果您遵循了在 Installing PostgreSQL on Ubuntu 18.04上的前提教程中的所有步骤,您可能已为您的 PostgreSQL 安装配置了一个新的角色。
1sudo -u sammy psql
美元
接下来,通过运行创建数据库:
1CREATE DATABASE birthdays;
然后通过键入选择此数据库:
1\c birthdays
接下来,在这个数据库中创建两个表. 我们将使用第一个表来跟踪你的朋友在球场上的记录. 下面的命令将创建一个名为比赛的表,其中包含每个朋友的名字的列,他们赢得的锦标赛数量(胜利),他们所有时间的最佳分数,以及他们穿的球鞋尺寸(尺寸):
1CREATE TABLE tourneys (
2name varchar(30),
3wins real,
4best real,
5size real
6);
一旦运行CREATE TABLE命令并填充列标题,您将收到以下输出:
1[secondary_label Output]
2CREATE TABLE
用一些样本数据填充巡回赛表:
1INSERT INTO tourneys (name, wins, best, size)
2VALUES ('Dolly', '7', '245', '8.5'),
3('Etta', '4', '283', '9'),
4('Irma', '9', '266', '7'),
5('Barbara', '2', '197', '7.5'),
6('Gladys', '13', '273', '8');
您将收到以下输出:
1[secondary_label Output]
2INSERT 0 5
然后,在同一个数据库中创建另一个表,我们将使用它来存储有关朋友最喜欢的生日餐的信息。下面的命令创建了一个名为晚餐的表,其中包含每个朋友的名字,他们的生日期,他们最喜欢的入场,他们最喜欢的侧面菜肴和他们最喜欢的甜点的列:
1CREATE TABLE dinners (
2name varchar(30),
3birthdate date,
4entree varchar(30),
5side varchar(30),
6dessert varchar(30)
7);
同样,对于此表,您将收到验证表创建的反馈:
1[secondary_label Output]
2CREATE TABLE
将此表填充到一些样本数据中:
1INSERT INTO dinners (name, birthdate, entree, side, dessert)
2VALUES ('Dolly', '1946-01-19', 'steak', 'salad', 'cake'),
3('Etta', '1938-01-25', 'chicken', 'fries', 'ice cream'),
4('Irma', '1941-02-18', 'tofu', 'fries', 'cake'),
5('Barbara', '1948-12-25', 'tofu', 'salad', 'ice cream'),
6('Gladys', '1944-05-28', 'steak', 'fries', 'ice cream');
1[secondary_label Output]
2INSERT 0 5
一旦该命令成功完成,您将完成设置您的数据库,接下来,我们将讨论SELECT查询的基本命令结构。
了解 SELECT 声明
正如介绍中提到的,SQL 查询几乎总是以SELECT语句开始。SELECT在查询中用于指定结果集中应该返回表中的哪个列。
一般来说,SQL 查询会遵循以下语法:
1SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply;
例如,以下语句将从晚餐表中返回整个名称列:
1SELECT name FROM dinners;
1[secondary_label Output]
2 name
3---------
4 Dolly
5 Etta
6 Irma
7 Barbara
8 Gladys
9(5 rows)
您可以从相同的表中选择多个列,通过将它们的名称分开,如下:
1SELECT name, birthdate FROM dinners;
1[secondary_label Output]
2 name | birthdate
3---------+------------
4 Dolly | 1946-01-19
5 Etta | 1938-01-25
6 Irma | 1941-02-18
7 Barbara | 1948-12-25
8 Gladys | 1944-05-28
9(5 rows)
取而不是命名特定列或列组,您可以跟随SELECT运算符,用星座(`*)作为表中的所有列的位置。
1SELECT * FROM tourneys;
1[secondary_label Output]
2 name | wins | best | size
3---------+------+------+------
4 Dolly | 7 | 245 | 8.5
5 Etta | 4 | 283 | 9
6 Irma | 9 | 266 | 7
7 Barbara | 2 | 197 | 7.5
8 Gladys | 13 | 273 | 8
9(5 rows)
在查询中,使用WHERE来过滤符合特定条件的记录,并且从结果中删除不符合该条件的行。
1. . . WHERE column_name comparison_operator value
在WHERE条款中的比较运算符定义了指定的列应该如何与值进行比较,以下是一些常见的SQL比较运算符:
运算器 所做的 <<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
例如,如果你想找到伊尔玛的鞋子尺寸,你可以使用以下查询:
1SELECT size FROM tourneys WHERE name = 'Irma';
1[secondary_label Output]
2 size
3------
4 7
5(1 row)
SQL 允许使用 wildcard 字符,这些字符在使用WHERE条款时特别有用。百分比字符(%)代表零或更多的未知字符,而突出字符(_)代表一个单一的未知字符。这些字符是有用的,如果您试图在表中找到特定条目,但不确定具体的条目是什么。
1SELECT entree FROM dinners WHERE entree LIKE 't%';
1[secondary_label Output]
2 entree
3-------
4 tofu
5 tofu
6(2 rows)
基于上面的输出,我们看到我们忘记的输入是豆腐。
在某些情况下,您可以使用具有相对长或难以读取名称的列或表的数据库进行工作。在这些情况下,您可以通过创建使用AS关键字的名称来使这些名称更易读。
1SELECT name AS n, birthdate AS b, dessert AS d FROM dinners;
1[secondary_label Output]
2 n | b | d
3---------+------------+-----------
4 Dolly | 1946-01-19 | cake
5 Etta | 1938-01-25 | ice cream
6 Irma | 1941-02-18 | cake
7 Barbara | 1948-12-25 | ice cream
8 Gladys | 1944-05-28 | ice cream
9(5 rows)
在这里,我们告诉 SQL 将名称列显示为n,出生日期列显示为b,甜点列显示为d。
到目前为止,我们已经通过的示例包括 SQL 查询中更常用的关键字和条款. 这些用于基本查询,但如果您试图基于数据执行计算或衍生一个 _scalar 值(单个值,而不是一组多个不同的值)时,它们并不有用。
合并功能
通常情况下,在处理数据时,您不一定想要看到数据本身,而是想要信息 about 数据 SQL 语法包含一系列函数,允许您通过发出一个SELECT查询来解释或运行数据上的计算。
数函数计算并返回符合特定标准的行数,例如,如果你想知道你的朋友在生日礼物中喜欢多少豆腐,你可以发出这个查询:
1SELECT COUNT(entree) FROM dinners WHERE entree = 'tofu';
1[secondary_label Output]
2 count
3-------
4 2
5(1 row)
AVG函数返回列的平均值. 使用我们的示例表,您可以使用此查询找到您的朋友中平均最佳分数:
1SELECT AVG(best) FROM tourneys;
1[secondary_label Output]
2 avg
3-------
4 252.8
5(1 row)
例如,如果你想看到你和你的朋友多年来积累了多少游戏,你可以运行此查询:
1SELECT SUM(wins) FROM tourneys;
1[secondary_label Output]
2 sum
3-----
4 35
5(1 row)
请注意,AVG和SUM函数只有在使用数值数据时才会正确工作,如果你尝试将它们用于非数值数据,则会导致错误或仅仅是0,取决于你正在使用的RDBMS:
1SELECT SUM(entree) FROM dinners;
1[secondary_label Output]
2ERROR: function sum(character varying) does not exist
3LINE 1: select sum(entree) from dinners;
4 ^
5HINT: No function matches the given name and argument types. You might need to add explicit type casts.
MIN用于查找指定的列中最小的值. 您可以使用此查询来查看迄今为止最糟糕的球场总记录(以胜利数量为准):
1SELECT MIN(wins) FROM tourneys;
1[secondary_label Output]
2 min
3-----
4 2
5(1 row)
类似地,MAX用于在特定列中找到最大的数字值,下面的查询将显示最佳的总体球场记录:
1SELECT MAX(wins) FROM tourneys;
1[secondary_label Output]
2 max
3-----
4 13
5(1 row)
与SUM和AVG不同,可以将MIN和MAX函数用于数字和字母数据类型。
1SELECT MIN(name) FROM dinners;
1[secondary_label Output]
2 min
3---------
4 Barbara
5(1 row)
同样,在包含字符串值的列上运行时,MAX函数将以字母形式显示最后一个值:
1SELECT MAX(name) FROM dinners;
1[secondary_label Output]
2 max
3------
4 Irma
5(1 row)
聚合函数的用途远远超出本节所描述的用途,当与下一节所涵盖的GROUP BY条款使用时,它们特别有用,以及影响结果集分类的方式的其他几个查询条款。
操纵 Query 输出
除了从和哪里条款外,还有一些其他条款用于操纵选择查询的结果,在本节中,我们将解释并提供一些更常用的查询条款的示例。
除了从和哪里之外,最常用的查询条款之一是GROUP BY条款,通常用于在一个列上执行汇总函数,但与另一个列中的匹配值有关。
例如,假设你想知道你的朋友中有多少人喜欢你做的三个输入,你可以通过以下查询找到这个信息:
1SELECT COUNT(name), entree FROM dinners GROUP BY entree;
1[secondary_label Output]
2 count | entree
3-------+---------
4 1 | chicken
5 2 | steak
6 2 | tofu
7(3 rows)
使用命令按条款来排序查询结果. 默认情况下,数字值以上升顺序排序,文本值以字母顺序排序。
1SELECT name, birthdate FROM dinners ORDER BY birthdate;
1[secondary_label Output]
2 name | birthdate
3---------+------------
4 Etta | 1938-01-25
5 Irma | 1941-02-18
6 Gladys | 1944-05-28
7 Dolly | 1946-01-19
8 Barbara | 1948-12-25
9(5 rows)
请注意,Orders by 的默认行为是将以上升顺序设置的结果排序. 若要逆转此操作并将结果排序以下降顺序,请用 DESC 关闭查询:
1SELECT name, birthdate FROM dinners ORDER BY birthdate DESC;
1[secondary_label Output]
2 name | birthdate
3---------+------------
4 Barbara | 1948-12-25
5 Dolly | 1946-01-19
6 Gladys | 1944-05-28
7 Irma | 1941-02-18
8 Etta | 1938-01-25
9(5 rows)
如前所述,WHERE条款用于过滤基于特定条件的结果,但是,如果您使用WHERE条款与集合函数,则会返回错误,如下所示,试图查找至少三位朋友最喜欢的页面:
1SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3;
1[secondary_label Output]
2ERROR: aggregate functions are not allowed in WHERE
3LINE 1: SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3...
「HAVING」条款被添加到 SQL 中,以提供类似于「WHERE」条款的功能,同时也兼容集合函数。 将这两个条款之间的区别认为是「WHERE」适用于单个记录,而「HAVING」适用于群集记录,有助于考虑到每次您发布「HAVING」条款时,「GROUP BY」条款也必须存在。
下面的示例是另一个尝试找出哪种侧菜是至少三个朋友的喜爱,尽管这一个会返回一个结果,没有错误:
1SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3;
1[secondary_label Output]
2 count | side
3-------+-------
4 3 | fries
5(1 row)
聚合函数对概括特定列在特定表中的结果有用,但是,有许多情况需要查询多个表的内容。
想要多张桌子
更常见的是,数据库包含多个表,每个表包含不同的数据集. SQL 提供了在多个表上运行单个查询的几种不同的方法。
可以使用JOIN条款将两个或多个表的行组合到查询结果中,通过在表之间找到相关列,并在输出中对结果进行适当的排序。
包含JOIN条款的SELECT陈述通常遵循以下语法:
1SELECT table1.column1, table2.column2
2FROM table1
3JOIN table2 ON table1.related_column=table2.related_column;
请注意,因为JOIN条款比较了多个表的内容,前面的示例指定了从哪个表选择每个列,以表的名称和期限为前列的名称。
想象一下,你想给每个朋友买一双篮球鞋作为生日礼物,因为有关朋友的生日和鞋子尺寸的信息存储在单独的表中,你可以单独查询两个表,然后比较每个表的结果。
1SELECT tourneys.name, tourneys.size, dinners.birthdate
2FROM tourneys
3JOIN dinners ON tourneys.name=dinners.name;
1[secondary_label Output]
2 name | size | birthdate
3---------+------+------------
4 Dolly | 8.5 | 1946-01-19
5 Etta | 9 | 1938-01-25
6 Irma | 7 | 1941-02-18
7 Barbara | 7.5 | 1948-12-25
8 Gladys | 8 | 1944-05-28
9(5 rows)
在本示例中使用的JOIN条款,没有其他参数,是一个 innerJOIN条款,这意味着它选择了两个表中具有匹配值的所有记录,并将其打印到结果集中,而不匹配的任何记录都被排除在外。
1INSERT INTO tourneys (name, wins, best, size)
2VALUES ('Bettye', '0', '193', '9');
1INSERT INTO dinners (name, birthdate, entree, side, dessert)
2VALUES ('Lesley', '1946-05-02', 'steak', 'salad', 'ice cream');
然后,重新运行以前的SELECT声明与JOIN条款:
1SELECT tourneys.name, tourneys.size, dinners.birthdate
2FROM tourneys
3JOIN dinners ON tourneys.name=dinners.name;
1[secondary_label Output]
2 name | size | birthdate
3---------+------+------------
4 Dolly | 8.5 | 1946-01-19
5 Etta | 9 | 1938-01-25
6 Irma | 7 | 1941-02-18
7 Barbara | 7.5 | 1948-12-25
8 Gladys | 8 | 1944-05-28
9(5 rows)
请注意,由于巡回赛表没有Lesley的输入,而晚餐表没有Bettye的输入,所以这些记录不在这个输出中。
然而,可以使用一个 outerJOIN条款返回其中一个表的所有记录,外部JOIN条款写作为LEFT JOIN,RIGHT JOIN或FULL JOIN。
LEFT JOIN条款返回了左表中的所有记录,并仅返回了来自右表的匹配记录。在外部合并的背景下,左表是由FROM条款引用的表,右表是JOIN声明之后引用的任何其他表。
再次运行前一个查询,但这次使用一个LEFT JOIN条款:
1SELECT tourneys.name, tourneys.size, dinners.birthdate
2FROM tourneys
3LEFT JOIN dinners ON tourneys.name=dinners.name;
此命令将返回来自左表的每一个记录(在这种情况下,‘turneys’),即使在右表中没有相应的记录. 随时没有来自右表的匹配的记录,它将返回为空值或‘NULL’,取决于您的 RDBMS:
1[secondary_label Output]
2 name | size | birthdate
3---------+------+------------
4 Dolly | 8.5 | 1946-01-19
5 Etta | 9 | 1938-01-25
6 Irma | 7 | 1941-02-18
7 Barbara | 7.5 | 1948-12-25
8 Gladys | 8 | 1944-05-28
9 Bettye | 9 |
10(6 rows)
现在再次运行查询,这一次用RIGHT JOIN条款:
1SELECT tourneys.name, tourneys.size, dinners.birthdate
2FROM tourneys
3RIGHT JOIN dinners ON tourneys.name=dinners.name;
由于Lesley的出生日期记录在右表中,但左表中没有对应的行,因此名称和大小列将在该行中返回为空值:
1[secondary_label Output]
2 name | size | birthdate
3---------+------+------------
4 Dolly | 8.5 | 1946-01-19
5 Etta | 9 | 1938-01-25
6 Irma | 7 | 1941-02-18
7 Barbara | 7.5 | 1948-12-25
8 Gladys | 8 | 1944-05-28
9 | | 1946-05-02
10(6 rows)
请注意,左边和右边的合并可以写作LEFT OUTER JOIN或RIGHT OUTER JOIN,尽管条款的OUTER部分是暗示的。
有一个名为FULL JOIN的第四个合并条款可用于某些RDBMS分布,包括PostgreSQL。
1SELECT tourneys.name, tourneys.size, dinners.birthdate
2FROM tourneys
3FULL JOIN dinners ON tourneys.name=dinners.name;
1[secondary_label Output]
2 name | size | birthdate
3---------+------+------------
4 Dolly | 8.5 | 1946-01-19
5 Etta | 9 | 1938-01-25
6 Irma | 7 | 1941-02-18
7 Barbara | 7.5 | 1948-12-25
8 Gladys | 8 | 1944-05-28
9 Bettye | 9 |
10 | | 1946-05-02
11(7 rows)
<$>[注] 注: 截至本文,‘FULL JOIN’条款不受MySQL或MariaDB的支持。
作为使用FULL JOIN来查询多个表中的所有记录的替代方案,您可以使用UNION条款。
UNION运算器与JOIN条款略有不同:而不是将来自多个表的结果打印成单独的列,使用单个SELECT陈述,UNION将两个SELECT陈述的结果合并成一个单一的列。
为了说明,运行以下查询:
1SELECT name FROM tourneys UNION SELECT name FROM dinners;
此查询会删除任何重复的条目,这是UNION操作员的默认行为:
1[secondary_label Output]
2 name
3---------
4 Irma
5 Etta
6 Bettye
7 Gladys
8 Barbara
9 Lesley
10 Dolly
11(7 rows)
返回所有条目(包括重复)使用UNION ALL运算器:
1SELECT name FROM tourneys UNION ALL SELECT name FROM dinners;
1[secondary_label Output]
2 name
3---------
4 Dolly
5 Etta
6 Irma
7 Barbara
8 Gladys
9 Bettye
10 Dolly
11 Etta
12 Irma
13 Barbara
14 Gladys
15 Lesley
16(12 rows)
结果表中的列名和数目反映了第一个SELECT陈述所查询的列名和数目。 请注意,当使用UNION查询来自多个表的多个列时,每个SELECT陈述必须查询相同数量的列,相应的列必须具有相似的数据类型,每个SELECT陈述中的列必须以相同的顺序进行查询。
1SELECT name FROM dinners UNION SELECT name, wins FROM tourneys;
1[secondary_label Output]
2ERROR: each UNION query must have the same number of columns
3LINE 1: SELECT name FROM dinners UNION SELECT name, wins FROM tourne...
另一种查询多表的方法是使用 subqueries. 子查询(也称为 inner 或 nested queries)是包含在另一个查询中的查询,这些查询在尝试过滤查询的结果与单独集合函数的结果的情况下是有用的。
为了说明这个想法,假设你想知道你的朋友谁赢得了比芭芭拉更多的比赛,而不是询问芭芭拉赢得了多少比赛,然后运行另一个查询,看看谁赢得了比这更多的比赛,你可以用一个查询计算两者:
1SELECT name, wins FROM tourneys
2WHERE wins > (
3SELECT wins FROM tourneys WHERE name = 'Barbara'
4);
1[secondary_label Output]
2 name | wins
3--------+------
4 Dolly | 7
5 Etta | 4
6 Irma | 9
7 Gladys | 13
8(4 rows)
此声明中的子查询只运行一次;只需要在名称列中找到与Barbara相同行中的获胜列的值,并且由子查询和外部查询返回的数据是相互独立的。
此查询旨在查找你的朋友中谁赢得了比同一尺寸鞋子的玩家平均数的更多比赛:
1SELECT name, size FROM tourneys AS t
2WHERE wins > (
3SELECT AVG(wins) FROM tourneys WHERE size = t.size
4);
要完成查询,它必须首先从外部查询中收集名称和尺寸列,然后将该查询的每个行与内部查询的结果进行比较,这决定了具有相同鞋子尺寸的个人平均获胜次数。
1[secondary_label Output]
2 name | size
3------+------
4 Etta | 9
5(1 row)
如前所述,子查询可以用来查询来自多个表的结果. 用一个最后的例子来说明这一点,假设您想为该组有史以来最好的球手投一个惊喜晚餐。
1SELECT name, entree, side, dessert
2FROM dinners
3WHERE name = (SELECT name FROM tourneys
4WHERE wins = (SELECT MAX(wins) FROM tourneys));
1[secondary_label Output]
2 name | entree | side | dessert
3--------+--------+-------+-----------
4 Gladys | steak | fries | ice cream
5(1 row)
请注意,此声明不仅包含子查询,还包含该子查询中的子查询。
结论
发出查询是数据库管理领域中最常执行的任务之一. 有许多数据库管理工具,例如 phpMyAdmin或 pgAdmin,允许您执行查询并可视化结果,但从命令行发出SELECT陈述仍然是一个广泛实践的工作流,可以为您提供更大的控制权。
如果您是新手使用 SQL,我们鼓励您使用我们的 SQL Cheat Sheet作为参考,并审查 官方 PostgreSQL 文档。
- 理解 SQL 和 NoSQL 数据库和不同的数据库模型
- [如何在 Ubuntu 18.04 上设置 PostgreSQL 10 的逻辑复制(https://andsky.com/tech/tutorials/how-to-set-up-logical-replication-with-postgresql-10-on-ubuntu-18-04)
- 如何保护 PostgreSQL 免受自动攻击