數(shù)據(jù)庫原理與應(yīng)用課件第4章關(guān)系數(shù)據(jù)庫的標準語言SQL5

第4章關(guān)系數(shù)據(jù)庫的標準語言SQL第4章關(guān)系數(shù)據(jù)庫的標準語言SQL4.1SQL語言4.3數(shù)據(jù)查詢4.4數(shù)據(jù)操作語句4.6索引4.5視圖4.2數(shù)據(jù)定義使學生能夠掌握SQL的數(shù)據(jù)的插入、刪除和修改操作功能教學目標能熟練地對記錄進行插入和修改操作技能目標重點掌握SQL的插入、刪除、修改等數(shù)據(jù)更新功能重點SQL的插入、刪除、修改

難點

前面我們掌握了數(shù)據(jù)表的查詢,那么表中的數(shù)據(jù)是怎么輸進去的呢？怎么修改和刪除呢？簡述SELECT語句的一般執(zhí)行步驟

什么是相關(guān)子查詢與不相關(guān)子查詢？

返回`4.4數(shù)據(jù)操作語句4.4.1插入語句（Insert）4.4.2更新語句（Update）4.4.3刪除語句（Delete）SQL的功能SQL的功能和相應(yīng)的語句——SQL功能語句數(shù)據(jù)定義CREATE，ALTER，DROP數(shù)據(jù)查詢SELECT數(shù)據(jù)修改INSERT，UPDATE，DELETE數(shù)據(jù)控制GRANT，REVOKE，……

4.4.1插入語句格式1:插入一個新元組InsertInto

關(guān)系[(屬性1,屬性2,…)]

Values

(值1,值2,…)注意所列值的個數(shù)必須和屬性的個數(shù)相等，且一一對應(yīng)在插入的新元組中，對沒有指定的屬性填入缺省值（CreateTable時定義），沒有缺省值時填入空值為新元組的哪些屬性賦值。缺省為全部屬性為前面的屬性賦予什么值例：InsertIntoRValues

('劉朝','物理',80)姓名課程成績王紅物理70張軍化學90姓名課程成績王紅物理70張軍化學90劉朝物理80RR插入一個元組4.4.1插入語句例：新建表S：

CreateTableS(學號VARCHAR(4),姓名VARCHAR(10)notnull,年齡SMALLINTdefault22,班級VARCHAR(20));在S中插入元組InsertInto

S(學號,姓名)Values

(1,'王紅')

學號姓名年齡班級學號姓名年齡班級1王紅22NullSS4.4.1插入語句格式2:插入多個元組（一個SQL查詢的結(jié)果）InsertInto

關(guān)系[(屬性1,屬性2,…)]

Select

……注意同樣地，對其它的屬性填入缺省值或空值為新元組的哪些屬性賦值。缺省為全部屬性查詢結(jié)果的各個屬性賦值給前面的屬性，所以要一一對應(yīng)：類型相容，屬性名可不同4.4.1插入語句例：添加所有學生選修數(shù)學課程的信息

InsertInto

R(姓名,課程)

Select

姓名,課程From

S,C

Where

課程=‘數(shù)學’姓名課程成績王紅物理70劉朝物理80張軍化學90姓名課程成績王紅物理70劉朝物理80張軍化學90王紅數(shù)學Null劉朝數(shù)學Null張軍數(shù)學NullRR姓名王紅張軍C課程物理數(shù)學化學S4.4.1插入語句例：統(tǒng)計每門課程的平均成績，然后保存到一個表里1.Createtable

C

(課程varchar(20),平均成績float)

2.

Insert

Into

C

Select

課程,Avg(成績)

From

R

Group

By

課程姓名課程成績王紅物理70王紅數(shù)學80張軍數(shù)學90課程平均成績物理70數(shù)學85RC4.4.1插入語句4.4數(shù)據(jù)操作語句4.4.1插入語句（Insert）4.4.2更新語句（Update）4.4.3刪除語句（Delete）格式

Update

關(guān)系Set

屬性1=表達式1

[,屬性2=表達式2]

……

[Where

條件]說明：在關(guān)系中找到滿足條件的元組，然后更新：表達式1的值賦予屬性1；表達式2的值賦予屬性2……沒有Where子句時，則對關(guān)系的全部元組都要更新4.4.2更新語句例：給銷售部門的職工增加10%的工資

Update

R

Set

工資=工資*1.1

Where

部門=“銷售”R工號姓名部門工資1老張銷售32002老李研發(fā)28003老孫銷售40004老陳生產(chǎn)2000工號姓名部門工資1老張銷售35202老李研發(fā)30803老孫銷售44004老陳生產(chǎn)2200R4.4.2更新語句例：在原有的學生關(guān)系S里面增加一個新屬性：選修課程數(shù)。然后填充正確的數(shù)值。Update

S

set

選修課程數(shù)

=

(

select

count(課程)

from

R

where

R.姓名=S.姓名)姓名課程成績王紅物理70王紅數(shù)學80張軍數(shù)學90姓名選修課程數(shù)王紅?張軍?RS4.4.2更新語句4.4數(shù)據(jù)操作語句4.4.1插入語句（Insert）4.4.2更新語句（Update）4.4.3刪除語句（Delete）格式DeleteFrom

關(guān)系[Where

條件]說明：在關(guān)系中找到滿足條件的元組，并刪除之如果沒有Where子句，表示刪除關(guān)系的全部元組(保留結(jié)構(gòu))一次只能刪除一個關(guān)系中的元組4.4.3刪除語句Delete語句例：刪除物理課的選修信息DeleteFrom

RWhere

課程=‘物理’RR姓名課程成績王紅物理50王紅數(shù)學60張軍數(shù)學90張軍化學90劉朝物理80姓名課程成績王紅數(shù)學60張軍數(shù)學90張軍化學904.4.3刪除語句例：刪除全部選修信息Delete

From

RRR姓名課程成績王紅物理50王紅數(shù)學60張軍數(shù)學90張軍化學90劉朝物理80姓名課程成績4.4.3刪除語句例：刪除平均分不及格的學生的選修信息

DeleteFrom

RWhere

姓名

in

(select

姓名

from

R

groupby

姓名

having

avg(成績)<60)R姓名課程成績王紅物理50王紅數(shù)學60張軍數(shù)學90張軍化學100劉朝物理80R姓名課程成績張軍數(shù)學90張軍化學100劉朝物理804.4.3刪除語句在進行數(shù)據(jù)的增、刪、改時數(shù)據(jù)庫管理系統(tǒng)自動檢查數(shù)據(jù)的完整性約束，而且這些檢查是在對數(shù)據(jù)進行操作之前進行的，只有當數(shù)據(jù)完全滿足完整性約束條件時才進行數(shù)據(jù)更改操作。對刪除和更新操作，無條件操作和有條件操作，對有條件的刪除和更新操作。小結(jié)

下課了。。。休息一會兒。。。追求第4章關(guān)系數(shù)據(jù)庫的標準語言SQL4.1SQL語言4.3數(shù)據(jù)查詢4.4數(shù)據(jù)操作語句4.6索引4.5視圖4.2數(shù)據(jù)定義理解視圖和的索引概念，掌握使用SQL語句定義視圖、刪除視圖和更新視圖的方法；創(chuàng)建和刪除索引的方法。教學目標培養(yǎng)學生積極思考、敢于動手、自主探究的能力，鼓勵學生要善于協(xié)作學習、大膽創(chuàng)新。培養(yǎng)學生團結(jié)互助的團隊合作意識技能目標定義視圖、刪除視圖和更新視圖；索引的創(chuàng)建和刪除方法。重點定義視圖、刪除視圖和更新視圖；索引的創(chuàng)建和刪除方法。難點

前面我們掌握了如何進行單表查詢,那么多表查詢數(shù)據(jù)庫如何實現(xiàn)?簡述SELECT語句的一般執(zhí)行步驟

數(shù)據(jù)操作中的刪除和數(shù)據(jù)定義中的刪除有什么不同？

返回`

