計(jì)算機(jī)數(shù)據(jù)庫(kù)課件第三章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言

第三章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL（StructuredQueryLanguage）結(jié)構(gòu)化查詢語(yǔ)言，是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言。學(xué)習(xí)建議：一定要習(xí)慣使用幫助！本課程以SQLServer2005為應(yīng)用背景。SQLServer2005的安裝和啟動(dòng)1、安裝。2、SQLServer服務(wù)的啟動(dòng)開(kāi)始→程序→MicrosoftSQLServer2005→配置工具→SQLServerConfigurationManager3、啟動(dòng)開(kāi)始→程序→MicrosoftSQLServer2005→SQLServerManagementStudio4、兩種身份驗(yàn)證：Windows身份驗(yàn)證和SQLServer身份驗(yàn)證。SQLServer2005系統(tǒng)數(shù)據(jù)庫(kù)1、master數(shù)據(jù)庫(kù)：主要記錄了SQLServer的所有系統(tǒng)信息。2、tmpdb數(shù)據(jù)庫(kù)：為臨時(shí)表和其他臨時(shí)存儲(chǔ)需求提供存儲(chǔ)空間。3、model數(shù)據(jù)庫(kù)：建立所有用戶數(shù)據(jù)庫(kù)的模板。4、msdb數(shù)據(jù)庫(kù)：數(shù)據(jù)庫(kù)在SQLServer代理程序調(diào)度報(bào)警和作業(yè)時(shí)使用。數(shù)據(jù)庫(kù)的創(chuàng)建與使用

createdatabasetest

on(name=testdata,filename='e:\lxd\td.mdf')

logon(name=testlog,filename='e:\lxd\tdlog.ldf')參數(shù)name：數(shù)據(jù)庫(kù)文件邏輯名，它必須在全部數(shù)據(jù)庫(kù)邏輯名中唯一。參數(shù)filename：存儲(chǔ)數(shù)據(jù)和日志的物理文件名及路徑。注意：數(shù)據(jù)文件后綴mdf，日志文件后綴ldf。

createdatabasenew_db

onprimary

(

name=new_db,

filename='d:\new_db.mdf',

size=5mb,

maxsize=50mb,

filegrowth=10%

)該命令自動(dòng)建立日志文件。use命令：

usenew_db使用new_db數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)的修改與維護(hù)1、修改數(shù)據(jù)庫(kù)數(shù)據(jù)文件的初始大小：

alterdatabasenew_db

modifyfile

(

name=new_db,

size=15mb

)

注意：數(shù)據(jù)庫(kù)邏輯名必須與最初數(shù)據(jù)庫(kù)定義時(shí)的邏輯名一致。2、修改數(shù)據(jù)庫(kù)名：

execsp_renamedb'new_db',

'old_db'

調(diào)用存儲(chǔ)過(guò)程，第一個(gè)參數(shù)為原數(shù)據(jù)庫(kù)名，第二個(gè)參數(shù)為新數(shù)據(jù)庫(kù)名。3、刪除數(shù)據(jù)庫(kù)：

dropdatabaseold_db4、查看系統(tǒng)中有哪些數(shù)據(jù)庫(kù)

execsp_helpdb5、查看new_db數(shù)據(jù)庫(kù)定義信息

execsp_helpdbnew_db數(shù)據(jù)庫(kù)的分離、附加與備份、還原1、分離數(shù)據(jù)庫(kù)：

execsp_detach_db

new_db2、附加數(shù)據(jù)庫(kù)：

createdatabasenew_dbon

(filename='d:\new_db.mdf'

),

(filename='d:\new_db_log.ldf'

)forattach3、備份數(shù)據(jù)庫(kù)：

backupdatabasenew_dbtodisk='e:\ll\newdb.bak'4、還原數(shù)據(jù)庫(kù)：

restoredatabasenew_dbfromdisk='e:\ll\newdb.bak'關(guān)系模式的設(shè)計(jì)屬性名SnoSnameSsexSageSdept域Char(9)Char(20)Char(2)smallintChar(20)約束PrimarykeyNotnullStudent屬性名CnoCnameCpnoCcredit域char(4)Char(20)Char(4)smallint約束PrimarykeyUniqueForeignkeyCourse屬性名SnoCnoCrade域Char(9)Char(4)smallint約束ForeignkeyForeignkeyPrimarykeySC注意：具有參照關(guān)系的屬性所對(duì)應(yīng)的域必須一致。關(guān)系模式的定義必須小心仔細(xì)！考慮數(shù)據(jù)的類型、大小；考慮數(shù)據(jù)值在應(yīng)用中的各種情況。常用數(shù)據(jù)類型常用數(shù)據(jù)類型含義char(n)長(zhǎng)度為n的定長(zhǎng)字符串varchar(n)最大長(zhǎng)度為n的變長(zhǎng)字符串bigint大整型（8字節(jié)）int整型（4字節(jié)）smallint短整型（2字節(jié)）tinyint微整型（1字節(jié)）decimal(p,s)numeric(p,s)定點(diǎn)數(shù)，由p位十進(jìn)制數(shù)位（小數(shù)位占s位）構(gòu)成的數(shù)。1<=p<=38，默認(rèn)為19；0<=s<=p，默認(rèn)為0。money8字節(jié)整數(shù)，精確到萬(wàn)分之一。smallmoney4字節(jié)整數(shù)，精確到萬(wàn)分之一。float[(n)]浮點(diǎn)數(shù)，尾數(shù)位數(shù)為n，1<=n<=53，默認(rèn)為53。real等同于float(24)datetime時(shí)間日期型。格式化為YYY-MM-DDHH:MM:SS。精確到3.33毫秒。smalldatetime同datetime，精確到1分鐘。text存儲(chǔ)大容量長(zhǎng)度可變字符串

