第5章SQLServer圖形操作及SQL語言

第5章SQLServer圖形操作及SQL語言04二月2023第2頁第5章SQLServer圖形操作及SQL語言5.1SQLServer的圖形界面5.2SQL語言5.3T-SQL編程04二月2023第3頁5.1SQLServer的圖形界面5.1.1連接SQLServer200804二月2023第4頁5.1SQLServer的圖形界面5.1.2數(shù)據(jù)庫的創(chuàng)建和刪除04二月2023第5頁5.1SQLServer的圖形界面5.1.3表的創(chuàng)建、修改和刪除1．創(chuàng)建數(shù)據(jù)表2．修改表結(jié)構(gòu)3．刪除表

04二月2023第6頁5.1SQLServer的圖形界面5.1.4建立表間的關(guān)聯(lián)（1）外鍵的創(chuàng)建（2）增添表間關(guān)系（3）刪除表間關(guān)系（刪除外鍵）04二月2023第7頁5.1SQLServer的圖形界面5.1.5增添數(shù)據(jù)和查詢（1）直接向數(shù)據(jù)表中添加數(shù)據(jù)（以“學生表”為例）（2）新建查詢04二月2023第8頁5.1SQLServer的圖形界面5.1.6CHECK約束04二月2023第9頁5.1SQLServer的圖形界面5.1.7存儲過程的使用04二月2023第10頁5.1SQLServer的圖形界面5.1.8視圖的使用04二月2023第11頁5.1SQLServer的圖形界面5.1.9觸發(fā)器的使用04二月2023第12頁5.1SQLServer的圖形界面5.1.10賬號及權(quán)限管理1．Windows登錄賬號的建立與刪除（1）建立和管理用戶賬號（2）混合認證模式下SQLServer04二月2023第13頁5.1SQLServer的圖形界面5.1.11分離和附加數(shù)據(jù)庫04二月2023第14頁5.1SQLServer的圖形界面5.1.12數(shù)據(jù)庫備份和還原04二月2023第15頁5.1SQLServer的圖形界面5.1.13DTS導入導出向?qū)?.2SQL語言SQL功能命令（動詞）數(shù)據(jù)定義DDLcreate、alter、drop數(shù)據(jù)操縱DMLinsert、update、delete數(shù)據(jù)查詢DQLselect數(shù)據(jù)控制DCLgrant、revoke、deny04二月2023星期五第16頁5.2.1DDL數(shù)據(jù)庫管理5.2.2DDL表格管理5.2.3DML數(shù)據(jù)管理5.2.4DQL數(shù)據(jù)查詢5.2.5DCL數(shù)據(jù)控制04二月2023第17頁5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理1．創(chuàng)建數(shù)據(jù)庫例5-1

創(chuàng)建簡單數(shù)據(jù)庫CREATEDATABASEProducts文

件

類

型文件擴展名主要數(shù)據(jù)文件.mdf次要數(shù)據(jù)文件.ndf事務日志文件.ldf04二月2023第18頁5.2SQL語言例5-2

創(chuàng)建“教學管理”數(shù)據(jù)庫：數(shù)據(jù)庫名稱為“教學管理”，數(shù)據(jù)庫文件初始大小為5MB，增長率為15%，文件名稱為“教學管理_DATA.mdf”，日志文件名稱為“教學管理_LOG.ldfCREATEDATABASE教學管理ONPRIMARY--默認就屬于PRIMARY主文件組，可省略(NAME='教學管理_DATA',--主數(shù)據(jù)文件的邏輯名

FILENAME='D:\教學管理_data.mdf',--主數(shù)據(jù)文件的物理名

SIZE=5MB,--主數(shù)據(jù)文件初始大小

MAXSIZE=100MB,--主數(shù)據(jù)文件增長的最大值

FILEGROWTH=15%--主數(shù)據(jù)文件的增長率)LOGON(NAME='教學管理_log',FILENAME='D:\教學管理_LOG.ldf',SIZE=2MB,FILEGROWTH=1MB)04二月2023第19頁5.2SQL語言例5-3

