數(shù)據(jù)庫原理與設(shè)計方法東南大學(xué)自動控制系邵家玉課件下載：25

1、數(shù)據(jù)庫原理與設(shè)計方法東南大學(xué)自動控制系邵家玉課件下載：聯(lián)系方式EMAIL： 邵家玉中國QQ: 171995639MSN： bistone_shaohotmail POPO: bistone參考書： 1王能斌。數(shù)據(jù)庫系統(tǒng)。電子工業(yè)出版社。1995年。 2王能斌編。數(shù)據(jù)庫系統(tǒng)原理。電子工業(yè)出版社。2000年。 3王珊 陳紅。數(shù)據(jù)庫系統(tǒng)原理教程。 4美JD厄爾曼。數(shù)據(jù)庫系統(tǒng)原理。課程考核第一章 Introduction1.1 Concepts Data、DataBase、DataBase System、DataBase Management System 1. Data(李明，男，1972，江蘇，計

2、算機系，1990)數(shù)據(jù)、信息、知識三者之間的關(guān)系：數(shù)據(jù)的語義即為信息，信息在計算機中的存儲表示形式即為數(shù)據(jù)。從信息中提升、推理、推導(dǎo)出的新的信息即為知識。例如：40數(shù)據(jù)40信息發(fā)燒知識 2. DatabaseDB 3. Database Management SystemDBMS 4. database systemDBS數(shù)據(jù)庫管理員(database administrator，簡稱DBA)。 5. Data Model數(shù)據(jù)模型是用來描述數(shù)據(jù)的一組概念和定義。一般來說，數(shù)據(jù)的描述包括兩個方面：(1)數(shù)據(jù)的靜態(tài)特性它包括數(shù)據(jù)的根本結(jié)構(gòu)、數(shù)據(jù)間的聯(lián)系和數(shù)據(jù)中的約束。(2)數(shù)據(jù)的動態(tài)特性它指定義在

3、數(shù)據(jù)上的操作。如文件系統(tǒng)。數(shù)據(jù)模型要面向現(xiàn)實世界，面向用戶。數(shù)據(jù)模型要面向?qū)崿F(xiàn)，面向計算機。 1) conceptual data model如ER模型、面向?qū)ο髷?shù)據(jù)模型等。 2) logical data model如關(guān)系數(shù)據(jù)模型、層次模型、網(wǎng)狀模型等。 3) physical data model概念數(shù)據(jù)模型只用于數(shù)據(jù)庫的設(shè)計，邏輯數(shù)據(jù)模型和物理數(shù)據(jù)模型用于DBMS的實現(xiàn)。 6. Data Schematype: 型是該數(shù)據(jù)所屬數(shù)據(jù)類型的說明。value: 值是型的一個實例(instance或occurrence)。對某一類數(shù)據(jù)的結(jié)構(gòu)、聯(lián)系和約束的描述是型的描述，型的描述稱為數(shù)據(jù)模式Dat

4、a Schema。在同一數(shù)據(jù)模式下，可以有很多的值，即實例。例如，學(xué)生記錄可以定義為圖1-3(a)的形式，這是數(shù)據(jù)模式。而圖1-3(b)是其一個實例。數(shù)據(jù)模型是描述數(shù)據(jù)的手段而數(shù)據(jù)模式是用給定數(shù)據(jù)模型對具體數(shù)據(jù)的描述。美國國家標(biāo)準(zhǔn)協(xié)會(ANSI)的ANSIX3SPARC報告把數(shù)據(jù)模式分為三級(見圖1-4)。 1) conceptual schema/logical schema 2) external schema 3) internal schema 7. Database Instance數(shù)據(jù)模式是相對穩(wěn)定的，而實例是相對變動的。數(shù)據(jù)模式反映一個單位的各種事物的結(jié)構(gòu)、屬性、聯(lián)系和約束，實質(zhì)

5、上是用數(shù)據(jù)模型對一個單位的模擬。而實例反映數(shù)據(jù)庫的某一時刻的狀態(tài)，也就是這一單位在此時的狀態(tài)。 數(shù)據(jù)庫技術(shù)的產(chǎn)生與開展 1. 人工管理階段 人工管理數(shù)據(jù)具有如下特點： 1) 數(shù)據(jù)不保存。 2) 數(shù)據(jù)需要由應(yīng)用程序自己管理，沒有相應(yīng)的軟件系統(tǒng)負(fù)責(zé)數(shù)據(jù)的管理工作。 3) 數(shù)據(jù)不共享。 4) 數(shù)據(jù)不具有獨立性。人工管理階段應(yīng)用程序與數(shù)據(jù)之間的對應(yīng)關(guān)系可用圖l-3表示。 2. 文件系統(tǒng)階段 用文件系統(tǒng)管理數(shù)據(jù)具有如下特點： 1) 數(shù)據(jù)可以長期保存。 2) 由專門的軟件即文件系統(tǒng)進行數(shù)據(jù)管理。 3) 數(shù)據(jù)共享性差。 4) 數(shù)據(jù)獨立性低。文件系統(tǒng)階段應(yīng)用程序與數(shù)據(jù)之間的關(guān)系如圖1-4所示。 3. 數(shù)據(jù)庫系

