1 / 91

第 8 章

第 8 章. SQL-99 ：綱要定義、基本限制與查詢. 學習重點 (1/2). SQL 的定義、功能與敘述 CREATE TABLE 語法 DROP 語法 ALTER 語法 SQL 中的基本查詢語法 SELECT-FROM-WHERE 結構 簡單的 SQL 查詢 別名、* 和 DISTINCT 的用法 集合運算 子字串的比對 算術運算 區間值的比較運算 查詢結果的排序. 學習重點 (2/2). 空值的比較 巢狀查詢 EXISTS 函數 明確指定的集合 屬性的重新命名 合併表格功能 聚合函數、 GROUP BY 與 HAVING 子句

noam
Download Presentation

第 8 章

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第 8 章 SQL-99：綱要定義、基本限制與查詢

  2. 學習重點 (1/2) • SQL的定義、功能與敘述 • CREATE TABLE語法 • DROP語法 • ALTER語法 • SQL中的基本查詢語法 • SELECT-FROM-WHERE結構 • 簡單的SQL查詢 • 別名、* 和DISTINCT的用法 • 集合運算 • 子字串的比對 • 算術運算 • 區間值的比較運算 • 查詢結果的排序 資料庫管理

  3. 學習重點 (2/2) • 空值的比較 • 巢狀查詢 • EXISTS函數 • 明確指定的集合 • 屬性的重新命名 • 合併表格功能 • 聚合函數、GROUP BY與HAVING子句 • 子字串比對與算術運算 • SQL查詢語法總整理 • 修改命令：INSERT、DELETE、UPDATE • 視界 (View) 資料庫管理

  4. SQL的定義與功能 (1/2) • SQL (Structural Query Language)：是結構化程式語言的簡稱，它已經成為關聯式DBMS的標準語言 • SQL包含下列功能 • SQL包含資料定義、查詢與更新的敘述 • SQL不但是一種DDL(資料定義語言)，也是一種DML(資料操作語言) • SQL還具備定義資料庫視界、設定安全性與權限、定義完整性限制以及設定交易控制的功能 • 可以將SQL敘述嵌入Java、COBOL或C/C++等通用用途程式語言的機制 資料庫管理

  5. SQL的定義與功能 (2/2) • SQL關聯式模型與理論性(theoretical)關聯式模型的差異： • SQL使用表格(table)、列(row)及欄(column)，來代表關聯(relation)、值組(tuple)和屬性(attribute) • SQL中的table可能包含重覆列(duplicated row)，但relation中不包含重覆tuple • SQL中的table可能沒有指定主鍵(primary key) 資料庫管理

  6. SQL的定義敘述 • 定義綱要(schema) • 語法：CREATE SCHEMA • 範例：(須經由DBA下此語法) CREATE SCHEMA COMPANY AUTHORIZATION Jsmith • 定義表格(table) • 語法：CREATE TABLE • 範例： CREATE TABLE EMPLOYEE … … … • 基底表格(base table)：透過CREATE TABLE宣告的關聯稱之。 • 與透過CREATE VIEW建立的關聯不同 資料庫管理

  7. 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 table-name(attribute set) ON DELETE ... ON UPDATE ... • UNIQUE (attribute-set) • attribute-name attribute-type NOT NULL • attribute-name attribute-type DEFAULT value 資料庫管理

  8. CREATE TABLE 範例 (1/2) 資料庫管理

  9. CREATE TABLE 範例 (2/2) 資料庫管理

  10. SQL的屬性資料型態和定義域 • INT • SMALLINT • FLOAT • REAL • DOUBLE • DECIMAL(i, j) • CHAR(n) • VARCHAR(n) • BIT(n) • BOOLEAN：TRUE/FALSE/UNKNOWN • DATE • TIMESTAMP • INTERVAL 資料庫管理

  11. SQL Server的資料型態 (1/2) 資料庫管理

  12. SQL Server的資料型態 (2/2) 資料庫管理

  13. 以SQL語法指定基本限制的範例 資料庫管理

  14. 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); 資料庫管理

  15. SQL Server指定限制的語法範例 資料庫管理

  16. DROP 的語法 • DROP：用來移除某個有名稱的綱要元件，如表格、定義域、限制或綱要 • 移除綱要： • DROP SCHEMA COMPANY CASCADE; • 移除綱要與它所有的表格、定義域和其他元素 • DROP SCHEMA COMPANY RESTRICT; • 只有在綱要內沒有任何元素時才能執行移除 • 移除表格： • DROP TABLE DEPENDENT CASCADE; 資料庫管理

  17. ALTER 的語法 (1/2) • ALTER：用來變更基底關聯的定義或其他綱要元件 • 變更基底關聯/表格的動作包括： • 新增或移除欄位(即屬性) • 變更欄位的定義 • 新增或移除表格限制 • 新增表格的欄位/屬性 • ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12); • 新增一個用來記錄員工職務的新屬性 • 移除表格的欄位/屬性 • ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN Address CASCADE; • 移除欄位時，有CASCADE和RESTRICT兩種方式可以選擇 資料庫管理

  18. 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 資料庫管理

  19. 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 上的資料 資料庫管理

  20. SQL2與SQL-99的新增功能 • CREATE SCHEMA • REFERENTIAL INTEGRITY OPTIONS 資料庫管理

  21. 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期間 • 當它相加或相減另一個絕對值，可能是正數或負數，結果也會是絕對值 資料庫管理

  22. SQL中的基本查詢語法 • SQL有個很重要的基本敘述，可以讓我們從資料庫中擷取資訊，也就是SELECT敘述 • 不同於關聯式代數(relational algebra)的SELECT運算 • SQL與關聯式模型正式定義間有個很重要差異： • SQL允許表格 (即關聯) 中存在兩筆或多筆所有屬性值完全相同的值組 • 因此，通常SQL表格並非一個值組的集合，因為集合不允許有重複的值組。 • SQL表格是值組的多重集合 (multiset，或稱為bag) • 不過，SQL關聯如果有指定PRIMARY KEY或UNIQUE屬性，或是在SELECT命令中加上DISTINCT選項，此時的SQL關聯就必須是值組的集合 資料庫管理

  23. SELECT-FROM-WHERE結構 • 基本的SELECT敘述，有時也稱為SELECT-FROM-WHERE區塊 (block) SELECT <attribute list> FROM <table list> WHERE <condition> • <attribute list> ：屬性名稱的列表 • 在查詢時需要參考這些屬性的值 • <table list>：列出處裡查詢時會用到的關聯 • <condition> ：條件（布林）運算式 • 用來識別查詢時所要擷取的值組 資料庫管理

  24. SQL的基本語法 資料庫管理

  25. 範例關聯式資料庫綱要 (圖5.5) 資料庫管理

  26. 範例關聯式資料庫狀態 (圖5.6) 資料庫管理

  27. 基本的SQL查詢 (單一表格) • 查詢範例0：擷取名叫 ‘John B. Smith’ 員工的生日與住址 SELECT Bdate, Address FROM EMPLOYEE WHERE Fname=‘John’ AND Minit=‘B’ AND Lname=‘Smith’; • 類似於關聯式代數的SELECT-PROJECT運算配對 • 其中SELECT子句負責指定投影屬性 • 而WHERE子句則負責指定選擇條件 • 查詢的結果可能會有重複的值組 資料庫管理

  28. 基本的SQL查詢結果 (單一表格) 範例0 的查詢結果 資料庫管理

  29. 基本的SQL查詢 (雙表格) • 查詢範例1：擷取在 ‘Research’ 部門工作的所有員工的姓名與住址 SELECT Fname, Lname, Address FROM EMPLOYEE, DEPARTMENT WHERE Dname=‘Research’ AND Dnumber=Dno; • 類似關聯式代數運算中的SELECT-PROJECT-JOIN • (DNAME=‘Research’) 是選擇條件 • 相當於關聯式代數的SELECT運算 • (DNUMBER=DNO) 則是合併條件 • 相當於在關聯式代數的JOIN運算 資料庫管理

  30. 基本的SQL查詢結果 (雙表格) 範例1 的查詢結果 資料庫管理

  31. 基本的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，則讓控管部門與管理此部門的員工產生關聯 資料庫管理

  32. 基本的SQL查詢結果 (三表格) 範例2 的查詢結果 資料庫管理

  33. 模稜兩可的屬性名稱 • 在SQL中，只要屬性是屬於不同的關聯，就可以讓兩個或多個屬性使用同樣的名稱 • 若查詢會參考到兩個或多個同名的屬性，就必須用關聯名稱來修飾 (qualify) 屬性名稱，做法是將關聯名稱放在屬性名稱前，並用英文的句點 (.) 來分隔 • 範例1A: SELECT Fname, EMPLOYEE.Name, Address FROM EMPLOYEE, DEPARTMENT WHERE DEPARTMENT.Name=‘Research’ AND DEPARTMENT.Dnumber=EMPLOYEE.Dnumber; 資料庫管理

  34. 模稜兩可的關聯─使用別名 (1/2) • 假如查詢對同一個關聯參考兩次，也會發生模稜兩可的情形 • 在這類情況下要對關聯名稱指定別名(aliases) • 查詢範例8：擷取每一位員工的姓名與其直屬上司的姓名SELECT E.Fname, E.Lname, S.Fname, S.LnameFROM EMPLOYEE ESWHERE E.Super_ssn=S.Ssn; • 在範例8中的替代關聯名稱E和S被稱作EMPLOYEE關聯的別名 (alias) 或值組變數 (tuple) • E扮演部屬角色的員工 • S扮演上司角色的員工 資料庫管理

  35. 模稜兩可的關聯─使用別名 (2/2) • 別名可以用在任何SQL查詢中 • 也可以使用AS關鍵字來指定別名 • 查詢範例8：SELECT E.Fname, E.Lname, S.Fname, S.LnameFROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.Super_ssn=S.Ssn; • 查詢範例1B：SELECT Fname, EMPLOYEE.Name, Address FROM EMPLOYEE E, DEPARTMENT D WHERE D.Name=‘Research’ AND D.Dnumber=E.Dnumber; 資料庫管理

  36. 未指定的WHERE子句 • 假如沒有WHERE的子句，代表沒有選擇條件 • 因此FROM子句裡所指定關聯的所有值組都會被選取 • 查詢範例9：在資料庫中選擇所有員工的SSN資料 SELECT Ssn FROM EMPLOYEE; • 假如在FROM子句中指定一個以上的關聯，而且沒有WHERE子句，則表示結果等於這些關聯的CROSS PRODUCT • 也就是所有可能的值組組合都會被選取 • 查詢範例10：選擇EMPLOYEE SSN與DEPARTMENT DNAME的所有組合 SELECT Ssn, Dname FROM EMPLOYEE, DEPARTMENT; 資料庫管理

  37. 星號(*) 的使用 (1/2) • 在SQL中，假如要擷取所選值組的所有屬性值，只需要用一個星號 (*) 即可，這個星號就代表所有的屬性 • 查詢範例1C：擷取在DEPARTMENT編號為5的部門工作的員工 SELECT * FROM EMPLOYEE WHERE Dno=5; 資料庫管理

  38. 星號(*) 的使用 (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; 資料庫管理

  39. DISTINCT的用法 • SQL通常不會將關聯視為集合，因此可以出現重複的值組 • 為了消除查詢結果中的重複值組，可使用關鍵字DISTINCT • Q11的結果可能會有重複的SALARY值 • Q11A則不會有任何重複值 • 查詢範例11：擷取每一位員工的薪資 SELECT ALLSalary FROM EMPLOYEE; • 查詢範例11A：擷取所有不同的薪資值 SELECT DISTINCTSalary FROM EMPLOYEE; 資料庫管理

  40. 集合運算 (1/3) • SQL提供某些集合運算 • 聯集運算 (UNION) • 有些SQL 版本還會有差集 (EXCEPT) 和交集(INTERSECT) 運算 • 這些集合運算所產生的關聯是值組的集合 • 也就是說，重複的值組會在結果中被除去 • 集合運算只能應用在聯集相容(union compatible)的關聯上 • 所以必須先確定要運算的兩個關聯具有相同的屬性，而且這些屬性出現在兩個關聯的順序也相同 資料庫管理

  41. 集合運算 (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’); 資料庫管理

  42. 集合運算 (3/3) • SQL針對多重集合的運算，是利用ALL關鍵字 • UNION ALL • EXCEPT ALL • INTERSECT ALL 資料庫管理

  43. 子字串的比對 (1/2) • SQL利用LIKE運算，進行字串比對 • 「%」字元可取代任何數目的字元 • 「_」字元則可取代單一字元 • 查詢範例12：擷取所有住址在Houston, Texas的員工 SELECT Fname, Lname FROM EMPLOYEE WHERE Address LIKE ‘%Houston, TX%’; 資料庫管理

  44. 子字串的比對 (2/2) • 查詢範例12A：擷取所有在1950年代出生的員工 SELECT Fname, Lname FROM EMPLOYEE WHERE Bdate LIKE ‘1 9 5 _ _ _ _ _ _ _’; 資料庫管理

  45. 算術運算 • 標準的四則運算 • 加法(+)、減法(-)、乘法(*)、除法(/) • 查詢範例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’; 資料庫管理

  46. 區間值的比較運算 • 區間值的比較運算：BETWEEN • 查詢範例14：擷取5號部門中所有薪資在$30,000到$40,000之間的員工 SELECT * FROM EMPLOYEE WHERE (Salary BETWEEN 30000 AND 40000) AND Dno=5; 資料庫管理

  47. 查詢結果的排序 • 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; 資料庫管理

  48. 空值(NULL)的比較 (1/2) • 在資料庫中，每個空值(NULL)都會被視為不同 • 所以，當空值在進行比較運算時，結果會是「未知」(UNKNOWN) • SQL中，使用3種邏輯值：TRUE、FALASE和UNKNOWN • 標準的布林邏輯理論只有TRUE和FALSE兩種值 資料庫管理

  49. 空值(NULL)的比較 (2/2) • SQL在查詢中檢查數值是否為NULL • 不是用 = 或 < > 來比較 • 而是用 IS 或 IS NOT 進行比較 • 查詢範例18：擷取所有無上司的員工姓名 SELECT Fname, Lname FROM EMPLOYEE WHERE Super_ssn IS NULL; 資料庫管理

  50. 巢狀查詢 (1/4) • 巢狀查詢 (nested query)：指在一個查詢的WHERE子句內，含有完整的「SELECT-FROM-WHERE」區塊。 • 此時這個外部的WHERE查詢被稱為外部查詢 (outer query) • 之前的查詢範例可以使用巢狀查詢來改寫 • 查詢範例1：擷取所有在 'Research' 部門工作的員工的姓名與住址SELECT Fname, Lname, AddressFROM EMPLOYEEWHERE Dno IN (SELECT DnumberFROM DEPARTMENTWHERE Dname='Research' ); • 由巢狀查詢先選出 ‘Research’ 部門的編號 • 外部查詢來選擇其DNO值屬於巢狀查詢結果中的EMPLOYEE值 資料庫管理

More Related