視圖是從一個或幾個基本表（或視圖）導(dǎo)出的虛表。

（用戶外模式是由若干基本表和/或若干視圖構(gòu)成的）

視圖是一個虛表，只存儲視圖的定義，數(shù)據(jù)存在所基于

的基本表中。

視圖定義后就可象基本表一樣來使用?？蓜?chuàng)建、刪除視圖可用來定義新的視圖可在視圖上查詢（SELECT）可更新（INSERT，DELETE，UPDATE）視圖，但受限制4.5視圖4.5.1視圖的定義格式：CREATEVIEW視圖名

[（字段名[，字段名]…）]

AS子查詢

[WITHCHECKOPTION]；功能：在數(shù)據(jù)字典中存儲視圖的定義（但并不執(zhí)行子查詢），視圖名就可作為一個表來使用。例1建立信息系學生的視圖。

CREATEVIEWIS_StudentASSELECT*FROMStudentWHERESdept=‘IS'WITHCHECKOPTION對視圖UPDATE或INSERT時,記錄要滿足子查詢中的條件視圖中包含的字段屬性列省略，隱含同4.5視圖

例2在上例創(chuàng)建的視圖（IS_Student）的基礎(chǔ)上在派生視圖，建立信息系男學生的視圖。CREATEVIEWIS_Student_sexASSELECT*FROMIS_StudentWHERESsex='男'

