3 種使用 PostgreSQL 命令的方式
在 PostgreSQL 入門一文中, 我解釋了如何安裝、設置和開始使用這個開源資料庫軟體。不過,使用 PostgreSQL 中的命令可以做更多事情。
例如,我使用 Postgres 來跟蹤我的雜貨店購物清單。我的大多數雜貨店購物是在家裡進行的,而且每周進行一次大批量的採購。我去幾個不同的地方購買清單上的東西,因為每家商店都提供特定的選品或質量,亦或更好的價格。最初,我製作了一個 HTML 表單頁面來管理我的購物清單,但這樣無法保存我的輸入內容。因此,在想到要購買的物品時我必須馬上列出全部清單,然後到採購時我常常會忘記一些我需要或想要的東西。
相反,使用 PostgreSQL,當我想到需要的物品時,我可以隨時輸入,並在購物前列印出來。你也可以這樣做。
創建一個簡單的購物清單
首先,輸入 psql
命令進入資料庫,然後用下面的命令創建一個表:
Create table groc (item varchar(20), comment varchar(10));
輸入如下命令在清單中加入商品:
insert into groc values ('milk', 'K');
insert into groc values ('bananas', 'KW');
括弧中有兩個信息(逗號隔開):前面是你需要買的東西,後面字母代表你要購買的地點以及哪些東西是你每周通常都要買的(W
)。
因為 psql
有歷史記錄,你可以按向上鍵在括弧內編輯信息,而無需輸入商品的整行信息。
在輸入一小部分商品後,輸入下面命令來檢查前面的輸入內容。
Select * from groc order by comment;
item | comment
----------------+------ ground coffee | H
butter | K
chips | K
steak | K
milk | K
bananas | KW
raisin bran | KW
raclette | L
goat cheese | L
onion | P
oranges | P
potatoes | P
spinach | PW
broccoli | PW
asparagus | PW
cucumber | PW
sugarsnap peas | PW
salmon | S
(18 rows)
此命令按 comment
列對結果進行排序,以便按購買地點對商品進行分組,從而使你的購物更加方便。
使用 W
來指明你每周要買的東西,當你要清除表單為下周的列表做準備時,你可以將每周的商品保留在購物清單上。輸入:
delete from groc where comment not like '%W';
注意,在 PostgreSQL 中 %
表示通配符(而非星號)。所以,要保存輸入內容,需要輸入:
delete from groc where item like 'goat%';
不能使用 item = 'goat%'
,這樣沒用。
在購物時,用以下命令輸出清單並列印或發送到你的手機:
o groclist.txt
select * from groc order by comment;
o
最後一個命令 o
後面沒有任何內容,將重置輸出到命令行。否則,所有的輸出會繼續輸出到你創建的雜貨店購物文件 groclist.txt
中。
分析複雜的表
這個逐項列表對於數據量小的表來說沒有問題,但是對於數據量大的表呢?幾年前,我幫 FreieFarbe.de 的團隊從 HLC 調色板中創建一個自由色的色樣冊。事實上,任何能想像到的列印色都可按色調、亮度、濃度(飽和度)來規定。最終結果是 HLC Color Atlas,下面是我們如何實現的。
該團隊向我發送了具有顏色規範的文件,因此我可以編寫可與 Scribus 配合使用的 Python 腳本,以輕鬆生成色樣冊。一個例子像這樣開始:
HLC, C, M, Y, K
H010_L15_C010, 0.5, 49.1, 0.1, 84.5
H010_L15_C020, 0.0, 79.7, 15.1, 78.9
H010_L25_C010, 6.1, 38.3, 0.0, 72.5
H010_L25_C020, 0.0, 61.8, 10.6, 67.9
H010_L25_C030, 0.0, 79.5, 18.5, 62.7
H010_L25_C040, 0.4, 94.2, 17.3, 56.5
H010_L25_C050, 0.0, 100.0, 15.1, 50.6
H010_L35_C010, 6.1, 32.1, 0.0, 61.8
H010_L35_C020, 0.0, 51.7, 8.4, 57.5
H010_L35_C030, 0.0, 68.5, 17.1, 52.5
H010_L35_C040, 0.0, 81.2, 22.0, 46.2
H010_L35_C050, 0.0, 91.9, 20.4, 39.3
H010_L35_C060, 0.1, 100.0, 17.3, 31.5
H010_L45_C010, 4.3, 27.4, 0.1, 51.3
這與原始數據相比,稍有修改,原始數據用製表符分隔。我將其轉換成 CSV 格式(用逗號分割值),我更喜歡其與 Python 一起使用(CSV 文也很有用,因為它可輕鬆導入到電子表格程序中)。
在每一行中,第一項是顏色名稱,其後是其 C、M、Y 和 K 顏色值。 該文件包含 1,793 種顏色,我想要一種分析信息的方法,以了解這些值的範圍。這就是 PostgreSQL 發揮作用的地方。我不想手動輸入所有數據 —— 我認為輸入過程中我不可能不出錯,而且令人頭痛。幸運的是,PostgreSQL 為此提供了一個命令。
首先用以下命令創建資料庫:
Create table hlc_cmyk (color varchar(40), c decimal, m decimal, y decimal, k decimal);
然後通過以下命令引入數據:
copy hlc_cmyk from '/home/gregp/HLC_Atlas_CMYK_SampleData.csv' with (header, format CSV);
開頭有反斜杠,是因為使用純 copy
命令的許可權僅限於 root 用戶和 Postgres 的超級用戶。在括弧中,header
表示第一行包含標題,應忽略,CSV
表示文件格式為 CSV。請注意,在此方法中,顏色名稱不需要用括弧括起來。
如果操作成功,會看到 COPY NNNN
,其中 N 表示插入到表中的行數。
最後,可以用下列命令查詢:
select * from hlc_cmyk;
color | c | m | y | k
---------------+-------+-------+-------+--- H010_L15_C010 | 0.5 | 49.1 | 0.1 | 84.5
H010_L15_C020 | 0.0 | 79.7 | 15.1 | 78.9
H010_L25_C010 | 6.1 | 38.3 | 0.0 | 72.5
H010_L25_C020 | 0.0 | 61.8 | 10.6 | 67.9
H010_L25_C030 | 0.0 | 79.5 | 18.5 | 62.7
H010_L25_C040 | 0.4 | 94.2 | 17.3 | 56.5
H010_L25_C050 | 0.0 | 100.0 | 15.1 | 50.6
H010_L35_C010 | 6.1 | 32.1 | 0.0 | 61.8
H010_L35_C020 | 0.0 | 51.7 | 8.4 | 57.5
H010_L35_C030 | 0.0 | 68.5 | 17.1 | 52.5
所有的 1,793 行數據都是這樣的。回想起來,我不能說此查詢對於 HLC 和 Scribus 任務是絕對必要的,但是它減輕了我對該項目的一些擔憂。
為了生成 HLC 色譜,我使用 Scribus 為色板頁面中的 13,000 多種顏色自動創建了顏色圖表。
我可以使用 copy
命令輸出數據:
copy hlc_cmyk to '/home/gregp/hlc_cmyk_backup.csv' with (header, format CSV);
我還可以使用 where
子句根據某些值來限制輸出。
例如,以下命令將僅發送以 H10
開頭的色調值。
copy hlc_cmyk to '/home/gregp/hlc_cmyk_backup.csv' with (header, format CSV) where color like 'H10%';
備份或傳輸資料庫或表
我在此要提到的最後一個命令是 pg_dump
,它用於備份 PostgreSQL 資料庫,並在 psql
控制台之外運行。 例如:
pg_dump gregp -t hlc_cmyk > hlc.out
pg_dump gregp > dball.out
第一行是導出 hlc_cmyk
表及其結構。第二行將轉儲 gregp
資料庫中的所有表。這對於備份或傳輸資料庫或表非常有用。
要將資料庫或表傳輸到另一台電腦(查看 PostgreSQL 入門那篇文章獲取詳細信息),首先在要轉入的電腦上創建一個資料庫,然後執行相反的操作。
psql -d gregp -f dball.out
一步創建所有表並輸入數據。
總結
在本文中,我們了解了如何使用 WHERE
參數限制操作,以及如何使用 PostgreSQL 通配符 %
。我們還了解了如何將大批量數據載入到表中,然後將部分或全部表數據輸出到文件,甚至是將整個資料庫及其所有單個表輸出。
via: https://opensource.com/article/20/2/postgresql-commands
作者:Greg Pittman 選題:lujun9972 譯者:Morisun029 校對:wxy
本文轉載來自 Linux 中國: https://github.com/Linux-CN/archive