[ 永遠的UNIX > PostgreSQL7.0手冊-教程 -72. 查詢語言 ]

[ 永遠的UNIX::UNIX技術資料的寶庫 ] GB | BIG5

首頁 > 數據庫 > 其它 > 正文

PostgreSQL7.0手冊-教程 -72. 查詢語言

編譯：何偉平 laser@zhengmai.com.cn (2001-04-21 23:44:40)

第七十二章.　查詢語言
內容　
SQL　界面　
概念　
創建新表　
向表中加入記錄　
查詢一個表　
重新定向　SELECT　查詢　
聯接表　
更新　
刪除　
使用聚集函數　
Postgres　的查詢語言是一個　SQL　下一代標準　SQL3　初稿的一個變種．它有許多擴展，比如可擴展的類型系統，繼承，函數和使用(?production)　規則等．這些特性是從最初的　Postgres　查詢語言，　PostQuel，上繼承過來的．本節概要的介紹如何使用　PostgresSQL　進行一些簡單操作．這本手冊只是試圖給你一些我們熟知的SQL的概念，而不是一個SQL的完整的教程．有大量的介紹SQL92的書,包括　Melton　and　Simon,　1993　和　Date　and　Darwen,　1997．你同時也要明白有些語言特性並非　ANSI　標準．　
SQL　界面
在下面的例子裡，我們假定你已經創建了mydb　數據庫並且啟動了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　選項讓你進入單步模式，這樣在把查詢送給端之前會停下來．本節的查詢語句都在　basics.sql　文件裡．　
psql　有一系列　\d　命令用顯示系統信息．使用這些命令去獲得更多詳細信息；　或在　psql　提示符下鍵入　\?　查看列表．

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

概念
Postgres　裡最基本的概念是表（class），表是一個命名的對象實例/記錄（instance）的集合．每一實例/記錄由一組相同的命名屬性/字段（attributes）組成．而且每一屬性有一特定的類型．另外，每個實例都有一個唯一的永久對象標識　（OID）．因為　SQL　語法中使用表（tables）的概念，我們這裡class和talbes是可以互換的．同樣，　SQL行(row)　是一個實例(instance)　而　SQL列(columns)　是　屬性(attributes)．正如前面所述，一組表組成數據庫，一組由某一postmaster　進程管理的數據庫構成一個安裝或節點．

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

創建新表
你可以通過指定表名和表中各字段屬性和字段類型的方法創建一個新表：　
CREATE　TABLE　weather　(
　　　　city　　　　　　　　　　　　varchar(80),
　　　　temp_lo　　　　　　　　　int,　　　　　　　　　　　--　low　temperature
　　　　temp_hi　　　　　　　　　int,　　　　　　　　　　　--　high　temperature
　　　　prcp　　　　　　　　　　　　real,　　　　　　　　　　--　precipitation
　　　　date　　　　　　　　　　　　date
);
注意：所有關鍵字和標識都是大小寫無關的；　SQL92　允許使用雙引號將標識括起來實現大小寫相關．　PostgresSQL支持通常的　SQL　類型　int，　float，　real，　smallint，　char(N)，varchar(N)，　date，　time，和　timestamp，還支持其他的通用類型和豐富的數組類型．我們稍將看到，Postgres　可以定制任意的用戶定義的數據類型．因而類型名並不是語法關鍵字，除了SQL92　標準要求支持的特例外．所以，　Postgres　用建表的命令　CREATE　和傳統的關系型系統的命令幾乎一模一樣．不過，我們很快就會看到這裡的表是關系型模型的擴展．

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

向表中加入記錄
INSERT　語句用向表中添加記錄：　
INSERT　INTO　weather
　　　　VALUES　('San　Francisco',　46,　50,　0.25,　'11/27/1994');
你還可以使用　copy　命令從一個平面（ASCII）文件裡向表中添加大數量的記錄。這樣做通常要快一些，因為數據是做為單個量化事務從目標表中讀取或者寫入到目標表中去的。例如：　
COPY　weather　FROM　'/home/user/weather.txt'
　　　　USING　DELIMITERS　'|';
這裡源文件的路徑必須是端服務器機器可以訪問的，而不是客戶端可訪問的路徑，因為端服務器直接從文件中讀取數據。

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

