數(shù)據(jù)庫系統(tǒng)原理教學課件

1、數(shù)據(jù)庫系統(tǒng)原理教學1l4.1 SQL語言概述語言概述l4.2 數(shù)據(jù)定義功能與約束數(shù)據(jù)定義功能與約束l4.3 數(shù)據(jù)操縱功能數(shù)據(jù)操縱功能l4.4 數(shù)據(jù)查詢功能與視圖數(shù)據(jù)查詢功能與視圖數(shù)據(jù)庫系統(tǒng)原理教學2lSELECT FROM WHERE GROUP BY HAVING ORDER BY 數(shù)據(jù)庫系統(tǒng)原理教學3l5.4.1 單表查詢單表查詢l5.4.2 查詢條件的設(shè)置查詢條件的設(shè)置l5.4.3 聚合函數(shù)聚合函數(shù)l5.4.4 分組分組l5.4.5 查詢結(jié)果的排序查詢結(jié)果的排序l5.4.6 多表連接查詢多表連接查詢l5.4.7 子查詢子查詢l5.4.8 視圖視圖數(shù)據(jù)庫系統(tǒng)原理教學4l數(shù)據(jù)源只涉及一張表的

2、查詢數(shù)據(jù)源只涉及一張表的查詢l選擇列選擇列數(shù)據(jù)庫系統(tǒng)原理教學5lSELECT FROM 數(shù)據(jù)庫系統(tǒng)原理教學6CREATE TABLE 演員演員( 姓名姓名 char ( 20 ), 工號工號 char ( 10 ), 性別性別 char (2), PRIMARY KEY (工號工號 ) )CREATE TABLE 電影電影( 片名片名 char ( 60 ), 導演導演 char ( 20 ), 年代年代 datetime, PRIMARY KEY (片名，年代片名，年代 ) )CREATE TABLE 出演出演( 工號工號 char ( 10 ), 片名片名 char ( 60 ), 年代年

3、代 datetime, 片酬片酬 money, PRIMARY KEY (工號工號,片名片名,年代年代 ) )數(shù)據(jù)庫系統(tǒng)原理教學7姓名姓名工號工號性別性別王菲王菲A010女女寧靜寧靜A120女女梁朝偉梁朝偉A231男男葛優(yōu)葛優(yōu)A751男男片名片名導演導演年代年代重慶森林重慶森林王家衛(wèi)王家衛(wèi)1994紅河谷紅河谷馮小寧馮小寧1998不見不散不見不散馮小剛馮小剛1998花樣年華花樣年華王家衛(wèi)王家衛(wèi)2000工號工號片名片名年代年代片酬片酬A120紅河谷紅河谷199850kA231花樣年華花樣年華2000100kA751不見不散不見不散1998120k演員演員電影電影出演出演數(shù)據(jù)庫系統(tǒng)原理教學8l查詢演

4、員的姓名與性別查詢演員的姓名與性別lSELECT 姓名，性別姓名，性別 FROM 演員演員l查詢結(jié)果查詢結(jié)果姓名姓名性別性別王菲王菲女女寧靜寧靜女女梁朝偉梁朝偉男男葛優(yōu)葛優(yōu)男男數(shù)據(jù)庫系統(tǒng)原理教學9l查詢?nèi)w演員的記錄查詢?nèi)w演員的記錄lSELECT * FROM 演員演員l查詢結(jié)果查詢結(jié)果姓名姓名工號工號性別性別王菲王菲A010女女寧靜寧靜A120女女梁朝偉梁朝偉A231男男葛優(yōu)葛優(yōu)A751男男數(shù)據(jù)庫系統(tǒng)原理教學10lgetdate()取當前的系統(tǒng)日期取當前的系統(tǒng)日期lyear(date)取日期時間型參數(shù)取日期時間型參數(shù)date的年份的年份lmonth(date)lday(date)l數(shù)據(jù)庫系

