Transcript Document

第 8 章
SQL-99：綱要定義、基本
限制與查詢
學習重點 (1/2)
☼ SQL的定義、功能與敘述
☼ CREATE TABLE語法
☼ DROP語法
☼ ALTER語法
☼ SQL中的基本查詢語法
☼ SELECT-FROM-WHERE結構
☼ 簡單的SQL查詢
☼ 別名、* 和DISTINCT的用法
☼ 集合運算
☼ 子字串的比對
☼ 算術運算
☼ 區間值的比較運算
☼ 查詢結果的排序
資料庫管理
2
學習重點 (2/2)
☼ 空值的比較
☼ 巢狀查詢
☼ EXISTS函數
☼ 明確指定的集合
☼ 屬性的重新命名
☼ 合併表格功能
☼ 聚合函數、GROUP BY與HAVING子句
☼ 子字串比對與算術運算
☼ SQL查詢語法總整理
☼ 修改命令：INSERT、DELETE、UPDATE
☼ 視界 (View)
資料庫管理
3
SQL的定義與功能 (1/2)
☼SQL (Structural Query Language)：是
結構化程式語言的簡稱，它已經成為關
聯式DBMS的標準語言
☼SQL包含下列功能
♦ SQL包含資料定義、查詢與更新的敘述
♦ SQL不但是一種DDL(資料定義語言)，也是
一種DML(資料操作語言)
♦ SQL還具備定義資料庫視界、設定安全性與
權限、定義完整性限制以及設定交易控制的
功能
♦ 可以將SQL敘述嵌入Java、COBOL或C/C++
等通用用途程式語言的機制
資料庫管理
4
SQL的定義與功能 (2/2)
☼SQL關聯式模型與理論性(theoretical)關聯
式模型的差異：
♦ SQL使用表格(table)、列(row)及欄(column)，
來代表關聯(relation)、值組(tuple)和屬性
(attribute)
♦ SQL中的table可能包含重覆列(duplicated row)，
但relation中不包含重覆tuple
♦ SQL中的table可能沒有指定主鍵(primary key)
資料庫管理
5
SQL的定義敘述
☼定義綱要(schema)
♦ 語法：CREATE SCHEMA
♦ 範例：(須經由DBA下此語法)
CREATE SCHEMA COMPANY AUTHORIZATION Jsmith
☼定義表格(table)
♦ 語法：CREATE TABLE
♦ 範例：
CREATE TABLE EMPLOYEE … … …
♦ 基底表格(base table)：透過CREATE TABLE宣
告的關聯稱之。
▫ 與透過CREATE VIEW建立的關聯不同
資料庫管理
6
CREATE TABLE 語法
☼ CREATE TABLE: the following are defined:
♦ table name
♦ attribute name
♦ attribute type
▫ CHAR(), VARCHAR(), NUMBER or INTEGER,
DECIMAL(x.x), TIME, DATE, BLOB, etc.
♦ attribute constraints:
♦ PRIMARY KEY (attribute-set)
♦ FOREIGN KEY (attribute-set) REFERENCES tablename(attribute set) ON DELETE ... ON UPDATE ...
♦ UNIQUE (attribute-set)
♦ attribute-name attribute-type NOT NULL
♦ attribute-name attribute-type DEFAULT value
資料庫管理
7
CREATE TABLE 範例 (1/2)
資料庫管理
8
CREATE TABLE 範例 (2/2)
資料庫管理
9
SQL的屬性資料型態和定義域
☼ INT
☼ SMALLINT
☼ FLOAT
☼ REAL
☼ DOUBLE
☼ DECIMAL(i, j)
☼ CHAR(n)
☼ VARCHAR(n)
☼ BIT(n)
☼ BOOLEAN：TRUE/FALSE/UNKNOWN
☼ DATE
☼ TIMESTAMP
☼ INTERVAL
資料庫管理
10
SQL Server的資料型態 (1/2)
資料庫管理
11
SQL Server的資料型態 (2/2)
資料庫管理
12
以SQL語法指定基本限制的範例
資料庫管理
13
SQL指定限制的例子
☼指定屬性的限制和屬性的預設值
♦ Dnumber INT NOT NULL CHECK (Dnumber
> 0 AND Dnumber < 21)
♦ CREATE DOMAIN D_NUM AS INTEGER
CHECK (D_NUM > 0 AND D_NUM < 21)
☼指定鍵值和參考完整性限制
♦ Dnumber INT PRIMARY KEY;
☼使用CHECK指定值組的限制
♦ CHECK (Dept_create_date <= Mgr_start_date);
資料庫管理
14
SQL Server指定限制的語法範例
資料庫管理
15
DROP 的語法
☼DROP：用來移除某個有名稱的綱要元件，
如表格、定義域、限制或綱要
☼移除綱要：
♦ DROP SCHEMA COMPANY CASCADE;
▫ 移除綱要與它所有的表格、定義域和其他元
素
♦ DROP SCHEMA COMPANY RESTRICT;
▫ 只有在綱要內沒有任何元素時才能執行移除
☼移除表格：
♦ DROP TABLE DEPENDENT CASCADE;
資料庫管理
16
ALTER 的語法 (1/2)
☼ ALTER：用來變更基底關聯的定義或其他綱要
元件
☼ 變更基底關聯/表格的動作包括：
♦ 新增或移除欄位(即屬性)
♦ 變更欄位的定義
♦ 新增或移除表格限制
☼ 新增表格的欄位/屬性
♦ ALTER TABLE COMPANY.EMPLOYEE ADD
COLUMN Job VARCHAR(12);
▫ 新增一個用來記錄員工職務的新屬性
☼ 移除表格的欄位/屬性
♦ ALTER TABLE COMPANY.EMPLOYEE DROP
COLUMN Address CASCADE;
▫ 移除欄位時，有CASCADE和RESTRICT兩種方
式可以選擇
資料庫管理
17
ALTER 的語法 (2/2)
☼移除原來的預設子句
♦ ALTER TABLE COMPANY.DEPARTMENT
ALTER COLUMN Mgr_ssn DROP DEFAULT;
☼定義新的預設子句
♦ ALTER TABLE COMPANY.DEPARTMEN
ALTER COLUMN Mgr_ssn SET DEFAULT
‘333445555’;
☼新增/移除表格的限制
♦ ALTER TABLE COMPANY.EMPLOYEE
DROP CONSTRAINT EMPSUPERFK
CASCADE
資料庫管理
18
DDL的摘要整理
☼基底關聯表上的 DDL:
♦
♦
♦
♦
Create table: 定義 base relations 上的 schema
Alter table: 修改 base relations 上的 schema
Drop schema: 刪除 database 的 schema
Drop table: 刪除 base relations 上的 schema
和資料(data)
♦ Truncate table: 僅刪除 base relations 上的資
料
資料庫管理
19
SQL2與SQL-99的新增功能
☼CREATE SCHEMA
☼REFERENTIAL INTEGRITY OPTIONS
資料庫管理
20
SQL2與SQL-99的新增資料型態
☼ DATE：
♦ 由年-月-日所組成，格式為yyyy-mm-dd
☼ TIME：
♦ 由時:分:秒所組成，格式為hh:mm:ss
☼ TIME(i)：
♦ 由時:分:秒加上代表幾分之一秒的數字 i 所組成
♦ 格式為hh:mm:ss:ii...i
☼ TIMESTAMP (時間戳記)：
♦ 由DATE和TIME元件所組成
☼ INTERVAL (期間)：
♦ 指定一個相對值，而不是絕對值
♦ 可能是YEAR/MONTH期間或DAY/TIME期間
♦ 當它相加或相減另一個絕對值，可能是正數或負數，結
果也會是絕對值
資料庫管理
21
SQL中的基本查詢語法
☼ SQL有個很重要的基本敘述，可以讓我們從資料
庫中擷取資訊，也就是SELECT敘述
♦ 不同於關聯式代數(relational algebra)的SELECT運算
☼ SQL與關聯式模型正式定義間有個很重要差異：
♦ SQL允許表格 (即關聯) 中存在兩筆或多筆所有屬性值
完全相同的值組
♦ 因此，通常SQL表格並非一個值組的集合，因為集合
不允許有重複的值組。
♦ SQL表格是值組的多重集合 (multiset，或稱為bag)
☼ 不過，SQL關聯如果有指定PRIMARY KEY或
UNIQUE屬性，或是在SELECT命令中加上
DISTINCT選項，此時的SQL關聯就必須是值組
的集合
資料庫管理
22
SELECT-FROM-WHERE結構
☼ 基本的SELECT敘述，有時也稱為SELECT-FROMWHERE區塊 (block)
SELECT
FROM
WHERE
<attribute list>
<table list>
<condition>
♦ <attribute list> ：屬性名稱的列表
▫ 在查詢時需要參考這些屬性的值
♦ <table list>：列出處裡查詢時會用到的關聯
♦ <condition> ：條件（布林）運算式
▫ 用來識別查詢時所要擷取的值組
資料庫管理
23
SQL的基本語法
資料庫管理
24
範例關聯式資料庫綱要 (圖5.5)
資料庫管理
25
範例關聯式資料庫狀態 (圖5.6)
資料庫管理
26
基本的SQL查詢 (單一表格)
☼ 查詢範例0：擷取名叫 ‘John B. Smith’ 員工的生日
與住址
SELECT Bdate, Address
FROM EMPLOYEE
WHERE Fname=‘John’ AND Minit=‘B’ AND
Lname=‘Smith’;
♦
♦
♦
♦
類似於關聯式代數的SELECT-PROJECT運算配對
其中SELECT子句負責指定投影屬性
而WHERE子句則負責指定選擇條件
查詢的結果可能會有重複的值組
資料庫管理
27
基本的SQL查詢結果 (單一表格)
範例0 的查詢結果
資料庫管理
28
基本的SQL查詢 (雙表格)
☼ 查詢範例1：擷取在 ‘Research’ 部門工作的所有員
工的姓名與住址
SELECT
FROM
WHERE
Fname, Lname, Address
EMPLOYEE, DEPARTMENT
Dname=‘Research’ AND
Dnumber=Dno;
♦ 類似關聯式代數運算中的SELECT-PROJECT-JOIN
♦ (DNAME=‘Research’) 是選擇條件
▫ 相當於關聯式代數的SELECT運算
♦ (DNUMBER=DNO) 則是合併條件
▫ 相當於在關聯式代數的JOIN運算
資料庫管理
29
基本的SQL查詢結果 (雙表格)
範例1 的查詢結果
資料庫管理
30
基本的SQL查詢 (三表格)
☼ 查詢範例2：列出所有位在 ‘Stafford’ 地點的計畫，
其計畫編號、控管部門編號，以及部門經理的姓氏、
住址和生日
SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND
Plocation=‘Stafford’;
♦ 在範例2裡有兩個合併條件
♦ 合併條件DNUM=DNUMBER，使得計劃與其控管部門產
生關聯
♦ 合併條件MGRSSN=SSN，則讓控管部門與管理此部門的
員工產生關聯
資料庫管理
31
基本的SQL查詢結果 (三表格)
範例2 的查詢結果
資料庫管理
32
模稜兩可的屬性名稱
☼ 在SQL中，只要屬性是屬於不同的關聯，就可以讓
兩個或多個屬性使用同樣的名稱
☼ 若查詢會參考到兩個或多個同名的屬性，就必須用
關聯名稱來修飾 (qualify) 屬性名稱，做法是將關聯
名稱放在屬性名稱前，並用英文的句點 (.) 來分隔
☼ 範例1A:
SELECT Fname, EMPLOYEE.Name, Address
FROM
EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT.Name=‘Research’ AND
DEPARTMENT.Dnumber=EMPLOYEE.Dnumber;
資料庫管理
33
模稜兩可的關聯─使用別名 (1/2)
☼假如查詢對同一個關聯參考兩次，也會發生
模稜兩可的情形
♦ 在這類情況下要對關聯名稱指定別名(aliases)
☼查詢範例8：擷取每一位員工的姓名與其直
屬上司的姓名
SELECT
FROM
WHERE
E.Fname, E.Lname, S.Fname, S.Lname
EMPLOYEE E S
E.Super_ssn=S.Ssn;
♦ 在範例8中的替代關聯名稱E和S被稱作EMPLOYEE關聯的
別名 (alias) 或值組變數 (tuple)
♦ E 扮演部屬角色的員工
♦ S 扮演上司角色的員工
資料庫管理
34
模稜兩可的關聯─使用別名 (2/2)
♦ 別名可以用在任何SQL查詢中
♦ 也可以使用AS關鍵字來指定別名
☼查詢範例8：
SELECT
FROM
WHERE
E.Fname, E.Lname, S.Fname, S.Lname
EMPLOYEE AS E, EMPLOYEE AS S
E.Super_ssn=S.Ssn;
☼查詢範例1B：
SELECT
FROM
WHERE
資料庫管理
Fname, EMPLOYEE.Name, Address
EMPLOYEE E, DEPARTMENT D
D.Name=‘Research’ AND
D.Dnumber=E.Dnumber;
35
未指定的WHERE子句
☼ 假如沒有WHERE的子句，代表沒有選擇條件
♦ 因此FROM子句裡所指定關聯的所有值組都會被選取
☼ 查詢範例9：在資料庫中選擇所有員工的SSN資料
SELECT
Ssn
FROM
EMPLOYEE;
☼ 假如在FROM子句中指定一個以上的關聯，而且沒有
WHERE子句，則表示結果等於這些關聯的CROSS
PRODUCT
♦ 也就是所有可能的值組組合都會被選取
☼ 查詢範例10：選擇EMPLOYEE SSN與DEPARTMENT
DNAME的所有組合
資料庫管理
SELECT
Ssn, Dname
FROM
EMPLOYEE, DEPARTMENT;
36
星號(*) 的使用 (1/2)
☼在SQL中，假如要擷取所選值組的所
有屬性值，只需要用一個星號 (*) 即
可，這個星號就代表所有的屬性
☼查詢範例1C：擷取在DEPARTMENT編號
為5的部門工作的員工
資料庫管理
SELECT
*
FROM
EMPLOYEE
WHERE
Dno=5;
37
星號(*) 的使用 (2/2)
☼ 查詢範例1D：擷取每個在 ‘Research’部門工作的員
工，其所有的EMPLOYEE屬性值與DEPARTMENT
的全部屬性值
SELECT *
FROM
EMPLOYEE, DEPARTMENT
WHERE
Dname=‘Research’ AND Dno=Dnumber;
☼ 查詢範例10A：擷取EMPLOYEE與DEPARTMENT
關聯的CROSS PRODUCT
資料庫管理
SELECT
*
FROM
EMPLOYEE, DEPARTMENT;
38
DISTINCT的用法
☼ SQL通常不會將關聯視為集合，因此可以出現
重複的值組
☼ 為了消除查詢結果中的重複值組，可使用關鍵
字DISTINCT
♦ Q11的結果可能會有重複的SALARY值
♦ Q11A則不會有任何重複值
☼ 查詢範例11：擷取每一位員工的薪資
SELECT
ALL Salary
FROM
EMPLOYEE;
☼ 查詢範例11A：擷取所有不同的薪資值
資料庫管理
SELECT
DISTINCT Salary
FROM
EMPLOYEE;
39
集合運算 (1/3)
☼ SQL提供某些集合運算
♦ 聯集運算 (UNION)
♦ 有些SQL 版本還會有差集 (EXCEPT) 和交集
(INTERSECT) 運算
☼ 這些集合運算所產生的關聯是值組的集合
♦ 也就是說，重複的值組會在結果中被除去
☼ 集合運算只能應用在聯集相容(union compatible)
的關聯上
♦ 所以必須先確定要運算的兩個關聯具有相同的屬性，而
且這些屬性出現在兩個關聯的順序也相同
資料庫管理
40
集合運算 (2/3)
☼ 查詢範例4：列出姓氏為 'Smith' 的員工所參與的
所有計畫，不論該員工是計畫的工作人員或是管
理此計畫的部門經理
資料庫管理
(SELECT DISTINCT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND
Lname=‘Smith’)
UNION
(SELECT DISTINCT Pnumber
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber=Pno AND Essn=Ssn AND
Lname=‘Smith’);
41
集合運算 (3/3)
☼ SQL針對多重集合的運算，是利用ALL關鍵字
♦ UNION ALL
♦ EXCEPT ALL
♦ INTERSECT ALL
資料庫管理
42
子字串的比對 (1/2)
☼SQL利用LIKE運算，進行字串比對
♦ 「%」字元可取代任何數目的字元
♦ 「_」字元則可取代單一字元
☼查詢範例12：擷取所有住址在Houston,
Texas的員工
資料庫管理
SELECT
Fname, Lname
FROM
EMPLOYEE
WHERE
Address LIKE ‘%Houston, TX%’;
43
子字串的比對 (2/2)
☼查詢範例12A：擷取所有在1950年代出
生的員工
資料庫管理
SELECT
Fname, Lname
FROM
EMPLOYEE
WHERE
Bdate LIKE ‘1 9 5 _ _ _ _ _ _ _’;
44
算術運算
☼標準的四則運算
♦ 加法(+)、減法(-)、乘法(*)、除法(/)
☼查詢範例13：顯示出所有工作於 ‘ProductX’
計畫的員工加薪 10% 後的薪資結果
SELECT
Fname, Lname, 1.1*Salary AS Increased_sal
FROM
EMPLOYEE, WORK_ON, PROJECT
WHERE
Ssn=Essn AND Pno=Pnumber AND
Pname=‘ProductX’;
資料庫管理
45
區間值的比較運算
☼區間值的比較運算：BETWEEN
☼查詢範例14：擷取5號部門中所有薪
資在$30,000到$40,000之間的員工
SELECT
FROM
WHERE
資料庫管理
*
EMPLOYEE
(Salary BETWEEN 30000 AND 40000)
AND Dno=5;
46
查詢結果的排序
☼ ORDER BY子句是用來針對值組內的一或多個屬性值，將查詢
結果的值組加以排序
♦ 預設的順序是遞增排序(ASC)，可用關鍵字DESC變成遞減
♦ 例如，ORDER BY Dname DESC, Lname ASC, Fname ASC
☼ 查詢範例15：擷取員工與他們所工作計畫的清單，在清單中先
針對部門號碼排序，每個部門內再依員工姓名的字母順序排序
SELECT Dname, Lname, Fname, Pname
FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
WHERE Dnumber=Dno AND Ssn=Essn AND Pno=Pnumber
ORDER BY Dname, Lname, Fname;
資料庫管理
47
空值(NULL)的比較 (1/2)
☼ 在資料庫中，每個空值(NULL)都會被視為不同
♦ 所以，當空值在進行比較運算時，結果會是「未知」
(UNKNOWN)
☼ SQL中，使用3種邏輯值：TRUE、FALASE和
UNKNOWN
♦ 標準的布林邏輯理論只有TRUE和FALSE兩種值
資料庫管理
48
空值(NULL)的比較 (2/2)
☼SQL在查詢中檢查數值是否為NULL
♦ 不是用 = 或 < > 來比較
♦ 而是用 IS 或 IS NOT 進行比較
☼查詢範例18：擷取所有無上司的員工姓名
SELECT
FROM
WHERE
資料庫管理
Fname, Lname
EMPLOYEE
Super_ssn IS NULL;
49
巢狀查詢 (1/4)
☼ 巢狀查詢 (nested query)：指在一個查詢的
WHERE子句內，含有完整的「SELECT-FROMWHERE」區塊。
♦ 此時這個外部的WHERE查詢被稱為外部查詢 (outer
query)
♦ 之前的查詢範例可以使用巢狀查詢來改寫
☼ 查詢範例1：擷取所有在 'Research' 部門工作的員
工的姓名與住址
SELECT
FROM
WHERE
Fname, Lname, Address
EMPLOYEE
Dno IN (SELECT Dnumber
FROM DEPARTMENT
WHERE Dname='Research' );
♦ 由巢狀查詢先選出 ‘Research’ 部門的編號
♦ 外部查詢來選擇其DNO值屬於巢狀查詢結果中的
EMPLOYEE值
資料庫管理
50
巢狀查詢 (2/4)
☼查詢範例4：列出姓氏為 ‘Smith’ 的員工所參
與的所有計畫，不論該員工是計畫的工作人
員或是管理此計畫的部門經理
SELECT DISTINCT Pnumber
FROM PROJECT
WHERE Pnumber IN
(SELECT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum=Dnumber AND Mgr_ssn=Ssn
AND Lname=‘Smith’);
OR
Pnumber IN
(SELECT Pno
FROM WORKS_ON, EMPLOYEE
WHERE Essn=Ssn AND Lname=‘Smith’);
資料庫管理
51
巢狀查詢 (3/4)
☼若巢狀查詢結果只有一筆值組，而且只
有一個屬性，也就是只有單一值
♦ 可以使用 = 而不必使用IN
☼範例：
SELECT Ssn
FROM EMPLOYEE
WHERE Dno = (SELECT Dnum
FROM
PROJECT
WHERE Pnumber = 2);
資料庫管理
52
巢狀查詢 (4/4)
☼SQL也可以將資料值的值組(tuple)放在一對
括號內來比較
☼範例：
SELECT DISTINCT Essn
FROM WORKS_ON
WHERE (Pno, Hours) IN (SELECT Pno, Hours
FROM
WORKS_ON
WHERE Ssn=‘123456789’);
資料庫管理
53
其他比較運算子
☼除IN運算子外，還有其他比較運算子
♦ = (>, >=, <, <=) ANY (或 SOME)
♦ = (>, >=, <, <=) ALL
☼ 範例：傳回薪資比所有5號部門員工多的員工
SELECT Lname, Fname
FROM EMPLOYEE
WHERE Salary > ALL (SELECT
FROM
WHERE
資料庫管理
Salary
EMPLOYEE
Dno=5);
54
相互關聯的巢狀查詢 (1/2)
☼ 假如巢狀查詢中WHERE子句中的條件，會參考到
宣告在外部查詢的關聯裡的某些屬性，就稱這兩
個查詢是相互關聯的 (correlated)
☼ 查詢範例16：擷取眷屬的名字和性別與員工本人
相同的員工姓名
SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE E.Ssn IN (SELECT Essn
FROM DEPENDENT
WHERE E.Fname=DEPENDENT_name
AND E.Sex=Sex);
資料庫管理
55
相互關聯的巢狀查詢 (2/2)
☼ 以巢狀「SELECT-FROM-WHERE」區塊所撰寫，而且使用
「=」或「IN」比較運算子的查詢，一定可以改寫成單一區
塊的查詢。例如「範例16」可以改寫成「範例16A」。
☼ 查詢範例16
SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE E.Ssn IN (SELECT Essn
FROM DEPENDENT
WHERE E.Fname=DEPENDENT_name
AND E.Sex=Sex);
☼ 查詢範例16A
SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E, DEPENDENT AS D
WHERE E.Ssn=D.Essn AND E.Sex=D.Sex AND
E.Fname=D.DEPENDENT_name;
資料庫管理
56
EXISTS函數 (1/5)
☼ EXISTS函數：用來檢查相互關聯的巢狀查詢的結果是否為
空的 (沒有任何值組)
☼ 將「範例16」用EXISTS改寫成「範例16B」
☼ 查詢範例16
SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE E.Ssn IN (SELECT Essn
FROM DEPENDENT
WHERE E.Fname=DEPENDENT_name
AND E.Sex=Sex);
☼ 查詢範例16B
SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE EXISTS (SELECT *
FROM
DEPENDENT
WHERE E.Ssn=Essn AND E.Sex=Sex
AND E.Fname=DEPENDENT_name);
資料庫管理
57
EXISTS函數 (2/5)
☼ 查詢範例6：擷取沒有眷屬的員工姓名
SELECT Fname, Lname
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE Ssn=Essn);
♦ 在「範例6」中，相互關聯的巢狀查詢會擷取所
有與EMPLOYEE值組相關聯的DEPENDENT值
組，如果不存在則此EMPLOYEE值組會被選取
資料庫管理
58
EXISTS函數 (3/5)
☼ 查詢範例7：列出至少有一位眷屬的經理姓名
SELECT Fname, Lname
FROM EMPLOYEE
WHERE EXISTS ( SELECT *
FROM DEPENDENT
WHERE Ssn=Essn)
AND
EXISTS ( SELECT *
FROM DEPARTMENT
WHERE Ssn=Mgr_ssn);
資料庫管理
59
EXISTS函數 (4/5)
☼ 查詢範例3：找出有參與5號部門所管理的全部計
劃的員工姓名
語法3A:
SELECT Fname, Lname
FROM EMPLOYEE
WHERE NOT EXISTS (( SELECT Pnumber
FROM PROJECT
WHERE Dnum=5)
EXCEPT
(SELECT Pno
FROM WORKS_ON
WHERE Ssn=Essn));
♦ 第一個子查詢，選出所有由5號部門控管的計劃
♦ 第二個子查詢，選出某員工參與的所有計劃
資料庫管理
60
EXISTS函數 (5/5)
語法3B:
SELECT Lname, Fname
FROM EMPLOYEE
WHERE NOT EXISTS
資料庫管理
(SELECT *
FROM WORKS_ON B
WHERE (B.Pno IN (SELECT Pnumber
FROM PROJECT
WHERE Dnum=5))
AND
NOT EXISTS
(SELECT *
FROM WORKS_ON C
WHERE C.Essn=Ssn AND
C.Pno=B.Pno));
61
明確指定的集合
☼ 可以在WHERE子句中，使用明確數值的集合
來取代巢狀查詢
☼ 查詢範例17：擷取所有在1、2或3號計畫工作
的員工的社會安全號碼
SELECT
FROM
WHERE
資料庫管理
DISTINCT Essn
WORKS_ON
Pno IN (1,2,3);
62
屬性的重新命名
☼在SQL中，可以在屬性名稱後面加上AS
關鍵字與想要的新名稱，來重新命名出
現在查詢結果中的任何屬性
☼查詢範例8A：擷取每一位員工的姓名與
其直屬上司的姓名
SELECT E.Lname AS EMPLOYEE_name,
S.Lname AS SUPERVISOR_name
FROM
EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;
資料庫管理
63
合併表格功能 (1/3)
☼ SQL中可以合併表格(關聯)
♦ 在查詢中指定FROM子句的合併運算所得結果的表格
☼ 使用者可以指定各種不同類型的合併運算(INNER JOIN、
NATURAL JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、
CROSS JOIN等)
☼ 範例8：擷取每一位員工的姓名與其直屬上司的姓名
語法8:
SELECT E.Lname AS EMPLOYEE_name,
S.Lname AS SUPERVISOR_name
FROM
EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;
語法8B:
SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM (EMPLOYEE AS E LEFT OUTER JOIN
EMPLOYEE AS S ON E.Super_ssn=S.Ssn);
資料庫管理
64
合併表格功能 (2/3)
☼ 範例1：擷取在 ‘Research’ 部門工作的所有員工的姓名與住址
語法1:
SELECT
FROM
WHERE
Fname, Lname, Address
EMPLOYEE, DEPARTMENT
Dname=‘Research’ AND
Dnumber=Dno;
語法1A:
SELECT
FROM
Fname, Lname, Address
(EMPLOYEE JOIN DEPARTMENT
ON Dno=Dnumber
Dname=‘Research’;
WHERE
語法1B:
SELECT
FROM
WHERE
資料庫管理
Fname, Lname, Address
(EMPLOYEE NATURAL JOIN
(DEPARTMENT AS
DEPT(Dname, Dno, Mssn, Msdate)))
Dname=‘Research;
65
合併表格功能 (3/3)
☼ 合併條件也可能是巢狀的
☼ 查詢範例2：列出所有位在 ‘Stafford’ 地點的計畫，
其計畫編號、控管部門編號，以及部門經理的姓
氏、住址和生日
語法2:
SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM
PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND
Plocation=‘Stafford’;
語法2A:
SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM
((PROJECT JOIN DEPARTMENT
ON Dnum = Dnumber) JOIN
EMPLOYEE ON Mgr_ssn = Ssn)
WHERE Plocation=‘Stafford’;
資料庫管理
66
聚合函數 (1/4)
☼ 常見的聚合函數：
♦
♦
♦
♦
♦
COUNT：傳回滿足查詢條件的值組或資料值個數
SUM：傳回滿足查詢條件的資料值的總和
MAX ：傳回滿足查詢條件的資料值的最大值
MIN ：傳回滿足查詢條件的資料值的最小值
AVG ：傳回滿足查詢條件的資料值的平均值
☼ 查詢範例19：計算出所有員工薪資的總和、最高
薪資、最低薪資和平均薪資
SELECT
FROM
SUM (Salary), MAX (Salary),
MIN (Salary), AVG (Salary)
EMPLOYEE;
♦ 有些SQL版本可能不允許在SELECT子句中有一個以上的
函數
資料庫管理
67
聚合函數 (2/4)
☼ 查詢範例20：計算出所有在 'Research' 部門工作的
員工其薪資總和，以及此部門員工的最高薪資、最
低薪資與平均薪資
SELECT
FROM
WHERE
SUM (Salary), MAX (Salary),
MIN (Salary), AVG (Salary)
EMPLOYEE, DEPARTMENT
Dno=Dnumber AND Dname=‘Research’;
☼ 查詢範例23：計算在資料庫中個別薪資值的個數
SELECT
FROM
資料庫管理
COUNT (DISTINCT Salary)
EMPLOYEE;
68
聚合函數 (3/4)
☼ 查詢範例21：計算公司裡員工的總人數
SELECT
FROM
COUNT (*)
EMPLOYEE;
☼ 查詢範例22：計算公司裡在 ‘Research’部門工作的
員工總人數
SELECT
FROM
WHERE
資料庫管理
COUNT (*)
EMPLOYEE, DEPARTMENT
Dno=Dnumber AND Dname=‘Research’;
69
聚合函數 (4/4)
☼ 查詢範例5：擷取所有具有兩個或兩個以上眷
屬的員工姓名
SELECT
FROM
WHERE
資料庫管理
Lname, Fname
EMPLOYEE
(SELECT
FROM
WHERE
COUNT(*)
DEPENDENT
Ssn=Essn) >= 2;
70
分組：Group By子句 (1/4)
☼在很多時候會需要將聚合函數應用在關聯中，
根據某些屬性值分類的值組子群組上
♦ 每個部門的平均薪資
♦ 每個計劃的工作員工人數
☼分組的作法：
♦ 先分割關聯，將屬性值相同的值組組成一個群組
▫ 這些屬性被稱為群組化屬性(grouping attribute)
♦ 再對每個群組分別使用函數
☼SQL提供GROUP BY子句來指定群組化屬性
♦ 群組化屬性要同時出現在SELECT子句中
資料庫管理
71
分組：Group By子句 (2/4)
☼ 查詢範例24：列出每個部門的編號，以及此部門的
工作員工人數和平均薪資
SELECT
FROM
GROUP BY
Dno, COUNT (*), AVG (Salary)
EMPLOYEE
Dno;
♦ 將EMPLOYEE值組分成數個群組，每個群組都有相同的
群組化屬性Dno值
♦ 而在每個值組群組中分別執行COUNT與AVG函數
♦ SELECT子句只包含群組化屬性及在每個值組群組上執行
的函數
資料庫管理
72
分組：Group By子句 (3/4)
☼查詢範例25：擷取每一個計畫的計畫編號、
計畫名稱、以及在此計畫裡工作的員工人數
SELECT
FROM
WHERE
GROUP BY
Pnumber, Pname, COUNT(*)
PROJECT, WORKS_ON
Pnumber=Pno
Pnumber, Pname;
♦ 此例的分組與函數要等到兩個關聯合併(JOIN)之
後才會執行
資料庫管理
73
分組：Group By子句 (4/4)
☼查詢範例27：擷取每個計畫的計畫編號、
計畫名稱、以及在此計畫中工作的5號部
門員工人數
SELECT Pnumber, Pname, COUNT(*)
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber=Pno AND Ssn=Essn AND Dno=5
GROUP BY Pnumber, Pname;
資料庫管理
74
HAVING子句 (1/3)
☼有時候只需要擷取滿足特定條件群組的函
數值
♦ 此時可使用HAVING子句在群組 (而不是個別
值組) 上指定選擇條件
☼查詢範例26：對工作員工在兩人以上的每
個計畫，擷取其計畫編號、計畫名稱以及
計畫中的工作員工人數
SELECT
FROM
WHERE
GROUP BY
HAVING
資料庫管理
Pnumber, Pname, COUNT(*)
PROJECT, WORKS_ON
Pnumber=Pno
Pnumber, Pname
COUNT(*) > 2;
75
HAVING子句 (2/3)
資料庫管理
76
HAVING子句 (3/3)
☼ 查詢範例28：對每個有超過5名員工的部門，擷取部門號碼及
薪資超過 $40,000的員工總人數
SELECT
FORM
WHERE
GROUP BY
Dnumber, COUNT (*)
DEPARTMENT, EMPLOYEE
Dnumber=Dno AND Salary>40000 AND
Dno IN (SELECT
Dno
FROM
EMPLOYEE
GROUP BY
Dno
HAVING
COUNT (*)>5)
Dnumber;
☼ 查詢範例28A：對有5名以上員工且每位員工的薪資皆超過
$40,000的部門，擷取部門號碼及其員工總人數
SELECT
FROM
WHERE
GROUP BY
HAVING
資料庫管理
Dname, COUNT (*)
DEPARTMENT, EMPLOYEE
Dnumber=Dno AND Salary>40000
Dname
COUNT (*) > 5;
77
SQL查詢語法總整理 (1/2)
☼ SQL的查詢最多可包含6個子句，但只有前兩個
SELECT與FROM子句是必要的。子句是以下
列的順序來指定：
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
資料庫管理
<attribute list>
<table list>
<condition>]
<grouping attribute(s)>]
<group condition>]
<attribute list>];
78
SQL查詢語法總整理 (2/2)
☼ SELECT子句：列出被擷取的屬性或函數
☼ FROM子句：指定所有在查詢時需要的關聯 (表格)
♦ 包括合併的關聯，但不包括巢狀查詢所需的關聯
☼ WHERE子句：指定從這些關聯裡選取值組的條件
♦ 同時視需要加入合併(JOIN)條件
☼ GROUP BY子句：指定群組化屬性
☼ HAVING子句：指定群組的選取條件
☼ ORDER BY：用來指定查詢結果的顯示順序
☼ 理論上查詢的執行順序是
♦
♦
♦
♦
最早執行的會是FROM子句
接著是WHERE子句
然後是GROUP BY和HAVING子句
最後是ORDER BY子句用來將查詢結果排序
資料庫管理
79
SQL的修改命令：INSERT (1/4)
☼在SQL中有3個命令可以用來修改資料庫：
INSERT、DELETE和UPDATE
☼INSERT：在關聯中加入資料/值組
♦ 最簡單的形式是在關聯中加入一筆值組
♦ 在指定值組內欄位的值時，必須以CREATE
TABLE命令指定的屬性順序來排列
☼INSERT的基本語法：
INSERT INTO table-name[(attribute-list)]
VALUES (attribute-value-list)
資料庫管理
80
SQL的修改命令：INSERT (2/4)
☼ 修改範例1：
INSERT INTO EMPLOYEE
VALUES (‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-30’,
’98 Oak Forest,Katy,TX’, ‘M’, 37000, ‘987654321’, 4);
☼ 另一種INSERT的形式
♦ 允許使用者明確的指定對應到INSERT命令裡的數值的
屬性名稱
♦ 有NULL或DEFAULT值的屬性，可以不指定資料值
☼ 修改範例1A：
INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)
VALUES
(‘Richard’, ‘Marini’, 4, ‘653298653’);
資料庫管理
81
SQL的修改命令：INSERT (3/4)
☼ 假如DBMS支援並遵守DDL所指定的完整性限制，
則INSERT命令就必須正確才能執行
☼ 修改範例2：
INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Dno)
VALUES
(‘Robert’, ‘Hatcher’, ‘980760540’, 2);
♦ 假如DBMS有檢查參考完整性，範例2就會被拒絕
☼ 修改範例2A：
INSERT INTO EMPLOYEE (Fname, Lname, Dno)
VALUES
(‘Robert’, ‘Hatcher’, 5);
♦ 假如DBMS有檢查NOT NULL，範例2A就會被拒絕
資料庫管理
82
SQL的修改命令：INSERT (4/4)
☼ INSERT可以插入多個值組到關聯中，而且也建立
此關聯，並將查詢結果載入其中
♦ 例如，建立具有每個部門的名稱、員工人數與總薪資的暫
存表格 DEPTS_INFO
♦ Note: DEPTS_INFO表格可能不是最新的；因為若是在執
行過範例3B後，才更新DEPARTMENT或EMPLOYEE關
聯，則DEPTS_INFO內的資訊就會變成過時的資訊。
▫ 解決方案：建立視界(view)，讓表格資料維持最新
☼ 修改範例3A：
CREATE TABLE DEPTS_INFO
(Dept_name
VARCHAR(15),
No_of_emps
INTEGER,
Total_sal
INTEGER);
☼ 修改範例3B：
INSERT INTO DEPTS_INFO (Dept_name, No_of_emps, Total_sal)
SELECT
Dname, COUNT (*), SUM (Salary)
FROM
(DEPARTMENT JOIN EMPLOYEE ON Dnumber=Dno)
GROUP BY Dname
資料庫管理
83
SQL的修改命令：DELETE (1/2)
☼DELETE：用來將值組從關聯中移除
♦ 包括WHERE子句來選取要被刪除的值組
♦ 一次只能從一個表格中刪除值組
▫ 除非在參考完整性限制上有指定CASCADE
♦ 假如沒有指定WHERE子句，則會將值組全部
刪除
▫ 最後把這個表格當作一個空的表格留在資
料庫中
♦ 被刪除的值組個數是根據符合WHERE子句條
件的個數
♦ 一定會遵守參考完整性限制
資料庫管理
84
SQL的修改命令：DELETE (2/2)
☼ 修改範例4A：
DELETE
WHERE
FROM
EMPLOYEE
Lname = ‘Brown’;
☼ 修改範例4B：
DELETE
WHERE
FROM
EMPLOYEE
Ssn = ‘123456789’;
☼ 修改範例4C：
DELETE
WHERE
FROM EMPLOYEE
Dno IN (SELECT Dnumber
FROM
DEPARTMENT
WHERE Dname = ‘Research’);
☼ 修改範例4D：
DELETE
資料庫管理
FROM
EMPLOYEE;
85
SQL的修改命令：UPDATE (1/2)
☼UPDATE：用來修改一或多個被選取值
組的屬性值
♦ WHERE子句從關聯中選取要修改的值組
♦ SET子句是用來指定被修改的屬性和它的新
數值
♦ 每個命令修改同一個關聯裡的值組
♦ 一定會遵守參考完整性限制
資料庫管理
86
SQL的修改命令：UPDATE (2/2)
☼ 修改範例5：把10號計劃的位置和控管部門編號分
別改成 ‘Bellaire’和 5
UPDATE
SET
WHERE
PROJECT
Plocation = ‘Bellaire’, Dnum = 5
Pnumber = 10;
☼ 修改範例6：將所有在 ‘Research’部門工作的員工
加薪10%
UPDATE
SET
WHERE
資料庫管理
EMPLOYEE
Salary = Salary*1.1
Dno IN (SELECT
FROM
WHERE
Dnumber
DEPARTMENT
Dname = ‘Research’);
87
使用宣示和觸發敘述來指定限制
☼ 宣示範例：員工薪資不得超過其工作部門經理的
薪資
CREATE ASSERTION
SALARY_CONSTRAINT
CHECK (NOT EXISTS (SELECT
*
FROM
EMPLOYEE E,
EMPLOYEE M,
DEPARTMENT D
WHERE
E.Salary > M.Salary
AND E.Dno = D.Dnumber
AND D.Mgr_ssn = M.Ssn) );
資料庫管理
88
SQL中的視界：View (1/3)
☼ 視界(View)：一個從其他表格所衍生出來的單一
表格
♦ 其他表格可以是基底表格(base table)或先前已定義的視
界
♦ 視界不需以實體的形式存在，可被視為虛擬表格 (virtual
table)
♦ 指定一個視界的命令為CREATE VIEW
☼ 視界提供下列優點：
♦ 若是複雜的查詢(complex query)經常發生，可利用視界
讓存取更為方便
♦ 使資料庫存取的安全性更佳
♦ 資料能依不同的方式被組合，以滿足不同程式和使用者
的需求
資料庫管理
89
SQL中的視界：View (2/3)
☼視界範例1：
CREATE VIEW
AS SELECT
FROM
WHERE
WORKS_ON1
Fname, Lname, Pname, Hours
EMPLOYEE, PROJECT, WORKS_ON
Ssn = Essn AND Pno = Pnumber;
♦ 在範例1中，視界的屬性繼承FROM子句中原來的表格
☼視界範例2：
CREATE VIEW
AS SELECT
FROM
WHERE
GROUP BY
DEPT_INFO(Dept_name, No_of_emps, Total_sal)
DNAME,COUNT(*), SUM(Salary)
DEPARTMENT,EMPLOYEE
Dnumber = Dno
Dname;
♦ 在範例2中，視界的SELECT子句有指定新的屬性名稱
資料庫管理
90
SQL中的視界：View (3/3)
☼視界查詢範例1：擷取所有在「ProjectX」
計劃工作的員工姓名
SELECT
FROM
WHERE
Fname, Lname
WORKS_ON1
Pname = ‘ProjectX’;
♦ 若是沒有視界WORK_ON1而是使用基底關聯，這個查
詢將需要兩個合併(JOIN)動作
♦ 視界能被定義來簡化經常發生的查詢
資料庫管理
91