數(shù)據(jù)庫原理與應用第三章ppt課件

1、第三章第三章 關系數(shù)據(jù)庫規(guī)范言語關系數(shù)據(jù)庫規(guī)范言語SQLSQL3.1 SQL概述 SQL的特點 1. 綜合一致 2. 高度非過程化 3. 面向集合的操作方式 4. 以同一種語法構造提供兩種運用方法 5. 言語簡約，易學易用5. 言語簡捷，易學易用表表 3.1 SQL語語言言的的動動詞詞SQL 功功 能能動動 詞詞數(shù)數(shù) 據(jù)據(jù) 定定 義義CREATE， DROP， ALTER數(shù)數(shù) 據(jù)據(jù) 查查 詢詢SELECT數(shù)數(shù) 據(jù)據(jù) 操操 縱縱INSERT，UPDATEDELETE數(shù)數(shù) 據(jù)據(jù) 控控 制制GRANT，REVOKE3.2 數(shù) 據(jù) 定 義 表表3.2 SQL的的數(shù)數(shù)據(jù)據(jù)定定義義語語句句操操 作作 方方

2、 式式操操 作作 對對象象創(chuàng)創(chuàng) 建建刪刪 除除修修 改改表表CREATET ABLEDROPT ABLEALTERT ABLE視視 圖圖CREATEVIEWDROP VIEW索索 引引CREATEINDEXDROPINDEX3.2.1 定義語句格式CREATE TABLE ( , , , ) 給出要創(chuàng)建的根本表的稱號；給出要創(chuàng)建的根本表的稱號； 給出列名或字段名；給出列名或字段名； 為列指定數(shù)據(jù)為列指定數(shù)據(jù)類型及其數(shù)據(jù)類型及其數(shù)據(jù)寬度；寬度； 關系數(shù)據(jù)庫支關系數(shù)據(jù)庫支持非常豐富的持非常豐富的數(shù)據(jù)類型，不數(shù)據(jù)類型，不同的數(shù)據(jù)庫管同的數(shù)據(jù)庫管理系統(tǒng)支持的理系統(tǒng)支持的數(shù)據(jù)類型根本數(shù)據(jù)類型根本是一樣的

3、，右是一樣的，右表列出了常用表列出了常用的數(shù)據(jù)類型。的數(shù)據(jù)類型。 數(shù)據(jù)類型 用于定義列或字段一級的完好性約束，普通包括：用于定義列或字段一級的完好性約束，普通包括： NOT NULL和和NULL約束約束 PRIMARY KEY約束約束 UNIQUE約束約束 FOREIGN KEY約束約束 DEFAULT定義定義 CHECK約束約束 列級完好性約束 用于定義表一級的完好性約束，普通包括：用于定義表一級的完好性約束，普通包括： PRIMARY KEY約束復合屬性構成的主關鍵約束復合屬性構成的主關鍵字闡明字闡明 FOREIGN KEY約束外部關鍵字及參照關系約束外部關鍵字及參照關系闡明闡明 CHEC

4、K約束同時涉及到多個屬性的域完好約束同時涉及到多個屬性的域完好性約束性約束 表級完好性約束 不是SQL的規(guī)范選項，普通用于與物理存儲有關的闡明，不同的數(shù)據(jù)庫管理系統(tǒng)定義的方式一定不同，另外該項參數(shù)普通也不是必需的。 其他參數(shù)例題 例1 建立一個“學生表Student，它由學號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成。其中學號不能為空，值是獨一的，并且姓名取值也獨一。 CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE， Sname CHAR(20) UNIQUE， Ssex CHAR(1) ， Sage I

5、NT， Sdept CHAR(15)； 二、修正根本表ALTER TABLE ADD | DROP MODIFY DROP COLUMN ALTER COLUMN 例題 例2 向Student表添加“入學時間列，其數(shù)據(jù)類型為日期型。 ALTER TABLE Student ADD Scome DATE；不論根本表中原來能否已有數(shù)據(jù)，新添加的列一概為空值。 例題 例3 將年齡的數(shù)據(jù)類型改為半字長整數(shù)。 ALTER TABLE Student MODIFY Sage SMALLINT；修正原有的列定義能夠會破壞已有數(shù)據(jù)。 例題 例4 刪除關于學號必需取獨一值的約束。 ALTER TABLE Stu

