數(shù)據(jù)庫系統(tǒng)原理-第六章 數(shù)據(jù)庫語言SQL

1、第第6章章 數(shù)據(jù)庫語言數(shù)據(jù)庫語言SQL 6.1 SQL中的簡單查詢6.2 多關系查詢6.3 子查詢6.4 全關系操作6.5 數(shù)據(jù)庫更新6.6 SQL中的事務Page 1第六章第六章 數(shù)據(jù)庫語言數(shù)據(jù)庫語言SQL SQL：Structured Query Language SQL與關系代數(shù)之間的關系：與關系代數(shù)之間的關系： SQL的多數(shù)特性可對應關系代數(shù)的特性，如投影、選擇、的多數(shù)特性可對應關系代數(shù)的特性，如投影、選擇、自然連接等；自然連接等； SQL也超越關系代數(shù)，如聚合運算和數(shù)據(jù)更新；也超越關系代數(shù)，如聚合運算和數(shù)據(jù)更新； 關系代數(shù)可實現(xiàn)的計算，標準關系代數(shù)可實現(xiàn)的計算，標準SQL都可實現(xiàn)。都

2、可實現(xiàn)。 SQL標準：標準： ANSI(American National Standard Institute) SQL ； SQL-92 或或 SQL2； 較新的較新的 SQL-99 或或 SQL3。 Page 2第六章第六章 數(shù)據(jù)庫語言數(shù)據(jù)庫語言SQL 商業(yè)數(shù)據(jù)庫管理系統(tǒng)符合的標準：商業(yè)數(shù)據(jù)庫管理系統(tǒng)符合的標準： 都支持都支持ANSI SQL； 很大程度支持很大程度支持SQL2標準；標準； 支持支持SQL3的部分標準；的部分標準； 都有自己的方言。都有自己的方言。 SQL的主要內(nèi)容：的主要內(nèi)容： DQL數(shù)據(jù)查詢數(shù)據(jù)查詢(select) DML數(shù)據(jù)操縱數(shù)據(jù)操縱(insert, delete

3、, update) DDL數(shù)據(jù)定義數(shù)據(jù)定義(create, drop, alter) DCL數(shù)據(jù)控制數(shù)據(jù)控制(grant, revoke)Page 3第六章第六章 數(shù)據(jù)庫語言數(shù)據(jù)庫語言SQL 數(shù)據(jù)庫服務器、數(shù)據(jù)庫和表之間的關系：數(shù)據(jù)庫服務器、數(shù)據(jù)庫和表之間的關系： 一個數(shù)據(jù)庫服務器一個數(shù)據(jù)庫服務器Database Server是一個驅動引擎程是一個驅動引擎程序，當驅動起來之后，可提供序，當驅動起來之后，可提供SQL服務。服務。 一個數(shù)據(jù)庫一個數(shù)據(jù)庫database是一組語義相關的關系模式。通是一組語義相關的關系模式。通?？蓪骋粋€或幾個文件。常可對應某一個或幾個文件。 一個數(shù)據(jù)庫服務器可支持

4、一個數(shù)據(jù)庫服務器可支持1個或多個數(shù)據(jù)庫運行。個或多個數(shù)據(jù)庫運行。 一個表一個表table是一個關系。是一個關系。 一個數(shù)據(jù)庫可包含多個表，相互間可建立參照完整性一個數(shù)據(jù)庫可包含多個表，相互間可建立參照完整性約束。約束。 要訪問某個表，必須由數(shù)據(jù)庫服務器先啟動該表所在要訪問某個表，必須由數(shù)據(jù)庫服務器先啟動該表所在的數(shù)據(jù)庫。的數(shù)據(jù)庫。Page 4第六章第六章 數(shù)據(jù)庫語言數(shù)據(jù)庫語言SQL 表的定義：表的定義： 表名表名 一組屬性一組屬性(列列columns): 列名，數(shù)據(jù)類型，缺省列名，數(shù)據(jù)類型，缺省值，約束值，約束(主鍵、允許空值、唯一值主鍵、允許空值、唯一值)等。等。 一組外鍵一組外鍵Forei