數(shù)據(jù)類型決定了數(shù)據(jù)在計(jì)算機(jī)中的存儲(chǔ)格式、長(zhǎng)度和精度?；颈淼亩xcreatetable

Student(

Sno

char(9)

primarykey,

Sname

char(20)notnull,

Ssex

char(2),

Sage

smallint,

Sdept

char(20)

)屬性名SnoSnameSsexSageSdept域Char(9)Char(20)Char(2)smallintChar(20)約束PrimarykeyNotnullStudent問(wèn)題：該列取值可以重復(fù)嗎？查看表的基本定義信息：

execsp_helpstudent表主碼的定義?；颈淼亩x（續(xù)）createtableCourse(

Cno

char(4)

primarykey,

Cname

char(20)unique,

Cpno

char(4),

Credit

smallint,

foreignkey(Cpno)

references

Course(Cno))屬性名CnoCnameCpnoCcredit域char(4)Char(20)Char(4)smallint約束PrimarykeyUniqueForeignkeyCoursecreatetableSC(

Sno

char(9),

Cno

char(4),

Grade

smallint,

primarykey

(Sno,Cno),

foreignkey(Sno)

references

Student(Sno),

foreignkey(Cno)

references

Course(Cno))屬性名SnoCnoCrade域Char(9)Char(4)smallint約束ForeignkeyForeignkeyPrimarykeySC問(wèn)題：該列取值可以為空嗎？參照完整性的定義。表主碼的定義基本表數(shù)據(jù)的錄入SnoSnameSsexSageSdept2008001李勇男20CS2008002劉晨女19CS2008003王敏女18MA2008004張立男19ISStudentCnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27C語(yǔ)言64CourseSnoCnoCrade20080011922008001287200800214520080033SC你可以試圖輸入一些非法數(shù)據(jù)。問(wèn)題：創(chuàng)建一個(gè)無(wú)任何約束的表，錄入多個(gè)數(shù)據(jù)相同的行會(huì)怎樣？基本表的常用操作1、表重命名

execsp_rename'student',

'stu'2、列重命名

execsp_rename'student.sname',

'name',

'column'3、添加新列

altertablestudent

add

sentrance

datetime注意：新增列的數(shù)據(jù)為空。4、更改列數(shù)據(jù)類型

altertablestudentaltercolumnsageint注意：若表中已有數(shù)據(jù)，數(shù)據(jù)類型不相容時(shí)不能進(jìn)行列數(shù)據(jù)類型的更改。5、刪除列

altertablestudentdropcolumnsentrance刪除表

droptablestudent注意：若該表與其它表關(guān)聯(lián)（如外鍵的參照等），則刪除失敗。什么是索引SnoSnameSsexSageSdept2008001李勇男20CS2008002劉晨女19CS2008003王敏女18MA2008004戴旭男19ISStudentSnamePointer戴旭李勇劉晨王敏唯一索引SdeptIndexSdeptPointerCSISMA由于Sno為主碼，故在基本表記錄上自動(dòng)進(jìn)行直接物理排序（聚集索引）。由于Sname屬性有unique約束，故自動(dòng)建立一個(gè)唯一索引。屬性名SnoSnameSsexSageSdept域Char(9)Char(20)Char(2)smallintChar(20)約束PrimarykeyuniqueStudent新建的索引索引小結(jié)1、建立索引的目的是為了加快查詢的速度。2、索引分為聚集索引和非聚集索引兩大類。3、聚集索引會(huì)針對(duì)表中數(shù)據(jù)在索引項(xiàng)上直接進(jìn)行物理排序。4、若屬性有primarykey約束，則針對(duì)該主碼自動(dòng)建立聚集索引。5、一個(gè)表只能建立一個(gè)聚集索引。6、若屬性有unique約束，則針對(duì)該屬性自動(dòng)建立唯一非聚集索引。7、若表中某列已有數(shù)據(jù)，且數(shù)據(jù)中有重復(fù)值，則建立唯一索引將會(huì)失敗。8、針對(duì)某列創(chuàng)建唯一索引，可保證該列數(shù)據(jù)取值的唯一性，相當(dāng)于在該列上定義了unique約束。9、一個(gè)表中可建立多個(gè)索引，甚至是索引名不同，而內(nèi)容相同的索引。10、索引需要占用物理存儲(chǔ)空間，且該空間隨表中數(shù)據(jù)的增大而增大。11、對(duì)表中數(shù)據(jù)進(jìn)行增刪改時(shí)，增大了數(shù)據(jù)和索引的維護(hù)成本。什么情況下需要建立索引下列情況需要我們考慮是否應(yīng)該建立索引：1、是否表的數(shù)據(jù)量會(huì)很大？2、是否對(duì)表中數(shù)據(jù)的增刪改操作很少？3、是否建立索引后不會(huì)嚴(yán)重地影響系統(tǒng)效率？注意：一個(gè)表中最好不要建立很多的索引。索引的建立與刪除1、建立聚集索引

createclusteredindex

CI_Student_Sno

onstudent(sno)2、建立聚集唯一索引

createuniqueclusteredindex

CUI_Student_Sno

on

student(sno)3、建立非聚集唯一索引

createuniqueindex

UI_Student_Sno

on

student(sno)4、建立非聚集不唯一索引

createindex

idx_Student_Sno

on

student(sno)5、在多個(gè)列上建索引并指定升序或降序

createindex

idx_SC_Sno_Cnoonsc(sno

asc,cno

desc)6、索引刪除

dropindexidx_Student_Sno

onstudent索引默認(rèn)為升序數(shù)據(jù)查詢select[distinct]<目標(biāo)列表達(dá)式>[,<目標(biāo)列表達(dá)式>]…[into<新表名>]from<表名或視圖名>[,<表名或視圖名>]…[where<條件表達(dá)式>][groupby<列名>[having<條件表達(dá)式>]][orderby<列名>[asc|desc]]學(xué)習(xí)建議：通過(guò)語(yǔ)句的運(yùn)算過(guò)程來(lái)理解和掌握。選擇表中的若干列1、查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名