6、dent DROP UNIQUESno ；SQL沒有提供刪除屬性列的語句，只能間接實現(xiàn)，先將原表中要保管的列及其內容復制到一個新表中，然后刪除原表，并將新表命名為原表名。 三、刪除根本表 DROP TABLE ; 根本表刪除 數(shù)據(jù)、表上的索引都刪除 表上的視圖往往依然保管，但無法援用刪除根本表時，系統(tǒng)會自動從數(shù)據(jù)字典中刪去有關該根本表及其索引的描畫 ，因此建立在此表上的視圖雖然已保管，但已無法援用例題 例5 刪除Student表 DROP TABLEStudent ;3.2.2 建立與刪除索引 建立索引是加快查詢速度的有效手段 建立索引 DBA或表的屬主即建立表的人根據(jù)需求建立和刪除 有些DB

7、MS自動建立以以下上的索引 PRIMARY KEY UNIQUE 維護索引 DBMS自動完成 運用索引 DBMS自動選擇能否運用索引以及運用哪些索引一、建立索引 語句格式 CREATE UNIQUE CLUSTER INDEX ON (, )； 用指定要建索引的根本表名字 索引可以建立在該表的一列或多列上，各列名之間用逗號分隔 用指定索引值的陳列次序，升序：ASC，降序：DESC。缺省值：ASC UNIQUE闡明此索引的每一個索引值只對應獨一的數(shù)據(jù)記錄 CLUSTER表示要建立的索引是聚簇索引建立索引 續(xù) 獨一值索引 對 于 已 含 反 復 值 的 屬 性 列 不 能 建UNIQUE索引 對某

8、個列建立UNIQUE索引后，插入新記錄時DBMS會自動檢查新記錄在該列上能否取了反復值。這相當于添加了一個UNIQUE約束建立索引 續(xù) 聚簇索引 建立聚簇索引后，基表中數(shù)據(jù)也需求按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項順序與表中記錄的物理順序一致 例： CREATE CLUSTER INDEX Stusname ON Student(Sname)； 在Student表的Sname姓名列上建立一個聚簇索引，而 且Student表中的記錄將按照Sname值的升序存放 建立索引 續(xù) 在一個根本表上最多只能建立一個聚簇索引 聚簇索引的用途：對于某些類型的查詢，可以提高查詢效率 聚簇索引

9、的適用范圍 很少對基表進展增刪操作 很少對其中的變長列進展修正操作 聚簇索引是指索引項的順序與表中記錄的物理順序一致的索引組織。 例CREATE CLUSTER INDEX Stusname ON Student (Sname); 將會在Student 表的Sname列上建一個聚簇索引，而且記錄會按照Sname值的升序存放。 用戶可以在最常查詢的列上建立聚簇索引以提高查詢效率。但在一個根本表上只能建一個，而且更新索引列數(shù)據(jù)會導致表中記錄的物理順序的變卦，因此經(jīng)常更新的數(shù)據(jù)列不宜建立。例題 例6 為學生-課程數(shù)據(jù)庫中的Student，Course，SC三個表建立索引。其中Student表按學號升

10、序建獨一索引，Course表按課程號升序建獨一索引，SC表按學號升序和課程號降序建獨一索引。CREATE UNIQUE INDEX Stusno ON Student(Sno)CREATE UNIQUE INDEX Coucno ON Course(Cno)；CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)； 二、刪除索引 DROP INDEX ；刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關該索引的描畫。例7 刪除Student表的Stusname索引。DROP INDEX Stusname；3.3.1 概述 語句格式SELECT ALL|DISTIN

11、CT , FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC；例如數(shù)據(jù)庫 學生-課程數(shù)據(jù)庫學生表：Student(Sno，Sname，Ssex，Sage，Sdept)課程表：Course(Cno，Cname，Cpno，Ccredit) 學生選課表：SC(Sno，Cno，Grade) 3.3 查 詢 3.3.1 概述3.3.2 單表查詢3.3.3 銜接查詢3.3.4 嵌套查詢3.3.5 集合查詢3.3.6 小結 3.3.2 單表查詢 查詢僅涉及一個表，是一種最簡單的查詢操作一、選擇表中的假設干列二、選擇表中的假設干元組三、對查詢結果排序四、運用集函數(shù)五、

