Transcript Ch06 結構化查詢語言SQL(一)

資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
資料庫系統理論與實務 [邏輯思維系列]
©2007版權所有，翻印必究
各種系統之基本邏輯單位(1/4)
資
料
庫
系
統
理
論
與
實
務
• 檔案系統(File System)
– 檔案(File)
• 欄位(Fields)
• 紀錄(Record)
陳
祥
輝
著
• 關聯式資料模型(Relational Data Model)
• 關聯式資料庫管理系統(RDBMS)
續
下
頁
第六章 結構化查詢語言SQL(一)
2 /107
©2007版權所有，翻印必究
各種系統之基本邏輯單位(2/4)
資
料
庫
系
統
理
論
與
實
務
• 檔案系統(File System)
• 關聯式資料模型(Relational Data Model)
– 關聯(Relation)
• 屬性(Attribute)
• 值組(Tuple)
陳
祥
輝
著
• 關聯式資料庫管理系統(RDBMS)
續
下
頁
第六章 結構化查詢語言SQL(一)
3 /107
©2007版權所有，翻印必究
各種系統之基本邏輯單位(3/4)
資
料
庫
系
統
理
論
與
實
務
• 檔案系統(File System)
• 關聯式資料模型(Relational Data Model)
• 關聯式資料庫管理系統(RDBMS)
– 資料表(Table)
陳
祥
輝
著
• 行(Column)
• 列(Row)
續
下
頁
第六章 結構化查詢語言SQL(一)
4 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
相關名詞比較
檔案系統
(File System)
關聯式資料模型
(Relational Data Model)
關聯式資料庫管理系統
(RDBMS)
檔案(File)
關聯(Relation)
資料表(Table)
欄位(Fields)
屬性(Attribute)
行(Column)
紀錄(Record)
值組(Tuple)
列(Row)
表一：相關名詞比較
第六章 結構化查詢語言SQL(一)
5 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
Table 與 View
陳
祥
輝
著
• 『檢視表』 (Views)
• 『資料表』 (Tables)
– 實際儲存資料內容的地方
– 以二維的方形表格來表達
–
–
–
–
也稱為『虛擬資料表』 (Virtual Tables)
『檢視表』和『資料表』非常類似
檢視表本身並不儲存任何的資料內容
查詢是自最下層的資料表，或是經由其他檢視
表再自下層的資料表所取得的資料
第六章 結構化查詢語言SQL(一)
6 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
View與Table的關係圖
Z
檢視表Z，它的直接來
檢視表W，是源自於單
檢視表Y，直接來源是
檢視表X，是源自於兩
個(或更多)資料表B與C
一個資料表A
兩個(或更多)檢視表W
源是合併一個(或更多)
和X
檢視表和兩個(或更多)
的『合併』(Join)處理
資料表後
Join
Y
Join
views
W
X
Join
tables
A
B
Join
C
D
E
圖6-2 View與Table的關係圖
第六章 結構化查詢語言SQL(一)
7 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
View的實際範例
Join
陳
祥
輝
著
Views
男業務的
訂單資料
女業務
男業務
tables
員工
訂單
客戶
圖6-3 View的範例
第六章 結構化查詢語言SQL(一)
8 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
結構化程式語言簡介
•
•
•
•
Structured Query Language
簡稱『SQL』，唸成Sequel
源自於1970年IBM公司的一項研究所延伸
由美國國家標準局(the American National
Standards Institute，簡稱ANSI)和國際標準
組織(International Standards Organization，
簡稱ISO)訂定為國際依據之標準
第六章 結構化查詢語言SQL(一)
9 /107
©2007版權所有，翻印必究
結構化程式語言
資
料
庫
系
統
理
論
與
實
務
• 資料定義語言
– Data Definition Language，簡稱DDL
– 建立與維護資料庫內的相關物件
– 包括綱要(Schema)、資料表(Tables)、檢視表
(Views) …等等
陳
祥
輝
著
• 資料操作語言
續
下
頁
第六章 結構化查詢語言SQL(一)
10 /107
©2007版權所有，翻印必究
結構化程式語言
資
料
庫
系
統
理
論
與
實
務
• 資料定義語言
• 資料操作語言
– Data Manipulation Language，簡稱DML
– 對資料的存取操作(Operations) ，包括對資料的
陳
祥
輝
著
•
•
•
•
新增 ( Insert )
刪除 ( Delete )
修改 ( Update )
查詢 ( Select )
續
下
頁
第六章 結構化查詢語言SQL(一)
11 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
6-2 資料定義語言(DDL)
• 『定義』以下物件
–
–
–
–
–
資料庫(Database)
綱要(Schema)
資料表(Table)
檢視表(View)
其他不同物件
• 定義的順序
– 父資料表先於子資料表
– 如下頁圖
第六章 結構化查詢語言SQL(一)
12 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
資料庫ERD之父子關係
(
建
立
順
序
由
上
而
下
)
圖6-4
資料庫ERD之主從關係
第六章
結構化查詢語言SQL(一)
13 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
定義『綱要』(Schema)
•
•
目的在於將不同系統或不同用途的相關物
件做一區隔或分類，達到管理上的方便
在存取控制上的適當授權，達到資料保密
上的安全性，避免未授權的使用者讀取或
異動其中的資料
第六章 結構化查詢語言SQL(一)
14 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
定義『綱要』之基本語法
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
15 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
【範例6-1】(1/4)
陳
祥
輝
著
• 【語法】
• 為人力資源部門建立一獨立的綱要
• 【說明】
– (語法一)
– 僅以最簡單方式建立一名為『HumanResource』的綱要。
– (語法一)
CREATE SCHEMA HumanResource
第六章 結構化查詢語言SQL(一)
16 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-1】(2/4)
• 為人力資源部門建立一獨立的綱要
• 【說明】
– (語法二)
– 建立一個綱要名稱與使用者 『hrOwner』相同名稱，並授權給予
此相同帳號為 『hrOwner』之使用者 。
• 【語法】
– (語法二)
CREATE SCHEMA AUTHORIZATION hrOwner
未指定schema_name
第六章 結構化查詢語言SQL(一)
17 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-1】(3/4)
• 為人力資源部門建立一獨立的綱要
• 【說明】
– (語法三)
– 建 立 一 個 綱 要 名 稱 為 『HumanResource』 ， 並 授 權 于 帳 號 為
『hrOwner』的使用者。
• 【語法】
– (語法三)
CREATE SCHEMA HumanResource AUTHORIZATION hrOwner
schema_name
第六章 結構化查詢語言SQL(一)
18 /107
username
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-1】(4/4)
• 為人力資源部門建立一獨立的綱要
• 【說明】
– (語法四)
– 建立一個綱要名為 『HumanResouce』，並於建立此綱要時，建
立一個資料表名為『員工』，與一檢視表名為『男員工』。
• 【語法】
– (語法四)
CREATE SCHEMA HumanResource AUTHORIZATION
CREATE TABLE 員工
(
員工編號 int PRIMARY KEY,
性名 varchar(12),
schema_element
性別 char(1)
)
CREATE View 男員工
AS
SELECT * FROM 員工 WHERE 性別 = ‘男’
第六章 結構化查詢語言SQL(一)
19 /107
定義名為
『員工』
之資料表
定義名為
『男員工』
之檢視表
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
刪除『綱要』之基本語法
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
20 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-2】(1/2)
• 請刪除為人力資源部門所建立的綱要
• 【說明】
– (語法一)
– 在綱要內若尚有其他屬性、定義域或元素時，且希望一併刪除
時，可以使用CASCADE的選項。
• 【語法】
– (語法一)
– DROP SCHEMA HumanResource CASCADE
第六章 結構化查詢語言SQL(一)
21 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-2】(2/2)
• 請刪除為人力資源部門所建立的綱要
• 【說明】
– (語法二)
– 在綱要內若沒有任何元素存在，則可以使用RESTRICT的選項。
倘若尚有其他屬性、定義域或元素時，使用RESTRICT的選項
會阻止DROP命令執行，可避免不小心刪除掉綱要內所有的元素。
• 【語法】
– (語法二)
– DROP SCHEMA HumanResource RESTRICT
第六章 結構化查詢語言SQL(一)
22 /107
©2007版權所有，翻印必究
定義『資料表』的限制(1/4)
資
料
庫
系
統
理
論
與
實
務
• 實體限制
– Entity Constraint
– 限制一個資料表內的每一筆紀錄都必須具有唯
一性(Uniqueness)
– 主要鍵具有兩個特性
陳
祥
輝
著
• 『鍵值不可重複』
• 『鍵值不可為空值』
續
下
頁
• 定義域限制
• 參考完整性限制
• 使用者定義限制
第六章 結構化查詢語言SQL(一)
23 /107
©2007版權所有，翻印必究
定義『資料表』的限制(2/4)
資
料
庫
系
統
理
論
與
實
務
• 實體限制
• 定義域限制
– Domain Constraint
– 資料表中的屬性設定一個限制條件
陳
祥
輝
著
• 值域範圍(range)
• 資料型態
• 參考完整性限制
• 使用者定義限制
續
下
頁
第六章 結構化查詢語言SQL(一)
24 /107
©2007版權所有，翻印必究
定義『資料表』的限制(3/4)
資
料
庫
系
統
理
論
與
實
務
• 實體限制
• 定義域限制
• 參考完整性限制
陳
祥
輝
著
– Referential Integrity Constraint
– 兩個資料表之間關聯性(Relationship)的限制
– 『子資料表』與『父資料表』之間的相依關係
• 使用者定義限制
續
下
頁
第六章 結構化查詢語言SQL(一)
25 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
定義『資料表』的限制(4/4)
•
•
•
•
實體限制
定義域限制
參考完整性限制
使用者定義限制
– User-Defined Constraint
– 由使用者依據企業之需求而自訂的其他限制
第六章 結構化查詢語言SQL(一)
26 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
『新增操作』可能違反的限制
• 可能違反的限制
–
–
–
–
實體限制
定義域限制
參考完整性限制
使用者定義
• 解決方式
– 拒絕新增
第六章 結構化查詢語言SQL(一)
27 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
『刪除操作』可能違反的限制
• 可能違反的限制
– 參考完整性限制
• 解決方式
– 拒絕刪除(Restrict)
– 連鎖性刪除(Delete Cascade)
– 將參考到被刪除紀錄之外來鍵值設成空值(Set to
Null Value)
第六章 結構化查詢語言SQL(一)
28 /107
©2007版權所有，翻印必究
連鎖性刪除(Delete Cascade)(1/2)
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
父資料表 - 『訂單』資料表
續
下
頁
第六章 結構化查詢語言SQL(一)
子資料表 - 『訂單明細』資料表
29 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
連鎖性刪除(Delete Cascade)(2/2)
94010201
Delete Cascade
陳
祥
輝
著
父資料表 - 『訂單』資料表
子資料表 - 『訂單明細』資料表
第六章 結構化查詢語言SQL(一)
30 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
『更新操作』可能違反的限制
• 可能違反的限制
– 若非更新主要鍵或外來鍵
• 定義域限制
• 使用者定義限制
– 若更新主要鍵或外來鍵
•
•
•
•
實體限制
定義域限制
參考完整性限制
使用者定義
• 解決方式
– 拒絕更新(Restrict)
– 連鎖性更新(Update Cascade)
– 將參考不到父資料表的外來鍵值設為空值(Set to Null Value)
第六章 結構化查詢語言SQL(一)
31 /107
©2007版權所有，翻印必究
連鎖性更新(Update Cascade)(1/2)
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
父資料表 - 『訂單』資料表
續
下
頁
第六章 結構化查詢語言SQL(一)
子資料表 - 『訂單明細』資料表
32 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
連鎖性更新(Update Cascade)(2/2)
Update Cascade
陳
祥
輝
著
父資料表 - 『訂單』資料表
子資料表 - 『訂單明細』資料表
第六章 結構化查詢語言SQL(一)
33 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
定義資料表之基本語法
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
34 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
資料表之父子關係
(
建
立
順
序
由
上
而
下
)
圖6-4
資料庫ERD之主從關係
第六章
結構化查詢語言SQL(一)
35 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
【範例6-3】
•
–
依圖6-4定義出所有之資料表
建
立
順
序
由
上
而
下
1)
2)
3)
4)
5)
6)
7)
(
陳
祥
輝
著
【題目】
)
•
員工
客戶
供應商
產品類別
產品資料
訂單
訂單明細
【說明】
–
定義資料表的基本語法在第一欄為資料表之屬性，再定
義每個屬性的資料型態，資料型態之後即是該屬性的限
制。
第六章 結構化查詢語言SQL(一)
36 /107
©2007版權所有，翻印必究
1)建立【員工】資料表
資
料
庫
系
統
理
論
與
實
務
• 【員工】資料表之限制說明
– 在員工資料表中，『員工編號』為此資料表之
主要鍵(Primary Key)，『姓名』之屬性值不得為
空值(Null Value)。
陳
祥
輝
著
• 【語法】
續
– (語法一) 將『員工編號』為主要鍵之限制寫在下
方的table level區，並將『姓名』不得為空值之
限制，直接寫在姓名屬性後方。
– (語法二) 將『員工編號』為主要鍵之限制直接寫
在員工編號屬性後方。
下
頁
第六章 結構化查詢語言SQL(一)
37 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
38 /107
©2007版權所有，翻印必究
2)建立【客戶】資料表
資
料
庫
系
統
理
論
與
實
務
• 【客戶】資料表之限制說明
– 在客戶資料表中，『客戶編號』為此資料表之
主要鍵(Primary Key)，『公司名稱』之屬性值不
得為空值(Null Value)。
陳
祥
輝
著
• 【語法】
續
– (語法一) 將『客戶編號』為主要鍵之限制寫在下
方的table level區，並將『公司名稱』不得為空
值之限制，直接寫在公司名稱屬性後方。
– (語法二) 將『客戶編號』為主要鍵之限制直接寫
在客戶編號屬性後方。
下
頁
第六章 結構化查詢語言SQL(一)
39 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
40 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
3)建立【供應商】資料表
陳
祥
輝
著
• 【語法】
• 【供應商】資料表之限制說明
– 在供應商資料表中，除了『供應商編號』為此資料表之主
要鍵(Primary Key)之外，此資料表中的『地址』和『電話』
屬性，不得同時為空值(Null Value)，也就是至少要有一個
屬性有值。
續
– (語法一) 將『供應商編號』為主要鍵之限制寫在下方的
table level區，並將『地址』和『電話』屬性，不得同時為
空值(Null Value)之限制，寫於table level區。
– (語法二) 將『供應商編號』為主要鍵之限制直接寫在供應
商編號屬性後面，但將『地址』和『電話』屬性，不得同
時為空值(Null Value)之限制，仍要寫於table level區，不
得寫於個別的屬性後方。倘若將此限制寫於個別屬性後方，
其語意將成為『地址』和『電話』兩個屬性皆不可為空值
(Null Value)。
下
頁
第六章 結構化查詢語言SQL(一)
41 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
42 /107
©2007版權所有，翻印必究
4)建立【產品類別】資料表
資
料
庫
系
統
理
論
與
實
務
• 【產品類別】資料表之限制說明
– 在產品類別資料表中，『類別編號』為此資料
表之主要鍵(Primary Key)，『類別名稱』之屬性
值不得為空值(Null Value)。
陳
祥
輝
著
• 【語法】
續
– (語法一) 將『類別編號』為主要鍵之限制寫在下
方的table level區，並將『類別名稱』不得為空
值之限制，直接寫在類別名稱屬性後方。
– (語法二) 將『類別編號』為主要鍵之限制直接寫
在類別編號屬性後方。
下
頁
第六章 結構化查詢語言SQL(一)
43 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
44 /107
©2007版權所有，翻印必究
5)建立【產品資料】資料表
資
料
庫
系
統
理
論
與
實
務
• 【產品資料】資料表之限制說明
– 在產品資料的資料表中，『產品編號』為此資料表之主要
鍵(Primary Key)，『類別編號』為此資料表中一個外來鍵，
參考『產品類別』資料表中的主要鍵『類別編號』；『供
應商編號』為此資料表中另一個外來鍵，參考『供應商』
資料表中的主要鍵『供應商編號』；並且庫存量和安全存
量必須設預設值 0。
陳
祥
輝
著
• 【語法】
續
– (語法一) 將『產品編號』為主要鍵及『類別編號』和『供
應商編號』為兩個外來鍵之限制寫在下方的table level區，
並將『庫存量』和『安全存量』設預設值為0之限制，直
接寫在兩個屬性後方。
– (語法二) 將『產品編號』為主要鍵及『類別編號』和『供
應商編號』為兩個外來鍵之限制直接寫在個別屬性後方。
下
頁
第六章 結構化查詢語言SQL(一)
45 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
46 /107
©2007版權所有，翻印必究
6)建立【訂單】資料表
資
料
庫
系
統
理
論
與
實
務
• 【訂單】資料表之限制說明
– 在訂單資料表中，『訂單編號』為此資料表之主要鍵
(Primary Key)，『員工編號』為此資料表中一個外來鍵，
參考『員工』資料表中的主要鍵『員工編號』；『客戶編
號』為此資料表中另一個外來鍵，是參考『客戶』資料表
中的主要鍵『客戶編號』。
陳
祥
輝
著
• 【語法】
– (語法一) 將『訂單編號』為主要鍵及『員工編號』和『客
戶編號』為兩個外來鍵之限制寫在下方的table level區。
– (語法二) 將『訂單編號』為主要鍵及『員工編號』和『客
戶編號』為兩個外來鍵之限制直接寫在個別屬性後方。
續
下
頁
第六章 結構化查詢語言SQL(一)
47 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
48 /107
©2007版權所有，翻印必究
7)建立【訂單明細】資料表
資
料
庫
系
統
理
論
與
實
務
• 【訂單明細】資料表之限制說明
– 『訂單編號』+『產品編號』兩個屬性結合成為此資料表
之主要鍵(Primary Key)。單一個『訂單編號』屬性又剛好
為此資料表中一個外來鍵，參考『訂單』資料表中的主要
鍵『訂單編號』，除了要達到參考完整性限制外，若有違
反此限制時，採用『連鎖刪除』(DELETE CASCADE)和
『連鎖更新』(UPDATE CASCADE)；『產品編號』為此
資料表中另一個外來鍵，參考『產品資料』資料表中的主
要鍵『產品編號』。除此之外，對於數量屬性也將限制必
須大於0。
陳
祥
輝
著
續
下
頁
第六章 結構化查詢語言SQL(一)
49 /107
©2007版權所有，翻印必究
建立【訂單明細】資料表
資
料
庫
系
統
理
論
與
實
務
• 【語法】
陳
祥
輝
著
續
下
頁
– (語法一) 將所有的限制全部寫在下方的table level區。但
是在外來鍵的限制方面，本資料表共有兩個外來鍵，一為
訂單編號參考訂單資料表的訂單編號，且要求在違反限制
時，將使用連鎖刪除和連鎖更新，所以在其參考語法後方
要再加上選項ON DELETE CASCADE ON UPDATE
CASCADE。另一個外來鍵為產品編號參考產品資料資料
表的產品編號，由於沒有特別限制在違反時的處理方式，
預設沒有任何選項時，則為拒絕刪除和拒絕更新。最後為
一個數量的限制必須大於0。
– (語法二) 由於此資料表的主要鍵是由兩個屬性『訂單編號』
和『產品編號』組合而成，所以並不可分別寫在屬性後方，
否則在語意上將解釋為訂單編號為主要鍵，產品編號也是
主要鍵，也就是此資料表有兩個主要鍵，這是不被允許的，
所以只要多於一個屬性所組成的鍵，就必須要置於table
level區，不可分別寫於屬性後方。其他限制都屬於單一個
屬性的限制，所以皆可置於該屬性後面做限制。
第六章 結構化查詢語言SQL(一)
50 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
51 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
刪除資料表之基本語法
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
52 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 刪除順序與建立順序相反
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
53 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
新增INSERT之基本語法
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
54 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-4】(1/2)
• 【範例6-4】
– 新增一筆新的產品資料。
• 【說明】
– (語法一) 將屬性列全部省略不寫，但必須將新增的資料依
資料表內的屬性順序填寫，否則將會造成錯誤。
• 【語法】
– (語法一)
– INSERT INTO 產品資料
VALUES ( 13, 8, ‘S0005’, ‘拿鐵’, 35, 0, 30)
第六章 結構化查詢語言SQL(一)
55 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-4】(2/2)
• 【範例6-4】
– 新增一筆新的產品資料。
• 【說明】
– (語法二) 將屬性列名稱全部列出，其順序只要和所要新增
的屬性值位置相對應即可。
• 【語法】
– (語法二)
– INSERT INTO 產品資料
(產品編號, 類別編號, 供應商編號, 產品名稱, 建議單價,
庫存量, 安全存量)
VALUES ( 13, 8, ‘S0005’, ‘拿鐵’, 35, 0, 30)
第六章 結構化查詢語言SQL(一)
56 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-5】
• 【範例6-5】
– 新增一筆新的產品資料，其中的庫存量與安全存量使用資
料表內的預設值。
• 【說明】
– 此範例只要是說明新增一筆資料時，可以使用保留字
DEFAULT，來達到該屬性是使用資料表內的預設值。
• 【語法】
– INSERT INTO 產品資料 (產品編號, 類別編號, 產品名稱,
建議單價, 庫存量, 安全存量)
VALUES ( 15, 2, ‘S0002’, ‘紅茶拿鐵’, 20, DEFAULT,
DEFAULT)
第六章 結構化查詢語言SQL(一)
57 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
刪除DELETE之基本語法
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
58 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-6】
• 【範例6-6】
– 從『供應商』資料表中，刪除掉供應商編號為S0006的供
應商資料。
• 【說明】
– 此範例是利用DML中的DELETE語法，藉由WHERE的條
件限制，將供應商編號為S0006的資料刪除。
• 【語法】
– DELETE FROM 供應商
WHERE 供應商編號=’S0006’
第六章 結構化查詢語言SQL(一)
59 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-7】
• 【範例6-7】
– 從『供應商』資料表中，刪除所有資料。
• 【說明】
– 此範例可以使用TRUNCATE TABLE的語法將供應商資料表內的
資料全部刪除。但也等同於利用DML中的DELETE語法，而不
使用WHERE的任何限制，亦可將全部資料刪除。
– 但此兩者的差異在於，TRUNCATE並不會將所刪除的執行過程
記錄於交易日誌檔(log file)中，而DELETE的語法，則會將所有
被刪除的資料全部記錄於交易日誌檔中，所以在刪除大量資料
時，TRUNCATE將會顯得較有效率。
• 【語法】
– (語法一)
TRUNCATE TABLE 供應商
– (語法二)
DELETE FROM 供應商
第六章 結構化查詢語言SQL(一)
60 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
更新UPDATE之基本語法
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
61 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-8】
• 【範例6-8】
– 將『產品資料』資料表中的產品編號為13的庫存量更新為
300，安全存量更新為50。
• 【說明】
– 此範例可以利用DML中的UPDATE語法，並使用WHERE
來限制要更新的資料。
• 【語法】
– UPDATE 產品資料
SET 庫存量 = 300, 安全存量 = 50
WHERE 產品編號 = 13
第六章 結構化查詢語言SQL(一)
62 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-9】
• 【範例6-9】
– 將『產品資料』資料表中類別編號為1的所有產品之『建
議單價』更新為原價之80%。
• 【說明】
– 此範例可以利用DML中的UPDATE語法，使用
建議單價 = 建議單價*0.8
計算式，並使用WHERE來限制被更新的資料條件限制。
• 【語法】
– UPDATE 產品資料
SET 建議單價 = 建議單價* 0.8
WHERE 類別編號 = 1
第六章 結構化查詢語言SQL(一)
63 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例6-10】
• 【範例6-10】
– 將『產品資料』資料表中類別編號為1的所有產品之安全
存量全部改成資料表內的預設值。
• 【說明】
– 此範例只要是說明更新一筆資料時，可以使用保留字
DEFAULT，來達到該屬性是使用資料表內的預設值。
• 【語法】
– UPDATE 產品資料
SET 安全存量 = DEFAULT
WHERE 類別編號 = 1
第六章 結構化查詢語言SQL(一)
64 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
查詢SELECT之基本語法
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
65 /107
©2007版權所有，翻印必究
【範例6-11】基本使用與萬用字元查詢
資
料
庫
系
統
理
論
與
實
務
• 【範例6-11】基本使用與萬用字元查詢
– 使用SELECT查詢出所有的產品資料。
• 【說明】
– 在此範例中可使用萬用字元(wild card) * 來代表所有的屬性，或
將所有屬性一一寫出也等同意義。
陳
祥
輝
著
• 【語法】
續
– (語法一)
SELECT *
FROM 產品資料
– (語法二)
SELECT產品編號, 類別編號, 供應商編號, 產品名稱, 建議單價,
庫存量, 安全存量
FROM 產品資料
下
頁
第六章 結構化查詢語言SQL(一)
66 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【結果】
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
67 /107
©2007版權所有，翻印必究
【範例6-12】基本屬性輸出之查詢
資
料
庫
系
統
理
論
與
實
務
• 【範例6-12】基本屬性輸出之查詢
– 利用SELECT查詢出『產品資料』資料表中的產品編號,
類別編號, 產品名稱, 建議單價, 庫存量等屬性之屬性值。
• 【說明】
陳
祥
輝
著
– 此範例中已一一表示出屬性，故必須要將每一屬性清楚寫
出，不可使用萬用字元。並且此範例亦等同於關聯代數中
的『投影操作』 (PROJECT) -π。
• 【語法】
– SELECT 產品編號, 類別編號, 產品名稱, 建議單價, 庫存量
FROM 產品資料
續
下
頁
第六章 結構化查詢語言SQL(一)
68 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
(a)原產品資料之資料表
(b)查詢後之結果
第六章 結構化查詢語言SQL(一)
69 /107
©2007版權所有，翻印必究
【範例6-13】條件限制之查詢
資
料
庫
系
統
理
論
與
實
務
• 【範例6-13】條件限制之查詢
– 利用SELECT查詢出『產品資料』之資料表中，類別編號
為1的所有產品資訊。
• 【說明】
陳
祥
輝
著
– 此範例可使用WHERE來限制輸出的資料中，僅包含類別
為1的紀錄，而輸出之屬性可直接使用萬用字元*。並且此
範例亦等同於關聯代數中的『選取操作』 (SELECT) -σ。
• 【語法】
– SELECT *
FROM 產品資料
WHERE 類別編號 = 1
續
下
頁
第六章 結構化查詢語言SQL(一)
70 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
(a)原產品資料之資料表
(b)查詢後之結果
第六章 結構化查詢語言SQL(一)
71 /107
©2007版權所有，翻印必究
【範例6-14】基本屬性輸出與條件限制
之綜合查詢
資
料
庫
系
統
理
論
與
實
務
• 【範例6-14】基本屬性輸出與條件限制之綜合查詢
– 利用SELECT查詢出 『產品資料』資料表中類別編號小於
5的所有相關產品資料，輸出包括產品編號, 類別編號, 產
品名稱, 建議單價, 庫存量等屬性。
陳
祥
輝
著
• 【說明】
– 此範例相當於綜合了【範例6-12】和【範例6-13】兩個範
例，也就是利用關聯代數中的縱向選取(投影操作)和橫向
選取(選取操作)兩個操作原理。
• 【語法】
續
– SELECT產品編號, 類別編號, 產品名稱, 建議單價, 庫存量
FROM 產品資料
WHERE 類別編號 < 5
下
頁
第六章 結構化查詢語言SQL(一)
72 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
(a)原產品資料之資料表
(b)查詢後之結果
第六章 結構化查詢語言SQL(一)
73 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
【範例6-15】屬性別名之查詢
陳
祥
輝
著
• 【說明】
• 【範例6-15】屬性別名之查詢
– 請查詢出男性業務之員工相關資料，包括員工編號、姓名、
職稱和性別，但輸出時之屬性名稱要改為員工編號、員工
姓名、職務名稱和性別。
– 此範例主要是說明輸出查詢時，將屬性以別名方式來表示，
只要在每個欲給別名的屬性後方給予
AS 別名
• 【語法】
續
– SELECT 員工編號, 姓名 AS 員工姓名,
職稱 AS 職務名稱, 性別
FROM 員工
WHERE 性別 = ‘男’ AND 職稱 = ‘業務’
下
頁
第六章 結構化查詢語言SQL(一)
74 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【結果】
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
75 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
【範例6-16】排序問題之查詢
陳
祥
輝
著
• 【說明】
• 【範例6-16】排序問題之查詢
– 請查詢產品資料，並依類別編號遞增排序，倘若相同類別
編號之產品，再依據產品編號遞減排序，輸出屬性依序包
括類別編號、產品編號、產品名稱和建議單價。
– 此範例主要是說明輸出查詢時的排序問題，可使用
ORDER BY方式，並於每個欲排序的屬性後方指名遞增
(ASC)或遞減(DESC)排序，倘若未明確指出是遞增或遞減，
預設值將以遞增(ASC)做為排序。
• 【語法】
續
– SELECT 類別編號, 產品編號, 產品名稱, 建議單價
FROM 產品資料
ORDER BY 類別編號 ASC, 產品編號 DESC
下
頁
第六章 結構化查詢語言SQL(一)
76 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【結果】
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
77 /107
©2007版權所有，翻印必究
【範例6-17】重複值只輸出一筆之查詢
資
料
庫
系
統
理
論
與
實
務
• 【範例6-17】重複值只輸出一筆之查詢
– 請從員工資料表中查詢出有那幾種職稱，重複職稱僅一筆
顯示，並依職稱屬性遞增排序。
• 【說明】
陳
祥
輝
著
– 此範例主要在屬性前加上DISTINCT即可，如圖6-13(a)當
不加DISTINCT時會有很多重複的職稱出現，加上
DISTINCT之後如(b)重複之資料只會出現一次。
• 【語法】
– SELECT DISTINCT 職稱
FROM 員工
ORDER BY 職稱
續
下
頁
第六章 結構化查詢語言SQL(一)
78 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【結果】
重
複
重
複
重
複
(b)加DISTINCT
(a)不加DISTINCT
第六章 結構化查詢語言SQL(一)
79 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
【範例6-18】經過計算的輸出之查詢
陳
祥
輝
著
• 【說明】
• 【範例6-18】經過計算的輸出之查詢
– 請依訂單明細資料表，查詢出訂單編號小於94010300之資
料，並於每一筆後面計算出該筆資料之小計(小計=實際單
價×數量)，並依訂單編號、產品編號遞增排序。輸出之屬
性包括訂單編號、產品編號、實際單價、數量和小計。
– 此範例主要是說明輸出查詢時，可利用SELECT的運算方
式，將訂單明細資料表內的實際單價與數量計算出該筆的
小計金額，並給于一個別名為『小計』，計算方式如下
實際單價*數量 AS 小計
• 【語法】
續
下
頁
– SELECT 訂單編號, 產品編號, 實際單價, 數量,
實際單價*數量 AS 小計
FROM 訂單明細
WHERE 訂單編號 < ‘94010300’
ORDER BY 訂單編號, 產品編號
第六章 結構化查詢語言SQL(一)
80 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【結果】
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
81 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• SELECT 訂單編號, 產品編號, 實際單價, 數
量, 實際單價*數量 AS 小計
FROM 訂單明細
WHERE 訂單編號 < ‘94010300’
ORDER BY 訂單編號, 產品編號
HAVING 小計 < 300
第六章 結構化查詢語言SQL(一)
82 /107
©2007版權所有，翻印必究
【範例6-19】兩個資料表的基本內部合
併(INNER JOIN)查詢
資
料
庫
系
統
理
論
與
實
務
• 【範例6-19】兩個資料表的基本內部合併(INNER JOIN)查詢
– 請查詢出每一位員工所承接的訂單資料，輸出屬性包括員工編
號、姓名、訂單編號、訂貨日期，並依員工編號和訂單編號遞
增排序。
• 【說明】
陳
祥
輝
著
續
– 由於此範例所要查詢的屬性，可從圖6-15(a)的ER圖中看出，分
佈在『員工』資料表和『訂單』資料表，所以本查詢必須使用
兩個資料表的合併查詢，可參考圖(b)的Join示意圖，是取得兩個
資料表關聯之後的共同紀錄，也就是藉由『員工』資料表中的
員工編號和『訂單』資料表中的員工編號的『相等關係』。而
在此範例中使用WHERE對兩資料表限制其中的關聯性，其效果
等同於『內部合併』 (INNER JOIN)。
– 也由於在此兩個資料表中，皆有員工編號之屬性，為避免產生
資料庫管理系統的混淆，所以必須在屬性前加上資料表名稱，
並加上一個『點』，稱之為『點表示法』 (Dot Notation)，如語
法中的員工.員工編號和訂單.員工編號。
下
頁
第六章 結構化查詢語言SQL(一)
83 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
員工
(a) ER圖
續
下
頁
第六章 結構化查詢語言SQL(一)
訂單
(b) Join之示意圖
84 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【語法】
– SELECT 員工.員工編號, 姓名, 訂單編號, 訂貨日期
FROM 員工, 訂單
WHERE 員工.員工編號 = 訂單.員工編號
ORDER BY 員工.員工編號, 訂單編號
• 【結果】
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
85 /107
©2007版權所有，翻印必究
【範例6-20】三個資料表的基本內部合
併(INNER JOIN)查詢
資
料
庫
系
統
理
論
與
實
務
• 【範例6-20】三個資料表的基本內部合併(INNER JOIN)查詢
– 請查詢出每一位員工所承接的訂單資料，輸出屬性包括員工編
號、姓名、訂單編號、訂貨日期和產品編號，並依員工編號、
訂單編號和產品編號三個屬性遞增排序。
• 【說明】
陳
祥
輝
著
續
– 由於此範例所要查詢的屬性，可從圖6-17(a)的ER圖中看出，分
佈在『員工』資料表、『訂單』資料表和『訂單明細』資料表
三個資料表，所以本查詢必須使用此三個資料表的合併查詢，
可參考圖(b)的Join示意圖，是取得三個資料表關聯之後的共同紀
錄，也就是藉由『員工』資料表中的員工編號和『訂單』資料
表中的員工編號的『相等關係』以及『訂單』資料表中的訂單
編號和『訂單明細』資料表中的訂單編號的『相等關係』。而
在此範例中使用WHERE對兩資料表限制其中的關聯性，其效果
等同於『內部合併』 (INNER JOIN)。
下
頁
第六章 結構化查詢語言SQL(一)
86 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
訂單明細
(a) ER圖
員工
續
下
頁
第六章 結構化查詢語言SQL(一)
訂單
87 /107
(b) Join之示意圖
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– SELECT 員工.員工編號, 姓名, 訂單.訂單編號, 訂貨日期,
產品編號
FROM 員工, 訂單, 訂單明細
WHERE 員工.員工編號 = 訂單.員工編號 AND
訂單.訂單編號 = 訂單明細.訂單編號
ORDER BY 員工.員工編號, 訂單.訂單編號, 產品編號
第六章 結構化查詢語言SQL(一)
88 /107
©2007版權所有，翻印必究
【範例6-21】間接關聯性的基本內部合
併(INNER JOIN)查詢
資
料
庫
系
統
理
論
與
實
務
• 【範例6-21】間接關聯性的基本內部合併(INNER JOIN)查詢
– 請查詢出每一位員工所承接的訂單資料，輸出屬性包括員工編
號、姓名、訂單編號、訂貨日期和產品名稱，並依員工編號、
訂單編號二個屬性遞增排序。
• 【說明】
陳
祥
輝
著
續
– 由於此範例所要查詢的屬性，可從圖6-19(a)的ER圖中看出，分
佈在『員工』資料表、『訂單』資料表和『產品資料』資料表
三個資料表，但是從圖中可看出，『訂單』資料表與『產品資
料』資料表之間並無直接的關聯性存在，而是透過『訂單明細』
資料表所產生的間接關聯性，所以本查詢必須使用四個資料表
的合併查詢，可參考圖(b)的Join示意圖，取得四個資料表關聯之
後的共同紀錄，也就是藉由『員工』資料表中的員工編號和
『訂單』資料表中的員工編號的『相等關係』、『訂單』資料
表中的訂單編號和『訂單明細』資料表中的訂單編號的『相等
關係』以及『訂單明細』資料表中的產品編號和『產品資料』
資料表中的產品編號的『相等關係』。而在此範例中使用
WHERE對兩資料表限制其中的關聯性，其效果等同於『內部合
併』 (INNER JOIN)。
下
頁
第六章 結構化查詢語言SQL(一)
89 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
透過『訂
單明細』
產生間接
關聯性
陳
祥
輝
著
訂單明細
(a) ER圖
員工
續
下
頁
第六章 結構化查詢語言SQL(一)
訂單
90 /107
產品資料
(b) Join之示意圖
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【語法】
– SELECT 員工.員工編號, 姓名, 訂單.訂單編號, 訂貨日期,
產品名稱
FROM 員工, 訂單, 訂單明細, 產品資料
WHERE 員工.員工編號 = 訂單.員工編號 AND
訂單.訂單編號 = 訂單明細.訂單編號 AND
訂單明細.產品編號 = 產品資料.產品編號
ORDER BY 員工.員工編號, 訂單.訂單編號
陳
祥
輝
著
續
下
頁
第六章 結構化查詢語言SQL(一)
91 /107
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
第六章 結構化查詢語言SQL(一)
92 /107
©2007版權所有，翻印必究
【範例6-22】使用資料表的別名
資
料
庫
系
統
理
論
與
實
務
• 【範例6-22】使用資料表的別名
– 如同【範例6-21】的查詢條件，並使用資料表的別名。
• 【說明】
– 此範例主要是說明當很多資料表的屬性名稱相同時，皆必
須要於屬性前加上資料表名稱，無形中會產生很多冗長的
文字，此問題可透過給於資料表別名方式來達成精簡的效
果。
陳
祥
輝
著
• 【語法】
續
– SELECT E.員工編號, 姓名, O.訂單編號, 訂貨日期, 產品名稱
FROM 員工 AS E, 訂單 AS O, 訂單明細 AS OD, 產品資料 AS P
WHERE E.員工編號 = O.員工編號 AND
O.訂單編號 = OD.訂單編號 AND
OD.產品編號 = P.產品編號
ORDER BY E.員工編號, O.訂單編號
下
頁
第六章 結構化查詢語言SQL(一)
93 /107
©2007版權所有，翻印必究