selectsno,snamefromstudent2、查詢?nèi)w學(xué)生的全部屬性值

select*fromstudent3、取消取值重復(fù)的記錄

selectdistinctsdeptfromstudentselectdistinctsdept,ssexfromstudent問(wèn)題：如何理解？問(wèn)題：查詢結(jié)果取消重復(fù)行嗎？屬性列可以是表達(dá)式1、查詢?nèi)w學(xué)生的姓名與出生年份

selectsname,2011-sagefromstudent2、查詢結(jié)果是什么？

selectsname,'YearofBirth',2011-sage,lower(sdept)fromstudent3、問(wèn)題：你知道SQLServer中都提供了哪些函數(shù)嗎？問(wèn)題：什么是表達(dá)式？在幫助的索引中按關(guān)鍵詞“函數(shù)”進(jìn)行查找。屬性列的別名利用別名來(lái)改變查詢結(jié)果的列標(biāo)題。如：

selectsnameNAME,2011-sageBIRTHDAYfromstudentselect'YearofBirth'asbirth,lower(sdept)asdeptfromstudentas可省略返回查詢結(jié)果的前n行數(shù)據(jù)利用topn可以得到查詢結(jié)果的前n行記錄的數(shù)。如：

selecttop2*fromstudentselecttop2sno,snamefromstudent

selectdistincttop3ssex

fromstudent注意：1、topn只能出現(xiàn)在第1個(gè)屬性名之前。2、該語(yǔ)句語(yǔ)法錯(cuò)誤：selecttop3distinctssex

fromstudent3、當(dāng)n大于結(jié)果行數(shù)時(shí)，則返回全部的查詢結(jié)果。into子句1、自動(dòng)創(chuàng)建一個(gè)新表，并將查詢結(jié)果的值存入該新表。如：

selectsno,

cno

intoxk

fromsc注意：新的xk表屬性類型來(lái)自于sc表。但并不具有碼、參照完整性等其它約束的定義。2、將查詢結(jié)果存入到一個(gè)新的臨時(shí)表。

selectcname,

ccredit

into#kc

fromcourse注意：1、可以對(duì)#kc

表進(jìn)行增刪改查詢操作。如：select*from#kc2、該臨時(shí)表將在會(huì)話期結(jié)束后被刪除。該表存入系統(tǒng)的tempdb數(shù)據(jù)庫(kù)中。完成下列查詢要求1、求我校都哪些系招收學(xué)生了？2、求哪些學(xué)生選修了課程？求出他（她）們的學(xué)號(hào)。3、求哪些課程被學(xué)生選修了？求出它們的課程名。4、求我校所開(kāi)設(shè)的全部課程的學(xué)分總和是多少？參考答案1、求我校都哪些系招收學(xué)生了？

selectdistinctsdeptfromstudentwheresdeptisnotnull2、求哪些學(xué)生選修了課程？求出他（她）們的學(xué)號(hào)。

selectdistinctsnofromsc3、求哪些課程被學(xué)生選修了？求出它們的課程號(hào)。

selectdistinctcnofromSC4、求我校所開(kāi)設(shè)的全部課程的學(xué)分總和是多少？

selectsum(credit)fromcourse常用的查詢條件查

詢

條

件謂

詞比

較=，>，<，>=，<=，!=，<>，!>，!<確定范圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空

值ISNULL，ISNOTNULL邏輯運(yùn)算AND，OR，NOT查詢滿足條件的元組1、查詢CS系全體學(xué)生的姓名。

selectsnamefromstudentwheresdept='CS'2、查詢所有年齡在20歲以下的學(xué)生姓名及年齡。

selectsname,sagefromstudentwheresage<203、查詢不是CS系的學(xué)生姓名。

selectsnamefromstudentwheresdept<>'CS'問(wèn)題：若表中的數(shù)據(jù)值為小寫(xiě)的cs呢？（字符串內(nèi)的字符不區(qū)分大小寫(xiě)）含不含20？（自然語(yǔ)言的不精確性）問(wèn)題：若sdept屬性數(shù)據(jù)含有空值呢？結(jié)論：空值不參與比較運(yùn)算。謂詞and、or、not1、查詢CS系年齡在20歲以下的學(xué)生的姓名。

selectsnamefromstudentwheresdept='cs'andsage<202、查詢CS系和IS系全體學(xué)生的姓名及所在系。

selectsname,sdeptfromstudentwheresdept='cs'orsdept='is'

3、查詢不是CS系學(xué)生的姓名。

selectsnamefromstudentwheresdept<>'cs'

問(wèn)題：你能理解嗎？問(wèn)題：它的等價(jià)形式是什么？謂詞between…and…1、查詢年齡在20~23歲之間的學(xué)生的姓名及年齡。

selectsname,sagefromstudentwheresagebetween20and232、查詢年齡不在20~23歲之間的學(xué)生的姓名及年齡。

selectsname,sagefromstudentwheresagenotbetween20and233、問(wèn)題：你能利用其它運(yùn)算寫(xiě)出上述兩條查詢的等價(jià)形式嗎？注意：包括20和23。謂詞in1、查詢CS系、MA系和IS系全體學(xué)生的姓名和性別。

selectsname,ssexfromstudentwheresdeptin('cs','ma','is')2、查詢既不是CS系、MA系，也不是IS系的學(xué)生姓名。

selectsname,ssexfromstudentwheresdeptnotin('cs','ma','is')3、問(wèn)題：你能用其它運(yùn)算寫(xiě)出上述兩條查詢的等價(jià)形式嗎？謂詞like1、查詢所有不姓劉的學(xué)生姓名。