12、對查詢結果分組 從職工關系中檢索一切工資值SELECT 工資 FROM 職工結果是：結果是：12201210125012301250SELECT DISTINCT工資工資 FROM 職工職工結果是：結果是：1220121012501230單表查詢 用戶在查詢時可根據(jù)運用的需求改動列的顯示順序 例2查詢全體學生的姓名、學號、所在系。 查詢全部列-將一切的列名在SELECT后面列出或者用*表示列名 例3 SELECT * FROM Student; 查詢經(jīng)過計算的值 例4查詢全體學生姓名及其出生年份 SELECT Sname,1996-Sage FROM Student;單表查詢不僅可以是算術表達

13、式，還可以是字符串常量、不僅可以是算術表達式，還可以是字符串常量、函數(shù)等。函數(shù)等。例例5查詢全體學生的姓名、出生年份和一切系，要求用小寫字母查詢全體學生的姓名、出生年份和一切系，要求用小寫字母表示一切系名表示一切系名SELECT Sname,Year of Birth:,1996-Sage,ISLOWER(Sdept) FROM Student;輸出結果：輸出結果：Sname Year of Birth: 1996-Sage ISLOWER(Sdept) - - - - 李勇李勇 Year of Birth: 1976 cs 劉晨劉晨 Year of Birth: 1977 is 王名王名 Y

14、ear of Birth: 1978 ma 張立張立 Year of Birth: 1977 is例5.* 運用列別名改動查詢結果的列標題SELECT Sname NAME，Year of Birth: BIRTH， 2000-Sage BIRTHDAY，ISLOWER(Sdept) DEPARTMENTFROM Student；輸出結果：輸出結果： NAME BIRTH BIRTHDAY DEPARTMENT - - - - 李勇李勇 Year of Birth: 1976 cs 劉晨劉晨 Year of Birth: 1977 is 王名王名 Year of Birth: 1978 ma

15、張立張立 Year of Birth: 1977 is二、選擇表中的假設干元組 消除取值反復的行 查詢滿足條件的元組 1. 消除取值反復的行 在SELECT子句中運用DISTINCT短語 假設SC表中有以下數(shù)據(jù) Sno Cno Grade - - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80ALL 與 DISTINCT 例6 查詢選修了課程的學生學號。(1) SELECT Sno FROM SC;(由于默許的是ALL) SELECT ALL Sno FROM SC; 所以結果為： Sno - 95001 95001 95001

16、 95002 95002 例題續(xù)(2) SELECT DISTINCT Sno FROM SC; 結果： Sno - 95001 95002 總結：兩個本來并不完全一樣的元組，投影到指定的某些列上后，能夠變成完全一樣的行，于是必需用DISTINCT短語或象下面那樣用WHERE語句。2.查詢滿足條件的元組表表3.3 常常 用用 的的 查查 詢詢 條條 件件查查 詢詢 條條 件件謂謂 詞詞比比 較較=， ， = ， = ， != ， ， ! ， ! ；N O T + 上上 述述 比比 較較 運運 算算 符符確確 定定 范范 圍圍BETW E EN A N D ， N O T BETW EEN A

17、N D確確 定定 集集 合合IN ， N O T IN字字 符符 匹匹 配配LIK E ， N O T LIK E空空 值值IS N U LL ， IS N O T N U LL多多 重重 條條 件件A N D ， O RWHERE子句常用的查詢條件(1) 比較大小在WHERE子句的中運用比較運算符=，=，=，!= 或 ，!，!， 邏輯運算符NOT + 比較運算符例8 查詢一切年齡在20歲以下的學生姓名及其年齡。 SELECT Sname，Sage FROM Student WHERE Sage = 20； 例9 查詢考試成果有不及格的學生的學號 SELECT DISTINCT Sno FRO

18、M SC WHERE Grade 60;(2) 確定范圍 運用謂詞 BETWEEN (下限) AND 上限 NOT BETWEEN AND 例10 查詢年齡在2023歲包括20歲和23歲之間的學生的姓名、系別和年齡。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23； 例題續(xù)例11 查詢年齡不在2023歲之間的學生姓名、系別和年齡。SELECT Sname，Sdept，SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23； (3) 確定集合運用謂詞 IN , NOT I

