第四章TSQL语言存储过程触发器例题.docx
《第四章TSQL语言存储过程触发器例题.docx》由会员分享，可在线阅读，更多相关《第四章TSQL语言存储过程触发器例题.docx（20页珍藏版）》请在冰点文库上搜索。
第四章TSQL语言存储过程触发器例题
第四章T-SQL语言部分例题
【例4-13】创建一个只有一个主数据文件SchoolTest（不指定该文件的大小）的数据库。
CREATEDATABASESchoolTest
ON
（NAME=SchoolTest_Data,
FILENAME='D:
\SchoolTest_Data.MDF'）
【例4-14】建立学生表T_StudentTest。
CREATETABLET_StudentTest（
StudentCodeCHAR（8）NOTNULLUNIQUE,/*唯一性约束，不许取空值*/
StudentNameVARCHAR（16）NOTNULL,
SexCHAR
（2）NOTNULL,
LiveInDormBITDEFAULT1,/*默认值为1*/
ConstraintStudentPKPrimaryKey（StudentCode）/*StudentCode为主键约束*/
）
【例4-15】建立成绩表T_GradeTest。
CREATETABLET_GradeTest（
StudentCodeCHAR（8）NOTNULL,/*不许取空值*/
GradeREALDEFAULT0,/*默认值为0*/
ConstraintGradeCKCheck（Grade>=0ANDGrade<=100）/*检查约束0>=Grade<=100*/
）
【例4-16】删除T_GradeTest表
DROPTABLET_GradeTest
【例4-17】在T_Student表的姓名（StudentName）字段上建立升序索引。
CREATEINDEXNameIndexonT_Student（StudentName）
【例4-18】在T_Student表的学号（StudentCode）字段上建立唯一降序索引。
CREATEUNIQUEINDEXStudentCodeIndexonT_Student（StudentCodeDESC）
【例4-19】删除T_Student表中索引名称分别为NameIndex和StudentCodeIndex的两个索引。
DROPINDEXT_Student.ManyIndex,T_Student.StudentCodeIndex
【例4-20】使用SELECT语句查询T_Student表中的学生姓名（StudentName）和性别（Sex）。
SELECTStudentName,Sex
FROMT_Student
【例4-21】使用SELECT语句查询T_Student表中所有字段的值。
SELECT*
FROMT_Student
【例4-22】显示T_Class表中班级代号（ClassCode）字段的值，并将字段名ClassCode改为ClassNumber（注意，字段别名[ClassNumber]含有空格，所以用方括号括起来）。
SELECTClassCodeAS[ClassNumber]
FROMT_Class
【例4-23】查询T_Course表中的前3条记录。
SELECTTOP3*
FROMT_Course
【例4-24】查询T_Course表中前20%的记录。
SELECTTOP20PERCENT*
FROMT_Course
【例4-25】查询T_Course表中开课的学院名称Academy（与所开课程无关）。
SELECTDISTINCTAcademy
FROMT_Course
【例4-26】计算学生的总分、平均分、最高分、最低分及总人数。
计算结果如图4-37显示。
SELECTSum（Grade）AS总分,Avg（Grade）AS平均分,
Max（Grade）AS最高分,Min（Grade）AS最低分,
Count（StudentCode）AS人数
FROMT_Grade
【例4-27】查询T_Student表和T_Grade表，报告StudentCode、CourseCode和Grade。
因两个表都有StudentCode字段，执行如图4-38a）所示的语句，系统显示出错信息“列名‘StudentCode’不明确”；4-38b）正确执行，其中的T_Student.StudentCode也可换为T_Grade.StudentCode。
【例4-28】在T_Student表中查询所有女同学的姓名。
SELECTStudentCode,StudentName,Sex,LiveInDorm
FROMT_Student
WHERESex='女'
【例4-29】查询未住校的女学生的情况。
查询结果如图4-39所示。
SELECTStudentCode,StudentName,Sex,LiveInDorm
FROMT_Student
WHERESex='女'andLiveInDorm=0
【例4-30】在T_Grade表中查询课程代号（CourseCode）为“110001”、成绩（Grade）在70分到90分之间的学生代号（StudentCode）及成绩。
SELECTStudentCode,CourseCode,Grade
FROMT_Grade
WHERECourseCode='110001'ANDGradeBETWEEN70AND90
此例的WHERE子句也可以改为：
WHERECourseCode='110001'ANDGrade>=70ANDGrade<=90
【例4-31】查询T_Student表中不在1986年到1995年中出生的学生学号和姓名。
SELECTStudentCode,StudentName,Birthday
FROMT_Student
WHEREBirthdayNOTBETWEEN'1986-01-01'AND'1995-12-12'
【例4-32】查询T_Student表中班号（ClassCode）为“061121”、“071221”、“081221”的学生。
SELECTStudentCode,StudentName,Sex,ClassCode
FROMT_Student
WHEREClasscodeIN（'061121','071221','081221'）
此例的WHERE子句也可以改为：
WHEREClassCode='061121'ORClassCode='071221'ORClassCode='081221'
【例4-33】查询T_Course表中课程名称（CourseName）中包含“化学”两个字的课程代号（CourseCode）及课程名称。
查询结果如图4-40所示。
SELECTCourseCode,CourseName
FROMT_Course
WHERECourseNameLIKE'%化学%'
【例4-34】查询未住校学生的选课及成绩情况。
要求显示学生姓名（StudentName）、课程代号（CourseCode）、成绩（Grade）、是否住校（LiveInDorm）。
查询结果如图4-41所示。
SELECTStudentName,CourseCode,Grade,LiveinDorm
FROMT_Grade,T_Student
WHERELiveInDorm=0ANDT_Grade.StudentCode=T_Student.StudentCode
【例4-35】使用内联接查询未住校学生的选课及成绩情况。
SELECTStudentName,CourseCode,Grade,LiveinDorm
FROMT_StudentINNERJOINT_Grade
ONT_Student.StudentCode=T_Grade.StudentCode
WHERELiveInDorm=0
【例4-36】使用左外联接查询未住校学生的选课及成绩情况。
SELECTStudentName,CourseCode,Grade,LiveInDorm
FROMT_StudentLEFTOUTERJOINT_Grade
ONT_Student.StudentCode=T_Grade.StudentCode
WHERELiveInDorm=0
【例4-37】使用右外联接查询未住校学生的选课及成绩情况。
SELECTStudentName,CourseCode,Grade,LiveInDorm
FROMT_StudentRIGHTOUTERJOINT_Grade
ONT_Student.StudentCode=T_Grade.StudentCode
WHERELiveInDorm=0
【例4-38】查询化工学院开设的课程的选修学生和考试成绩。
要求显示课程代号（CourseCode）、课程名（CourseName）、学号（StudentCode）、成绩（Grade）。
SELECTT_Course.CourseCode,T_Course.CourseName,StudentCode,Academy,Grade
FROMT_GradeRIGHTJOINT_Course
ONT_Grade.CourseCode=T_Course.CourseCode
WHEREAcademy='化工学院'
【例4-39】使用交叉联接列出所有学生需选的所有课程情况。
SELECTStudentName,CourseCode
FROMT_StudentCROSSJOINT_Grade
【例4-40】查询班号是“051011”的学生姓名、所选课程名称和成绩。
该查询需要用到3张表T_Student、T_Course和T_Grade，查询结果如图4-44所示。
查询语句如下：
SELECTStudentName,CourseName,Grade
FROMT_StudentJOINT_Grade
ONT_Student.StudentCode=T_Grade.StudentCode
JOINT_CourseONT_Grade.CourseCode=T_Course.CourseCode
WHEREClassCode='051011'
【例4-41】按住校与否统计学生的平均成绩。
查询结果如图4-45所示。
SELECTLiveInDorm,STR（AVG（Grade）,5,2）AS平均成绩
FROMT_StudentJOINT_Grade
ONT_Student.StudentCode=T_Grade.StudentCode
GROUPBYLiveInDorm
【例4-42】统计课程代号（CourseCode）大于“110006”的各门课程的选课人数。
查询结果如图4-47所示。
SELECTCourseCode,Count（StudentCode）AS选课人数
FROMT_Grade
GROUPBYCourseCode
HAVINGCourseCode>'110006'
【例4-43】按成绩（Grade）升序显示T_Grade表中的所有数据。
SELECT*
FROMT_Grade
ORDERBYGrade
【例4-44】按姓名（StudentName）升序＋出生日期（Birthday）降序查询学生及其班级信息，要求显示学生姓名（StudentName）、班级名称（ClassName）、及出生日期（Birthday）。
SELECTStudentCode,StudentName,ClassName,Birthday
FROMT_StudentJOINT_Class
ONT_Student.ClassCode=T_Class.ClassCode
ORDERBYStudentnameASC,BirthdayDESC
【例4-45】查询“李佳婷”同班同学的学号、姓名。
SELECTT_Student.StudentCode,T_Student.StudentName,T_Student.ClassCode
FROMT_Student
WHERET_Student.ClassCode=
（SELECTT_Student.ClassCode
FROMT_Student
WHERET_Student.StudentName='李佳婷'）
【例4-46】查询选修了课程代码为“110001”的学生的学号、姓名和班级代号。
查询结果如图4-50所示。
SELECTStudentCode,StudentName,ClassCode
FROMT_Student
WHEREStudentCodeIN
（SELECTStudentCode
FROMT_GradeWHERECourseCode='110001'）
或
SELECTStudentCode,StudentName,ClassCode
FROMT_Student
WHEREEXISTS
（SELECT*
FROMT_Grade
WHERET_Student.StudentCode=T_Grade.StudentCode
ANDT_Grade.CourseCode='110001'）
【例4-47】向T_Class表插入一条记录。
INSERTINTOT_Class
VALUES（'081221','软件081','120002'）
【例4-48】向T_Student表插入一条记录。
INSERTINTOT_Student（StudentCode,Sex,StudentName,ClassCode）
VALUES（'08011106','女','张军','071221'）
【例4-49】向T_StudentTest表插入记录,数据为T_Student中所有女同学。
INSERTINTOT_StudentTest
SELECTStudentCode,StudentName,Sex,LiveInDormFromT_StudentWHERESex='女'
【例4-50】更新T_Student表中StudentCode为“08011106”的记录信息，将其电话字段（Telephone）的值改为“67792280”。
UPDATET_Student
SETTelephone='67792280'
WHEREStudentCode='08011106'
【例4-50】更新T_Course表中信息，将所有开课学院（Academy）为“计算机学院”的课程的实验课时（LabTime）增加10%,上课课时增加10。
UPDATET_Course
SETLabTime=LabTime*（1+0.1）,CourseTime=CourseTime+10
WHEREAcademy='计算机学院'
【例4-51】删除T_Student表中StudentCode字段值为“08011106”的记录。
DELETEFROMT_Student
WHEREStudentCode='08011106'
【例4-52】删除T_Student表中选课平均成绩低于60的学生信息。
DELETEFROMT_Student
WHEREStudentCodeIN
（SELECTStudentCodeFROMT_Grade
GROUPBYStudentCode
HAVINGAVG（Grade）<60）
【例4-53】定义变量c_code为长度为6的定长字符串，score为精度为1的浮点型，并分别用SET和SELECT语句为它们赋值，然后用这两个变量查询T_Grade表中课程代码为“110006”且成绩低于90分的记录。
执行结果如图4-51所示：
DECLARE@c_codechar（6）,@scoreNumeric（3,1）
SET@c_code='130001'
SELECT@score=80
SELECT*
FROMT_Grade
WHERECourseCode=@c_codeANDGrade<@score
【例4-54】将学号为“05101103”的学生姓名存放到变量@sname中。
DECLARE@snamevarchar（16）
SELECT@sname=
（SELECTStudentName
FROMT_Student
WHEREStudentCode='05101103'）
SELECT@snameas'姓名'
【例4-55】统计学号为“05101101”的学生的选课数目，如果不少于三门课就显示“你选了××门课。
很好，你完成了任务！
“否则显示“你选了××门课。
选课太少，加油！
”。
（其中××表示选课数目）。
执行结果见图4-53。
USESchool
DECLARE@cnsmallint,@textvarchar（100）
SET@cn=（SELECTcount（StudentCode）
FROMT_Grade
WHEREStudentCode='05101101'）
IF@cn>=3
BEGIN
SET@text='你选了'+CAST（@cnASchar
（2））
/*CAST函数将@cn的值转换为长度为2的字符数据*/
SET@text=@text+'门课。
很好，你完成了任务！
'
END
ELSE
BEGIN
SET@text='你选了'+CAST（@cnASchar
（2））
SET@text=@text+'门课。
选课太少，加油！
'
END
SELECT@textAS选课提示
【例4-56】计算10！
。
执行结果见图4-54。
DECLARE@pint,@ismallint,@textvarchar（100）
SET@p=1
SET@i=1
WHILE@i<=10
BEGIN
SET@p=@p*@i
SET@i=@i+1
END
SET@text='1×2×3×……×10='+CAST（@pASchar（10））
SELECT@textAS计算结果
【例4-57】查询07级女同学的住校情况。
执行结果见图4-55。
SELECTStudentNameAS'姓名',
CASELiveInDorm
WHEN0THEN'未住校'
WHEN1THEN'住校'
ENDAS'是否住校'
FROMT_Student
WHERESex='女'ANDLEFT（StudentCode,2）='07'
【例4-58】统计每个学生平均成绩并划分等级。
执行结果如图4-56所示。
SELECTStudentCodeAS'学号',STR（AVG（Grade）,5,2）AS'平均成绩',
CASE
WHENAVG（Grade）>=90THEN'A'
WHENAVG（Grade）>=80THEN'B'
WHENAVG（Grade）>=70THEN'C'
WHENAVG（Grade）>=60THEN'D'
WHENAVG（Grade）<60THEN'E'
ENDAS'等级'
FROMT_GradeGROUPBYStudentCode
【例4-59】在数据库School中，建视图V_StudentGrade，查询学生成绩情况。
1）展开School数据库文件夹，右击“视图”对象，在弹出的快捷菜单上选择“新建视图”命令，打开新建视图对话框，该对话框包括四个窗格：
上格是表及关系窗口，中格为视图选择表中列的网格，然后是SQL语句窗格，下格是结果窗格。
2）右单击对话框窗口上格，在快捷菜单中选择“添加表”命令，显示添加表对话框。
3）在添加表对话框中选择与视图有关的表、视图或函数（通过Ctrl或Shift键可选择多项），然后单击【添加】按钮；或者直接双击要添加的表等，即可将其添加到视图的查询中。
这里选择表：
T_Student、T_Course和T_Grade。
4）选择的表添加到窗口的上格，单击每个表字段前的复选框，可将该字段添加到视图中。
也可在所示窗口的第二个子窗口选择视图字段，并可指定列的别名、排序方式和规则等。
在选择过程中，第三个子窗口中的SELECT语句也会随之自动改变。
也可直接在该子窗口中输入SELECT语句。
这里选择StudentName、CourseName和Grade三个字段。
5）单击工具栏上的 ！
按钮执行，结果显示窗口的第三个子窗口。
6）单击工具栏上的 保存 按钮，在弹出的另存为对话框中为视图命名，本例输入“V_StudentGrade”，单击【确定】按钮保存视图，从而完成视图创建。
【例4-60】在数据库School中，建立视图V_StudentAvgGrade,查询学生的平均成绩。
1）在新建视图窗口中上格选择表T_Student和T_Grade；
2）在字段网格中选择字段和设定汇总字段，或直接在SQL语句窗格中辅助完成语句：
SELECTStudentName,AVG（Grade）AS平均成绩
FROMT_GradeINNERJOIN
T_StudentONT_Grade.StudentCode=T_Student.StudentCode
GROUPBYStudentName
3）运行查看视图结果，保存该视图为V_StudentAvgGrade。
【例4-61】在数据库School中，建立视图V_StudentAvgGradeExcellent,查询平均成绩大于等于90学生。
该例可以参照前面方法在基本表T_Student和T_Grade上创建。
但由于视图的内容也可以来自另一个视图，本例基于V-StudentAvgGrade完成。
各窗格设置及查询结果如图4-61所示。
【例4-62】在数据库School中，从视图V_StudentGrade修改陈佳迪的成绩。
UpdateV_StudentGradeSetGrade=33WHEREStudentName='陈佳迪'
【例4-63】在School数据库中，创建存储过程proc_Course，
查询所有课程信息。
1）打开企业管理器，展开School数据库文件夹，右单击“存储过程”，在弹出的快捷菜单上选择“新建存储过程”命令，打开新建存储过程对话框。
2）将“[OWNER]”和“[PROCEDURENAME]”参数分别替换成存储过程所有者的名称和存储过程的名称。
默认为dbo和[PROCEDURENAME]。
3）从“文本”列表框的第二行开始输入存储过程的T-SQL语句。
这里输入的T-SQL
升级会员 