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

1、數(shù)據(jù)查詢總結(jié)和數(shù)據(jù)更新操作數(shù)據(jù)查詢總結(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ù)量的查詢：帶數(shù)量的查詢：集合查詢集合查詢-Select * from studentWhere sdept=計算機UnionSelect * from studentWhere sage between 18 and 20-集合查詢沒有直接提供并差的關(guān)鍵詞，一般對集合查詢用條件的復(fù)合來查詢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() 運算公式 2005-sage2 集函數(shù)的一般格式集函數(shù)的一般格式 集函數(shù)（集函數(shù)（distinct | all ）Count：計數(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 語句)2) Not between and 3) not in () (select 語句語句)4) not like escape 5) Is not null6) not exists (select語句 ) 要注意全稱量詞的表達(dá)7) 多條件的連接3.4 數(shù)據(jù)更新數(shù)據(jù)更新（插入insert 修改update 刪除delete）3.4.1 插入數(shù)據(jù)插入數(shù)據(jù)一、插入單個元組一、插入單個元組插入單個元組的INSERT語句的格式為：INS

4、ERT INTO (,.) VALUES ( ,.) 注意： 1）如果某些屬性列在INTO子句中沒有出現(xiàn)，則新記錄在這些列上將取空值。但必須注意的是，在表定義時說明了NOT NULL的屬性列不能取空值。 2）如果INTO子句中沒有指明任何列名，則新插入的記錄必須在每個屬性列上均有值。例例 插入一條選課記錄插入一條選課記錄(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列上取空值。 二、插入子查詢結(jié)果二、插入子查詢結(jié)果 子查詢不僅可以嵌套在SELECT語句中，用以構(gòu)造父查詢的條件，也可以嵌套在INSERT語句中，用以生成要插入的數(shù)據(jù)。插入子查詢結(jié)果的INSERT語句的格式為： -INSERT INTO ( ,.) 子查詢子查詢；-其功能是以批量插入，一次將子查詢的結(jié)果全部插入指定表中。首先要在數(shù)據(jù)庫中建立一個新表例例 對每一個系，求學(xué)生人數(shù)及平均年齡，并把結(jié)果存入數(shù)對每一個系，求學(xué)生人數(shù)及平均年齡

6、，并把結(jié)果存入數(shù)據(jù)庫。據(jù)庫。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;然后將查詢結(jié)果存入新表中。3.4.2 修改數(shù)據(jù)修改數(shù)據(jù)修改操作又稱為更新操作，其語句的一般格式為：UPDATE SET =,=. WHERE ;其功能是修改指定表中滿足WHERE子句條件的元組。其中SET子句用于指定修改方法，即用的值取代相應(yīng)的屬性列值。

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

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

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

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

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

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

13、select cno from course Where cname=數(shù)據(jù)庫 ) 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 刪除李強選修刪除李強選修數(shù)據(jù)庫的選課記錄的選課記錄3 將選了所有課程的學(xué)生的姓名后面加

14、將選了所有課程的學(xué)生的姓名后面加*1 有表有表qq（cno,cname,count）,要求將選課人數(shù)在要求將選課人數(shù)在100人以上的課號，課程名稱，選課人數(shù)存放到表人以上的課號，課程名稱，選課人數(shù)存放到表qq中。中。Select * from sc,(select * from student where sdept=計算計算機機) 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.從一個或多個基本表(或視圖)導(dǎo)出的表，是虛表虛表；2. 使用戶以不同方式看數(shù)據(jù)不同方式看數(shù)據(jù)；3. 數(shù)據(jù)庫中只存只存視圖的定義定義，不存數(shù)據(jù)不存數(shù)據(jù)；4. 對視圖的更新有限制更新有限制。主要目的：提供數(shù)據(jù)庫保護(hù)。學(xué)生教師教務(wù)管理學(xué)生管理系統(tǒng)教師管理系統(tǒng)教務(wù)管理系統(tǒng)基本表基本表基本表基本表視圖視圖用戶3.5.1 定義視圖一、創(chuàng)建視圖 SQL語言用CRE

16、ATE VIEW命令建立視圖，其一般格式為： CREATE VIEW (,.) AS WITH CHECK OPTION; 其中子查詢可以是任意復(fù)雜的SELECT語句，但通常不允許不允許含有ORDER BY子句子句和DISTINCT短語。 WITH CHECK OPTION表示對視圖進(jìn)行UPDATE、 INSERT和DELETE操作時要保證更新、插入或刪除的行滿足視圖定義中視圖定義中的謂詞條件的謂詞條件（即子查詢中的條件表達(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)行修改和插入操作時仍須保證該視圖只有信息系的學(xué)生-CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS WITHCHECKOPTION;- WITH CHECK OPTION保證更新時滿足視圖定義的謂詞條件。行列子集視圖行列子集視圖：從一個基本

18、表中導(dǎo)出，只是去掉了某些行或列(保留原表主碼)，這樣的視圖稱行列子集視圖行列子集視圖。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分組視圖分組視圖，子查詢帶集函數(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二、刪除視圖語句的格式為：DROP VIEW ;一個視圖被刪除后，由此視圖導(dǎo)出的其他視圖也將失效，用戶應(yīng)該使用DROP VIEW語句將他們一一刪除。例8 刪除視圖IS_S1DROP VIEW IS_S1;3.5.2 查詢視圖查詢視圖DBMS執(zhí)行對視圖的查詢時，首先進(jìn)行有效性檢查，檢查查詢涉及的表、視圖等是否在數(shù)據(jù)庫中存在，如果存在，則從數(shù)據(jù)字典中取出查詢涉及的視圖的定義，把定義中的子查詢和用戶對視圖的查詢結(jié)合起來，轉(zhuǎn)

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

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

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

23、, 20);DBMS將其轉(zhuǎn)換為對基本表的更新： INSERT INTO Student(Sno,Sname,Sage,Sdept)VALUES(95008, 小新, 20, IS); 這里系統(tǒ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)換為對基本表的更新： Update IS_StudentSet sname=蠟筆小新蠟筆小新，sage=20Where sname= 小新小新 and sdept=is; 修改Update view1Set scount=200Where sdept=is象這種修改不能被執(zhí)行，因為scount是虛列，也就是不能改元組的個數(shù)，這里計數(shù)值改了 也沒用。從視圖中刪除：從視圖中刪除：-Delete From is_studentWhere sno=97008-Delete From studentWhere sno=97008And sdept=is通過視圖更新基本表，從理論上來說有的是可以更新的（不能更新），有的是根本不可能更新的（不可