數(shù)據(jù)庫(kù)原理李芳芳第11講

1、An Introduction to Database System,3.3.5 集合查詢,標(biāo)準(zhǔn)SQL直接支持的集合操作種類 并操作(UNION) 一般商用數(shù)據(jù)庫(kù)支持的集合操作種類 并操作(UNION) 交操作(INTERSECT) 差操作(MINUS),An Introduction to Database System,1 并操作,形式 UNION ALL 參加UNION操作的各結(jié)果表的列數(shù)必須相同；對(duì)應(yīng)項(xiàng)的數(shù)據(jù)類型也必須相同,An Introduction to Database System,并操作（續(xù)）,例45 查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。 方法一： SELECT

2、 * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19；,An Introduction to Database System,并操作（續(xù)）,方法二： SELECT DISTINCT * FROM Student WHERE Sdept= CS OR Sage=19；,An Introduction to Database System,并操作（續(xù)）,例46 查詢選修了課程1或者選修了課程2的學(xué)生。 方法一： SELECT Sno FROM SC WHERE Cno= 1 UNION SELECT Sn

3、o FROM SC WHERE Cno= 2 ；,An Introduction to Database System,并操作（續(xù)）,方法二： SELECT DISTINCT Sno FROM SC WHERE Cno= 1 OR Cno= 2 ；,An Introduction to Database System,并操作（續(xù)）,例47 設(shè)數(shù)據(jù)庫(kù)中有一教師表Teacher(Tno, Tname,.)。查詢學(xué)校中所有師生的姓名。 SELECT Sname FROM Student UNION SELECT Tname FROM Teacher;,new,An Introduction to D

4、atabase System,2 交操作,標(biāo)準(zhǔn)SQL中沒有提供集合交操作，但可用其他方法間接實(shí)現(xiàn)。,An Introduction to Database System,2 交操作,例48 查詢計(jì)算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的交集 本例實(shí)際上就是查詢計(jì)算機(jī)科學(xué)系中年齡不大于19歲的學(xué)生 SELECT * FROM Student WHERE Sdept= CS AND Sage=19；,An Introduction to Database System,交操作（續(xù)）,例49 查詢選修課程1的學(xué)生集合與選修課程2的學(xué)生集合的交集 本例實(shí)際上是查詢既選修了課程1又選修了課程2的學(xué)生

5、Select sno From sc Where cno=1 Intersect Select sno From sc Where cno=2;,An Introduction to Database System,SELECT Sno FROM SC WHERE Cno= 1 AND Sno IN (SELECT Sno FROM SC WHERE Cno= 2 )；,An Introduction to Database System,交操作（續(xù)）,例50 查詢學(xué)生姓名與教師姓名的交集 本例實(shí)際上是查詢學(xué)校中與教師同名的學(xué)生姓名 SELECT DISTINCT Sname FROM Stu

6、dent WHERE Sname IN (SELECT Tname FROM Teacher);,new,An Introduction to Database System,3 差操作,例51 查詢計(jì)算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的差集。 Select * from student where sdept=CS Minus Select * from student where age=19;,An Introduction to Database System,3 差操作,例51 查詢計(jì)算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的差集。 本例實(shí)際上是查詢計(jì)算機(jī)科學(xué)系中年齡大于19歲

7、的學(xué)生 SELECT * FROM Student WHERE Sdept= CS AND Sage19；,An Introduction to Database System,差操作（續(xù)）,例52 查詢學(xué)生姓名與教師姓名的差集 本例實(shí)際上是查詢學(xué)校中未與教師同名的學(xué)生姓名 SELECT DISTINCT Sname FROM Student WHERE Sname NOT IN (SELECT Tname FROM Teacher);,new,An Introduction to Database System,4. 對(duì)集合操作結(jié)果的排序,ORDER BY子句只能用于對(duì)最終查詢結(jié)果排序，不能