selectsnamefromstudentwheresnamenotlike'劉%'2、查詢所有姓劉的但姓名只有兩個(gè)字的學(xué)生姓名。

selectsnamefromstudentwheresnamelike'劉_'運(yùn)算符含義%與任意長(zhǎng)度的字符串匹配。（長(zhǎng)度可為0）_與長(zhǎng)度為0或1的單個(gè)字符匹配。（一個(gè)漢字當(dāng)作單字符）[]與指定范圍內(nèi)的單個(gè)字符匹配。如[張王李][^]與不屬于指定范圍內(nèi)的單個(gè)字符匹配。如[^張王李]謂詞like（續(xù)）3、查詢所有姓劉、姓李和姓張的學(xué)生姓名。

selectsnamefromstudentwheresnamelike'[劉李張]%'4、查詢不姓李、不姓張，但名叫海燕的學(xué)生姓名。

selectsnamefromstudentwheresnamelike'[^李張]海燕'5、若查詢字符串中本身含有通配符，則需要進(jìn)行轉(zhuǎn)義。如：

selectsnamefromstudentwheresnamelike'劉\_%'escape'\'6、注意：含通配符時(shí)的like不可以用=代替。\為轉(zhuǎn)義（換碼）字符。它只限定緊跟在它后面的通配符，習(xí)慣上人們通常使用\字符作為轉(zhuǎn)義符。涉及空值的查詢1、查詢所有缺考的學(xué)生學(xué)號(hào)及相應(yīng)的課程號(hào)。

selectsno,cnofromscwheregradeisnull2、查詢所有有成績(jī)的學(xué)生學(xué)號(hào)及相應(yīng)的課程號(hào)。

selectsno,cnofromscwheregradeisnotnull注意：is不能用=代替。完成下列查詢要求1、求哪些學(xué)生還沒(méi)有被分配系?2、求CS系中所有男同學(xué)的學(xué)生姓名和年齡。3、我校開(kāi)設(shè)的課程中哪些課程名以“數(shù)據(jù)”兩個(gè)字開(kāi)頭？4、求哪些學(xué)生的姓名中第2個(gè)字是“立”？5、求哪些學(xué)生的成績(jī)?yōu)閮?yōu)秀，求出該學(xué)生的學(xué)號(hào)及相應(yīng)的課程號(hào)。6、求既不是CS系，也不是MA系的學(xué)生中年齡不小于20的學(xué)生姓名。參考答案1、求哪些學(xué)生還沒(méi)有被分配系?

select*fromstudentwheresdeptisnull2、求CS系中所有男同學(xué)的學(xué)生姓名和年齡。

selectsname,sagefromstudentwheresdept='cs'andssex='男'3、我校開(kāi)設(shè)的課程中哪些課程名以“數(shù)據(jù)”兩個(gè)字開(kāi)頭？

selectcnamefromcoursewherecnamelike'數(shù)據(jù)%'4、求哪些學(xué)生的姓名中第2個(gè)字是“立”？

select*fromstudentwheresnamelike'_立%'5、求哪些學(xué)生的成績(jī)?yōu)閮?yōu)秀，求出該學(xué)生的學(xué)號(hào)及相應(yīng)的課程號(hào)。

selectsno,cnofromscwheregrade>=906、求既不是CS系，也不是MA系的學(xué)生中年齡不小于20的學(xué)生姓名。

selectsnamefromstudentwheresdeptnotin('cs','ma')andsage>=20

selectsnamefromstudentwhere(sdept<>'cs'andsdept<>'ma')and

sage>=

20orderby子句1、查詢所有學(xué)生的姓名和年齡，要求按年齡由小到大排序。

selectsname,sagefromstudentorderbysageasc2、查詢CS系的全體學(xué)生，要求按年齡升序排列，年齡相同的按姓名的降序排列。

select*fromstudentwheresdept='cs'orderbysage,sname

desc3、問(wèn)題：若排序?qū)傩粤猩嫌锌罩担瑒t如何排序呢?

（空值當(dāng)成最小值。）注意：asc

可以省略，即默認(rèn)為升序。topnwithties子句在有orderby子句時(shí)，可以使用topnwithties子句。例如：selecttop1withties*fromstudentorderbysage該語(yǔ)句的功能是查詢出年齡最小的學(xué)生的全部屬性數(shù)據(jù)。注意：若使用topnwithties時(shí)，則返回結(jié)果不一定只有n行記錄，而是和排序后的第n行記錄的排序列取值相同的所有行都會(huì)被查詢出來(lái)。又如：查詢學(xué)生中成績(jī)最高的學(xué)生學(xué)號(hào)。selecttop1withtiessnofromscorderbygrade

desc聚集函數(shù)注意：1、沒(méi)有分組時(shí)，函數(shù)是在整個(gè)查詢結(jié)果的基礎(chǔ)上進(jìn)行計(jì)算。2、<列名>只能是一個(gè)列名。3、應(yīng)用avg函數(shù)時(shí)，若列為整型類型，則計(jì)算結(jié)果也為整型，并進(jìn)行下取整。4、除count(*)函數(shù)外，其它聚集函數(shù)都跳過(guò)空值而只處理非空值。聚集函數(shù)含義count([distinct]*)統(tǒng)計(jì)元組個(gè)數(shù)count([distinct]<列名>)統(tǒng)計(jì)一列中值的個(gè)數(shù)sum([distinct]<列名>)計(jì)算一列值的總和（此列必須是數(shù)值型）avg([distinct]<列名>)計(jì)算一列的平均值（此列必須是數(shù)值型）max([distinct]<列名>)求一列值中的最大值min([distinct]<列名>)求一列值中的最小值聚集函數(shù)的使用1、查詢學(xué)生總?cè)藬?shù)。

selectcount(*)fromstudent2、查詢選修了課程的學(xué)生總?cè)藬?shù)。

