數(shù)據(jù)庫原理及應(yīng)用第3章1database

1、An Introduction to Database System吉首大學(xué)信息科學(xué)與工程學(xué)院計算機系吉首大學(xué)信息科學(xué)與工程學(xué)院計算機系數(shù)據(jù)庫系統(tǒng)概論數(shù)據(jù)庫系統(tǒng)概論An Introduction to An Introduction to Database SystemDatabase System第三章第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQLSQLAn Introduction to Database System第三章第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1 SQL概述3.2 數(shù)據(jù)定義3.3 查詢3.4 數(shù)據(jù)更新3.5 視圖3.6 數(shù)據(jù)控制3.7 嵌入式SQL3.

2、8 小結(jié)An Introduction to Database System3.1 SQL概述概述nSQL的特點n1. 綜合統(tǒng)一n2. 高度非過程化n3. 面向集合的操作方式n4. 以同一種語法結(jié)構(gòu)提供兩種使用方法n5. 語言簡潔，易學(xué)易用An Introduction to Database System5. 語言簡捷，易學(xué)易用語言簡捷，易學(xué)易用表表 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ù) 控控 制制GRA

3、NT，REVOKEAn Introduction to Database System第三章第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1 SQL概述3.2 數(shù)據(jù)定義3.3 查詢3.4 數(shù)據(jù)更新3.5 視圖3.6 數(shù)據(jù)控制3.7 嵌入式SQL3.8 小結(jié)An Introduction to Database System3.2 數(shù)數(shù) 據(jù)據(jù) 定定 義義 表表3.2 SQL的的數(shù)數(shù)據(jù)據(jù)定定義義語語句句操操 作作 方方 式式操操 作作 對對象象創(chuàng)創(chuàng) 建建刪刪 除除修修 改改表表CREATET ABLEDROPT ABLEALTERT ABLE視視 圖圖CREATEVIEWDROP VIEW

4、索索 引引CREATEINDEXDROPINDEXAn Introduction to Database System3.2.1 定義語句格式定義語句格式CREATE TABLE （ ， ， ）；：所要定義的基本表的名字：組成該表的各個屬性（列）：涉及相應(yīng)屬性列的完整性約束條件：涉及一個或多個屬性列的完整性約束條件 An Introduction to Database System例題例題 例1 建立一個“學(xué)生”表Student，它由學(xué)號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成。其中學(xué)號不能為空，值是唯一的，并且姓名取值也唯一。 CREATE TABL

5、E 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ù)）定義基本表（續(xù)）n常用完整性約束n主碼約束： PRIMARY KEYn唯一性約束：UNIQUEn非空值約束：NOT NULLn參照完整性約束nPRIMARY KEY與 UNIQUE的區(qū)別？An Introduction to Database System例題例題 （續(xù)）（續(xù)）n例2 建立一個“學(xué)生選課”表