5、gn keys 一組約束一組約束constraints 一組權限一組權限permissions 一組索引一組索引indexes 一組觸發(fā)器一組觸發(fā)器triggers等等Page 5第六章第六章 數(shù)據(jù)庫語言數(shù)據(jù)庫語言SQL 常用數(shù)據(jù)類型：常用數(shù)據(jù)類型： 字符型：字符型： Char, Varchar, 需確定字符串長度。用單引號標注。需確定字符串長度。用單引號標注。 數(shù)值型：數(shù)值型： 整數(shù)：整數(shù)： Tinyint 1字節(jié)字節(jié) Smallint 2字節(jié)字節(jié) Int | Integer 4字節(jié)字節(jié) BigInt 8字節(jié)字節(jié) 浮點數(shù)：浮點數(shù)： 精確浮點數(shù)：精確浮點數(shù)：Decimal | Dec (pre

6、cision, scale) (數(shù)字總位數(shù)數(shù)字總位數(shù), 小數(shù)點小數(shù)點后位數(shù)后位數(shù)) Numeric (precision, scale) 近似浮點數(shù)：近似浮點數(shù)：Real, Double, Float(小數(shù)點后位數(shù)小數(shù)點后位數(shù))日期型日期型 Date yyyy-mm-dd 每一個屬性每一個屬性(列列)必須確定一種數(shù)據(jù)類型。必須確定一種數(shù)據(jù)類型。Page 6第六章第六章 數(shù)據(jù)庫語言數(shù)據(jù)庫語言SQL 用用SQL查詢或更新數(shù)據(jù)查詢或更新數(shù)據(jù) 大多大多DBMS均提供交互操作界面，方便用戶定義均提供交互操作界面，方便用戶定義數(shù)據(jù)庫，完成查詢及數(shù)據(jù)更新等操作。數(shù)據(jù)庫，完成查詢及數(shù)據(jù)更新等操作。 SQL語句

7、計算正確性語句計算正確性的驗證：驗證：分析計算要求分析計算要求構造構造SQL語句語句準備試驗數(shù)據(jù)準備試驗數(shù)據(jù)手工計算結果手工計算結果運行運行SQL得到結果得到結果結果是否一致結果是否一致?Page 7 SQL中最簡單的查詢是找出關系中滿足特定條件的元組，這和關系代數(shù)中選擇操作()類似。簡單查詢使用了代表SQL特點的三個保留字：SELECT、FROM、WHERE來表示。 最典型SQL的查詢格式：select-from-where 形式6.1 簡單查詢簡單查詢Page 8舉例從students表中查詢劉夢同學的學生信息：name=劉夢(students)SQL語句：語句：SELECT * FROM

8、 studentsWHERE name= 劉夢劉夢; ;sidnamesexB12070101劉夢女B12070102朱正潔女B12070103閆文柯女B12070104季曉倩女B12070105吳塵女B12070106曹雪寧女studentsPage 9如何使用SQL 普通用戶通過DBMS提供的用戶界面訪問數(shù)據(jù)庫，如：psql DBA(database administrator,數(shù)據(jù)庫管理員)通過DBMS提供的DBA界面訪問數(shù)據(jù)庫，如：pgAdmin 在應用程序中通過主語言(如C+、JAVA)提供的數(shù)據(jù)庫接口訪問數(shù)據(jù)庫(客戶端編程) 存儲過程、觸發(fā)器(服務器編程) 無論哪一種方式都使用SQ

9、L語句對數(shù)據(jù)庫查詢和修改Page 10閱讀和書寫查詢語句的小技巧 首先查看FROM子句：從哪些關系中查詢信息 接著查看WHERE子句：把滿足哪些條件的元組查詢出來 最后查看SELECT子句：把哪些屬性查詢出來Page 116.1 簡單查詢簡單查詢 對單個關系的查詢。對單個關系的查詢。SELECT ALL | DISTINCT select-listFROM R WHERE cond ORDER BY expression | integer ASC | DESC ,;其中：其中：select-list :* | column-name | expression AS alias-name ,

10、. 讀寫的順序：讀寫的順序：FROM - WHERE - SELECT 所有所有SQL語句的關鍵詞、表名、列名大小寫無關。語句的關鍵詞、表名、列名大小寫無關。 SQL語句可多行書寫。語句可多行書寫。 SQL語句用分號語句用分號(英文英文)結尾。結尾。 Page 126.1.1 投影投影 SELECT子句的構成：子句的構成： *表示所有屬性，按缺省次序排列。表示所有屬性，按缺省次序排列。SELECT * FROM customer; 可投影到若干屬性：可投影到若干屬性：SELECT custid, name FROM customer; AS 別名別名 (改名運算，改變列名，改名運算，改變列名，

11、AS可省略可省略)SELECT custid, name AS custname FROM customer; 表達式表達式SELECT orderno, lineno, singlecost * quantity AS accountFROM salesitem; 常量也可作為一列常量也可作為一列SELECT orderno, lineno, singlecost * quantity AS account, Yuan AS “RMB”FROM DBA.salesitem; Page 136.1.2 選擇選擇 用用 WHERE 子句表示條件子句表示條件 condition： 運算對象：屬性、