selectcount(distinct

sno)fromsc3、計(jì)算選修1號(hào)課程的學(xué)生平均成績(jī)。

selectavg(grade)fromscwherecno='1'分組1、什么叫分組？

將某個(gè)表或查詢結(jié)果按某一列或多列的值分組，值相等的為一組。2、例如：將sc表按sno屬性分組。SnoCnoCrade20080011922008001287200800214520080033200800119220080012872008002145200800333、再如：將student表按ssex屬性分組SnoSnameSsexSageSdept2008001李勇男20CS2008002劉晨女19MA2008003王敏女18MA2008004張立19IS2008001李勇男20CS2008002劉晨女19MA2008003王敏女18MA2008004張立19ISgroupby子句問(wèn)題：結(jié)果的值是什么意思？SnoSnameSsexSageSdept2008001李勇男20CS2008002劉晨女19MA2008003王敏女18MA2008004張立19IS2008001李勇男20CS2008002劉晨女19MA2008003王敏女18MA2008004張立19IS

selectssexfromstudentgroupbyssexselectsdeptfromstudentgroupbyssex

selectcount(*)

fromstudent

groupbyssexSsex男女分析下列語(yǔ)句的執(zhí)行結(jié)果。語(yǔ)法錯(cuò)誤，說(shuō)明什么？121問(wèn)題：它的等價(jià)查詢是什么？groupby子句（續(xù)）切記：分組后聚集函數(shù)將作用于每一個(gè)分組，即每一個(gè)組都有一個(gè)函數(shù)值。SnoSnameSsexSageSdept2008001李勇男20CS2008002劉晨女19MA2008003王敏女18MA2008004張立NULL19NULL2008001李勇男20CS2008002劉晨女19MA2008003王敏女18MA2008004張立NULL19NULL

selectssex,count(*)fromstudentgroupbyssexselectssex,count(sdept)fromstudentgroupbyssex

selectssex,count(distinctsdept)

fromstudent

groupbyssexSsex男1女2NULL1分析下列語(yǔ)句的執(zhí)行結(jié)果。Ssex男1女1NULL0Ssex男1女2NULL0groupby子句示例1、求每門課程號(hào)及相應(yīng)的選課人數(shù)。

selectcno

課程號(hào),count(*)選課人數(shù)

fromscgroupbycno2、求每門課程的課程號(hào)、選課人數(shù)及該課的平均成績(jī)。

selectcno

課程號(hào),

count(*)

總?cè)藬?shù),

avg(grade)

平均分

fromsc

groupbycno3、求每個(gè)學(xué)生的學(xué)號(hào)及其平均成績(jī)。

selectsno

學(xué)號(hào),avg(grade)平均分

fromscgroupbysno4、問(wèn)題：如何查詢平均成績(jī)及格的學(xué)生學(xué)號(hào)及其平均成績(jī)？having短語(yǔ)1、求平均成績(jī)及格的學(xué)生學(xué)號(hào)及其平均成績(jī)。

selectsno,

avg(grade)

fromsc

groupbysno

havingavg(grade)

>=

602、求選修了3門以上課程的學(xué)生學(xué)號(hào)。

selectsnofromscgroupbysnohavingcount(*)>33、問(wèn)題：having短語(yǔ)與where子句有什么區(qū)別嗎？（作用對(duì)象不同：前者作用于組，后者作用于表或視圖。）SnoCnoCrade20080011902008001287200800215020080033SC分組查詢舉例1、求CS系中男女學(xué)生的數(shù)量分別是多少？

selectssex,

count(*)

fromstudent

wheresdept

=

'cs'

groupbyssex2、求各系中每個(gè)年齡段的學(xué)生總?cè)藬?shù)，要求結(jié)果中對(duì)系進(jìn)行排序，同一個(gè)系的按年齡排序。即想得到右表所示的結(jié)果。

selectsdept,

sage,

count(*)

snum

fromstudent

groupbysdept,

sage

orderbysdept,

sagesdeptsagesnumcs201is192ma181連接查詢1、下列查詢得到什么結(jié)果?

select*selectstudent.*,sc.*

fromstudent,scfromstudent,sc2、笛卡爾積也稱為交叉連接。

select*

fromstudentcrossjoinsc3、問(wèn)題：連接運(yùn)算是什么樣的運(yùn)算了？例如：求選修了1號(hào)課程的學(xué)生姓名。關(guān)系代數(shù)：πsname(σcno='1'(studentsc))

等價(jià)于：πsname(σcno='1‘∧student.sno=sc.sno(student×sc))4、問(wèn)題：你還記得它的運(yùn)算過(guò)程和結(jié)果嗎？三者等價(jià)連接查詢的執(zhí)行過(guò)程SnoSnameSsexSageSdept2008001李勇男20CS2008002劉晨女19CS2008003王敏女18MA2008004張立男19ISStudentSnoCnoCrade20080011922008001287200800214520080033SCStudent.SnoSnameSsexSageSdeptSC.SnoCnoGrade2008001李勇男20CS20080011922008001李勇男20CS20080012872008002劉晨女19CS20080021452008003王敏女18MA20080033Student和SC關(guān)系在Student.Sno與SC.Sno兩屬性上做等值連接Sname李勇劉晨按Cno=1選擇再投影連接查詢示例1、求選修了1號(hào)課程的學(xué)生姓名。

selectsnamefromstudent,scwherestudent.sno=sc.snoandcno='1'2、求選修了2號(hào)課程且成績(jī)?yōu)閮?yōu)秀的所有學(xué)生學(xué)號(hào)和姓名。

selectstudent.sno,snamefromstudent,scwherestudent.sno=sc.snoandcno='2'andgrade>=903、求選修了DB課程的學(xué)生學(xué)號(hào)和姓名。（即打印課程的學(xué)生名單）

