学习PostgreSQL后可以放弃你的数据库

今天你吸猫了么
210 阅读6分钟

Postgres是目前最灵活的数据库之一，而且它是开源的。

数据库是以一种有组织但灵活的方式存储信息的工具。电子表格本质上是一个数据库，但由于图形化应用的限制，大多数电子表格应用对程序员来说毫无用处。随着边缘和物联网设备成为重要的目标平台，开发者需要强大但轻量级的解决方案来存储、处理和查询大量的数据。我最喜欢的组合之一是PostgreSQL数据库和Lua绑定，但可能性是无穷的。无论你使用什么语言，Postgres都是数据库的最佳选择，但在采用它之前，你需要了解一些基本知识。

安装Postgres

要在Linux上安装PostgreSQL，请使用你的软件库。在Fedora、CentOS、Mageia和类似的地方。

$ sudo dnf install postgresql postgresql-server

在 Debian, Linux Mint, Elementary, 和类似系统上。

$ sudo apt install postgresql postgresql-contrib

在macOS和Windows上，从postgresql.org下载一个安装程序。

设置Postgres

大多数发行版在安装Postgres数据库时并不启动它，而是为你提供一个脚本或systemd服务来帮助它可靠地启动。然而，在你启动Postgres之前，你必须创建一个数据库集群。

Fedora

在Fedora、CentOS或类似系统上，Postgres软件包中提供了一个Postgres设置脚本。运行这个脚本可以轻松配置。

$ sudo /usr/bin/postgresql-setup --initdb[sudo] password:
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

蝶变

在基于Debian的发行版上，安装时由apt 自动进行设置。

其他一切

最后，如果你正在运行其他东西，那么你可以直接使用Postgres本身提供的工具链。initdb 命令创建了一个数据库集群，但你必须以postgres 用户的身份运行它，你可以用sudo 暂时假定这个身份。

$ sudo -u postgres \
"initdb -D /var/lib/pgsql/data \
--locale en_US.UTF-8 --auth md5 --pwprompt"

启动Postgres

现在集群已经存在，使用initdb 输出中提供的命令或使用systemd来启动Postgres服务器。

$ sudo systemctl start postgresql

创建一个数据库用户

要创建一个Postgres用户，使用createuser 命令。postgres 用户是安装Postgres的超级用户。

$ sudo -u postgres createuser --interactive --password bogus
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
Password:

创建一个数据库

要创建一个新的数据库，使用createdb 命令。在这个例子中，我创建了数据库exampledb ，并把它的所有权分配给用户bogus 。

$ createdb exampledb --owner bogus

与PostgreSQL交互

你可以使用psql 命令与PostgreSQL数据库交互。这个命令提供了一个交互式的外壳，所以你可以查看和更新你的数据库。要连接到一个数据库，指定你要使用的用户和数据库。

$ psql --user bogus exampledb
psql (XX.Y)
Type "help" for help.exampledb=>

创建一个表

数据库包含表，它可以被看作是一个电子表格。有一系列的行（在数据库中称为记录）和列。一行和一列的交集被称为字段。

结构化查询语言（SQL）是以其提供的内容命名的。一种以可预测和一致的语法查询数据库内容的方法，以获得有用的结果。

目前，你的数据库是空的，没有任何表。你可以用CREATE 查询来创建一个表。把它和IF NOT EXISTS 语句结合起来是很有用的，它可以防止PostgreSQL把现有的表弄坏。

在你创建一个表之前，考虑一下你希望这个表包含什么样的数据（SQL术语中的 "数据类型"）。在这个例子中，我创建了一个表，其中一列是唯一的标识符，另一列是最多九个字符的任意文本。

