第四章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