SQL教程：在PostgreSQL上学习SQL

最新推荐文章于 2024-01-25 15:14:04 发布

cxq8989

最新推荐文章于 2024-01-25 15:14:04 发布

阅读量329

点赞数

文章标签：数据库分布式 mysql java python

原文链接：https://www.infoworld.com/article/3311438/sql-tutorial-learn-sql-on-postgresql.html

版权

PostgreSQL是一个开放源代码的对象关系（也称为扩展关系）数据库管理系统。 PostgreSQL中的现代关系数据库功能包括复杂查询，外键，触发器，可更新视图，事务完整性和多版本并发控制。用户可以使用新的数据类型，函数，运算符，聚合函数，索引方法和过程语言来扩展PostgreSQL。

PostgreSQL经过20多年的开发和部署，是一个可靠的开源数据库，在很多方面都可以与商业关系数据库匹敌。您可以将其安装在Linux（所有最新发行版），Windows（Windows 2000 SP4和更高版本），FreeBSD，OpenBSD，NetBSD，MacOS，AIX，HP / UX和Solaris上。您还可以在Amazon Aurora中找到托管的PostgreSQL高性能版本，并在CockroachDB中找到与有线兼容的分布式实现。

从这里开始，我将按照惯例将PostgreSQL和Postgres（不是全大写）名称互换使用。我可以发音 Postgres；我永远不知道该说Post-gres-QL还是Post-gres-sequel。

在本教程中，我们将逐步安装PostgreSQL，连接到数据库，加载一些数据以及运行各种SQL查询。为了解决这个问题，我们将使用SQL join子句针对两个相关表执行查询。在此过程中，我将为您可能要使用的Postgres建议一些GUI客户端，并简要概述有用的PostgreSQL扩展。最后，我将为您提供一些很好的参考，以帮助您使用Postgres和SQL进行下一步。

安装PostgreSQL

我的InfoWorld同事Serdar Yegulalp写了一篇有关安装和配置PostgreSQL出色教程，“ PostgreSQL 10入门 ”。我用它来通知我在iMac上的安装，并结束了下载和运行下面的屏幕快照中所示的仅限Mac的Postgres.App 。

除了使用默认配置设置安装最新的生产数据库版本（10.5），我还将Postgres命令行实用程序添加到计算机的路径中，并安装了跨平台的pgAdmin 4和仅限Mac的Postico GUI工具，如下所示。

postgres cli utils — 将Postgres的命令行实用程序添加到您的路径很有用。

Postico是用于MacOS的商业Postgres客户端产品。免费试用有限制，但可以使用并且不会过期。

由于我拥有有效的JetBrains订阅，因此我还安装了DataGrip ，这是一个多数据库客户端工具。如果在Windows上进行此练习，则可能会在Alpha Anywhere中使用可视化SQL查询生成器和SQL命令行。

连接到PostgreSQL数据库

要连接到Postgres数据库的关键要领是主机，主机分配的端口，最初要使用的数据库以及用户名和密码。其中一些具有众所周知的默认值。

如果将Postgres与客户端安装在同一台计算机上，则主机将为localhost。默认情况下，将有两个可见的数据库，一个名为postgres，一个使用您的用户名。除非已更改（通常在数据库启动时），否则端口将为5432。

上面的信息与您使用的客户端无关。对于大多数目的，psql命令行在一开始就可以正常工作。稍后，当您尝试使用复杂的联接构造查询时，您可能希望使用图形客户端，主要是为了节省大量时间和易于出错的键入。

如果数据库使用默认值并且在使用的计算机上，则要与psql连接，请确保数据库正在运行，然后在命令行中键入psql <enter> ：

Martins-iMac:~ mheller$ psql
psql (10.5)
Type "help" for help.

相反，如果看到一条错误消息，表明未找到该命令，则可以将Postgres命令行实用程序添加到路径中，然后重新启动Shell，或者完全限定可执行文件的位置。