exampledb=> CREATE TABLE IF NOT EXISTS my_sample_table(
exampledb(> id SERIAL,
exampledb(> wordlist VARCHAR(9) NOT NULL);

SERIAL 这个关键词实际上不是一个数据类型。它是PostgreSQL中的特殊符号，可以创建一个自动递增的整数字段。VARCHAR 关键字是一种数据类型，表示一个限制内的可变字符数。在这段代码中，我指定了一个最大的9个字符。在PostgreSQL中有很多数据类型，所以请参考项目文档中的选项列表。

插入数据

你可以通过使用INSERT SQL关键字，用一些样本数据填充你的新表。

exampledb=> INSERT INTO my_sample_table (wordlist) VALUES ('Alice');
INSERT 0 1

如果你试图在wordlist 字段中输入超过9个字符，你的数据输入将失败。

exampledb=> INSERT INTO my_sample_table (WORDLIST) VALUES ('Alexandria');
ERROR:  VALUE too long FOR TYPE CHARACTER VARYING(9)

改变一个表或列

当你需要改变一个字段定义时，你可以使用ALTER SQL关键字。例如，如果你决定为wordlist ，9个字符的限制，你可以通过设置它的数据类型来增加它的容量。

exampledb=> ALTER TABLE my_sample_table
ALTER COLUMN wordlist SET DATA TYPE VARCHAR(10);
ALTER TABLE
exampledb=> INSERT INTO my_sample_table (WORDLIST) VALUES ('Alexandria');
INSERT 0 1

查看表中的数据

SQL是一种查询语言，所以你通过查询来查看数据库的内容。查询可以是简单的，也可以是在几个不同的表之间连接复杂的关系。要查看一个表中的所有内容，请在* （星号是通配符）上使用SELECT 关键字。

exampledb=> SELECT * FROM my_sample_table;
 id |  wordlist
\----+------------
  1 | Alice
  2 | Bob
  3 | Alexandria(3 ROWS)

更多的数据

PostgreSQL可以处理大量的数据，但是和任何数据库一样，成功的关键在于你如何设计你的数据库进行存储，以及你在存储了数据之后如何处理这些数据。在OECD.org上可以找到一个相对较大的公共数据集，利用这个数据你可以尝试一些高级数据库技术。

首先，将数据下载为逗号分隔的值（CSV），并将文件保存为land-cover.csv ，放在你的Downloads 。

在文本编辑器或电子表格应用程序中浏览数据，以了解有哪些列，以及每一列包含什么样的数据。仔细观察数据，留意明显规则的例外情况。例如，COU 列，包含国家代码，如澳大利亚的AUS 和希腊的GRC ，往往是 3 个字符，直到奇特的BRIICS 。

一旦你了解了你正在处理的数据，你就可以准备一个Postgres数据库。

$ createdb landcoverdb --owner bogus
$ psql --user bogus landcoverdblandcoverdb=> create table land_cover(
country_code varchar(6),
country_name varchar(76),
small_subnational_region_code varchar(5),
small_subnational_region_name varchar(14),
large_subnational_region_code varchar(17),
large_subnational_region_name varchar(44),
measure_code varchar(13),
measure_name varchar(29),
land_cover_class_code varchar(17),
land_cover_class_name varchar(19),
year_code integer,
year_value integer,
unit_code varchar(3),
unit_name varchar(17),
power_code integer,
power_name varchar(9),
reference_period_code varchar(1),
reference_period_name varchar(1),
value float(8),
flag_codes varchar(1),
flag_names varchar(1));

导入数据

Postgres可以使用特殊的元命令\copy ，直接导入CSV数据。

landcoverdb=> \copy land_cover from '~/land-cover.csv' with csv header delimiter ','
COPY 22113

查询数据

一个广泛的SELECT 语句来查看所有22,113条记录的所有列是可能的，而且Postgres非常好地将输出管道到一个屏幕寻呼机，所以你可以悠闲地滚动浏览输出。然而，使用高级SQL，你可以得到一些有用的视图，这些视图本来是一些非常原始的数据。

landcoverdb=> SELECT
    lcm.country_name,
    lcm.year_value,
    SUM(lcm.value) sum_valueFROM land_cover lcmJOIN (
    SELECT
        country_name,
        large_subnational_region_name,
        small_subnational_region_name,
        MAX(year_value) max_year_value
    FROM land_cover
    GROUP BY country_name,
        large_subnational_region_name,
        small_subnational_region_name) AS lcmyvON
    lcm.country_name = lcmyv.country_name AND
    lcm.large_subnational_region_name = lcmyv.large_subnational_region_name AND
    lcm.small_subnational_region_name = lcmyv.small_subnational_region_name AND
    lcm.year_value = lcmyv.max_year_valueGROUP BY lcm.country_name,
    lcm.large_subnational_region_name,
    lcm.small_subnational_region_name,
    lcm.year_valueORDER BY country_name,
    year_value;

下面是一些输出的例子。

\---------------+------------+------------
 Afghanistan    |       2019 |  743.48425
 Albania        |       2019 |  128.82532
 Algeria        |       2019 |  2417.3281
 American Samoa |       2019 |   100.2007
 Andorra        |       2019 |  100.45613
 Angola         |       2019 |  1354.2192
 Anguilla       |       2019 | 100.078514
 Antarctica     |       2019 |  12561.907
[...]

SQL是一种丰富的语言，所以它超出了本文的范围。阅读一下SQL代码，看看你是否可以修改它以提供一组不同的数据。

开放的数据库

PostgreSQL是伟大的开放源代码数据库之一。有了它，你可以为结构化数据设计存储库，然后用SQL以不同的方式查看，这样你就可以获得关于这些数据的新观点。Postgres集成了许多语言，包括Python、Lua、Groovy、Java等等，所以无论你的工具集是什么，你都可能利用这个优秀的数据库。

avatar
文章
阅读
粉丝