查詢表
我們可以用通常的關系查詢選擇和影射查詢檢索　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　命令，創建一個有著與　select　into　命令目標列表一樣的字段的新表　temp．這樣，我們可以象對其他表一樣對生成的表做操作．

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

聯接表
到此為止，我們一次只能對一個表進行查詢．我們可以同時查詢多個表，或者同時處理一個表的多個記錄．一個同時訪問一個或多個表的多個記錄的查詢叫連接查詢．比如，我們想找出所有溫度范圍在其他記錄的溫度范圍之內的記錄．實際上，我們需要拿每個　EMP　記錄的　temp_lo　和　temp_hi　字段和所有其他　EMP　記錄的相應字段作比較．　
注意：這只是一個概念上的模型．實際的聯合查詢可以一種更有效的方式進行，但卻是用戶不可見的．
我們可以用下面的查詢：　
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表的所有記錄．在大多數數據庫術語裡　W1　和　W2　被看作是范圍變量(range　variables.)　一個查詢可以包含任意數量的表名和別名．

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

更新
你可以使用　update　命令更新現有記錄．例如你發現11月28日所有溫度記都偏高2度，你可以用下面命令更新數據庫數據：　
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　將簡單地把指定表中的所有記錄都刪除掉，而且做這些之前系統將不給出任何提示！

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

使用聚集函數
與其他大多數查詢語言一樣，　PostgreSQL　支持聚集函數。一個聚集函數從多個輸入行計算單一一個結果。例如，存在聚集函數對一個實例的集合進行　count（計數），sum（求和），avg（平均），max　（最大），和　min　（最小）的計算。　
理解聚集和　SQL　的　WHERE　和　HAVING　子句之間的相互作用是非常重要的。在　WHERE　和　HAVING　之間的基本差別是：WHERE　在分組和聚集計算之前選擇輸入行（因此，它控制哪一行進入聚集計算），而　HAVING　在分組和聚集計算之選擇分組行。因此　WHERE　子句可能不包含聚集函數；因為試圖使用一個聚集來判斷那些行需要輸入到聚集函數中沒有什意義。另一方面，HAVING　子句總是包含聚集函數。（嚴格的說，你允許寫一個不使用聚集的　HAVING　子句，不過是在浪費時間；相同的條件可以在　WHERE　階段更有效地使用。）　

例如，我們可以用下面語句找出最高的最低溫度　

SELECT　max(temp_lo)　FROM　weather;
如我們想知道這個讀數是在哪個城市發生的，我們可以　
SELECT　city　FROM　weather　WHERE　temp_lo　=　max(temp_lo);
不過這樣做是不行的，因為聚集　max()　不能在　WHERE　中使用。不過，正如經常見到的那樣，我們可以重新排列查詢以實現這樣的目標；下面是通過運用子查詢實現的方式：　
SELECT　city　FROM　weather
　　　　WHERE　temp_lo　=　(SELECT　max(temp_lo)　FROM　weather);
這樣做是可以的，因為子查詢是一個獨立的計算，它計算出與外層　select　無關的自身的聚集。　
聚集在和　group　by　子句聯合使用時也是非常有用的。例如，我們可以用下面語句獲取在每個城市觀察到的最低溫度的最高值　

SELECT　city,　max(temp_lo)
　　　　FROM　weather
　　　　GROUP　BY　city;
這個查詢為每個城市輸出一行。我們可以用　　HAVING　過濾這些分組的行：　
SELECT　city,　max(temp_lo)
　　　　FROM　weather
　　　　GROUP　BY　city
　　　　HAVING　min(temp_lo)　<　0;
這個查詢只給出那些最低溫度有零下讀數的城市。最，如果我們只關心以　"P"　開頭的城市，我們可以　
　　
SELECT　city,　max(temp_lo)
　　　　FROM　weather
　　　　WHERE　city　like　'P%'
　　　　GROUP　BY　city
　　　　HAVING　min(temp_lo)　<　0;
請注意我們可以在　WHERE　子句裡面給出城市名稱的約束，因為這是不需要聚集的。這樣比在　HAVING　上加限制更有效，因為我們不必對未通過　WHERE　檢查的行進行分組和聚集計算。

--------------------------------------------------------------------------------

(http://www.fanqiang.com) 進入【UNIX論壇】

相關文章

===更多相關===

★ 樊強制作歡迎分享 ★