SQL-Server2005实训指导.doc

数据库编程实验指导书实训一 SQL Server2005的安装与配置2实训二 数据库的创建和管理5实训三 数据库的备份和恢复9实训四 数据库表的设计与管理14实训五 数据库数据完整性应用21实训六 创建和维护索引24实训七 查询数据26实训八 T-SQL流程控制语句编程及函数、游标的创建使用30实训九 创建和使用视图35实训十 存储过程的创建及调用38实训十一 触发器的创建和应用40实训一 SQL Server2005的安装与配置一、 实训目的：1能够熟练安装SQL Server 2005开发版2掌握常用的SQL Server 2005服务器的配置二、实训环境：Windows XP 系统、SQL Server 2005环境三、实训内容：1安装SQL Server 2005，并在安装时将登录身份验证模式设置为“SQL Server 和windows”验证，其他可选择默认设置，一定要记住sa帐户的密码。2. 利用SQL Server Management Studio连接到SQL Server数据库引擎服务器。3. 更改SQL Server 2005服务器身份验证模式和设置sa账号口令。4. 停止和暂停服务。5. 注册和删除服务器。6. 配置服务的启动模式。7. 配置服务器。四、实训过程：1安装SQL Server 2005并设置相应的身份验证模式。2操作1：执行“开始”-“程序”-“Microsoft SQL Server 2005”-“SQL Server Management Studio”程序项。操作2：在“服务器类型”下拉列表框中选择“数据库引擎”；在“服务器名称”下拉列表框中选择本地计算机名，表示本地默认实例；在“身份验证”下拉列表框中选择验证类型“Windows身份验证”，然后单击“连接”按钮。3更改SQL Server 2005服务器身份验证模式和设置sa账号口令。（1）更改SQL Server 2005服务器身份验证模式1）在 SQL Server Management Studio的对象资源管理器中，右键单击服务器，再单击“属性”。2）在“安全性”页上的“服务器身份验证”下，选择新的服务器身份验证模式，再单击“确定”。3）在 SQL Server Management Studio 对话框中，单击“确定”以确认需要重新启动 SQL Server。4）在 SQL Server Management Studio的对象资源管理器中，右键单击服务器，再单击“重新启动”。（2）设置sa账号口令1）在对象资源管理器中，依次展开“安全”、“登录名”，右键单击“sa”，再单击“属性”。2）在“常规”页上，为 sa 登录名创建密码并确认该密码。3）在“状态”页上的“登录”部分中，单击“启用”，然后单击“确定”。（3）分别使用Windows身份验证和SQL Server身份验证连接SQL Server（数据库引擎）服务器。4. 停止和暂停服务。操作1：选择“开始”“所有程序”“Microsoft SQL Server 2005” “配置工具”，选择“SQL Server Configuration Manager”程序项，打开SQL Server配置管理器。操作2：右击服务名称，在快捷菜单可以选择启动、停止、暂停和重新启动SQL Server各项服务。5. 注册和删除服务器。先删除已注册的本地服务器，再重新完成注册。（1）删除已注册的本地服务器在“SQL Server Management Studio”主窗口左侧“已注册的服务器”窗口选择某个已注册的服务器右击，在快捷菜单选择“删除”即可。（2）注册在“SQL Server Management Studio”主窗口左侧“已注册的服务器”窗口右击“数据库引擎”，在快捷菜单选择“新建”项下的“服务器注册”，在“新建服务器注册”对话框，输入各项注册服务器信息，即可完成注册。6. 配置服务的启动模式。（1）启动“SQL Server配置管理器”，单击“SQL Server 2005服务”，右击SQL Server Agent服务，在快捷菜单里选择“属性”。（2）在属性对话框中，切换到“服务”选项卡，单击“启动模式”项右边的下三角按钮，在下拉列表框选择“自动”，单击“确定”完成。7. 配置服务器。在SQL Server Management Studio中的“对象资源管理器”窗口中，右击要配置的服务器名，在快捷菜单中选择“属性”。实训二 数据库的创建和管理一、 实训目的：1掌握系统数据库和用户数据库的相关基本概念2掌握使用SQL Server Management Studio创建、修改和删除数据库的方法3掌握使用T-SQL语句创建、修改和删除数据库的方法二、实训环境：Windows XP 系统、SQL Server 2005三、实训内容：1使用SQL Server Management Studio创建用户数据库“TEST1”。主要数据文件：逻辑文件名为TEST1Data1，物理文件名为Test1Data1.mdf；初始容量为10MB，最大容量为50MB，递增量为1MB。次要数据文件：逻辑文件名为TEST1Data2，物理文件名为Test1Data2.ndf；初始容量为10MB，最大容量为50MB，递增量为1MB。事务日志文件：逻辑文件名为TEST1Log，实际文件名为Test1Log.ldf；初始容量为10MB，最大容量为30MB，递增量为1MB。2使用SQL Server Management Studio修改用户数据库“TEST1”。主要数据文件的容量为20MB，最大容量为60MB，递增量为2MB。事务日志文件的容量为20MB，最大容量为50MB，递增量为2MB。3使用T-SQL语句创建未指定文件的数据库“TEST2”。4使用T-SQL语句创建指定多个文件的数据库“TEST3”。主要数据文件：逻辑文件名为TEST3Data1，物理文件名为Test3Data1.mdf；初始容量为10MB，最大容量为50MB，递增量为1MB。次要数据文件：逻辑文件名为TEST3Data2、TEST3Data3，物理文件名为Test3Data2.ndf，Test3Data3.ndf；初始容量为10MB，最大容量为50MB，递增量为1MB。事务日志文件：逻辑文件名为TEST3Log，实际文件名为Test3Log.ldf；初始容量为10MB，最大容量为30MB，递增量为1MB。5使用T-SQL语句修改TEST3。次要数据文件的容量为20MB，最大容量为60MB，递增量为2MB。6重命名数据库“TEST3”。四、实训过程： 1使用SQL Server Management Studio创建用户数据库“TEST1”。（1）点击“开始程序Microsoft SQL Server 2005SQL Server Management Studio”，打开SQL Server Management Studio窗口，设置好服务器类型、服务器名称、身份验证、用户名和密码，并单击“连接”按钮。（2）在“对象资源管理器”窗口中右击“数据库”节点，弹出快捷菜单，选择“新建数据库”命令，弹出新建数据库窗口。（3）在“常规”选项设置界面的“数据库名称”文本框中输入“TEST1”。在文件名字段中输入数据文件逻辑文件名“TEST1Data1”；设置该文件初始大小为10MB；在“文件增长”标记下选中“按兆字节”设置文件容量递增值为1；在“最大文件大小”标记下，选择“限制文件增长(MB)”为50。同样，在第二行输入事务数据文件信息，并进行相应设置。（4）点击“添加”按钮，添加次要数据文件并按（3）输入次要数据文件的信息，并进行相应设置。（5）点击“确定”按钮完成数据库的创建。2使用SQL Server Management Studio修改用户数据库“TEST1”。（1）经过1（1）、（2）步骤后，在第（3）步中，修改该文件初始大小为20MB；在“文件增长”标记下选中“按兆字节”设置文件容量递增值为2；在“最大文件大小”标记下，选择“限制文件增长(MB)”为60。（2）同样，按以上步骤对事务日志文件进行相应的修改。3使用T-SQL语句创建未指定文件的数据库“TEST2”。create database TEST24使用T-SQL语句创建指定多个文件的数据库“TEST3”。create database TEST3on( name=TEST3Data1, filename=d:数据库Test3Data1.mdf, size=10, maxsize=50, filegrowth=1),( name=TEST3Data2, filename=d:数据库Test3Data2.ndf, size=10, maxsize=50, filegrowth=1),( name=TEST3Data3, filename=d:数据库Test3Data3.ndf, size=10, maxsize=50, filegrowth=1)log on( name=TEST3Log, filename=d:数据库Test3Log.ldf, size=10, maxsize=30, filegrowth=1)5使用T-SQL语句修改TEST3。alter database TEST3modify file(name=TEST3Data2, size=20,maxsize=60,filegrowth=2)goalter database TEST3modify file(name=TEST3Data3, size=20,maxsize=60,filegrowth=2)6重命名数据库“TEST3”。alter database TEST3modify name=测试3实训三 数据库的备份和恢复一、 实训目的：1掌握备份设备的创建方法2掌握数据库的备份和还原方法3掌握数据库的分离和附加方法二、实训环境：Windows XP 系统、 SQL Server 2005三、实训内容：使用企业管理器和T-SQL语句完成下列操作：1. 创建一个名为“test1bak”的备份设备（文件路径及文件名自定）。2使用SQL Server Management Studio创建用户数据库“test1”。主要数据文件：逻辑文件名为test1data1，物理文件名为test1data1.mdf；初始容量为10MB，最大容量为50MB，递增量为1MB。次要数据文件：逻辑文件名为test1data2，物理文件名为test1data2.ndf；初始容量为10MB，最大容量为50MB，递增量为1MB。事务日志文件：逻辑文件名为test1log，实际文件名为test1log.ldf；初始容量为10MB，最大容量为30MB，递增量为1MB。3. 把数据库“test1”完全备份到“test1bak”备份设备上，再建立一个差异备份和一个事务日志备份，追加到完全备份的后面。4删除数据库“test1”，然后用已建立的备份还原数据库test1。5. 将已建立的数据库test1更名为“学生管理”数据库。6. 对“学生管理”数据库执行分离操作。7. 把分离的“学生管理”数据库附加到当前的数据库服务器中。四、实训过程：1. 创建一个名为“test1bak”的备份设备（文件路径及文件名自定）。使用企业管理器：（1）打开SQL Server Management Studio连接上服务器，在“对象资源管理器”中展开“服务器对象”节点，右击“备份设备”，选择“新建备份设备”命令，弹出备份设备对话框。（2）在“备份设备”对话框“设备名称”文本框中输入逻辑备份名“test1bak”，在“文件”文本框中指定相应的物理备份。使用T-SQL语句：sp_addumpdevice disk,test1bak,d:数据库备份设备test1bak.bak2使用SQL Server Management Studio创建用户数据库“test1”。使用企业管理器：（1）点击“开始程序Microsoft SQL Server 2005SQL Server Management Studio”，打开SQL Server Management Studio窗口，设置好服务器类型、服务器名称、身份验证、用户名和密码，并单击“连接”按钮。（2）在“对象资源管理器”窗口中右击“数据库”节点，弹出快捷菜单，选择“新建数据库”命令，弹出新建数据库窗口。（3）在“常规”选项设置界面的“数据库名称”文本框中输入“test1”。在文件名字段中输入数据文件逻辑文件名“test1data1”；设置该文件初始大小为10MB；在“文件增长”标记下选中“按兆字节”设置文件容量递增值为1；在“最大文件大小”标记下，选择“限制文件增长(MB)”为50。同样，在第二行输入事务数据文件信息，并进行相应设置。（4）点击“添加”按钮，添加次要数据文件并按（3）输入次要数据文件的信息，并进行相应设置。（5）点击“确定”按钮完成数据库的创建。使用T-SQL语句：create database test1on( name=test1data1, filename=d:数据库test1data1.mdf, size=10, maxsize=50, filegrowth=1),( name=test1data2, filename=d:数据库test1data2.ndf, size=10, maxsize=50, filegrowth=1)log on( name=test1log, filename=d:数据库test1log.ldf, size=10, maxsize=30, filegrowth=1)3. 把数据库“test1”完全备份到“test1bak”备份设备上，再建立一个差异备份和一个事务日志备份，追加到完全备份的后面。使用企业管理器：(1) “对象资源管理器”窗口中选择test1数据库，单击鼠标右键，执行快捷菜单“所有任务备份”命令。(2)在“备份数据库”对话框中，选择备份类型为“完全数据库备份”。(3)删除目标对话框中原有的默认备份文件，单击对话框中“添加”按钮，选择“备份设备”对话框中的test1bak,单击“确定”按钮，再单击“确定”按钮,完成备份。(4)在test1中新建一个表后，重复(1)(2)(3)以上操作，在第2步中选择数据库差异备份。(5) 在test1中新建一个表后,重复(1)(2)(3)以上操作，在第2步中选择事务日志。使用T-SQL语句：backup database test1 to test1bakgobackup database test1 to test1bakwith differentialgobackup log test1 to test1bakgo4删除数据库“test1”，然后用已建立的备份还原数据库test1。使用企业管理器：(1)在资源管理器中删除“test1”数据库。（2）在“对象资源管理器”中,右击数据库，选择“任务还原数据库”命令，打开还原数据库对话框。（3）在“目标数据库”文本框中输入“test1”。（4）在“还原的源”选项组里，选择“源设备”，单击“”按钮，按提示添加备份设备或备份文件。（5）尝试从设备进行还原时，过程的差异。使用T-SQL语句：drop database test1gorestore database test1 from test1bakwith norecoverygorestore database test1 from test1bakwith file=2,norecoverygorestore log test1 from test1bakwith file=3go5. 将已建立的数据库test1更名为“学生管理”数据库。使用企业管理器：在“对象资源管理器”窗口中选择test1数据库，单击鼠标右键，选择“重命名”，输入“学生管理”，按回车键确认。使用T-SQL语句：alter database test1modify name=学生管理go6. 对“学生管理”数据库执行分离操作。使用企业管理器：（1）打开SQL Server Management Studio的“对象资源管理器”，右击要分离的数据库“学生管理”，选择“任务分离”。（2）若要更新现有的优化统计信息，则选中“更新统计信息”复选框。点击“确定”，完成分离。使用T-SQL语句：sp_detach_db 学生管理,true7. 把分离的“学生管理”数据库附加到当前的数据库服务器中。使用企业管理器：（1）打开SQL Server Management Studio的“对象资源管理器”，右击“数据库”节点，选择“附加”命令。（2）点击“添加”按钮，选中附加数据库test1的主要数据文件。（3）点击“确定”，回到附加数据库窗口，为数据库设定名称，可以采用原名也可以设定新的数据库名称，点击“确定”即可完成附加操作。使用T-SQL语句：sp_attach_db 学生管理,d:数据库test2data1.mdf,d:数据库test2data2.ndf,d:数据库test2log.ldf实训四 数据库表的设计与管理一、 实训目的：1掌握用户自定义数据类型的创建方法2会使用SQL Server Management Studio 创建表及其约束3会使用T-SQL语句创建表及其约束二、实训环境：Windows XP 系统、SQL Server 2005三、实训内容：分别使用SQL Server Management Studio和T-SQL语句完成下列操作：1创建两个用户自定义数据类型“学号类型”（char（8） not null）和“课程编号类型”（char（3） not null）。2创建一个名为“学籍管理系统”的数据库，然后在这个数据库中创建3个表对象。学生表结构：字段名 数据类型 长度 允许为空 学号 学号类型 姓名 Varchar 12 性别 char 2 出生日期 datetime 专业 Varchar 20成绩表结构：字段名 数据类型 长度 小数位 允许为空 学号 学号类型 课程编号 课程编号类型 成绩 int课程表结构：字段名 数据类型 长度 允许为空课程编号 课程编号类型课程名 varchar 20 学分 int3将“学生表”中的“学号”字段设置为主键; 将“课程表”中的“课程编号”字段设置为主键; 将“成绩表”中的“学号”和“课程编号”两个字段组合起来作为主键。4将“成绩表”中的“学号”字段设置为“学生表”的外键; “课程编号”字段设置为“课程表”的外键。5将“成绩表”中的“成绩”字段设置检查约束，成绩值在0到100之间；将“学生表”的“性别”字段设置检查约束，使其只能接受“男”和“女”。6将“学生表”中的“专业”设置默认值约束为“计算机软件”。学生表模拟数据记录课程表记录成绩表记录四、实训过程：1创建两个用户自定义数据类型“学号类型”（char（8） not null）和“课程编号类型”（char（3） not null）。使用SQL Server Management Studio :(1) 创建一个名为“学籍管理系统”的数据库。(2)在SQL Server Management Studio的“对象资源管理器”中，找到“学籍管理系统可编程性类型”，右击类型，选择“新建用户定义数据类型”命令。(3)在“新建用户定义数据类型窗口”中，输入用户数据类型的名称“学号类型”，在数据类型下拉框中选择基于的系统数据类型char;设置其长度为8；不选中“允许为空”复选框，单击“确定”，完成创建。(4) “课程编号类型”用户数据类型的创建步骤参考（2）和（3）。使用T-SQL语句：sp_addtype 学号类型,char(8),not nullsp_addtype 课程编号类型,char(3),not null2在名为“学籍管理系统”的数据库中创建3个表对象。使用SQL Server Management Studio :（1）打开SQL Server Management Studio的“对象资源管理器”，找到“数据库学生图书管理系统表”节点，右击，选择“新建表”命令。（2）在“列名”栏中键入字段名称。（3）在“数据类型”栏中选择相应的数据类型，并设置数据类型的长度。（4）在“允许空”栏指定是否可以为空(NULL)，打勾说明允许为空，空白说明不允许为空。（5）保存使用T-SQL语句：use 学籍管理系统create table 学生表( 学号 学号类型, 姓名 varchar(12) not null, 性别 char(2), 出生日期 datetime not null, 班级 char(10), 专业 varchar(20)gocreate table 成绩表( 学号 学号类型, 课程编号 课程编号类型, 成绩 int not null)gocreate table 课程表( 课程编号 课程编号类型, 课程名称 varchar(20) not null, 课程类型 char(6), 学分 int)3将“学生表”中的“学号”字段设置为主键; 将“课程表”中的“课程编号”字段设置为主键; 将“成绩表”中的“学号”和“课程编号”两个字段组合起来作为主键。使用SQL Server Management Studio :选中要作为主键的列，并单击工具栏上的“设置主键”按钮，主键列的前方将显示钥匙标记。若设置多个字段的组合为主键，可按crl键进行选择。最后记得保存。使用T-SQL语句：use 学籍管理系统alter table 学生表add constraint pk_学生表_学号 primary key(学号)goalter table 课程表add constraint pk_课程表_课程编号 primary key(课程编号)goalter table 成绩表add constraint pk_成绩表_学号_课程编号 primary key(学号,课程编号)go4将“成绩表”中的“学号”字段设置为“学生表”的外键; “课程编号”字段设置为“课程表”的外键。使用SQL Server Management Studio :（1）打开SQL Server Management Studio的“对象资源管理器”，找到“学籍管理系统成绩表”，右击，选择“修改”命令，打开表设计窗口。（2）在任意位置右击，在快捷菜单中选择“关系”命令，弹出“外键关系”窗口，点击“添加”按钮，点开“表和列规范”前面的“+”。（3）点击“表和列规范”右侧的“”按钮，弹出“表和列”窗口，在“主键表”下拉框中选择“学生表学号列”，在外键表中也选择“成绩表-学号”列。点击“确定”。返回“外键关系窗口”，关闭即可。将“课程编号”字段设置为“课程表”的外键，可参照（3）。最后保存。使用T-SQL语句：use 学籍管理系统alter table 成绩表add constraint fk_成绩表学生表_学号 foreign key(学号) references 学生表(学号),constraint fk_成绩表课程表_课程编号foreign key(课程编号) references 课程表(课程编号)5将“成绩表”中的“成绩”字段设置检查约束，成绩值在0到100之间；将“学生表”的“性别”字段设置检查约束，使其只能接受“男”和“女”。使用SQL Server Management Studio :（1）打开SQL Server Management Studio的“对象资源管理器”，找到“学籍管理系统成绩表”，右击，选择“修改”命令，打开表设计窗口。（2）选中要设置检查约束的字段“成绩”，右击，在快捷菜单中选择“CHECK约束”命令，弹出“CHECK约束”窗口，点击“添加”按钮。（3）点击“表达式”右侧的“”按钮，弹出“CHECK约束表达式”窗口，输入表达式，成绩=0 and 成绩=0 and 成绩=15 and var(select avg(成绩) from 成绩表 where 课程编号=101)7列出各科的平均成绩、最高成绩、最低成绩和选课人数。select 课程编号,avg(成绩) as 平均成绩,max(成绩) as 最高成绩,min(成绩) as 最低成绩,count(*) as 选课人数from 成绩表group by 课程编号8查询没有选修任何课程的学生姓名。select 姓名from 学生表where not exists(select * from 成绩表 where 成绩表.学号=学生表.学号)9在成绩表中，找出课程编号为“102”的这门课程的所有学生的分数以及最高分、最低分和平均分。select *from 成绩表where 课程编号=102compute max(成绩),min(成绩),avg(成绩)10查找“成绩表”中，高于各门课程平均分的学生信息。select *from 学生表where (select count(*) from 成绩表 as a where a.学号=学生表.学号 and a.成绩(select avg(b.成绩) from 成绩表as b where b.课程编号=a.课程编号) =(select count(*) from 成绩表 where 成绩表.学号=学生表.学号)-补有一门课程成绩高于平均分的学生信息select *from 学生表where exists(select * from 成绩表 as a where a.学号=学生表.学号 and a.成绩(select avg(b.成绩) from 成绩表 as b where b.课程编号=a.课程编号)11在“成绩表”中，录入一个学生的课程成绩。insert 成绩表 values(10000303,102,80)12修改姓名为“李丽”的学生的成绩。update 成绩表set 成绩=60where 课程编号=103 and 学号=(select 学号 from 学生表 where 姓名=李丽)实训八 T-SQL流程控制语句编程及函数、游标的创建使用一、实训目的：1理解批处理、脚本的概念，掌握T-SQL的流程控制语句及编程方法。2理解并掌握函数应用。3学会正确使用游标。二、实训环境：Windows XP 系统、SQL Server 2005三、实训内容：1在“学籍管理系统”数据库中创建“计算平均分”标量函数。在该函数中创建输入参数“学号”和“姓名”，以便查询不同学生的成绩平均分。调用该函数并查看执行结果。2在“学籍管理系统”数据库中创建一个函数，该函数可返回某个学生或全部学生的学号，姓名，班级，课程名称，成绩，学分。调用该函数并查看执行结果。3建立一个游标，利用游标显示“学籍管理系统”数据中学生信息表中的数据。4、已知三角形三边为5、6、7，用T-SQL编程计算面积。5、编程计算分段函数f(x)值。6、找出100999之间所有“水仙花数”。所谓“水仙花数”是指一个三位数，其各位数字的立方和等于该数本身（如153=13+53+33）。7、输出三角形如图8、计算1-100间的奇数之和。9、判断2015年是否为闰年，如果是输出2015年是闰年，否则输出2015年不是闰年。10、输出如图所示的菱形。四、实训过程：1在“学籍管理系统”数据库中创建“计算平均分”标量函数。在该函数中创建输入参数“学号”和“姓名”，以便查询不同学生的成绩平均分。调用该函数并查看执行结果。use 学籍管理系统gocreate function 计算平均分(学号 varcha