5、統(tǒng)原理教學11l查詢電影的片名以及拍攝距今時間查詢電影的片名以及拍攝距今時間lSELECT 片名片名, 拍攝距今拍攝距今, l year(getdate()-year(年代年代), 年年 FROM 電影電影l(fā)查詢結(jié)果查詢結(jié)果片名片名重慶森林重慶森林拍攝距今拍攝距今11年年紅河谷紅河谷拍攝距今拍攝距今7年年不見不散不見不散拍攝距今拍攝距今7年年花樣年華花樣年華拍攝距今拍攝距今5年年數(shù)據(jù)庫系統(tǒng)原理教學12l計算列、函數(shù)列和常量列的顯示結(jié)果都計算列、函數(shù)列和常量列的顯示結(jié)果都沒有列標題，通過指定列的別名可以改沒有列標題，通過指定列的別名可以改變查詢結(jié)果的列標題變查詢結(jié)果的列標題l改變列標題的語法：改

6、變列標題的語法： 列名列名 | 表達式表達式 AS 列標題列標題 或：列標題或：列標題 列名列名 | 表達式表達式數(shù)據(jù)庫系統(tǒng)原理教學13l查詢電影的片名以及拍攝距今時間查詢電影的片名以及拍攝距今時間lSELECT 片名片名, year(getdate()-year(年代年代)l AS 拍攝距今年限拍攝距今年限 FROM 電影電影l(fā)查詢結(jié)果查詢結(jié)果片名片名拍攝距今年限拍攝距今年限重慶森林重慶森林11紅河谷紅河谷7不見不散不見不散7花樣年華花樣年華5數(shù)據(jù)庫系統(tǒng)原理教學14姓名姓名工號工號性別性別王菲王菲A010女女寧靜寧靜A120女女梁朝偉梁朝偉A231男男葛優(yōu)葛優(yōu)A751男男片名片名導演導演年代

7、年代重慶森林重慶森林王家衛(wèi)王家衛(wèi)1994紅河谷紅河谷馮小寧馮小寧1998不見不散不見不散馮小剛馮小剛1998花樣年華花樣年華王家衛(wèi)王家衛(wèi)2000工號工號片名片名年代年代片酬片酬A120紅河谷紅河谷199850kA231花樣年華花樣年華2000100kA751不見不散不見不散1998120k演員演員電影電影出演出演數(shù)據(jù)庫系統(tǒng)原理教學15l查詢數(shù)據(jù)庫中的電影拍攝年代查詢數(shù)據(jù)庫中的電影拍攝年代lSELECT year(年代年代) AS 拍攝年代拍攝年代 FROM 電影電影l(fā)查詢結(jié)果查詢結(jié)果拍攝年代拍攝年代199419982000數(shù)據(jù)庫系統(tǒng)原理教學16l5.4.1 單表查詢單表查詢l5.4.2 查詢條

8、件的設(shè)置查詢條件的設(shè)置l5.4.3 聚合函數(shù)聚合函數(shù)l5.4.4 分組分組l5.4.5 查詢結(jié)果的排序查詢結(jié)果的排序l5.4.6 多表連接查詢多表連接查詢l5.4.7 子查詢子查詢l5.4.8 視圖視圖數(shù)據(jù)庫系統(tǒng)原理教學17lSELECT FROM WHERE 數(shù)據(jù)庫系統(tǒng)原理教學18 WHERE子句常用查詢條件子句常用查詢條件 謂謂 詞詞 比較（比較運算符）比較（比較運算符） =, , =, , =, =, , =, , =, （或（或!=!=），），NOT+NOT+上述比較運算符上述比較運算符 確定范圍確定范圍 BETWEEN AND, NOT BETWEEN AND BETWEEN AND

9、, NOT BETWEEN AND 確定集合確定集合 IN, NOT IN IN, NOT IN 字符匹配字符匹配 LIKE, NOT LIKE LIKE, NOT LIKE 空值空值 IS NULL, IS NOT NULL IS NULL, IS NOT NULL 多重條件（邏輯謂詞）多重條件（邏輯謂詞） AND, OR AND, OR 數(shù)據(jù)庫系統(tǒng)原理教學19l查詢女演員的姓名查詢女演員的姓名lSELECT 姓名姓名 FROM 演員演員 WHERE 性別性別女女姓名姓名王菲王菲寧靜寧靜數(shù)據(jù)庫系統(tǒng)原理教學20l查詢拍攝距今超過查詢拍攝距今超過10年電影的片名年電影的片名lSELECT 片名片

10、名 FROM 電影電影 WHERE year(getdate()-year(年代年代) 10l查詢結(jié)果查詢結(jié)果片名片名重慶森林重慶森林數(shù)據(jù)庫系統(tǒng)原理教學21l用于查找指定列中符合匹配模式的元組用于查找指定列中符合匹配模式的元組l列名列名 NOT LIKE l匹配串中可包含如下四種匹配串中可包含如下四種通配符通配符 _ 匹配任意一個字符；匹配任意一個字符； % 匹配匹配0個或多個字符；個或多個字符； 匹配匹配 中的任意一個字符；中的任意一個字符； 不匹配不匹配 中的任意一個字符中的任意一個字符數(shù)據(jù)庫系統(tǒng)原理教學22l查詢查詢 “張張”“”“王王”“”“李李”姓演員的情況姓演員的情況lSELECT

11、 姓名姓名 FROM 演員演員 WHERE 姓名姓名 LIKE 張王李張王李%l查詢結(jié)果查詢結(jié)果姓名姓名王菲王菲數(shù)據(jù)庫系統(tǒng)原理教學23l查詢姓名第查詢姓名第2個字不是個字不是“菲菲”或者或者“優(yōu)優(yōu)” 的的演員的情況演員的情況lSELECT 姓名姓名 FROM 演員演員 WHERE 姓名姓名 LIKE _菲優(yōu)菲優(yōu)%l查詢結(jié)果查詢結(jié)果姓名姓名寧靜寧靜梁朝偉梁朝偉數(shù)據(jù)庫系統(tǒng)原理教學24l查詢姓名由查詢姓名由2個字組成的演員的情況個字組成的演員的情況lSELECT 姓名姓名 FROM 演員演員 WHERE 姓名姓名 LIKE _ _l查詢結(jié)果查詢結(jié)果姓名姓名王菲王菲寧靜寧靜葛優(yōu)葛優(yōu)數(shù)據(jù)庫系統(tǒng)原理教學2

12、5l空值（空值（NULL）表示）表示不確定的值不確定的值l判斷取值為空的語句格式：判斷取值為空的語句格式： 列名列名 IS NULLl判斷取值不為空的語句格式：判斷取值不為空的語句格式： 列名列名 IS NOT NULL數(shù)據(jù)庫系統(tǒng)原理教學26l查詢有片酬記錄的出演情況查詢有片酬記錄的出演情況lSELECT * FROM 出演出演 WHERE 片酬片酬 IS NOT NULLl查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學27l在在WHERE子句中可以使用邏輯運算符子句中可以使用邏輯運算符AND和和OR來組成多重條件查詢來組成多重條件查詢l用用AND連接的條件表示必須全部滿足所連接的條件表示必須全部滿足所有

13、的條件的元組才被選中有的條件的元組才被選中l(wèi)用用OR連接的條件表示只要滿足其中一個連接的條件表示只要滿足其中一個條件的元組即被選中條件的元組即被選中數(shù)據(jù)庫系統(tǒng)原理教學28l查詢王姓女演員的情況查詢王姓女演員的情況lSELECT * FROM 演員演員 WHERE 姓名姓名 LIKE 王王% AND 性別性別女女 l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學29l查詢王姓演員和全體男演員的情況查詢王姓演員和全體男演員的情況lSELECT * FROM 演員演員 WHERE 姓名姓名 LIKE 王王% OR 性別性別男男 l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學30l BETWEENAND和和 NOT BET

14、WEENANDl格式格式 列名列名 | 表達式表達式 NOT BETWEEN 下限值下限值 AND 上限值上限值數(shù)據(jù)庫系統(tǒng)原理教學31l查詢拍攝距今查詢拍攝距今5至至10年的電影年的電影l(fā)SELECT 片名片名 FROM 電影電影 WHERE year(getdate()year( 年代年代) BETWEEN 5 AND 10不見不散不見不散紅河谷紅河谷花樣年華花樣年華片名片名l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學32l查詢拍攝距今查詢拍攝距今5至至10年的電影年的電影l(fā)SELECT 片名片名 FROM 電影電影 WHERE year(getdate()year( 年代年代) = 5 AND y

15、ear(getdate()year( 年代年代) = 10 數(shù)據(jù)庫系統(tǒng)原理教學33l查詢拍攝距今不到查詢拍攝距今不到5年或者年或者10年以上的電影年以上的電影l(fā)SELECT 片名片名 FROM 電影電影 WHERE year(getdate()year( 年代年代) NOT BETWEEN 5 AND 10l查詢結(jié)果查詢結(jié)果片名片名重慶森林重慶森林數(shù)據(jù)庫系統(tǒng)原理教學34l查詢拍攝距今不到查詢拍攝距今不到5年或者年或者10年以上的電影年以上的電影l(fā)SELECT 片名片名 FROM 電影電影 WHERE year(getdate()year( 年代年代) 10 數(shù)據(jù)庫系統(tǒng)原理教學35l IN和和N

16、OT INl格式格式 列名列名 NOT IN （集合）（集合）數(shù)據(jù)庫系統(tǒng)原理教學36l查詢拍攝距今為查詢拍攝距今為5，10或者或者15年的電影年的電影l(fā)SELECT 片名片名 FROM 電影電影 WHERE year(getdate() - year( 年代年代) IN (5, 10, 15)l查詢結(jié)果查詢結(jié)果片名片名花樣年華花樣年華數(shù)據(jù)庫系統(tǒng)原理教學37l查詢拍攝距今為查詢拍攝距今為5，10或者或者15年的電影年的電影l(fā)SELECT 片名片名 FROM 電影電影 WHERE year(getdate()year( 年代年代) = 5 OR year(getdate()year( 年代年代)

17、= 10 OR year(getdate()year( 年代年代) = 15 數(shù)據(jù)庫系統(tǒng)原理教學38l查詢拍攝距今年限不是查詢拍攝距今年限不是5，10或者或者15的電影的電影l(fā)SELECT 片名片名 FROM 電影電影 WHERE year(getdate() - year( 年代年代) NOT IN (5, 10, 15)l查詢結(jié)果查詢結(jié)果片名片名重慶森林重慶森林紅河谷紅河谷不見不散不見不散數(shù)據(jù)庫系統(tǒng)原理教學39l查詢拍攝距今年限不是查詢拍攝距今年限不是5，10或者或者15的電影的電影l(fā)SELECT 片名片名 FROM 電影電影 WHERE year(getdate()year( 年代年代)

18、 != 5 AND year(getdate()year( 年代年代) != 10 AND year(getdate()year( 年代年代) != 15 數(shù)據(jù)庫系統(tǒng)原理教學40l5.4.1 單表查詢單表查詢l5.4.2 查詢條件的設(shè)置查詢條件的設(shè)置l5.4.3 聚合函數(shù)聚合函數(shù)l5.4.4 分組分組l5.4.5 查詢結(jié)果的排序查詢結(jié)果的排序l5.4.6 多表連接查詢多表連接查詢l5.4.7 子查詢子查詢l5.4.8 視圖視圖數(shù)據(jù)庫系統(tǒng)原理教學41l計算函數(shù)計算函數(shù)=l集合函數(shù)集合函數(shù)=l聚合函數(shù)聚合函數(shù)=l聚集函數(shù)聚集函數(shù)l對一組值進行計算并返回一個單值對一組值進行計算并返回一個單值數(shù)據(jù)庫系

19、統(tǒng)原理教學42lCOUNT（ * ）: 統(tǒng)計表中元組個數(shù)統(tǒng)計表中元組個數(shù)lCOUNT（ ）: 統(tǒng)計本列列值個數(shù)統(tǒng)計本列列值個數(shù)lMAX（ ）：求列值最大值）：求列值最大值lMIN（ ）：求列值最小值）：求列值最小值lSUM（ ）: 計算列值總和計算列值總和lAVG（ ）: 計算列值平均值計算列值平均值數(shù)據(jù)庫系統(tǒng)原理教學43l在在SELECT，HAVING子句中使用子句中使用l計算函數(shù)不能直接出現(xiàn)在計算函數(shù)不能直接出現(xiàn)在WHERE子句中子句中l(wèi)如果使用了計算函數(shù)或者分組，那么如果使用了計算函數(shù)或者分組，那么 SELECT子句中的列必須是計算函數(shù)或者是分組依據(jù)列子句中的列必須是計算函數(shù)或者是分組依

20、據(jù)列l(wèi)SUM 與與AVG只能用于數(shù)值類型的列，參數(shù)可以只能用于數(shù)值類型的列，參數(shù)可以是計算列是計算列l(wèi)要求只有一個參數(shù)要求只有一個參數(shù)l除除COUNT（*）外，其他函數(shù)在計算過程中均忽）外，其他函數(shù)在計算過程中均忽略略NULL值值數(shù)據(jù)庫系統(tǒng)原理教學44 數(shù)據(jù)庫系統(tǒng)原理教學45l查詢演員出演的總?cè)舜螖?shù)查詢演員出演的總?cè)舜螖?shù)l查詢演員出演記錄的總條數(shù)查詢演員出演記錄的總條數(shù)lSELECT COUNT(*) FROM 出演出演lSELECT COUNT(演員姓名演員姓名) FROM 出演出演lSELECT COUNT(片名片名) FROM 出演出演lSELECT COUNT(年代年代) FROM 出

21、演出演 lSELECT COUNT(片酬片酬) FROM 出演出演l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學46l查詢有片酬記錄的總條數(shù)查詢有片酬記錄的總條數(shù)lSELECT COUNT(片酬片酬) AS 片酬記錄條數(shù)片酬記錄條數(shù)lFROM 出演出演l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學47l查詢出演表中不同演員的人數(shù)查詢出演表中不同演員的人數(shù)lSELECT COUNT(DISTINCT 演員姓名演員姓名) AS 演員總數(shù)演員總數(shù)lFROM 出演出演l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學48l查詢出演表中支付的片酬總數(shù)查詢出演表中支付的片酬總數(shù)lSELECT SUM(片酬片酬) AS 總片酬總片酬lFRO

22、M 出演出演l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學49l查詢梁朝偉的片酬總數(shù)查詢梁朝偉的片酬總數(shù)lSELECT 演員姓名，演員姓名，SUM(片酬片酬) AS 總片酬總片酬lFROM 出演出演lWHERE 演員姓名演員姓名 梁朝偉梁朝偉l查詢結(jié)果查詢結(jié)果 列列 出演出演.演員姓名演員姓名 在選擇列表中無效，因為在選擇列表中無效，因為該列未包含在聚合函數(shù)中，并且沒有該列未包含在聚合函數(shù)中，并且沒有 GROUP BY 子句。子句。數(shù)據(jù)庫系統(tǒng)原理教學50l查詢梁朝偉的片酬總數(shù)查詢梁朝偉的片酬總數(shù)lSELECT SUM(片酬片酬) AS 梁朝偉的總片酬梁朝偉的總片酬lFROM 出演出演lWHERE 演員姓

23、名演員姓名 梁朝偉梁朝偉l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學51l查詢每人次出演支付的片酬平均數(shù)查詢每人次出演支付的片酬平均數(shù)lSELECT AVG(片酬片酬) AS 平均片酬平均片酬lFROM 出演出演l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學52lSELECT AVG(片酬片酬),l SUM(片酬片酬)/COUNT(片酬片酬),l SUM(片酬片酬)/COUNT(*)lFROM 出演出演l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學53l查詢梁朝偉的平均片酬查詢梁朝偉的平均片酬lSELECT 演員姓名，演員姓名，AVG(片酬片酬) AS 平均片酬平均片酬lFROM 出演出演lWHERE 演員姓名演員姓名

24、梁朝偉梁朝偉l查詢結(jié)果查詢結(jié)果 列列 出演出演.演員姓名演員姓名 在選擇列表中無效，因為在選擇列表中無效，因為該列未包含在聚合函數(shù)中，并且沒有該列未包含在聚合函數(shù)中，并且沒有 GROUP BY 子句。子句。數(shù)據(jù)庫系統(tǒng)原理教學54l查詢梁朝偉的平均片酬查詢梁朝偉的平均片酬lSELECT AVG(片酬片酬) AS 梁朝偉的平均片酬梁朝偉的平均片酬lFROM 出演出演lWHERE 演員姓名演員姓名 梁朝偉梁朝偉l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學55l查詢出演表中支付的最高片酬查詢出演表中支付的最高片酬lSELECT MAX(片酬片酬) AS 最高片酬最高片酬lFROM 出演出演l查詢結(jié)果查詢結(jié)果數(shù)

25、據(jù)庫系統(tǒng)原理教學56l查詢最低片酬以及獲得最低片酬的演員姓名查詢最低片酬以及獲得最低片酬的演員姓名lSELECT 演員姓名，演員姓名，MIN(片酬片酬) AS 最低片酬最低片酬lFROM 出演出演l查詢結(jié)果查詢結(jié)果 列列 出演出演.演員姓名演員姓名 在選擇列表中無效，因為在選擇列表中無效，因為該列未包含在聚合函數(shù)中，并且沒有該列未包含在聚合函數(shù)中，并且沒有 GROUP BY 子句。子句。數(shù)據(jù)庫系統(tǒng)原理教學57l查詢最低片酬以及有關(guān)演員的姓名查詢最低片酬以及有關(guān)演員的姓名lSELECT 演員姓名演員姓名lFROM 出演出演lWHERE 片酬片酬 MIN(片酬片酬) l查詢結(jié)果查詢結(jié)果 聚合不應出

26、現(xiàn)在聚合不應出現(xiàn)在 WHERE 子句中，除非該聚子句中，除非該聚合位于合位于 HAVING 子句或選擇列表所包含的子子句或選擇列表所包含的子查詢中，并且要對其進行聚合的列是外部引用。查詢中，并且要對其進行聚合的列是外部引用。數(shù)據(jù)庫系統(tǒng)原理教學58l5.4.1 單表查詢單表查詢l5.4.2 查詢條件的設(shè)置查詢條件的設(shè)置l5.4.3 聚合函數(shù)聚合函數(shù)l5.4.4 分組分組l5.4.5 查詢結(jié)果的排序查詢結(jié)果的排序l5.4.6 多表連接查詢多表連接查詢l5.4.7 子查詢子查詢l5.4.8 視圖視圖數(shù)據(jù)庫系統(tǒng)原理教學59lSELECT FROM WHERE GROUP BY HAVING 數(shù)據(jù)庫系統(tǒng)

27、原理教學60l分組把一個表劃分為子集，可以細化計分組把一個表劃分為子集，可以細化計算函數(shù)的作用對象算函數(shù)的作用對象lHAVING子句指定滿足條件的組子句指定滿足條件的組l在一個查詢語句中，可以使用任意多個在一個查詢語句中，可以使用任意多個列進行分組列進行分組l分組依據(jù)列不能是分組依據(jù)列不能是text，ntext，image和和bit類型類型數(shù)據(jù)庫系統(tǒng)原理教學61l查詢每位演員拍片數(shù)目以及個人平均片酬，查詢每位演員拍片數(shù)目以及個人平均片酬，個人最低個人最低/最高片酬最高片酬lSELECT 演員姓名演員姓名，COUNT(片名片名) 拍片數(shù)拍片數(shù)目，目，AVG(片酬片酬) 個人平均片酬，個人平均片酬

28、， MIN(片酬片酬) 個人最低片酬，個人最低片酬， MAX(片酬片酬) 個人最高片酬個人最高片酬lFROM 出演出演lGROUP BY 演員姓名演員姓名數(shù)據(jù)庫系統(tǒng)原理教學62數(shù)據(jù)庫系統(tǒng)原理教學63數(shù)據(jù)庫系統(tǒng)原理教學64 數(shù)據(jù)庫系統(tǒng)原理教學65lHAVING子句用于對分組后的結(jié)果再進子句用于對分組后的結(jié)果再進行過濾行過濾lHAVING子句的作用對象是組而不是行子句的作用對象是組而不是行l(wèi)在在HAVING子句中可以使用計算函數(shù)子句中可以使用計算函數(shù)lHAVING與與GROUP BY子句一起使用子句一起使用數(shù)據(jù)庫系統(tǒng)原理教學66l查詢出演不少于查詢出演不少于2部電影的演員姓名部電影的演員姓名數(shù)據(jù)庫

29、系統(tǒng)原理教學67數(shù)據(jù)庫系統(tǒng)原理教學68數(shù)據(jù)庫系統(tǒng)原理教學69lSELECT 演員姓名，演員姓名，COUNT(片名片名) 拍片數(shù)目拍片數(shù)目lFROM 出演出演lGROUP BY 演員姓名演員姓名lHAVING COUNT(*)=2數(shù)據(jù)庫系統(tǒng)原理教學70l先用先用GROUP BY按按演員姓名演員姓名分組分組l再用再用COUNT(*)統(tǒng)計每組包含的記錄條統(tǒng)計每組包含的記錄條數(shù)數(shù)l最后挑選記錄條數(shù)最后挑選記錄條數(shù)=2的組，返回對應的的組，返回對應的演員姓名演員姓名和該組記錄的條數(shù)和該組記錄的條數(shù)l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學71l查詢梁朝偉的個人片酬總數(shù)與個人平均片酬查詢梁朝偉的個人片酬總數(shù)與個

30、人平均片酬lSELECT 演員姓名，演員姓名，SUM(片酬片酬) 個人總片酬，個人總片酬， AVG(片酬片酬) 個人平均片酬個人平均片酬lFROM 出演出演lGROUP BY 演員姓名演員姓名lHAVING 演員姓名演員姓名 梁朝偉梁朝偉數(shù)據(jù)庫系統(tǒng)原理教學72查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學73lSELECT 演員姓名演員姓名, AVG(片酬片酬) 個人平均片酬個人平均片酬lFROM 出演出演lWHERE year(年代年代)100000l查詢查詢2000年以前個人最低片酬大于年以前個人最低片酬大于100k的演員的的演員的個人平均片酬個人平均片酬數(shù)據(jù)庫系統(tǒng)原理教學74數(shù)據(jù)庫系統(tǒng)原理教學75

31、數(shù)據(jù)庫系統(tǒng)原理教學76lSELECT 演員姓名演員姓名, AVG(片酬片酬) 個人平均片酬個人平均片酬lFROM 出演出演lGROUP BY 演員姓名演員姓名lHAVING MIN(片酬片酬)100000 and year(年代年代)2000l查詢結(jié)果查詢結(jié)果列列 出演出演.年代年代 在在 HAVING 子句中無效，因為該列子句中無效，因為該列既不包含在聚合函數(shù)中，也不包含在既不包含在聚合函數(shù)中，也不包含在 GROUP BY 子子句中。句中。l查詢查詢2000年以前個人最低片酬大于年以前個人最低片酬大于100k的演員的個人的演員的個人平均片酬平均片酬數(shù)據(jù)庫系統(tǒng)原理教學77l查詢查詢2000年以

32、前演員的個人平均片酬年以前演員的個人平均片酬lSELECT 演員姓名演員姓名, AVG(片酬片酬) 個人平均片酬個人平均片酬lFROM 出演出演lGROUP BY 演員姓名演員姓名lWHERE year(年代年代)100000 and year(年代年代)2000lGROUP BY 演員姓名演員姓名l查詢結(jié)果查詢結(jié)果聚合不應出現(xiàn)在聚合不應出現(xiàn)在 WHERE 子句中，除非該聚合位于子句中，除非該聚合位于 HAVING 子句或選擇列表所包含的子查詢中，并且子句或選擇列表所包含的子查詢中，并且要對其進行聚合的列是外部引用。要對其進行聚合的列是外部引用。l查詢查詢2000年以前個人最低片酬大于年以前個

33、人最低片酬大于100k的演員的個人的演員的個人平均片酬平均片酬數(shù)據(jù)庫系統(tǒng)原理教學79l5.4.1 單表查詢單表查詢l5.4.2 查詢條件的設(shè)置查詢條件的設(shè)置l5.4.3 聚合函數(shù)聚合函數(shù)l5.4.4 分組分組l5.4.5 查詢結(jié)果的排序查詢結(jié)果的排序l5.4.6 多表連接查詢多表連接查詢l5.4.7 子查詢子查詢l5.4.8 視圖視圖數(shù)據(jù)庫系統(tǒng)原理教學80lSELECT FROM WHERE GROUP BY HAVING ORDER BY 數(shù)據(jù)庫系統(tǒng)原理教學81l排序子句的格式為：排序子句的格式為： ORDER BY ASC | DESC , n l為排序的依據(jù)列，可以是列名或列為排序的依據(jù)

34、列，可以是列名或列的別名的別名lASC表示對列進行升序排序，表示對列進行升序排序，DESC表示對表示對列進行降序排序列進行降序排序l默認的排序方式為升序排序默認的排序方式為升序排序數(shù)據(jù)庫系統(tǒng)原理教學82l指定多個排序列時，首先按最前面的列進指定多個排序列時，首先按最前面的列進行排序行排序l如果排序后存在多個列值相同的記錄，則如果排序后存在多個列值相同的記錄，則對這些記錄依據(jù)后續(xù)排序列進行排序?qū)@些記錄依據(jù)后續(xù)排序列進行排序數(shù)據(jù)庫系統(tǒng)原理教學83l查詢?nèi)w演員的情況，按姓名排序查詢?nèi)w演員的情況，按姓名排序lSELECT * l FROM 演員演員 ORDER BY 姓名姓名l查詢結(jié)果查詢結(jié)果數(shù)

35、據(jù)庫系統(tǒng)原理教學84l查詢?nèi)w演員的情況，先女后男再按姓名排查詢?nèi)w演員的情況，先女后男再按姓名排序序lSELECT * l FROM 演員演員 ORDER BY 性別性別 DESC, 姓名姓名l查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學85lSELECT 演員姓名演員姓名, AVG(片酬片酬) 個人平均片酬個人平均片酬lFROM 出演出演lWHERE year(年代年代)100000 lORDER BY 演員姓名演員姓名 DESCl查詢結(jié)果查詢結(jié)果l查詢查詢2000年以前個人最低片酬大于年以前個人最低片酬大于100k的演員的個人的演員的個人平均片酬平均片酬數(shù)據(jù)庫系統(tǒng)原理教學86lSELECT 演員姓

36、名演員姓名, AVG(片酬片酬) 個人平均片酬個人平均片酬lFROM 出演出演lWHERE year(年代年代)100000l查詢結(jié)果查詢結(jié)果在關(guān)鍵字在關(guān)鍵字 GROUP 附近有語法錯誤。附近有語法錯誤。l查詢查詢2000年以前個人最低片酬大于年以前個人最低片酬大于100k的演員的個人的演員的個人平均片酬平均片酬數(shù)據(jù)庫系統(tǒng)原理教學87l5.4.1 單表查詢單表查詢l5.4.2 查詢條件的設(shè)置查詢條件的設(shè)置l5.4.3 聚合函數(shù)聚合函數(shù)l5.4.4 分組分組l5.4.5 查詢結(jié)果的排序查詢結(jié)果的排序l5.4.6 多表連接查詢多表連接查詢l5.4.7 子查詢子查詢l5.4.8 視圖視圖數(shù)據(jù)庫系統(tǒng)原

37、理教學88l查詢結(jié)果來源于多張表查詢結(jié)果來源于多張表l匹配不同表的公共列，實現(xiàn)多表連接匹配不同表的公共列，實現(xiàn)多表連接l連接的類型連接的類型內(nèi)連接內(nèi)連接外連接外連接數(shù)據(jù)庫系統(tǒng)原理教學89l. .l. BETWEEN . AND .l連接字段：連接謂詞中的列名稱連接字段：連接謂詞中的列名稱l連接條件中的各連接字段類型必須是可比的，但連接條件中的各連接字段類型必須是可比的，但名字不必是相同的名字不必是相同的數(shù)據(jù)庫系統(tǒng)原理教學90l最常用的比較運算符是最常用的比較運算符是lFROM 表表1，表，表2 WHERE . . l連接列必須是可比較的連接列必須是可比較的語義相同語義相同數(shù)據(jù)庫系統(tǒng)原理教學91

38、姓名姓名工號工號性別性別王菲王菲A010女女寧靜寧靜A120女女梁朝偉梁朝偉A231男男葛優(yōu)葛優(yōu)A751男男片名片名導演導演年代年代重慶森林重慶森林王家衛(wèi)王家衛(wèi)1994紅河谷紅河谷馮小寧馮小寧1998不見不散不見不散馮小剛馮小剛1998花樣年華花樣年華王家衛(wèi)王家衛(wèi)2000黑駿馬黑駿馬管虎管虎2000工號工號片名片名年代年代片酬片酬A120紅河谷紅河谷1998NULLA231花樣年華花樣年華2000100kA751不見不散不見不散1998120k演員演員電影電影出演出演數(shù)據(jù)庫系統(tǒng)原理教學92l查詢演員出演電影的情況查詢演員出演電影的情況lSELECT 演員演員.姓名，出演姓名，出演.片名，出演片

39、名，出演.年代年代lFROM 演員，出演演員，出演lWHERE 演員演員.工號工號 出演出演.工號工號數(shù)據(jù)庫系統(tǒng)原理教學93姓名姓名性別性別工號工號王菲王菲女女A010寧靜寧靜女女A120梁朝偉梁朝偉男男A231葛優(yōu)葛優(yōu)男男A751工號工號片名片名年代年代片酬片酬A120紅河谷紅河谷1998NULLA231花樣年華花樣年華2000100kA751不見不散不見不散1998120k演員演員出演出演查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學94l查詢演員與導演的合作情況查詢演員與導演的合作情況lSELECT 姓名姓名,導演導演, 電影電影.片名片名,l year(電影電影.年代年代) 年代年代 lFROM

40、演員，電影，出演演員，電影，出演lWHERE 演員演員.工號工號=出演出演.工號工號 AND l 出演出演.片名片名=電影電影.片名片名 AND l 出演出演.年代年代=電影電影.年代年代 數(shù)據(jù)庫系統(tǒng)原理教學95片名片名年代年代導演導演重慶森林重慶森林1994王家衛(wèi)王家衛(wèi)紅河谷紅河谷1998馮小寧馮小寧不見不散不見不散1998馮小剛馮小剛花樣年華花樣年華2000王家衛(wèi)王家衛(wèi)黑駿馬黑駿馬2000管虎管虎工號工號片名片名年代年代片酬片酬A120紅河谷紅河谷1998NULLA231花樣年華花樣年華2000100kA751不見不散不見不散1998120k演員演員電影電影出演出演姓名姓名性別性別工號工號

41、王菲王菲女女A010寧靜寧靜女女A120梁朝偉梁朝偉男男A231葛優(yōu)葛優(yōu)男男A751數(shù)據(jù)庫系統(tǒng)原理教學96 數(shù)據(jù)庫系統(tǒng)原理教學97l查詢與王家衛(wèi)影片同年拍攝的其他導演的影查詢與王家衛(wèi)影片同年拍攝的其他導演的影片片lSELECT 電影電影1.片名，片名， 電影電影1.導演，導演， year（電影（電影1.年代）年代） 年代年代lFROM 電影電影 電影電影1，電影，電影 電影電影2lWHERE 電影電影2.導演導演 = 王家衛(wèi)王家衛(wèi) ANDl 電影電影2.年代年代 = 電影電影1.年代年代 ANDl 電影電影1.導演導演 != 王家衛(wèi)王家衛(wèi)數(shù)據(jù)庫系統(tǒng)原理教學98片名片名導演導演年代年代重慶森林重

42、慶森林王家衛(wèi)王家衛(wèi)1994紅河谷紅河谷馮小寧馮小寧1998不見不散不見不散馮小剛馮小剛1998花樣年華花樣年華王家衛(wèi)王家衛(wèi)2000黑駿馬黑駿馬管虎管虎2000電影電影 1年代年代導演導演片名片名1994王家衛(wèi)王家衛(wèi)重慶森林重慶森林1998馮小寧馮小寧紅河谷紅河谷1998馮小剛馮小剛不見不散不見不散2000王家衛(wèi)王家衛(wèi)花樣年華花樣年華2000管虎管虎黑駿馬黑駿馬電影電影 2查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學99l連接時，輸出一方的全部元組和對方的匹配元組連接時，輸出一方的全部元組和對方的匹配元組lFROM l 表表1 LEFT | RIGHT OUTER JOIN 表表2 l ON 數(shù)據(jù)庫系統(tǒng)原

43、理教學100lFROM l 表表1 LEFT OUTER JOIN 表表2 l ON l輸出：表輸出：表1全部，表全部，表2滿足連接條件元組滿足連接條件元組數(shù)據(jù)庫系統(tǒng)原理教學101lFROM l 表表1 RIGHT OUTER JOIN 表表2 l ON l輸出輸出:表表1滿足連接條件元組，表滿足連接條件元組，表2全部全部數(shù)據(jù)庫系統(tǒng)原理教學102l查詢所有演員出演影片的情況，包括沒有出查詢所有演員出演影片的情況，包括沒有出演記錄的演員演記錄的演員lSELECT 姓名姓名, 片名片名lFROM 演員演員 LEFT OUTER JOIN 出演出演 l ON 演員演員.工號工號=出演出演.工號工號數(shù)

44、據(jù)庫系統(tǒng)原理教學103姓名姓名性別性別工號工號王菲王菲女女A010寧靜寧靜女女A120梁朝偉梁朝偉男男A231葛優(yōu)葛優(yōu)男男A751工號工號片名片名年代年代片酬片酬A120紅河谷紅河谷1998NULLA231花樣年華花樣年華2000100kA751不見不散不見不散1998120k演員演員出演出演查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學104l查詢所有演員出演影片的情況，包括沒有出查詢所有演員出演影片的情況，包括沒有出演記錄的演員演記錄的演員lSELECT 姓名姓名, 片名片名lFROM 出演出演 RIGHT OUTER JOIN 演員演員 l ON 演員演員.工號工號=出演出演.工號工號數(shù)據(jù)庫系統(tǒng)原理

45、教學105工號工號姓名姓名性別性別A010王菲王菲女女A120寧靜寧靜女女A231梁朝偉梁朝偉男男A751葛優(yōu)葛優(yōu)男男片名片名年代年代片酬片酬工號工號紅河谷紅河谷1998NULLA120花樣年華花樣年華2000100kA231不見不散不見不散1998120kA751演員演員出演出演查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學106l5.4.1 單表查詢單表查詢l5.4.2 查詢條件的設(shè)置查詢條件的設(shè)置l5.4.3 聚合函數(shù)聚合函數(shù)l5.4.4 分組分組l5.4.5 查詢結(jié)果的排序查詢結(jié)果的排序l5.4.6 多表連接查詢多表連接查詢l5.4.7 子查詢子查詢l5.4.8 視圖視圖數(shù)據(jù)庫系統(tǒng)原理教學107l

46、SQL的的查詢塊查詢塊：一個：一個SELECTFROMWHERE的組合體的組合體l子查詢子查詢內(nèi)層查詢內(nèi)層查詢：嵌套在：嵌套在SELECT、UPDATE或或DELETE等語句中的查詢塊等語句中的查詢塊l主查詢主查詢外層查詢外層查詢：包含子查詢的語句：包含子查詢的語句數(shù)據(jù)庫系統(tǒng)原理教學108l子查詢要寫在圓括號中子查詢要寫在圓括號中l(wèi)查詢塊中不能使用查詢塊中不能使用ORDER BY子句子句l引入子查詢的保留字為引入子查詢的保留字為INl子查詢通常用在外層查詢的子查詢通常用在外層查詢的WHERE子子句或句或HAVING子句中子句中數(shù)據(jù)庫系統(tǒng)原理教學109l帶有帶有IN謂詞的子查詢謂詞的子查詢 l帶

47、有比較運算符的子查詢帶有比較運算符的子查詢l帶有帶有ANY（SOME）或）或ALL謂詞的子查詢謂詞的子查詢l帶有帶有EXISTS謂詞的子查詢謂詞的子查詢數(shù)據(jù)庫系統(tǒng)原理教學110姓名姓名工號工號性別性別王菲王菲A010女女寧靜寧靜A120女女梁朝偉梁朝偉A231男男葛優(yōu)葛優(yōu)A751男男片名片名導演導演年代年代重慶森林重慶森林王家衛(wèi)王家衛(wèi)1994紅河谷紅河谷馮小寧馮小寧1998不見不散不見不散馮小剛馮小剛1998花樣年華花樣年華王家衛(wèi)王家衛(wèi)2000黑駿馬黑駿馬管虎管虎2000工號工號片名片名年代年代片酬片酬A120紅河谷紅河谷1998NULLA231花樣年華花樣年華2000100kA751不見不散

48、不見不散1998120k演員演員電影電影出演出演數(shù)據(jù)庫系統(tǒng)原理教學111l查詢片酬最低的演員姓名查詢片酬最低的演員姓名lSELECT 演員演員.姓名姓名lFROM 演員演員, 出演出演lWHERE 演員演員.工號工號=出演出演.工號工號 ANDl 片酬片酬 IN ( SELECT MIN (片酬片酬) l FROM 出演出演 )數(shù)據(jù)庫系統(tǒng)原理教學112姓名姓名工號工號性別性別王菲王菲A010女女寧靜寧靜A120女女梁朝偉梁朝偉A231男男葛優(yōu)葛優(yōu)A751男男片名片名導演導演年代年代重慶森林重慶森林王家衛(wèi)王家衛(wèi)1994紅河谷紅河谷馮小寧馮小寧1998不見不散不見不散馮小剛馮小剛1998花樣年華花

49、樣年華王家衛(wèi)王家衛(wèi)2000黑駿馬黑駿馬管虎管虎2000工號工號片名片名年代年代片酬片酬A120紅河谷紅河谷1998NULLA231花樣年華花樣年華2000100kA751不見不散不見不散1998120k演員演員電影電影出演出演數(shù)據(jù)庫系統(tǒng)原理教學113查詢結(jié)果查詢結(jié)果數(shù)據(jù)庫系統(tǒng)原理教學114 WHERE Sage ANY（ ） AND Sdept IS ORDER BY Sage DESC 例（例（帶帶ANY或或ALL謂詞的子查詢）找出比謂詞的子查詢）找出比IS系至系至少一名學生年齡小的其他系學生名單。少一名學生年齡小的其他系學生名單。 SELECT Sname，Sage，Sdept FROM

50、StudentSELECT distinct Sage FROM Student WHERE Sdept=IS數(shù)據(jù)庫系統(tǒng)原理教學115WHERE （ Sage ALL（ ） ） AND （ Sdept IS ）ORDER BY Sage DESC 例例 查詢比查詢比IS系系所有所有學生年齡學生年齡都小都小的其他各系學生的其他各系學生姓名姓名, 年齡，系名。年齡，系名。SELECT Sage FROM Student WHERE Sdept=IS SELECT Sname，Sage，Sdept FROM Student數(shù)據(jù)庫系統(tǒng)原理教學116l由由EXISTS引出的子查詢，其目標列表達式通常引出

51、的子查詢，其目標列表達式通常都用都用* ，因為帶，因為帶EXISTS的子查詢只返回真值或的子查詢只返回真值或假值，給出列名無實際意義假值，給出列名無實際意義lEXISTSl若內(nèi)層查詢結(jié)果非空，則外層的若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句子句返回真值返回真值l若內(nèi)層查詢結(jié)果為空，則外層的若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句子句返回假值返回假值數(shù)據(jù)庫系統(tǒng)原理教學117l查詢所有選修了查詢所有選修了1號課程的學生姓名號課程的學生姓名l用嵌套查詢用嵌套查詢l SELECT Snamel FROM Studentl WHERE EXISTSl (SELECT *l FROM SCl WHER

52、E Sno=Student.Sno AND Cno= 1 )數(shù)據(jù)庫系統(tǒng)原理教學118l查詢所有選修了查詢所有選修了1號課程的學生姓名號課程的學生姓名l用連接查詢用連接查詢l SELECT SnamelFROM Student, SClWHERE Student.Sno=SC.Sno AND l SC.Cno= 1數(shù)據(jù)庫系統(tǒng)原理教學119l查詢選修了全部課程的學生姓名查詢選修了全部課程的學生姓名lSELECT Snamel FROM Studentl WHERE NOT EXISTSl （SELECT * FROM Coursel WHERE NOT EXISTSl (SELECT * FROM

53、 SCl WHERE Sno= Student.Snol AND Cno= ourse.Cnol ） ）數(shù)據(jù)庫系統(tǒng)原理教學120數(shù)據(jù)庫系統(tǒng)原理教學121 例例1 查找選修了課程查找選修了課程1或課程或課程2的學生學號和姓名。的學生學號和姓名。 SELECT SC.Sno, Sname FROM SC, Student WHERE (Cno=2) AND (SC.Sno =Student.Sno) SELECT SC.Sno, Sname FROM SC, Student WHERE (Cno=1) AND (SC.Sno =Student.Sno) UNION集合查詢就是對集合查詢就是對幾個查

54、詢塊的結(jié)果集幾個查詢塊的結(jié)果集進行并、進行并、交、差運算。交、差運算。數(shù)據(jù)庫系統(tǒng)原理教學122 例例1 查找選修了課程查找選修了課程1或課程或課程2的學生學號和姓名。的學生學號和姓名。 SELECT SC.Sno, Sname FROM SC, Student WHERE ( (Cno=1) OR (Cno=2) AND (SC.Sno =Student.Sno)集合查詢就是對集合查詢就是對幾個查詢塊的結(jié)果集幾個查詢塊的結(jié)果集進行并、進行并、交、差運算。交、差運算。數(shù)據(jù)庫系統(tǒng)原理教學123 AND Sno IN （SELECT Sno FROM SC WHERE Cno =2） 例例2 查找既選修課程查找既選修課程1，又選修課程，又選修課程2的學生集合的學生集合 這實際上是查找選修了課程這實際上是查找選修了課程1學生，與選修了學生，與選修了課程課程2 的