使用 PostgreSQL 建立你的資料庫

PostgreSQL 是最靈活的資料庫之一，並且它是開源的。

資料庫是以一種有組織且靈活的方式存儲信息的工具。電子表格在本質上就是一個資料庫，但是圖形化應用程序這一限制使得大多數的電子表格應用程序對程序員毫無用處。隨著 邊緣計算 和物聯網設備成為重要的平台，開發者們需要更有效且輕量級的方法，來存儲、處理、查詢大量的數據。我最愛的一種組合是使用 Lua 連接 PostgreSQL 資料庫。無論你使用什麼編程語言，PostgreSQL 一定是資料庫的絕佳選擇，但是在使用 PostgreSQL 之前，首先你需要知道一些基本的東西。

安裝 PostgreSQL

在 Linux 上安裝 PostgreSQL，要使用你的軟體庫。在 Fedora，CentOS，Megeia 等類似的 Linux 版本上使用命令：

$ sudo dnf install postgresql postgresql-server

在 Debian， Linux Mint， Elementary 等類似的 Linux 版本上使用命令：

$ sudo apt install postgresql postgresql-contrib

在 macOs 和 Windows 上，可以從官網 postgresql.org 下載安裝包。

配置 PostgreSQL

大多數發行版安裝 PostgreSQL 資料庫時沒有啟動它，但是為你提供了一個腳本或 systemd 服務，能夠可靠地啟動 PostgreSQL。但是，在啟動 PostgreSQL 之前，必須創建一個資料庫集群。

Fedora

在 Fedora，CentOS 等類似的版本上，PostgreSQL 安裝包中提供了一個 PostgreSQL 配置腳本。運行這個腳本，可以進行簡單地配置：

$ 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

在基於 Debian 的發行版上，在安裝 Postgres 的過程中，配置會通過 apt 自動完成。

其他版本

最後，如果你是在其他版本上運行的，那麼你可以直接使用 PostgreSQL 提供的一些工具。initdb 命令會創建一個資料庫集群，但是這個命令必須在 postgres 用戶下運行，你可以使用 sudo 來暫時地成為 postgres 用戶：

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

運行 PostgreSQL

現在，資料庫集群已經存在了，使用 initdb 的輸出中提供給你的命令或者使用 systemd 啟動 PostgreSQL 伺服器：

$ sudo systemctl start postgresql

創建一個資料庫用戶

使用 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 是很有用的，它可以避免破壞現有的表。

在你創建一個表之前，想想看你希望這個表包含哪一種數據（在 SQL 術語中稱為「數據類型」）。在這個例子中，我創建了一個表，包含兩列，有唯一標識符的一列和最多九個字元的可變長的一列。

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

關鍵字 SERIAL 並不是一個數據類型。SERIAL 是 PostgreSQL 中的一個特殊的標記，它可以創建一個自動遞增的整數欄位。關鍵字 VARCHAR 是一個數據類型，表示限制內字元數的可變字元。在此例中，我指定了最多 9 個字元。PostgreSQL 中有很多數據類型，因此請參閱項目文檔以獲取選項列表。

插入數據

你可以使用 INSERT 語句來給你的新表插入一些樣本數據：

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）的文件，並將文件另存為 Downloads 文件夾中的 land-cover.csv。

在文本編輯器或電子表格應用程序中瀏覽數據，來了解有哪些列，以及每列包含哪些類型的數據。仔細查看數據，並留意錯誤情況。例如，COU 列指的是國家代碼，例如 AUS 表示澳大利亞和 GRC 表示希臘，在奇怪的 BRIICS 之前，這一列的值通常是 3 個字元。

在你理解了這些數據項後，你就可以準備一個 PostgreSQL 資料庫了。

$ createdb landcoverdb --owner bogus
$ psql --user bogus landcoverdb
landcoverdb=> 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

插入了 22113 條記錄。這是一個很好的開始！

查詢數據

用 SELECT 語句可以查詢這 22113 條記錄的所有列，此外 PostgreSQL 將輸出通過管道傳輸到屏幕上，因此你可以輕鬆地滾動滑鼠來查看輸出的結果。更進一步，你可以使用高級 SQL 語句，來獲得一些有用的視圖。

landcoverdb=> SELECT
    lcm.country_name,
    lcm.year_value,
    SUM(lcm.value) sum_value
FROM land_cover lcm
JOIN (
    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 lcmyv
ON
    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_value
GROUP BY lcm.country_name,
    lcm.large_subnational_region_name,
    lcm.small_subnational_region_name,
    lcm.year_value
ORDER 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 以不同的方式查詢它，以便能夠獲得有關該數據的新視角。PostgreSQL 也能與許多語言集成，包括 Python、Lua、Groovy、Java 等，因此無論你使用什麼工具集，你都可以充分利用好這個出色的資料庫。

via: https://opensource.com/article/22/9/drop-your-database-for-postgresql

作者：Seth Kenlon 選題：lkxed 譯者：chai001125 校對：wxy

本文由 LCTT 原創編譯，Linux中國 榮譽推出

本文轉載來自 Linux 中國: https://github.com/Linux-CN/archive