第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数据库的创建和删除04二月2023第5页5.1SQLServer的图形界面5.1.3表的创建、修改和删除1．创建数据表2．修改表结构3．删除表

04二月2023第6页5.1SQLServer的图形界面5.1.4建立表间的关联（1）外键的创建（2）增添表间关系（3）删除表间关系（删除外键）04二月2023第7页5.1SQLServer的图形界面5.1.5增添数据和查询（1）直接向数据表中添加数据（以“学生表”为例）（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触发器的使用04二月2023第12页5.1SQLServer的图形界面5.1.10账号及权限管理1．Windows登录账号的建立与删除（1）建立和管理用户账号（2）混合认证模式下SQLServer04二月2023第13页5.1SQLServer的图形界面5.1.11分离和附加数据库04二月2023第14页5.1SQLServer的图形界面5.1.12数据库备份和还原04二月2023第15页5.1SQLServer的图形界面5.1.13DTS导入导出向导5.2SQL语言SQL功能命令（动词）数据定义DDLcreate、alter、drop数据操纵DMLinsert、update、delete数据查询DQLselect数据控制DCLgrant、revoke、deny04二月2023星期五第16页5.2.1DDL数据库管理5.2.2DDL表格管理5.2.3DML数据管理5.2.4DQL数据查询5.2.5DCL数据控制04二月2023第17页5.2SQL语言5.2.1DDL数据库管理1．创建数据库例5-1

创建简单数据库CREATEDATABASEProducts文

件

类

型文件扩展名主要数据文件.mdf次要数据文件.ndf事务日志文件.ldf04二月2023第18页5.2SQL语言例5-2

创建“教学管理”数据库：数据库名称为“教学管理”，数据库文件初始大小为5MB，增长率为15%，文件名称为“教学管理_DATA.mdf”，日志文件名称为“教学管理_LOG.ldfCREATEDATABASE教学管理ONPRIMARY--默认就属于PRIMARY主文件组，可省略(NAME='教学管理_DATA',--主数据文件的逻辑名

FILENAME='D:\教学管理_data.mdf',--主数据文件的物理名

SIZE=5MB,--主数据文件初始大小

MAXSIZE=100MB,--主数据文件增长的最大值

FILEGROWTH=15%--主数据文件的增长率)LOGON(NAME='教学管理_log',FILENAME='D:\教学管理_LOG.ldf',SIZE=2MB,FILEGROWTH=1MB)04二月2023第19页5.2SQL语言例5-3

使用文件组、多个数据文件和事务日志文件创建数据库。CREATEDATABASE教务管理ONPRIMARY(NAME=学籍管理,FILENAME='C:\学籍管理.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),(NAME=境内生学籍管理,FILENAME='C:\境内生学籍管理.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),(NAME=境外生学籍管理,FILENAME='C:\境外生学籍管理.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),FILEGROUP宿舍管理组1(NAME=境内生宿舍管理,FILENAME='D:\境内生宿舍管理.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=境外生宿舍管理,FILENAME='D:\境外生宿舍管理.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUP选课系统(NAME=选课系统,FILENAME='E:\选课系统.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

创建一个包含下列物理文件的名为“product”的数据库C:\product1.mdfC:\product2.ndfC:\product3.ndfC:\productlog1.ldfC:\productlog2.ldf04二月2023第21页5.2SQL语言可以使用sp_detach_db存储过程分离该数据库，然后使用带有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) ——创建数据库GOsp_detach_dbproductGOCREATEDATABASEproductONPRIMARY(FILENAME='C:\product1.mdf')FORATTACHGO04二月2023第22页5.2SQL语言5.2.1DDL数据库管理2．修改数据库04二月2023第23页5.2SQL语言5.2.1DDL数据库管理2．修改数据库例5-5

向数据库添加文件。创建数据库Test1，数据库文件为t1dat1.ndf并更改该数据库以添加一个5MB的新数据文件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数据库管理2．修改数据库例5-6向数据库Test1添加文件组Test1FG1，并向Test1FG1添加数据库文件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数据库管理2．修改数据库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

向数据库Test1中添加日志文件，数据库中添加两个5MB的日志test2log.ldf和test3log.ldf。04二月2023第26页5.2SQL语言5.2.1DDL数据库管理2．修改数据库例5-8

从数据库Test1中删除文件。将例5-6中添加到数据库Test1中的一个文件test1dat4删除。USEmasterGOALTERDATABASETest1REMOVEFILEtest1dat4GO04二月2023第27页5.2SQL语言5.2.1DDL数据库管理2．修改数据库例5-9

