SQL語句經(jīng)典實例 收藏
無論您是一位 SQL 的新手,或是一位只是需要對 SQL 復(fù)習(xí)一下的資料倉儲業(yè)界老將,您就來對地方了!
- SQL 指令: SQL 如何被用來儲存、讀取、以及處理數(shù)據(jù)庫之中的資料。
- 表格處理: SQL 如何被用來處理數(shù)據(jù)庫中的表格。
- SQL語法: 這一頁列出所有在這個教材中被提到的 SQL 語法
SQL SELECT
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
若要選出所有的店名 (store_Name),我們就打入:
SELECT store_name FROM Store_Information
結(jié)果:
store_name
Los Angeles
San Diego
Los Angeles
Boston
SQL DISTINCT
若要在以下的表格,Store_Information,找出所有不同的店名時,
Store_Information表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
我們就打入,
SELECT DISTINCT store_name FROM Store_Information
結(jié)果:
store_name
Los Angeles
San Diego
Boston
SQL WHERE
若我們要由以下的表格抓出營業(yè)額超過 $1,000 的資料,
Store_Information
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
表格
我們就打入,
SELECT store_name
FROM Store_Information
WHERE Sales > 1000
結(jié)果:
store_name
Los Angeles
SQL AND OR
我們?nèi)粢?Store_Information表格中選出所有 Sales 高于 $1,000 或是 Sales 在 $500 及 $275 之間的資料的話,
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
San Francisco $300 Jan-08-1999
Boston $700 Jan-08-1999
我們就打入,
SELECT store_name
FROM Store_Information
WHERE Sales > 1000
OR (Sales < 500 AND Sales > 275)
結(jié)果:
store_name
Los Angeles
San Francisco
SQL IN
若我們要在 Store_Information 表格中找出所有含蓋 Los Angeles 或 San Diego 的資料,
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
San Francisco $300 Jan-08-1999
Boston $700 Jan-08-1999
我們就打入,
SELECT *
FROM Store_Information
WHERE store_name IN ('Los Angeles', 'San Diego')
結(jié)果: store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
SQL BETWEEN
若我們要由 Store_Information 表格中找出所有介于 January 6, 1999 及 January 10, 1999 中的資料,
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
San Francisco $300 Jan-08-1999
Boston $700 Jan-08-1999
我們就打入,
SELECT *
FROM Store_Information
WHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999'
請讀者注意:在不同的數(shù)據(jù)庫中,日期的儲存法可能會有所不同。在這里我們選擇了其中一種儲存法。
結(jié)果: store_name Sales Date
San Diego $250 Jan-07-1999
San Francisco $300 Jan-08-1999
Boston $700 Jan-08-1999
SQL LIKE
以下是幾個例子:
'A_Z': 所有以 'A' 起頭,另一個任何值的字原,且以 'Z' 為結(jié)尾的字串。 'ABZ' 和 'A2Z' 都符合這一個模式,而 'AKKZ' 并不符合 (因為在 A 和 Z 之間有兩個字原,而不是一個字原)。
'ABC%': 所有以 'ABC' 起頭的字串。舉例來說,'ABCD' 和 'ABCABC' 都符合這個套式。
'%XYZ': 所有以 'XYZ' 結(jié)尾的字串。舉例來說,'WXYZ' 和 'ZZXYZ' 都符合這個套式。
'%AN%': 所有含有 'AN' 這個套式的字串。舉例來說, 'LOS ANGELES' 和 'SAN FRANCISCO' 都符合這個套式。
我們將以上最后一個例子用在我們的 Store_Information 表格上:
Store_Information 表格
store_name Sales Date
LOS ANGELES $1500 Jan-05-1999
SAN DIEGO $250 Jan-07-1999
SAN FRANCISCO $300 Jan-08-1999
BOSTON $700 Jan-08-1999
我們就打入,
SELECT *
FROM Store_Information
WHERE store_name LIKE '%AN%'
結(jié)果: store_name Sales Date
LOS ANGELES $1500 Jan-05-1999
SAN FRANCISCO $300 Jan-08-1999
SAN DIEGO $250 Jan-07-1999
SQL ORDER BY
若我們要依照 Sales 欄位的由大往小列出 Store_Information 表格中的資料,
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
San Francisco $300 Jan-08-1999
Boston $700 Jan-08-1999
我們就打入,
SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY Sales DESC
結(jié)果:
store_name Sales Date
Los Angeles $1500 Jan-05-1999
Boston $700 Jan-08-1999
San Francisco $300 Jan-08-1999
San Diego $250 Jan-07-1999
在以上的例子中,我們用欄位名來指定排列順序的依據(jù)。除了欄位名外,我們也可以用欄位的順序 (依據(jù) SQL 句中的順序)。在 SELECT 后的第一個欄位為 1,第二個欄位為 2,以此類推。在上面這個例子中,我們打以下這一句 SQL 可以達到完全一樣的效果:
SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY 2 DESC
SQL 函數(shù)
AVG (平均)
COUNT (計數(shù))
MAX (最大值)
MIN (最小值)
SUM (總合)
運用函數(shù)的語法是:
SELECT "函數(shù)名"("欄位名")
FROM "表格名"
舉例來說,若我們要由我們的示范表格中求出 Sales 欄位的總合,
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
我們就打入,
SELECT SUM(Sales) FROM Store_Information
結(jié)果:
SUM(Sales)
$2750
$2750 代表所有 Sales 欄位的總合: $1500 + $250 + $300 + $700.
除了函數(shù)的運用外,SQL 也可以做簡單的數(shù)學(xué)運算,例如加(+)和減(-)。對于文字類的資料,SQL 也有好幾個文字處理方面的函數(shù),例如文字相連 (concatenation),文字修整 (trim),以及子字串 (substring)。不同的數(shù)據(jù)庫對這些函數(shù)有不同的語法,所以最好是參考您所用數(shù)據(jù)庫的信息,來確定在那個數(shù)據(jù)庫中,這些函數(shù)是如何被運用的。
SQL COUNT
若我們要由我們的示范表格中求出 Sales 欄位的總合,
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
我們就打入,
SELECT SUM(Sales) FROM Store_Information
結(jié)果:
SUM(Sales)
$2750
$2750 代表所有 Sales 欄位的總合: $1500 + $250 + $300 + $700.
除了函數(shù)的運用外,SQL 也可以做簡單的數(shù)學(xué)運算,例如加(+)和減(-)。對于文字類的資料,SQL 也有好幾個文字處理方面的函數(shù),例如文字相連 (concatenation),文字修整 (trim),以及子字串 (substring)。不同的數(shù)據(jù)庫對這些函數(shù)有不同的語法,所以最好是參考您所用數(shù)據(jù)庫的信息,來確定在那個數(shù)據(jù)庫中,這些函數(shù)是如何被運用的。
SQL GROUP BY
在我們的示范上,
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
我們就打入,
SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name
結(jié)果:
store_name SUM(Sales)
Los Angeles $1800
San Diego $250
Boston $700
當(dāng)我們選不只一個欄位,且其中至少一個欄位有包含函數(shù)的運用時,我們就需要用到 GROUP BY 這個指令。在這個情況下,我們需要確定我們有 GROUP BY 所有其他的欄位。換句話說,除了有包括函數(shù)的欄位外,我 們都需要將其放在 GROUP BY 的子句中。
SQL HAVING
那我們?nèi)绾螌瘮?shù)產(chǎn)生的值來設(shè)定條件呢?舉例來說,我們可能只需要知道哪些店的營業(yè)額有超過 $1,500。在這個情況下,我們不能使用 WHERE 的指令。那要怎么辦呢?很幸運地,SQL 有提供一個 HAVING 的指令,而我們就可以用這個指令來達到這個目標。 HAVING 子句通常是在一個 SQL 句子的最后。一個含有 HAVING 子句的 SQL 并不一定要包含 GROUP BY 子句。HAVING 的語法如下
SELECT "欄位1", SUM("欄位2")
FROM "表格名"
GROUP BY "欄位1"
HAVING (函數(shù)條件)
請讀者注意: GROUP BY 子句并不是一定需要的。
在我們Store_Information 表格這個例子中,
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
我們打入,
SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500
結(jié)果:
store_name SUM(Sales)
Los Angeles $1800
SQL ALIAS
基本上,這兩種別名都是放在它們要替代的物件后面,而它們中間由一個空白分開。我們 繼續(xù)使用 Store_Information這個表格來做例子:
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
我們用跟 SQL GROUP BY 那一頁 一樣的例子。這里的不同處是我們加上了欄位別名以及表格別名:
SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales"
FROM Store_Information A1
GROUP BY A1.store_name
結(jié)果:
Store Total Sales
Los Angeles $1800
San Diego $250
Boston $700
在結(jié)果中,資料本身沒有不同。不同的是欄位的標題。這是運用欄位別名的結(jié)果。在第二個欄位上,原本我們的標題是 "Sum(Sales)",而現(xiàn)在我們有一個很清楚的 "Total Sales"。很明顯地,"Total Sales" 能夠比 "Sum(Sales)" 更精確地闡述這個欄位的含意。用表格別名的好處在這里并沒有顯現(xiàn)出來,不過這在下一頁 (SQL Join) 就會很清楚了。
SQL 表格連接
現(xiàn)在我們介紹連接(join)的概念。要了解連接,我們需要用到許多我們之前已介紹過的指令。 我們先假設(shè)我們有以下的兩個表格,
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
Geography 表格 region_name store_name
East Boston
East New York
West Los Angeles
West San Diego
而我們要知道每一區(qū) (region_name) 的營業(yè)額 (sales)。 Geography 這個表格告訴我們每一區(qū)有哪些店,而 Store_Information 告訴我們每一個店的營業(yè)額。 若我們要知道每一區(qū)的營業(yè)額,我們需要將這兩個不同表格中的資料串聯(lián)起來。當(dāng)我們仔細了解這兩個 表格后,我們會發(fā)現(xiàn)它們可經(jīng)由一個相同的欄位,store_name,連接起來。我們先將 SQL 句列出, 之后再討論每一個子句的意義:
SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
結(jié)果:
REGION SALES
East $700
West $2050
在第一行中,我們告訴 SQL 去選出兩個欄位:第一個欄位是 Geography 表格中的 Region_name 欄位 (我們?nèi)×艘粋€別名叫做 REGION);第二個欄位是 Store_Information 表格中的 sales 欄位 (別名為 SALES)。請注意在這里我們有用到表格別名:Geography 表格的別名是 A1,Store_Information 表格的別名是 A2。若我們沒有用表格別名的話, 第一行就會變成
SELECT Geography.region_name REGION, SUM(Store_Information.Sales) SALES
很明顯地,這就復(fù)雜多了。在這里我們可以看到表格別名的功用:它能讓 SQL 句容易被了解,尤其是這個 SQL 句含蓋好幾個不同的表格時。
接下來我們看第三行,就是 WHERE 子句。 這是我們闡述連接條件的地方。在這里,我們要確認 Geography 表格中 Store_name 欄位的值與 Store_Information 表格中 store_name 欄位的值是相等的。這個 WHERE 子句是一個連接的靈魂人物,因為它的角色是確定兩個表格之間的連接是正確的。如果 WHERE子句是錯誤的,我們就極可能得到一個笛卡兒連接 (Cartesian join)。笛卡兒連接會造成我們得到所有兩個表格每兩行之間所有可能的組合。在這個例子中,笛卡兒連接會讓我們得到 4 x 4 = 16 行的結(jié)果。
SQL 外部連接
之前我們看到的左連接 (left join),又稱內(nèi)部連接 (inner join)。在這個情況下,要兩個表格內(nèi)都有同樣的值,那一筆資料才會被選出。那如果我們想要列出一個表格中每一筆的資料,無論它的值在另一個表格中有沒有出現(xiàn),那該怎么辦呢?在這個時候,我們就需要用到 SQL OUTER JOIN (外部連接) 的指令。
外部連接的語法是依數(shù)據(jù)庫的不同而有所不同的。舉例來說,在 Oracle 上,我們會在 WHERE 子句中要選出所有資料的那個表格之后加上一個 "(+)" 來代表說這個表格中的所有資料我們都要。
假設(shè)我們有以下的兩個表格:
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
Geography 表格 region_name store_name
East Boston
East New York
West Los Angeles
West San Diego
我們需要知道每一間店的營業(yè)額。如果我們用一個普通的連接,我們將會漏失掉 'New York'這個店,因為它并不存在于 Store_Information 這個表格。所以,在這個情況下,我們需要用外部連接來串聯(lián)這兩個表格:
SELECT A1.store_name, SUM(A2.Sales) SALES
FROM Georgraphy A1, Store_Information A2
WHERE A1.store_name = A2.store_name (+)
GROUP BY A1.store_name
我們在這里是使用了 Oracle 的外部連接語法。
結(jié)果:
store_name SALES
Boston $700
New York
Los Angeles $1800
San Diego $250
請注意: 當(dāng)?shù)诙€表格沒有相對的資料時,SQL 會傳回 NULL 值。在這一個例子中, 'New York' 并不存在于 Store_Information 表格,所以它的 "SALES" 欄位是 NULL。
SQL Subquery
我們可以在一個 SQL 語句中放入另一個 SQL 語句。當(dāng)我們在 WHERE 子句或 WHERE 子句或 HAVING 子句中插入另一個 SQL 語句時,我們就有一個 subquery 的架構(gòu)。 Subquery 的作用是什么呢?第一,它可以被用來連接表格。另外,有的時候 subquery 是唯一能夠連接兩個表格的方式。
Subquery 的語法如下:
SELECT "欄位1"
FROM "表格"
WHERE "欄位2" [比較運算素]
(SELECT "欄位1"
FROM "表格"
WHERE [條件])
[比較運算素] 可以是相等的運算素,例如 =, >, <, >=, <=. 這也可以是一個對文字的運算素,例如 "LIKE."
我們就用剛剛在闡述 SQL 連接時用過的例子:
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
Geography 表格 region_name store_name
East Boston
East New York
West Los Angeles
West San Diego
我們要運用 subquery 來找出所有在西部的店的營業(yè)額。我們可以用下面的 SQL 來達到我們的目的:
SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = 'West')
結(jié)果:
SUM(Sales)
2050
在這個例子中,我們并沒有直接將兩個表格連接起來,然后由此直接算出每一間西區(qū)店面的營業(yè)額。我們做的是先找出哪些店是在西區(qū)的,然后再算出這些店的營業(yè)額總共是多少。
SQL UNION
UNION 指令的目的是將兩個 SQL 語句的結(jié)果合并起來。從這個角度來看, UNION 跟 JOIN 有些許類似,因為這兩個指令都可以由多個表格中擷取資料。 UNION 的一個限制是兩個 SQL 語句所產(chǎn)生的欄位需要是同樣的資料種類。另外,當(dāng)我們用 UNION這個指令時,我們只會看到不同的資料值 (類似 SELECT DISTINCT)。
UNION 的語法如下:
[SQL 語句 1]
UNION
[SQL 語句 2]
假設(shè)我們有以下的兩個表格,
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
Internet Sales 表格 Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
Jan-12-1999 $750
而我們要找出來所有有營業(yè)額 (sales) 的日子。要達到這個目的,我們用以下的 SQL 語句:
SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales
結(jié)果:
Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999
有一點值得注意的是,如果我們在任何一個 SQL 語句 (或是兩句都一起) 用 "SELECT DISTINCT Date" 的話,那我們會得到完全一樣的結(jié)果。
SQL UNION ALL
UNION ALL 這個指令的目的也是要將兩個 SQL 語句的結(jié)果合并在一起。 UNION ALL 和 UNION 不同之處在于 UNION ALL 會將每一筆符合條件的資料都列出來,無論資料值有無重復(fù)。
UNION ALL 的語法如下:
[SQL 語句 1]
UNION ALL
[SQL 語句 2]
我們用和上一頁同樣的例子來顯示出 UNION ALL 和 UNION 的不同。同樣假設(shè)我們有以下兩個表格,
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
Internet Sales 表格 Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
Jan-12-1999 $750
而我們要找出有店面營業(yè)額以及網(wǎng)絡(luò)營業(yè)額的日子。要達到這個目的,我們用以下的 SQL 語句:
SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales
結(jié)果:
Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-08-1999
Jan-07-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999
SQL INTERSECT
和 UNION 指令類似, INTERSECT 也是對兩個 SQL 語句所產(chǎn)生的結(jié)果做處理的。不同的地方是, UNION 基本上是一個 OR (如果這個值存在于第一句或是第二句,它就會被選出),而 INTERSECT 則比較像 AND ( 這個值要存在于第一句和第二句才會被選出)。 UNION 是聯(lián)集,而 INTERSECT 是交集。
INTERSECT 的語法如下:
[SQL語句 1]
INTERSECT
[SQL語句 2]
假設(shè)我們有以下的兩個表格,
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
Internet Sales 表格 Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
Jan-12-1999 $750
而我們要找出哪幾天有店面交易和網(wǎng)絡(luò)交易。要達到這個目的,我們用以下的 SQL 語句:
SELECT Date FROM Store_Information
INTERSECT
SELECT Date FROM Internet_Sales
結(jié)果:
Date
Jan-07-1999
請注意,在 INTERSECT 指令下,不同的值只會被列出一次。
SQL MINUS
MINUS 指令是運用在兩個 SQL 語句上。它先找出第一個 SQL 語句所產(chǎn)生的結(jié)果,然后看這些結(jié)果有沒有在第二個 SQL 語句的結(jié)果中。如果有的話,那這一筆資料就被去除,而不會在最后的結(jié)果中出現(xiàn)。如果第二個 SQL 語句所產(chǎn)生的結(jié)果并沒有存在于第一個 SQL 語句所產(chǎn)生的結(jié)果內(nèi),那這筆資料就被拋棄。
MINUS 的語法如下:
[SQL 語句 1]
MINUS
[SQL 語句 2]
我們繼續(xù)使用一樣的例子:
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
Internet Sales 表格 Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
Jan-12-1999 $750
而我們要知道有哪幾天是有店面營業(yè)額而沒有網(wǎng)絡(luò)營業(yè)額的。要達到這個目的,我們用以下的 SQL 語句:
SELECT Date FROM Store_Information
MINUS
SELECT Date FROM Internet_Sales
結(jié)果:
Date
Jan-05-1999
Jan-08-1999
"Jan-05-1999", "Jan-07-1999", and "Jan-08-1999" 是 "SELECT Date FROM Store_Information" 所產(chǎn)生的結(jié)果。在這里面,"Jan-07-1999" 是存在于 "SELECT Date FROM Internet_Sales" 所產(chǎn)生的結(jié)果中。因此 "Jan-07-1999" 并不在最后的結(jié)果中。
請注意,在 MINUS 指令下,不同的值只會被列出一次。
SQL CONCATENATE
有的時候,我們有需要將由不同欄位獲得的資料串連在一起。每一種資料庫都有提供方法來達到這個目的:
MySQL: CONCAT()
Oracle: CONCAT(), ||
SQL Server: +
CONCAT() 的語法如下:
CONCAT(字串1, 字串2, 字串3, ...): 將字串1、字串2、字串3,等字串連在一起。請注意,Oracle的CONCAT()只允許兩個參數(shù);換言之,一次只能將兩個字串串連起來。不過,在Oracle中,我們可以用'||'來一次串連多個字串。
來看幾個例子。假設(shè)我們有以下的表格:
Geography 表格 region_name store_name
East Boston
East New York
West Los Angeles
West San Diego
例子1:
MySQL/Oracle:
SELECT CONCAT(region_name,store_name) FROM Geography
WHERE store_name = 'Boston';
結(jié)果:
'EastBoston'
例子2:
Oracle:
SELECT region_name || ' ' || store_name FROM Geography
WHERE store_name = 'Boston';
結(jié)果:
'East Boston'
例子3:
SQL Server:
SELECT region_name + ' ' + store_name FROM Geography
WHERE store_name = 'Boston';
結(jié)果:
'East Boston'
SQL SUBSTRING
SQL 中的 substring 函數(shù)是用來抓出一個欄位資料中的其中一部分。這個函數(shù)的名稱在不同的資料庫中不完全一樣:
MySQL: SUBSTR(), SUBSTRING()
Oracle: SUBSTR()
SQL Server: SUBSTRING()
最常用到的方式如下 (在這里我們用SUBSTR()為例):
SUBSTR(str,pos): 由<str>中,選出所有從第<pos>位置開始的字元。請注意,這個語法不適用于SQL Server上。
SUBSTR(str,pos,len): 由<str>中的第<pos>位置開始,選出接下去的<len>個字元。
假設(shè)我們有以下的表格:
Geography 表格 region_name store_name
East Boston
East New York
West Los Angeles
West San Diego
例1:
SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = 'Los Angeles';
結(jié)果:
's Angeles'
例2:
SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = 'San Diego';
結(jié)果:
'an D'
SQL TRIM
SQL 中的 TRIM 函數(shù)是用來移除掉一個字串中的字頭或字尾。最常見的用途是移除字首或字尾的空白。這個函數(shù)在不同的資料庫中有不同的名稱:
MySQL: TRIM(), RTRIM(), LTRIM()
Oracle: RTRIM(), LTRIM()
SQL Server: RTRIM(), LTRIM()
各種 trim 函數(shù)的語法如下:
TRIM([[位置] [要移除的字串] FROM ] 字串): [位置] 的可能值為 LEADING (起頭), TRAILING (結(jié)尾), or BOTH (起頭及結(jié)尾)。 這個函數(shù)將把 [要移除的字串] 從字串的起頭、結(jié)尾,或是起頭及結(jié)尾移除。如果我們沒有列出 [要移除的字串] 是什么的話,那空白就會被移除。
LTRIM(字串): 將所有字串起頭的空白移除。
RTRIM(字串): 將所有字串結(jié)尾的空白移除。
例1:
SELECT TRIM(' Sample ');
結(jié)果:
'Sample'
例2:
SELECT LTRIM(' Sample ');
結(jié)果:
'Sample '
例3:
SELECT RTRIM(' Sample ');
結(jié)果:
' Sample'
SQL CREATE TABLE
CREATE TABLE 的語法是:
CREATE TABLE "表格名"
("欄位 1" "欄位 1 資料種類",
"欄位 2" "欄位 2 資料種類",
... )
若我們要建立我們上面提過的顧客表格,我們就打入以下的 SQL:
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
SQL CREATE VIEW
視觀表 (Views) 可以被當(dāng)作是虛擬表格。它跟表格的不同是,表格中有實際儲存資料,而視觀表是建立在表格之上的一個架構(gòu),它本身并不實際儲存資料。
建立一個視觀表的語法如下:
CREATE VIEW "VIEW_NAME" AS "SQL 語句"
"SQL 語句" 可以是任何一個我們在這個教材中有提到的 SQL。
來看一個例子。假設(shè)我們有以下的表格:
TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
若要在這個表格上建立一個包括 First_Name, Last_Name, 和 Country 這三個欄位的視觀表,我們就打入,
CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer
現(xiàn)在,我們就有一個叫做 V_Customer 的視觀表:
View V_Customer
(First_Name char(50),
Last_Name char(50),
Country char(25))
我們也可以用視觀表來連接兩個表格。在這個情況下,使用者就可以直接由一個視觀表中找出她要的資訊,而不需要由兩個不同的表格中去做一次連接的動作。假設(shè)有以下的兩個表格: Store_Information 表格 store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
Geography 表格 region_name store_name
East Boston
East New York
West Los Angeles
West San Diego
我們就可以用以下的指令來建一個包括每個地區(qū) (region) 銷售額 (sales) 的視觀表:
CREATE VIEW V_REGION_SALES
AS SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
這就給我們有一個名為 V_REGION_SALES 的視觀表。這個視觀表包含不同地區(qū)的銷售哦。如果我們要從這個視觀表中獲取資料,我們就打入,
SELECT * FROM V_REGION_SALES
結(jié)果:
REGION SALES
East $700
West $2050
SQL CREATE INDEX
索引 (Index) 可以幫助我們從表格中快速地找到需要的資料。
因此,在表格上建立索引是一件有利于系統(tǒng)效率的事。一個索引可以涵蓋一或多個欄位。建立索引的語法如下:
CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)
現(xiàn)在假設(shè)我們有以下這個表格,
TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
若我們要在 Last_Name 這個欄位上建一個索引,我們就打入以下的指令,
CREATE INDEX IDX_CUSTOMER_LAST_NAME
on CUSTOMER (Last_Name)
若我們要在 Last_Name 這個欄位上建一個索引,我們就打入以下的指令,
CREATE INDEX IDX_CUSTOMER_LOCATION
on CUSTOMER (City, Country)
索引的命名并沒有一個固定的方式。通常會用的方式是在名稱前加一個字首,例如 "IDX_" ,來避免與資料庫中的其他物件混淆。另外,在索引名之內(nèi)包括表格名及欄位名也是一個好的方式。
請讀者注意,每個資料庫會有它本身的 CREATE INDEX 語法,而不同資料庫的語法會有不同。因此,在下指令前,請先由資料庫使用手冊中確認正確的語法
SQL ALTER TABLE
在表格被建立在資料庫中后,我們常常會發(fā)現(xiàn),這個表格的結(jié)構(gòu)需要有所改變。常見的改變?nèi)缦拢?
加一個欄位
刪去一個欄位
改變欄位名稱
改變欄位的資料種類
以上列出的改變并不是所有可能的改變。ALTER TABLE 也可以被用來作其他的改變,例如改變主鍵定義。
ALTER TABLE 的語法如下:
ALTER TABLE "table_name"
[改變方式]
[改變方式] 的詳細寫法會依我們想要達到的目標而有所不同。再以上列出的改變中,[改變方式] 如下:
加一個欄位: ADD "欄位 1" "欄位 1 資料種類"
刪去一個欄位: DROP "欄位 1"
改變欄位名稱: CHANGE "原本欄位名" "新欄位名" "新欄位名資料種類"
改變欄位的資料種類: MODIFY "欄位 1" "新資料種類"
以下我們用在CREATE TABLE 一頁建出的 customer 表格來當(dāng)作例子:
customer 表格 欄位名稱 資料種類
First_Name char(50)
Last_Name char(50)
Address char(50)
City char(50)
Country char(25)
Birth_Date date
第一,我們要加入一個叫做 "gender" 的欄位。這可以用以下的指令達成:
ALTER table customer add Gender char(1)
這個指令執(zhí)行后的表格架構(gòu)是:
customer 表格 欄位名稱 資料種類
First_Name char(50)
Last_Name char(50)
Address char(50)
City char(50)
Country char(25)
Birth_Date date
Gender char(1)
接下來,我們要把 "Address" 欄位改名為 "Addr"。這可以用以下的指令達成:
ALTER table customer change Address Addr char(50)
這個指令執(zhí)行后的表格架構(gòu)是:
customer 表格 欄位名稱 資料種類
First_Name char(50)
Last_Name char(50)
Addr char(50)
City char(50)
Country char(25)
Birth_Date date
Gender char(1)
再來,我們要將 "Addr" 欄位的資料種類改為 char(30)。這可以用以下的指令達成:
ALTER table customer modify Addr char(30)
這個指令執(zhí)行后的表格架構(gòu)是:
customer 表格 欄位名稱 資料種類
First_Name char(50)
Last_Name char(50)
Addr char(30)
City char(50)
Country char(25)
Birth_Date date
Gender char(1)
最后,我們要刪除 "Gender" 欄位。這可以用以下的指令達成:
ALTER table customer drop Gender
這個指令執(zhí)行后的表格架構(gòu)是:
customer 表格
欄位名稱 資料種類
First_Name char(50)
Last_Name char(50)
Addr char(30)
City char(50)
Country char(25)
Birth_Date date
SQL 主鍵
主鍵 (Primary Key) 中的每一筆資料都是表格中的唯一值。換言之,它是用來獨一無二地確認一個表格中的每一行資料。主鍵可以是原本資料內(nèi)的一個欄位,或是一個人造欄位 (與原本資料沒有關(guān)系的欄位)。主鍵可以包含一或多個欄位。當(dāng)主鍵包含多個欄位時,稱為組合鍵 (Composite Key)。
主鍵可以在建置新表格時設(shè)定 (運用 CREATE TABLE 語句),或是以改變現(xiàn)有的表格架構(gòu)方式設(shè)定 (運用 ALTER TABLE)。
以下舉幾個在建置新表格時設(shè)定主鍵的方式:
MySQL:
CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));
Oracle:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
SQL Server:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
以下則是以改變現(xiàn)有表格架構(gòu)來設(shè)定主鍵的方式:
MySQL:
ALTER TABLE Customer ADD PRIMARY KEY (SID);
Oracle:
ALTER TABLE Customer ADD PRIMARY KEY (SID);
SQL Server:
ALTER TABLE Customer ADD PRIMARY KEY (SID);
請注意,在用ALTER TABLE語句來添加主鍵之前,我們需要確認被用來當(dāng)做主鍵的欄位是設(shè)定為 『NOT NULL』 ;也就是說,那個欄位一定不能沒有資料。
SQL 外來鍵
外來鍵是一個(或數(shù)個)指向另外一個表格主鍵的欄位。外來鍵的目的是確定資料的參考完整性(referential integrity)。換言之,只有被準許的資料值才會被存入資料庫內(nèi)。
舉例來說,假設(shè)我們有兩個表格:一個 CUSTOMER 表格,里面記錄了所有顧客的資料;另一個 ORDERS 表格,里面記錄了所有顧客訂購的資料。在這里的一個限制,就是所有的訂購資料中的顧客,都一定是要跟在 CUSTOMER 表格中存在。在這里,我們就會在 ORDERS 表格中設(shè)定一個外來鍵,而這個外來鍵是指向 CUSTOMER 表格中的主鍵。這樣一來,我們就可以確定所有在 ORDERS 表格中的顧客都存在 CUSTOMER 表格中。換句話說,ORDERS表格之中,不能有任何顧客是不存在于 CUSTOMER 表格中的資料。
這兩個表格的結(jié)構(gòu)將會是如下:
CUSTOMER 表格 欄位名 性質(zhì)
SID 主鍵
Last_Name
First_Name
ORDERS 表格 欄位名 性質(zhì)
Order_ID 主鍵
Order_Date
Customer_SID 外來鍵
Amount
在以上的例子中,ORDERS 表格中的 customer_SID 欄位是一個指向 CUSTOMERS 表格中 SID 欄位的外來鍵。
以下列出幾個在建置 ORDERS 表格時指定外來鍵的方式:
MySQL:
CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));
Oracle:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date date,
Customer_SID integer references CUSTOMER(SID),
Amount double);
SQL Server:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);
以下的例子則是藉著改變表格架構(gòu)來指定外來鍵。這里假設(shè) ORDERS 表格已經(jīng)被建置,而外來鍵尚未被指定:
MySQL:
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);
Oracle:
ALTER TABLE ORDERS
ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);
SQL Server:
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);
SQL DROP TABLE
有時候我們會決定我們需要從數(shù)據(jù)庫中清除一個表格。事實上,如果我們不能這樣做的話,那將會是一個很大的問題,因為數(shù)據(jù)庫管理師 (Database Administrator -- DBA) 勢必?zé)o法對數(shù)據(jù)庫做有效率的管理。還好,SQL 有提供一個 DROP TABLE的語法來讓我們清除表格。 DROP TABLE 的語法是:
DROP TABLE "表格名"
我們?nèi)绻宄赟QL CREATE 中建立的顧客表格,我們就打入:
DROP TABLE customer.
SQL TRUNCATE TABLE
有時候我們會需要清除一個表格中的所有資料。要達到者個目的,一種方式是我們在 SQL DROP 那一頁 看到的 DROP TABLE 指令。不過這樣整個表格就消失,而無法再被用了。另一種方式就是運用 TRUNCATE TABLE 的指令。在這個指令之下,表格中的資料會完全消失,可是表格本身會繼續(xù)存在。 TRUNCATE TABLE 的語法為下:
TRUNCATE TABLE "表格名"
所以,我們?nèi)绻宄?SQL Create 那一頁建立的顧客表格之內(nèi)的資料,我們就打入:
TRUNCATE TABLE customer.
SQL INSERT INTO
到目前為止,我們學(xué)到了將如何把資料由表格中取出。但是這些資料是如果進入這些表格的呢? 這就是這一頁 (INSERT INTO) 和下一頁 (UPDATE) 要討論的。
基本上,我們有兩種作法可以將資料輸入表格中內(nèi)。一種是一次輸入一筆,另一種是一次輸入好幾筆。 我們先來看一次輸入一筆的方式。
依照慣例,我們先介紹語法。一次輸入一筆資料的語法如下:
INSERT INTO "表格名" ("欄位1", "欄位2", ...)
VALUES ("值1", "值2", ...)
假設(shè)我們有一個架構(gòu)如下的表格:
Store_Information 表格
Column Name Data Type
store_name char(50)
Sales float
Date datetime
而我們要加以下的這一筆資料進去這個表格:在 January 10, 1999,Los Angeles 店有 $900 的營業(yè)額。我們就打入以下的 SQL 語句:
INSERT INTO Store_Information (store_name, Sales, Date)
VALUES ('Los Angeles', 900, 'Jan-10-1999')
第二種 INSERT INTO 能夠讓我們一次輸入多筆的資料。跟上面剛的例子不同的是,現(xiàn)在我們要用 SELECT 指令來指明要輸入表格的資料。如果您想說,這是不是說資料是從另一個表格來的,那您就想對了。一次輸入多筆的資料的語法是:
INSERT INTO "表格1" ("欄位1", "欄位2", ...)
SELECT "欄位3", "欄位4", ...
FROM "表格2"
以上的語法是最基本的。這整句 SQL 也可以含有 WHERE、 GROUP BY、 及 HAVING 等子句,以及表格連接及別名等等。
舉例來說,若我們想要將 1998 年的營業(yè)額資料放入 Store_Information 表格,而我們知道資料的來源是可以由 Sales_Information 表格取得的話,那我們就可以打入以下的 SQL:
INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998
在這里,我用了 SQL Server 中的函數(shù)來由日期中找出年。不同的數(shù)據(jù)庫會有不同的語法。 舉個例來說,在 Oracle 上,您將會使用 WHERE to_char(date,'yyyy')=1998。
SQL UPDATE
我們有時候可能會需要修改表格中的資料。在這個時候,我們就需要用到 UPDATE 指令。這個指令的語法是:
UPDATE "表格名"
SET "欄位1" = [新值]
WHERE {條件}
最容易了解這個語法的方式是透過一個例子。假設(shè)我們有以下的表格:
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
我們發(fā)現(xiàn)說 Los Angeles 在 01/08/1999 的營業(yè)額實際上是 $500,而不是表格中所儲存的 $300,因此我們用以下的 SQL 來修改那一筆資料:
UPDATE Store_Information
SET Sales = 500
WHERE store_name = "Los Angeles"
AND Date = "Jan-08-1999"
現(xiàn)在表格的內(nèi)容變成:
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $500 Jan-08-1999
Boston $700 Jan-08-1999
在這個例子中,只有一筆資料符合 WHERE 子句中的條件。如果有多筆資料符合條件的話,每一筆符合條件的資料都會被修改的。
我們也可以同時修改好幾個欄位。這語法如下:
UPDATE "表格"
SET "欄位1" = [值1], "欄位2" = [值2]
WHERE {條件}
SQL DELETE FROM
在某些情況下,我們會需要直接由數(shù)據(jù)庫中去除一些資料。這可以藉由 DELETE FROM 指令來達成。它的語法是:
DELETE FROM "表格名"
WHERE {條件}
以下我們用個實例說明。假設(shè)我們有以下這個表格:
Store_Information 表格
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
而我們需要將有關(guān) Los Angeles 的資料全部去除。在這里我們可以用以下的 SQL 來達到這個目的:
DELETE FROM Store_Information
WHERE store_name = "Los Angeles"
現(xiàn)在表格的內(nèi)容變成:
Store_Information 表格
store_name Sales Date
San Diego $250 Jan-07-1999
Boston $700 Jan-08-1999