例3

建立包含計算機系選修了C001號課程的學生學號，姓名，課程號，成績的視圖。CREATEVIEWV_CS_S1(學號,姓名,課程號,成績)ASSELECTStudent.Sno,Sname,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept='計算機系'ANDCno='C001'4.5視圖

例4

定義一個反映學生出生年份的視圖。CREATEVIEWV_birth(Sno,Sname,Sbirth)ASSELECTSno,Sname,2015-SageFROMStudent

例5建立一個反應(yīng)各個系人數(shù)的視圖CREATEVIEWV_sdept_countASSELECTSdept

系名,COUNT(*)AS各系人數(shù)FROMStudentGROUPBYSdept思考：建立男女學生的平均年齡視圖？4.5視圖4.5.2修改和刪除視圖定義視圖后，如果其結(jié)構(gòu)不能滿足用戶的要求，則可以對其進行修改。如果一個視圖如果不再具有使用價值，則可以將其刪除。ALTERVIEW<視圖名>[(<列名>[,<列名>]...)]AS<子查詢>[WITHCHECKOPTION]4.5視圖例7刪除上例創(chuàng)建的視圖IS_Student。DROPVIEWIS_Student

例6修改上例創(chuàng)建的V_CS_S1視圖。修改為包含計算機系選修了C001號課程并且成績大于90分的學生學號，姓名，課程號，成績的視圖。ALTERVIEWV_CS_S1(學號,姓名,課程號,成績)ASSELECTStudent.Sno,Sname,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept='計算機系'ANDCno='C001'AND在刪除基本表和視圖時要注意是否存在引用被刪除對象的視圖，如果有應(yīng)同時刪除。4.5視圖4.5.3查詢視圖例8查詢信息系學生視圖（IS_Student）當中年齡小于20歲的學生。SELECT*FROMIS_StudentWHERESage<20CREATEVIEWIS_StudentASSELECT*FROMStudentWHERESdept=‘CS'WITHCHECKOPTION修改為SELECTS#，SAFROMWHERESA<20;Sdept=‘CS'ANDstudent4.5視圖例9SELECT*FROMIS_StudentWHERESage<20修改為StudentSdept=‘IS'CREATEVIEWIS_StudentASSELECT*FROMStudentWHERESdept=‘IS'WITHCHECKOPTIONANDSELECT*FROMWHERE

Sage<20;請看下例2、注意事項當視圖中的字段對應(yīng)的是一個庫函數(shù)或字段表達式時，有些系統(tǒng)轉(zhuǎn)換后的查詢可能會不正確4.5視圖studentGROUPBYSdeptSdept,count(*)SELECTFROMWHEREcount(*)>=4例10對于一個反映各個系人數(shù)的視圖V_sdept_count，現(xiàn)在要求查詢出人數(shù)大于或等于4人的系，列出系名和人數(shù)。SELECT*FROMV_sdept_countWHERE各系人數(shù)>=4CREATEVIEWV_sdept_countASSELECTSdept系名,

COUNT(*)AS各系人數(shù)FROMStudentGROUPBYSdept修改為正確嗎

?