运行psql后，如果键入help <enter> 您会看到以下内容：

mheller=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
mheller=#

提示会告诉您正在使用什么数据库。您可以使用\conninfo了解更多\conninfo ，并使用\c更改连接：

mheller=# \conninfo
You are connected to database "mheller" as user "mheller" via socket in "/tmp" at port "5432".
From the command help ( \? ):
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
                 connect to new database (currently "mheller")

让我们尝试更改数据库：

mheller=# \c postgres
You are now connected to database "postgres" as user "mheller".
postgres=#

现在让我们切换回数据库mheller：

postgres=# \c mheller
You are now connected to database "mheller" as user "mheller".
mheller=#

您的默认数据库和用户名将与我的不同。

要执行SQL命令和查询，请在查询完成后添加分号（ ; ），然后按<enter>键。在这里，我们创建一个新数据库，连接到该数据库，再连接回我们的默认数据库，最后删除新数据库：

mheller=# create database test;
CREATE DATABASE
mheller=# \c test
You are now connected to database "test" as user "mheller".
test=# \c mheller
You are now connected to database "mheller" as user "mheller".
mheller=# drop database test
mheller-# ;
DROP DATABASE
mheller=#

数据库创建和删除也可以从系统外壳中完成。相关的命令行实用程序createdb和dropdb 。

将数据加载到PostgreSQL数据库中

数据库设计是一门艺术，而不是一门科学，需要大量的经验才能正确。即使是专业的数据库设计人员，也经常需要随着时间的推移修改其设计以匹配应用程序数据使用模式，例如，通过为常见查询添加索引，或通过添加外键约束来强制引用完整性。

不用花费数周（或数月）来学习数据库设计，而是加载一个已经具有Postgres格式的库存数据库。我想使用PgFoundry示例数据库之一，但是，该站点不再处于活动状态。

一种替代方法是使用PostgreSQL Exercises网站上的三表数据库。这很简单，但是我将其留给您以后再处理，因为您可以在线进行所有练习，并且希望您现在在本地使用数据库。 SportsDB和DVD Rental（Pagila）示例很有趣，但是对于第一次复习来说有点太复杂了。

相反，我希望您打开https://github.com/pthom/northwind_psql并克隆或下载并解压缩存储库。 Northwind最初是Microsoft的示例，但GitHub用户pthom已将其改编为Postgres。我之所以喜欢Northwind，主要是因为我曾经使用它来向开发人员讲授SQL，并逐渐熟悉了数据。数据库图如下：

像这样的实体关系图来自工具。该图像看起来很像是由SQL Server Management Studio生成的。本质上，矩形是表格，列显示在矩形内，主键和外键用键图标标记。表之间的连接是外键约束。

您可以使用create_db脚本将数据库加载到PostgreSQL中，假设您使用的是Bash shell。如果您在Windows上运行，请尝试将create_db.sh重命名为create_db.cmd并删除脚本的第一行。

在克隆或解压缩northwind_psql的目录中打开一个Shell，然后运行脚本。成功的数据库加载将如下所示：

Martins-iMac:northwind_psql mheller$ ./create_db.sh
NOTICE:  database "northwind" does not exist, skipping
NOTICE:  role "northwind_user" does not exist, skipping
SET
SET
SET
SET
SET
SET
SET
SET
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
…
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE ROLE
ALTER ROLE
GRANT
GRANT

现在我们可以尝试查询数据库。

在PostgreSQL中运行SQL查询

首先，让我们尝试查看一个表。从命令行对northwind数据库运行psql 。您的默认用户名可能会起作用；如果不是，请使用凭据用户northwind_user和密码thewindisblowing 。

数据库中定义了哪些区域？ select查询应如下所示。我从上图获得了表名。

northwind=# select * from region;
region_id | region_description
-----------+--------------------
         1 | Eastern
         2 | Western
         3 | Northern
         4 | Southern
(4 rows)