6、SC，它由學(xué)號Sno、課程號Cno，修課成績Grade組成，其中(Sno, Cno)為主碼。nCREATE TABLE SC(n Sno CHAR(5) ,n Cno CHAR(3) , n Grade int,n Primary key (Sno, Cno);An Introduction to Database System例題例題 （續(xù)）（續(xù)）SnoSnameSsexSageSdept 字字符符型型 字字符符型型 字字符符型型 整整數(shù)數(shù) 字字符符型型 長長度度為為5 5 長長度度為為2 20 0 長長度度為為1 1 長長度度為為1 15 5 不不能能為為空空值值A(chǔ)n Introducti

7、on to Database System三、刪除基本表三、刪除基本表 DROP TABLE ; 基本表刪除 數(shù)據(jù)、表上的索引都刪除 表上的視圖往往仍然保留，但無法引用刪除基本表時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該基本表及其索引的描述 （標(biāo)準(zhǔn)中沒有，認(rèn)為表建立后就永久存在）An Introduction to Database System例題例題n例5 刪除Student表n DROP TABLEStudent ;An Introduction to Database System二、修改基本表二、修改基本表ALTER TABLE ADD 完整性約束 DROP MODIFY ；：要修改的基本表A

8、DD子句：增加新列和新的完整性約束條件DROP子句：刪除指定的完整性約束條件MODIFY子句：用于修改列名和數(shù)據(jù)類型An Introduction to Database System例題例題 例2 向Student表增加“入學(xué)時間”列，其數(shù)據(jù)類型為日期型。 ALTER TABLE Student ADD e DATE；不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。 An Introduction to Database System語句格式（續(xù)）語句格式（續(xù)）n刪除屬性列n 直接/間接刪除n把表中要保留的列及其內(nèi)容復(fù)制到一個新表中n刪除原表n再將新表重命名為原表名n直接刪除屬性列:(新

9、)n例：ALTER TABLE Student Drop e；An Introduction to Database System 例3 將年齡的數(shù)據(jù)類型改為半字長整數(shù)。 ALTER TABLE Student MODIFY Sage SMALLINT；注：修改原有的列定義有可能會破壞已有數(shù)據(jù)An Introduction to Database System例題例題 例4 刪除學(xué)生姓名必須取唯一值的約束。A LT E R TA B L E S t u d e n t D R O P UNIQUE(Sname)； An Introduction to Database System3.2.2

10、建立與刪除索引建立與刪除索引 n建立索引是加快查詢速度的有效手段n建立索引nDBA或表的屬主（即建立表的人）根據(jù)需要建立n有些DBMS自動建立以下列上的索引n PRIMARY KEYn UNIQUEn維護(hù)索引n DBMS自動完成 n使用索引n DBMS自動選擇是否使用索引以及使用哪些索引An Introduction to Database System一、建立索引一、建立索引 n語句格式nCREATE UNIQUE CLUSTER INDEX ON (, )；n用指定要建索引的基本表名字n索引可以建立在該表的一列或多列上，各列名之間用逗號分隔n用指定索引值的排列次序，升序：ASC，降序：DE

11、SC。缺省值：ASCnUNIQUE表明此索引的每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄nCLUSTER表示要建立的索引是聚簇索引An Introduction to Database System例題例題 例6 為學(xué)生-課程數(shù)據(jù)庫中的Student，Course，SC三個表建立索引。其中Student表按學(xué)號升序建唯一索引，Course表按課程號升序建唯一索引，SC表按學(xué)號升序和課程號降序建唯一索引。CREATE UNIQUE INDEX Stusno ON Student(Sno)；CREATE UNIQUE INDEX Coucno ON Course(Cno)；CREATE UNIQUE IND

12、EX SCno ON SC(Sno ASC，Cno DESC)； An Introduction to Database System建立索引建立索引 （續(xù)）（續(xù)）n唯一值索引n對 于 已 含 重 復(fù) 值 的 屬 性 列 不 能 建UNIQUE索引n對某個列建立UNIQUE索引后，插入新記錄時DBMS會自動檢查新記錄在該列上是否取了重復(fù)值。這相當(dāng)于增加了一個UNIQUE約束An Introduction to Database System建立索引建立索引 （續(xù)）（續(xù)）n聚簇索引n建立聚簇索引后，基表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項順序與表中記錄的物理順序一

13、致n例：nCREATE CLUSTER INDEX Stusname ONn Student(Sname)；n在Student表的Sname（姓名）列上建立一個聚簇索引，而n且Student表中的記錄將按照Sname值的升序存放 An Introduction to Database System建立索引建立索引 （續(xù)）（續(xù)）n在一個基本表上最多只能建立一個聚簇索引n聚簇索引的用途：對于某些類型的查詢，可以提高查詢效率n聚簇索引的適用范圍n 很少對基表進(jìn)行增刪操作n 很少對其中的變長列進(jìn)行修改操作 An Introduction to Database System二、刪除索引二、刪除索引 D

14、ROP INDEX ；刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。例7 刪除Student表的Stusname索引。DROP INDEX Stusname；An Introduction to Database System3.3 查查 詢詢 3.3.1 概述3.3.2 單表查詢3.3.3 連接查詢3.3.4 嵌套查詢3.3.5 集合查詢3.3.6 小結(jié) An Introduction to Database System3.3.1 概述概述 n語句格式nSELECT ALL|DISTINCT n ， nFROM ， n WHERE n GROUP BY HAVING n ORDER

15、BY ASC|DESC ；n An Introduction to Database System語句格式語句格式nSELECT子句：指定要顯示的屬性列nFROM子句：指定查詢對象(基本表或視圖)nWHERE子句：指定查詢條件n GROUP BY子句：對查詢結(jié)果按指定列的值分組，該屬性列值相等的元組為一個組。通常會在每組中作用集函數(shù)。nHAVING短語：篩選出只有滿足指定條件的組nORDER BY子句：對查詢結(jié)果表按指定列值的升序或降序排序 An Introduction to Database System示例數(shù)據(jù)庫示例數(shù)據(jù)庫 學(xué)生-課程數(shù)據(jù)庫學(xué)生表：Student(Sno，Sname，Ss

16、ex，Sage，Sdept)課程表：Course(Cno，Cname，Cpno，Ccredit) 學(xué)生選課表：SC(Sno，Cno，Grade) An Introduction to Database System3.3 查查 詢詢 3.3.1 概述3.3.2 單表查詢3.3.3 連接查詢3.3.4 嵌套查詢3.3.5 集合查詢3.3.6 小結(jié) An Introduction to Database System3.3.2 單表查詢單表查詢 查詢僅涉及一個表，是一種最簡單的查詢操作一、選擇表中的若干列二、選擇表中的若干元組三、對查詢結(jié)果排序四、使用集函數(shù)五、對查詢結(jié)果分組 An Introdu

17、ction to Database System查詢指定列查詢指定列例1 查詢?nèi)w學(xué)生的學(xué)號與姓名。SELECT Sno，SnameFROM Student； 例2 查詢?nèi)w學(xué)生的姓名、學(xué)號、所在系。SELECT Sname，Sno，SdeptFROM Student；An Introduction to Database System查詢?nèi)苛胁樵內(nèi)苛衝例3 查詢?nèi)w學(xué)生的詳細(xì)記錄。nSELECT Sno，Sname，Ssex，Sage，Sdept nFROM Student； n 或nSELECT *nFROM Student； An Introduction to Database S

