SQL Server 数据库设计实验指导书(软件09.1-2)

数据库设计实验指导书

软件2009（12）

第 1/12 页 1

软件09（12）SQL数据库设计实验指导书

《数据库设计》课程实验

一、本实验课在培养实验能力中的地位及作用

数据库技术是一个理论和实际紧密联系的技术，而SQL server数据库设计则是数据库具体应用的一个数据库开发系统。在学习过程中，除了解SQL server系统的设计环境及各种工具的使用方法、数据库系统结构、数据库应用方法等知识外，通过实验对相关技术加深认识，切实加强学生的实践能力，是教学中一个重要且必要的环节。《数据库设计》是一门实践性很强的课程，只有通过上机实践才能真正领会主教材中介绍的知识。

二、应达到的实验能力标准

本实验的教学目标是使学生掌握如何使用SQL server 数据库开发系统，了解数据库设计及相关操作的基本概念与方法，进而学会建立与组织、操作数据库。上机实验的主要目标如下:

(1)通过上机操作，加深对数据库系统理论知识的理解。

(2)通过使用具体的DBMS，了解一种实际的数据库管理系统并掌握其操作技术。 (3)通过上机实验，提高动手能力，提高分析问题和解决问题的能力。

三、实验要求

学生在实验课前认真做好预习，事先写好实验报告。实验结束及时提交实验报告。

三、实验成绩考核方法

实验成绩在课程总成绩中占30%，包括平时每次实验考核与考勤。

第 2/12 页 2

软件09（12）SQL数据库设计实验指导书

实验内容目录

实验1 SQL server常用工具的使用及数据库的创建 实验2 数据定义 实验3 数据更新 实验4 数据的简单查询 实验5 数据的高级查询

实验6 数据完整性 实验7 数据控制

实验8 视图 实验9 函数 实验10 存储过程 实验11 备份与还原

实验12 SQL Server数据转换

第 3/12 页 3

软件09（12）SQL数据库设计实验指导书

实验1 常用工具的使用及数据库的创建和管理

实验目的：

1. 掌握SQL server的安装方法及其常用工具的使用方法 2. 掌握SQL server数据库的创建和管理

实验内容：

1．熟悉SQL server常用工具。

2．使用企业管理器创建名为student的数据库，并设置数据库主文件名为syudent_data,大小为10MB;日志文件为student_log,大小为2MB.其他参数取默认值。

3. 使用Transact—SQL语言创建一个student1数据库，主文件逻辑名为student1_data，物理文件名为student1.mdf,为10MB,增长速度为10%;数据库的日志文件逻辑名为syudent1_log, 物理文件名为student1.ldf,初始化大小为1MB,最大尺寸为5MB, 增长速度为1 MB；文件存放路径为c:\\data文件夹下。(查询分析器)

Create database student1 On

(name=student1_data,

filename=c:\\data\\student1.mdf, size=10,maxsize=unlimited, filegrowth=10%) log on

(name=student1_log, filename=c:\\data\\student1.ldf, size=1,maxsize=5, filegrowth=1) 4. 用企业管理器，查看pubs数据库中authors表与employee表的数据信息。 5. 用查询分析器中，查看pubs数据库中authors表的数据信息。

Use pubs Go

Select * from authors

6. 把C:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\student_data, student_log文件复制到自己U盘，然后再把该数据库文件恢复。

第 4/12 页

4

软件09（12）SQL数据库设计实验指导书

实验2-3 数据定义和数据更新

实验目的：

1. 掌握SQL server表的创建与修改 2. 掌握添加、修改表格数据 3. 掌握索引的建立与主键、外键建立

实验内容：

1．在student数据库中创建一个名为pupil的表，要求：(sno char(6) not null,sname char(10) not null,ssex char(2) not null,birthday datetime not null, polity char(20))，其中表中字段满足：sno设置为主键，sname字段设置惟一性约束；

2．将pupil表的ssex和birthday设置检查性约束，要求ssex只能为‘男’或‘女’，birthday应该大于‘1987-1-1’，polity字段设置默认约束，值为‘群众’ 3．在student数据库中创建一个名为course的表（cno, cname, teacher, class）。， 4．创建一个学生选课表sc(sno char(6) not null,cno char(10) not null,grade real).将sc表创建外键约束，把sc表的sno和pupil表的sno关联起来，在这两个表之间创建一种制约关系。

5．利用insert语句向其中插入一条新的记录：（‘0007’，‘张三’，‘男’，‘1982-3-21’，‘团员’,‟计算机系‟）

6．利用update语句将编号为‘0004’的学生polity改为‘党员’： 7．利用delete语句将‘1986-1-1’以后出生的女同学记录删除。

第 5/12 页 5

软件09（12）SQL数据库设计实验指导书

实验4 数据的简单查询

实验目的：

1. 掌握SELECT 语句的基本语法 2. 掌握子查询及order by 子句用法

3. 掌握SELECT 语句的统计函数的作用和使用方法

实验内容：

1．针对pupil表查询所有学生的基本信息，并按出生日期升序排列。 2．针对pupil表查询女同学的信息和女同学的人数。 3．在pupil表中查询所有男同学的年龄。

4．在pupil表中，(1) 查询‘刘’姓学生的信息;(2) 查询polity为‟团员‟或‟党员‟的学生信息。

5．在sc表中，(1) 查询各门课程的选课人数; (2) 查询缺少成绩的学生的学号及课程号。

6．查询与‘刘成’同一个系的学生情况。