12、常量、別名運算對象：屬性、常量、別名 算術運算符：數(shù)值運算：算術運算符：數(shù)值運算： + - * / 比較運算符：比較運算符： = = =5000 OR quantity=3000;Page 14舉例 查詢由中國電影集團有限公司生產(chǎn)的電影名稱，并且它們要么是2010年生產(chǎn)，要么是片長大于140分鐘。 SELECT title FROM MoviesWHERE studioName= 中國電影集團有限公司 AND (year=2010 or length140);Page 156.1.3 字符串比較字符串比較 字符串比較：字符串比較： = = A0053; 當兩個字符串里的字符序列完全相同時稱兩個

13、字符串相等當兩個字符串里的字符序列完全相同時稱兩個字符串相等。 如如a1a2an和和b1b2bm分別是兩個字符串，進行比較。分別是兩個字符串，進行比較。Page 16位串的表示 由B打頭后跟0、1組成的符號串，如：B 101011表示二進制位串101011 由X打頭后跟十六進制數(shù)字組成的符號串，如：X 7ff表示12位二進制位串011111111111Page 176.1.4 SQL中的模式匹配中的模式匹配 字符串模式匹配字符串模式匹配 ： s LIKE p其中其中 s 是字符屬性或字符串，是字符屬性或字符串，p 是包含是包含 % 或下劃線或下劃線_的字符串模式的字符串模式匹配規(guī)則：匹配規(guī)則：

14、 p 中普通字符只和中普通字符只和 s 中完全相同的字符匹配；中完全相同的字符匹配； p 中中% 可與可與 s 中中 0 個或多個字符匹配；個或多個字符匹配； p 中一個下劃線可與中一個下劃線可與 s 中一個任意字符匹配。中一個任意字符匹配。SELECT * FROMsalesman WHERE name LIKE 王王%;SELECT * FROMsalesman WHERE name LIKE 王王_;Page 18舉例 查詢電影名稱中含有所有格s的電影：select * from movieswhere title like %s%;Page 19Longs Run or Alices

15、RestaurantLIKE表達式中的轉義字符 如果要在LIKE表達式中的模式中直接使用%和_字符怎么辦？ 方法是通過在模式后面跟一個保留字ESCAPE和一個用單引號括起來的字符指定轉義字符，如：s LIKE x%x% ESCAPE x該模式表示以%號打頭并以%結尾的字符串。s matches %asd% or %y%Page 206.1.5 日期和時間日期和時間 日期和時間：日期和時間： 日期類常量：日期類常量： Date yyyy-mm-dd 時間類常量：時間類常量： Time hh:mm:ss.sssss 日期和時間的比較：日期和時間的比較： = = = d1d2 表示表示d1早于早于d

16、2。Select * from salesorder where signdate =d1 AND d =d2select * from salesorder where signdate between 2005-10-1 and 2005-10-31; Page 21 時間戳數(shù)據(jù)類型TIMESTAMP，是將日期和時間組合起來。 通過保留字TIMESTAMP后跟日期和時間組合起來的字符串表示，日期和時間之間用空格隔開。如：TIMESTAMP1979-01-18 12:30:01表示1979年1月18日12點30分01秒。 可以使用比較運算符對日期和時間數(shù)據(jù)類型進行比較。6.1.5 日期和時間

17、日期和時間Page 226.1.6 空值和涉及空值的比較空值和涉及空值的比較 SQL允許屬性有一個特殊值NULL(空值) NULL值的常見解釋為： 未知值：即知道它有一個值，但不知道是什么。 例如：一個未知的生日。 不適用的值：“任何值在這里都沒有意義”。 例如：對于Moviestar關系如果有一個spouse屬性表示其配偶。對于一個未婚的影星這個屬性值可能為NULL值。Page 236.1.6 空值和涉及空值的比較空值和涉及空值的比較 保留的值：“屬于某對象但無權知道的值。例如：未公布的電話號碼在phone屬性中顯示為NULL值。 WHERE子句中要考慮元組中的空值可能帶來的影響。Page

18、246.1.6 空值和涉及空值的比較空值和涉及空值的比較 空值空值NULL表示未知或不存在的信息。表示未知或不存在的信息。 只有允許為空的屬性才可能有空值。只有允許為空的屬性才可能有空值。 需要對空值進行計算、比較和判斷。需要對空值進行計算、比較和判斷。 外連接是一種特殊的連接方式。外連接是一種特殊的連接方式。 外連接會產(chǎn)生空值結果。外連接會產(chǎn)生空值結果。 空值運算規(guī)則：空值運算規(guī)則： NULL值與任何值的任何算術運算結果都為值與任何值的任何算術運算結果都為 NULL值。值。 NULL值與任何值的任何比較結果都為值與任何值的任何比較結果都為 Unknown 布爾布爾值。值。Page 256.1