18、ystem3. 查詢經(jīng)過計算的值查詢經(jīng)過計算的值 SELECT子句的為表達(dá)式算術(shù)表達(dá)式字符串常量函數(shù)列別名等 An Introduction to Database System3. 查詢經(jīng)過計算的值查詢經(jīng)過計算的值例4 查全體學(xué)生的姓名及其出生年份。SELECT Sname，2000-SageFROM Student； 輸出結(jié)果： Sname 2000-Sage - - 李勇 1976 劉晨 1977 王名 1978 張立 1978 An Introduction to Database System3. 查詢經(jīng)過計算的值查詢經(jīng)過計算的值例5 查詢?nèi)w學(xué)生的姓名、出生年份和所有系，要求用小寫

19、字母表示所有系名。SELECT Sname，Year of Birth: ，2000-Sage， ISLOWER(Sdept)FROM Student； An Introduction to Database System例題（續(xù)）例題（續(xù)）輸出結(jié)果： Sname Year of Birth: 2000-Sage ISLOWER(Sdept) - - - - 李勇 Year of Birth: 1976 cs 劉晨 Year of Birth: 1977 is 王名 Year of Birth: 1978 ma 張立 Year of Birth: 1977 is An Introduction

20、 to Database System例例5.1 使用列別名改變查詢結(jié)果的列標(biāo)題使用列別名改變查詢結(jié)果的列標(biāo)題SELECT Sname NAME，Year of Birth: BIRTH， 2000-Sage BIRTHDAY，ISLOWER(Sdept) DEPARTMENTFROM Student；輸出結(jié)果： NAME BIRTH BIRTHDAY DEPARTMENT - - - - 李勇 Year of Birth: 1976 cs 劉晨 Year of Birth: 1977 is 王名 Year of Birth: 1978 ma 張立 Year of Birth: 1977 is

21、An Introduction to Database System二、選擇表中的若干元組二、選擇表中的若干元組 n消除取值重復(fù)的行n查詢滿足條件的元組 An Introduction to Database System1. 消除取值重復(fù)的行消除取值重復(fù)的行n在SELECT子句中使用DISTINCT短語n假設(shè)SC表中有下列數(shù)據(jù)n Sno Cno Graden - - -n 95001 1 92n 95001 2 85n 95001 3 88n 95002 2 90n 95002 3 80An Introduction to Database SystemALL 與與 DISTINCT 例6

