《关系数据库与SQL Server 2012》--例题源代码

关系数据库与SQLServer2012例题源代码第1章关系数据库原理第2章SQLServer2012基础第3章数据库的创建与管理【例3-2】创建一个不带任何参数的数据库DB1。CREATEDATABASEDB1【例3-3】创建一个数据库，指定数据库的数据文件所在位置。CREATEDATABASEDB2ON(NAME=DB2,FILENAME='D:\TEST\DB2.MDF')【例3-4】创建一个数据库，指定数据库的数据文件所在位置、初始容量、最大容量和文件增量。CREATEDATABASEDB3ON(NAME=DB3,FILENAME='D:\TEST\DB3.MDF',SIZE=10,MAXSIZE=50,FILEGROWTH=5%)【例3-5】创建一个数据库，指定数据库的数据文件和日志文件的存放位置。CREATEDATABASEDB4ON(NAME=DB4,FILENAME='D:\TEST\DB4.MDF',SIZE=10,MAXSIZE=50,FILEGROWTH=5%)LOGON(NAME=DB4LOG,FILENAME='D:\TEST\DB4.LDF')【例3-6】创建一个数据库，该库共包含3个数据文件和2个日志文件。CREATEDATABASEDB5ON(NAME=DB51,FILENAME='D:\TEST\DB51.MDF’,SIZE=100,MAXSIZE=200,FILEGROWTH=20),(NAME=DB52,FILENAME='D:\TEST\DB52.NDF’,SIZE=100,MAXSIZE=200,FILEGROWTH=20),(NAME=DB53,FILENAME='D:\TEST\DB53.NDF’,SIZE=100,MAXSIZE=200,FILEGROWTH=20)LOGON(NAME=DB5LOG1,FILENAME='D:\TEST\DB5LOG1.LDF’),(NAME=DB5LOG2,FILENAME='D:\TEST\DB5LOG2.LDF’,SIZE=50,MAXSIZE=200,FILEGROWTH=20)【例3-7】创建一个数据库，该库共包含3个数据文件和2个自定义文件组。CREATEDATABASEDB6ON(NAME=DB61,FILENAME='D:\TEST\DB61.MDF’,SIZE=100,MAXSIZE=200,FILEGROWTH=20),FILEGROUPFDB61(NAME=DB62,FILENAME='D:\TEST\DB62.NDF’,SIZE=100,MAXSIZE=200,FILEGROWTH=20),FILEGROUPFDB62(NAME=DB63,FILENAME='D:\TEST\DB63.NDF’,SIZE=100,MAXSIZE=200,FILEGROWTH=20)【例3-9】将数据库DB1的数据库名称改为“DB8”。ALTERDATABASEDB1MODIFYNAME=DB8【例3-10】在数据库DB8中增加一个数据文件和一个事务日志文件。ALTERDATABASEDB8ADDFILE(NAME=DB81,FILENAME=‘D:\TEST\DB81.NDF')ALTERDATABASEDB8ADDLOGFILE(NAME=DB8LOG1,FILENAME=‘D:\TEST\DB8LOG1.LDF')【例3-11】在数据库DB8中增加一个名为“FDB8”的文件组。ALTERDATABASEDB8ADDFILEGROUPFDB8【例3-12】将数据库DB8中的FDB8文件组的名称改为“FG8”。ALTERDATABASEDB8MODIFYFILEGROUPFDB8NAME=FG8【例3-13】在数据库DB8中增加两个数据文件到文件组“FG8”中，并将该文件组设为默认文件组。ALTERDATABASEDB8ADDFILE(NAME=DB82,FILENAME=‘D:\TEST\DB82.NDF'),(NAME=DB83,FILENAME=‘D:\TEST\DB83.NDF')TOFILEGROUPFG8GOALTERDATABASEDB8MODIFYFILEGROUPFG8DEFAULT【例3-14】将数据库DB8中增加的“DB83”的数据库文件名称改为“DDD”。ALTERDATABASEDB8MODIFYFILE(NAME=DB83,NEWNAME=DDD,FILENAME=‘D:\TEST\DDD.NDF')【例3-15】将数据库DB8的文件组“FG8”中的数据文件“DB82”删除，并将事务日志文件“DB8LOG1”删除。ALTERDATABASEDB8REMOVEFILEDB82ALTERDATABASEDB8REMOVEFILEDB8LOG1【例3-16】将数据库DB8中的文件组“FG8”删除。ALTERDATABASEDB8MODIFYFILEGROUPPRIMARYDEFAULTGOALTERDATABASEDB8REMOVEFILEDDDGOALTERDATABASEDB8REMOVEFILEGROUPFG8【例3-18】将数据库DB3、DB4、DB5、DB6、DB7删除。DROPDATABASEDB3,DB4,DB5,DB6,DB7【例3-20】查看指定数据库的信息，如图3-11所示。sp_helpdbdb【例3-21】查看服务器上所有数据库的信息，如图3-12所示。sp_helpdb第4章数据表的创建与管理【例4-2】在XSCJ数据库中，创建一个名为KC1的数据表，该表中只涉及到列的定义。CREATETABLEXSCJ.DBO.KC1(序号INT,课程号CHAR(3),课程名VARCHAR(20),授课教师VARCHAR(10),开课学期TINYINT,学时TINYINT,学分TINYINT)【例4-3】在XSCJ数据库中，创建一个名为KC2的数据表，该表中的“序号”列为标识列。USEXSCJCREATETABLEKC2(序号INTIDENTITY,课程号CHAR(3),课程名VARCHAR(20),授课教师VARCHAR(10),开课学期TINYINT,学时TINYINT,学分TINYINT)【例4-4】在XSCJ数据库中，创建一个名为KC的数据表，该表的结构如表4-3所示。方法一：在新建表时，在单个列定义之后，紧接着定义约束。USEXSCJCREATETABLEKC(序号INTIDENTITY,课程号CHAR(3)NOTNULLCONSTRAINTPK_KC_KCHPRIMARYKEY,课程名VARCHAR(20)NOTNULL,授课教师VARCHAR(10),开课学期TINYINTNOTNULLCONSTRAINTDF_KC_XQDEFAULT1CONSTRAINTCK_KC_XQCHECK(开课学期>=1and开课学期<=6),学时TINYINTNOTNULL,学分TINYINT)方法二：在新建表时，在所有列定义完之后，再定义约束。USEXSCJCREATETABLEKC(序号INTIDENTITY,课程号CHAR(3)NOTNULL,课程名VARCHAR(20)NOTNULL,授课教师VARCHAR(10),开课学期TINYINTNOTNULLDEFAULT1,学时TINYINTNOTNULL,学分TINYINTCONSTRAINTPK_KC_KHPRIMARYKEY(课程号),CONSTRAINTCK_KC_XQCHECK(开课学期>=1AND开课学期<=6))方法三：在已经创建好的表上定义约束，通过修改该表的方式添加约束。ALTERTABLEKCADDCONSTRAINTPK_KC_KHPRIMARYKEY(课程号),CONSTRAINTDF_KC_XQDEFAULT1FOR开课学期,CONSTRAINTCK_KC_XQCHECK(开课学期>=1AND开课学期<=6)【例4-5】在XSCJ数据库中，创建一个名为XS_KC的数据表，该表的结构如表4-4所示。USEXSCJCREATETABLEXS_KC(学号char(10)notnullREFERENCESXSQK(学号),课程号char(3)notnull,成绩TINYINT,学分TINYINTPRIMARYKEY(学号,课程号),CHECK(成绩>=0and成绩<=100),FOREIGNKEY(课程号)REFERENCESKC(课程号))【例4-7】在XSQK表中，增加两列：“籍贯”字段，char(12)，默认值为“重庆”。“email”字段，varchar(30)。ALTERTABLEXSQKAdd籍贯char(12)constraintdf_xsqk_jgdefault‘重庆’,emailvarchar(30)【例4-8】将学生课程表xs_kc中的成绩列的数据类型修改为numeric(4,1)。ALTERTABLEXS_KCaltercolumn成绩numeric(4,1)【例4-9】在XSQK表的“姓名”列上增加唯一性约束，约束名为UK_XSQK_XM，并忽略对原有数据的约束检查。ALTERTABLEXSQKWITHNOCHECKADDCONSTRAINTUK_XSQK_XMUNIQUE(姓名)【例4-10】将XSQK表中的“姓名”列上的约束删除。AltertablexsqkdropconstraintUK_XSQK_XM【例4-11】将XSQK表中的“籍贯”、“emai”列删除。Altertablexsqkdropconstraintdf_xsqk_jgAltertablexsqkdropcolumn籍贯,email【例4-12】暂停XSQK表中的所有约束。ALTERTABLEXSQKNOCHECKCONSTRAINTALL【例4-14】向XSQK表插入两行数据。INSERTINTOXSQK(学号,姓名,性别,出生日期,专业名,所在系,联系电话,总学分,备注)VALUES(‘2012130501’,‘王成’,0,‘1996-5-21’,‘硬件与外设’,‘计算机’,‘13367614111‘,30,‘学习委员’)insertxsqk(学号,姓名,性别,专业名,所在系,出生日期)values(‘2012130405’,‘田芳’,0,‘信息安全’,‘计算机’,‘1995-7-15’)【例4-15】将xs_kc表中的成绩不及格的记录，插入到nopass表中。usexscjinsertintonopassselect*fromxs_kcwhere成绩<60GOselect*fromnopass【例4-16】将XS_KC表中课程号为“101”的不及格的学生成绩加5分。UPDATEXS_KCSET成绩=成绩+5WHERE(课程号=‘101’AND成绩<60)【例4-17】删除XS_KC表中所有不及格的记录。DELETEXS_KCWHERE(成绩<60)【例4-20】用命令查看服务器上所有数据表的信息。sp_help【例4-21】用命令查看XS_KC数据表的信息，查看的结果如图4-17所示。sp_helpXS_KC【例4-23】查看XS_KC数据表的表约束，查看结果如图4-19所示。sp_helpconstraintXS_KC第5章数据查询【例5-1】查看XSQK表中的所有记录。查询结果如图5-1所示。SELECT*FROMXSQK【例5-2】查询XSQK表中的学号、姓名和专业名信息。查询结果如图5-2所示。SELECT学号,姓名,专业名FROMXSQK【例5-3】查询XSQK表中的前3条记录。查询结果如图5-3所示。SELECTTOP3*FROMXSQK【例5-4】查看XS_KC表中的信息，其中，要求查询折算成绩，折算成绩为原成绩的70%。查询结果如图5-4所示。select学号,课程号,成绩,成绩*0.7fromxs_kc【例5-5】为【例5-4】中的计算列指定别名。查询结果如图5-5所示。select学号,课程号,成绩AS原成绩,调整成绩1=成绩*0.7,成绩+5调整成绩2fromxs_kc【例5-6】在查询结果集中的“备注”列前增加了一个的说明列。查询结果如图5-6所示。SELECT学号,姓名,'职务是',备注FROMXSQK【例5-7】在查询结果集中的“备注”列前增加“其‘职务’是”的说明列。查询结果如图5-7所示。SELECT学号,姓名,'其''职务''是',备注FROMXSQK【例5-8】查看XS_KC表中学生选修了哪些课程。查询结果如图5-8所示。selectDISTINCT课程号fromxs_kc【例5-9】查询XS_KC表中成绩不及格的学生记录。查询结果如图5-10所示。SELECT学号,课程号,成绩FROMXS_KCWHERE成绩<60【例5-10】查询XSQK表中1996年及其后出生的学生信息。查询结果如图5-11所示。select*fromxsqkwhere出生日期>‘1995-12-31’【例5-11】查询XSQK表中信息安全专业、性别是1的学生信息。查询结果如图5-12所示。select学号,姓名,性别,专业名fromxsqkwhere性别=1and专业名=‘信息安全’【例5-12】查询XS_KC表中成绩在70和80之间的学生信息，查询结果如图5-13所示。select*fromxs_kcwhere成绩>=70AND成绩<=80【例5-13】查询1996年12月出生的学生信息，查询结果如图5-14所示。select姓名,性别,出生日期fromxsqkwhere出生日期between‘1996-12-01’and‘1996-12-31’【例5-14】查询XSQK表中的所有陈姓的学生信息，查询结果如图5-15所示。SELECT学号,姓名FROMXSQKWHERE姓名LIKE'陈%'【例5-15】查询XSQK表中电话号码尾数为3的学生信息，查询结果如图5-16所示。select*fromxsqkwhere联系电话like‘%3’【例5-16】查询XSQK表中学号尾数不为1、2、3的学生信息，查询结果如图5-17所示。select*fromxsqkwhere学号like‘%[^123]’或select*fromxsqkwhere学号NOTlike‘%[123]’【例5-17】查询XS_KC表中课程号为‘101’、‘105’或‘108’的学生成绩信息，查询结果如图5-18所示。SELECT学号,课程号,成绩FROMXS_KCWHERE课程号IN('101','105','108')【例5-18】将【例5-17】中的代码改成如下形式，其查询结果不变。SELECT学号,课程号,成绩FROMXS_KCWHERE课程号='101'OR课程号='105'OR课程号='108'【例5-19】查询XSQK表中的学生干部的名单，查询结果如图5-19所示。select学号,姓名,备注fromxsqkwhere备注isnotnull【例5-20】为数据源指定别名。SELECT*FROMXS_KCCJ【例5-21】查询XSQK表的记录，并以姓名降序排列。SELECT*FROMXSQKORDERBY姓名DESC【例5-22】查询XSQK表的记录，并以出生日期升序排列。SELECT学号,姓名,出生日期FROMXSQKORDERBY3【例5-23】查询XS_KC表的记录，并先按课程号升序排列，当课程号相同时再按成绩降序排列。查询结果如图5-20所示。SELECT学号,课程号,成绩FROMXS_KCORDERBY2,3DESC【例5-24】将【例5-23】的查询结果保存到新表TEMP_KC中。SELECT学号,课程号,成绩INTOtemp_KCFROMXS_KCORDERBY2,3DESC【例5-25】计算XS_KC表中成绩列的总和，并为该列指定别名为“总分”。查询结果如图5-21所示。SELECTSUM(成绩)AS总分FROMXS_KC【例5-26】计算XS_KC表中成绩列的算数平均值，并为该列指定别名为“平均分”。SELECTAVG(成绩)AS平均分FROMXS_KC【例5-27】计算XS_KC表中成绩列的最大值，并为该列指定别名为“最高分”。SELECTMAX(成绩)AS最高分FROMXS_KC【例5-28】计算XS_KC表中成绩列的最小值，并为该列指定别名为“最低分”。SELECTMIN(成绩)AS最低分FROMXS_KC【例5-29】计算XSQK表中学生记录的行数，可以用以下两种方法实现，其结果一样。SELECTCOUNT(学号)FROMXSQK或SELECTCOUNT(*)FROMXSQK【例5-30】计算XS_KC表中成绩不及格的学生人次，并为该列指定别名为“不及格学生人次”。查询结果如图5-22所示。SELECTCOUNT(DISTINCT学号)AS'不及格的学生人数'FROMXS_KCWHERE成绩<60【例5-31】在XS_KC表中，统计每门课程的平均分。SELECT课程号,AVG(成绩)AS平均分FROMXS_KCGROUPBY课程号【例5-32】在XSQK表中，统计各专业男女生的人数。统计结果如图5-25所示。Select专业名,性别,count(性别)as人数fromxsqkgroupby专业名,性别orderby专业名【例5-33】在XSQK表中，统计各专业男女生的人数超过2人的信息。统计结果如图5-26所示。Select专业名,性别,count(性别)as人数fromxsqkgroupby专业名,性别havingcount(性别)>2【例5-34】查询不及格学生的学号、姓名、课程号和成绩信息。查询结果如图5-27所示。1ANSI连接语法形式如下：selectxsqk.学号,姓名,课程号,成绩fromxsqkinnerjoinxs_kconxsqk.学号=xs_kc.学号where成绩<602SQLServer连接语法形式如下：selectxsqk.学号,姓名,课程号,成绩fromxsqk,xs_kcwherexsqk.学号=xs_kc.学号and成绩<60【例5-35】用左外连接方式查询学生的学号、姓名、课程号和成绩信息。查询结果如图5-28所示。selectxsqk.学号,姓名,课程号,成绩fromxsqkLEFTOUTERjoinxs_kconxsqk.学号=xs_kc.学号【例5-36】用右外连接方式查询不及格学生的学号、姓名、课程号和成绩信息。查询结果如图5-29所示。selectxsqk.学号,姓名,课程号,成绩fromxsqkRIGHTOUTERjoinxs_kconxsqk.学号=xs_kc.学号where成绩<60【例5-37】用全外连接方式查询学号、姓名、课程号、成绩。查询结果如图5-30所示。selectxsqk.学号,姓名,课程号,成绩fromxsqkFULLOUTERjoinxs_kconxsqk.学号=xs_kc.学号【例5-38】在查询结果集中一行显示每个学生的两门课程成绩。查询结果如图5-31所示。1ANSI连接语法形式如下：SELECTa.学号,a.课程号,a.成绩,b.课程号,b.成绩FROMXS_KCaJOINXS_KCbONa.学号=b.学号WHEREa.课程号<b.课程号2SQLServer连接语法形式如下：SELECTa.学号,a.课程号,a.成绩,b.课程号,b.成绩FROMXS_KCa,XS_KCbWHEREa.学号=b.学号ANDa.课程号<b.课程号【例5-39】查询平均分低于60分的学生学号和姓名。查询结果如图5-32所示。SELECT学号,姓名FROMXSQKaWHERE(SELECTAVG(成绩)FROMXS_KCbWHEREb.学号=a.学号)<60【例5-40】查询有不及格学生的课程的授课教师。查询结果如图5-33所示。SELECTDISTINCT授课教师FROMKCWHERE课程号=ANY(SELECT课程号FROMXS_KCbWHERE成绩<60)【例5-41】查询每门课程的最低分。查询结果如图5-34所示。SELECT*FROMXS_KCAWHERE成绩<=ALL(select成绩fromxs_kcBWHEREB.课程号=A.课程号)或SELECT*FROMXS_KCAWHERE成绩=(selectmIN(成绩)fromxs_kcBWHEREB.课程号=A.课程号)【例5-42】将【例5-40】中的查询改为用“IN”运算符。查询结果如图5-35所示。SELECTDISTINCT授课教师FROMKCWHERE课程号IN(SELECT课程号FROMXS_KCWHERE成绩<60)【例5-43】查询至少有一门课程不及格的学生信息。查询结果如图5-36所示。SELECTDISTINCT学号,姓名FROMXSQKaWHEREEXISTS(SELECT*FROMXS_KCbWHEREb.成绩<60ANDb.学号=a.学号)第6章视图与索引【例6-2】创建一个名为“V_不及格学生信息”的视图，该视图包含所有有不及格记录的学生的学号、姓名和原始成绩。执行结果如图6-5所示。USEXSCJGOCREATEVIEWV_不及格学生信息(学号,姓名,原始成绩)ASSELECTDISTINCTXSQK.学号,姓名,成绩FROMXSQK,XS_KCWHEREXSQK.学号=XS_KC.学号ANDXS_KC.成绩<60GOSELECT*FROMV_不及格学生信息【例6-3】查看XSCJ数据库中的“V_不及格学生信息”视图的定义，执行结果如图6-6所示。USEXSCJGOEXECsp_helptextV_不及格学生信息【例6-6】修改“V_学生信息”视图，使该视图用于查询是班委成员的男生信息，并强制检查指定条件。执行结果如图6-9所示。USEXSCJGOALTERVIEWV_学生信息ASSELECT学号,姓名,性别,出生日期,专业名,所在系,备注AS职务FROMXSQKWHERE性别=1AND备注LIKE‘%’WITHCHECKOPTIONGOSELECT*FROMV_学生信息【例6-8】通过“V_学生信息”视图中添加一条记录。执行结果如图6-14所示。USEXSCJGOINSERTINTOV_学生信息VALUES('2012130112','陈立',1,'1996-05-08','信息安全','计算机应用','体育委员')GOSELECT*FROMXSQK【例6-9】将“V_学生信息”视图中的“张琳”的姓名改为“张林”。执行结果如图6-15所示。USEXSCJGOUPDATEV_学生信息SET姓名='张林'WHERE姓名='张琳'GOSELECT*FROMXSQK【例6-10】通过“V_学生信息”视图删除“张林”的记录。DELETEFROMV_学生信息WHERE姓名='张林'【例6-13】为XS_KC表创建非聚集索引IX_XS_KC_学号_课程号，该索引包括学号和课程号两个索引列，均按升序排列。USEXSCJCREATEINDEXIX_XS_KC_学号_课程号ONXS_KC(学号,课程号)【例6-14】对【例6-13】作一点修改，使该索引变为唯一性的非聚集索引。由于索引已经存在，我们使用DROP_EXISTING选项删除同名的原索引，然后再创建新索引。USEXSCJCREATEUNIQUEINDEXIX_XS_KC_学号_课程号ONXS_KC(学号,课程号)WITHDROP_EXISTING【例6-15】创建复杂的索引。为“XS_KC”表创建一个唯一索引，索引字段为“课程号”、“学号”，其中“课程号”为降序排列，该索引的包含性列里面包含“成绩”、“学分”两个字段。指定索引填充，索引的填充因子为70，并允许在Tempdb数据库中存储临时排序的信息，可以忽略索引的重复键值，运行自动重新计算统计信息。USEXSCJCREATEUNIQUEINDEXIX_XS_KC_1ONXS_KC(课程号DESC,学号)INCLUDE(成绩,学分)WITH(PAD_INDEX=ON,FILLFACTOR=70,SORT_IN_TEMPDB=ON,IGNORE_DUP_KEY=ON,STATISTICS_NORECOMPUTE=OFF)【例6-16】查看表XSQK中的索引信息。执行结果如图6-23所示。USEXSCJEXECsp_helpindexXSQK【例6-18】重新生成“IX_XS_KC_学号_课程号”索引。ALTERINDEXIX_XS_KC_学号_课程号ONXS_KCREBUILD【例6-19】删除“XS_KC”表中的“IX_XS_KC_学号_课程号”索引。DROPINDEXXSQK.IX_XS_KC_学号_课程号【例6-20】为“XSCJ”数据库启用全文索引。EXECsp_fulltext_database'enable'【例6-22】为“XSCJ”数据库在默认位置创建一个全文目录“FTC_XSCJ”。EXECsp_fulltext_catalog'FTC_XSCJ','create'【例6-24】利用唯一索引PK_KC，用命令方式完成【例6-23】中全文索引的创建。EXECsp_fulltext_table'KC','create','FTC_XSCJ','PK_KC'【例6-25】将KC表中的“课程名”列添加到全文索引中。EXECsp_fulltext_column'KC','课程名','add'【例6-26】激活表的全文检索能力，在全文目录中注册表KC。EXECsp_fulltext_table'KC','activate'【例6-27】填充全文目录。EXECsp_fulltext_catalog'FTC_XSCJ','start_full'第7章规则与默认值【例7-1】创建数据库XSCJ的规则，规则名为CJ_rule，要求表XS_KC中成绩列的取值范围为0～100。执行结果如图7-1所示。USEXSCJGOCREATERULECJ_ruleAS@成绩>=0AND@成绩<=100【例7-2】查看规则CJ_rule。执行结果如图7-2所示。USEXSCJGOEXECsp_helptextCJ_rule【例7-3】将规则CJ_rule绑定到表XS_KC的成绩列上。执行结果如图7-3所示。USEXSCJGOEXECsp_bindrule'CJ_rule','XS_KC.成绩'【例7-4】解除XS_KC表中“成绩”列上绑定的规则。执行结果如图7-4所示。USEXSCJGOEXECsp_unbindrule'XS_KC.成绩'【例7-5】删除规则CJ_rule。USEXSCJGODROPRULECJ_rule【例7-6】创建一个名为XS_def的默认值对象，要求默认值为20。执行结果如图7-5所示。USEXSCJGOCREATEDEFAULTXS_defAS20【例7-7】将默认值XS_def绑定到表KC的“学时”列上。执行结果如图7-6所示。USEXSCJGOEXECsp_bindefault'XS_def','KC.学时'【例7-8】删除数据库XSCJ中名为XS_def的默认值对象。执行结果如图7-7所示。USEXSCJGOEXECsp_unbindefault‘KC.学时’GODROPDEFAULTXS_def第8章T-SQL编程【例8-1】在T-SQL中，可用多种方式使用常量。①作为算术表达式中的数据值：SELECT成绩+5FROMXS_KC②在WHERE子句中，作为与列进行比较的数据值：SELECT*FROMXS_KCWHERE学号='2012130101'③作为赋给变量的数据值：SET@学分=4④作为当前行的某列中的数据值。可使用UPDATE语句的SET子句或INSERT语句的VALUES子句来指定：UPDATEXS_KCSET成绩=90WHERE学号='2012130101'AND课程号='101'或INSERTXS_KCVALUES(‘2012130101’,‘103’,78)⑤作为指定PRINT或RAISERROR语句发出的消息文本的字符串：PRINT'Thisisamessage.'⑥作为条件语句（例如：IF语句或CASE语句）中进行测试的值：IF(@SALESTOTAL>$100000.00)EXECUTEProc_SALES【例8-2】声明3个变量，用两种不同的方法分别为变量赋值，并输出显示，执行结果如图8-1所示。DECLARE@x1char(10),@x2varchar(20),@x3char(8)SELECT@x1='关系数据库',@x2='SQLServer'SET@x3='2012'PRINT@x1+@x2+@x3【例8-3】定义一个整数变量，并将其作为循环计数器使用。DECLARE@counterintSELECT@counter=0WHILE@counter<10BEGINSELECT@counter=@counter+1PRINT@counterEND【例8-4】声明一个变量，将“XSCJ”数据库的“XSQK”表中的2012130101号学生的姓名赋值给变量，并输出显示，执行结果如图8-2所示。USEXSCJGODECLARE@namevarchar(10)SELECT@name=姓名FROMXSQKWHERE学号='2012130101'SELECT@nameAS姓名【例8-5】在Accounts表中检索tot_assets列，并将money数据类型的检索结果利用CAST或CONVERT函数转换为varchar数据类型，以便在字符串连接中使用。SELECT(acct_firstname+acct_lastname)+’----’+CAST(tot_assetsASvarchar(15))FROMAccounts或者SELECT(acct_firstname+acct_lastname)+’----’+CONVERT(varchar(15),tot_assets)FROMAccounts【例8-6】统计XSQK表中学生记录的行数。执行结果如图8-3所示。USEXSCJGODECLARE@counterintSELECT@counter=COUNT(*)FROMXSQKPRINT'总行数为：'+STR(@counter)【例8-7】将浮点数123.21转换为5个字符的字符串。执行结果如图8-4所示。DECLARE@xfloatSET@x=123.21PRINT'将浮点数123.21转换成5个字符的字符串为：'+STR(@x,5,1)【例8-8】查找KC表中课程号为“103”的课程名称，并计算该课程名称的字符数目。执行结果如图8-5所示。USEXSCJGODECLARE@counterintSELECT@counter=LEN(课程名)FROMKCWHERE课程号='103'PRINT'103号课程名称的字符数为：'+STR(@counter)【例8-9】将字符串“HelloWorld”中的字符全部转换为大写字母。执行结果如图8-6所示。DECLARE@s1varchar(20)SET@s1='HelloWorld'PRINT'将“HelloWorld”中的字符全部转换为大写字母：'+UPPER(@s1)【例8-10】从字符串“SQLServer2012”的左边开始截取5个字符构成的子串。执行结果如图8-7所示。DECLARE@s2varchar(20)SET@s2='SQLServer2012'PRINT'从“SQLServer2012”中截取5个字符构成的子串为：'+LEFT(@s2,5)【例8-11】修改【例8-6】中的显示结果，去除显示结果中多余的空格。执行结果如图8-8所示。USEXSCJGODECLARE@counterintSELECT@counter=COUNT(*)FROMXSQKPRINT'总行数为：'+LTRIM(STR(@counter))【例8-12】分别计算在日期“2015-5-10”上加上22年、22月、22天后得到的新日期。执行结果如图8-9所示。SELECTDATEADD(yy,22,'2015-5-10')AS加年后的新日期,DATEADD(mm,22,'2015-5-10')AS加月后的新日期,DATEADD(dd,22,'2015-5-10')AS加日后的新日期【例8-13】分别计算日期“2012-8-18”和“2015-5-10”相隔的天数、月数和年数。执行结果如图8-10所示。SELECTDATEDIFF(dd,'2012-8-18','2015-5-10')AS间隔天数,DATEDIFF(mm,'2012-8-18','2015-5-10')AS间隔月数,DATEDIFF(yy,'2012-8-18','2015-5-10')AS间隔年数【例8-14】分别返回日期“2015-5-1”的年、月、日。执行结果如图8-11所示。SELECTDATEPART(yy,'2015-5-1')AS年,DATEPART(mm,'2015-5-1')AS月,DATEPART(dd,'2015-5-1')AS日【例8-15】计算XSQK表中陈伟的年龄，并显示“年龄为：XX岁”。执行结果如图8-12所示。DECLARE@nlvarchar(4)SELECT@nl=CONVERT(varchar(4),DATEDIFF(yy,出生日期,GETDATE()))FROMXSQKWHERE姓名='陈伟'PRINT'年龄为：'+@nl+'岁'【例8-16】检查XSQK表中是否存在“李渊”这个人，若有则显示其信息，若无则显示“没有XX这个人！”。执行结果如图8-13所示。USEXSCJGODECLARE@xmvarchar(10)SET@xm='李渊'IFEXISTS(SELECT*FROMXSQKWHERE姓名=@xm)SELECT*FROMXSQKWHERE姓名=@xmELSEPRINT'没有'+@xm+'这个人!'【例8-17】计算1+2+3+…+100的累加和。执行结果如图8-14所示。DECLARE@iint,@sumintSET@i=1SET@sum=0WHILE(@i<=100)BEGINSET@sum=@sum+@iSET@i=@i+1ENDPRINT'1+2+3+…+100='+CAST(@sumASvarchar(10))【例8-18】查找失败时输出错误信息并退出。执行结果如图8-15所示。USEXSCJGOIFNOTEXISTS(SELECT*FROMXSQKWHERE姓名='李敏')BEGINPRINT'notfound'RETURNENDPRINT'noerrorfound'【例8-19】用IF和GOTO语句实现循环，求1+2+3+…+100的累加和。DECLARE@iint,@sumintSET@i=1SET@sum=0CountLoop:SET@sum=@sum+@iSET@i=@i+1IF@i<=100GOTOCountLoopPRINT'1+2+3+…+100='+CAST(@sumASvarchar(10))【例8-20】直到10点45分才从KC表中检索所有课程信息。WAITFORTIME'10:45:00'SELECT*FROMKC第9章存储过程与触发器【例9-1】创建一个不包含任何参数的存储过程P_KC，查询KC表中第一学期开设的课程信息，并执行该存储过程。执行结果如图9-1所示。USEXSCJGOCREATEPROCEDUREP_KCASSELECT*FROMKCWHERE开课学期=1GOEXECP_KC【例9-2】创建一个带有输入参数的存储过程P_CJ，查询指定课程号的学生成绩信息，并执行该存储过程。执行结果如图9-2所示。USEXSCJGOCREATEPROCP_CJ@kchchar(3)ASSELECT学号,课程号,成绩FROMXS_KCWHERE课程号=@kchGOEXECP_CJ'102'【例9-3】创建一个带有输入参数和输出参数的存储过程P_KCH，返回指定教师所授课程的课程号，并执行该存储过程。执行结果如图9-3所示。USEXSCJGOCREATEPROCP_KCH@skjsvarchar(10),@kchchar(3)OUTPUTASSELECT@kch=课程号FROMKCWHERE授课教师=@skjsGODECLARE@skjsvarchar(10),@kchchar(3)SET@skjs='王颐'EXECP_KCH@skjs,@kchOUTPUTPRINT@skjs+'教师所授课程的课程号为：'+@kch【例9-4】修改【例9-1】中创建的存储过程P_KC，使它只查询课程信息中的课程号、课程名和授课教师三列信息。执行结果如图9-4所示。USEXSCJGOALTERPROCEDUREP_KCASSELECT课程号,课程名,授课教师FROMKCWHERE开课学期=1GOEXECP_KC【例9-6】查看存储过程P_KCH的定义文本。执行结果如图9-6所示。USEXSCJGOEXECsp_helptextP_KCH【例9-7】查看存储过程P_KCH的所有者、创建时间和各个参数信息。执行结果如图9-7所示。USEXSCJGOEXECsp_helpP_KCH【例9-8】查看存储过程P_KCH的依赖关系，即该存储过程引用的对象和引用该存储过程的对象。执行结果如图9-8所示。USEXSCJGOEXECsp_dependsP_KCH【例9-10】为XSQK表创建一个INSERT触发器，当插入的新行中“所在系”的值不是“计算机应用”时，就撤销该插入操作，使用RAISERROR语句返回错误信息。然后激活触发器以实现数据完整性，执行结果如图9-10所示。CREATETRIGGERT_INSERT_XSQKONXSQKFORINSERTASDECLARE@szxvarchar(20)SELECT@szx=所在系FROMInsertedIF@szx!='计算机应用'BEGINROLLBACKTRANSACTIONRAISERROR('不能插入非计算机应用系的学生信息！',16,10)ENDGOINSERTXSQK(学号,姓名,性别,出生日期,专业名,所在系)VALUES('2012140101','陈希',1,'1995-8-12','环境艺术','传媒')【例9-11】为XSQK表创建一个UPDATE触发器，当更新了某位学生的学号信息时，就用触发器级联更新XS_KC表中相关的学号信息。然后激活触发器以实现数据完整性，执行结果如图9-11所示。CREATETRIGGERT_UPDATE_XSQKONXSQKFORUPDATEASDECLARE@oldchar(10),@newchar(10)SELECT@old=Deleted.学号,@new=Inserted.学号FROMDeleted,InsertedWHEREDeleted.姓名=Inserted.姓名PRINT'准备级联更新XS_KC表中的学号信息…'UPDATEXS_KCSET学号=@newWHERE学号=@oldPRINT'已经级联更新XS_KC表中原学号为'+@old+'的信息！'GOUPDATEXSQKSET学号='2012130999'WHERE学号='2012130101'【例9-12】为XS_KC表创建一个UPDATE触发器，并利用UPDATE函数检测成绩列是否被更新，若成绩列被更新，则显示学号、课程号、原成绩和新成绩信息。然后激活触发器以实现数据完整性，执行结果如图9-13所示。分析：UPDATE（列名）函数检测在指定的列上是否进行了INSERT或UPDATE操作，但不能检测DELETE操作。根据指定列是否被更新，UPDATE（）将返回TRUE或FALSE。CREATETRIGGERT_UPDATE_XS_KCONXS_KCFORUPDATEASIFUPDATE(成绩)BEGINSELECTInserted.学号,Inserted.课程号,Deleted.成绩AS原成绩,Inserted.成绩AS新成绩FROMInserted,DeletedWHEREInserted.学号=Deleted.学号ENDGOUPDATEXS_KCSET成绩=95WHERE学号='2012130102'AND课程号='101'【例9-13】为XSQK表创建一个DELETE触发器，当要删除学生记录时，就撤销该删除操作，并给出提示信息。然后激活触发器以实现数据完整性，执行结果如图9-14所示。createtriggert_delete_xsqkonxsqkfordeleteasrollbacktransactionprint'不能删除xsqk表中的学生信息!'godeletexsqkwhere学号='2012130301'【例9-14】为XSCJ数据库创建一个DDL触发器。当要修改或删除数据库中的表时，就撤销该操作，并给出提示信息。然后激活触发器以实现数据完整性，执行结果如图9-15所示。USEXSCJGOCREATETRIGGERT_XSCJ_DDLONDATABASEFORALTER_TABLE,DROP_TABLEASROLLBACKTRANSACTIONPRINT'无法修改或删除表！请在操作前禁用或删除DDL触发器"T_XSCJ_DDL"'GOALTERTABLEXSQKADDQQVARCHAR(30)【例9-15】修改【例9-10】中创建的触发器T_INSERT_XSQK，使得当插入的新行中“所在系”的值既不是“计算机应用”也不是“工商管理”时，就撤销该插入操作，并使用RAISERROR语句返回错误信息。ALTERTRIGGERT_INSERT_XSQKONXSQKFORINSERTASDECLARE@szxvarchar(20)SELECT@szx=所在系FROMInsertedIF@szx!='计算机应用'AND@szx!='工商管理'BEGINROLLBACKTRANSACTIONRAISERROR('不能插入非计算机应用系和工商管理系的学生信息！',16,10)END【例9-17】查看触发器T_UPDATE_XSQK的所有者、创建时间等基本信息。执行结果如图9-17所示。EXECSP_HELPT_UPDATE_XSQK【例9-18】查看触发器T_UPDATE_XSQK的依赖关系，即该触发器引用的对象和引用该触发器的对象