更改数据库文件，将例5-6中添加到数据库Test1中的test1dat3，将文件由5MB修改为20MB。USEmasterGOALTERDATABASETest1MODIFYFILE(NAME=test1dat3,SIZE=20MB)GO04二月2023第28页5.2SQL语言5.2.1DDL数据库管理3．数据库快照的维护（1）创建数据库快照CREATEDATABASEdatabase_snapshot_nameON(NAME=logical_file_name,FILENAME='os_file_name')[,…n]ASSNAPSHOTOFsource_database_name[;]例5-10

为例5-5创建数据库快照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数据库管理3．数据库快照的维护（2）删除数据库快照DROPDATABASEdatabase_snapshot_name例5-11

删除Test1数据快照Test1_snapshot。DROPDATABASETest1_snapshot5.2SQL语言04二月2023第30页5.2SQL语言5.2.1DDL数据库管理4．分离和附加数据库（1）分离数据库SP_DETACH_DB[@DBNAME=]'database_name'[,[@SKIPCHECKS=]'skipchecks'][,[@KEEPFULLTEXTINDEXFILE=]'keepfulltextindexfile']

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

创建一个数据库Test2，并分离。5.2SQL语言5.2.1DDL数据库管理4．分离和附加数据库（2）以附加的方式创建数据库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分离的数据库创建新的数据库。CREATEDATABASETest3ON(FILENAME='c:\Test2.mdf')FORATTACH5.2SQL语言04二月2023第32页5.2.1DDL数据库管理4．分离和附加数据库（3）附加数据库SP_ATTACH_DB[@DBNAME=]'dbname',[@FILENAME1=]'filename_n'[,…16]例5-14