19、.6 空值和涉及空值的比較空值和涉及空值的比較 注意：注意：NULL不是常量，不是常量，SQL語句中，不能顯式將語句中，不能顯式將 NULL作為操作數(shù)使用，例如：作為操作數(shù)使用，例如：NULL是錯誤的是錯誤的表達式表達式 判斷表達式的值是否為判斷表達式的值是否為 NULL 表示為：表示為： Expression IS NOT NULL 運算結果為布爾值運算結果為布爾值 True 或或 False 。例：例：Select * From salesman Where deptid IS NULL 注意：聚合函數(shù)注意：聚合函數(shù) Sum, Avg 等可作用于允許空值等可作用于允許空值 NULL 的屬性

20、上，計算時，所有空值的行不參與聚的屬性上，計算時，所有空值的行不參與聚合運算。合運算。Page 266.1.7 布爾值布爾值 Unknown 布爾值布爾值 Unknown 如何參與邏輯運算？如何參與邏輯運算？ Unknown 值作為一個邏輯值，與值作為一個邏輯值，與 True 和和 False 構成三值構成三值邏輯。邏輯。 三值邏輯運算：三值邏輯運算：NOT非、非、AND與、與、OR或或ANDTrueFalseUnknownTrueFalseUnknownTrueFalseUnknownFalseFalseFalseUnknownFalseUnknownORTrueFalseUnknownTr

21、ueFalseUnknownTrueTrueTrueTrueFalseUnknownTrueUnknownUnknown設：設：True=1; False=0; Unknown=0.5 那么那么 a AND b = Min(a,b) a OR b = Max(a,b)Page 276.1.7 布爾值布爾值 Unknown SQL語句如何支持三值邏輯值的比較？語句如何支持三值邏輯值的比較？ Expression IS NOT True Expression IS NOT False Expression IS Unknown運算結果是運算結果是 True 或或 False。NOTTrueFals

22、eUnknownFalseTrueUnknownNOT a = 1- aPage 286.1.7 布爾值布爾值 Unknown 注意：在注意：在 Select 語句中，語句中，WhereHaving子句中的子句中的條件表達式可能為條件表達式可能為 True, False, Unknown三種值，三種值，但只有使條件為但只有使條件為 True 的元組才符合要求。的元組才符合要求。 例如：例如： Select * from salesorder where signdate = 2005-10-1 ;Page 296.1.7 布爾值布爾值 UnknownWhere length IS NOT NU

23、LLPage 306.1.8 輸出排序輸出排序 ORDER BY子句子句 ORDER BY expression | integer ASC | DESC , 當輸出結果為多行時，往往需要按某次序輸出結果。當輸出結果為多行時，往往需要按某次序輸出結果。即改變行順序。即改變行順序。 ASC 表示升序表示升序(缺省缺省)；DESC 表示降序。表示降序。例如：例如：SELECT * FROM salesorder ORDER BY signdate DESC;Page 31舉例 SELECT * FROM MoviesWHERE studioName= 中國電影集團有限公司 ORDER BY yea

24、r, length; 注意：ORDER BY子句中的屬性和SELECT子句中的屬性一樣也可以是表達式。Page 32練習練習 P152 6.1.3 a) e) f) SELECT model, speed, hd FROM PC WHERE price1000;e) SELECT * FROM Printer WHERE color = true;Page 33練習練習 P152 6.1.3 a) e) f)f) SELECT model, hd FROM PC WHERE speed = 3.2 AND price2000;Page 3435 關系代數(shù)中的連接、笛卡兒積、并、交和差可以組合多

25、個關系。在SQL中也可以實現(xiàn)相同的操作。6.2 多關系查詢多關系查詢6.2 多關系查詢多關系查詢 SQL 用簡單的方式在一個查詢中處理多個用簡單的方式在一個查詢中處理多個關系：在關系：在 From 子句中列出每個關系，然子句中列出每個關系，然后在后在Select和和Where子句中引用任何出現(xiàn)子句中引用任何出現(xiàn)在在From子句中關系的屬性。子句中關系的屬性。Select a1, a2, From R1, R2, Where condOrder By b1, Page 366.2.1 乘積和連接乘積和連接Select a1, a2, From R1, R2, Where cond實現(xiàn)關系代數(shù)運算