6、統(tǒng)階段用數(shù)據(jù)庫系統(tǒng)來管理數(shù)據(jù)具有如下特點： 1) 數(shù)據(jù)結(jié)構(gòu)化學(xué)生人事記錄學(xué)號姓名性別系別年齡政治面貌家庭出身籍貫家庭成員獎懲情況圖1-5 2) 數(shù)據(jù)的共享性好，冗余度低 3) 數(shù)據(jù)獨立性高 4) 數(shù)據(jù)由DBMS統(tǒng)一管理和控制 l 數(shù)據(jù)的平安性(security) l 數(shù)據(jù)的完整性(integrity) l 并發(fā)(concurrency)控制 l 數(shù)據(jù)庫恢復(fù)(recovery) 量大 持久 共享 數(shù)據(jù)庫技術(shù)的研究領(lǐng)域 1. 數(shù)據(jù)庫管理系統(tǒng)軟件的研制 2. 數(shù)據(jù)庫設(shè)計 3. 數(shù)據(jù)庫理論1.2 數(shù)據(jù)庫工程與應(yīng)用 數(shù)據(jù)庫設(shè)計的目標(biāo)與特點圖 1-10 數(shù)據(jù)庫設(shè)計方法新奧爾良方法：需求分析(分析用戶要求)

7、、概念設(shè)計(信息分析和定義)、邏輯設(shè)計(設(shè)計實現(xiàn))和物理設(shè)計(物理數(shù)據(jù)庫設(shè)計)。SBYao：需求分析、模式構(gòu)成、模式匯總、模式重構(gòu)、模式分析和物理數(shù)據(jù)庫設(shè)計。IRPalmer那么主張把數(shù)據(jù)庫設(shè)計當(dāng)成一步接一步的過程，并采用一些輔助手段實現(xiàn)每一過程。此外，基于ER模型的數(shù)據(jù)庫設(shè)計方法，基于3NF(第三范式)的設(shè)計方法，基于抽象語法標(biāo)準(zhǔn)的設(shè)計方法等。標(biāo)準(zhǔn)設(shè)計法在具體使用中又可以分為兩類：手工設(shè)計和計算機輔助數(shù)據(jù)庫設(shè)計。ORACLE Designer 2000 數(shù)據(jù)庫設(shè)計步驟1. 需求分析2. 概念結(jié)構(gòu)設(shè)計3. 邏輯結(jié)構(gòu)設(shè)計圖1-114. 數(shù)據(jù)庫物理設(shè)計5. 數(shù)據(jù)庫實施6. 數(shù)據(jù)庫運行和維護在數(shù)據(jù)庫

8、設(shè)計過程中必須注意以下問題。1. 數(shù)據(jù)庫設(shè)計過程中要注意充分調(diào)動用戶的積極性。2. 應(yīng)用環(huán)境的改變、新技術(shù)的出現(xiàn)等都會導(dǎo)致應(yīng)用需求的變化，因此設(shè)計人員在設(shè)計數(shù)據(jù)庫時必須充分考慮到系統(tǒng)的可擴充性，使設(shè)計易于變動。3. 系統(tǒng)的可擴充性最終都是有一定限度的。Database Application各種用戶的數(shù)據(jù)視圖DBA主要職責(zé)包括：1. 設(shè)計與定義數(shù)據(jù)庫系統(tǒng)2. 幫助最終用戶使用數(shù)據(jù)庫系統(tǒng)3. 監(jiān)督與控制數(shù)據(jù)庫系統(tǒng)的使用和運行4. 改進和重組數(shù)據(jù)庫系統(tǒng)，調(diào)優(yōu)數(shù)據(jù)庫系統(tǒng)的性能5. 轉(zhuǎn)儲與恢復(fù)數(shù)據(jù)庫6. 重構(gòu)數(shù)據(jù)庫 第二章 Data Model數(shù)據(jù)模型應(yīng)滿足三方面要求：一是能比較真實地模擬現(xiàn)實世界；二

9、是容易為人所理解；三是便于在計算機上實現(xiàn)。兩類：概念模型也稱信息模型，數(shù)據(jù)模型包括網(wǎng)狀模型、層次模型、關(guān)系模型。2.1 數(shù)據(jù)模型的要素 數(shù)據(jù)結(jié)構(gòu) 數(shù)據(jù)操作 數(shù)據(jù)的約束條件2.2 概念模型E-R Data Model ConceptsE-R數(shù)據(jù)模型Entity-Relationship Data ModelEER數(shù)據(jù)模型Extended Entity-Relationship Data Model1實體(entity)、實體集entity setentity set與entity是型type與值value的關(guān)系類似于前述data schema與database instance2屬性attrib

10、ute值集value set 實體鍵entity key實體主鍵entity primary key3聯(lián)系relationship基數(shù)比約束cardinality ratio constraint參與約束participation constraint：局部參與、全參與 結(jié)構(gòu)約束structural constraint 兩個實體之間的聯(lián)系可以分為三類：l 一對一聯(lián)系(1:1)l 一對多聯(lián)系(1:m)l 多對多聯(lián)系(m:n)所有ownership關(guān)系弱實體weak entity E-R diagram用E-R數(shù)據(jù)模型對某一單位進行模擬，可以得到ER數(shù)據(jù)模式，ER數(shù)據(jù)模式可以ER圖來直觀地表示。

11、entity:weak entity:relationship:attribute:例如： 教職工研究生班級職工編號姓名出生年月職稱是否博導(dǎo)是否碩導(dǎo)學(xué)號姓名出生年月學(xué)位類型是否在職課程課程號名稱開課學(xué)期學(xué)時上課地點學(xué)分班級號信箱教職工班級研究生課程班主任C_G導(dǎo)師任課可擔(dān)任選課MN1NNNMMNMMN止起時間止起時間類型性質(zhì)成績類型類型專業(yè)方向說明：1學(xué)位類型：碩士/博士2導(dǎo)師類型：主要指導(dǎo)老師、協(xié)助指導(dǎo)3研究生可能換導(dǎo)師，換專業(yè)、方向4選課性質(zhì)：學(xué)位課/非學(xué)位課5任課類型：主講/輔講6可擔(dān)任描述有哪些老師可以上哪些課7任課是指目前該課程的任課老師8開課學(xué)期：春/秋季9上課地點：目前該課程的上