selectstudent.sno,snamefromstudent,sc,coursewherestudent.sno=sc.snoando=oandcname='DB'這個(gè)問(wèn)題如何求解？求每一門課程直接先行課的直接先行課。CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27C語(yǔ)言64CourseCnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27C語(yǔ)言64Course自身連接求每一門課程直接先行課的直接先行課。CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27C語(yǔ)言64Course起別名叫firstCnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27C語(yǔ)言64Course起別名叫secondselecto,second.cpnofromcoursefirst,coursesecondwherefirst.cpno=o問(wèn)題：它的執(zhí)行結(jié)果是什么？自身連接（續(xù)）CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27C語(yǔ)言64Courseselecto,second.cpnofromcoursefirst,coursesecondwherefirst.cpno=o執(zhí)行結(jié)果CnoCpno17354NULL567NULL問(wèn)題：若想得到右表所示的結(jié)果該咋辦呢？CnoCpno173556自身連接（續(xù)）CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27C語(yǔ)言64Courseselecto,second.cpnofromcoursefirst,coursesecondwherefirst.cpno=oandsecond.cpnoisnotnull執(zhí)行結(jié)果問(wèn)題：若又想得到右表所示的結(jié)果又該咋辦呢？（你還記得外連接嗎？）CnoCpno173556CnoCpno172NULL354NULL566NULL7NULL外連接CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27C語(yǔ)言64Courseselectfirst.cno,second.cpnofromcoursefirstleftjoincoursesecondon

(first.cpno

=second.cno)表自身的左外連接。執(zhí)行結(jié)果CnoCpno172NULL354NULL566NULL7NULL1、左外連接

select*fromstudentleftouterjoinscon

(student.sno

=

sc.sno)左外連接可省略連接條件外連接（續(xù)）1、右外連接

select*

fromstudentrightouterjoinscon

(student.sno

=

sc.sno)2、外連接

select*

fromstudentfullouterjoinscon

(student.sno

=

sc.sno)3、注意：外連接不滿足交換律。4、求IS系全體學(xué)生的選課情況。

select*

fromstudentleftjoinscon

(student.sno

=

sc.sno)

wheresdept

=

'IS'

問(wèn)題：如果不用左外連接會(huì)出現(xiàn)什么結(jié)果？注意：where子句的條件不能寫(xiě)在外連接條件處。關(guān)于外連接的格式連接：

select*

fromstudent,scwherestudent.sno

=

sc.sno它還有一種等價(jià)形式：

select*

fromstudentjoinscon

(student.sno

=

sc.sno)外連接：

select*

fromstudentleftjoinscon

(student.sno

=

sc.sno)

select*fromstudentrightjoinscon

(student.sno

=

sc.sno)

select*fromstudentfulljoinscon

(student.sno

=

sc.sno)這種形式現(xiàn)在很少使用select*

fromstudent,sc

wherestudent.sno*=sc.snoEXECsp_dbcmptlevel'student','80'完成下列查詢要求1、打印李勇的成績(jī)單（即求李勇所選修的課程名及其成績(jī)）。2、求不及格和缺考的學(xué)生所在系、學(xué)號(hào)、姓名及相應(yīng)課程名，要求按系排序，同一個(gè)系的按學(xué)號(hào)排序。3、求CS系不及格和缺考的學(xué)生學(xué)號(hào)、姓名及相應(yīng)課程名，要求按學(xué)號(hào)排序。4、求既不是CS系，也不是MA系缺考學(xué)生的學(xué)號(hào)、姓名及相應(yīng)課程名。5、求選修BD課程的學(xué)生平均成績(jī)。6、求每一門課程的學(xué)生平均成績(jī)，要求輸出課程名及對(duì)應(yīng)的平均成績(jī)，并按平均成績(jī)由大到小排序。7、求李勇所選修的總學(xué)分（即成績(jī)及格的課程學(xué)分總和）。參考答案1、打印李勇的成績(jī)單（即求李勇所選修的課程名及其成績(jī)）。

selectcname,grade

fromstudent,course,sc

wherestudent.sno=sc.snoand

o=oandsname='李勇'2、求不及格和缺考的學(xué)生所在系、學(xué)號(hào)、姓名及相應(yīng)課程名，要求按系升序排序，同一個(gè)系的按學(xué)號(hào)升序排序。

selectsdept,student.sno,sname,cname

fromstudent,course,sc

wherestudent.sno=sc.snoando=oand(grade<60orgradeisnull)

orderbysdept,student.sno3、求CS系不及格和缺考的學(xué)生學(xué)號(hào)、姓名及相應(yīng)課程名，要求按學(xué)號(hào)排序。

selectstudent.sno,sname,cname

fromstudent,course,sc

wherestudent.sno=sc.snoando=oand(grade<60orgradeisnull)

andsdept='cs'

orderbystudent.sno參考答案（續(xù)）4、求既不是CS系，也不是MA系缺考學(xué)生的學(xué)號(hào)、姓名及相應(yīng)課程名。

selectstudent.sno,sname,cname

fromstudent,course,sc

wherestudent.sno=sc.snoando=oandgradeisnull

andsdeptnotin('cs','ma')5、求選修BD課程的學(xué)生平均成績(jī)。

selectavg(grade)

fromcourse,sc

whereo=oandcname='DB'6、求每一門課程的學(xué)生平均成績(jī)，要求輸出課程名及對(duì)應(yīng)的平均成績(jī)，并按平均成績(jī)由大到小排序。

selectcname,avg(grade)agrade

fromcourse,sc

whereo=o

groupbycname

orderbyagradedesc7、求李勇所選修課程得到的總學(xué)分（即成績(jī)及格的課程學(xué)分總和）。

selectsum(credit)

fromstudent,course,sc

wherestudent.sno=sc.snoando=oandsname='李勇'andgrade>=60嵌套查詢求選修了1號(hào)課程的學(xué)生姓名。