26、：實現(xiàn)關系代數(shù)運算： a1,a2,(R1 cond R2) 即即 a1,a2,(cond (R1 R2 ) 注意：結果是一個包，而不是集合注意：結果是一個包，而不是集合 ( 除非顯式確除非顯式確定定 Select distinct . ) Select 子句和子句和 Where 子句中可包含子句中可包含 From 子句中子句中的的 R1，R2 等關系的屬性。等關系的屬性。Page 3738 查詢電影手機導演的姓名。其對應的查詢語句為：SELECT name FROM Movies, MovieExec WHERE title=手機 AND producerC=cert;Movies(title

27、, year, length, genre, studioName, producerC) 名稱名稱 年份年份 長度長度(分鐘分鐘) 流派流派 電影公司名稱電影公司名稱 導演證書號導演證書號MovieExec(name, address, cert, netWorth) 導演姓名導演姓名 住址住址 導演證書號導演證書號 凈資產(chǎn)凈資產(chǎn)6.2.1 乘積和連接乘積和連接6.2.1 乘積和連接乘積和連接例：計算員工號為例：計算員工號為“A0043”的銷售員所經(jīng)辦的客戶的銷售員所經(jīng)辦的客戶的姓名及其電話號碼的姓名及其電話號碼 Salesorder (orderno, signdate, empid, c

28、ustid) Customer (custid, name, prov, city, phone, unit)Step1. Select * From salesorder, customer;Step2. Select * From salesorder, customer Where salesorder.custid = customer.custid AND empid=A0043;Step3. Select name, phone From salesorder, customer Where salesorder.custid = customer.custid AND empid

29、=A0043;Page 396.2.1 乘積和連接乘積和連接 例：計算員工號為例：計算員工號為“A0044”的銷售員所銷售的商品的銷售員所銷售的商品的種類和規(guī)格。的種類和規(guī)格。 Salesitem (orderno, lineno, prodid, unitprice, quantity) Salesorder (orderno, signdate, empid, custid) Product (prodid, factory, type, spec, price, desc)Select type, specFrom salesitem, salesorder, productWhere

30、salesitem.orderno = salesorder.orderno AND did = didAND salesorder.empid = A0044;Page 406.2.1 乘積和連接乘積和連接 例：計算員工號為例：計算員工號為“A0044”的銷售員所銷售的商品的銷售員所銷售的商品的種類和規(guī)格。的種類和規(guī)格。 Salesitem (orderno, lineno, prodid, unitprice, quantity) Salesorder (orderno, signdate, empid, custid) Product (p

31、rodid, factory, type, spec, price, desc) 自然連接方法：自然連接方法：( SQL Server 中不支持中不支持 )Select type, specFrom salesitem Natural Join salesorder Natural Join productWhere salesorder.empid = A0043;Page 416.2.2 避免歧義避免歧義(二義性二義性) 屬性歧義：屬性歧義： 一個查詢所涉及的多個關系中有兩個或兩個以上的一個查詢所涉及的多個關系中有兩個或兩個以上的屬性同名。屬性同名。 此時在此時在Select、Where等

32、子句中就不能簡單引用屬性等子句中就不能簡單引用屬性名。名。 消除歧義：消除歧義： 關系名關系名.屬性名，限定關系和屬性。屬性名，限定關系和屬性。 若表名或列名與若表名或列名與SQL關鍵詞關鍵詞(keywords)相同：相同： 把表名或列名用雙引號標注，以區(qū)別于關鍵詞。把表名或列名用雙引號標注，以區(qū)別于關鍵詞。例如：例如：SELECT name FROM “user” ; Page 4243 查詢住址相同的影星和導演的姓名。 SELECT MovieS, MovieE FROM MovieStar, MovieExecWHERE MovieStar.address

33、=MovieExec.address;MovieStar(name, address, gender, birthdate) 姓名姓名 住址住址 性別性別 生日生日MovieExec(name, address, cert, netWorth) 導演姓名導演姓名 住址住址 導演證書號導演證書號 凈資產(chǎn)凈資產(chǎn)6.2.2 避免歧義避免歧義(二義性二義性)44 查詢同一家庭的影星和導演的姓名。 SELECT MovieS, MovieE FROM MovieStar, MovieExecWHERE MovieStar.address=MovieExec.address

34、 AND MovieSMovieE;MovieStar(name, address, gender, birthdate) 姓名姓名 住址住址 性別性別 生日生日MovieExec(name, address, cert, netWorth) 導演姓名導演姓名 住址住址 導演證書號導演證書號 凈資產(chǎn)凈資產(chǎn)6.2.2 避免歧義避免歧義(二義性二義性)45 即使屬性沒有二義性，在它前面加上關系名前綴也是允許的。如：SELECT MovieE FROM Movies, MovieExec WHERE Movies.title=手機 AND Movies

35、.producerC=MovieExec.cert;6.2.2 避免歧義避免歧義(二義性二義性)Movies(title, year, length, genre, studioName, producerC) 名稱名稱 年份年份 長度長度(分鐘分鐘) 流派流派 電影公司名稱電影公司名稱 導演證書號導演證書號MovieExec(name, address, cert, netWorth) 導演姓名導演姓名 住址住址 導演證書號導演證書號 凈資產(chǎn)凈資產(chǎn)6.2.3 元組變量元組變量 元組變量：元組變量： 對關系的改名對關系的改名(別名別名)。Select a1, a2From R AS S, Wh

36、ere cond Select、Where等子句中必須使用改過的表名，即等子句中必須使用改過的表名，即“元元組變量組變量”。 例：計算例：計算“所有重名的銷售員的員工號及其姓名所有重名的銷售員的員工號及其姓名”Select s1.empid, From salesman as s1, salesman as s2Where s1.empids2.empid AND =Order by s1.empid; Page 4647 查找具有相同地址的影星。SELECT S, SFROM MovieStar Star1, Mo

37、vieStar Star2WHERE Star1.address=Star2.address;MovieStar(name, address, gender, birthdate) 姓名姓名 住址住址 性別性別 生日生日6.2.3 元組變量元組變量48 查找具有相同地址的兩個影星。SELECT S, SFROM MovieStar Star1, MovieStar Star2WHERE Star1.address=Star2.address AND SS;6.2.3 元組變量元組變量49 查找具有相同地址的兩個影星。SEL

38、ECT S, SFROM MovieStar Star1, MovieStar Star2WHERE Star1.address=Star2.address AND S100000000);6.2.5 查詢的并、交、差查詢的并、交、差58 查詢不是導演的影星的姓名和地址。(SELECT name, address FROM MovieStar)EXCEPT(SELECT name, address FROM MovieExec);6.2.5 查詢的并、交、差查詢的并、交、差59 查詢所有影星或導演的姓名和地址。(SELECT name, a

39、ddress FROM MovieStar)UNION(SELECT name, address FROM MovieExec);6.2.5 查詢的并、交、差查詢的并、交、差60 注意：和關系代數(shù)中的并、交、差運算一樣，在SQL中進行UNION、INTERSECT、EXCEPT操作時，也要求查詢提供的關系具有相同的屬性名和數(shù)據(jù)類型。6.2.5 查詢的并、交、差查詢的并、交、差61 將Movies表中的電影名稱、年份和StarsIn表中的電影名稱、年份進行并操作。(SELECT title, year FROM Movies)UNION(SELECT movieTitle AS title, m

40、ovieYear AS year FROM StarsIn);Movies(title,year,length,genre,studioName,producerC) 名稱名稱 年份年份 長度長度(分鐘分鐘) 流派流派 電影公司名稱電影公司名稱 導演證書號導演證書號StarsIn(movieTitle, movieYear, starName) 影片名稱影片名稱 年份年份 主演姓名主演姓名6.2.5 查詢的并、交、差查詢的并、交、差6.2.5 查詢的并、交、差查詢的并、交、差Page 626.2.5 查詢的并、交、差查詢的并、交、差Page 636.2.5 查詢的并、交、差查詢的并、交、差Pa