12、課教室問題：1課性質(zhì)屬性為什么不屬于課程實體，而屬于選課聯(lián)系？2專業(yè)、方向可不可以屬于研究生？ EER data model1特殊化specialization和普遍化generalization全特殊化total specialization/局部特殊化partial specialization不相交特殊化disjoint specialization/重疊特殊化overlapping specialization2聚集aggregation3范疇category2.3 Hierarchy Data Model 層次數(shù)據(jù)模型的數(shù)據(jù)結(jié)構(gòu) 1層次模型的根本結(jié)構(gòu)圖 TS數(shù)據(jù)模式圖 TS數(shù)據(jù)模式的

13、一個值2多對多聯(lián)系在層次模型中的表示 層次數(shù)據(jù)模型的操縱與完整性約束 層次數(shù)據(jù)模型的存儲結(jié)構(gòu) 層次數(shù)據(jù)模型的優(yōu)缺點層次數(shù)據(jù)模型的優(yōu)點主要有：l層次數(shù)據(jù)模型本身比較簡單，只需很少幾條命令就能操縱數(shù)據(jù)庫，比較容易使用。l對于實體間聯(lián)系是固定的，且預(yù)先定義好的應(yīng)用系統(tǒng)，采用層次模型來實現(xiàn)，其性能優(yōu)于關(guān)系模型，不次于網(wǎng)狀模型。l層次數(shù)據(jù)模型提供了良好的完整性支持。層次數(shù)據(jù)模型的缺點主要有：l現(xiàn)實世界中很多聯(lián)系是非層次性的，如多對多聯(lián)系、一個結(jié)點具有多個雙親等，層次模型表示這類聯(lián)系的方法很笨拙，只能通過引入冗余數(shù)據(jù)(易產(chǎn)生不一致性)或創(chuàng)立非自然的數(shù)據(jù)組織(引入虛擬結(jié)點)來解決。l對插入和刪除操作的限制比

14、較多。l查詢子女結(jié)點必須通過雙親結(jié)點。l由于結(jié)構(gòu)嚴(yán)密，層次命令趨于程序化。2.4 網(wǎng)狀數(shù)據(jù)模型 網(wǎng)狀數(shù)據(jù)模型的數(shù)據(jù)結(jié)構(gòu) 網(wǎng)狀數(shù)據(jù)模型的操縱與完整性約束 網(wǎng)狀數(shù)據(jù)模型的存儲結(jié)構(gòu) 網(wǎng)狀數(shù)據(jù)模型的優(yōu)缺點網(wǎng)狀數(shù)據(jù)模型的優(yōu)點主要有：l能夠更為直接地描述現(xiàn)實世界，如一個結(jié)點可以有多個雙親、允許結(jié)點之間為多對多的聯(lián)系等。l具有良好的性能，存取效率較高。網(wǎng)狀數(shù)據(jù)模型的缺點主要有：l其DDL語言極其復(fù)雜。l數(shù)據(jù)獨立性較差。由于實體問的聯(lián)系本質(zhì)上是通過存取路徑指示的，因此應(yīng)用程序在訪問數(shù)據(jù)時要指定存取路徑。2.5 Relation Data Model Concepts 1. Attribute and Doma

15、inDomain: 第一范式1NF(first nomal form) atomic data非第一范式Non-First Nomal FormNF2空值：NULL 2. relation and tuple設(shè)有一命名為R的關(guān)系，它有屬性A1、A2、An，其對應(yīng)的城分別為Dl、D2、Dn那么關(guān)系R可表示為：R(D1/Al，D2/A2，Dn/An)或 R(A1，A2，An)或 R(A1A2An)R.A1表示關(guān)系R的屬性A1。degree(arity):nR的值：r r(R)r=t1,t2,tmt=， viDi,1in笛卡爾乘積 A B ABDEFGDEFG125612563478127890=1