那很简单。星号表示返回表中的所有字段。假设我们只是想按字母顺序查看区域名称。这需要指定所需的字段名称（我们从上一个查询中获知的名称），并添加一个order by子句。默认是升序排列。

northwind=# select region_description from region order by region_description;
 region_description
--------------------
 Eastern
 Northern
 Southern
 Western
(4 rows)

现在让我们看一下产品类别：

northwind=# select * from categories;
 category_id | category_name  |                        description                         | picture
-------------+----------------+------------------------------------------------------------+---------
           1 | Beverages      | Soft drinks, coffees, teas, beers, and ales                | \x
           2 | Condiments     | Sweet and savory sauces, relishes, spreads, and seasonings | \x
           3 | Confections    | Desserts, candies, and sweet breads                        | \x
           4 | Dairy Products | Cheeses                                                    | \x
           5 | Grains/Cereals | Breads, crackers, pasta, and cereal                        | \x
           6 | Meat/Poultry   | Prepared meats                                             | \x
           7 | Produce        | Dried fruit and bean curd                                  | \x
           8 | Seafood        | Seaweed and fish                                           | \x
(8 rows)

让我们看一下刚才看到的具有category_id=1的饮料产品。这需要一个where子句。

northwind=# select * from products where category_id=1;
 product_id |       product_name        | supplier_id | category_id |  quantity_per_unit  | unit_price | units_in_stock | units_on_order | reorder_level | discontinued
------------+---------------------------+-------------+-------------+---------------------+------------+----------------+----------------+---------------+--------------
          1 | Chai                      |           8 |           1 | 10 boxes x 30 bags  |         18 |             39 |              0 |            10 |            1
          2 | Chang                     |           1 |           1 | 24 - 12 oz bottles  |         19 |             17 |             40 |            25 |            1
         24 | Guaraná Fantástica        |          10 |           1 | 12 - 355 ml cans    |        4.5 |             20 |              0 |             0 |            1
         34 | Sasquatch Ale             |          16 |           1 | 24 - 12 oz bottles  |         14 |            111 |              0 |            15 |            0
         35 | Steeleye Stout            |          16 |           1 | 24 - 12 oz bottles  |         18 |             20 |              0 |            15 |            0
         38 | Côte de Blaye             |          18 |           1 | 12 - 75 cl bottles  |      263.5 |             17 |              0 |            15 |            0
         39 | Chartreuse verte          |          18 |           1 | 750 cc per bottle   |         18 |             69 |              0 |             5 |            0
         43 | Ipoh Coffee               |          20 |           1 | 16 - 500 g tins     |         46 |             17 |             10 |            25 |            0
         67 | Laughing Lumberjack Lager |          16 |           1 | 24 - 12 oz bottles  |         14 |             52 |              0 |            10 |            0
         70 | Outback Lager             |           7 |           1 | 24 - 355 ml bottles |         15 |             15 |             10 |            30 |            0
         75 | Rhönbräu Klosterbier      |          12 |           1 | 24 - 0.5 l bottles  |       7.75 |            125 |              0 |            25 |            0
         76 | Lakkalikööri              |          23 |           1 | 500 ml              |         18 |             57 |              0 |            20 |            0
(12 rows)

employees中有很多领域，所以让我们来看一下其中的一些领域。在这里，我们按两个字段进行排序，分别是title和last_name 。

翻译自: https://www.infoworld.com/article/3311438/sql-tutorial-learn-sql-on-postgresql.html

cxq8989

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL教程：在PostgreSQL上学习SQL

PostgreSQL是一个开放源代码的对象关系（也称为扩展关系）数据库管理系统。 PostgreSQL中的现代关系数据库功能包括复杂查询，外键，触发器，可更新视图，事务完整性和多版本并发控制。用户可以使用新的数据类型，函数，运算符，聚合函数，索引方法和过程语言来扩展PostgreSQL。 PostgreSQL经过20多年的开发和部署，是一个可靠的开源数据库，在很多方面都可以与商业关系数据...
复制链接

扫一扫