7．查询选修了课程名为‘MIS’的学生的学号和姓名

第 6/12 页 6

软件09（12）SQL数据库设计实验指导书

实验5 数据的高级查询

实验目的：

1. 掌握表中数据的查询方法及操作方法 2. 掌握连接查询与嵌套查询的方法

3. 掌握SELECT 语句的GROUP BY 和COMPUTE BY子句使用方法

实验内容：

1．查询每个学生的基本情况及选修的课程情况，格式如下：

学号 „„

姓名 „„

专业 „„

课程号 成绩 „„

„„

2．查询选修了课程号为101的每个学生的基本情况及成绩，若学生未选修101号课程，也包括其情况。

3．在pupil、sc、course三张表中，查出不及格学生的姓名。 4．统计pupil表各个专业的男女生人数。格式如下：

专业 „„

性别 „„

人数 „„

5．查找平均成绩在80分以上的学生的学号和平均成绩。

第 7/12 页

7

软件09（12）SQL数据库设计实验指导书

参见PDF格式的实验指导书

第 8/12 页 8

实验6和7 软件09（12）SQL数据库设计实验指导书

实验8 视图的创建及使用

实验目的：

1. 通过企业管理器和Transact_SQL语句对视图的创建及调用 2. 掌握视图的修改与删除

3. 掌握用Insert,Update通过视图更新数据

实验内容：

1．通过Transact_SQL语句创建一个视图，计算各个班级的各门课程的平均分。 2．创建一个视图，显示‘高等数学’未过的学生的信息。 3．创建一个视图，查询的数据为99521班学生的考试成绩。

4．在STUDENT 库中以pupil表为基础，建立一个名为“V_计算机系学生”的视图（注：计算机系的系部代码为“02”）。在使用该视图时，将显示pupil表中的所有信息。

5．使用视图“V_计算机系学生”查询所有男同学的信息。

6．在查询分析器中使用更改视图的命令将视图“V_计算机系学生”更名为“V_计算机系男生”，然后删除视图“V_计算机系男生”

补充：视图常用_SQL语句

创建：create view 视图名 [with encryption] as select_statement

[with check option]

修改：利用 alter view 视图名 删除：利用 drop view 视图名

第 9/12 页 9

软件09（12）SQL数据库设计实验指导书

实验9 存储过程

实验目的：

1. 掌握SQL 语言流程控制语句 2. 掌握SQL Server 的存储过程

实验内容：

1．创建一个存储过程stugradeinfo,查询班级、学号、姓名、性别、课程名称、分数。

2．利用企业管理器创建一个存储过程stu_info，根据传入的编号，查询某学生的基本信息。

第 10/12 页 10

软件09（12）SQL数据库设计实验指导书

实验7 SQL Server的数据库安全、恢复与备份

实验目的：

1. 掌握SQL Server数据库的完整性约束

2. 掌握SQL Server 的恢复与备份，数据的导入与导出 3. 掌握SQL Server 的安全与权限

实验内容：

1．在sc表中，成绩值应该在0-100之间，对成绩字段创建CHECK约束，使取值在正常范围内。

2．在pupile表中，对polity字段建立一个默认约束，内容为团员。

3．在student数据库中建立一个规则,其名称为“专业规则”. 将„专业规则‟绑定到„学生‟表的„专业‟字段上,可以限制录入或修改记录时提供的专业名称必须是„电子商务‟,‟计算机网络‟,‟信息管理‟。

4．使用企业管理器对一数据库做一次完全数据备份与恢复。 5．把下列文本文件导入到SQL Server数据库当中。

6．将pupil表导出到一个文本文件与access数据库当中。 7．利用DTS设计器将表从一个数据库复制到另一个数据库当中。

8．创建了一个名叫“ssq”，密码是“518405”，默认数据库为student的登陆帐户。 9．使用系统存储过程sp_password修改登录帐户的密码，把ssq帐户的密码改为208107。

第 11/12 页 11

软件09（12）SQL数据库设计实验指导书

实验8 综合练习

实验目的：

1. 复习表的基本操作 2. 复习基本查询与高级查询

3. 掌握SQL Server 用户自定义函数的创建方法

实验内容：

1．从pupil表中检索出所有姓张的学生资料。

2．从pupil表中检索出名字的第二个字是“伟”或“炜”的所有学生资料。 3．在pub库中，若titles表中经济类书籍的平均价格低于20美元，就检索所有经济类图书；否则，则打印“平均书价超过$20”。

4．将Products表中的数据按CategoryID进行分组，然后分别统计每一组产品的平均价格及总库存。

use Northwind go

SELECT CategoryID,AVG(UnitPrice) AS '平均价格',

SUM(UnitsInStock) AS '总库存' FROM Products GROUP BY CategoryID

5．将上题中只显示平均价格低于10元的分组汇总信息。 use Northwind go

SELECT CategoryID,AVG(UnitPrice) AS '平均价格', SUM(UnitsInStock) AS '总库存' FROM Products GROUP BY CategoryID HAVING AVG(UnitPrice)<10

7．编写一个用户自定义函数,完成以下功能：根据两个输入参数(成绩上限和成绩下限)，求sc数据表中满足输入条件的学生人数。

Create function fun_sumren (@maxline real,@minline real) returns int begin

declare @personcount as int

select @personcount=(select count(distinct sno) from sc) from sc

where grade<=@maxline real and grade>=@minline return @personcount end

第 12/12 页

12