SQLServer数据库课程实训教学大纲Word文件下载.docx
《SQLServer数据库课程实训教学大纲Word文件下载.docx》由会员分享，可在线阅读，更多相关《SQLServer数据库课程实训教学大纲Word文件下载.docx（27页珍藏版）》请在冰点文库上搜索。
实验四
SQL中select语句的使用
实验五
SQL视图与索引的相关操作
实验六
自定义函数
实验七
SQL中存储过程的建立
实验八
SQL中触发器相关操作
实验一、SQL2005软件基本操作及数据库的操作
【实验目的】：
1.掌握SQL2005软件的安装注意事项，软件基本操作
2.掌握使用SQLServer管理平台来创建数据库的操作
3.掌握使用T-SQL语言创建数据库的基本语法
4.掌握使用T-SQL语言删除数据库的语法
【实验内容】：
1.熟悉SQL2005的工作界面及基本操作。
2.为一个基于C/S模式的教学管理系统创建数据库，数据库的名称为TeachingDB，数据库中的文件组和文件配置情况见下表。
文件组
文件
逻辑
文件名
存储路径和物理文件名
初始
大小
最大值
增长
速度
PRIMARY
主数据文件
PFile1
D:
\Teaching\PFile1Data.mdf
4MB
50MB
1MB
UGroup1
次数据文件1
G1File1
\Teaching\G1File1Data.ndf
2MB
次数据文件2
G1File2
E:
\Teaching\G1File2Data.ndf
UGroup2
G2File1
\Teaching\G2File1Data.ndf
日志文件
LogFile1
F:
\Teaching\LogFile1.ldf
步骤1：
在D、E和F盘上分别创建文件夹Teaching用于保存数据库文件。
步骤2：
按照表中的要求，使用T-SQL语句创建数据库TeachingDB。
步骤3：
通过T-SQL语句修改文件组UGroup1的属性，将其配置为默认文件组。
步骤4：
通过T-SQL语句添加一个日志文件，逻辑文件名为LogFile2，存储路径为F:
\Teaching，初始大小为1MB，最大值为50MB，增长速度为1MB。
步骤5：
目前，数据库的大小为12MB，通过将文件组UGroup1中的两个数据文件分别收缩到1MB大小的方式使数据库收缩到10MB大小，使用T-SQL语句实现以上的要求。
步骤6：
通过T-SQL语句备份数据库TeachingDB。
步骤7：
通过T-SQL语句删除数据库TeachingDB。
步骤8：
通过T-SQL语句恢复数据库TeachingDB。
【实验结果】：
--创建数据库TeachingDB
CREATEDATABASETeachingDB
ONPRIMARY
（NAME=PFile1,
FILENAME='
\Teaching\PFile1Data.mdf'
SIZE=4MB,
MAXSIZE=50MB,
FILEGROWTH=1MB）,
FILEGROUPUGroup1
（NAME=G1File1,
\Teaching\G1File1Data.ndf'
SIZE=2MB,
（NAME=G1File2,
\Teaching\G1File2Data.ndf'
FILEGROUPUGroup2
（NAME=G2File1,
\Teaching\G2File1Data.ndf'
SIZE=1MB,
FILEGROWTH=1MB）
LOGON
（NAME=LogFile1,
\Teaching\LogFile1.ldf'
GO
--将UGroup1修改为默认文件组
ALTERDATABASETeachingDB
MODIFYFILEGROUPUGroup1DEFAULT
在【对象资源管理器】窗口中展开【数据库】节点，在数据库TeachingDB上单击鼠标右键，在弹出的快捷菜单中选择【属性】命令弹出【数据库属性】对话框。
切换到【文件】页，单击右下角的【添加】按钮添加一个文件并配置文件的属性如图所示。
单击【确定】按钮管理【数据库属性】对话框。
--收缩数据库文件
USETeachingDB
DBCCSHRINKFILE（'
G1File1'
1）
G1File2'
实验二、数据库表的基本操作
1.掌握以界面的方式建立数据库表
2.掌握SQL2005中常用的数据类型的使用
3.掌握使用SQLServer管理平台来创建数据库的操作
4.掌握使用T-SQL语言创建数据表的基本语法
5.掌握使用T-SQL语言删除数据表的语法
6、掌握使用T-SQL语言修改数据表的语法
首先在C盘根目录创建文件夹Teaching，执行脚本文件“创建与管理表.sql”，创建数据库TeachingDB并将用户自定义文件组UGroup1设置为默认文件组。
使用Transact-SQL在数据库TeachingDB中定义数据类型NameChar，该数据类型使用的基本数据类型为nvarchar，宽度为50，允许有空值。
使用Transact-SQL在数据库TeachingDB中定义表Departments，该表的特定见表1。
表1Departments表的特点
列名
数据类型
允许空
约束
其他
dptCode
nchar（4）
主键
dptName
nvarchar（50）
×
使用Transact-SQL在数据库TeachingDB中定义表Students，该表的特定见表2。
表2Students表的特点
sudID
int
标识列
stdName
NameChar
DOB
datetime
gender
nchar
（2）
DEFAULT女'
classCode
外键
使用Transact-SQL修改Students表的结构，在该表中增加一个新列age，该列是一个计算列，计算表达式为YEAR（GETDATE（））-YEAR（DOB）。
使用Transact-SQL在TeachingDB数据库中创建表Classes，该表的特定见表3。
表3Classes表的特点
className
使用Transact-SQL修改Students表的结构，在该表的classCode列上添加一个外键约束，参照Classes表中的主键列classCode。
使用insert向数据表中插入数据。
Student表：
Class表：
使用update修改数据表中数据：
将student表中所有姓名中包含“小”或“志”的字删除；
步骤9：
使用delete删除数据表student中数据。
--创建一个数据类型，用于存放姓名。
CREATETYPENameChar
FROMnvarchar（50）
--表Departments，用于存放图书的类别信息。
CREATETABLEDepartments
（dptCodenchar（4）PRIMARYKEY,
dptNamenvarchar（50）NOTNULL
）
--表Students，用于存放学生信息。
CREATETABLEStudents
（stdIDintIDENTITYPRIMARYKEY,
stdNameNameCharNOTNULL,
DOBdatetimeNOTNULL,
gendernchar
（2）NOTNULLDEFAULT'
男'
classCodenchar（4）,
dptCodenchar（4）FOREIGNKEYREFERENCESDepartments（dptCode）
ALTERTABLEStudents
ADDageASYEAR（GETDATE（））-YEAR（DOB）
表的定义见图1，外键的定义见图2。
图1表Classes的定义图2表Classes中的外键定义
添加的外键见图3
图3在Students表中添加外键
实验三、数据库表的完整性约束
1.掌握SQL2005中完整性约束的定义
3.掌握使用SQLServer管理平台来创建约束的操作
4.掌握使用T-SQL语言创建约束
5.掌握使用T-SQL语言创建规则
6、掌握使用T-SQL语言创建默认值
注意，首先在C盘根目录创建文件夹Teaching，执行脚本文件“完整性控制.sql”，创建数据库TeachingDB，并在数据库中创建数据表Teachers和Courses。
使用Transact-SQL在数据库TeachingDB中创建数据表Departments，该表的特点见表1，为dptCode列添加主键约束，约束名为PK_Departments_dptCode。
使用Transact-SQL在数据库TeachingDB中创建表Studying，该表的特点见表2，为stdID列和courseID列的组合添加主键约束，约束名为PK_Studying_stdID_courseID。
表2Studying表的特点
stdID
courseID
tchID
semester
nchar（12）
mark
decimal（5,2）
使用Transact-SQL在数据库TeachingDB中修改数据表Departments，为dptName列添加唯一性约束，约束名为UN_Departments_dptName。
使用Transact-SQL在数据库TeachingDB中修改数据表Studying，为mark列添加检查约束，约束名为CK_Studying_mark，要求mark列的取值范围是0-100之间。
使用Transact-SQL在数据库TeachingDB中创建数据表Students，该表的特点见表3，为gender列添加检查约束，约束名为CK_Students_gender，要求gender列的取值只能是“男”或“女”。
表3Students表的特点
'
或'
女'
使用Transact-SQL在数据库TeachingDB中修改数据表Studying，为stdID列添加外键约束，约束名为FK_Studying_stdID，该约束参照Students表中的主键列stdID。
使用Transact-SQL在数据库TeachingDB中修改数据表Studying，为courseID列添加外键约束，约束名为FK_Studying_courseID，该约束参照Courses表中的主键列courseID。
使用Transact-SQL在数据库TeachingDB中创建默认值对象gen，其默认值为‘男’，然后使用可视化工具将该默认值绑定在表Students中的gender列上。
使用在数据库TeachingDB中创建规则对象RL_classcode，要求classcode列的第一位必须是大写字母，后三位必须是数字。
创建数据表Classes，该表的特点见表4，将规则RL_classcode绑定在表Classes中的classcode列上。
表4Classes表的特点
步骤10：
使用Transact-SQL在数据库TeachingDB中删除规则对象RL_classcode，删除默认值对象gen，删除约束FK_Studying_courseID。
INSERTINTODepartmentsVALUES（'
INDE'
'
信息工程系'
ELDE'
电气工程系'
MADE'
机械工程系'
图1在Classes表中插入数据
UPDATEClasses
SETclassName='
08计算机控制技术1班'
WHEREclassCode='
A081'
步骤4使用Transact-SQL删除表Classes中班级编号为A081的班级记录。
答案：
DELETEFROMClasses
不能，因为表Students中的外键classCode参照表Classes中的主键classCode，所以Classes是父表，不能使用TRUNCATETABLE语句清空。
实验四、SQL中select语句的使用
1.掌握SQL中使用SELECT语句进行简单查询
2.掌握SQL中使用SELECT语句进行条件查询
3.掌握使用T-SQL语言对查询结果排序的方法
4.掌握使用T-SQL语言进行分组查询的方法
5.掌握使用into子句定义新表的方法
注意，首先在C盘根目录创建文件夹Teaching，执行脚本文件“数据查询.sql”，创建数据库、表，并向表中导入实验数据。
步骤1现在需要制作一个全校的花名册，包括所有学生的学号、姓名、性别、所在班级名称以及所在系名称，使用SELECT语句实现该功能，结果如下图所示。
步骤2每学期初，学校为不及格（60分以下）和缺考（没有成绩）的学生提供补考的机会。
使用查询语句为教务管理员提供必要的补考信息，如下图所示。
步骤3考试结束后要对每门课程的考试情况进行统计分析，使用SELECT语句为教务管理员进行课程成绩分析提供必要的数据，如下图所示。
SELECTs.stdID,s.stdName,s.gender,c.className,d.dptName
FROMStudentssLEFTJOINClassescONs.classCode=c.classCode
LEFTJOINDepartmentsdONs.dptCode=d.dptCode
SELECTs.stdID,s.stdName,r.courseName,t.tchName,d.dptName
FROMStudyingyJOINStudentssONy.stdID=s.stdID
JOINCoursesrONy.courseID=r.courseID
JOINTeacherstONy.tchID=t.tchID
LEFTJOINDepartmentsdONt.dptCode=d.dptCode
WHEREy.markISNULLORy.mark<
60
SELECTr.courseIDAS课程编号,r.courseNameAS课程名称,MAX（y.mark）AS最高分,MIN（y.mark）AS最低分,AVG（y.mark）AS平均分
FROMStudyingyJOINCoursesrONy.courseID=r.courseID
GROUPBYr.courseID,r.courseName
实验五、SQL视图与索引的相关操作
1.掌握SQL中以界面的方式创建、改名和删除视图的方法
2.掌握使用T-SQL命令创建视图和删除视图的方法
3.掌握利用视图更新、插入和删除记录的方法
4.掌握创建索引、查看、修改和删除索引的方法
1、视图的有关操作：
注意，首先在C盘根目录创建文件夹Teaching，执行脚本文件“视图的应用.sql”，创建数据库、表，并向表中导入实验数据。
步骤1使用Transact-SQL在TeachingDB数据库中创建一个视图ViewMark，统计每名学生在2008-2009-02学期选修课程的门数以及课程平均分。
视图中包含学生的学号、姓名、选修课程门数、平均分。
要求加密该视图的定义。
步骤2使用可视化的方式删除视图ViewMark，写出步骤。
步骤3使用Transact-SQL在TeachingDB数据库中创建一个视图ViewStudying。
显示每名学生的学号、姓名、班级名称、所选修课程名称、学期、任课教师姓名、任课教师所在系以及课程成绩。
步骤4使用可视化的方式修改视图ViewStudying，要求视图中包含学生性别，写出步骤。
步骤5使用Transact-SQL在视图ViewStudying中统计每名教师所任课程的平均分。
2、索引的操作：
注意，首先在C盘根目录创建文件夹Teaching，执行脚本文件“索引的创建与维护.sql”，创建数据库TeachingDB，并在数据库中创建数据表Students、Teachers、Courses和Studying，并录入实验数据。
步骤1使用Transact-SQL为数据库TeachingDB的数据表Studying创建唯一的聚集索引，索引名为IX_Studying_sidcid，索引关键值是stdID和courseID，并将该索引创建在文件组UGroup1上。
步骤2使用可视化工具为数据库TeachingDB的数据表Studying创建非聚集索引，索引名为IX_Studying_tid，索引关键值是tchID，该索引的填充因子设为80%，且不在tempdb中存储临时排序结果。
CREATEVIEWViewMark（学号,姓名,选修门数,平均分）
WITHENCRYPTION
AS
SELECTs.stdID,s.stdName,COUNT（y.courseID）,AVG（y.mark）
FROMStudentssLEFTJOINStudyingyONs.stdID=y.stdID
WHEREy.semester='
200802009-02'
GROUPBYs.stdID,s.stdName
在【对象资源管理器】窗口中展开TeachingDB数据库中的【视图】节点（如果看不到用户定义的视图，则刷新该节点），在视图ViewMark上单击鼠标右键，在弹出的快捷菜单中选择【删除】命令，在弹出的【删除对象】对话框中单击【确定】按钮即可删除该视图。
CREATEVIEWViewStudying
SELECTs.stdID,s.stdName,l.className,c.courseName,y.semester,t.tchName,d.dptName,y.mark
FROMStudentssJOINStudyingyONs.stdID=y.stdID
JOINClasseslONs.classCode=l.classCode
JOINCoursescONy.courseID=c.courseID
JOINDepartmentsdONt.dptCode=d.dptCode
步骤4
升级会员 