《SQL Server数据库》课程实训教学大纲.doc

1、数据库技术实验计划一、实验目的与要求1. 实验目的：掌握 SQL 数据库的基本操作，会创建数据库和数据库表及表的相关操作，能进一步理解视图、索引和函数的运用，掌握 T-SQL 高级语言的运用，在掌握基本知识的基础上能够运用 SQL 数据库开发出具有一定实用效率的数据库系统，以提高自身的动手能力和基本技能。2. 要求：独立认真完成每次实验内容。二、实验方式1. 由指导教师讲清实验的基本原理、要求，实验目的及注意事项。2. 学生阅读实验指导书。3. 教学实践除巩固课程理论外，还要求学生具备一定的分析和调试的能力。4. 要求学生填写实验报告，记录实验经过、实验结果。实验报告的主要内容包括：（1）实验

2、目的和内容；（2）实验的简单过程；（3）实验的结果。三、考核方法1. 实验后，学生将按要求写成实验报告文档，符合实验教学的要求并得到指导教师的认可。2. 指导教师对实验报告进行批改、评分。四、实验项目及学时分配实验项目一览表序号 实验项目名称 学时实验一 SQL2005 软件操作及数据库的操作 2实验二 数据库表的基本操作 4实验三 完整性约束的实现 4实验四 SQL 中 select 语句的使用 4实验五 SQL 视图与索引的相关操作 4实验六 自定义函数 4实验七 SQL 中存储过程的建立 4实验八 SQL 中触发器相关操作 4实验一、SQL 2005 软件基本操作及数据库的操作【实验目的

3、】：1.掌握 SQL2005 软件的安装注意事项，软件基本操作2.掌握使用 SQL Server 管理平台来创建数据库的操作3.掌握使用 T-SQL 语言创建数据库的基本语法4.掌握使用 T-SQL 语言删除数据库的语法【实验内容】：1.熟悉 SQL2005 的工作界面及基本操作。2. 为一个基于 C/S 模式的教学管理系统创建数据库，数据库的名称为TeachingDB，数据库中的文件组和文件配置情况见下表。文件组 文件 逻辑文件名 存储路径和物理文件名 初始大小 最大 值 增长速度PRIMARY 主数据文件 PFile1 D:TeachingPFile1Data.mdf 4MB 50MB 1

4、MB次数据文件 1 G1File1 D:TeachingG1File1Data.ndf 2MB 50MB 1MBUGroup1次数据文件 2 G1File2 E:TeachingG1File2Data.ndf 2MB 50MB 1MBUGroup2 次数据文件 1 G2File1 E:TeachingG2File1Data.ndf 1MB 50MB 1MB日志文件 LogFile1 F:TeachingLogFile1.ldf 2MB 50MB 1MB步骤 1： 在 D、E 和 F 盘上分别创建文件夹 Teaching 用于保存数据库文件。步骤 2：按照表中的要求，使用 T-SQL 语句创建数

5、据库 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 语句删除数据库 Teac

6、hingDB。步骤 8：通过 T-SQL 语句恢复数据库 TeachingDB。【实验结果】：步骤 2：-创建数据库 TeachingDBCREATE DATABASE TeachingDBON PRIMARY(NAME=PFile1,FILENAME=D:TeachingPFile1Data.mdf,SIZE=4MB,MAXSIZE=50MB,FILEGROWTH=1MB),FILEGROUP UGroup1(NAME=G1File1,FILENAME=D:TeachingG1File1Data.ndf,SIZE=2MB,MAXSIZE=50MB,FILEGROWTH=1MB),(NAME=

7、G1File2,FILENAME=E:TeachingG1File2Data.ndf,SIZE=2MB,MAXSIZE=50MB,FILEGROWTH=1MB),FILEGROUP UGroup2(NAME=G2File1,FILENAME=E:TeachingG2File1Data.ndf,SIZE=1MB,MAXSIZE=50MB,FILEGROWTH=1MB)LOG ON(NAME=LogFile1,FILENAME=F:TeachingLogFile1.ldf,SIZE=2MB,MAXSIZE=50MB,FILEGROWTH=1MB)GO步骤 3：-将 UGroup1 修改为默认文件组