8、對(duì)中間結(jié)果排序 任何情況下，ORDER BY子句只能出現(xiàn)在最后 對(duì)集合操作結(jié)果排序時(shí)，ORDER BY子句中用數(shù)字指定排序?qū)傩?new,An Introduction to Database System,對(duì)集合操作結(jié)果的排序（續(xù)）,例53 錯(cuò)誤寫法 SELECT * FROM Student WHERE Sdept= CS ORDER BY Sno UNION SELECT * FROM Student WHERE Sage=19 ORDER BY Sno；,new,An Introduction to Database System,對(duì)集合操作結(jié)果的排序（續(xù)）,正確寫法 SELECT *

9、FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19 ORDER BY 1；,new,An Introduction to Database System,3.3.6 SELECT語(yǔ)句的一般格式,SELECT ALL|DISTINCT 別名 ， 別名 FROM 別名 ， 別名 WHERE GROUP BY ， . HAVING ORDER BY ASC|DESC ， ASC|DESC ；,An Introduction to Database System,3.4 數(shù) 據(jù) 更 新,3.4.1 插入數(shù)據(jù) 3.

10、4.2 修改數(shù)據(jù) 3.4.3 刪除數(shù)據(jù),An Introduction to Database System,3.4.1 插入數(shù)據(jù),兩種插入數(shù)據(jù)方式 插入單個(gè)元組 插入子查詢結(jié)果,An Introduction to Database System,1. 插入單個(gè)元組,語(yǔ)句格式 INSERT INTO (，) VALUES ( ， ) 功能 將新元組插入指定表中。,An Introduction to Database System,插入單個(gè)元組（續(xù)）,例1 將一個(gè)新學(xué)生記錄 INSERT INTO Student VALUES (95020，陳冬，男，null，18)； 或 INSERT I