16、290345634783490關(guān)系模式：SUDENT(姓名，學(xué)號，性別，出生年份籍貫，系別，入學(xué)年份)投影：RX tX STUDENT姓名，性別3. key定義：如果關(guān)系的某一屬性或?qū)傩越M的值唯一地決定其他所有屬性的值，也就是唯一地決定一個元組，而其任何真子集無此性質(zhì)，那么這個屬性或?qū)傩越M稱為該關(guān)系的候選鍵(candidate key)，或簡稱為鍵。superkey primary key alternate key all key (SUPPLY(供給商，零件名，工程名)prime attribute non-prime attributeforeign keyCOURSE(課程名，課程號，

17、學(xué)分，開課時間，先修課程號)GRADE(學(xué)號，課程號，成績) ConstraintR(D1/Al，D2/A2，Dn/An)1. Domain integrity constraint2. Entity integrity constraint3. Referential integrity constraint4. General integrity constraint Operationrelational algebra operations1. Select operation()2. Project operation()性別，籍貫、出生年份(STUDENT)假設(shè)包含那么：(R)=(

18、R)姓名(性別=女(STUDENT)3. Set operationABA-(A-B)union compatibility課程號(COURSE)-先修課程號(COURSE)系別=計算機系(STUDENT)系別=電子系(STUDENT)RS=|tR AND gS4 Join operationR S=(RS)連接條件：ANDANDAND 連接：AiBj等連接equijoin 自然連接natural join例：GRADE GRADE.課程號=COURSE.課程號課程名，課程號，學(xué)分COURSE關(guān)系代數(shù)操作集，-，是完備的操作集。，-， relationally complete5. Outer

19、 join operation6. Outer union operation Relational Calculus1. Tuple Relational Calculus2. Domain Relational Calculus 第三章Database Language SQL結(jié)構(gòu)化查詢語言(structured query language，簡稱SQL) 3.1 IntroductionSQL語言是1974年由Boyce和Chamberlin提出的。1975年至1979年IBM System R實現(xiàn)了這種語言。1986年10月 美國國家標(biāo)準(zhǔn)局(簡稱ANSI) SQL-861987年國際標(biāo)

20、準(zhǔn)化組織簡稱ISO也通過了這一標(biāo)準(zhǔn)。ANSI 1989年第二次公布SQL標(biāo)準(zhǔn)(SQL-89)1992年 SQL-92標(biāo)準(zhǔn)目前ANSI正在醞釀新的SQL標(biāo)準(zhǔn)：SQL3。現(xiàn)在SQL已被重新解釋成為：Standard Query LanguageSQL按其功能可分為四大局部：1.數(shù)據(jù)定義語言(Data Definition Language，簡稱DDL)2.查詢語言(Query Language，簡稱QL) 3.數(shù)據(jù)操縱語言(Data Manipulation Language，簡稱DML)4.數(shù)據(jù)控制語言(Data Control Language，簡稱DCL)SQL的特點1.綜合統(tǒng)一2.高度非過

21、程化3.面向集合的操作方式4.以同一種語法結(jié)構(gòu)提供兩種使用方式5.語言簡潔，易學(xué)易用表3-1 SQL語言的動詞 SQL語言的根本概念 3.2 數(shù)據(jù)定義表3-2 SQL的數(shù)據(jù)定義語句 定義、刪除與修改基表 1. 定義基表CREATE TABLE 表名 (列名數(shù)據(jù)類型列級完整件約束條件,列名數(shù)據(jù)類型列級完整性約束條件 表級完整性約束條件；列級完整性約束條件格式：NOT NULL UNIQUE DEFAULT 字值|USER|NULL表級完整性約束條件有三個任選項。用于定義主鍵的PRIMARY KEY子句，用于定義外鍵的FOREIGN KEY子句和用于定義列值限制條件的CHECK子句。格式：，PRI

22、MARY KEY ，F(xiàn)OREIGN KEY 外鍵名 REFERENCES ON DELETE RESTRICT |CASCADE|SET NULL，CHECK 條件IBM DB2 SQL主要支持以下數(shù)據(jù)類型：SMALLINT 半字長二進制整數(shù)。INTEGER或INT 全字長二進制整數(shù)。DECIMAL(p,q)或DEC(p,q) 壓縮十進制數(shù)，共p位，其中小數(shù)點后有q位。0qp15,q0時可以省略。FLOAT 雙字長浮點數(shù)。CHARTER(n)或CHAR(n) 長度為n的定長字符串。VARCHAR(n) 最大長度為n的變長字符串。GRAPHIC(n) 長度為n的定長圖形字符串。VARGRAPHI

23、C(n) 最大長度為n的變長圖形字符串。DATE 日期型，格式為YYYYMMDD。TIME 時間型，格式為。TIMESTAMP 日期加時間。例1 建立Student(學(xué)生)、Course課程、SC選課表。1“學(xué)生表student由學(xué)號(Sno)、姓名(Sname)、性別(Ssex)、年齡Sage、所在系(Sdept)5個屬性組成，可記為Student(Sno，Sname，Ssex，Sage，Sdept)其中sno為主鍵。2 “課程表course由課程號(Cno)、課程名(Cname)、先修課號(Cpno)、學(xué)分(Ccredit)4個屬性組成，可記為：Course(Cno，Cname，Cpno，

24、Ccredit)其中Cno為主鍵。3“學(xué)生選課表SC由學(xué)號(Sno)、課程號(Cno)、成績(Grade)3個屬性組成，其中(Sno，Cno)為主鍵。CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname VARCHAR(20) NOT NULL, Ssex CHAR(1)， Sage INT, Sdept CHAR(15),PRIMARY KEY(Sno)；CREATE TABLE Course (Cno CHAR(1) NOT NULL,Cname VARCHAR(20),Cpno CHAR(1)Ccredit DEC(2,1),P

25、RIMARY KEY(Cno),FOREIGN KEY (Cpno) REFERENCES Course ON DELETE RESTRICT)；CREATE TABLE SC (Sno CHAR(5) NOT NULL, Cno CHAR(1) NOT NULL, Grade DEC(4,1) DEFAULT NULL,PRIMARY KEY(Sno,Cno),FOREIGN KEY (Sno) REFERENCES Student ON DELETE CASCADE,FOREIGN KEY (Cno) REFERENCES Course ON DELETE RESTRICT); 2. 修改

26、基表 ALTER TABLE表名 ADD新列名數(shù)據(jù)類型完整性約束 DROP完整性約束名 MODIFY列名數(shù)據(jù)類型；例2 向student表增加“入學(xué)時間列，其數(shù)據(jù)類型為日期型。 ALTER TABLE Student ADD Scome DATE；例3 將年齡的數(shù)據(jù)類型改為半字長整數(shù)。ALTER TABLE Student MODIFY Sage SMALLINT;例4 刪除撤消Student表主鍵定義。ALTER TABLE Student DROP PRIMARY KEY；3. 刪除基表DROP TABLE 表名；例5刪除Student表。DROP TABLE Student； 建立與刪除

27、索引 1. 建立索引CREATE UNIQUE CLUSTER INDEX ON ( 次序 ， 次序)；排列次序，包括ASC(升序)和DESC(降序)兩種，缺省值為ASC。CREATE CLUSTER INDEX Stusname ON Student(Sname);例6 為學(xué)生課程數(shù)據(jù)庫中的Student，Course，SC 3個表建立索引。其中Student表按學(xué)號升序建立唯一索引，course表按課程號升序建立唯一索引，SC表按學(xué)號升序和課程號降序建唯一索引。CREATE UNIQUE INDEX Stusno ON Student(Sno);CREATE UNIQUE INDEX Co

28、ucno ON Course(Cno);CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC); 2. 刪除索引DROP INDEX索引名；例7 刪除Student表的Stusname索引。DROP INDEX Stusname; 3.3 查詢SELECT ALL| DISTINCT 目標(biāo)列表達式，目標(biāo)列表達式FROM 表名或視圖名，表名或視圖名WHERE條件表達式GROUP BY列名1HAVING條件表達式ORDER BY列名2ASC | DESC； 單表查詢1. 選擇表中的假設(shè)干列1) 查詢指定列例1 查詢?nèi)w學(xué)生的學(xué)號與姓名。SELECT Sno