8、ALTER DATABASE TeachingDBMODIFY FILEGROUP UGroup1 DEFAULTGO步骤 4：在【对象资源管理器】窗口中展开【数据库】节点，在数据库 TeachingDB 上单击鼠标右键，在弹出的快捷菜单中选择【属性】命令弹出【数据库属性】对话框。切换到【文件】页，单击右下角的【添加】按钮添加一个文件并配置文件的属性如图所示。单击【确定】按钮管理【数据库属性】对话框。步骤 5：-收缩数据库文件USE TeachingDBGODBCC SHRINKFILE(G1File1,1)GODBCC SHRINKFILE(G1File2,1)GO实验二、数据库表的基本操作

9、【实验目的】：1.掌握以界面的方式建立数据库表2.掌握 SQL2005 中常用的数据类型的使用 3.掌握使用 SQL Server 管理平台来创建数据库的操作4.掌握使用 T-SQL 语言创建数据表的基本语法5.掌握使用 T-SQL 语言删除数据表的语法6、掌握使用 T-SQL 语言修改数据表的语法【实验内容】：首先在 C 盘根目录创建文件夹 Teaching，执行脚本文件“创建与管理表.sql”， 创建数据库 TeachingDB 并将用户自定 义文件组 UGroup1 设置为默认文件组。步骤 1：使用 Transact-SQL 在数据库 TeachingDB 中定义数据类型 NameCha

10、r，该数据类型使用的基本数据类型为 nvarchar，宽度为 50，允许有空值。步骤 2：使用 Transact-SQL 在数据库 TeachingDB 中定义表 Departments，该表的特定见表 1。表 1 Departments 表的特点列名 数据类型 允许空 约束 其他dptCode nchar(4) 主键dptName nvarchar(50) 步骤 3：使用 Transact-SQL 在数据库 TeachingDB 中定义表 Students，该表的特定见表2。表 2 Students 表的特点列名 数据类型 允许空 约束 其他sudID int 主键 标识列stdName N