正確的應(yīng)為:SELECTSdept,count(*)FROMV_sdept_countGROUPBYSdeptHAVINGcount(*)>=44.5視圖例11查詢信息系的學生的基本學生信息和選課情況,未選課的學生也列出基本學生信息。SELECT*FROMIS_StudentLEFTJOINSCONIS_Student.Sno=SC.SnoSELECT*FROMStudentLEFTJOINSCONStudent.Sno=SC.SnoWHEREStudent.Sdept='‘IS”轉(zhuǎn)換4.5視圖4.5.4更新視圖數(shù)據(jù)

視圖是不實際存儲數(shù)據(jù)的虛表、因此對視圖的更新，最終要轉(zhuǎn)換為對基本表的更新。像查詢視圖那樣，對視圖的更新操作也是通過視圖消解、轉(zhuǎn)換為對基本表的更新操作。例12將前面建立的視圖IS_Student中學號為S0006的學生姓名改為劉向麗。UPDATEIS_StudentSETSname='劉向麗'WHERESno='S0006'UPDATEStudentSETSname='劉向麗'WHERESno='S0006'ANDSdept=‘IS'轉(zhuǎn)換4.5視圖例13向信息系學生視圖IS_Student插入一條新記錄，其中學號為S0012，姓名為黃俊，性別為男，年齡為21歲。INSERTINTOIS_StudentVALUES('S0012','黃俊','男',21,'信息系')INSERTINTOStudentVALUES('S0012','黃俊','男',21,'信息系')轉(zhuǎn)換例14刪除信息系學生視圖IS_Student一條記錄，其學號為S0012。DELETE

FROMIS_StudentWHERESno='S0012'DELETE

FROMStudentWHERESno='S0012'轉(zhuǎn)換4.5視圖例15將視圖V_sdept_count中的數(shù)學系的人數(shù)修改為４人。UPDATEV_sdept_countSET各系人數(shù)=4WHERE系名='數(shù)學系'不是所有的視圖都可以進行插入，修改和刪除操作，4.5視圖4.5.5視圖的作用1、能夠簡化用戶的操作2、用戶能以不同的方式對待同一數(shù)據(jù)，方便靈活3、提供一定程度的邏輯獨立性4、有利于安全保密4.5視圖第4章關(guān)系數(shù)據(jù)庫的標準語言SQL4.1SQL語言4.3數(shù)據(jù)查詢4.4數(shù)據(jù)操作語句4.6索引4.5視圖4.2數(shù)據(jù)定義

4.6索引

漢語字典中的漢字按頁存放，一般都有漢語拼音目錄（索引）、偏旁部首目錄等。我們可以根據(jù)拼音或偏旁部首，快速查找某個字詞。索引概念：數(shù)據(jù)庫中的索引是一個列表，在這個列表中包含了某個表中一列或者若干列值的集合，以及這些值的記錄在數(shù)據(jù)表中的存儲位置的物理地址。索引的作用：通過使用索引，可以大大提高數(shù)據(jù)庫和檢索速度，改善數(shù)據(jù)庫性能。

4.6索引

索引的分類：

1、聚集索引：

2、非聚集索引：

3、唯一索引：

4.6索引

聚集索引特點：1、表中的數(shù)據(jù)頁會依照該索引的順序來存放。即：表中的數(shù)據(jù)的物理存儲順序和排列順序相同，可按表中的某一字段或多個字段來排序記錄2、每個數(shù)據(jù)庫中的每個表只能有一個聚簇索引。因為一個表中的記錄只能以一種物理順序存放。3、通常情況下是對一個表按照主碼（關(guān)鍵字）建立聚簇索引。也可以用其它字段建立聚簇索引。

4.6索引

例16職員表（未建立聚簇索引之前）注意：

按‘姓名’建立一個聚簇索引，表中的記錄會自動按姓名的拼音順序進行存儲，如下圖：編號姓名性別工資20056李海龍男350020012丁亮男300020009楊秋生男500020032李秀華女4000

4.6索引

編號姓名性別工資20012丁亮男300020056李海龍男350020032李秀華女400020009楊秋生男5000基于“姓名”字段建立了聚簇索引以后：注意：現(xiàn)在向表中添加一條記錄“20006，牛東華，女，6000”，則該記