本文檔為PostgreSQL 9.6.0文檔，本轉載已得到原譯者彭煜瑋授權。

2.1. 引言

本章提供一個如何使用SQL執(zhí)行簡單操作的概述。本教程的目的只是給你一個介紹，并非完整的SQL教程。有許多關于SQL的書籍，包括Understanding the New SQL和A Guide to the SQL Standard。你還要知道有些PostgreSQL語言特性是對標準的擴展。

在隨后的例子里，我們假設你已經創(chuàng)建了名為mydb的數據庫，就象在前面的章里面介紹的一樣，并且已經能夠啟動psql。

本手冊的例子也可以在PostgreSQL源代碼的目錄src/tutorial/中找到（二進制PostgreSQL發(fā)布中可能沒有編譯這些文件）。要使用這些文件，首先進入該目錄然后運行make：

$ cd ..../src/tutorial $ make

這樣就創(chuàng)建了那些腳本并編譯了包含用戶定義函數和類型的 C 文件。接下來，要開始本教程，按照下面說的做：

$ cd ..../tutorial $ psql -s mydb ...mydb=> \i basics.sql

i命令從指定的文件中讀取命令。psql的-s選項把你置于單步模式，它在向服務器發(fā)送每個語句之前暫停。 在本節(jié)使用的命令都在文件basics.sql中。

2.2. 概念

PostgreSQL是一種關系型數據庫管理系統(tǒng) （RDBMS）。這意味著它是一種用于管理存儲在關系中的數據的系統(tǒng)。關系實際上是表的數學術語。 今天，把數據存儲在表里的概念已經快成了固有的常識了， 但是還有其它的一些方法用于組織數據庫。在類 Unix 操作系統(tǒng)上的文件和目錄就形成了一種層次數據庫的例子。 更現代的發(fā)展是面向對象數據庫。

每個表都是一個命名的行集合。一個給定表的每一行由同一組的命名列組成，而且每一列都有一個特定的數據類型。雖然列在每行里的順序是固定的， 但一定要記住 SQL 并不對行在表中的順序做任何保證（但你可以為了顯示的目的對它們進行顯式地排序）。

表被分組成數據庫，一個由單個PostgreSQL服務器實例管理的數據庫集合組成一個數據庫集簇。

2.3. 創(chuàng)建一個新表

你可以通過指定表的名字和所有列的名字及其類型來創(chuàng)建表∶

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;

2.4. 在表中增加行

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);

許多開發(fā)人員認為明確列出列要比依賴隱含的順序是更好的風格。

請輸入上面顯示的所有命令，這樣你在隨后的各節(jié)中才有可用的數據。

你還可以使用COPY從文本文件中裝載大量數據。這種方式通常更快，因為COPY命令就是為這類應用優(yōu)化的， 只是比 INSERT少一些靈活性。比如：

COPY weather FROM '/home/user/weather.txt';

這里源文件的文件名必須在運行后端進程的機器上是可用的， 而不是在客戶端上，因為后端進程將直接讀取該文件。你可以在COPY中讀到更多有關COPY命令的信息。

2.5. 查詢一個表

要從一個表中檢索數據就是查詢這個表。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-27San Francisco | 43 | 57 | 0 | 1994-11-29Hayward | 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-27San Francisco | 50 | 1994-11-29Hayward | 45 | 1994-11-29 (3 rows)

請注意這里的AS子句是如何給輸出列重新命名的（AS子句是可選的）。

一個查詢可以使用WHERE子句"修飾"，它指定需要哪些行。WHERE子句包含一個布爾（真值）表達式，只有那些使布爾表達式為真的行才會被返回。在條件中可以使用常用的布爾操作符（AND、OR和NOT）。 比如，下面的查詢檢索舊金山的下雨天的天氣：

SELECT * FROM weatherWHERE 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 weatherORDER BY city;city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------Hayward | 37 | 54 | | 1994-11-29San Francisco | 43 | 57 | 0 | 1994-11-29San Francisco | 46 | 50 | 0.25 | 1994-11-27

在這個例子里，排序的順序并未完全被指定，因此你可能看到屬于舊金山的行被隨機地排序。但是如果你使用下面的語句，那么就總是會得到上面的結果：

SELECT * FROM weatherORDER BY city, temp_lo;

你可以要求在查詢的結果中消除重復的行：

SELECT DISTINCT cityFROM weather;city ---------------HaywardSan Francisco (2 rows)

再次聲明，結果行的順序可能變化。你可以組合使用DISTINCT和ORDER BY來保證獲取一致的結果： [2]

SELECT DISTINCT cityFROM weatherORDER BY city;

Notes

[1]
雖然SELECT *對于即席查詢很有用，但我們普遍認為在生產代碼中這是很糟糕的風格，因為給表增加一個列就改變了結果。

[2]
在一些數據庫系統(tǒng)里，包括老版本的PostgreSQL，DISTINCT的實現自動對行進行排序，因此ORDER BY是多余的。但是這一點并不是 SQL 標準的要求，并且目前的PostgreSQL并不保證DISTINCT會導致行被排序。

2.6. 在表之間連接

到目前為止，我們的查詢一次只訪問一個表。查詢可以一次訪問多個表，或者用這種方式訪問一個表而同時處理該表的多個行。 一個同時訪問同一個或者不同表的多個行的查詢叫連接查詢。舉例來說，比如你想列出所有天氣記錄以及相關的城市位置。要實現這個目標，我們需要拿 weather表每行的city列和cities表所有行的name列進行比較， 并選取那些在該值上相匹配的行對。