11、NTO Student (sname,sno,ssex,sage) VALUES (陳冬， 95020， 男，18)；,An Introduction to Database System,Add date value,Consider Party( name, birthday ) Insert into party values(Newborn, sysdate); Insert into party values(Rose, 11-Jan-84); Insert into party values(Jack, todate(11/01/1984,MM/DD/YYYY);,An Intro

12、duction to Database System,Use substitution variables,Consider SC(sno, cno, grade) Insert into sc values ( new 1:insert into sc values(95001,c01,90) 1 row created.,An Introduction to Database System,2. 插入子查詢結(jié)果,語(yǔ)句格式 INSERT INTO ( ， ) 子查詢； 功能 將子查詢結(jié)果插入指定表中,An Introduction to Database System,插入子查詢結(jié)果（續(xù)）,

13、例3 對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫(kù)。 第一步：建表 CREATE TABLE Deptage (Sdept CHAR(15) /* 系名*/ Avgage SMALLINT)； /*學(xué)生平均年齡*/,An Introduction to Database System,插入子查詢結(jié)果（續(xù)）,第二步：插入數(shù)據(jù) INSERT INTO Deptage(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；,An Introduction to Database System,Create Table R

14、e-visited,Oracle supports creating table and inserting tuples from a query combined. Example: Create talbe CSStudent as select sno, sname, ssex,sage from student where sdept=CS;,An Introduction to Database System,3.4.2 修改數(shù)據(jù),語(yǔ)句格式 UPDATE SET =，= WHERE ； 功能 修改指定表中滿足WHERE子句條件的元組,An Introduction to Datab

15、ase System,修改數(shù)據(jù)（續(xù)）,三種修改方式 修改某一個(gè)元組的值 修改多個(gè)元組的值 帶子查詢的修改語(yǔ)句,An Introduction to Database System,1. 修改某一個(gè)元組的值,例4 將學(xué)生95001的年齡改為22歲。 UPDATE Student SET Sage=22 WHERE Sno= 95001 ；,An Introduction to Database System,2. 修改多個(gè)元組的值,例5 將所有學(xué)生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1；,An Introduction to Database Syste

16、m,修改多個(gè)元組的值(續(xù)),例6 將信息系所有學(xué)生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1 WHERE Sdept= IS ；,An Introduction to Database System,3. 帶子查詢的修改語(yǔ)句,例7 將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)置零。 UPDATE SC SET Grade=0 WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；,An Introduction to Database System,修改數(shù)據(jù)（續(xù)）,DBMS在執(zhí)行修改語(yǔ)句時(shí)會(huì)檢查修

17、改操作 是否破壞表上已定義的完整性規(guī)則 實(shí)體完整性 主碼不允許修改 用戶定義的完整性 NOT NULL約束 UNIQUE約束 值域約束,An Introduction to Database System,3.4.3 刪除數(shù)據(jù),DELETE FROM WHERE ； 功能 刪除指定表中滿足WHERE子句條件的元組 WHERE子句 指定要?jiǎng)h除的元組 缺省表示要修改表中的所有元組,An Introduction to Database System,刪除數(shù)據(jù)（續(xù)）,三種刪除方式 刪除某一個(gè)元組的值 刪除多個(gè)元組的值 帶子查詢的刪除語(yǔ)句,An Introduction to Database Sys

18、tem,1. 刪除某一個(gè)元組的值,例8 刪除學(xué)號(hào)為95019的學(xué)生記錄。 DELETE FROM Student WHERE Sno=95019；,An Introduction to Database System,2. 刪除多個(gè)元組的值,例9 刪除2號(hào)課程的所有選課記錄。 DELETE FROM SC； WHERE Cno=2； 例10 刪除所有的學(xué)生選課記錄。 DELETE FROM SC；,An Introduction to Database System,3. 帶子查詢的刪除語(yǔ)句,例11 刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。 DELETE FROM SC WHERE CS= (SE

19、LETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；,An Introduction to Database System,刪除數(shù)據(jù)(續(xù)),DBMS在執(zhí)行刪除語(yǔ)句時(shí)會(huì)檢查所插元組 是否破壞表上已定義的完整性規(guī)則 參照完整性 不允許刪除 級(jí)聯(lián)刪除,An Introduction to Database System,更新數(shù)據(jù)與數(shù)據(jù)一致性,DBMS在執(zhí)行插入、刪除、更新語(yǔ)句時(shí)必 須保證數(shù)據(jù)庫(kù)一致性 必須有事務(wù)的概念和原子性 完整性檢查和保證,An Introduction to Database System,Consider the followi

20、ng table definitions: create table Departments (Name varchar2(20) not null, ); create table Students ( Sdept varchar2(20), foreign key(Sdept) references Departments(Name);,更新數(shù)據(jù)與數(shù)據(jù)一致性,An Introduction to Database System,Students:,Departments:,An Introduction to Database System,Insert a new tuple t int

21、o Students Allowed if tSdept is currently under Departments.Name Rejected otherwise,更新數(shù)據(jù)與數(shù)據(jù)一致性,An Introduction to Database System,Delete an existing tuple t from Departments Options: Rejected if tName is referenced by some tuple in Students. Allowed but also delete all tuples of Students which refer

22、ence t Allowed but also change t1Sdept to null for every t1 in Students which references t,更新數(shù)據(jù)與數(shù)據(jù)一致性,An Introduction to Database System,Update tName of a tuple t in Departments: Options: Rejected if tName is referenced by some tuple in Students Allowed but also update all tuples of Students which r

23、eference t Allowed but also change t1Sdept to null for every t1 in Students which reference t,更新數(shù)據(jù)與數(shù)據(jù)一致性,An Introduction to Database System,Specify options using SQL Format: foreign key (Sdept) references Departments(Name) on delete no action|cascade| set default|set null,更新數(shù)據(jù)與數(shù)據(jù)一致性,An Introduction

24、to Database System,Redefine the Students table. create table Students ( foreign key(Sdept) references Departments(Name) on delete no action on update cascade);,更新數(shù)據(jù)與數(shù)據(jù)一致性,An Introduction to Database System,視圖（Views）,A tables created by create table is a base table. A base table is a real table. The

25、conceptual schema of a database is the set of all base tables. A view is a virtual table that does not necessarily exist in physical form. Each view is defined, directly or indirectly, in terms of base tables.,An Introduction to Database System,3.5 視 圖,視圖的特點(diǎn) 虛表，是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表 只存放視圖的定義，不會(huì)出現(xiàn)數(shù)據(jù)冗余 基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變,An Introduction to Database System,3.5 視 圖,基于視圖的操作 查詢 刪除 受限更新 定義基于該視圖的新視圖,An Introduction to