887
PostgreSQL教程（二）：SQL語言
本文檔為PostgreSQL 9.6.0文檔，本轉載已得到原譯者彭煜瑋授權。
本章提供一個如何使用SQL執行簡單操作的概述。本教程的目的隻是給你一個介紹，並非完整的SQL教程。有許多關於SQL的書籍，包括Understanding the New SQL和A Guide to the SQL Standard。你還要知道有些PostgreSQL語言特性是對標準的擴展。
在隨後的例子裏，我們假設你已經創建了名為mydb的數據庫，就象在前麵的章裏麵介紹的一樣，並且已經能夠啟動psql。
本手冊的例子也可以在PostgreSQL源代碼的目錄src/tutorial/中找到（二進製PostgreSQL發布中可能沒有編譯這些文件）。要使用這些文件，首先進入該目錄然後運行make：
$ cd ..../src/tutorial
$ make
這樣就創建了那些腳本並編譯了包含用戶定義函數和類型的 C 文件。接下來，要開始本教程，按照下麵說的做：
$ cd ..../tutorial
$ psql -s mydb
...
mydb=> \i basics.sql
\i命令從指定的文件中讀取命令。psql的-s選項把你置於單步模式，它在向服務器發送每個語句之前暫停。 在本節使用的命令都在文件basics.sql中。
PostgreSQL是一種關係型數據庫管理係統 （RDBMS）。這意味著它是一種用於管理存儲在關係中的數據的係統。關係實際上是表的數學術語。 今天，把數據存儲在表裏的概念已經快成了固有的常識了， 但是還有其它的一些方法用於組織數據庫。在類 Unix 操作係統上的文件和目錄就形成了一種層次數據庫的例子。 更現代的發展是麵向對象數據庫。
每個表都是一個命名的行集合。一個給定表的每一行由同一組的命名列組成，而且每一列都有一個特定的數據類型。雖然列在每行裏的順序是固定的， 但一定要記住 SQL 並不對行在表中的順序做任何保證（但你可以為了顯示的目的對它們進行顯式地排序）。
表被分組成數據庫，一個由單個PostgreSQL服務器實例管理的數據庫集合組成一個數據庫集簇。
你可以通過指定表的名字和所有列的名字及其類型來創建表∶
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- 最低溫度
temp_hi int, -- 最高溫度
prcp real, -- 濕度
date date
);
你可以在psql輸入這些命令以及換行符。psql可以識別該命令直到分號才結束。
你可以在 SQL 命令中自由使用空白（即空格、製表符和換行符）。 這就意味著你可以用和上麵不同的對齊方式鍵入命令，或者將命令全部放在一行中。兩個劃線（"--"）引入注釋。 任何跟在它後麵直到行尾的東西都會被忽略。SQL 是對關鍵字和標識符大小寫不敏感的語言，隻有在標識符用雙引號包圍時才能保留它們的大小寫（上例沒有這麼做）。
varchar(80)指定了一個可以存儲最長 80 個字符的任意字符串的數據類型。int是普通的整數類型。real是一種用於存儲單精度浮點數的類型。date類型應該可以自解釋（沒錯，類型為date的列名字也是date。 這麼做可能比較方便或者容易讓人混淆 — 你自己選擇）。
PostgreSQL支持標準的SQL類型int、smallint、real、double precision、char(N)、varchar(N)、date、time、timestamp和interval，還支持其他的通用功能的類型和豐富的幾何類型。PostgreSQL中可以定製任意數量的用戶定義數據類型。因而類型名並不是語法關鍵字，除了SQL標準要求支持的特例外。
第二個例子將保存城市和它們相關的地理位置：
CREATE TABLE cities (
name varchar(80),
location point
);
類型point就是一種PostgreSQL特有數據類型的例子。
最後，我們還要提到如果你不再需要某個表，或者你想以不同的形式重建它，那麼你可以用下麵的命令刪除它：
DROP TABLE tablename;
INSERT語句用於向表中添加行：
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
請注意所有數據類型都使用了相當明了的輸入格式。那些不是簡單數字值的常量通常必需用單引號（'）包圍，就象在例子裏一樣。date類型實際上對可接收的格式相當靈活，不過在本教程裏，我們應該堅持使用這種清晰的格式。
point類型要求一個座標對作為輸入，如下：
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
到目前為止使用的語法要求你記住列的順序。一個可選的語法允許你明確地列出列：
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
如果你需要，你可以用另外一個順序列出列或者是忽略某些列， 比如說，我們不知道降水量：
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);
許多開發人員認為明確列出列要比依賴隱含的順序是更好的風格。
請輸入上麵顯示的所有命令，這樣你在隨後的各節中才有可用的數據。
你還可以使用COPY從文本文件中裝載大量數據。這種方式通常更快，因為COPY命令就是為這類應用優化的， 隻是比 INSERT少一些靈活性。比如：
COPY weather FROM '/home/user/weather.txt';
這裏源文件的文件名必須在運行後端進程的機器上是可用的， 而不是在客戶端上，因為後端進程將直接讀取該文件。你可以在COPY中讀到更多有關COPY命令的信息。
要從一個表中檢索數據就是查詢這個表。SQL的SELECT語句就是做這個用途的。 該語句分為選擇列表（列出要返回的列）、表列表（列出從中檢索數據的表）以及可選的條件（指定任意的限製）。比如，要檢索表weather的所有行，鍵入：
SELECT * FROM weather;
這裏*是"所有列"的縮寫。 [1] 因此相同的結果應該這樣獲得：
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
而輸出應該是：
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)
你可以在選擇列表中寫任意表達式，而不僅僅是列的列表。比如，你可以：
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
這樣應該得到：
city | temp_avg | date
---------------+----------+------------
San Francisco | 48 | 1994-11-27
San Francisco | 50 | 1994-11-29
Hayward | 45 | 1994-11-29
(3 rows)
請注意這裏的AS子句是如何給輸出列重新命名的（AS子句是可選的）。
一個查詢可以使用WHERE子句"修飾"，它指定需要哪些行。WHERE子句包含一個布爾（真值）表達式，隻有那些使布爾表達式為真的行才會被返回。在條件中可以使用常用的布爾操作符（AND、OR和NOT）。 比如，下麵的查詢檢索舊金山的下雨天的天氣：
SELECT * FROM weather
WHERE city = 'San Francisco' AND prcp > 0.0;
結果：
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
(1 row)
你可以要求返回的查詢結果是排好序的：
SELECT * FROM weather
ORDER BY city;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
Hayward | 37 | 54 | | 1994-11-29
San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | 46 | 50 | 0.25 | 1994-11-27
在這個例子裏，排序的順序並未完全被指定，因此你可能看到屬於舊金山的行被隨機地排序。但是如果你使用下麵的語句，那麼就總是會得到上麵的結果：
SELECT * FROM weather
ORDER BY city, temp_lo;
你可以要求在查詢的結果中消除重複的行：
SELECT DISTINCT city
FROM weather;
city
---------------
Hayward
San Francisco
(2 rows)
再次聲明，結果行的順序可能變化。你可以組合使用DISTINCT和ORDER BY來保證獲取一致的結果： [2]
SELECT DISTINCT city
FROM weather
ORDER BY city;
Notes
[1]
雖然SELECT *對於即席查詢很有用，但我們普遍認為在生產代碼中這是很糟糕的風格，因為給表增加一個列就改變了結果。
[2]
在一些數據庫係統裏，包括老版本的PostgreSQL，DISTINCT的實現自動對行進行排序，因此ORDER BY是多餘的。但是這一點並不是 SQL 標準的要求，並且目前的PostgreSQL並不保證DISTINCT會導致行被排序。
到目前為止，我們的查詢一次隻訪問一個表。查詢可以一次訪問多個表，或者用這種方式訪問一個表而同時處理該表的多個行。 一個同時訪問同一個或者不同表的多個行的查詢叫連接查詢。舉例來說，比如你想列出所有天氣記錄以及相關的城市位置。要實現這個目標，我們需要拿 weather表每行的city列和cities表所有行的name列進行比較， 並選取那些在該值上相匹配的行對。
Note: 這裏隻是一個概念上的模型。連接通常以比實際比較每個可能的行對更高效的方式執行， 但這些是用戶看不到的。
這個任務可以用下麵的查詢來實現：
SELECT *
FROM weather, cities
WHERE city = name;
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)
觀察結果集的兩個方麵：
沒有城市Hayward的結果行。這是因為在cities表裏麵沒有Hayward的匹配行，所以連接忽略 weather表裏的不匹配行。我們稍後將看到如何修補它。
有兩個列包含城市名字。這是正確的， 因為weather和cities表的列被串接在一起。不過，實際上我們不想要這些， 因此你將可能希望明確列出輸出列而不是使用*：
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
練習：. 看看這個查詢省略WHERE子句的語義是什麼
因為這些列的名字都不一樣，所以規劃器自動地找出它們屬於哪個表。如果在兩個表裏有重名的列，你需要限定列名來說明你究竟想要哪一個，如：
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather, cities
WHERE cities.name = weather.city;
人們廣泛認為在一個連接查詢中限定所有列名是一種好的風格，這樣即使未來向其中一個表裏添加重名列也不會導致查詢失敗。
到目前為止，這種類型的連接查詢也可以用下麵這樣的形式寫出來：
SELECT *
FROM weather INNER JOIN cities ON (weather.city = cities.name);
這個語法並不象上文的那個那麼常用，我們在這裏寫出來是為了讓你更容易了解後麵的主題。
現在我們將看看如何能把Hayward記錄找回來。我們想讓查詢幹的事是掃描weather表， 並且對每一行都找出匹配的cities表行。如果我們沒有找到匹配的行，那麼我們需要一些"空值"代替cities表的列。 這種類型的查詢叫外連接 （我們在此之前看到的連接都是內連接）。這樣的命令看起來象這樣：
SELECT *
FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
Hayward | 37 | 54 | | 1994-11-29 | |
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
(3 rows)
這個查詢是一個左外連接， 因為在連接操作符左部的表中的行在輸出中至少要出現一次， 而在右部的表的行隻有在能找到匹配的左部表行是才被輸出。 如果輸出的左部表的行沒有對應匹配的右部表的行，那麼右部表行的列將填充空值（null）。
練習：. 還有右外連接和全外連接。試著找出來它們能幹什麼。
我們也可以把一個表和自己連接起來。這叫做自連接。 比如，假設我們想找出那些在其它天氣記錄的溫度範圍之外的天氣記錄。這樣我們就需要拿 weather表裏每行的temp_lo和temp_hi列與weather表裏其它行的temp_lo和temp_hi列進行比較。我們可以用下麵的查詢實現這個目標：
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
W2.city, W2.temp_lo AS low, W2.temp_hi AS high
FROM weather W1, weather W2
WHERE W1.temp_lo < W2.temp_lo
AND W1.temp_hi > W2.temp_hi;
city | low | high | city | low | high
---------------+-----+------+---------------+-----+------
San Francisco | 43 | 57 | San Francisco | 46 | 50
Hayward | 37 | 54 | San Francisco | 46 | 50
(2 rows)
在這裏我們把weather表重新標記為W1和W2以區分連接的左部和右部。你還可以用這樣的別名在其它查詢裏節約一些敲鍵，比如：
SELECT *
FROM weather w, cities c
WHERE w.city = c.name;
你以後會經常碰到這樣的縮寫的。
和大多數其它關係數據庫產品一樣，PostgreSQL支持聚集函數。 一個聚集函數從多個輸入行中計算出一個結果。 比如，我們有在一個行集合上計算count（計數）、sum（和）、avg（均值）、max（最大值）和min（最小值）的函數。
比如，我們可以用下麵的語句找出所有記錄中最低溫度中的最高溫度：
SELECT max(temp_lo) FROM weather;
max
-----
46
(1 row)
如果我們想知道該讀數發生在哪個城市，我們可以用：
SELECT city FROM weather WHERE temp_lo = max(temp_lo); 錯誤
不過這個方法不能運轉，因為聚集max不能被用於WHERE子句中（存在這個限製是因為WHERE子句決定哪些行可以被聚集計算包括；因此顯然它必需在聚集函數之前被計算）。 不過，我們通常都可以用其它方法實現我們的目的；這裏我們就可以使用子查詢：
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city
---------------
San Francisco
(1 row)
這樣做是 OK 的，因為子查詢是一次獨立的計算，它獨立於外層的查詢計算出自己的聚集。
聚集同樣也常用於和GROUP BY子句組合。比如，我們可以獲取每個城市觀測到的最低溫度的最高值：
SELECT city, max(temp_lo)
FROM weather
GROUP BY city;
city | max
---------------+-----
Hayward | 37
San Francisco | 46
(2 rows)
這樣給我們每個城市一個輸出。每個聚集結果都是在匹配該城市的表行上麵計算的。我們可以用HAVING 過濾這些被分組的行：
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo) < 40;
city | max
---------+-----
Hayward | 37
(1 row)
這樣就隻給出那些所有temp_lo值曾都低於 40的城市。最後，如果我們隻關心那些名字以"S"開頭的城市，我們可以用：
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%'
GROUP BY city
HAVING max(temp_lo) < 40;
理解聚集和SQL的WHERE以及HAVING子句之間的關係對我們非常重要。WHERE和HAVING的基本區別如下：WHERE在分組和聚集計算之前選取輸入行（因此，它控製哪些行進入聚集計算）， 而HAVING在分組和聚集之後選取分組行。因此，WHERE子句不能包含聚集函數； 因為試圖用聚集函數判斷哪些行應輸入給聚集運算是沒有意義的。相反，HAVING子句總是包含聚集函數（嚴格說來，你可以寫不使用聚集的HAVING子句， 但這樣做很少有用。同樣的條件用在WHERE階段會更有效）。
在前麵的例子裏，我們可以在WHERE裏應用城市名稱限製，因為它不需要聚集。這樣比放在HAVING裏更加高效，因為可以避免那些未通過 WHERE檢查的行參與到分組和聚集計算中。
你可以用UPDATE命令更新現有的行。假設你發現所有 11 月 28 日以後的溫度讀數都低了兩度，那麼你就可以用下麵的方式改正數據：
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';
看看數據的新狀態：
SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
Hayward | 35 | 52 | | 1994-11-29
(3 rows)
數據行可以用DELETE命令從表中刪除。假設你對Hayward的天氣不再感興趣，那麼你可以用下麵的方法把那些行從表中刪除：
DELETE FROM weather WHERE city = 'Hayward';
所有屬於Hayward的天氣記錄都被刪除。
SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
(2 rows)
我們用下麵形式的語句的時候一定要小心
DELETE FROM tablename;
如果沒有一個限製，DELETE將從指定表中刪除所有行，把它清空。做這些之前係統不會請求你確認！
最後更新：2017-08-16 14:02:15