11、ameChar DOB datetime gender nchar(2) DEFAULT 女classCode nchar(4)dptCode nchar(4) 外键步骤 4：使用 Transact-SQL 修改 Students 表的结构，在该表中增加一个新列 age，该列是一个计算列，计算表达式为 YEAR(GETDATE()-YEAR(DOB)。步骤 5：使用 Transact-SQL 在 TeachingDB 数据库中创建表 Classes，该表的特定见表3。表 3 Classes 表的特点列名 数据类型 允许空 约束 其他classCode nchar(4) 主键className

12、nvarchar(50) dptCode nchar(4) 外键步骤 6：使用 Transact-SQL 修改 Students 表的结构，在该表的 classCode 列上添加一个外键约束，参照 Classes 表中的主键列 classCode。步骤 7：使用 insert 向数据表中插入数据。Student 表：Class 表：步骤 8：使用 update 修改数据表中数据：将 student 表中所有姓名中包含“小”或“志”的字删除；步骤 9：使用 delete 删除数据表 student 中数据。【实验结果】：步骤 1：USE TeachingDBGO-创建一个数据类型，用于存放姓名。

13、USE TeachingDBGOCREATE TYPE NameCharFROM nvarchar(50)GO步骤 2：-表 Departments，用于存放图书的类别信息。USE TeachingDBGOCREATE TABLE Departments(dptCode nchar(4) PRIMARY KEY,dptName nvarchar(50) NOT NULL)GO步骤 3：-表 Students，用于存放学生信息。USE TeachingDBGOCREATE TABLE Students(stdID int IDENTITY PRIMARY KEY,stdName NameChar

14、 NOT NULL,DOB datetime NOT NULL,gender nchar(2) NOT NULL DEFAULT 男,classCode nchar(4),dptCode nchar(4) FOREIGN KEY REFERENCES Departments(dptCode)GO步骤 4：USE TeachingDBGOALTER TABLE StudentsADD age AS YEAR(GETDATE()-YEAR(DOB)GO步骤 5：表的定义见图 1，外键的定义见图 2。图 1 表 Classes 的定义 图 2 表 Classes 中的外键定义步骤 6：添加的外键见图

15、 3图 3 在 Students 表中添加外键实验三、数据库表的完整性约束【实验目的】：1.掌握 SQL2005 中完整性约束的定义 3.掌握使用 SQL Server 管理平台来创建约束的操作4.掌握使用 T-SQL 语言创建约束5.掌握使用 T-SQL 语言创建规则6、掌握使用 T-SQL 语言创建默认值【实验内容】：注意，首先在 C 盘根目录创建文件夹 Teaching，执行脚本文件“完整性控制.sql ”，创建数据库 TeachingDB，并在数据库中创建数据表 Teachers 和 Courses。步骤 1： 使用 Transact-SQL 在数据库 TeachingDB 中创建数据

16、表 Departments，该表的特点见表 1，为 dptCode 列添加主键约束，约束名为 PK_Departments_dptCode。表 1 Departments 表的特点列名 数据类型 允许空 约束 其他dptCode nchar(4) 主键dptName nvarchar(50) 步骤 2：使用 Transact-SQL 在数据库 TeachingDB 中创建表 Studying，该表的特点见表2，为 stdID 列和 courseID 列的组合添加主键约束，约束名为PK_Studying_stdID_courseID。表 2 Studying 表的特点列名 数据类型 允许空 约束

17、 其他stdID int courseID int 主键tchID int semester nchar(12) mark decimal(5,2)步骤 3： 使用 Transact-SQL 在数据库 TeachingDB 中修改数据表 Departments，为dptName 列添加唯一性约束，约束名为 UN_Departments_dptName。步骤 4： 使用 Transact-SQL 在数据库 TeachingDB 中修改数据表 Studying，为 mark 列添加检查约束，约束名为 CK_Studying_mark，要求 mark 列的取值范围是 0-100 之间。步骤 5： 使

18、用 Transact-SQL 在数据库 TeachingDB 中创建数据表 Students，该表的特点见表 3，为 gender 列添加检查约束，约束名为 CK_Students_gender，要求 gender 列的取值只能是“男”或“女” 。表 3 Students 表的特点列名 数据类型 允许空 约束 其他sudID int 主键 标识列stdName NameChar DOB datetime gender nchar(2) 男 或女classCode nchar(4)dptCode nchar(4)步骤 6： 使用 Transact-SQL 在数据库 TeachingDB 中修改数

19、据表 Studying，为 stdID列添加外键约束，约束名为 FK_Studying_stdID，该约束参照 Students 表中的主键列stdID。步骤 7： 使用 Transact-SQL 在数据库 TeachingDB 中修改数据表 Studying，为 courseID列添加外键约束，约束名为 FK_Studying_ courseID，该约束参照 Courses 表中的主键列courseID。步骤 8：使用 Transact-SQL 在数据库 TeachingDB 中创建默认值对象 gen，其默认值为男 ，然后使用可视化工具将该默认值绑定在表 Students 中的 gender

20、 列上。步骤 9：使用在数据库 TeachingDB 中创建规则对象 RL_classcode，要求 classcode 列的第一位必须是大写字母，后三位必须是数字。创建数据表 Classes，该表的特点见表 4，将规则 RL_classcode 绑定在表 Classes 中的 classcode 列上。表 4 Classes 表的特点列名 数据类型 允许空 约束 其他classCode nchar(4) 主键className nvarchar(50) dptCode nchar(4) 步骤 10：使用 Transact-SQL 在数据库 TeachingDB 中删除规则对象 RL_clas

21、scode，删除默认值对象 gen，删除约束 FK_Studying_courseID。【实验结果】：步骤 1：USE TeachingDBGOINSERT INTO Departments VALUES(INDE,信息工程系)GOINSERT INTO Departments VALUES(ELDE,电气工程系)GOINSERT INTO Departments VALUES(MADE,机械工程系)GO步骤 2：图 1 在 Classes 表中插入数据步骤 3：UPDATE ClassesSET className=08 计算机控制技术 1 班WHERE classCode=A081GO步骤

22、 4 使用 Transact-SQL 删除表 Classes 中班级编号为 A081 的班级记录。答案：DELETE FROM ClassesWHERE classCode=A081GO步骤 5：不能，因为表 Students 中的外键 classCode 参照表 Classes 中的主键 classCode，所以 Classes 是父表，不能使用 TRUNCATE TABLE 语句清空。实验四、SQL 中 select 语句的使用【实验目的】：1.掌握 SQL 中使用 SELECT 语句进行简单查询2.掌握 SQL 中使用 SELECT 语句进行条件查询3.掌握使用 T-SQL 语言对查询结

23、果排序的方法4.掌握使用 T-SQL 语言进行分组查询的方法5.掌握使用 into 子句定义新表的方法【实验内容】：注意，首先在 C 盘根目录创建文件夹 Teaching，执行脚本文件“数据查询.sql ”，创建数据库、表，并向表中导入实验数据。步骤 1 现在需要制作一个全校的花名册，包括所有学生的学号、姓名、性别、所在班级名称以及所在系名称，使用 SELECT 语句实现该功能，结果如下图所示。步骤 2 每学期初，学校为不及格（60 分以下）和缺考（没有成绩）的学生提供补考的机会。使用查询语句为教务管理员提供必要的补考信息，如下图所示。步骤 3 考试结束后要对每门课程的考试情况进行统计分析，使

24、用 SELECT 语句为教务管理员进行课程成绩分析提供必要的数据，如下图所示。【实验结果】：步骤 1：SELECT s.stdID,s.stdName,s.gender,c.className,d.dptNameFROM Students s LEFT JOIN Classes c ON s.classCode=c.classCodeLEFT JOIN Departments d ON s.dptCode=d.dptCodeGO步骤 2：SELECT s.stdID,s.stdName,r.courseName,t.tchName,d.dptNameFROM Studying y JOIN S

25、tudents s ON y.stdID=s.stdIDJOIN Courses r ON y.courseID=r.courseIDJOIN Teachers t ON y.tchID=t.tchIDLEFT JOIN Departments d ON t.dptCode=d.dptCodeWHERE y.mark IS NULL OR y.markdbo.FunAvgMark(3)GO步骤 3：USE TeachingDBGOCREATE FUNCTION FunMark()RETURNS Mark TABLE(stdID int,numbers int,maxMark decimal(5

26、,2),minMark decimal(5,2),avgMark decimal(5,2)AS BEGININSERT INTO MarkSELECT stdID,COUNT(mark),MAX(mark),MIN(mark),AVG(mark)FROM StudyingGROUP BY stdIDRETURNENDGO步骤 4 使用 Transact-SQL 在数据库 TeachingDB 中创建一个角色 TRole1。答案：SELECT s.stdName,m.*FROM Students s JOIN dbo.FunMark() m ON s.stdID=m.stdIDGO结果如下图所示

27、。实验七、SQL 中存储过程的建立【实验目的】：1.了解存储过程的优点2.掌握常用的系统存储过程3.掌握如何创建存储过程4.掌握如何调用存储过程【实验内容】：注意，首先在 C 盘根目录创建文件夹 Teaching，执行脚本文件“存储过程的创建与管理.sql”，创 建数据库 TeachingDB 和表。步骤 1 使用 Transact-SQL 创建一个存储过程 PrcStudentIns，通过该存储过程可以向Students 表中插入一条学生记录。如果插入成功，返回插入的行数。如果插入失败，返回-1。步骤 2 使用 Transact-SQL 执行刚刚创建的存储过程，插入一条学生记录。步骤 3 使

28、用 Transact-SQL 创建一个存储过程，根据指定的学号删除一名学生的记录。如果删除成功，返回删除的行数。如果删除失败，返回-1 。步骤 4 使用 Transact-SQL 执行刚刚创建的存储过程，删除一条学生记录，并观察结果。【实验结果】：步骤 1：USE TeachingDBGOCREATE PROCEDURE PrcStudentInsstdName nvarchar(50),DOB datetime,gender nchar(2),classCode nchar(4),dptCode nchar(4),n int OUTPUTASBEGIN TRYINSERT INTO Stud

29、ents(stdName,DOB,gender,classCode,dptCode)VALUES(stdName,DOB,gender,classCode,dptCode)SET n=ROWCOUNTEND TRYBEGIN CATCHPRINT An Error Occurred!SET n=-1ROLLBACK TRANSACTIONEND CATCHGO步骤 2：DECLARE result intEXEC PrcStudentIns 刘岩,1980-8-9,男,I071,INDE,result OUTPUTGO步骤 3：USE TeachingDBGOCREATE PROCEDURE

30、PrcStudentDelstdID int,n int OUTPUTASBEGIN TRYDELETE FROM Students WHERE stdID=stdIDSET n=ROWCOUNTEND TRYBEGIN CATCHPRINT An Error Occurred!SET n=-1ROLLBACK TRANSACTIONEND CATCHGO步骤 4：DECLARE result intEXEC PrcStudentDel 11,result OUTPUTSELECT resultGO实验八、SQL 中触发器相关操作【实验目的】：1.了解为什么需要触发器2.理解触发器的工作原理3

31、.掌握如何使用 inserted 表和 deleted 表4.掌握如何创建：INSERT 触发器、UPDATE 触发器、DELETE 触发器【实验内容】：注意，首先在 C 盘根目录创建文件夹 Teaching，执行脚本文件“触发器的创建与管理.sql”，创 建数据库 TeachingDB 和表。步骤 1 首先创建一个视图 ViewTeacherDepartment，包括教师的详细信息以及其所在系的名称。在该视图上创建一个针对删除操作的 INSTEAD OF 触发器 TrViewDelete，将从视图中删除记录的操作转换为从 Teachers 表中删除记录。从视图中删除 tchID 为 4 的教

32、师记录，观察结果。的名称。在该视图上创建一个针对删除操作的 INSTEAD OF 触发器TrViewDelete，将从视图中删除记录的操作转换为从 Teachers 表中删除记录。从视图中删除 tchID 为 4 的教师记录，观察结果。步骤 2 创建一个触发器 TrDatabaseDropAlter，当用户在服务器上删除和修改数据库时，提示用户不允许删除和修改数据库。步骤 3 在对象资源管理器中找到触发器 TrDatabaseDropAlter 并将它删除。【实验结果】：步骤 1：-创建视图USE TeachingDBGOCREATE VIEW ViewTeacherDepartmentASS

33、ELECT t.*,d.dptNameFROM Teachers t JOIN Departments d ON t.dptCode=d.dptCodeGO-创建触发器CREATE TRIGGER TrViewDelete ON ViewTeacherDepartment INSTEAD OF DELETEASDELETE FROM Teachers WHERE tchID IN(SELECT tchID FROM deleted)GO-从视图中删除记录DELETE FROM ViewTeacherDepartment WHERE tchID=4GO步骤 2：CREATE TRIGGER TrDatabaseDropAlter ON ALL SERVER AFTER DROP_DATABASE,ALTER_DATABASEASBEGINPRINT 禁止删除和修改数据库！ROLLBACK TRANSACTIONENDGO步骤 3：在【对象资源管理器】窗口中展开【服务器对象】节点，展开【服务器触发器】节点，右键单击触发器 TrDatabaseDropAlter，在弹出的快捷菜单中执行【删除】命令，如下图所示。在随后弹出的【删除对象】对话框中单击【确定】按钮，删除该触发器。