selectsname

fromstudent,sc

wherestudent.sno=sc.snoandcno='1'

換個(gè)思路：

selectsname

fromstudent

wheresnoin

(selectsno

fromsc

wherecno='1')特點(diǎn)：內(nèi)層子查詢條件中所涉及的屬性與外層無(wú)關(guān)。將這樣的子查詢稱為不相關(guān)子查詢。問(wèn)題1：你知道該語(yǔ)句是怎么執(zhí)行的嗎？問(wèn)題2：哪種表達(dá)方式好？問(wèn)題3：你還有別的辦法嗎？嵌套查詢（續(xù)）1、問(wèn)題：下列語(yǔ)句的執(zhí)行結(jié)果是什么？

select*

fromsc

wherecno='1'

2、問(wèn)題：將student與tmpT做自然連接后再做sname的投影得到什么？3、查詢表達(dá)如下：

selectsname

fromstudent,(select*fromscwherecno='1')

tmpT

wherestudent.sno=tmpT.sno4、問(wèn)題：下列語(yǔ)句的執(zhí)行結(jié)果是什么？

selectsname,tmpT.grade

fromstudent,(select*fromscwherecno='1')

tmpT

wherestudent.sno=tmpT.sno這也是一種嵌套：嵌套表SnoCnoCrade20080011922008002145給結(jié)果關(guān)系起個(gè)名，叫tmpT嵌套查詢（續(xù)）1、求沒(méi)選修1號(hào)課程的學(xué)生姓名。

selectsname

fromstudent,sc

wherestudent.sno=sc.snoandcno<>

'1'

問(wèn)題：該語(yǔ)句的執(zhí)行結(jié)果是什么？它對(duì)嗎？2、下列語(yǔ)句的執(zhí)行結(jié)果是什么？

selectsname

fromstudent

wheresnonotin

(selectsno

fromsc

wherecno='1')3、問(wèn)題：求不是CS系的學(xué)生姓名，下面的語(yǔ)句對(duì)嗎？

selectsname

fromstudent

wheresdept<>'cs'orsdeptisnull4、你能理解這是為什么嗎？嵌套查詢舉例求與李勇在同一個(gè)系的學(xué)生姓名。1、selectsname

fromstudent

wheresdeptin

(

selectsdept

fromstudent

wheresname

=

'李勇'

)2、selectsname

fromstudent,

(selectsdeptfromstudentwheresname='李勇')tmpT

wherestudent.sdept

=

tmpT.sdept3、selectfirst.sname

fromstudentfirst,

studentsecond

wherefirst.sdept

=

second.sdept

and

second.sname

='李勇'

問(wèn)題：求選修D(zhuǎn)B課程的學(xué)生學(xué)號(hào)和姓名。你能寫(xiě)出三種不同查詢語(yǔ)句嗎？注意：若能確定子查詢的結(jié)果一定返回的是單值，則可用比較運(yùn)算符代替in。相關(guān)子查詢下面的語(yǔ)句是如何執(zhí)行的？執(zhí)行結(jié)果是什么？

selectsno,

cno

fromscx

wheregrade>=

(

selectavg(grade)

fromsc

wheresno

=

x.sno)SnoCnoCrade20080011922008001287200800214520080033SCx取出第1個(gè)元組的值，并將該值傳遞到內(nèi)層。執(zhí)行如下的語(yǔ)句：

selectsno,

cno

fromscx

where92>=

(

selectavg(grade)

fromsc

wheresno

=2008001)取出第k個(gè)元組的值重復(fù)進(jìn)行再取出第2個(gè)元組的值，并將該值傳遞到內(nèi)層。執(zhí)行如下的語(yǔ)句：

selectsno,

cno

fromscx

where87>=

(

selectavg(grade)

fromsc

wheresno

=2008001)……直到x中的全部元組處理完畢相關(guān)子查詢：即子查詢條件中涉及到了外層關(guān)系的屬性。不相關(guān)子查詢與相關(guān)子查詢比較不相關(guān)子查詢：子查詢的查詢條件不依賴于父查詢由里向外逐層處理。即每個(gè)子查詢?cè)谏弦患?jí)查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。相關(guān)子查詢：子查詢的查詢條件依賴于父查詢首先取外層查詢中表的第一個(gè)元組，利用該元組值執(zhí)行內(nèi)層查詢，若此時(shí)子查詢結(jié)果使得外層的WHERE子句表達(dá)式為真，則該元組的值放入結(jié)果表；然后再取外層表的下一個(gè)元組值，重復(fù)上述過(guò)程，直至外層表全部元組處理完畢為止。子查詢內(nèi)不可以使用orderby子句。相關(guān)子查詢舉例求選修1號(hào)課程的學(xué)生姓名。想法：①

在student關(guān)系中取出一個(gè)元組。如：第1個(gè)元組；②利用該元組的Sno屬性值2008001到SC關(guān)系找有沒(méi)有Sno為該值且Cno為1的元組，若在SC關(guān)系中查得有這樣的元組，則說(shuō)明在①中取出的元組符合要求，將其對(duì)應(yīng)的Sname屬性值輸出；③

重復(fù)上述過(guò)程，直到Student關(guān)系的全部元組依次處理完畢。

selectsname

fromstudent

wheresnoin

(

selectsno

fromsc

wherestudent.sno

=

snoandcno='1')SnoSnameSsexSageSdept2008001李勇男20CS2008002劉晨女19CS2008003王敏女18MA2008004張立男19ISStudentSnoCnoCrade20080011922008001287200800214520080033SC注意：這里其實(shí)僅關(guān)心子查詢的結(jié)果有還是沒(méi)有元組。exists謂詞exists謂詞代表存在量詞，只產(chǎn)生“真”或“假”邏輯值?？捎糜谂卸ㄗ硬樵兊慕Y(jié)果是否為空。1、當(dāng)子查詢的結(jié)果非空，該謂詞返回“真”2、當(dāng)子查詢的結(jié)果為空，該謂詞返回“假”上例的語(yǔ)句可描述如下：