41、ge 64練習練習 P158 6.2.2 a) e);a) Select maker, speedFrom Product P, Laptop LWhere hd = 30 And P.model = L.model;Page 65練習練習 P158 6.2.2 a) e);e) Select P1.model, P2.modelFrom PC P1, PC P2Where P1.speed = P2.speed And P1.ram = P2. ram And P1.model ANY( Select 成績成績 From 選修選修 Where 課號課號 = c1 ) ;對于每個選修對于每個選

42、修c1課的學生，若該成績大于子查課的學生，若該成績大于子查詢結果中的某一個值，則該元組滿足條件。詢結果中的某一個值，則該元組滿足條件。Page 766.3.2 涉及關系的條件涉及關系的條件expression compare ALL (Subquery)：比較子查詢結果中的所有值。比較子查詢結果中的所有值。例：求例：求 c1 成績最高的學生學號。成績最高的學生學號。Select 學號學號 From 選修選修 Where 課號課號 = c1 AND 成績成績 = ALL( Select 成績成績 From 選修選修 Where 課號課號 = c1 ) ;對于每個選修對于每個選修c1課的學生，若該

43、成績大于等于課的學生，若該成績大于等于子查詢結果中的每個值，則該元組滿足條件。子查詢結果中的每個值，則該元組滿足條件。Page 776.3.2 涉及關系的條件涉及關系的條件expression NOT IN (Subquery)：當且僅當表達式的值出現(xiàn)在子查詢的結果中當且僅當表達式的值出現(xiàn)在子查詢的結果中時，條件為真。時，條件為真。等價的表示：（等價的表示：（表示等價）表示等價）expression IN (Subquery) expression = ANY (Subquery)expression NOT IN (Subquery) expression ALL (Subquery) Pa

44、ge 786.3.2 涉及關系的條件涉及關系的條件例：求沒有訂單的銷售員的員工號及其姓名。例：求沒有訂單的銷售員的員工號及其姓名。Select empid, name From salesmanWhere empid ALL( Select empid From salesorder )；等價運算：等價運算：Select empid, name From salesmanWhere empid NOT IN( Select empid From salesorder )；Page 796.3.2 涉及關系的條件涉及關系的條件例：求貨號為例：求貨號為MT2226的產(chǎn)品的訂單號及日期。的產(chǎn)品的訂單

