《《數(shù)據(jù)庫系統(tǒng)概論》PPT課件.ppt》由會員分享，可在線閱讀，更多相關(guān)《《數(shù)據(jù)庫系統(tǒng)概論》PPT課件.ppt（80頁珍藏版）》請在裝配圖網(wǎng)上搜索。
1、An Introduction to Database System 數(shù)據(jù)庫系統(tǒng)概論 An Introduction to Database System 第三章 關(guān)系數(shù)據(jù)庫標準語言 SQL An Introduction to Database System 第三章 關(guān)系數(shù)據(jù)庫標準語言 SQL 3.1 SQL概述 3.2 數(shù)據(jù)定義 3.3 查詢 3.4 數(shù)據(jù)更新 3.5 視圖 3.6 數(shù)據(jù)控制 3.7 嵌入式 SQL 3.8 小結(jié) An Introduction to Database System 3.1 SQL概述 SQL的特點 1. 綜合統(tǒng)一 2. 高度
2、非過程化 3. 面向集合的操作方式 4. 以同一種語法結(jié)構(gòu)提供兩種使用方法 5. 語言簡潔，易學易用 An Introduction to Database System 5. 語言簡捷，易學易用 表 3 . 1 S Q L 語言的動詞 S Q L 功 能 動 詞 數(shù) 據(jù) 定 義 C R E A T E ， D R O P ， A L T E R 數(shù) 據(jù) 查 詢 S E L E C T 數(shù) 據(jù) 操 縱 I N S E R T ， U P D A T E D E L E T E 數(shù) 據(jù) 控 制 G R A N T ， R E V O K E An Introduction t
3、o Database System 第三章 關(guān)系數(shù)據(jù)庫標準語言 SQL 3.1 SQL概述 3.2 數(shù)據(jù)定義 3.3 查詢 3.4 數(shù)據(jù)更新 3.5 視圖 3.6 數(shù)據(jù)控制 3.7 嵌入式 SQL 3.8 小結(jié) An Introduction to Database System 3.2 數(shù) 據(jù) 定 義 表 3 . 2 S Q L 的數(shù)據(jù)定義語句 操 作 方 式 操 作 對 象 創(chuàng) 建 刪 除 修 改 表 C R E A T E T A B L E D R O P T A B L E A L T E R T A B L E 視 圖 C R E A T E V
4、I E W D R O P V I E W 索 引 C R E A T E I N D E X D R O P I N D E X An Introduction to Database System 3.2.1 定義 語句格式 CREATE TABLE （ ， ， ） ； ：所要定義的基本表的名字 ：組成該表的各個屬性 （ 列 ） ：涉及相應(yīng)屬性列的完整性 約束條件 ：涉及一個或多個屬性列的 完整性約束條件 An Introduction to Database System 例題 例 1 建立一個 “ 學生 ” 表 Student，
5、 它由學號 Sno、 姓名 Sname、 性別 Ssex、 年齡 Sage、 所 在系 Sdept五個屬性組成 。 其中學號不能為空 ， 值是唯一的 ， 并且姓名取值也唯一 。 CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE， Sname CHAR(20) UNIQUE， Ssex CHAR(1) ， Sage INT， Sdept CHAR(15))； An Introduction to Database System 定義基本表（續(xù)） 常用完整性約束
6、 主碼約束： PRIMARY KEY 唯一性約束： UNIQUE 非空值約束： NOT NULL 參照完整性約束 PRIMARY KEY與 UNIQUE的區(qū)別？ An Introduction to Database System 例題 （續(xù)） 例 2 建立一個“學生選課”表 SC，它由 學號 Sno、課程號 Cno，修課成績 Grade組 成，其中 (Sno, Cno)為主碼。 CREATE TABLE SC( Sno CHAR(5) , Cno CHAR(3) , Grade int, Primary key (S
7、no, Cno)); An Introduction to Database System 例題 （續(xù)） Sno S n am e S s e x S age S d e p t 字符型 字符型 字符型 整數(shù) 字符型 長度為 5 長度為 20 長度為 1 長度為 15 不能為空值 An Introduction to Database System 三、刪除基本表 DROP TABLE ; 基本表刪除 數(shù)據(jù) 、 表上的索引都刪除 表上的視圖往往仍然保留， 但 無法引用 刪除基本表時，
8、系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該 基本表及其索引的描述 （標準中沒有，認為表建立后就永久存在） An Introduction to Database System 例題 例 5 刪除 Student表 DROP TABLE Student ; An Introduction to Database System 二、修改基本表 ALTER TABLE ADD 完整性約束 DROP MODIFY ； ： 要修改的基本表 ADD子句 ： 增加新列和新的完整性約束條件 DROP子句 ： 刪除指定的完整性約束條件 MODIFY子句 ： 用于修改列名
9、和數(shù)據(jù)類型 An Introduction to Database System 例題 例 2 向 Student表增加 “ 入學時間 ” 列 ， 其數(shù)據(jù)類 型為日期型 。 ALTER TABLE Student ADD Scome DATE； 不論基本表中原來是否已有數(shù)據(jù) ， 新增加的列一律為 空值 。 An Introduction to Database System 語句格式（續(xù)） 刪除屬性列 直接 /間接刪除 把表中要保留的列及其內(nèi)容復(fù)制到一個新表中 刪除原表 再將新表重命名為原表名 直接刪除屬性列 :(新 ) 例： ALTER TABLE
10、Student Drop Scome； An Introduction to Database System 例 3 將年齡的數(shù)據(jù)類型改為半字長整數(shù)。 ALTER TABLE Student MODIFY Sage SMALLINT； 注：修改原有的列定義有可能會破壞已有數(shù)據(jù) An Introduction to Database System 例題 例 4 刪除學生姓名必須取唯一值的約束 。 ALTER TABLE Student DROP UNIQUE(Sname)； An Introduction to Database System 3.2.2 建立與刪除
11、索引 建立索引是加快查詢速度的有效手段 建立索引 DBA或表的屬主 （ 即建立表的人 ） 根據(jù)需要建立 有些 DBMS自動建立以下列上的索引 PRIMARY KEY UNIQUE 維護索引 DBMS自動完成 使用索引 DBMS自動選擇是否使用索引以及使用哪些索引 An Introduction to Database System 一、建立索引 語句格式 CREATE UNIQUE CLUSTER INDEX ON (, )； 用 指定要建索引的基本表名字 索引可以建立在該表的一 列 或多列上 ， 各列名之間用逗號分 隔 用 指定索
12、引值的排列次序 ， 升序： ASC， 降序： DESC。 缺省值： ASC UNIQUE表明此索引的每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄 CLUSTER表示要建立的索引是聚簇索引 An Introduction to Database System 例題 例 6 為學生 -課程數(shù)據(jù)庫中的 Student， Course， SC三個 表建立索引 。 其中 Student表按學號升序建唯一索引 ， Course表按課程號升序建唯一索引 ， SC表按學號升序和課 程號降序建唯一索引 。 CREATE UNIQUE INDEX Stusno ON Student(Sno)； CREATE
13、 UNIQUE INDEX Coucno ON Course(Cno)； CREATE UNIQUE INDEX SCno ON SC(Sno ASC， Cno DESC)； An Introduction to Database System 建立索引 （續(xù)） 唯一值索引 對于已含重復(fù)值的屬性列不能建 UNIQUE索引 對某個列建立 UNIQUE索引后 ， 插入新記錄時 DBMS 會自動檢查新記錄在該列上是否取了重復(fù)值 。 這相 當于增加了一個 UNIQUE約束 An Introduction to Database System 建立索引 （續(xù)） 聚簇索引 建立聚簇索引
14、后 ， 基表中數(shù)據(jù)也需要按指 定的聚簇屬性值的升序或降序存放 。 也即 聚簇索引的索引項順序與表中記錄的物理 順序一致 例： CREATE CLUSTER INDEX Stusname ON Student(Sname)； 在 Student表的 Sname（ 姓名 ） 列上建立一個聚簇索引 ， 而 且 Student表中的記錄將按照 Sname值的升序存放 An Introduction to Database System 建立索引 （續(xù)） 在一個基本表上最多只能建立一個聚簇索引 聚簇索引的用途：對于某些類型的查詢，可 以提高查詢效率 聚簇索引的適用范圍 很少對基表進
15、行增刪操作 很少對其中的變長列進行修改操作 An Introduction to Database System 二、刪除索引 DROP INDEX ； 刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān) 該索引的描述。 例 7 刪除 Student表的 Stusname索引。 DROP INDEX Stusname； An Introduction to Database System 3.3 查 詢 3.3.1 概述 3.3.2 單表查詢 3.3.3 連接查詢 3.3.4 嵌套查詢 3.3.5 集合查詢 3.3.6 小結(jié) An Introduction to Data
16、base System 3.3.1 概述 語句格式 SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ； An Introduction to Database System 語句格式 SELECT子句 ：指定要顯示的屬性列 FROM子句 ：指定查詢對象 (基本表或視圖 ) WHERE子句 ：指定查詢條件 GROUP BY子句 ：對查詢結(jié)果按指定列的值分組 ， 該 屬性列值相等的元組為一個組 。 通常會在每
17、組中作用 集函數(shù) 。 HAVING短語 ：篩選出只有滿足指定條件的組 ORDER BY子句 ：對查詢結(jié)果表按指定列值的升序或降 序排序 An Introduction to Database System 示例數(shù)據(jù)庫 學生 -課程數(shù)據(jù)庫 學生表： Student(Sno， Sname， Ssex， Sage， Sdept) 課程表： Course(Cno， Cname， Cpno， Ccredit) 學生選課表： SC(Sno， Cno， Grade) An Introduction to Database System 3.3 查 詢 3.3.1 概述 3.3.
18、2 單表查詢 3.3.3 連接查詢 3.3.4 嵌套查詢 3.3.5 集合查詢 3.3.6 小結(jié) An Introduction to Database System 3.3.2 單表查詢 查詢僅涉及一個表 ， 是一種最簡單的查詢操作 一 、 選擇表中的若干列 二 、 選擇表中的若干元組 三 、 對查詢結(jié)果排序 四 、 使用集函數(shù) 五、對查詢結(jié)果分組 An Introduction to Database System 查詢指定列 例 1 查詢?nèi)w學生的學號與姓名 。 SELECT Sno， Sname FROM Student； 例 2 查詢?nèi)w學生的姓
19、名 、 學號 、 所在系 。 SELECT Sname， Sno， Sdept FROM Student； An Introduction to Database System 查詢?nèi)苛? 例 3 查詢?nèi)w學生的詳細記錄 。 SELECT Sno， Sname， Ssex， Sage， Sdept FROM Student； 或 SELECT * FROM Student； An Introduction to Database System 3. 查詢經(jīng)過計算的值 SELECT子句的 為表達式 算術(shù)表達式 字符串常量 函數(shù) 列別名 等 An I
20、ntroduction to Database System 3. 查詢經(jīng)過計算的值 例 4 查全體學生的姓名及其出生年份 。 SELECT Sname， 2000-Sage FROM Student； 輸出結(jié)果： Sname 2000-Sage --------- ------------- 李勇 1976 劉晨 1977 王名 1978 張立 1978 An Introduction to Database System 3. 查詢經(jīng)過計算的值 例 5 查詢?nèi)w學生的
21、姓名 、 出生年份和所 有系 ， 要求用小寫字母表示所有系名 。 SELECT Sname， Year of Birth: ， 2000-Sage， ISLOWER(Sdept) FROM Student； An Introduction to Database System 例題（續(xù)） 輸出結(jié)果： Sname Year of Birth: 2000-Sage ISLOWER(Sdept) ------- ---------------- --------- -------------- 李勇 Year of Birth: 1976 c
22、s 劉晨 Year of Birth: 1977 is 王名 Year of Birth: 1978 ma 張立 Year of Birth: 1977 is An Introduction to Database System 例 5.1 使用列 別名 改變查詢結(jié)果的列標題 SELECT Sname NAME， Year of Birth: BIRTH， 2000-Sage BIRTHDAY， ISLOWER(Sdept) DEPARTMENT FROM Student； 輸出結(jié)果： NAME BIRTH
23、 BIRTHDAY DEPARTMENT ------- ---------------- ------------- ------------------ 李勇 Year of Birth: 1976 cs 劉晨 Year of Birth: 1977 is 王名 Year of Birth: 1978 ma 張立 Year of Birth: 1977 is An Introduction to Database System 二、選擇表中的若干元組 消除取值重復(fù)的行 查詢滿足條件的元組 An
24、 Introduction to Database System 1. 消除取值重復(fù)的行 在 SELECT子句中使用 DISTINCT短語 假設(shè) SC表中有下列數(shù)據(jù) Sno Cno Grade ------- ------- ------- 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80 An Introduction to Database
25、 System ALL 與 DISTINCT 例 6 查詢選修了課程的學生學號 。 (1) SELECT Sno FROM SC; 或 (默認 ALL) SELECT ALL Sno FROM SC; 結(jié)果： Sno ------- 95001 95001 95001 95002 95002 An Introduction to Database System 例題（續(xù)） (2) SELECT DISTINCT Sno FROM SC; 結(jié)果：
26、 Sno ------- 95001 95002 An Introduction to Database System 例題（續(xù)） 注意 DISTINCT短語的作用范圍是所有目標列 例：查詢選修課程的各種成績 錯誤的寫法 SELECT DISTINCT Cno， DISTINCT Grade FROM SC; 正確的寫法 SELECT DISTINCT Cno， Grade FROM SC; An Introduction to Database System 2.查詢滿足條件的元組 表 3.3 常用的查詢條
27、件 查 詢 條 件 謂 詞 比 較 = ， ， = ， <= ， != ， ， ! ， !< ； N O T + 上述比較運算符 確定范圍 B E T WE E N A N D ， N O T B E T W E E N A N D 確定集合 IN ， N O T I N 字符匹配 L I K E ， N O T L I K E 空 值 I S N U L L ， I S N O T N U L L 多重條件 AND ， OR WHERE子句常用的查詢條件 An Introduction to Database System (1) 比較大小 在 WHERE子句的 中使用比較
28、運算符 =， ， =， <=， != 或 ， !， !<， 邏輯運算符 NOT + 比較運算符 例 8 查詢所有年齡在 20歲以下的學生姓名及其年齡。 SELECT Sname， Sage FROM Student WHERE Sage = 20； An Introduction to Database System (2) 確定范圍 使用謂詞 BETWEEN AND NOT BETWEEN AND 例 10 查詢年齡在 2023歲（包括 20歲和 23歲）之間的 學生的姓名、系別和年齡 。 SELECT Sname， S
29、dept， Sage FROM Student WHERE Sage BETWEEN 20 AND 23； An Introduction to Database System 例題（續(xù)） 例 11 查詢年齡不在 2023歲之間的學生姓名 、 系別和年齡 。 SELECT Sname， Sdept， Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23； An Introduction to Database System (3) 確定集合 使用謂詞 IN , NOT IN ： 用逗號分隔的一組取值 例 12查詢
30、信息系（ IS）、數(shù)學系（ MA）和計 算機科學系（ CS）學生的姓名和性別。 SELECT Sname， Ssex FROM Student WHERE Sdept IN ( IS， MA， CS ); An Introduction to Database System (3) 確定集合 例 13查詢既不是信息系 、 數(shù)學系 ， 也不是計算 機科學系的學生的姓名和性別 。 SELECT Sname， Ssex FROM Student WHERE Sdept NOT IN ( IS， MA， CS ); An Introduction to Databa
31、se System (4) 字符串匹配 NOT LIKE ESCAPE ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 當匹配模板為固定字符串時 ， 可以用 = 運算符取代 LIKE 謂詞 用 != 或 運算符取代 NOT LIKE 謂詞 An Introduction to Database System 通配符 % (百分號 ) 代表任意長度（長度可以為 0）的字符串 例： a%b表示以 a開頭，以 b結(jié)尾的任意長度的字符 串。如 acb， addgb， ab 等都滿足該匹配串 _ (下橫線 ) 代表任意單個字符 例：
32、a_b表示以 a開頭，以 b結(jié)尾的長度為 3的任意字 符串。如 acb， afb等都滿足該匹配串 An Introduction to Database System ESCAPE 短語： 當用戶要查詢的字符串本身就含有 % 或 _ 時，要使用 ESCAPE 短語對通配符進行轉(zhuǎn)義。 An Introduction to Database System 例題 1) 匹配模板為固定字符串 例 14 查詢學號為 95001的學生的詳細情況 。 SELECT * FROM Student WHERE Sno LIKE 95001； 等價于： SELEC
33、T * FROM Student WHERE Sno = 95001； An Introduction to Database System 例題（續(xù)） 2) 匹配模板為含通配符的字符串 例 15 查詢所有姓劉學生的姓名、學號和性別。 SELECT Sname， Sno， Ssex FROM Student WHERE Sname LIKE 劉 %； An Introduction to Database System 例題（續(xù)） 匹配模板為含通配符的字符串（續(xù)） 例 16 查詢姓 歐陽 且全名為三個漢字的學生的姓 名。 SELEC
34、T Sname FROM Student WHERE Sname LIKE 歐陽 __； An Introduction to Database System 例題（續(xù)） 匹配模板為含通配符的字符串（續(xù)） 例 17 查詢名字中第 2個字為 陽 字的學生的姓名 和學號。 SELECT Sname， Sno FROM Student WHERE Sname LIKE __陽 %； An Introduction to Database System 例題（續(xù)） 匹配模板為含通配符的字符串（續(xù)） 例 18 查詢所有不姓劉的學生姓名。 SEL
35、ECT Sname， Sno， Ssex FROM Student WHERE Sname NOT LIKE 劉 %； An Introduction to Database System 例題（續(xù)） 3) 使用換碼字符將通配符轉(zhuǎn)義為普通字符 例 19 查詢 DB_Design課程的課程號和學分。 SELECT Cno， Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE An Introduction to Database System 例題（續(xù)） 使用換碼字
36、符將通配符轉(zhuǎn)義為普通字符 (續(xù) ) 例 20 查詢以 DB_開頭，且倒數(shù)第 3個字符為 i的 課程的詳細情況。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ； An Introduction to Database System (5) 涉及空值的查詢 使用謂詞 IS NULL 或 IS NOT NULL “IS NULL” 不能用 “ = NULL” 代替 例 21 某些學生選修課程后沒有參加考試，所以有選課記 錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W生的學號和相應(yīng) 的課程號。 SELEC
37、T Sno， Cno FROM SC WHERE Grade IS NULL； An Introduction to Database System 例題 (續(xù) ) 例 22 查所有有成績的學生學號和課程號。 SELECT Sno， Cno FROM SC WHERE Grade IS NOT NULL； An Introduction to Database System (6) 多重條件查詢 用邏輯運算符 AND和 OR來聯(lián)結(jié)多個查詢條件 AND的優(yōu)先級高于 OR 可以用括號改變優(yōu)先級 可用來實現(xiàn)多種其他謂詞 NOT IN NOT
38、BETWEEN AND An Introduction to Database System 例題 例 23 查詢計算機系年齡在 20歲以下的學生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage=20 AND Sage<=23； An Introduction to Database System 三、對查詢結(jié)果排序 使用 ORDER BY子句 可以按一個或多個屬性列排序 升序： ASC；降序： DESC；缺省值為升序 當排序列含空值時 ASC：排序列為空值的元組最后顯示 DESC：排
39、序列為空值的元組最先顯示 An Introduction to Database System 對查詢結(jié)果排序（續(xù)） 例 24 查詢選修了 3號課程的學生的學號及 其成績 ， 查詢結(jié)果按分數(shù)降序排列 。 SELECT Sno， Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC； An Introduction to Database System 查詢結(jié)果 Sno Grade ------- ------- 95010 95024 950
40、07 92 95003 82 95010 82 95009 75 95014 61 95002 55 An Introduction to Database System 對查詢結(jié)果排序（續(xù)） 例 25 查詢?nèi)w學生情況 ， 查詢結(jié)果按所在 系的系號升序排列 ， 同一系中的學生按年 齡降序排列 。 SELECT * FROM Student ORDER BY Sdept， Sage DESC； An Introduction to Database Syste
41、m 四、使用集函數(shù) 5類主要集函數(shù) 計數(shù) COUNT（ DISTINCT|ALL *） COUNT（ DISTINCT|ALL ） 計算總和 SUM（ DISTINCT|ALL ） 計算平均值 AVG（ DISTINCT|ALL ） An Introduction to Database System 使用集函數(shù)（續(xù)） 求最大值 MAX（ DISTINCT|ALL ） 求最小值 MIN（ DISTINCT|ALL ） DISTINCT短語：在計算時要取消指定列中的重 復(fù)值 ALL短語：不取消重復(fù)值 ALL為缺省值 An Introduction
42、 to Database System 使用集函數(shù) （續(xù)） 例 26 查詢學生總?cè)藬?shù) 。 SELECT COUNT(*) FROM Student； 例 27 查詢選修了課程的學生人數(shù) 。 SELECT COUNT(DISTINCT Sno) FROM SC； 注：用 DISTINCT以避免重復(fù)計算學生人數(shù) An Introduction to Database System 使用集函數(shù) （續(xù)） 例 28 計算 1號課程的學生平均成績 。 SELECT AVG(Grade) FROM SC WHERE Cno= 1 ； 例 29 查詢
43、選修 1號課程的學生最高分數(shù) 。 SELECT MAX(Grade) FROM SC WHER Cno= 1 ； An Introduction to Database System 五、對查詢結(jié)果分組 使用 GROUP BY子句分組 細化集函數(shù)的作用對象 未對查詢結(jié)果分組 ， 集函數(shù)將作用于整個查詢 結(jié)果 對查詢結(jié)果分組后，集函數(shù)將分別作用于每個 組 An Introduction to Database System 使用 GROUP BY子句分組 例 30 求各個課程號及相應(yīng)的選課人數(shù) 。 SELECT Cno， COUNT(Sno)
44、FROM SC GROUP BY Cno； 結(jié)果 Cno COUNT(Sno) 1 22 2 34 3 44 4 33 5 48 An Introduction to Database System 對查詢結(jié)果分組 （續(xù)） GROUP BY子句的作用對象是查詢的中間結(jié)果表 分組方法：按指定的一列或多列值分組 ， 值相等 的為一組 使用 GROUP BY子句后 ， SELECT子句的列名列表 中只能出現(xiàn)分組屬性和集函數(shù) An Introduct
45、ion to Database System 使用 HAVING短語篩選最終輸出結(jié)果 例 31 查詢選修了 3門以上課程的學生學號 。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3； An Introduction to Database System 例題 例 32 查詢有 3門以上課程是 90分以上的 學生的學號及 （ 90分以上的 ） 課程數(shù) SELECT Sno, COUNT(*) FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=3; An Introduction to Database System 使用 HAVING短語篩選最終輸出結(jié)果 只有滿足 HAVING短語指定條件的組才輸出 HAVING短語與 WHERE子句的區(qū)別：作用對象 不同 WHERE子句作用于基表或視圖 ， 從中選擇 滿足條件的元組 。 HAVING短語作用于組 ， 從中選擇滿足條件 的組 。 An Introduction to Database System 下課了。。。 休息一會兒。。。
《數(shù)據(jù)庫系統(tǒng)概論》PPT課件.ppt
