PostgreSQL7.0手冊-教程 -72. 查詢語言
2019-09-08 23:34:02
供稿：網(wǎng)友
第七十二章. 查詢語言
內(nèi)容
SQL 界面
概念
創(chuàng)建新表
向表中加入記錄
查詢一個表
重新定向 SELECT 查詢
聯(lián)接表
更新
刪除
使用聚集函數(shù)
Postgres 的查詢語言是一個 SQL 下一代標準 SQL3 初稿的一個變種．它有許多擴展，比如可擴展的類型系統(tǒng)，繼承，函數(shù)和使用(?production) 規(guī)則等．這些特性是從最初的 Postgres 查詢語言， PostQuel，上繼承過來的．本節(jié)概要的介紹如何使用 PostgresSQL 進行一些簡單操作．這本手冊只是試圖給你一些我們熟知的SQL的概念，而不是一個SQL的完整的教程．有大量的介紹SQL92的書,包括 Melton and Simon, 1993 和 Date and Darwen, 1997．你同時也要明白有些語言特性并非 ANSI 標準．
SQL 界面
在下面的例子里，我們假定你已經(jīng)創(chuàng)建了mydb 數(shù)據(jù)庫并且啟動了psql．本手冊的例子也可以在/usr/local/pgsql/src/tutorial/ 目錄下找到．關于如何使用他們，請參閱該目錄下的 README 文件．要開始教程，鍵入下面命令：
% cd /usr/local/pgsql/src/tutorial
% psql -s mydb
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type /? for help on slash commands
type /q to quit
type /g or terminate with semicolon to execute query
You are currently connected to the database: postgres
mydb=> /i basics.sql
/i 命令從指定的文件里讀入查詢．-s 選項讓你進入單步模式，這樣在把查詢送給后端之前會停下來．本節(jié)的查詢語句都在 basics.sql 文件里．
psql 有一系列 /d 命令用于顯示系統(tǒng)信息．使用這些命令去獲得更多詳細信息； 或在 psql 提示符下鍵入 /? 查看列表．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
概念
Postgres 里最基本的概念是表（class），表是一個命名的對象實例/記錄（instance）的集合．每一實例/記錄由一組相同的命名屬性/字段（attributes）組成．而且每一屬性有一特定的類型．另外，每個實例都有一個唯一的永久對象標識 （OID）．因為 SQL 語法中使用表（tables）的概念，我們這里class和talbes是可以互換的．同樣， SQL行(row) 是一個實例(instance) 而 SQL列(columns) 是 屬性(attributes)．正如前面所述，一組表組成數(shù)據(jù)庫，一組由某一postmaster 進程管理的數(shù)據(jù)庫構成一個安裝或節(jié)點．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
創(chuàng)建新表
你可以通過指定表名和表中各字段屬性和字段類型的方法創(chuàng)建一個新表：
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
注意：所有關鍵字和標識都是大小寫無關的； SQL92 允許使用雙引號將標識括起來實現(xiàn)大小寫相關． PostgresSQL支持通常的 SQL 類型 int， float， real， smallint， char(N)，varchar(N)， date， time，和 timestamp，還支持其他的通用類型和豐富的數(shù)組類型．我們稍后將看到，Postgres 可以定制任意的用戶定義的數(shù)據(jù)類型．因而類型名并不是語法關鍵字，除了SQL92 標準要求支持的特例外．所以， Postgres 用于建表的命令 CREATE 和傳統(tǒng)的關系型系統(tǒng)的命令幾乎一模一樣．不過，我們很快就會看到這里的表是關系型模型的擴展．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
向表中加入記錄
INSERT 語句用于向表中添加記錄：
INSERT INTO weather
VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994');
你還可以使用 copy 命令從一個平面（ASCII）文件里向表中添加大數(shù)量的記錄。這樣做通常要快一些，因為數(shù)據(jù)是做為單個量化事務從目標表中讀取或者寫入到目標表中去的。例如：
COPY weather FROM '/home/user/weather.txt'
USING DELIMITERS '|';
這里源文件的路徑必須是后端服務器機器可以訪問的，而不是客戶端可訪問的路徑，因為后端服務器直接從文件中讀取數(shù)據(jù)。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
查詢表
我們可以用通常的關系查詢選擇和影射查詢檢索 weather 表．一個 SQL select 語句可以用于此事． 該語句分成目標列表（返回的字段）部分和條件（限制條件）部分．例如，我們檢索weather表中的所有行，鍵入：
SELECT * FROM weather;
輸出為：
+--------------+---------+---------+------+------------+
|city | temp_lo | temp_hi | prcp | date |
+--------------+---------+---------+------+------------+
|San Francisco | 46 | 50 | 0.25 | 11-27-1994 |
+--------------+---------+---------+------+------------+
|San Francisco | 43 | 57 | 0 | 11-29-1994 |
+--------------+---------+---------+------+------------+
|Hayward | 37 | 54 | | 11-29-1994 |
+--------------+---------+---------+------+------------+
你可以在目標列表中輸入任意的表達式．例如：
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
在任何查詢條件中條件都可以使用任意布爾操作符（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 | 11-27-1994 |
+--------------+---------+---------+------+------------+
最后，你可以對 select 的結果排序輸出或去除重復記錄.
SELECT DISTINCT city
FROM weather
ORDER BY city;
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
重新定向 SELECT 查詢
任何 select 查詢可以重定向到一個新表
SELECT * INTO TABLE temp FROM weather;
這句話隱含一個 create 命令，創(chuàng)建一個有著與 select into 命令目標列表一樣的字段的新表 temp．這樣，我們可以象對其他表一樣對生成的表做操作．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
聯(lián)接表
到此為止，我們一次只能對一個表進行查詢．我們可以同時查詢多個表，或者同時處理一個表的多個記錄．一個同時訪問一個或多個表的多個記錄的查詢叫連接查詢．比如，我們想找出所有溫度范圍在其他記錄的溫度范圍之內(nèi)的記錄．實際上，我們需要拿每個 EMP 記錄的 temp_lo 和 temp_hi 字段和所有其他 EMP 記錄的相應字段作比較．
注意：這只是一個概念上的模型．實際的聯(lián)合查詢可以一種更有效的方式進行，但卻是用戶不可見的．
我們可以用下面的查詢：
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 |
+--------------+-----+------+---------------+-----+------+
|San Francisco | 37 | 54 | San Francisco | 46 | 50 |
+--------------+-----+------+---------------+-----+------+
注意： 此連接查詢的語意是要從查詢里聲明的表的笛卡兒乘積里面找出符合查詢條件的所有記錄。對笛卡兒乘積里面資格條件為真的記錄， Postgres 計算并返回目標列表的所有值． PostgresSQL 并不對表達式中的重復值賦予任何特殊含義，這就意味著Postgres 有時候會若干次重復計算同一目標列表；這種情況在布爾表達式包含"or"時尤甚，為了避免重復，你應該使用 select distinct 語句．
在本例中，W1 和 W2 都是表 weather 的別名，都具有weather表的所有記錄．在大多數(shù)數(shù)據(jù)庫術語里 W1 和 W2 被看作是范圍變量(range variables.) 一個查詢可以包含任意數(shù)量的表名和別名．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
更新
你可以使用 update 命令更新現(xiàn)有記錄．例如你發(fā)現(xiàn)11月28日后所有溫度記都偏高2度，你可以用下面命令更新數(shù)據(jù)庫數(shù)據(jù)：
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '11/28/1994';
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
刪除
使用 DELETE 命令刪除記錄：
DELETE FROM weather WHERE city = 'Hayward';
所有屬于 Hayward 的天氣記錄都將被刪除．我們進行下面查詢操作時頭腦應該很清醒：
DELETE FROM classname;
在不帶任何條件時，delete 將簡單地把指定表中的所有記錄都刪除掉，而且做這些之前系統(tǒng)將不給出任何提示！
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
使用聚集函數(shù)
與其他大多數(shù)查詢語言一樣， PostgreSQL 支持聚集函數(shù)。一個聚集函數(shù)從多個輸入行計算單一一個結果。例如，存在聚集函數(shù)對一個實例的集合進行 count（計數(shù)），sum（求和），avg（平均），max （最大），和 min （最小）的計算。
理解聚集和 SQL 的 WHERE 和 HAVING 子句之間的相互作用是非常重要的。在 WHERE 和 HAVING 之間的基本差別是：WHERE 在分組和聚集計算之前選擇輸入行（因此，它控制哪一行進入聚集計算），而 HAVING 在分組和聚集計算之后選擇分組行。因此 WHERE 子句可能不包含聚集函數(shù)；因為試圖使用一個聚集來判斷那些行需要輸入到聚集函數(shù)中沒有什么意義。另一方面，HAVING 子句總是包含聚集函數(shù)。（嚴格的說，你允許寫一個不使用聚集的 HAVING 子句，不過是在浪費時間；相同的條件可以在 WHERE 階段更有效地使用。）
例如，我們可以用下面語句找出最高的最低溫度
SELECT max(temp_lo) FROM weather;
如我們想知道這個讀數(shù)是在哪個城市發(fā)生的，我們可以
SELECT city FROM weather WHERE temp_lo = max(temp_lo);
不過這樣做是不行的，因為聚集 max() 不能在 WHERE 中使用。不過，正如經(jīng)常見到的那樣，我們可以重新排列查詢以實現(xiàn)這樣的目標；下面是通過運用子查詢實現(xiàn)的方式：
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
這樣做是可以的，因為子查詢是一個獨立的計算，它計算出與外層 select 無關的自身的聚集。
聚集在和 group by 子句聯(lián)合使用時也是非常有用的。例如，我們可以用下面語句獲取在每個城市觀察到的最低溫度的最高值
SELECT city, max(temp_lo)
FROM weather
GROUP BY city;
這個查詢?yōu)槊總€城市輸出一行。我們可以用 HAVING 過濾這些分組的行：
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING min(temp_lo) < 0;
這個查詢只給出那些最低溫度有零下讀數(shù)的城市。最后，如果我們只關心以 "P" 開頭的城市，我們可以
SELECT city, max(temp_lo)
FROM weather
WHERE city like 'P%'
GROUP BY city
HAVING min(temp_lo) < 0;
請注意我們可以在 WHERE 子句里面給出城市名稱的約束，因為這是不需要聚集的。這樣比在 HAVING 上加限制更有效，因為我們不必對未通過 WHERE 檢查的行進行分組和聚集計算。
--------------------------------------------------------------------------------
