數(shù)據(jù)庫(kù)第3章456節(jié)

1、數(shù)據(jù)查詢(xún)總結(jié)和數(shù)據(jù)更新操作數(shù)據(jù)查詢(xún)總結(jié)和數(shù)據(jù)更新操作order by sno desc; select top 3 * from student-where sno in(select top 4 sno from student order by sno)-帶數(shù)量的查詢(xún)：帶數(shù)量的查詢(xún)：集合查詢(xún)集合查詢(xún)-Select * from studentWhere sdept=計(jì)算機(jī)UnionSelect * from studentWhere sage between 18 and 20-集合查詢(xún)沒(méi)有直接提供并差的關(guān)鍵詞，一般對(duì)集合查詢(xún)用條件的復(fù)合來(lái)查詢(xún)1 Select的一般格式Select all

2、 | distinct 別名， 別名From 別名 , 別名where group by having order by asc|desc1 目標(biāo)列表達(dá)式格式：目標(biāo)列表達(dá)式格式：1）*2）.* ,. * count(all | distinct *) 集函數(shù)3) 表名或別名.屬性列名表達(dá)式:屬性名 sno，sname 常量 出生年份 函數(shù) year(getdate() 運(yùn)算公式 2005-sage2 集函數(shù)的一般格式集函數(shù)的一般格式 集函數(shù)（集函數(shù)（distinct | all ）Count：計(jì)數(shù)Max：最大值Min：最小值A(chǔ)vg：平均值Sum：求和Select cno,count(sno)

3、From scGroup by cno Having count(sno) 33 Where 的條件表達(dá)式：的條件表達(dá)式：1) any | all(select 語(yǔ)句)2) Not between and 3) not in () (select 語(yǔ)句語(yǔ)句)4) not like escape 5) Is not null6) not exists (select語(yǔ)句 ) 要注意全稱(chēng)量詞的表達(dá)7) 多條件的連接3.4 數(shù)據(jù)更新數(shù)據(jù)更新（插入insert 修改update 刪除delete）3.4.1 插入數(shù)據(jù)插入數(shù)據(jù)一、插入單個(gè)元組一、插入單個(gè)元組插入單個(gè)元組的INSERT語(yǔ)句的格式為：INS

4、ERT INTO (,.) VALUES ( ,.) 注意： 1）如果某些屬性列在INTO子句中沒(méi)有出現(xiàn)，則新記錄在這些列上將取空值。但必須注意的是，在表定義時(shí)說(shuō)明了NOT NULL的屬性列不能取空值。 2）如果INTO子句中沒(méi)有指明任何列名，則新插入的記錄必須在每個(gè)屬性列上均有值。例例 插入一條選課記錄插入一條選課記錄(95020，1)INSERT INTO SC(Sno, Cno) VALUES (95020, 1); -Insert into sc(sno,cno,grade) values(95005,1,88)Insert into sc values(95005,1,88)-很多系

5、統(tǒng)允許一次插入多條記錄，如INSERT INTO SC(Sno, Cno) VALUES (95003 , 1),(95003 , 2); 新插入的記錄在Grade列上取空值。 二、插入子查詢(xún)結(jié)果二、插入子查詢(xún)結(jié)果 子查詢(xún)不僅可以嵌套在SELECT語(yǔ)句中，用以構(gòu)造父查詢(xún)的條件，也可以嵌套在INSERT語(yǔ)句中，用以生成要插入的數(shù)據(jù)。插入子查詢(xún)結(jié)果的INSERT語(yǔ)句的格式為： -INSERT INTO ( ,.) 子查詢(xún)子查詢(xún)；-其功能是以批量插入，一次將子查詢(xún)的結(jié)果全部插入指定表中。首先要在數(shù)據(jù)庫(kù)中建立一個(gè)新表例例 對(duì)每一個(gè)系，求學(xué)生人數(shù)及平均年齡，并把結(jié)果存入數(shù)對(duì)每一個(gè)系，求學(xué)生人數(shù)及平均年齡

6、，并把結(jié)果存入數(shù)據(jù)庫(kù)。據(jù)庫(kù)。CREATE TABLE Deptage (Sdept CHAR(15)， Num INT, Avgage INT); INSERTINTO Deptage(Sdept,Num, Avgage) SELECT Sdept, COUNT(*),AVG(Sage) FROM Student GROUP BY Sdept;然后將查詢(xún)結(jié)果存入新表中。3.4.2 修改數(shù)據(jù)修改數(shù)據(jù)修改操作又稱(chēng)為更新操作，其語(yǔ)句的一般格式為：UPDATE SET =,=. WHERE ;其功能是修改指定表中滿(mǎn)足WHERE子句條件的元組。其中SET子句用于指定修改方法，即用的值取代相應(yīng)的屬性列值。