29、，SnameFROM Student;例2 查詢?nèi)w學(xué)生的姓名、學(xué)號、所在系。SELECT Sname，Sno，SdeptFROM Student；2) 查詢?nèi)苛欣? 查詢?nèi)w學(xué)生的詳細(xì)記錄SELECT *FROM Student；3) 查詢經(jīng)過計算的值例4 查詢?nèi)w學(xué)生的姓名及其出生年份。SELECT Sname，2004-SageFROM Student；例5 查詢?nèi)w學(xué)生的姓名、出生年份和所在系，要求用小寫字母表示所在系名SELECT Sname，Year of Birth：，2004-Sage，ISLOWER(Sdept)FROM Student;SELECT Sname NAME，

30、Year of Birth： BIRTH，2004-Sagc BIRTHDAY， ISLOWER(Sdept) DEPARTMENTFROM Student；結(jié)果為：NAME BIRTH BIRTHDAY DEPARTMENT 李勇 Year of Birth： 1976 cs 劉晨 Year of Birth： 1977 if 王名 Year of Birth： 1978 ma 張立 Year of Birth： 1978 if 2. 選擇表中的假設(shè)干元組 1) 消除取值重復(fù)的行例6 查詢所有選修過課的學(xué)生的學(xué)號。SELECT SnoFROM SC；假設(shè)SC表中有以下數(shù)據(jù)：Sno Cno G

31、rade95001 1 9295001 2 8595001 3 8895002 2 9095002 3 80執(zhí)行上面的SELECT語句后，結(jié)果為：Sno9500195001950019500295002SELECT DISTINCT SnoFROM SC；執(zhí)行結(jié)果為：Sno9500195002SELECT SnoFROM SC；與SELECT ALL SnoFROM SC；完全等價。2) 查詢滿足條件的元組表3-5 常用的查詢條件比較大小 等于 大于 小于 大于等于 小于等于！或 不等于有些產(chǎn)品中還包括：！ 不大于！ 不小于邏輯運算符NOT可與比較運算符同用，對條件求非。例7 查計算機系全體學(xué)

32、生的名單。SELECT SnameFROM StudentWHERE SdeptCS；例8 查所有年齡在20歲以下的學(xué)生姓名及其年齡。SELECT Sname，SageFROM studentWHERE Sage20；或SELECT Sname，SageFROM studentWHERE NOT Sage=20；例9 查考試成績有不及格的學(xué)生的學(xué)號。SELECT DISTINCT SnoFROM SCWHERE Grade60；確定范圍謂詞BETWEENAND和NOT BETWEENAND可以用來查找屬性值在(或不在)指定范圍內(nèi)的元組，其中BETWEEN后是范圍的下限(即低值)，AND后是范圍

33、的上限(即高值)。例10 查詢年齡在20至23歲之間的學(xué)生的姓名、系別和年齡。SELECT Sname，Sdept，SageFROM StudentWHERE Sage BETWEEN 20 AND 23；與BETWEENAND相對的謂詞是NOT BETWEENAND。例11 查詢年齡不在20至23歲之間的學(xué)生姓名、系別和年齡。SELECT Sname，Sdept，SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23；確定集合謂詞IN可以用來查找屬性值屬于指定集合的元組。例12 查信息系(IS)、數(shù)學(xué)系(MA)和計算機科學(xué)系(CS)的學(xué)生的姓名和性別

34、。SELECT SnameSsexFROM StudentWHERE Sdept IN(IS，MA，CS)；與IN相對的謂詞是NOT IN，用于查找屬性值不屬于指定集合的元組。例13 查既不是信息系、數(shù)學(xué)系，也不是計算機科學(xué)系的學(xué)生的姓名和性別。SELECT SnameSsexFROM StudentWHERE Sdept NOT IN(IS，MA，CS)；字符匹配謂詞LIKE可以用來進行字符串的匹配。其一般語法格式如下：NOT LIKE 匹配串其含義是查找指定的屬性列值與匹配串相匹配的元組，匹配串可以是一個完整的字符串，也可以含有通配符和_。其中：(百分號) 代表任意長度(長度可以為0)的字

35、符串。例如a%b表示以a開頭，以b結(jié)尾的任意長度的字符串，acb,adefb,ab等都滿足該匹配串。_(下劃線) 代表任意單個寧符。例如a_b表示以a開頭,以b結(jié)尾，長度為3的字符串，acb,adb等都滿足該匹配串。例14 查詢學(xué)號為95001的學(xué)生的詳細(xì)情況SELECT *FROM StudentWHERE Sno LIKE 9500l;該語句實際上與下面的語句完全等價：SELECT *FROM StudentWHERE Sno=9500l;例15 查所有姓劉的學(xué)生的姓名、學(xué)號和性別。SELECT Sname,Sno,SsexFROM StudentWHERE Sname LIKE 劉%；例