22、查詢選修了課程的學(xué)生學(xué)號。(1) SELECT Sno FROM SC;或(默認(rèn) ALL) SELECT ALL Sno FROM SC; 結(jié)果： Sno - 95001 95001 95001 95002 95002 An Introduction to Database System例題（續(xù)）例題（續(xù)）(2) SELECT DISTINCT Sno FROM SC; 結(jié)果： Sno - 95001 95002 An Introduction to Database System例題（續(xù)）例題（續(xù)）n注意 DISTINCT短語的作用范圍是所有目標(biāo)列n例：查詢選修課程的各種成績n錯誤的寫法nS

23、ELECT DISTINCT Cno，DISTINCT GradenFROM SC;n正確的寫法n SELECT DISTINCT Cno，Graden FROM SC; An Introduction to Database System2.查詢滿足條件的元組查詢滿足條件的元組表表3.3 常常 用用 的的 查查 詢詢 條條 件件查查 詢詢 條條 件件謂謂 詞詞比比 較較=， ， = ， = ， != ， ， ! ， ! ；N OT + 上上 述述 比比 較較 運運 算算 符符確確 定定 范范 圍圍BETW E EN A N D ， N O T BETW EEN A N D確確 定定 集集 合

24、合IN ， N OT IN字字 符符 匹匹 配配LIKE ， N OT LIKE空空 值值IS N U LL ， IS N O T N U LL多多 重重 條條 件件A N D ， ORWHERE子句常用的查詢條件子句常用的查詢條件An Introduction to Database System(1) 比較大小比較大小在WHERE子句的中使用比較運算符=，=，=，!= 或 ，!，!， 邏輯運算符NOT + 比較運算符例8 查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。 SELECT Sname，Sage FROM Student WHERE Sage = 20； An Introductio

25、n to Database System(2) 確定范圍確定范圍n使用謂詞 BETWEEN AND n NOT BETWEEN AND n例10 查詢年齡在2023歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。n SELECT Sname，Sdept，SagenFROM StudentnWHERE Sage BETWEEN 20 AND 23； An Introduction to Database System例題（續(xù)）例題（續(xù)）例11 查詢年齡不在2023歲之間的學(xué)生姓名、系別和年齡。SELECT Sname，Sdept，SageFROM StudentWHERE Sage NOT

26、 BETWEEN 20 AND 23； An Introduction to Database System(3) 確定集合確定集合使用謂詞 IN , NOT IN ：用逗號分隔的一組取值例12查詢信息系（IS）、數(shù)學(xué)系（MA）和計 算機科學(xué)系（CS）學(xué)生的姓名和性別。SELECT Sname，SsexFROM StudentWHERE Sdept IN ( IS，MA，CS );An Introduction to Database System(3) 確定集合確定集合例13查詢既不是信息系、數(shù)學(xué)系，也不是計算 機科學(xué)系的學(xué)生的姓名和性別。SELECT Sname，SsexFROM Stud

27、ent WHERE Sdept NOT IN ( IS，MA，CS );An Introduction to Database System(4) 字符串匹配字符串匹配n NOT LIKE ESCAPE n：指定匹配模板n 匹配模板：固定字符串或含通配符的字符串n 當(dāng)匹配模板為固定字符串時，n 可以用 = 運算符取代 LIKE 謂詞n 用 != 或 運算符取代 NOT LIKE 謂詞An Introduction to Database System通配符通配符w% (百分號) 代表任意長度（長度可以為0）的字符串w例：a%b表示以a開頭，以b結(jié)尾的任意長度的字符串。如acb，addgb，ab

28、 等都滿足該匹配串w_ (下橫線) 代表任意單個字符w例：a_b表示以a開頭，以b結(jié)尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串An Introduction to Database SystemESCAPE 短語：短語：當(dāng)用戶要查詢的字符串本身就含有 % 或 _ 時，要使用ESCAPE 短語對通配符進(jìn)行轉(zhuǎn)義。An Introduction to Database System例題例題1) 匹配模板為固定字符串 例14 查詢學(xué)號為95001的學(xué)生的詳細(xì)情況。 SELECT * FROM Student WHERE Sno LIKE 95001；等價于： SELECT * FROM