使用文件組、多個數(shù)據(jù)文件和事務日志文件創(chuàng)建數(shù)據(jù)庫。CREATEDATABASE教務管理ONPRIMARY(NAME=學籍管理,FILENAME='C:\學籍管理.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),(NAME=境內(nèi)生學籍管理,FILENAME='C:\境內(nèi)生學籍管理.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),(NAME=境外生學籍管理,FILENAME='C:\境外生學籍管理.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),FILEGROUP宿舍管理組1(NAME=境內(nèi)生宿舍管理,FILENAME='D:\境內(nèi)生宿舍管理.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=境外生宿舍管理,FILENAME='D:\境外生宿舍管理.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUP選課系統(tǒng)(NAME=選課系統(tǒng),FILENAME='E:\選課系統(tǒng).ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME='教務管理_log',FILENAME='C:\教務管理_log.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)GO04二月2023第20頁5.2SQL語言例5-4

創(chuàng)建一個包含下列物理文件的名為“product”的數(shù)據(jù)庫C:\product1.mdfC:\product2.ndfC:\product3.ndfC:\productlog1.ldfC:\productlog2.ldf04二月2023第21頁5.2SQL語言可以使用sp_detach_db存儲過程分離該數(shù)據(jù)庫，然后使用帶有FORATTACH子句的CREATEDATABASE重新附加。CREATEDATABASEproductONPRIMARY(NAME=product1,FILENAME='C:\product1.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),(NAME=product2,FILENAME='C:\product2.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),(NAME=product3,FILENAME='C:\product3.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%)LOGON(NAME='productlog1',FILENAME='C:\productlog1.ldf',SIZE=5MB,MAXSIZE=20MB,FILEGROWTH=5MB),(NAME='productlog2',FILENAME='C:\productlog2.ldf',SIZE=5MB,MAXSIZE=20MB,FILEGROWTH=5MB) ——創(chuàng)建數(shù)據(jù)庫GOsp_detach_dbproductGOCREATEDATABASEproductONPRIMARY(FILENAME='C:\product1.mdf')FORATTACHGO04二月2023第22頁5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理2．修改數(shù)據(jù)庫04二月2023第23頁5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理2．修改數(shù)據(jù)庫例5-5

向數(shù)據(jù)庫添加文件。創(chuàng)建數(shù)據(jù)庫Test1，數(shù)據(jù)庫文件為t1dat1.ndf并更改該數(shù)據(jù)庫以添加一個5MB的新數(shù)據(jù)文件t1dat2.ndf。USEmasterGOCREATEDATABASETest1ON(NAME=Test1dat1,FILENAME='c:\t1dat1.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)GOALTERDATABASETest1ADDFILE(NAME=Test1dat2,FILENAME='c:\t1dat2.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)GO04二月2023第24頁5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理2．修改數(shù)據(jù)庫例5-6向數(shù)據(jù)庫Test1添加文件組Test1FG1，并向Test1FG1添加數(shù)據(jù)庫文件t1dat3.ndf和t1dat4.ndf，設置Test1FG1為默認文件組。USEmasterGOALTERDATABASETest1ADDFILEGROUPTest1FG1GOALTERDATABASETest1ADDFILE(NAME=test1dat3,FILENAME='c:\t1dat3.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB),(NAME=test1dat4,FILENAME='c:\t1dat4.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)TOFILEGROUPTest1FG1ALTERDATABASETest1MODIFYFILEGROUPTest1FG1DEFAULTGO04二月2023第25頁5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理2．修改數(shù)據(jù)庫USEmasterGOALTERDATABASETest1ADDLOGFILE(NAME=test1log2,FILENAME='c:\test2log.ldf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB),(NAME=test1log3,FILENAME='c:\test3log.ldf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)GO例5-7

向數(shù)據(jù)庫Test1中添加日志文件，數(shù)據(jù)庫中添加兩個5MB的日志test2log.ldf和test3log.ldf。04二月2023第26頁5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理2．修改數(shù)據(jù)庫例5-8

從數(shù)據(jù)庫Test1中刪除文件。將例5-6中添加到數(shù)據(jù)庫Test1中的一個文件test1dat4刪除。USEmasterGOALTERDATABASETest1REMOVEFILEtest1dat4GO04二月2023第27頁5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理2．修改數(shù)據(jù)庫例5-9

更改數(shù)據(jù)庫文件，將例5-6中添加到數(shù)據(jù)庫Test1中的test1dat3，將文件由5MB修改為20MB。USEmasterGOALTERDATABASETest1MODIFYFILE(NAME=test1dat3,SIZE=20MB)GO04二月2023第28頁5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理3．數(shù)據(jù)庫快照的維護（1）創(chuàng)建數(shù)據(jù)庫快照CREATEDATABASEdatabase_snapshot_nameON(NAME=logical_file_name,FILENAME='os_file_name')[,…n]ASSNAPSHOTOFsource_database_name[;]例5-10

為例5-5創(chuàng)建數(shù)據(jù)庫快照Test1_snapshot。CREATEDATABASETest1_snapshotON(NAME=Test1dat1,FILENAME='c:\t1dat1.ss'),(NAME=Test1dat2,FILENAME='c:\t1dat2.ss'),(NAME=Test1dat3,FILENAME='c:\t1dat3.ss')ASSNAPSHOTOFTest104二月2023第29頁5.2.1DDL數(shù)據(jù)庫管理3．數(shù)據(jù)庫快照的維護（2）刪除數(shù)據(jù)庫快照DROPDATABASEdatabase_snapshot_name例5-11

刪除Test1數(shù)據(jù)快照Test1_snapshot。DROPDATABASETest1_snapshot5.2SQL語言04二月2023第30頁5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理4．分離和附加數(shù)據(jù)庫（1）分離數(shù)據(jù)庫SP_DETACH_DB[@DBNAME=]'database_name'[,[@SKIPCHECKS=]'skipchecks'][,[@KEEPFULLTEXTINDEXFILE=]'keepfulltextindexfile']

CREATEDATABASETest2ONPRIMARY(NAME='Test2_data',FILENAME='c:\Test2.mdf')GOSP_DETACH_DbTest2例5-12

創(chuàng)建一個數(shù)據(jù)庫Test2，并分離。5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理4．分離和附加數(shù)據(jù)庫（2）以附加的方式創(chuàng)建數(shù)據(jù)庫04二月2023第31頁CREATEDATABASEdatabase_nameON<filespec>[,…n]FOR{ATTACH[WITH<service_broker_option>]|ATTACH_REBUILD_LOG}[;]<service_broker_option>::={ENABLE_BROKER|NEW_BROKER|ERROR_BROKER_CONVERSATIONS}例5-13

利用例5-12分離的數(shù)據(jù)庫創(chuàng)建新的數(shù)據(jù)庫。CREATEDATABASETest3ON(FILENAME='c:\Test2.mdf')FORATTACH5.2SQL語言04二月2023第32頁5.2.1DDL數(shù)據(jù)庫管理4．分離和附加數(shù)據(jù)庫（3）附加數(shù)據(jù)庫SP_ATTACH_DB[@DBNAME=]'dbname',[@FILENAME1=]'filename_n'[,…16]例5-14

附加例5-13創(chuàng)建的數(shù)據(jù)庫。SP_ATTACH_DB@DBNAME='Test2',@FILENAME1='c:\Test2.mdf'5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理5．數(shù)據(jù)庫備份和還原（1）數(shù)據(jù)庫備份命令04二月2023第33頁BACKUPDATABASE{database_name|@database_name_var}TO<backup_device>[,…n]<backup_device>:=DISK|TAPE|PIPE例5-15完全備份“教學管理”數(shù)據(jù)庫，運行結(jié)果如圖5-74所示。BACKUPDATABASE‘教學管理’TODISK=‘D:教學管理系統(tǒng)備份.bak’圖5-74完全備份“教學管理”數(shù)據(jù)庫5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理5．數(shù)據(jù)庫備份和還原（2）差異備份數(shù)據(jù)庫04二月2023第34頁BACKUPDATABASE{database_name|@database_name_var}TO<backup_device>[,…n][WITH[[,]DIFFERENTIAL]]例5-16差異備份“教學管理”數(shù)據(jù)庫，運行結(jié)果如圖5-75所示。BACKUPDATABASE教學管理TODISK='D:\教學管理系統(tǒng)差異備份.bak'WITHDIFFERENTIAL圖5-75差異備份“教學管理”數(shù)據(jù)庫5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理5．數(shù)據(jù)庫備份和還原（3）事務日志04二月2023第35頁BACKUPLOG{database_name

|@database_name_var

}TO<backup_device>[,…n]例5-17

日志備份，運行結(jié)果如圖5-76所示。BACKUPLOG教學管理TODISK='D:\教學管理系統(tǒng)日志備份.bak'圖5-76日志備份5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理5．數(shù)據(jù)庫備份和還原（4）數(shù)據(jù)庫恢復04二月2023第36頁RESTOREDATABASE{database_name|@database_name_var}FROM<backup_device>[,…n

]例5-18通過之前的備份文件恢復。RESTOREDATABASE教學管理TODISK='D:\教學管理系統(tǒng)備份.bak'5.2SQL語言5.2.1DDL數(shù)據(jù)庫管理6．刪除數(shù)據(jù)庫04二月2023第37頁DROPDATABASEdatabase_name[,…n

]例5-19刪除單個數(shù)據(jù)庫，從系統(tǒng)表中刪除Test1數(shù)據(jù)庫的所有引用。例5-20刪除多個數(shù)據(jù)庫DROPDATABASETest1DROPDATABASEproduct,Test15.2SQL語言5.2.2DDL表格管理1．創(chuàng)建表格04二月2023星期五第38頁5.2SQL語言5.2.2DDL表格管理1．創(chuàng)建表格其中表格的約束有：

主鍵約束—PrimaryKey

外鍵約束—ForeignKey

檢查約束—Check約束

默認約束—Default約束

唯一約束—Unique約束例5-21創(chuàng)建“院系表”和“學生表”，由系統(tǒng)提供PRIMARYKEY和FOREIGNKEY約束名，見表5-4和表5-5。CREATETABLE院系表/*-創(chuàng)建”院系表”-*/(YXBHCHAR(8)PRIMARYKEYCLUSTERED,

YXMCCHAR(20)NOTNULL,YXZRCHAR(8) )CREATETABLE學生表/*-創(chuàng)建學生表-*/(XHCHAR(7)--學號，主碼，自定義約束名

CONSTRAINTPK_XHPRIMARYKEYNONCLUSTERED,XMCHAR(20)NOTNULL,SFZCHAR(18)UNIQUENONCLUSTERED,YXBHCHAR(8)REFERENCES院系表(YXBH))或者CREATETABLE學生表/*-創(chuàng)建學生表-*/(XHCHAR(7)CONSTRAINTPK_XHPRIMARYKEYNONCLUSTERED，

XMCHAR(20)NOTNULL,SFZCHAR(18)UNIQUENONCLUSTERED,YXBHCHAR(8)FOREIGNKEY(YXBH)REFERENCES院系表(YXBH)--院系編號，外碼)2012年12月14日星期五第39頁04二月2023星期五第39頁5.2SQL語言5.2.2DDL表格管理1．創(chuàng)建表格例5-22創(chuàng)建表格“學生表”，自定義PRIMARYKEY和FOREIGNKEY約束名。CREATETABLE學生表/*-創(chuàng)建學生表-*/(XHCHAR(7)--學號,主碼，自定義約束名CONSTRAINTPK_XHPRIMARYKEYNONCLUSTERED，

XMCHAR(20)NOTNULL,--姓名，非空（必填）

SFZCHAR(18)UNIQUENONCLUSTERED,--身份證，唯一

YXBHCHAR(8)--院系編號，外碼

CONSTRAINTFK_YXBHFOREIGNKEY(YXBH)REFERENCES院系表(YXBH))GO2012年12月14日星期五第40頁04二月2023星期五第40頁5.2SQL語言5.2.2DDL表格管理1．創(chuàng)建表格例5-22創(chuàng)建表格“學生表”，自定義PRIMARYKEY和FOREIGNKEY約束名。CREATETABLE學生表/*-創(chuàng)建學生表-*/(XHCHAR(7)--學號,主碼，自定義約束名CONSTRAINTPK_XHPRIMARYKEYNONCLUSTERED，

XMCHAR(20)NOTNULL,--姓名，非空（必填）

SFZCHAR(18)UNIQUENONCLUSTERED,--身份證，唯一

YXBHCHAR(8)--院系編號，外碼

CONSTRAINTFK_YXBHFOREIGNKEY(YXBH)REFERENCES院系表(YXBH))GO2012年12月14日星期五第41頁04二月2023星期五第41頁5.2SQL語言5.2.2DDL表格管理1．創(chuàng)建表格例5-23創(chuàng)建“課程表”，“教師表”及“成績表”，表結(jié)構(gòu)如表5-6，表5-7及表5-8所示。CREATETABLE課程表(KCHCHAR(3)CONSTRAINTPK_KCHPRIMARYKEY,KCMCHAR(20))GOCREATETABLE教師表(JSHCHAR(5)CONSTRAINTPK_JSHPRIMARYKEY,JSMCHAR(20))GOCREATETABLE成績表(XHCHAR(7)REFERENCES學生表(XH),--外碼,指向“學生表”(XH)KCHCHAR(3)REFERENCES課程表(KCH),--外碼,指向“課程表”(KCH)CJINTDEFAULT0CHECK(CJ>=0ANDCJ<=100),--成績,默認值為0，在0和100之間JSHCHAR(5)REFERENCES教師表(JSH),教師號,外碼,指向“教師表”的教師號(JSH)CONSTRAINTPK_CJPRIMARYKEY(XH,KCH)--主碼)2012年12月14日星期五第42頁04二月2023星期五第42頁5.2SQL語言5.2.2DDL表格管理2．修改表格2012年12月14日星期五第43頁04二月2023星期五第43頁5.2SQL語言5.2.2DDL表格管理2．修改表格例5-24更改表以添加新列。ALTERTABLE成績表ADDcolumn_bVARCHAR(20)NULL2012年12月14日星期五第44頁例5-25更改表以添加具有約束的列。ALTERTABLE成績表ADDcolumn_bVARCHAR(20)NULLCONSTRAINTb_uniqueUNIQUE例5-26更改表以除去列。ALTERTABLE成績表DROPCOLUMNcolumn_b04二月2023星期五第44頁5.2SQL語言5.2.2DDL表格管理2．修改表格例5-27更改表以添加未驗證的約束。建立表t(column_b)，向該表插入記錄(-1)，再插入約束(column_b>1)，為了防止對現(xiàn)有的數(shù)據(jù)執(zhí)行約束驗證，采用WITHNOCHECK參數(shù)建立約束。2012年12月14日星期五第45頁04二月2023星期五第45頁5.2SQL語言5.2.2DDL表格管理2．修改表格CREATETABLETest(column_aINTCONSTRAINTcolumn_a_unUNIQUE)ALTERTABLETestADDcolumn_bINTIDENTITYCONSTRAINTcolumn_b_pkPRIMARYKEY,column_cINTNULLCONSTRAINTcolumn_c_fkREFERENCESTest(column_a),column_dVARCHAR(16)NULLCONSTRAINTcolumn_d_chkCHECK(column_dISNULLORcolumn_dLIKE'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'ORcolumn_dLIKE'[0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),column_eDECIMAL(3,3)CONSTRAINTcolumn_e_defaultDEFAULT.081例5-28更改表以添加多個帶有約束的列。向表中添加多個帶有約束的新列，第一個新列具有IDENTITY屬性，表中每一行的標識列都將具有遞增的新值。2012年12月14日星期五第46頁04二月2023星期五第46頁5.2SQL語言5.2.2DDL表格管理3．刪除表格DROPTABLEtable_name例5-29除去當前數(shù)據(jù)庫內(nèi)的表。從當前數(shù)據(jù)庫中刪除Test表及其數(shù)據(jù)和索引。2012年12月14日星期五第47頁DROPTABLETest例5-30除去另外一個數(shù)據(jù)庫內(nèi)的表。在“Master”數(shù)據(jù)庫中刪除“教務管理”數(shù)據(jù)庫中的“t”表。USEMasterDROPTABLE教務管理.dbo.t04二月2023星期五第47頁5.2SQL語言5.2.3DML數(shù)據(jù)管理1．插入數(shù)據(jù)例5-31使用簡單的INSERT，為院系表插入輸入數(shù)據(jù)('001','計算機','馮遠客')。INSERT院系表VALUES('001','計算機','馮遠客')也可以從Master數(shù)據(jù)庫的院系表復制到教務管理數(shù)據(jù)庫的院系表中。INSERT教務管理.dbo.院系表SELECT*FROMmaster.dbo.院系表2012年12月14日星期五第48頁04二月2023星期五第48頁5.2SQL語言5.2.3DML數(shù)據(jù)管理1．插入數(shù)據(jù)2012年12月14日星期五第49頁例5-32插入與列順序不同的數(shù)據(jù)。為院系表插入數(shù)據(jù)，并以列顯示指定的方式進行。INSERT院系表(YXMC,YXBH)VALUES('經(jīng)管','002')”INSERT院系表(YXZR,YXMC,YXBH)VALUES('黃梅','數(shù)學','003')例5-33插入值少于列個數(shù)的數(shù)據(jù)。下面的示例創(chuàng)建一個帶有4個列的表。INSERT語句插入一些行，這些行只有部分列包含值。CREATETABLET2(column_1intidentity,column_2varchar(30)CONSTRAINTdefault_nameDEFAULT('columndefault'),column_3intNULL,column_4varchar(40))INSERTINTOT2(column_4)VALUES('1A4')INSERTINTOT2(column_2,column_4)VALUES('2A2','2A4')INSERTINTOT2(column_2,column_3,column_4)VALUES('3A2',-44,'3A4')04二月2023星期五第49頁5.2SQL語言5.2.3DML數(shù)據(jù)管理1．插入數(shù)據(jù)例5-34從學生表中插入數(shù)據(jù)，運行結(jié)果如圖5-77所示。INSERTINTO學生表VALUES('0301002','張麗珍',,'001')INSERTINTO學生表VALUES('0302001','陳俊雄',,'002')INSERTINTO學生表VALUES('0302002','李軍',,'002')INSERTINTO學生表VALUES('0302003','王仁芳',,'002')INSERTINTO學生表VALUES('0303001','趙雄偉',,'003')2012年12月14日星期五第50頁04二月2023星期五第50頁5.2SQL語言5.2.3DML數(shù)據(jù)管理2．修改數(shù)據(jù)2012年12月14日星期五第51頁例5-35使用簡單的UPDATE，將成績表中的所有成績加10分。UPDATE成績表SETCJ=CJ+10例5-36把WHERE子句和UPDATE語句一起使用，例如院系表中的“電腦系”更名為“計算機”。UPDATE院系表

SETYXMC='計算機'WHEREYXBH='001'04二月2023星期五第51頁5.2SQL語言5.2.3DML數(shù)據(jù)管理2．修改數(shù)據(jù)例5-37通過UPDATE語句使用來自另一個表的信息。下面的示例在“學生表”中插入一行，在“成績表”中插入一行。INSERT學生表(XH,XM,SFZ)VALUES('0301001','李永年',)INSERT成績表(XH,KCH,CJ,JSH)VALUES('0301001','001',89,'01001')將名字為“李永年”的成績減少10分UPDATE成績表SETCJ=CJ-10FROM學生表,成績表WHERE學生表.XM='李永年'AND學生表.XH=成績表.XH2012年12月14日星期五第52頁04二月2023星期五第52頁5.2SQL語言5.2.3DML數(shù)據(jù)管理2．修改數(shù)據(jù)例5-38將UPDATE語句與SELECT語句中的TOP子句一起使用，將“成績表”前三名學生的成績修改為95分。UPDATE成績表SETCJ=95FROM(SELECTTOP3*FROM成績表ORDERBYCJDESC)ASt1WHERE成績表.XH=t1.XHAND成績表.KCH=t1.KCHAND成績表.JSH=t1.JSH2012年12月14日星期五第53頁04二月2023星期五第53頁5.2SQL語言5.2.3DML數(shù)據(jù)管理3．刪除數(shù)據(jù)例5-39不帶參數(shù)使用DELETE從成績表中刪除所有行。SELECT*INTOcopy_cjFROM成績表DELETE成績表2012年12月14日星期五第54頁04二月2023星期五第54頁5.2SQL語言5.2.3DML數(shù)據(jù)管理3．刪除數(shù)據(jù)INSERTINTO成績表VALUESSELECT*FROMcopy_cjDELETEFROM成績表WHEREXH='0301001''例5-40在行集上使用DELETE。刪除成績表表中所有學號為‘0301001’的成績記錄，因為“成績表”中XH可能不是唯一的，下例刪除的是‘0301001’的所有行。例5-40在游標的當前行上使用DELETE。下例顯示在名為complex_join_cursor的游標上所做的刪除。它只影響當前從游標提取的單行。DELETEFROM成績表

WHERECURRENTOFcomplex_join_cursor2012年12月14日星期五第55頁04二月2023星期五第55頁5.2SQL語言5.2.3DML數(shù)據(jù)管理3．刪除數(shù)據(jù)例5-41基于子查詢使用DELETE或使用Transact-SQL擴展本例顯示基于連接或相關(guān)子查詢從基表中刪除記錄的Transact-SQL擴展。第1個DELETE顯示與SQL-92兼容的子查詢解決方法，第2個DELETE顯示Transact-SQL擴展。兩個查詢都基于將姓張的同學的成績從成績表中刪除。DELETEFROM成績表WHEREXHIN(SELECTXHFROM學生表WHEREXMLIKE'張%')DELETEFROM成績表INNERJOIN學生表ON成績表.XH=學生表.XHWHEREXMLIKE'張%'2012年12月14日星期五第56頁04二月2023星期五第56頁5.2SQL語言5.2.3DML數(shù)據(jù)管理3．刪除數(shù)據(jù)例5-42在DELETE和SELECT中使用TOP子句。由于可以在DELETE語句中指定SELECT語句，因此還可以在SELECT語句中使用TOP子句。例如，本例從“成績表”中刪除前3個學生的成績。DELETE成績表FROM(SELECTTOP3*FROM成績表)ASt1WHERE成績表.XH=t1.XH2012年12月14日星期五第57頁04二月2023星期五第57頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢2012年12月14日星期五第58頁04二月2023星期五第58頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢1．投影：SELECT子句2012年12月14日星期五第59頁例5-43從表5-11中，查詢所有學生信息SELECT*FROM學生表04二月2023星期五第59頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢1．投影：SELECT子句SELECTXHAS學號,XMAS姓名,SFZAS身份證,FROM學生表例5-44從表5-11中，查詢所有學生的學號、姓名、身份證信息，并把結(jié)果的列名顯示成中文，查詢結(jié)果如圖5-80所示。2012年12月14日星期五第60頁04二月2023星期五第60頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢1．投影：SELECT子句例5-45從表5-12中，查詢學生表中的前4個學生信息，查詢結(jié)果如圖5-81所示。SELECTTOP4*FROM學生表查詢以名字順序排列的前50%的同學信息，查詢結(jié)果如圖5-82所示。SELECTTOP50PERCENT*FROM學生表ORDERBYXM圖5-81SELECTTOP查找前4位同學圖5-82SELECTTOP查找按姓名排序前50%同學2012年12月14日星期五第61頁04二月2023星期五第61頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢1．投影：SELECT子句例5-46查詢“成績表”不重復的KCH，結(jié)果如圖5-83所示。SELECTDISTINCT(KCH)ASKCHFROM成績表例5-47查詢所有人的平均成績，查詢結(jié)果如圖5-84所示。SELECTAVG(CJ)AS平均成績FROM成績表

圖5-83查詢成績表不重復課程號圖5-84查詢成績表平均成績例5-48從表5-13中，查詢最高的成績和最低的成績，查詢結(jié)果如圖5-85所示SELECTMAX(CJ)AS最高分,MIN(CJ)AS最低分FROM成績表圖5-85查詢成績表的最高分和最低分2012年12月14日星期五第62頁04二月2023星期五第62頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢2．連接：FROM2012年12月14日星期五第63頁04二月2023星期五第63頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢2．連接：FROM（1）內(nèi)部連接例5-49從表5-10、表5-11和表5-13中查詢學生的成績，要求實現(xiàn)內(nèi)部連接，查詢學生的姓名和課程號。結(jié)果如圖5-86所示。SELECT學生表.XHAS學號,學生表.XMAS姓名,課程表.KCMAS課程名,成績表.CJAS成績FROM學生表INNERJOIN成績表ON(學生表.XH=成績表.XH)INNERJOIN課程表ON(課程表.KCH=成績表.KCH)2012年12月14日星期五第64頁04二月2023星期五第64頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢2．連接：FROM（2）左連接例5-50從表5-10、表5-11和表5-13中查詢學生的課程名單，要求實現(xiàn)左連接。結(jié)果如圖5-87所示。SELECT學生表.XHAS學號,

學生表.XMAS姓名,

課程表.KCMAS課程名FROM學生表LEFTJOIN成績表ON成績表.XH=學生表.XH LEFTJOIN課程表ON成績表.KCH=課程表.KCH2012年12月14日星期五第65頁04二月2023星期五第65頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢2．連接：FROM（3）右連接例5-51從表5-10、表5-11和表5-13中查詢學生的課程名單，要求實現(xiàn)右連接。結(jié)果如圖5-88所示。SELECT學生表.XHAS學號,學生表.XMAS姓名,課程表.KCMAS課程名

FROM學生表RIGHTJOIN成績表ON成績表.XH=學生表.XHRIGHTJOIN課程表ON成績表.KCH=課程表.KCH2012年12月14日星期五第66頁04二月2023星期五第66頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢2．連接：FROM（4）完全連接例5-52從表5-10、表5-11和表5-13中查詢學生的課程名單，要求實現(xiàn)完全連接。SELECT學生表.XHAS學號,學生表.XMAS姓名,課程表.KCMAS課程名FROM學生表FULLJOIN成績表ON成績表.XH=學生表.XHFULLJOIN課程表ON成績表.KCH=課程表.KCH圖5-89完全連接的例子2012年12月14日星期五第67頁04二月2023星期五第67頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢3．選擇：WHERE指定用于限制返回的行的搜索條件。

2012年12月14日星期五第68頁例5-53查詢“成績表”中成績不在70～80分的學生名單。SELECT*FROM成績表WHERECJ<70ORCJ>80例5-54查詢“成績表”中成績及格的成績名單，如圖5-89所示。SELECT*FROM成績表WHERECJ>=60例5-55查詢成績在70～80（包括70）分的成績名單。SELECT*FROM成績表WHERECJBETWEEN70AND8004二月2023星期五第68頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢3．選擇：WHERE例5-56查詢選修了“001”課程的成績單，如圖5-92所示。SELECT*FROM成績表WHEREKCHIN('001')例5-57查詢姓李的學生名單，如圖5-93所示。SELECT*FROM學生表WHERE(XMLIKE'李%')圖5-92查詢選修了“001”課程的成績單圖5-93查詢學生表中李姓學生2012年12月14日星期五第69頁04二月2023星期五第69頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢4．ORDERBY[ORDERBY{order_by_expression[ASC|DESC]}[,…n]]例5-58查詢按照分數(shù)高低排列的成績名單，如圖5-94所示。SELECT*FROM成績表ORDERBYKCH,CJDESC例5-59查詢成績在前三名的學生，并按照成績從高到低排序，如圖5-95所示。SELECTTOP3*FROM成績表ORDERBYCJDESC2012年12月14日星期五第70頁04二月2023星期五第70頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢5．GROUPBY[GROUPBY[ALL]group_by_expression[,…n][WITH{CUBE|ROLLUP}]]例5-60查詢各個課程的平均成績SELECTAVG(CJ)AS平均成績,KCHAS課程編號FROM成績表GROUPBYKCH例5-61將成績表中的成績按照教師分組，要求統(tǒng)計成績高于60分的學生人數(shù)及他們所獲得的成績總和SELECTSUM(CJ)AS成績總和,JSHAS教師號COUNT(*)AS人數(shù)FROM成績表WHERECJ>60GROUPBYJSH2012年12月14日星期五第71頁04二月2023星期五第71頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢6．COMPUTE集合查詢例5-62將學號大于0302002和小于0302002的學生名單合并SELECT*FROM學生表WHEREXH>0302002UNIONSELECT*FROM學生表WHEREXH<03020022012年12月14日星期五第72頁04二月2023星期五第72頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢7．嵌套子查詢例5-64查詢選擇網(wǎng)絡基礎(chǔ)課程的學生名單。SELECT*FROM學生表WHEREXHIN(SELECTXHFROM成績表WHEREKCHIN(SELECTKCHFROM課程表WHERE(KCM='計算機基礎(chǔ)')))圖5-100查詢選擇網(wǎng)絡基礎(chǔ)課程的學生名單2012年12月14日星期五第73頁04二月2023星期五第73頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢7．嵌套子查詢例5-65查詢所有成績高于任何一個姓陳的成績的學生名單，如圖5-101所示SELECT*FROM學生表ASA,成績表ASB WHEREA.XH=B.XHANDB.CJ>ANY (SELECTC.CJFROM成績表ASC,學生表ASD WHEREC.XH=D.XHANDD.XMLIKE'陳%')2012年12月14日星期五第74頁04二月2023星期五第74頁5.2SQL語言5.2.4DQL數(shù)據(jù)查詢7．嵌套子查詢例5-66查詢成績高于所有姓陳的學生名單，如圖5-102所示。SELECT*FROM學生表ASA,成績表ASB WHEREA.XH=B.XHANDB.CJ>ALL (SELECTC.CJFROM成績表ASC,學生表ASD WHEREC.XH=D.XHANDD.XMLIKE'陳%')圖5-102查詢成績高于所有姓陳的學生名單04二月2023星期五第75頁5.2SQL語言5.2.5DCL數(shù)據(jù)控制1．賬號管理創(chuàng)建賬號：CREATELOGINloginNameWITHPASSWORD=password,DEFAULTDATA-BASE=database

修改賬號：ALTERLOGINlogin_name

刪除賬號：DROPLOGINlogin_name創(chuàng)建用戶：CREATEUSERuser_nameFORLOGINlogin_name04二月2023星期五第76頁5.2SQL語言5.2.5DCL數(shù)據(jù)控制1．賬號管理例5-67為教務管理數(shù)據(jù)庫創(chuàng)建一個登錄賬號teacher，密碼為123，并為該登錄賬號創(chuàng)建一個數(shù)據(jù)庫用戶，使得賬號可以登錄數(shù)據(jù)庫。USE教務管理CREATELOGINteacherWITHPASSWORD='123',DEFAULT_DATABASE=教務管理CREATEUSERteacher1FORLOGINteacherEXECSP_CHANGE_USERS_LOGIN'update_one','teacher1','teacher';-2012年12月14日星期五第77頁04二月2023星期五第77頁5.2SQL語言5.2.5DCL數(shù)據(jù)控制2．授權(quán)命令GRANT第78頁

GRANT{ALL|statement[,…n]}TOsecurity_account[,…n]

GRANT{ALL[PRIVILEGES]|permission[,…n]}{[(column[,…n])]ON{table|view}|ON{table|view}[(column[,…n])]|ON{stored_procedure|extended_procedure}|ON{user_defined_function}}TOsecurity_account[,…n][WITHGRANTOPTION][AS{group|role}]04二月2023第78頁5.2SQL語言5.2.5DCL數(shù)據(jù)控制2．授權(quán)命令GRANT例5-68授予學生student對教學管理數(shù)據(jù)庫中的學生表進行INSERT、UPDATE和DELETE的權(quán)限。EXECSP_ADDLOGIN‘student’,‘0000’,‘教學管理SP_ADDLOGINloginName,password,database"創(chuàng)建用戶"GRANTALLON管理系統(tǒng)TOSTUDENTWITHGRANTOPTION04二月2023第79頁5.2SQL語言5.2.5DCL數(shù)據(jù)控制3．廢除權(quán)限命令REVOKE第80頁例5-69廢除student在學生表的INSERT權(quán)限。REVOKEINSERTON學生表FROMstudent例5-70廢除所有用戶對學生表的操作。REVOKEALLON學生表FROMPUBLIC04二月2023第80頁5.2SQL語言5.2.5DCL數(shù)據(jù)控制4．拒絕繼承權(quán)限命令DENY例5-71本例對用戶拒絕多個語句權(quán)限。用戶不能使用CREATEDATABASE和CREATETABLE語句，除非給他們顯式授予權(quán)限。DENYCREATEDATABASE,CREATETABLETOstudent第81頁04二月2023第81頁5.3T-SQL編程5.3.1T-SQL注釋

5.3.2表達式

5.3.3批處理與腳本

5.3.4流程控制語句

5.3.5CASE表達式

5.3.6創(chuàng)建用戶自定義函數(shù)

5.3.7游標

5.3.8事務

5.3.9創(chuàng)建存儲過程

5.3.10創(chuàng)建視圖04二月2023第82頁5.3T-SQL編程5.3.1T-SQL注釋注釋多行/*fshjhfjkshfjsdhfsdjffsjdkfljskdlfjkldsfjkdslfjfjfj*/注釋單行//ghjfghkfdjhgkfhgjfdhgkgjfdh第83頁04二月2023第83頁5.3T-SQL編程5.3.2表達式2．常量常量是指在程序運行中值不變的量。根據(jù)常量的類型不同分為字符型常量，整型常量，日期時間型常量、實型常量、貨幣常量、全局唯一標識符。1．數(shù)據(jù)類型（1）字符串（2）整型（3）日期時間型（4）實型（5）貨幣（6）全局唯一標識符第84頁04二月2023第84頁5.3T-SQL編程5.3.2表達式3．變量局部變量聲明：DECLARE{@變量名數(shù)據(jù)類型，@變量名數(shù)據(jù)類型}賦值：SET@變量名=表達式SELECT@變量名=表達式/SELECT變量名=輸出值FROM表where第85頁04二月2023第85頁5.3T-SQL編程5.3.2表達式3．變量第86頁例5-72SELECT命令賦值，執(zhí)行腳本，運行結(jié)果如圖5-103所示。DECLARE@var1varchar(7) SELECT@var1='學生姓名' SELECT@var1=XM FROM學生表WHEREXH='0302001'SELECT@var1as'學生姓名'圖5-103根據(jù)學號查詢學生姓名04二月2023第86頁5.3T-SQL編程3．變量全局變量聲明：@@變量名全局變量記錄SQLServer服務器活動狀態(tài)的一組數(shù)據(jù)。系統(tǒng)提供30個全局變量。以下是幾個全局變量介紹：@@ERROR

最后一個Ｔ-SQL錯誤的錯誤號@@IDENTITY

最后一個插入的標識值@@LANGUAGE

當前使用語言的名稱@@MAX_CONNECTIONS

創(chuàng)建的同時可以鏈接的最大數(shù)目@@ROWCOUNT

受上一個SQL語言影響的行數(shù)@@SERVERNAME

本地服務器的名稱@@SERVICENAME

該計算機上的SQL服務的名稱@@TIMETICKS

當前計算機上每刻度的微秒數(shù)@@TRANSCOUNT

當前連接打開的事務數(shù)@@VERSION

SQLServer的版本信息04二月2023第87頁5.3T-SQL編程5.3.2表達式3．變量例5-75顯示SQLServer的版本，結(jié)果如圖5-106所示。select@@versionselect@@servername第88頁04二月2023第88頁5.3T-SQL編程5.3.2表達式4．運算符MicrosoftSQLServer提供了7種類型的運算符，分別是算術(shù)運算符、賦值運算符、位運算符、比較運算符、邏輯運算符和一元運算符。第89頁04二月2023第89頁5.3T-SQL編程5.3.2表達式5．函數(shù)、SQLServer提供的常用內(nèi)置函數(shù)主要有以下幾類：數(shù)學函數(shù)、字符串函數(shù)、日期函數(shù)、convert函數(shù)、聚合函數(shù)等。例5-76查詢出最高分的學號和最高分，如圖5-107所示。SELECTXH,CJFROM成績表WHERECJ=(SELECTmax(CJ)FROM成績表)第90頁圖5-107查詢最高學分的學號和分數(shù)04二月2023第90頁5.3T-SQL編程5.3.3批處理與腳本例5-80返回狀態(tài)，該過程檢查在成績表中是否存在選修了001課程的學生。存在則返回1，不存在返回2。CREATEPROCEDUREchecks_kch@paramintASIF(SELECTCOUNT(KCH)FROM成績表WHEREKCH=@param)>0RETURN1ELSERETURN2DECLARE@paramINTEXEC@param=check_kch'001'PRINT@param第91頁04二月2023第91頁5.3T-SQL編程5.3.4流程控制語句1．分支語句2．循環(huán)語句3．控制語句04二月2023第92頁5.3T-SQL編程5.3.4流程控制語句1．分支語句IF…ELSE分支語句可以根據(jù)某一條件執(zhí)行某一個語句塊，其語法如下。第93頁IFBoolean_expression {sql_statement|statement_block} [ELSE{sql_statement|statement_block}] 在實際程序中，IF…ELSE語句中不止包含一條語句，而是一組SQL語句，其語法格式為：BEGIN{sql_statement|statement_block}END/*語句塊*/04二月2023第93頁5.3T-SQL編程5.3.4流程控制語句2．循環(huán)語句WHILE循環(huán)語句可以根據(jù)某一條件反復執(zhí)行某一個語句塊，其語法如下。第94頁04二月2023第94頁5.3T-SQL編程5.3.4流程控制語句3．控制語句第95頁04二月2023第95頁5.3T-SQL編程5.3.4流程控制語句3．控制語句例5-81IF查詢課程中有沒有計算機課，如圖5-112所示。IFexists(SELECT*FROM課程表WHEREKCMlike'計算機%')SELECTCOUNT(*)AS計算機課程數(shù)量

FROM課程表

WHEREKCMlike'計算機%'ELSEPRINT'數(shù)據(jù)庫中沒有計算機課程'第96頁04二月2023第96頁5.3T-SQL編程5.3.4流程控制語句3．控制語句例5-82嵌套IF課程查詢，如圖5-113所示。IFexists(SELECT*FROM課程表WHEREKCM='高等數(shù)學')SELECTCOUNT(*)AS選修高等數(shù)學人數(shù)

FROM成績表,課程表

WHEREKCM='高等數(shù)學'AND成績表.KCH=課程表.KCHELSEIFexists(SELECT*FROM課程表WHEREKCM='編譯原理')SELECTCOUNT(*)AS選修編譯原理人數(shù)

FROM成績表,課程表

WHEREKCM='編譯原理'ELSEPRINT'高等數(shù)學和編譯原理都沒開！'第97頁04二月2023第97頁5.3T-SQL編程5.3.4流程控制語句3．控制語句例5-84一個小循環(huán)程序。DECLARE@XintSET@X=0WHILE@x<3BEGINSET@x=@X+1PRINT'x='+convert(char(1),@x)END第98頁04二月2023第98頁5.3T-SQL編程5.3.4