36、16 查姓“歐陽且全名為3個漢字的學(xué)生的姓名。SELECT SnameFROM StudentWHERE Sname LIKE 歐陽_；例17 查名字中第二字為“陽字的學(xué)生的姓名和學(xué)號。SELECT Sname，SnoFROM StudentWHERE Sname LIKE _陽；例18查所有不姓劉的學(xué)生姓名。SELECT Snamc，Sno，SsexFROM StudentWHERE Sname NOT LIKE 劉%；涉及空值的查詢謂詞IS NULL和IS NOT NULL可用來查詢空值和非空值。例19 某些學(xué)生選修某門課程后沒有參加考試，所以有選課記錄，但沒有考試成績，下面來查一下缺少成

37、績的學(xué)生的學(xué)號和相應(yīng)的課程號。SELECT Sno，CnoFROM SCWHERE Grade IS NULL；例20 查所有有成績的記錄的學(xué)生學(xué)號和課程號。SELECT Sno，CnoFROM SCWHERE Grade IS NOT NULL；多重條件查詢例21 查CS系年齡在20歲以下的學(xué)生姓名SELECT SnameFROM StudentWHERE Sdept=CSAND Sage20;例12中的IN謂詞實際上是多個OR運算符的縮寫，因此，例l2中的查詢也可以用OR運算符寫成如下等價形式：SELECT SnameSsexFROM StudentWHERE Sdept=ISOR Sde

38、pt=MAOR Sdept=CS； 3.對查詢結(jié)果排序例22 查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)的降序排列。SELECT Sno，GradeFROM SCWHERE Cno3ORDER BY Grade DESC；例23 查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系升序排列，對同一系中的學(xué)生按年齡降序排列。SELECT * FROM StudentORDER BY Sdept, Sage DESC; 4.使用集函數(shù)COUNT(DISTINCT | ALL *) 統(tǒng)計元組個數(shù)COUNT(DISTINCT | ALL列名) 統(tǒng)計一列中值的個數(shù)SUM(DISTINCT | ALL 列名)

39、計算一列值的總和(此列必須是數(shù)值型)AVG(DISTINCT | ALL 列名) 計算一列值的平均值(此列必須是數(shù)值型)MAX(DISTINCT | ALL 列名) 求一列值中的最大值MIN(DISTINCT | ALL 列名) 求一列值中的最小值例24 查詢學(xué)生總?cè)藬?shù)。SELECT COUNT*FROM Student;例25 查詢選修了課程的學(xué)生人數(shù)。SELECT COUNT(DISTINCT Sno)FROM SC;例26 計算1號課程的學(xué)生平均成績。SELECT AVG(Grade)FROM SCWHERE Cnol；例27 查詢學(xué)習(xí)l號課程的學(xué)生最高分?jǐn)?shù)。SELECT MAXGrad

40、eFROM SCWHERE Cno1； 5. 對查詢結(jié)果分組例28 查詢各個課程號與相應(yīng)的選課人數(shù)。SELECT Cno，COUNT(Sno)FROM SCGROUP BY Cno；例29 查詢信息系選修了3門以上課程的學(xué)生的學(xué)號，為簡單起見，假設(shè)SC表中有一列Dept，它記錄了學(xué)生所在系。SELECT SnoFROM SCWHERE DeptISGROUP BY SnoHAVING COUNT(*)3； 連接查詢1. 等值與非等值連接查詢 表名1列名1比較運算符表名2列名2其中比較運算符主要有：、！。此外，連接謂詞還可以使用下面形式：表名1列名1BETWEEN表名2列名2AND表名2當(dāng)連接運

41、算符為時，稱為等值連接。使用其它運算符稱為非等值連接。例30 查詢每個學(xué)生及其選修課程的情況。SELECT Student.*，SC.*FROM Student，SCWHERE Student.SnoSC.Sno;例31 Student表和SC表的笛卡爾積。SELECT Student.*，SC.*FROM Student，SC例32 自然連接Student表和SC表。SELECT Student.Sno，Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student，SCWHERE Student.SnoSC.Sno;或SELECT Student.*，Cn

42、o, GradeFROM Student，SCWHERE Student.SnoSC.Sno; 2. 自身連接例33 查詢每一門課的間接先修課(即先修課的先修課)。SELECT FIRST.Cno，SECOND.CpnoFROM Course FIRST，Course SECONDWHERE FIRST.CpnoSECOND.Cno；Cno Cpno 1 7 3 5 5 63. 外連接例34SELECT Student.Sno，Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student，SCWHERE Student.SnoSC.Sno(*);Studen

43、t.Sno，Sname, Ssex, Sage, Sdept, Cno, Grade 9500l 李勇 男 20 CS 1 92 9500l 李勇 男 20 CS 2 85 9500l 李勇 男 20 CS 3 88 95002 劉晨 女 19 IS 2 90 95002 劉晨 女 19 IS 3 80 95003 王名 女 18 MA 95004 張立 男 18 IS 4.復(fù)合條件連接 例35 查詢選修2號課程且成績在90分以上的所有學(xué)生。 SELECT Student.Sno,Sname FROM Student, SC WHERE Student .SnoSC.Sno AND SC.Cn

