PostgreSQL教程(二):SQL语言
本文檔為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.sqli命令從指定的文件中讀取命令。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子句決定哪些行可以被聚集計算包括;因此顯然它必需在聚集函數之前被計算)。 不過,我們通常都可以用其它方法實現我們的目的;這里我們就可以使用子查詢:
這樣做是 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语言的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 妹纸这套路玩的好深,吹泡泡能吹出气球来
- 下一篇: WordPress整站轻松开启HTTPS