SQL 教學指南
Part II
![[Illustration]](../../common/images/illustration35.gif)
內容摘要：
在這份 SQL 教學指南 part II 裡面， 我們將會學習幾個基本的 SQL 指令。
序論
這是 SQL 課程的第二個部份， 在這裡， 我們會將焦點集中在幾個 SQL
指令的用法， 包含表格的建立、 更改、 以及刪除。
除了以上提到的這些， 我們還會特別注意 SELECT 指令的用法，
在我的判斷裡面， 這是所有指令裡面最重要的一個。
希望你會喜歡這篇文章， 也希望你能夠從中獲得一些益處。
建立表格
我們在前一篇， 講安裝的時候看過，可以用 CREATE 指令加上
TABLE 定義式 (qualifier) 來建立表格， 事實上 CREATE 指令，
可以用來建立：
- 使用者 (users)： CREATE USER
- 表格 (tables)： CREATE TABLE
- 索引 (indexes)： CREATE INDEX
- 視界 (views)： CREATE VIEW
CREATE 指令會告訴 SQL 管理者準備要建立某些東西了，
待會兒， 我們再來看看有哪些東西， 以及要如何建。
不過， 我們現在的興趣， 在於利用 CREATE 指令來建立表格：
語法
CREATE TABLE name ( column type [DEFAULT value] [NOT NULL], ...
[INHERITS (inherits, ...)]
[CONSTRAINT constraints CHECK (test), CHECK (test)] );
其中：
| name: |
給予表格的名稱， 後續的指令， 都用這個名稱來參考這個表格。 |
| Column: |
欄位 (column) 的名稱。 |
| Type: |
資料的型別 (varchar, char, int, date, time, timestamp)，
Postgres 有其它資料的型別，不過這些型別並不相容於 ANSI SQL。 |
| Value: |
欄位的預設值。 |
| Inherits: |
這是 Postgres 特有的參數， 用來定義目前表格是從另一個表格繼承而來的，
新建的表格除了有我們建立的欄位以外，還會有繼承而來的欄位。 |
| Nom_cons: |
定義修改任一筆資料時所需符合的完整性規則 (rule of integrity)。 |
| Test: |
檢查的條件。 |
範例：
CREATE TABLE countries (
cod_country integer NOT NULL,
name varchar(30))
CONSTRAINT cod_raro CHECK (cod_country > 0 AND cod_country < 154);
這個範例裡面建立了一個名為 "countries"
的表格，而且每次插入新的一筆資料時，
都必須符合底下的這些規則：
註釋：
甚麼是 NULL 呢? 在 SQL 裡面， 存在著「有資料」跟「沒有資料」兩種狀態，
實際上， 我們有時候也會對表格中， 欄位裡沒有資料的那些紀錄感到興趣；
而諸如「零」 (0) 以及「空白」 (spaces) 等， 都算是資料， 不算是沒有資料。
SQL 引進了 NULL 的概念， 並應用這種做法， 底下便是一個實際的例子：
假設我有一個存放帳單的表格， 它裡面有下列的欄位：
customer, value, date_issued, date_paid
當建立一筆資料的時候， 我會插入這些資料：
customer, value, date_issued
所以一開始我讓 date_paid 欄位是空白的，
然後藉著底下指令， 我就可以找到需要去付錢的帳單囉：
SELECT * FROM bills WHERE date_paid IS NULL;
有些人可能會主張， 可以在 date_paid 欄位放一個零 (0)
來代表尚未付錢， 沒錯， 不過零 (0) 並不是一個日期的格式，
用零來代表尚未付錢的話， 就沒有辦法把欄位 date_paid
設成日期格式， 也就不能發揮「日期」的適當功能了。
以 NULL 建立資料的例子：
insert into countries values (15, NULL);
或：
insert into countries (cod_country) values (27);
在插入指令裡面沒有給定 "name" 欄位的值， 代表它將會被設定為 NULL。
更改表格
在 PostgreSQL 裡面， 表格建立以後要再變更的話， 就只能增加新的欄位：
ALTER TABLE table ADD name type;
其中：
| Table |
要更改的表格的名稱。 |
| Name |
要增加的欄位名稱。 |
| Type |
此欄位的資料型別 (參考 CREATE TABLE) |
在表格中插入資料
現在我們來將資料插入表格裡面：
語法：
INSERT INTO table [(column, column, ...)] VALUES (value-1, value-2,
...)
或者：
INSERT INTO table [(column, column, ...)] SELECT ....
如同上述所列， 在表格中插入資料的方式有兩種， 一種是給定各個欄位的值、
一行一行的插入， 一種是利用一個 SELECT 指令， 把選出的一筆或多筆的資料，
都插入表格裡面。
當我們在表格裡面插入資料的時候， 並不一定要指定所有欄位的值，
不過對於那些我們沒有給定內容的欄位， 系統會自動的把它的值設為 NULL。
又， 在 INSERT 指令裡面若沒有指定欄位的話，系統會認為我們將會在
VALUES 裡面給與所有欄位的值，例如：
INSERT INTO countries VALUES (34, 'Spain');
底下這個指令就下錯了：
INSERT INTO countries VALUES (34);
下面這樣子才正確：
INSERT INTO countries (cod_country) VALUES (34);
我建議所有內含在 C 程式、 或是資料庫相關函式， 裡面的 SQL 指令，
都能夠指定所要用到的欄位， 事實上， 假如有人在表格裡面，
增加一個新的欄位的話 (ALTER TABLE)，
沒有指明欄位的 insert 指令就會發生錯誤， 例如：
INSERT INTO countries VALUES (34, 'Spain');
INSERT 18301 1
ALTER TABLE countries add population integer
INSERT INTO countries VALUES (34, 'Spain');
上面的指令就會造成解譯器錯誤 (parser error)， 告訴您缺少了
population 的資料。
註釋：
PostgreSQL 並不會產生錯誤， 它會讓欄位 "population" 變成 NULL。
這只是 PostgreSQL 的特性， 其他的 SQL 程管理程式都會產生錯誤。
我們還有另一種類型的 INSERT 指令，
它是在 sub-select 功能中使用。
這種類型的 insert 常被用在建立暫存表格，
或是用在執行測試性運算工作的表格上。
上述的功能， 會把符合條件的資料，
The part replaced is that which touches the data itself, this
comes from the SELECT instructions that were carried out
previously and the inserting of the data. The instruction
SELECT can return one or more rows, this instruction
SELECT has the same restrictions of the same
SELECT.
選出資料
到了我渴望說明的地方囉 ! :-))
我們已經說明了好幾個必要的 SQL 指令， 不過假如 SQL 語言少掉了
SELECT， 那就好像吃油條不配燒餅一樣， 無味了。
SELECT 指令允許我們藉由一些搜尋規則， 來獲取資料庫內的資料，
不過， 諸如檢查條件、 表格間的聯集關係、 處理資料的函式、
或是能夠用來搜尋的規則本身， 都會對其產生一些限制。
一個例子：
select * from countries;
另一個例子：
SELECT a.name, SUM(population)
FROM countries a, states b, counties c
WHERE b.cod_country = a.cod_country
AND (c.cod_country = b.cod_country
AND c.state_code = b.state_code)
AND population IS NOT NULL
GROUP BY a.name
ORDER BY sum ASC;
讓我解釋一下， 我想要知道所有「國家」的「人口數」，
並依人口多寡從小到大排列好。
為了上述的功能， 我在表格 countries 中， 新增了一個欄位 (population)。
整個過程大致上像這樣：
create table counties (cod_country int,
state_code int,
county_code int,
county_name varchar(60),
population int);
insert into counties values (1, 1, 1, 'Country 1, State 1, County 1',
5435);
insert into counties values (2, 1, 1, 'Country 2, State 1, County 1',
7832);
insert into counties values (3, 1, 1, 'Country 3, State 1, County 1',
4129);
insert into counties values (1, 2, 1, 'Country 1, State 2, County 1',
76529);
insert into counties values (2, 2, 1, 'Country 2, State 2, County 1',
9782);
insert into counties values (3, 2, 1, 'Country 3, State 2, County 1',
852);
insert into counties values (1, 3, 1, 'Country 1, State 3, County 1',
3433);
insert into counties values (2, 3, 1, 'Country 2, State 3, County 1',
7622);
insert into counties values (3, 3, 1, 'Country 3, State 3, County 1',
2798);
insert into counties values (1, 1, 2, 'Country 1, State 1, County 2',
7789);
insert into counties values (2, 1, 2, 'Country 2, State 1, County 2',
76511);
insert into counties values (3, 1, 2, 'Country 3, State 1, County 2',
98);
insert into counties values (1, 2, 2, 'Country 1, State 2, County 2',
123865);
insert into counties values (2, 2, 2, 'Country 2, State 2, County 2',
886633);
insert into counties values (3, 2, 2, 'Country 3, State 2, County 2',
982345);
insert into counties values (1, 3, 2, 'Country 1, State 3, County 2',
22344);
insert into counties values (2, 3, 2, 'Country 2, State 3, County 2',
179);
insert into counties values (3, 3, 2, 'Country 3, State 3, County 2',
196813);
insert into counties values (1, 1, 3, 'Country 1, State 1, County 3',
491301);
insert into counties values (2, 1, 3, 'Country 2, State 1, County 3',
166540);
insert into counties values (3, 1, 3, 'Country 3, State 1, County 3',
165132);
insert into counties values (1, 2, 3, 'Country 1, State 2, County 3',
0640);
insert into counties values (2, 2, 3, 'Country 2, State 2, County 3',
65120);
insert into counties values (3, 2, 3, 'Country 3, State 2, County 3',
1651462);
insert into counties values (1, 3, 3, 'Country 1, State 3, County 3',
60650);
insert into counties values (2, 3, 3, 'Country 2, State 3, County 3',
651986);
insert into counties values (3, 3, 3, 'Country 3, State 3, County 3',
NULL);
commit work;
現在， 我們不能以 ALTER TABLE 來完成它，
但是可以使用 UPDATE， 這功能我還沒解釋到， 所以只好先使用 "剪貼"
將就一下， 大家應該都能滿意吧 :-))
我們可以執行 QUERY 功能， 而其結果應該會像這樣：
name | sum
- ---------+-------
country 1| 705559
country 2|1212418
country 3|2804018
(3 rows)
我們可以執行下列的確認動作：
select sum(population) from counties where cod_country = 1;
結果會是：
sum
- ------
791986
(1 row)
!!!!!! 居然有地方不一樣 !!!!!!
讓我們看看表格 states 裡， 少了 state 3 的資料， 所以請再執行：
INSERT INTO states VALUES (3, 1, 'State 3, Country 1');
INSERT INTO states VALUES (3, 2, 'State 3, Country 2');
INSERT INTO states VALUES (3, 3, 'State 3, Country 3');
再做一次確認檢查， 結果如下：
name | sum
- ---------+-------
country 1| 791986
country 2|1872205
country 3|3003629
我們每個國家都少了 state 3 的資料。
由於少了這些資料， 表格之間的結合關係是 EXACT，
要記得指定測試條件為完全符合， 才能取得資料。
讓我們看看 WHERE: b.cod_country = a.cod_country 的第一個部份。
這表示， 我將把 country 與 states 兩個表格中， 國家代碼相同的給結合起來，
請先把我們輸入進去的國家資料給記起來：
下列的範例僅供解釋之用， 請不要照做。
create table countries (cod_country integer, name varchar(30));
insert into countries values (1, 'country 1');
insert into countries values (2, 'country 2');
insert into countries values (3, 'country 3');
commit work;
states 的資料部份：
create table states (state_code int,
cod_country int,
state_name varchar(30));
insert into states values (1, 1, 'State 1, Country 1');
insert into states values (2, 1, 'State 2, Country 1');
insert into states values (1, 2, 'State 1, Country 2');
insert into states values (2, 2, 'State 2, Country 2');
insert into states values (1, 3, 'State 1, Country 3');
insert into states values (2, 3, 'State 2, Country 3');
commit work;
All of the states 3 from every country are missing, but in the country
table the corresponding data of the states 3, so it is normal that
we don't add the country data with the states with code 3 to be
discarded in the second part where:
AND (c.cod_country = b.cod_country
AND c.state_code = b.state_code)
The state exists in the counties table but NOT in the states
table.
For those that haven't understood, take an aspirin, go walk your
dog
(if you don't have a dog, go walk without a dog), breath a little
fresh
air and comeback to begin from the first exercise.
Is is very important to understand how the joining of data is
carried
out, without this the development that we do can have unpredictable
results.
Let's shift gears and start with the SELECT command syntax.
SELECT [DISTINCT] expression1 [AS nom-attribute] {, expression-i [as
nom-attribute-i]}
[INTO TABLE classname]
[FROM from-list]
[WHERE where-clause]
[GROUP BY attr_name1 {, attr_name-i....}]
[ORDER BY attr_name1 [ASC | DESC ] [USING op1 ] {,
nom-attribute-i...}]
[UNION {ALL} SELECT ...]
Step by step:
| DISTINCT: |
This is to eliminate duplicate row returned |
| expression1: |
what we want returned, normally a column for a table from the
list FROM |
| AS nom-attribute: |
an alias for the name of a column, example:
manu=> select cod_country from countries;
cod_country
- -----------
1
2
3
(3 rows)
Now with the alias:
manu=> select cod_country as countr from countries;
countr
- ------
1
2
3
(3 rows)
|
| INTO TABLE: |
allow the resulting row to be inserted directly into another table
(see INSERT ... SELECT...) |
| FROM: |
list of input tables |
| WHERE: | selection statement (union
and selection criteria). |
| GROUP BY: |
group criteria, certain functions that
are used in expressions might need grouping
|
| ORDER BY: |
Order criteria of the returned
rows, ASC ascending order, DESC descending order, USING if the column
that defines the order is not in the list (expression) |
| UNION ALL SELECT: |
This says to add to the
results of the first SELECT this second SELECT that can be
different tables, but return the same number of columns. |
我們見識到， SELECT 指令不僅能夠傳回資料庫裡的項目，
還可以修改它們：
SELECT SUM(salary * 1.1) - SUM(salary) AS increment FROM
employees;
如果將 salary 增加 10%， 這會傳回其新增的付款部份。
讓我們來看一看， 這裡提供了哪些函式功能：
| COUNT(): |
回傳其值不為 NULL 的資料筆數。
|
| SUM(): |
回傳某個數字欄位的總和。
|
| AVG(): |
回傳某個數字欄位的平均值。
|
| MIN(): |
回傳某個欄位中， 數字最小的值。
|
| MAX(): |
回傳某個欄位中， 數字最小的值。
|
| FLOAT(int): |
回傳一個倍精度浮點數 FLOAT8, FLOAT(12345)。
|
| FLOAT4(int): |
回傳一個單精度浮數 FLOAT4, FLOAT4(12345)。
|
| INT(float): |
回傳一個從單精度浮點數 (FLOAT4) 轉來的整數， 如
INT(123.456)。
|
| LOWER(text): |
把字串 text 變成小寫。 |
| UPPER(text): |
把字串 text 變成大寫。 |
| LPAD(text, long, char): |
用 char 填到欄位資料 text 的左邊，
使總長度成為 long 個字元。
|
| RPAD(text, long, char): |
用 char 填到欄位資料 text 的右邊，
使總長度成為 long 個字元。
|
| LTRIM(text, char): |
把欄位資料 text 左邊所有的字元 char 全部移除。
|
| RTRIM(text, char): |
把欄位資料 text 右邊所有的字元 char 全部移除。
|
| POSITION(string IN text): |
傳回在欄位 text 中， string 的位置，
不過現在不能用。
|
| SUBSTR(text,from[,to]): |
傳回在欄位 text 中， 從位置 from 到位置 to，
或到字串的最後面。
|
| DATETIME(date, hour): |
把一個「日期時間」格式， 轉換成一個「日期」(YYYY-MM-DD)
跟一個「時間」(HH:MM)。
|
這僅是 SQL 提供的函式中的一小部份， 而且這幾個函式在 ANSI SQL
裡面有定義， 在 Postgres95 裡面也可以找到。
使用 WHERE 的細節
到目前為止， 我們己經看到， 在 SELECT 中的 WHERE 部份，
可以加入這樣的東西：
AND column = value
上面是個小範例， 可供使用或組合的選項計有：
AND, OR, NOT, IN, IN ALL, =, !=, >, <, (SELECT....),
LIKE 或「左右括號」也在其列， 底下是範例：
WHERE
column IN (SELECT DISTINCT column FROM table WHERE ....)
column IN ('value1','value2','value3',...)
(column = 'value' and column = 'other_value' OR column != 'value')
!= 這個符號同義於不等於 (NOT EQUAL)
LIKE 允許在欄位中尋找字串時， 使用萬用符號：
WHERE column LIKE '%Pepito%'
% 在本例中是個萬用符號， 如果字串中含有 "Pepito"，
它會傳回「真」。
WHERE column LIKE 'Pepito%'
如果字串的開頭是 "Pepito"， 它會傳回「真」。
WHERE column LIKE '%Pepito'
如果字串的結尾是 "Pepito"， 它會傳回「真」。
在這裡， 我們沒有充份的時間， 去列出所有和 WHERE 有關的選項，
其限制是來自於程式設計員的想像力， 或是各別之程序分析。
現在我們把 SELECT 和其相關的語法， 放到後面兩節再談論。
UPDATE 指令
UPDATE 這個指令， 允許我們修改表格中一個或多個列 (row)，
取決於 WHERE 中所定義的條件為何。
語法：
UPDATE table SET column-1 = expression-1
[, column-i = expression-i]
[WHERE condition]
其中：
| table: |
想要修改的表格 (table) 名稱，同一時間內只能改一個表格。
|
| column: |
想要修改的欄位 (column)。
|
| expression: |
想要傳給欄位 (column) 的值， 傳送的值， 可以是函數 (function)
的結果或是固定的數字。
|
| condition: |
condition 定義了可修改的範圍， 而且這裡的規則 (rules) 也適用於
SELECT 。
|
DELETE 指令
DELETE 這個指令允許我們修改表格 (table) 中一個或多個列 (row) 。
語法
DELETE FROM table
[WHERE condition]
其中：
| table: |
表格中的列會被刪除掉， 在同一時間內只能選一個表格。
|
| condition: |
condition 定義了可修改的範圍， 而且這裡的規則 (rules) 也適用於
SELECT。
注意： 如果沒有加入 WHERE 的話， 那在表格中所有的列， 將會被刪掉。
|
|
![[Top
Bar]](../../common/images/../../common/images/Topbar-fr.gif){kind=small}
![[Bottom Bar]](../../common/images/../../common/images/Bottombar-fr.gif){kind=small}
![[Photo not available]](../../common/images/Manuel-S.gif)