44、o2AND SC.Grade90;結(jié)果表為；Student.Sno Sname 95002 劉晨例36 查詢每個學(xué)生選修的課程名及其成績。SELECT Student.Sno，Sname，Cname， GradeFROM Student，SC，CourseWHERE Student.SnoSCSno and SCCnoCOURSECno； 嵌套查詢SELECT SnameFROM StudentWHERE Sno IN (SELECT Sno FROM SC WHERE Cno2)； 1.帶有IN謂詞的子查詢例37 查詢與“劉晨在同一個系學(xué)習(xí)的學(xué)生。查詢與“劉晨在同一個系學(xué)習(xí)的學(xué)生，可以首先確

45、定“劉晨所在系名，然后再查找所有在該系學(xué)習(xí)的學(xué)生。所以可以分步來完成此查詢：確定“劉晨所在系名SELECT SdeptFROM StudentWHERE Sname=劉晨；結(jié)果為：IS查找所有在IS系學(xué)習(xí)的學(xué)生。SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept=IS；分步寫查詢畢竟比較麻煩，上述查詢實際上可以用子查詢來實現(xiàn)，即將第一步查詢嵌入到第二步查詢中，用以構(gòu)造第二步查詢的條件。SQL語句如下：SELECT Sno，Sname，SdeptFROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WH

46、ERE Sname劉晨)；本例中的查詢也可以用前面學(xué)過的表的自身連接查詢來完成：SELECT S1.Sno，S1.Sname，S1.SdeptFROM Student S1,Student S2WHERE S1.Sdept=S2.Sdept AND S2.Sname=劉晨；本例中父查詢和子查詢均引用了Student表也可以像表的自身連接查詢那樣用別名將父查詢中的Student表與子查詢中的Student表區(qū)分開：SELECT S1.Sno，S1.Sname，S1.SdeptFROM Student S1WHERE S1.Sdept IN (SELECT S2.Sdept FROM Studen

47、t S2 WHERE S2.Sname劉晨)；例38 查詢選修了課程名為信息系統(tǒng)的學(xué)生學(xué)號和姓名。完成此查詢的根本思路是：首先在Course關(guān)系中找出信息系統(tǒng)課程的課程號Cno。然后在SC關(guān)系中找出Cno等于第一步給出的Cno集合中某個元素的Sno。最后在Student關(guān)系中選出Sno等于第二步中求出Sno集合中某個元素的元組。取出Sno和Sname送入結(jié)果表列。將上述想法寫成SQL語句就是：SELECT Sno,Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WH

48、ERE Cname信息系統(tǒng))；DBMS按照由內(nèi)向外的原那么求解此SQL語句，首先處理最內(nèi)層查詢塊，即課程名信息系統(tǒng)的課程號：SELECT CnoFROM CourseWHERE Cname信息系統(tǒng)查詢結(jié)果為3。從而可以把上面的SQL語句簡化為：SELECT Sno,Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN 3)；對此SQL語句再處理內(nèi)層查詢，SELECT SnoFROM SCWHERE Cno IN 3結(jié)果為95001和95002。從而可以把上面的SQL語句進一步簡化為：SELECT Sno,SnameFR

49、OM StudentWHERE Sno IN95001，95002；這樣就可以求得最終結(jié)果。本查詢同樣可以用連接查詢實現(xiàn)：SELECT Student.Sno,SnameFROM Student，SC，CourseWHERE Student.SnoSC.Sno AND SC.CnoCourse.Cno AND Course.Cname信息系統(tǒng)； 2. 帶有比較運算符的子查詢帶有比較運算符的子查詢是指父查詢與子查詢之間用比較運算符進行連接。當(dāng)用戶能確切知道內(nèi)層查詢返回的是單值時，可以用、!或等比較運算符。例如，在例37中，由于一個學(xué)生只可能在一個系學(xué)習(xí)，也就是說內(nèi)查詢劉晨所在系的結(jié)果是一個唯一值

50、，因此該查詢也可以用比較運算符來實現(xiàn)，其SQL語句如下；SELECT S1.Sno，S1.Sname，S1.SdeptFROM Student S1WHERE S1.Sdept = (SELECT S2.Sdept FROM Student S2 WHERE S2.Sname劉晨)；需要注意的是，子查詢一定要跟在比較符之后。以下寫法是錯誤的：SELECT S1.Sno，S1.Sname，S1.SdeptFROM Student S1WHERE (SELECT S2.Sdept FROM Student S2 WHERE S2.Sname劉晨)=S1.Sdept；例38中信息系統(tǒng)的課程號是唯一的

51、，但選修該課程的學(xué)生并不止一個，所以例38也可以用=運算符和IN謂詞共同完成：SELECT Sno,Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno= (SELECT Cno FROM Course WHERE Cname信息系統(tǒng))；3.帶有ANY或ALL謂詞的子查詢ANY 大于子查詢結(jié)果中的某個值A(chǔ)NY 小于子查詢結(jié)果中的某個值A(chǔ)NY 大于等于子查詢結(jié)果中的某個值A(chǔ)NY 小于等于子查詢結(jié)果中的某個值A(chǔ)NY 等于子查詢結(jié)果中的某個值!ANY或ANY 不等于子查詢結(jié)果中的某個值A(chǔ)LL 大于子查詢結(jié)果中的所有值A(chǔ)LL 小于子

52、查詢結(jié)果中的所有值A(chǔ)LL 大于等于子查詢結(jié)果中的所有值A(chǔ)LL 小于等于子查詢結(jié)果中的所有值A(chǔ)LL 等于子查詢結(jié)果中的所有值(通常沒有實際意義)!ALL或ALL 不等于子查詢結(jié)果中的任何一個值例39 查詢其他系中比IS系某一學(xué)生年齡小的學(xué)生名單。SELECT S1.Sname，S1.SageFROM Student S1WHERE S1.SageANY (SELECT S2.Sage FROM Student S2 WHERE S2.SdeptIS) AND S1.SdeptISORDER BY S1.Sage DESC；注意，S1.SdeptIS條件是父查詢塊中的條件，不是子查詢塊中的條件。用