19、N ：用逗號分隔的一組取值例12查詢信息系IS、數(shù)學系MA和計 算機科學系CS學生的姓名和性別。SELECT Sname，SsexFROM StudentWHERE Sdept IN ( IS，MA，CS );(3) 確定集合例13查詢既不是信息系、數(shù)學系，也不是計算 機科學系的學生的姓名和性別。SELECT Sname，SsexFROM Student WHERE Sdept NOT IN ( IS，MA，CS );(4) 字符串匹配 NOT LIKE ESCAPE ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 當匹配模板為固定字符串時，即不含通配符時 可以用 = 運算符取代 LI

20、KE 謂詞 用 != 或 運算符取代 NOT LIKE 謂詞通配符w % (百分號) 代表恣意長度長度可以為0的字符串w 例：a%b表示以a開頭，以b結尾的恣意長度的字符串。如acb，addgb，ab 等都滿足該匹配串w _ (下橫線) 代表恣意單個字符w 例：a_b表示以a開頭，以b結尾的長度為3的恣意字符串。如acb，afb等都滿足該匹配串ESCAPE 短語：當用戶要查詢的字符串本身就含有 % 或 _ 時，要運用ESCAPE 短語對通配符進展本義。例題1) 匹配模板為固定字符串 例14 查詢學號為95001的學生的詳細情況。 SELECT * FROM Student WHERE Sno

21、LIKE 95001；等價于： SELECT * FROM Student WHERE Sno = 95001；例題續(xù)2) 匹配模板為含通配符的字符串例15 查詢一切姓劉學生的姓名、學號和性別。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 劉%；例題續(xù)匹配模板為含通配符的字符串續(xù)例16 查詢姓歐陽且全名為三個漢字的學生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 歐陽_ _；例題續(xù)匹配模板為含通配符的字符串續(xù)例17 查詢名字中第2個字為陽字的學生的姓名和學號。 SELECT Sname

22、，Sno FROM Student WHERE Sname LIKE _ _陽%；例題續(xù)匹配模板為含通配符的字符串續(xù)例18 查詢一切不姓劉的學生姓名。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE 劉%；例題續(xù)3) 運用換碼字符將通配符本義為普通字符 例19 查詢DB_Design課程的課程號和學分。 SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE 例題續(xù)運用換碼字符將通配符本義為普通字符(續(xù))例20 查詢以DB_開頭，且倒數(shù)第3個字符為 i的課程

23、的詳細情況。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ；(5) 涉及空值的查詢 運用謂詞 IS NULL 或 IS NOT NULL “IS NULL 不能用 “= NULL 替代 例21 某些學生選修課程后沒有參與考試，所以有選課記錄，但沒有考試成果。查詢短少成果的學生的學號和相應的課程號。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL；例題(續(xù)) 例22 查一切有成果的學生學號和課程號。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；(

24、6) 多重條件查詢用邏輯運算符AND和 OR來結合多個查詢條件 AND的優(yōu)先級高于OR 可以用括號改動優(yōu)先級可用來實現(xiàn)多種其他謂詞 NOT IN NOT BETWEEN AND 例題例23 查詢計算機系年齡在20歲以下的學生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage=20 AND Sage=23；三、對查詢結果排序 假設沒有指定查詢結果的顯示順序，DBMS按最方便的元組在表中的先后方式輸出查詢結果運用ORDER BY子句 可以按一個或多個屬性列排序 升序：ASC；降序：DESC；缺省值為升序當排序列含空值時ASC：排序列為空值的

25、元組最后顯示DESC：排序列為空值的元組最先顯示 對查詢結果排序續(xù) 例24 查詢選修了3號課程的學生的學號及其成果，查詢結果按分數(shù)降序陳列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC； 查詢結果 Sno Grade - - 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55對查詢結果排序續(xù) 例25 查詢全體學生情況，查詢結果按所在系的系號升序陳列，同一系中的學生按年齡降序陳列。 SELECT * FROM Student ORDER BY Sdept，Sage DESC； 四、運用集函數(shù) 5類主要集函數(shù)計數(shù)COUNTDISTINCT|ALL *COUNTDISTINCT|ALL 計算總和，此列必需是數(shù)值型的SUMDISTINCT|ALL 計算平均值，此列必需是數(shù)值型的AVGDISTINCT|ALL 運用集函數(shù)續(xù) 求最大值MAXDISTINCT|ALL 求最小值MINDISTINCT|ALL DISTINCT短語：在計算時要取消指定列中的反復值ALL短語：不取消反復值ALL為缺省值運用集函數(shù) 續(xù)例26 查詢學生總人數(shù)。 SEL