7、如果省略WHERE子句，則表示要修改表中的所有元組。一、修改某一個(gè)元組的值一、修改某一個(gè)元組的值例4 將學(xué)生95001的年齡改為22歲UPDATE Student SET Sage=22 WHERE Sno=95001; 二、修改多個(gè)元組的值二、修改多個(gè)元組的值例5 將所有信息系學(xué)生的年齡增加1歲UPDATE Student SET Sage=Sage+1 where sdept=is; 例例6 將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)加將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)加10分。分。UPDATE SCSET grade=grade+10WHERE CS=(SELECT Sdept FROM Student W

8、HERE Student.Sno=SC.Sno);(處理過(guò)程類(lèi)似于相關(guān)子查詢(xún))三、帶子查詢(xún)的修改語(yǔ)句三、帶子查詢(xún)的修改語(yǔ)句子查詢(xún)也可以嵌套在UPDATE語(yǔ)句中，用以構(gòu)造執(zhí)行修改操作的條件。例例 將李強(qiáng)的數(shù)據(jù)庫(kù)成績(jī)加將李強(qiáng)的數(shù)據(jù)庫(kù)成績(jī)加10分。分。update SCset grade=grade+10where sno=(select sno from student where sname=李強(qiáng)) and cno=(select cno from course where cname=數(shù)據(jù)庫(kù));例如，學(xué)號(hào)為95007的學(xué)生因病休學(xué)一年，復(fù)學(xué)后需要將其學(xué)號(hào)改為96089，由于Student表和S

9、C表都有關(guān)于95007的信息，因此兩個(gè)表都需要修改，這種修改只能通過(guò)兩條UPDATE語(yǔ)句進(jìn)行。第一條UPDATE語(yǔ)句修改Student表：UPDATE Student SET Sno=96089 WHERE Sno=95007;第二條UPDATE語(yǔ)句修改SC表：UPDATE SC SET Sno=96089 WHERE Sno=95007;四、修改操作與數(shù)據(jù)庫(kù)的一致性四、修改操作與數(shù)據(jù)庫(kù)的一致性UPDATE語(yǔ)句一次只能操作一個(gè)表。這會(huì)帶來(lái)一些問(wèn)題。UPDATE Student SET Sno=96089 WHERE Sno=95007;-UPDATE SC SET Sno=96089 WHER

10、E Sno=95007;機(jī)器突然出現(xiàn)故障機(jī)器突然出現(xiàn)故障數(shù)據(jù)將永遠(yuǎn)處于不一致?tīng)顟B(tài)參照完整性受到破壞在執(zhí)行了第一條UPDATE語(yǔ)句之后，數(shù)據(jù)庫(kù)中的數(shù)據(jù)已處于不一致?tīng)顟B(tài)，因?yàn)檫@時(shí)實(shí)際上已沒(méi)有學(xué)號(hào)為95007的學(xué)生了，但SC表中仍然記錄著關(guān)于95007學(xué)生的選課信息，即數(shù)據(jù)的參照完整性參照完整性受到破壞。只有執(zhí)行了第二條UPDATE語(yǔ)句之后，數(shù)據(jù)才重新處于一致?tīng)顟B(tài)。但如果執(zhí)行完一條語(yǔ)句之后，機(jī)器突然出現(xiàn)故障，無(wú)法再繼續(xù)執(zhí)行第二條UPDATE語(yǔ)句，則數(shù)據(jù)庫(kù)中的數(shù)據(jù)將永遠(yuǎn)處于不一致?tīng)顟B(tài)。因此必須保證這兩條UPDATE語(yǔ)句要么都做，要么都不做。為解決這一問(wèn)題，數(shù)據(jù)庫(kù)系統(tǒng)通常都引入了事務(wù)事務(wù)（Transac

11、tion）的概念，將在后面詳細(xì)介紹.3.4.3 刪除數(shù)據(jù)刪除數(shù)據(jù)刪除語(yǔ)句的一般格式為：DELETEFROM WHERE ; DELETE語(yǔ)句的功能是從指定表中刪除滿(mǎn)足WHERE子句條件的所有元組。如果省略WHERE子句，表示刪除表中全部元組，但表的定義仍在字典中。也就是說(shuō)，DELETE語(yǔ)句刪除的是表中的數(shù)據(jù)，而不是關(guān)于表的定義。 一、刪除某一個(gè)元組的值一、刪除某一個(gè)元組的值例7 刪除學(xué)號(hào)為95019的學(xué)生記錄DELETEFROM StudentWHERE Sno=95019; 二、刪除多個(gè)元組的值二、刪除多個(gè)元組的值例8 刪除所有的學(xué)生選課記錄DELETE FROM SC; 這條DELETE語(yǔ)