29、 Student WHERE Sno = 95001；An Introduction to Database System例題（續(xù)）例題（續(xù)）2) 匹配模板為含通配符的字符串例15 查詢所有姓劉學(xué)生的姓名、學(xué)號和性別。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 劉%；An Introduction to Database System例題（續(xù)）例題（續(xù)）匹配模板為含通配符的字符串（續(xù)）例16 查詢姓歐陽且全名為三個漢字的學(xué)生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 歐陽_；An

30、Introduction to Database System例題（續(xù)）例題（續(xù)）匹配模板為含通配符的字符串（續(xù)）例17 查詢名字中第2個字為陽字的學(xué)生的姓名和學(xué)號。 SELECT Sname，Sno FROM Student WHERE Sname LIKE _陽%；An Introduction to Database System例題（續(xù)）例題（續(xù)）匹配模板為含通配符的字符串（續(xù)）例18 查詢所有不姓劉的學(xué)生姓名。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE 劉%；An Introduction to Database

31、System例題（續(xù)）例題（續(xù)）3) 使用換碼字符將通配符轉(zhuǎn)義為普通字符 例19 查詢DB_Design課程的課程號和學(xué)分。 SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE An Introduction to Database System例題（續(xù)）例題（續(xù)）使用換碼字符將通配符轉(zhuǎn)義為普通字符(續(xù))例20 查詢以DB_開頭，且倒數(shù)第3個字符為 i的課程的詳細(xì)情況。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ；An Introduction to

32、Database System(5) 涉及空值的查詢涉及空值的查詢n 使用謂詞 IS NULL 或 IS NOT NULLn “IS NULL” 不能用 “= NULL” 代替n例21 某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。n SELECT Sno，Cnon FROM SCn WHERE Grade IS NULL；An Introduction to Database System例題例題(續(xù)續(xù)) 例22 查所有有成績的學(xué)生學(xué)號和課程號。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NU

33、LL；An Introduction to Database System(6) 多重條件查詢多重條件查詢用邏輯運算符AND和 OR來聯(lián)結(jié)多個查詢條件 AND的優(yōu)先級高于OR 可以用括號改變優(yōu)先級可用來實現(xiàn)多種其他謂詞 NOT IN NOT BETWEEN AND An Introduction to Database System例題例題例23 查詢計算機系年齡在20歲以下的學(xué)生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage=20 AND Sage=23；An Introduction to Database System三、對查

34、詢結(jié)果排序三、對查詢結(jié)果排序 使用ORDER BY子句 可以按一個或多個屬性列排序 升序：ASC；降序：DESC；缺省值為升序當(dāng)排序列含空值時ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示 An Introduction to Database System對查詢結(jié)果排序（續(xù)）對查詢結(jié)果排序（續(xù)） 例24 查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC； An Introduction to Database System查詢結(jié)果查詢結(jié)果

35、 Sno Grade - - 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55An Introduction to Database System對查詢結(jié)果排序（續(xù)）對查詢結(jié)果排序（續(xù)） 例25 查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學(xué)生按年齡降序排列。 SELECT * FROM Student ORDER BY Sdept，Sage DESC； An Introduction to Database System四、使用集函數(shù)四、使用集函數(shù) 5類主要集函數(shù)計數(shù)COUNT（DISTINCT|ALL *）COUNT（DISTINCT|ALL ）計算總和SUM（DISTINCT|ALL ） 計算平均值A(chǔ)VG（DISTINCT|ALL ）An Introduction to Database System使用集函數(shù)（續(xù)）使用集函數(shù)（續(xù)） 求最大值MAX（DISTINCT|ALL ） 求最小值MIN（DISTINCT|ALL ）DISTINCT短語：在計算時要取消指定列中的重復(fù)值A(chǔ)LL短語：不取消重復(fù)值A(chǔ)LL為缺省值A(chǔ)n Introduction to Database System使用集函數(shù)使用集函數(shù) （續(xù)）（續(xù)）例26 查詢學(xué)生總?cè)藬?shù)。 SELECT COUNT(*) FROM