Note: 這里只是一個概念上的模型。連接通常以比實際比較每個可能的行對更高效的方式執(zhí)行， 但這些是用戶看不到的。

這個任務可以用下面的查詢來實現：

SELECT *FROM weather, citiesWHERE 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, locationFROM weather, citiesWHERE city = name;

練習：. 看看這個查詢省略WHERE子句的語義是什么

因為這些列的名字都不一樣，所以規(guī)劃器自動地找出它們屬于哪個表。如果在兩個表里有重名的列，你需要限定列名來說明你究竟想要哪一個，如：

SELECT weather.city, weather.temp_lo, weather.temp_hi,weather.prcp, weather.date, cities.locationFROM weather, citiesWHERE 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 highFROM weather W1, weather W2WHERE W1.temp_lo < W2.temp_loAND W1.temp_hi > W2.temp_hi;city | low | high | city | low | high ---------------+-----+------+---------------+-----+------San Francisco | 43 | 57 | San Francisco | 46 | 50Hayward | 37 | 54 | San Francisco | 46 | 50 (2 rows)

在這里我們把weather表重新標記為W1和W2以區(qū)分連接的左部和右部。你還可以用這樣的別名在其它查詢里節(jié)約一些敲鍵，比如：

SELECT *FROM weather w, cities cWHERE w.city = c.name;

你以后會經常碰到這樣的縮寫的。

2.7. 聚集函數

和大多數其它關系數據庫產品一樣，PostgreSQL支持聚集函數。 一個聚集函數從多個輸入行中計算出一個結果。 比如，我們有在一個行集合上計算count（計數）、sum（和）、avg（均值）、max（最大值）和min（最小值）的函數。

比如，我們可以用下面的語句找出所有記錄中最低溫度中的最高溫度：

SELECT max(temp_lo) FROM weather;max -----46 (1 row)

如果我們想知道該讀數發(fā)生在哪個城市，我們可以用：

SELECT city FROM weather WHERE temp_lo = max(temp_lo); 錯誤
不過這個方法不能運轉，因為聚集max不能被用于WHERE子句中（存在這個限制是因為WHERE子句決定哪些行可以被聚集計算包括；因此顯然它必需在聚集函數之前被計算）。 不過，我們通常都可以用其它方法實現我們的目的；這里我們就可以使用子查詢：

SELECT city FROM weatherWHERE temp_lo = (SELECT max(temp_lo) FROM weather);city ---------------San Francisco (1 row)

這樣做是 OK 的，因為子查詢是一次獨立的計算，它獨立于外層的查詢計算出自己的聚集。

聚集同樣也常用于和GROUP BY子句組合。比如，我們可以獲取每個城市觀測到的最低溫度的最高值：

SELECT city, max(temp_lo)FROM weatherGROUP BY city;city | max ---------------+-----Hayward | 37San Francisco | 46 (2 rows)

這樣給我們每個城市一個輸出。每個聚集結果都是在匹配該城市的表行上面計算的。我們可以用HAVING 過濾這些被分組的行：

SELECT city, max(temp_lo)FROM weatherGROUP BY cityHAVING max(temp_lo) < 40;city | max ---------+-----Hayward | 37 (1 row)

這樣就只給出那些所有temp_lo值曾都低于 40的城市。最后，如果我們只關心那些名字以"S"開頭的城市，我們可以用：

SELECT city, max(temp_lo)FROM weatherWHERE city LIKE 'S%'GROUP BY cityHAVING max(temp_lo) < 40;

理解聚集和SQL的WHERE以及HAVING子句之間的關系對我們非常重要。WHERE和HAVING的基本區(qū)別如下：WHERE在分組和聚集計算之前選取輸入行（因此，它控制哪些行進入聚集計算）， 而HAVING在分組和聚集之后選取分組行。因此，WHERE子句不能包含聚集函數； 因為試圖用聚集函數判斷哪些行應輸入給聚集運算是沒有意義的。相反，HAVING子句總是包含聚集函數（嚴格說來，你可以寫不使用聚集的HAVING子句， 但這樣做很少有用。同樣的條件用在WHERE階段會更有效）。

在前面的例子里，我們可以在WHERE里應用城市名稱限制，因為它不需要聚集。這樣比放在HAVING里更加高效，因為可以避免那些未通過 WHERE檢查的行參與到分組和聚集計算中。

2.8. 更新

你可以用UPDATE命令更新現有的行。假設你發(fā)現所有 11 月 28 日以后的溫度讀數都低了兩度，那么你就可以用下面的方式改正數據：

UPDATE weatherSET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2WHERE date > '1994-11-28';

看看數據的新狀態(tài)：

SELECT * FROM weather;city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------San Francisco | 46 | 50 | 0.25 | 1994-11-27San Francisco | 41 | 55 | 0 | 1994-11-29Hayward | 35 | 52 | | 1994-11-29 (3 rows)

2.9. 刪除

數據行可以用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-27San Francisco | 41 | 55 | 0 | 1994-11-29 (2 rows)

我們用下面形式的語句的時候一定要小心

DELETE FROM tablename;

如果沒有一個限制，DELETE將從指定表中刪除所有行，把它清空。做這些之前系統(tǒng)不會請求你確認！

總結

以上是生活随笔為你收集整理的PostgreSQL教程（二）：SQL语言的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。