12、句將使SC成為空表，它刪除了SC的所有元組。 三、帶子查詢(xún)的刪除語(yǔ)句三、帶子查詢(xún)的刪除語(yǔ)句子查詢(xún)同樣也可以嵌套在DELETE語(yǔ)句中，用以構(gòu)造執(zhí)行刪除操作的條件。例例9刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄DELETE FROM SC WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno);例例9 刪除李強(qiáng)選修刪除李強(qiáng)選修數(shù)據(jù)庫(kù)的選課記錄的選課記錄Delete from scWhere sno= (select sno from student where sname=李強(qiáng)) and cno= (

13、select cno from course Where cname=數(shù)據(jù)庫(kù) ) Update student Set sname=sname+* Where sno in( select sno from student x where not exists ( select * from course Where not exists (select * from sc where sno=x.sno and cno=o) )例例10 將選了所有課程的學(xué)生的姓名后面加將選了所有課程的學(xué)生的姓名后面加*2 刪除李強(qiáng)選修刪除李強(qiáng)選修數(shù)據(jù)庫(kù)的選課記錄的選課記錄3 將選了所有課程的學(xué)生的姓名后面加

14、將選了所有課程的學(xué)生的姓名后面加*1 有表有表qq（cno,cname,count）,要求將選課人數(shù)在要求將選課人數(shù)在100人以上的課號(hào)，課程名稱(chēng)，選課人數(shù)存放到表人以上的課號(hào)，課程名稱(chēng)，選課人數(shù)存放到表qq中。中。Select * from sc,(select * from student where sdept=計(jì)算計(jì)算機(jī)機(jī)) as RWhere sc.sno=R.sno ( select * from student x where not exists ( select * from course Where not exists (select * from sc where sn

15、o=x.sno and cno=o) )Select sno,sname,2012-sage from Where sdept=isCreate table temp(sno ,Sname,Ssex ,Sage,Sdept)導(dǎo)入導(dǎo)入3.5 視圖1.從一個(gè)或多個(gè)基本表(或視圖)導(dǎo)出的表，是虛表虛表；2. 使用戶(hù)以不同方式看數(shù)據(jù)不同方式看數(shù)據(jù)；3. 數(shù)據(jù)庫(kù)中只存只存視圖的定義定義，不存數(shù)據(jù)不存數(shù)據(jù)；4. 對(duì)視圖的更新有限制更新有限制。主要目的：提供數(shù)據(jù)庫(kù)保護(hù)。學(xué)生教師教務(wù)管理學(xué)生管理系統(tǒng)教師管理系統(tǒng)教務(wù)管理系統(tǒng)基本表基本表基本表基本表視圖視圖用戶(hù)3.5.1 定義視圖一、創(chuàng)建視圖 SQL語(yǔ)言用CRE

16、ATE VIEW命令建立視圖，其一般格式為： CREATE VIEW (,.) AS WITH CHECK OPTION; 其中子查詢(xún)可以是任意復(fù)雜的SELECT語(yǔ)句，但通常不允許不允許含有ORDER BY子句子句和DISTINCT短語(yǔ)。 WITH CHECK OPTION表示對(duì)視圖進(jìn)行UPDATE、 INSERT和DELETE操作時(shí)要保證更新、插入或刪除的行滿(mǎn)足視圖定義中視圖定義中的謂詞條件的謂詞條件（即子查詢(xún)中的條件表達(dá)式）。例1 建立信息系學(xué)生的視圖。-CREATE VIEW IS_Student (Sno, Sname, Sage ) AS SELECT Sno, Sname, Sag

17、e FROM Student WHERE Sdept=IS;-CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS;例2 建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操作時(shí)仍須保證該視圖只有信息系的學(xué)生-CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS WITHCHECKOPTION;- WITH CHECK OPTION保證更新時(shí)滿(mǎn)足視圖定義的謂詞條件。行列子集視圖行列子集視圖：從一個(gè)基本

18、表中導(dǎo)出，只是去掉了某些行或列(保留原表主碼)，這樣的視圖稱(chēng)行列子集視圖行列子集視圖。Create view st2 (sno,sage,sdept)As select sno,sge,sdept From student-帶表達(dá)式的視圖，即帶虛擬列的視圖帶虛擬列的視圖。Create view st3 (sno,sname,brithyear)As Select sno,sname,year(getdate()-sageFrom student分組視圖分組視圖，子查詢(xún)帶集函數(shù)和GROUP BY分組的視圖。-Create view stu4(sdept,scount,maxsage,minsag

19、e,avgage)AsSelect sdept,count(sno),max(sage),min(sage),avg(sage)From studentGroup by sdept二、刪除視圖語(yǔ)句的格式為：DROP VIEW ;一個(gè)視圖被刪除后，由此視圖導(dǎo)出的其他視圖也將失效，用戶(hù)應(yīng)該使用DROP VIEW語(yǔ)句將他們一一刪除。例8 刪除視圖IS_S1DROP VIEW IS_S1;3.5.2 查詢(xún)視圖查詢(xún)視圖DBMS執(zhí)行對(duì)視圖的查詢(xún)時(shí)，首先進(jìn)行有效性檢查，檢查查詢(xún)涉及的表、視圖等是否在數(shù)據(jù)庫(kù)中存在，如果存在，則從數(shù)據(jù)字典中取出查詢(xún)涉及的視圖的定義，把定義中的子查詢(xún)和用戶(hù)對(duì)視圖的查詢(xún)結(jié)合起來(lái)，轉(zhuǎn)

20、換成對(duì)基本表的查詢(xún)，然后再執(zhí)行這個(gè)經(jīng)過(guò)修正的查詢(xún)。將對(duì)視圖的查詢(xún)轉(zhuǎn)換為對(duì)基本表的查詢(xún)的過(guò)程稱(chēng)為視視圖的消解圖的消解（View Resolution）。例例1 在信息系學(xué)生的視圖中找出年齡小于在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生歲的學(xué)生SELECT Sno, SageFROM IS_Student（信息系學(xué)生的視圖信息系學(xué)生的視圖） WHERE Sage20;DBMS執(zhí)行此查詢(xún)時(shí)，將其與執(zhí)行此查詢(xún)時(shí)，將其與IS_Student視圖定義中的子查詢(xún)視圖定義中的子查詢(xún)SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS ; 結(jié)合起來(lái)，轉(zhuǎn)換成對(duì)基

21、本表結(jié)合起來(lái)，轉(zhuǎn)換成對(duì)基本表Student的查詢(xún)，修正后的查詢(xún)語(yǔ)的查詢(xún)，修正后的查詢(xún)語(yǔ)句為：句為： SELECT Sno, Sage FROM Student WHERE Sdept=IS AND Sage100修正后的SQL表達(dá)： select sdept,count(sno) as scountFrom studentWhere count(sno)100有問(wèn)題：where條件中出現(xiàn)集函數(shù)正確表示：Select sdept,count(sno) as scountFrom studentGroup by sdeptHaving count(sno)100對(duì)試圖的查詢(xún)要注意非行列子集視圖的情

22、況，有可能出現(xiàn)錯(cuò)誤。3.5.3 更新視圖由于視圖是不實(shí)際存儲(chǔ)數(shù)據(jù)的虛表，因此對(duì)視圖的更新，最終要轉(zhuǎn)換為對(duì)基本表的更新最終要轉(zhuǎn)換為對(duì)基本表的更新。為防止用戶(hù)通過(guò)視圖對(duì)數(shù)據(jù)進(jìn)行增刪改時(shí)，無(wú)意或故意操作不屬于視圖范圍內(nèi)的基本表數(shù)據(jù)，可在定義視圖時(shí)加上WITH CHECK OPTION子句，這樣在視圖上增刪改數(shù)據(jù)時(shí)，DBMS會(huì)進(jìn)一步檢查視圖定義中的條件，若不滿(mǎn)足條件，則拒絕執(zhí)行該操作。 向視圖中插入數(shù)據(jù)向視圖中插入數(shù)據(jù)例1 向信息系學(xué)生視圖IS_Student中插入一個(gè)新的學(xué)生記錄，其中學(xué)號(hào)為95008，姓名為小新，年齡為20歲INSERTINTO IS_StudentVALUES(95008, 小新

23、, 20);DBMS將其轉(zhuǎn)換為對(duì)基本表的更新： INSERT INTO Student(Sno,Sname,Sage,Sdept)VALUES(95008, 小新, 20, IS); 這里系統(tǒng)自動(dòng)將系名IS放入VALUES子句中。 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS WITHCHECKOPTION; 視圖中修改數(shù)據(jù)視圖中修改數(shù)據(jù)例2 向信息系學(xué)生視圖IS_Student中姓名為小新改名為蠟筆小新，年齡為20歲Update IS_StudentSet sname=蠟筆小新蠟筆小

24、新，sage=20Where sname= 小新小新;-DBMS將其轉(zhuǎn)換為對(duì)基本表的更新： Update IS_StudentSet sname=蠟筆小新蠟筆小新，sage=20Where sname= 小新小新 and sdept=is; 修改Update view1Set scount=200Where sdept=is象這種修改不能被執(zhí)行，因?yàn)閟count是虛列，也就是不能改元組的個(gè)數(shù)，這里計(jì)數(shù)值改了 也沒(méi)用。從視圖中刪除：從視圖中刪除：-Delete From is_studentWhere sno=97008-Delete From studentWhere sno=97008And sdept=is通過(guò)視圖更新基本表，從理論上來(lái)說(shuō)有的是可以更新的（不能更新），有的是根本不可能更新的（不可