53、集函數(shù)實現(xiàn)：SELECT S1.Sname，S1.SageFROM Student S1WHERE S1.Sage (SELECT MAX(S2.Sage) FROM Student WHERE S2.SdeptIS) AND S1.SdeptISORDER BY Sage DESC；例40 查詢其他系中比IS系所有學(xué)生年齡都小的學(xué)生名單。SELECT S1.Sname，S1.SageFROM Student S1WHERE S1.SageALL (SELECT S2.Sage FROM Student S2 WHERE S2.SdeptIS AND S1.SdeptIS“ORDER BY S

54、1.Sage DESC；本查詢同樣也可以用集函數(shù)實現(xiàn)。即首先用子查詢找出IS系的最小年齡(18)，然后在父查詢中查所有非IS系且年齡小于18歲的學(xué)生姓名及其年齡。SQL語句如下： SELECT S1.Sname，S1.SageFROM Student S1WHERE S1.Sage (SELECT MIN(S2.Sage) FROM Student S2 WHERE S2.SdeptIS) AND S1.SdeptISORDER BY S1.Sage DESC；事實上，用集函數(shù)實現(xiàn)子查詢通常比直接用ANY或ALL查詢效率要高。 4. 帶有EXISTS謂詞的子查詢EXISTS代表存在量詞。帶有E

55、XISTS謂詞的子查詢不退回任何實際數(shù)據(jù)，它只產(chǎn)生邏輯真值“TRUE或邏輯假值“FALSE。例41 查詢所有選修了1號課程的學(xué)生姓名。SELECT SnameFROM StudentWHERE EXISTS (SELECT * FROM SC WHERE SC.SnoStudent. Sno AND Cno1)；例41 查詢所有未修1號課程的學(xué)生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS (SELECT * FROM SC WHERE SC.SnoStudent. Sno AND Cno1)；帶有IN謂詞的例37可以用如下帶EXISTS謂詞的子查詢替

56、換：查詢與“劉晨在同一個系學(xué)習(xí)的學(xué)生。SELECT S1.Sno，S1.Sname，S1.SdeptFROM Student S1WHERE EXISTS (SELECT * FROM Student S2 WHERE S1.Sdept=S2.Sdept AND S2.Sname劉晨)；例42 查詢選修了全部課程的學(xué)生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.SnoStudent.Sno AND SC.CnoCours

57、e.Cno)；例43 查詢至少選修了學(xué)生95002選修的全部課程的學(xué)生號碼。此題的查詢要求可以做如下解釋，查詢這樣的學(xué)生，但凡95002選修的課，他都選修了。換句話說，假設(shè)有一個學(xué)號為x的學(xué)生，對所有的課程y，只要學(xué)號為95002的學(xué)生選修了課程y，那么x也選修了y；那么就將他的學(xué)號選出來。即不存在這樣的課程y，學(xué)生95002選修了y，而學(xué)生x沒有選。用SQL語言可表示如下：SELECT DISTINCT SnoFROM SC SCXWHERE NOT EXISTS (SELECT * FROM SC SCYWHERE SCY.Sno95002 AND NOT EXISTS (SELECT *

58、 FROM SC SCZ WHERE SCZ.SnoSCX.Sno AND SCZ.CnoSCY.Cno)； 集合查詢集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。例44 查詢計算機科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。SELECT *FROM StudentWHERE SdeptCSUNIONSELECT *FROM StudentWHERE Sage19；本查詢實際上是求計算機科學(xué)系的所有學(xué)生與年齡不大于19歲的學(xué)生的并集。例45 查詢選修了課程1或者選修了課程2的學(xué)生。本例實際上是查選修課程1的學(xué)生集合與選修課程2的學(xué)生集合的并集。SELECT SnoFR

59、OM SCWHERE Cno=1UNIONSELECT SnoFROM SCWHERE Cno=2；注：標(biāo)準(zhǔn)SQL只有并，沒有交和差，但實際上，交或差都可以用其它方法實現(xiàn)，具體實現(xiàn)根據(jù)不同的查詢而不同用語義替換。例46 查詢計算機科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的交集。本查詢換種說法就是，查詢計算機科學(xué)系中年齡不大于19歲的學(xué)生。例47 查詢選修課程l的學(xué)生集合與選修課程2的學(xué)生集合的交集本例實際上是查詢既選修了課程1又選修了課程2的學(xué)生。例48 查詢計算機科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的差集。本查詢換種說法就是，查詢計算機科學(xué)系中年齡大于19歲的學(xué)生。例49 查詢選修課程1的學(xué)生集

60、合與選修課程2的學(xué)生集合的差集本例實際上是查詢選修了課程1但沒有選修課程2的學(xué)生。 小結(jié)問題：有關(guān)系模式part(Item_no,Name,P_no)表示一個產(chǎn)品零部件情況及產(chǎn)品的組成P_no表示上一級的零件，如何用SQL實現(xiàn)查詢：查詢某個產(chǎn)品給定Item_no的所有零部件？。 3.4 數(shù)據(jù)更新 插入數(shù)據(jù) 1. 插入單個元組INSERT INTO 表名(屬性列1，屬性列2)VALUES (常量1，常量2)例1 將一個新學(xué)生記錄(學(xué)號：95020；姓名：陳冬；性別：男；所在系：IS；年齡：18歲)插入Student表中。INSERT INTO StudentVALUES(95020，陳冬，男，I