selectsname

fromstudent

whereexists

(

select*

fromsc

wherestudent.sno

=

snoandcno='1')問(wèn)題1：你知道該語(yǔ)句是如何執(zhí)行的嗎？問(wèn)題2：求沒(méi)選修1號(hào)課程的學(xué)生姓名，如何表達(dá)？exists謂詞（續(xù)）求沒(méi)選修1號(hào)課程的學(xué)生姓名。

selectsname

fromstudent

wherenotexists

(

select*

fromsc

wherestudent.sno

=

snoandcno='1')問(wèn)題：你能理解嗎？any、all謂詞1、謂詞含義①any：某個(gè)（值）②all：所有（值）2、例如：

>any大于子查詢結(jié)果中的某個(gè)值

>all大于子查詢結(jié)果中的所有值（這是什么意思呢？）3、等價(jià)轉(zhuǎn)換空白處通常沒(méi)有實(shí)際意義。=<><<=>>=anyin<max<=max>min>=minallnotin<min<=min>max>=maxany(some)、all謂詞舉例查詢其他系中比CS系某一學(xué)生年齡小的學(xué)生姓名和年齡。selectsname,sagefromstudentwheresage<any

(

selectsage

fromstudent

wheresdept='CS')

andsdept<>'CS'selectsname,sagefromstudentwheresage<(selectmax(sage)

fromstudent

wheresdept='CS')

andsdept<>'CS'等價(jià)union、interset、except子句并操作：select*fromstudentwheresdept='cs'unionselect*fromstudentwheresdept='ma'交操作：selectsname,sagefromstudentwheresage>20intersectselectsname,sagefromstudentwheresdept='cs'差操作：select*fromstudentexceptselect*fromstudentwheresdept='cs'問(wèn)題：它們的執(zhí)行結(jié)果是什么？注意：1、需要滿足集合運(yùn)算的條件。

2、并操作結(jié)果會(huì)取消重復(fù)的元組。插入數(shù)據(jù)insert語(yǔ)句的格式：

insertinto<表名>[<屬性列1>[，<屬性列2>…]]values(<常量1>[，<常量2>]…)insertintostudent(sno,

sname,

sage)values('5',

'陳冬',

18)insertintostudentvalues('6',

'張飛',

'男',

23,

'IS')insertintosc(sno,cno,grade)values('5','2',null)例如：值序列必須與屬性序列對(duì)應(yīng)一致。其它屬性賦值為空。值序列必須與表的列定義順序一致。但這并不是一個(gè)好習(xí)慣。此處需要明確給出空值插入數(shù)據(jù)（續(xù)）注意：只有符合數(shù)據(jù)庫(kù)完整性要求的數(shù)據(jù)才能被成功插入到表中。指出下列語(yǔ)句的錯(cuò)誤。insertintostudent(sname,

sage)values('陳冬',

18)insertintostudent(sno,ssex,sname,sage,sdept)values('6',

'張飛',

'男',

'23',

'IS')insertintoscvalues('5','ab',-98)SQLServer可自動(dòng)將數(shù)串轉(zhuǎn)換成數(shù)。但這不是好習(xí)慣。插入子查詢結(jié)果第一步：建表

createtabledept_age(sdeptchar(20)primarykey,

avg_age

smallint)第二步：插入數(shù)據(jù)

insertintodept_age(sdept,avg_age)selectsdept,avg(sage)fromstudentgroupbysdept對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫(kù)。注意屬性列的數(shù)據(jù)類型問(wèn)題。select

sdept,

avg(sage)avg_ageintodept_agefromstudentgroupbysdept這兩者有什么區(qū)別嗎？修改數(shù)據(jù)update語(yǔ)句格式：

update<表名>set<列名>=<表達(dá)式>[，<列名>=<表達(dá)式>]…[where<條件>]修改指定表中滿足where子句條件的元組的指定列值。注意：修改后的數(shù)據(jù)只有滿足完整性要求時(shí)才能修改成功。1、將1號(hào)學(xué)生的年齡改為22。

updatestudent

setsage=22

wheresno='1'2、下面語(yǔ)句執(zhí)行是結(jié)果是什么？

updatestudent

setsage=22修改數(shù)據(jù)（續(xù)）3、將全體學(xué)生的年齡增加1歲。

updatestudent

setsage=sage+14、將DB課程的先行課置空，學(xué)分改為6。

updatecourse

setcpno=null,credit=6

wherecname='DB'5、將CS系全體學(xué)生的成績(jī)置零。

updatesc

setgrade=0

where'cs'=

(

selectsdept

fromstudent

wheresno=sc.sno

)問(wèn)題：你還能寫(xiě)出什么形式嗎？修改數(shù)據(jù)（續(xù)）將CS系全體學(xué)生的成績(jī)置零。

updatesc

setgrade=0

where'cs'=

(

selectsdept

fromstudent

wheresno=sc.sno

)updatescsetgrade=0whereexists

(select*

fromstudent

wheresno=sc.snoandsdept='cs')updatescsetgrade=0wheresnoin

(selectsno

fromstudent

wheresdept='cs')修改數(shù)據(jù)（續(xù)）updatescsetgrade=0whereexists

(

selectsno

fromstudent,

course

wheresno

=

sc.snoandcno=oandsdept='cs'andcname='db')7、將CS系選修D(zhuǎn)B課程的學(xué)生成績(jī)置零。刪除數(shù)據(jù)delete語(yǔ)句格式：

deletefrom<表名>[where<條件>]刪除指定表中滿足where子句條件的元組。注意：元組刪除后仍能滿足完整性要求才能刪除成功。1、將