附加例5-13创建的数据库。SP_ATTACH_DB@DBNAME='Test2',@FILENAME1='c:\Test2.mdf'5.2SQL语言5.2.1DDL数据库管理5．数据库备份和还原（1）数据库备份命令04二月2023第33页BACKUPDATABASE{database_name|@database_name_var}TO<backup_device>[,…n]<backup_device>:=DISK|TAPE|PIPE例5-15完全备份“教学管理”数据库，运行结果如图5-74所示。BACKUPDATABASE‘教学管理’TODISK=‘D:教学管理系统备份.bak’图5-74完全备份“教学管理”数据库5.2SQL语言5.2.1DDL数据库管理5．数据库备份和还原（2）差异备份数据库04二月2023第34页BACKUPDATABASE{database_name|@database_name_var}TO<backup_device>[,…n][WITH[[,]DIFFERENTIAL]]例5-16差异备份“教学管理”数据库，运行结果如图5-75所示。BACKUPDATABASE教学管理TODISK='D:\教学管理系统差异备份.bak'WITHDIFFERENTIAL图5-75差异备份“教学管理”数据库5.2SQL语言5.2.1DDL数据库管理5．数据库备份和还原（3）事务日志04二月2023第35页BACKUPLOG{database_name

|@database_name_var

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

日志备份，运行结果如图5-76所示。BACKUPLOG教学管理TODISK='D:\教学管理系统日志备份.bak'图5-76日志备份5.2SQL语言5.2.1DDL数据库管理5．数据库备份和还原（4）数据库恢复04二月2023第36页RESTOREDATABASE{database_name|@database_name_var}FROM<backup_device>[,…n

]例5-18通过之前的备份文件恢复。RESTOREDATABASE教学管理TODISK='D:\教学管理系统备份.bak'5.2SQL语言5.2.1DDL数据库管理6．删除数据库04二月2023第37页DROPDATABASEdatabase_name[,…n

]例5-19删除单个数据库，从系统表中删除Test1数据库的所有引用。例5-20删除多个数据库DROPDATABASETest1DROPDATABASEproduct,Test15.2SQL语言5.2.2DDL表格管理1．创建表格04二月2023星期五第38页5.2SQL语言5.2.2DDL表格管理1．创建表格其中表格的约束有：

主键约束—PrimaryKey

外键约束—ForeignKey

检查约束—Check约束

默认约束—Default约束

唯一约束—Unique约束例5-21创建“院系表”和“学生表”，由系统提供PRIMARYKEY和FOREIGNKEY约束名，见表5-4和表5-5。CREATETABLE院系表/*-创建”院系表”-*/(YXBHCHAR(8)PRIMARYKEYCLUSTERED,

YXMCCHAR(20)NOTNULL,YXZRCHAR(8) )CREATETABLE学生表/*-创建学生表-*/(XHCHAR(7)--学号，主码，自定义约束名

CONSTRAINTPK_XHPRIMARYKEYNONCLUSTERED,XMCHAR(20)NOTNULL,SFZCHAR(18)UNIQUENONCLUSTERED,YXBHCHAR(8)REFERENCES院系表(YXBH))或者CREATETABLE学生表/*-创建学生表-*/(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．创建表格例5-22创建表格“学生表”，自定义PRIMARYKEY和FOREIGNKEY约束名。CREATETABLE学生表/*-创建学生表-*/(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．创建表格例5-22创建表格“学生表”，自定义PRIMARYKEY和FOREIGNKEY约束名。CREATETABLE学生表/*-创建学生表-*/(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．创建表格例5-23创建“课程表”，“教师表”及“成绩表”，表结构如表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)，为了防止对现有的数据执行约束验证，采用WITHNOCHECK参数建立约束。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除去当前数据库内的表。从当前数据库中删除Test表及其数据和索引。2012年12月14日星期五第47页DROPTABLETest例5-30除去另外一个数据库内的表。在“Master”数据库中删除“教务管理”数据库中的“t”表。USEMasterDROPTABLE教务管理.dbo.t04二月2023星期五第47页5.2SQL语言5.2.3DML数据管理1．插入数据例5-31使用简单的INSERT，为院系表插入输入数据('001','计算机','冯远客')。INSERT院系表VALUES('001','计算机','冯远客')也可以从Master数据库的院系表复制到教务管理数据库的院系表中。INSERT教务管理.dbo.院系表SELECT*FROMmaster.dbo.院系表2012年12月14日星期五第48页04二月2023星期五第48页5.2SQL语言5.2.3DML数据管理1．插入数据2012年12月14日星期五第49页例5-32插入与列顺序不同的数据。为院系表插入数据，并以列显示指定的方式进行。INSERT院系表(YXMC,YXBH)VALUES('经管','002')”INSERT院系表(YXZR,YXMC,YXBH)VALUES('黄梅','数学','003')例5-33插入值少于列个数的数据。下面的示例创建一个带有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数据管理1．插入数据例5-34从学生表中插入数据，运行结果如图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数据管理2．修改数据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数据管理2．修改数据例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数据管理2．修改数据例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数据管理3．删除数据例5-39不带参数使用DELETE从成绩表中删除所有行。SELECT*INTOcopy_cjFROM成绩表DELETE成绩表2012年12月14日星期五第54页04二月2023星期五第54页5.2SQL语言5.2.3DML数据管理3．删除数据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数据管理3．删除数据例5-41基于子查询使用DELETE或使用Transact-SQL扩展本例显示基于连接或相关子查询从基表中删除记录的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数据管理3．删除数据例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数据查询2012年12月14日星期五第58页04二月2023星期五第58页5.2SQL语言5.2.4DQL数据查询1．投影：SELECT子句2012年12月14日星期五第59页例5-43从表5-11中，查询所有学生信息SELECT*FROM学生表04二月2023星期五第59页5.2SQL语言5.2.4DQL数据查询1．投影：SELECT子句SELECTXHAS学号,XMAS姓名,SFZAS身份证,FROM学生表例5-44从表5-11中，查询所有学生的学号、姓名、身份证信息，并把结果的列名显示成中文，查询结果如图5-80所示。2012年12月14日星期五第60页04二月2023星期五第60页5.2SQL语言5.2.4DQL数据查询1．投影：SELECT子句例5-45从表5-12中，查询学生表中的前4个学生信息，查询结果如图5-81所示。SELECTTOP4*FROM学生表查询以名字顺序排列的前50%的同学信息，查询结果如图5-82所示。SELECTTOP50PERCENT*FROM学生表ORDERBYXM图5-81SELECTTOP查找前4位同学图5-82SELECTTOP查找按姓名排序前50%同学2012年12月14日星期五第61页04二月2023星期五第61页5.2SQL语言5.2.4DQL数据查询1．投影：SELECT子句例5-46查询“成绩表”不重复的KCH，结果如图5-83所示。SELECTDISTINCT(KCH)ASKCHFROM成绩表例5-47查询所有人的平均成绩，查询结果如图5-84所示。SELECTAVG(CJ)AS平均成绩FROM成绩表

图5-83查询成绩表不重复课程号图5-84查询成绩表平均成绩例5-48从表5-13中，查询最高的成绩和最低的成绩，查询结果如图5-85所示SELECTMAX(CJ)AS最高分,MIN(CJ)AS最低分FROM成绩表图5-85查询成绩表的最高分和最低分2012年12月14日星期五第62页04二月2023星期五第62页5.2SQL语言5.2.4DQL数据查询2．连接：FROM2012年12月14日星期五第63页04二月2023星期五第63页5.2SQL语言5.2.4DQL数据查询2．连接：FROM（1）内部连接例5-49从表5-10、表5-11和表5-13中查询学生的成绩，要求实现内部连接，查询学生的姓名和课程号。结果如图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数据查询2．连接：FROM（2）左连接例5-50从表5-10、表5-11和表5-13中查询学生的课程名单，要求实现左连接。结果如图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数据查询2．连接：FROM（3）右连接例5-51从表5-10、表5-11和表5-13中查询学生的课程名单，要求实现右连接。结果如图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数据查询2．连接：FROM（4）完全连接例5-52从表5-10、表5-11和表5-13中查询学生的课程名单，要求实现完全连接。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数据查询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数据查询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数据查询4．ORDERBY[ORDERBY{order_by_expression[ASC|DESC]}[,…n]]例5-58查询按照分数高低排列的成绩名单，如图5-94所示。SELECT*FROM成绩表ORDERBYKCH,CJDESC例5-59查询成绩在前三名的学生，并按照成绩从高到低排序，如图5-95所示。SELECTTOP3*FROM成绩表ORDERBYCJDESC2012年12月14日星期五第70页04二月2023星期五第70页5.2SQL语言5.2.4DQL数据查询5．GROUPBY[GROUPBY[ALL]group_by_expression[,…n][WITH{CUBE|ROLLUP}]]例5-60查询各个课程的平均成绩SELECTAVG(CJ)AS平均成绩,KCHAS课程编号FROM成绩表GROUPBYKCH例5-61将成绩表中的成绩按照教师分组，要求统计成绩高于60分的学生人数及他们所获得的成绩总和SELECTSUM(CJ)AS成绩总和,JSHAS教师号COUNT(*)AS人数FROM成绩表WHERECJ>60GROUPBYJSH2012年12月14日星期五第71页04二月2023星期五第71页5.2SQL语言5.2.4DQL数据查询6．COMPUTE集合查询例5-62将学号大于0302002和小于0302002的学生名单合并SELECT*FROM学生表WHEREXH>0302002UNIONSELECT*FROM学生表WHEREXH<03020022012年12月14日星期五第72页04二月2023星期五第72页5.2SQL语言5.2.4DQL数据查询7．嵌套子查询例5-64查询选择网络基础课程的学生名单。SELECT*FROM学生表WHEREXHIN(SELECTXHFROM成绩表WHEREKCHIN(SELECTKCHFROM课程表WHERE(KCM='计算机基础')))图5-100查询选择网络基础课程的学生名单2012年12月14日星期五第73页04二月2023星期五第73页5.2SQL语言5.2.4DQL数据查询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数据查询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数据控制1．账号管理创建账号：CREATELOGINloginNameWITHPASSWORD=password,DEFAULTDATA-BASE=database

修改账号：ALTERLOGINlogin_name

删除账号：DROPLOGINlogin_name创建用户：CREATEUSERuser_nameFORLOGINlogin_name04二月2023星期五第76页5.2SQL语言5.2.5DCL数据控制1．账号管理例5-67为教务管理数据库创建一个登录账号teacher，密码为123，并为该登录账号创建一个数据库用户，使得账号可以登录数据库。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数据控制2．授权命令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数据控制2．授权命令GRANT例5-68授予学生student对教学管理数据库中的学生表进行INSERT、UPDATE和DELETE的权限。EXECSP_ADDLOGIN‘student’,‘0000’,‘教学管理SP_ADDLOGINloginName,password,database"创建用户"GRANTALLON管理系统TOSTUDENTWITHGRANTOPTION04二月2023第79页5.2SQL语言5.2.5DCL数据控制3．废除权限命令REVOKE第80页例5-69废除student在学生表的INSERT权限。REVOKEINSERTON学生表FROMstudent例5-70废除所有用户对学生表的操作。REVOKEALLON学生表FROMPUBLIC04二月2023第80页5.2SQL语言5.2.5DCL数据控制4．拒绝继承权限命令DENY例5-71本例对用户拒绝多个语句权限。用户不能使用CREATEDATABASE和CREATETABLE语句，除非给他们显式授予权限。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创建用户自定义函数

5.3.7游标

5.3.8事务

5.3.9创建存储过程

5.3.10创建视图04二月2023第82页5.3T-SQL编程5.3.1T-SQL注释注释多行/*fshjhfjkshfjsdhfsdjffsjdkfljskdlfjkldsfjkdslfjfjfj*/注释单行//ghjfghkfdjhgkfhgjfdhgkgjfdh第83页04二月2023第83页5.3T-SQL编程5.3.2表达式2．常量常量是指在程序运行中值不变的量。根据常量的类型不同分为字符型常量，整型常量，日期时间型常量、实型常量、货币常量、全局唯一标识符。1．数据类型（1）字符串（2）整型（3）日期时间型（4）实型（5）货币（6）全局唯一标识符第84页04二月2023第84页5.3T-SQL编程5.3.2表达式3．变量局部变量声明：DECLARE{@变量名数据类型，@变量名数据类型}赋值：SET@变量名=表达式SELECT@变量名=表达式/SELECT变量名=输出值FROM表where第85页04二月2023第85页5.3T-SQL编程5.3.2表达式3．变量第86页例5-72SELECT命令赋值，执行脚本，运行结果如图5-103所示。DECLARE@var1varchar(7) SELECT@var1='学生姓名' SELECT@var1=XM FROM学生表WHEREXH='0302001'SELECT@var1as'学生姓名'图5-103根据学号查询学生姓名04二月2023第86页5.3T-SQL编程3．变量全局变量声明：@@变量名全局变量记录SQLServer服务器活动状态的一组数据。系统提供30个全局变量。以下是几个全局变量介绍：@@ERROR

最后一个Ｔ-SQL错误的错误号@@IDENTITY

最后一个插入的标识值@@LANGUAGE

当前使用语言的名称@@MAX_CONNECTIONS

创建的同时可以链接的最大数目@@ROWCOUNT

受上一个SQL语言影响的行数@@SERVERNAME

本地服务器的名称@@SERVICENAME

该计算机上的SQL服务的名称@@TIMETICKS

当前计算机上每刻度的微秒数@@TRANSCOUNT

当前连接打开的事务数@@VERSION

SQLServer的版本信息04二月2023第87页5.3T-SQL编程5.3.2表达式3．变量例5-75显示SQLServer的版本，结果如图5-106所示。select@@versionselect@@servername第88页04二月2023第88页5.3T-SQL编程5.3.2表达式4．运算符MicrosoftSQLServer提供了7种类型的运算符，分别是算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符和一元运算符。第89页04二月2023第89页5.3T-SQL编程5.3.2表达式5．函数、SQLServer提供的常用内置函数主要有以下几类：数学函数、字符串函数、日期函数、convert函数、聚合函数等。例5-76查询出最高分的学号和最高分，如图5-107所示。SELECTXH,CJFROM成绩表WHERECJ=(SELECTmax(CJ)FROM成绩表)第90页图5-107查询最高学分的学号和分数04二月2023第90页5.3T-SQL编程5.3.3批处理与脚本例5-80返回状态，该过程检查在成绩表中是否存在选修了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．循环语句3．控制语句04二月2023第92页5.3T-SQL编程5.3.4流程控制语句1．分支语句IF…ELSE分支语句可以根据某一条件执行某一个语句块，其语法如下。第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．循环语句WHILE循环语句可以根据某一条件反复执行某一个语句块，其语法如下。第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计算机课程数量

FROM课程表

WHEREKCMlike'计算机%'ELSEPRINT'数据库中没有计算机课程'第96页04二月2023第96页5.3T-SQL编程5.3.4流程控制语句3．控制语句例5-82嵌套IF课程查询，如图5-113所示。IFexists(SELECT*FROM课程表WHEREKCM='高等数学')SELECTCOUNT(*)AS选修高等数学人数

FROM成绩表,课程表

WHEREKCM='高等数学'AND成绩表.KCH=课程表.KCHELSEIFexists(SELECT*FROM课程表WHEREKCM='编译原理')SELECTCOUNT(*)AS选修编译原理人数

FROM成绩表,课程表

WHEREKCM='编译原理'ELSEPRINT'高等数学和编译原理都没开！'第97页04二月2023第97页5.3T-SQL编程5.3.4流程控制语句3．控制语句例5-84一个小循环程序。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