45、號及日期。Select orderno, signdateFrom salesorder Where orderno IN ( Select orderno from salesitem where prodid = MT2226 )；等價計算：等價計算：Select orderno, signdateFrom salesorder Where orderno =ANY ( Select orderno from salesitem where prodid = MT2226)；Page 806.3.2 涉及關系的條件涉及關系的條件等價計算：等價計算：Select salesorder.ord

46、erno, signdateFrom salesorder, salesitem Where salesorder.orderno = salesitem.orderno AND prodid = MT2226；等價計算：等價計算：Select orderno, signdate From salesorder as sWhere Exists ( Select * from salesitem where salesitem.orderno = s.orderno AND prodid = MT2226 )；Page 816.3.2 涉及關系的條件涉及關系的條件子查詢亦可實現(xiàn)集合的減運算和交

47、運算。子查詢亦可實現(xiàn)集合的減運算和交運算。R S表示為：表示為： R ALL (S) 或或 R NOT IN (S)R S表示為：表示為： R = ANY (S) 或或 R IN (S)例：求與某客戶同名的銷售員姓名。例：求與某客戶同名的銷售員姓名。Select nameFrom salesmanWhere name IN ( Select name From customer );Page 8283 元組的表示：用括號括起來的標量值序列表示。如：(123，foo)(name, address, networth) 如果一個元組t和關系R的元組有相同的組成分量個數(shù)，那么使用6.3.2節(jié)的運算符

48、對t和R進行比較是有意義的。例如：t IN R, tANY R等6.3.3 涉及元組的條件涉及元組的條件6.3.3 涉及元組的條件涉及元組的條件 若若 t 是元組，是元組，R 是與是與 t 有相同分量的元組的集合，則有相同分量的元組的集合，則 t IN R 或或 t ANY R 可表達特定的查詢條件?？杀磉_特定的查詢條件。 例如：計算影星例如：計算影星Harrison Ford所主演的所有影片的制片人所主演的所有影片的制片人姓名。姓名。 Select name From MovieExec Where cert# IN ( Select producerC# From Movie Where

