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

1、第三章第三章 關(guān)系數(shù)據(jù)庫規(guī)范言語關(guān)系數(shù)據(jù)庫規(guī)范言語SQLSQL3.1 SQL概述 SQL的特點(diǎn) 1. 綜合一致 2. 高度非過程化 3. 面向集合的操作方式 4. 以同一種語法構(gòu)造提供兩種運(yùn)用方法 5. 言語簡約，易學(xué)易用5. 言語簡捷，易學(xué)易用表表 3.1 SQL語語言言的的動(dòng)動(dòng)詞詞SQL 功功 能能動(dòng)動(dòng) 詞詞數(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 ( , , , ) 給出要?jiǎng)?chuàng)建的根本表的稱號；給出要?jiǎng)?chuàng)建的根本表的稱號； 給出列名或字段名；給出列名或字段名； 為列指定數(shù)據(jù)為列指定數(shù)據(jù)類型及其數(shù)據(jù)類型及其數(shù)據(jù)寬度；寬度； 關(guān)系數(shù)據(jù)庫支關(guān)系數(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約束復(fù)合屬性構(gòu)成的主關(guān)鍵約束復(fù)合屬性構(gòu)成的主關(guān)鍵字闡明字闡明 FOREIGN KEY約束外部關(guān)鍵字及參照關(guān)系約束外部關(guān)鍵字及參照關(guān)系闡明闡明 CHEC

4、K約束同時(shí)涉及到多個(gè)屬性的域完好約束同時(shí)涉及到多個(gè)屬性的域完好性約束性約束 表級完好性約束 不是SQL的規(guī)范選項(xiàng)，普通用于與物理存儲有關(guān)的闡明，不同的數(shù)據(jù)庫管理系統(tǒng)定義的方式一定不同，另外該項(xiàng)參數(shù)普通也不是必需的。 其他參數(shù)例題 例1 建立一個(gè)“學(xué)生表Student，它由學(xué)號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個(gè)屬性組成。其中學(xué)號不能為空，值是獨(dú)一的，并且姓名取值也獨(dú)一。 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表添加“入學(xué)時(shí)間列，其數(shù)據(jù)類型為日期型。 ALTER TABLE Student ADD Scome DATE；不論根本表中原來能否已有數(shù)據(jù)，新添加的列一概為空值。 例題 例3 將年齡的數(shù)據(jù)類型改為半字長整數(shù)。 ALTER TABLE Student MODIFY Sage SMALLINT；修正原有的列定義能夠會破壞已有數(shù)據(jù)。 例題 例4 刪除關(guān)于學(xué)號必需取獨(dú)一值的約束。 ALTER TABLE Stu

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

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

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

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

10、序建獨(dú)一索引，Course表按課程號升序建獨(dú)一索引，SC表按學(xué)號升序和課程號降序建獨(dú)一索引。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 ；刪除索引時(shí)，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的描畫。例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ù)庫 學(xué)生-課程數(shù)據(jù)庫學(xué)生表：Student(Sno，Sname，Ssex，Sage，Sdept)課程表：Course(Cno，Cname，Cpno，Ccredit) 學(xué)生選課表：SC(Sno，Cno，Grade) 3.3 查 詢 3.3.1 概述3.3.2 單表查詢3.3.3 銜接查詢3.3.4 嵌套查詢3.3.5 集合查詢3.3.6 小結(jié) 3.3.2 單表查詢 查詢僅涉及一個(gè)表，是一種最簡單的查詢操作一、選擇表中的假設(shè)干列二、選擇表中的假設(shè)干元組三、對查詢結(jié)果排序四、運(yùn)用集函數(shù)五、

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

13、式，還可以是字符串常量、不僅可以是算術(shù)表達(dá)式，還可以是字符串常量、函數(shù)等。函數(shù)等。例例5查詢?nèi)w學(xué)生的姓名、出生年份和一切系，要求用小寫字母查詢?nèi)w學(xué)生的姓名、出生年份和一切系，要求用小寫字母表示一切系名表示一切系名SELECT Sname,Year of Birth:,1996-Sage,ISLOWER(Sdept) FROM Student;輸出結(jié)果：輸出結(jié)果：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.* 運(yùn)用列別名改動(dòng)查詢結(jié)果的列標(biāo)題SELECT Sname NAME，Year of Birth: BIRTH， 2000-Sage BIRTHDAY，ISLOWER(Sdept) DEPARTMENTFROM Student；輸出結(jié)果：輸出結(jié)果： 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二、選擇表中的假設(shè)干元組 消除取值反復(fù)的行 查詢滿足條件的元組 1. 消除取值反復(fù)的行 在SELECT子句中運(yùn)用DISTINCT短語 假設(shè)SC表中有以下數(shù)據(jù) Sno Cno Grade - - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80ALL 與 DISTINCT 例6 查詢選修了課程的學(xué)生學(xué)號。(1) SELECT Sno FROM SC;(由于默許的是ALL) SELECT ALL Sno FROM SC; 所以結(jié)果為： Sno - 95001 95001 95001

16、 95002 95002 例題續(xù)(2) SELECT DISTINCT Sno FROM SC; 結(jié)果： Sno - 95001 95002 總結(jié)：兩個(gè)本來并不完全一樣的元組，投影到指定的某些列上后，能夠變成完全一樣的行，于是必需用DISTINCT短語或象下面那樣用WHERE語句。2.查詢滿足條件的元組表表3.3 常常 用用 的的 查查 詢詢 條條 件件查查 詢詢 條條 件件謂謂 詞詞比比 較較=， ， = ， = ， != ， ， ! ， ! ；N O T + 上上 述述 比比 較較 運(yùn)運(yùn) 算算 符符確確 定定 范范 圍圍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子句的中運(yùn)用比較運(yùn)算符=，=，=，!= 或 ，!，!， 邏輯運(yùn)算符NOT + 比較運(yùn)算符例8 查詢一切年齡在20歲以下的學(xué)生姓名及其年齡。 SELECT Sname，Sage FROM Student WHERE Sage = 20； 例9 查詢考試成果有不及格的學(xué)生的學(xué)號 SELECT DISTINCT Sno FRO

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

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

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

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

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

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

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

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