49、(title, year) IN ( Select movieTitle, movieYear From StarsIN Where starName=Harrison Ford ) ); 多數(shù)多數(shù) DBMS 要求子查詢中的要求子查詢中的 Select-list 為單個屬性，即不支為單個屬性，即不支持元組條件的表示。持元組條件的表示。 Page 8485 查詢葛優(yōu)演過的電影的導演姓名。SELECT nameFROM MovieExecWHERE cert IN (SELECT producerC FROM Movies WHERE (title, year) IN (SELECT movieT

50、itle, movieYear FROM StarsIn WHERE starName=葛優(yōu));6.3.3 涉及元組的條件涉及元組的條件86 不用子查詢也可以查出葛優(yōu)演過的電影的導演的姓名。SELECT nameFROM MovieExec, Movies, StarsInWHERE starName=葛優(yōu) AND movieTitle=title AND movieYear=year AND producerC=cert;6.3.3 涉及元組的條件涉及元組的條件87 最簡單的子查詢只需計算一次，它返回的結果用于外層查詢； 復雜的嵌套子查詢要求一個子查詢計算多次，每次根據(jù)外層查詢的某個元組變量

51、的值執(zhí)行子查詢。這種類型的子查詢叫做關聯(lián)子查詢。6.3.4 關聯(lián)子查詢關聯(lián)子查詢6.3.4 關聯(lián)子查詢關聯(lián)子查詢 子查詢條件中含有對外層關系的引用子查詢條件中含有對外層關系的引用 對外層關系的每個元組，計算一次子查詢對外層關系的每個元組，計算一次子查詢例：計算例：計算“所有重名的銷售員的員工號及其姓名所有重名的銷售員的員工號及其姓名” Select empid, name From salesman as s Where empid ANY ( Select empid From salesman Where name= )；等價于：等價于： Select s1.empid, s1

52、.name From salesman as s1, salesman as s2 Where s1.empids2.empid AND =;Page 886.3.5 FROM 子句中的子查詢子句中的子查詢 子查詢結果可作為關系，直接用于子查詢結果可作為關系，直接用于 FROM 子句子句 此時，子查詢應當括起并給予別名此時，子查詢應當括起并給予別名例：求貨號為例：求貨號為MT2226的產(chǎn)品的訂單號及日期。的產(chǎn)品的訂單號及日期。Select orderno, signdateFrom salesorder，( Select orderno from salesitem

53、 where prodid = MT2226 ) as tWhere salesorder.orderno=t.orderno； 復雜的子查詢用于復雜的子查詢用于 FROM 子句會影響語句的可理子句會影響語句的可理解性解性Page 8990 查詢葛優(yōu)演過的電影的導演，也可這樣查詢：SELECT nameFROM MovieExec, ( SELECT producerC FROM Movies, StarsIn WHERE title=movieTitle AND year=movieYear AND starName=葛優(yōu) ) RWHERE cert=producerC;6.3.5 FROM

54、 子句中的子查詢子句中的子查詢6.3.6 連接表達式連接表達式 通過特定的連接運算符作用在兩個關系上，通過特定的連接運算符作用在兩個關系上，產(chǎn)生一個新關系。產(chǎn)生一個新關系。 連接表達式可直接用于連接表達式可直接用于 FROM 子句。子句。 連接表達式的一般形式包括：連接表達式的一般形式包括： R1 CROSS JOIN R2笛卡爾積笛卡爾積 R1 JOIN R2 ON cond連接連接 R1 NATURAL JOIN R2自然連接自然連接 R1 KEY JOIN R2鍵連接鍵連接Page 916.3.6 連接表達式連接表達式 R1 CROSS JOIN R2 笛卡爾積。笛卡爾積。例如：例如：

55、Select * From salesman CROSS JOIN salesorder等價于：等價于： Select * From salesman, salesorder注意：注意：CROSS JOIN 不能用不能用 ON 增加連接條件。增加連接條件。 R1 JOIN R2 ON cond 連接。連接。例如：例如： Select * From salesman JOIN salesorder ON salesman.empid = salesorder.empid等價于：等價于： Select * From salesman, salesorder Where salesman.empid

56、 = salesorder.empidPage 926.3.7 自然連接自然連接 自然連接隱含特定的連接條件。自然連接隱含特定的連接條件。 R1 NATURAL JOIN R2 自然連接。自然連接。 R1 和和 R2 之間應至少有一對同名屬性，否則連之間應至少有一對同名屬性，否則連接無效。接無效。 默認的連接條件是：默認的連接條件是：R1 和和 R2 的所有同名屬性的所有同名屬性值對應相等。值對應相等。例如：例如：Select * From salesman Natural Join salesorder 自然連接的結果中不包含同名屬性的副本自然連接的結果中不包含同名屬性的副本Page 936

57、.3.7 自然連接和鍵連接自然連接和鍵連接 自然連接可用自然連接可用 ON 增加連接條件。增加連接條件。例：計算員工號為例：計算員工號為“A0043”的銷售員所經(jīng)辦的客戶的銷售員所經(jīng)辦的客戶的姓名及其電話號碼的姓名及其電話號碼Select name, phoneFrom salesorder Natural Join customer ON empid=A0043;比較：比較： Select name, phone From salesorder Natural Join customer Where empid=A0043;Page 946.3.8 外連接外連接 外連接外連接 Outer J

58、oin： 參與連接的一方參與連接的一方(左方或右方左方或右方)的所有元組，即的所有元組，即使不滿足條件，也要出現(xiàn)在結果中。使不滿足條件，也要出現(xiàn)在結果中。 不滿足條件的元組的另一方屬性值均置為空不滿足條件的元組的另一方屬性值均置為空值值(NULL)。 外連接的形式：外連接的形式： 左外連接左外連接 Left Outer Join：左面關系的所有元：左面關系的所有元組將出現(xiàn)在結果中。組將出現(xiàn)在結果中。 右外連接右外連接 Right Outer Join：右面關系的所有元：右面關系的所有元組將出現(xiàn)在結果中。組將出現(xiàn)在結果中。 全外連接全外連接 Full Outer Join：兩面關系的所有元：兩面

59、關系的所有元組均出現(xiàn)在結果中。組均出現(xiàn)在結果中。Page 956.3.8 外連接外連接 例：求所有客戶的訂單號及簽單日期。例：求所有客戶的訂單號及簽單日期。 Select customer.custid, name, orderno, signdate From salesorder Natural Right Outer Join customer; 這是一個這是一個“自然右外連接自然右外連接”。 有訂單的客戶有對應的有訂單的客戶有對應的 orderno, signdate 值。值。 沒有訂單的客戶所對應的沒有訂單的客戶所對應的 orderno, signdate 值為值為NULL。 右面關

60、系右面關系(customer)中的所有元組都出現(xiàn)在結果中。中的所有元組都出現(xiàn)在結果中。 Page 966.3.8 外連接外連接 外連接可用外連接可用 ON 增加條件，但應區(qū)別于增加條件，但應區(qū)別于 Where 條條件。件。例：求所有銷售員在例：求所有銷售員在2005年年10月之后的訂單號及月之后的訂單號及簽單日期。簽單日期。Select salesman.empid, name, orderno, signdateFrom salesman Natural Left Outer Join salesorder ON signdate =2005-10-1對比：對比：Select salesma