数据库原理与应用实验报告完成11

河南财经政法大学

计算机与信息工程学院

实验报告

2012—2013学年第1学期

专 业： 信息管理与信息系统

课程名称： 数据库原理与应用

班 级： 100706

姓 名： 杜 学号：***********

指导教师： * *

实验报告

实验一：熟悉SQL Server数据库管理系统

实验目的

1.

2.

3.

了解Microsoft SQL Server 2008系统；

熟悉SQL Server服务的启动和停止；

熟悉SQL Server 2008管理器的使用。

启动SQL Server服务；

启动SQL Server 2008管理器，熟悉其界面环境；

停止SQL Server服务。

实验内容

1.

2.

3.

实验数据记录及分析（或程序及运行结果）

思考：

1. 启动SQL Server服务的方法有哪几种？怎样实现该服务的自动启动？

2. SQL Server 2008系统有哪几个系统数据库？

答：一

1使用服务管理器启动数据库服务器:：在计算机管理框里找到SQL Server配置管理器, 找到SQL Server服务并打开启动SQL Server服务。

2 使用企业管理器启动数据库服务器:：找到SQL安装目录。点击[开始]-[所有文件]-[Microsoft SQL Server 2008]-[配置工具]-[ SQL Server配置管理器]，开启服务。

3 在DOS命令行状态下启动数据库服务器和代理服务：打开DOS命令框，点击[开始]-[运行]-输入：CMD命令。打开命令框后输入：nets tart mssqlserver启动服务器。

自动启动服务：可以在操作系统启动时启动服务；可以在Server Manager中选择自动启动选项；可以在操作系统的计划任务中设置自动启动；可以在服务器属性配置中制定。

二 系统数据库主要包括master, model, msdb, resource,tempdb这四个数据库。

河南财经政法大学计算机与信息工程学院 第 1 页 共 17 页

实验报告

评语：

日期： 年 月 日

实验二：数据库操作

实验目的

1. 了解数据库的结构；

2. 学会使用图形化的方法创建、修改、删除数据库；

3. 学会使用T-SQL命令创建、修改、删除数据库。

实验内容

1. 启动SQL Server 服务；

2. 启动SQL Server 2008管理器，利用图形化的方法创建数据库student（如下表所示）；

3. 利用图形化的方法修改数据库student，增加一个数据文件到新建的文件组studata中，其中：逻辑文件名为student_data1，物理文件名为D:DATAstudent_，初始大小为50MB，以30%的速度增长，最大文件大小为500MB；

4. 利用图形化的方法删除数据库student；

5. 利用T-SQL命令重复上述工作。

选项

数据库名称

逻辑文件名

数

据

文

件

物理文件名

文件组

初始大小

最大文件大小

增量

逻辑文件名

日

志

文

件

参数

student

student_data

C:DATAstudent_

PRIMARY

3MB

不受限制

1MB

student_log

C:LOGstudent_

1MB

200MB

10%

物理文件名

初始大小

最大文件大小

增量

实验数据记录及分析（或程序及运行结果）

河南财经政法大学计算机与信息工程学院 第 2 页 共 17 页

实验报告

思考：

1. 你的老板要求你创建一个数据量为20G的数据库，但是你现在的硬盘上没有一个这么大空闲容量的分区，只有3个空闲容量为8G的分区，请问，你该如何完成这个任务？

2. 你想创建一个初始大小为2MB的数据库，但是你却发现你创建的数据库的初始大小是5MB，而且不能小于这个值，请问是什么原因？

河南财经政法大学计算机与信息工程学院 第 3 页 共 17 页

实验报告

评语：

日期： 年 月 日

实验三：数据表操作

实验目的

1.

2.

3.

4.

了解表的结构和SQL Server的基本数据类型；

学会使用图形化的方法创建、修改、删除数据表；

学会使用T-SQL命令创建、修改、删除数据表；

学会创建各种约束。

实验内容

1. 启动SQL Server服务；

2. 启动SQL Server 2008管理器，利用图形化的方法在数据库student中创建学生信息表stu_info、课程信息表course_info和学生成绩表stu_grade，其结构如下表所示；

3. 利用图形化的方法对数据表进行如下修改：

 在表stu_info中，增加备注字段，字段名memo，字段类型nvarchar，字段长度200；

 在表course_info中，对于字段course_name设置UNIQUE约束；

 在表stu_grade中，对于字段grade设置CHECK约束，其取值在0到100之间；

 在表stu_grade中，对于字段stu_id设置FOREIGN KEY约束，其取值参考表stu_info中stu_id字段的取值；

4. 利用图形化的方法向以上表中输入一些数据，体会约束的作用；

5. 利用图形化的方法删除表stu_info、course_info、stu_grade；

6. 利用CREATE TABLE命令在数据库student中创建学生信息表stu_info、课程信息表course_info和学生成绩表stu_grade，其结构如下表所示；

7. 利用ALTER TABLE命令对表进行如下修改：

 在表stu_info中，增加身份证号码字段，字段名code，字段类型char，字段长度18，不允许为空；

 在表stu_info中，对于字段code设置UNIQUE约束；

 在表stu_info中，删除身份证号码字段code；

 在表stu_info中，对于字段sex设置CHECK约束，其取值仅为“男”或 “女”；

 在表course_info中，增加先行课字段，字段名pre_course_id，其取值参考course_id字段的取值；

8. 利用图形化的方法向以上表中输入一些数据，体会约束的作用；

河南财经政法大学计算机与信息工程学院 第 4 页 共 17 页

实验报告

9. 利用DROP TABLE命令删除表stu_info、course_info、stu_grade。

学生信息表（stu_info）

列名

stu_id

name

birthday

sex

address

mark

major

sdept

数据类型

char

nvarchar

date

nchar

nvarchar

smallint

nvarchar

nvarchar

大小 小数位 是否为空 默认值

10

20

1

20

20

20

否

否

男

约束

主键

含义

学号

姓名

生日

性别

籍贯

入学成绩

专业

院系

课程信息表（stu_info）

列名

course_id

course_name

course_type

course_mark

数据类型

char

nvarchar

nvarchar

tinyint

大小 小数位 是否为空 默认值

3

20

2

否

否

考试

约束

主键

含义

课程编号

课程名

课程类型

课程学分

学生成绩表（stu_grade）

列名

stu_id

course_id

grade

数据类型

char

char

int

大小 小数位 是否为空 默认值

10

3

否

否

约束

主键

主键外键

含义

学号

课程编号

成绩

实验数据记录及分析（或程序及运行结果）

思考：

1. 创建和删除表stu_info、course_info和stu_grade在顺序上分别有什么样的要求？

河南财经政法大学计算机与信息工程学院 第 5 页 共 17 页

实验报告

答：创建时三个表对表的创建顺序并没有特别的要求，可以根据用户的个人需求进行创建，但进行删除时需要在顺序上进行特别的注意，要删除主表，必须先删除与其相关联的表，即要删除stu_grade必须首先删除course_info,要删除stu_info必须首先删除stu_grade.

河南财经政法大学计算机与信息工程学院 第 6 页 共 17 页

实验报告

评语：

日期： 年 月 日

实验四：数据的插入、修改和删除

实验目的

1. 学会使用图形化的方法对表中数据进行插入、修改和删除；

2. 学会使用T-SQL命令对对表中数据进行插入、修改和删除；

3. 了解数据在插入、修改和删除时要注意数据的完整性。

实验内容

1． 启动SQL Server服务；

2． 启动SQL Server 2008管理器，利用图形化的方法向学生信息表stu_info、课程信息表course_info和学生成绩表stu_grade中添加数据，其数据内容参考下表。

3． 利用图形化的方法对表数据进行如下修改：

 在表stu_info中，将学号（stu_id）为“2007070101”同学的籍贯（address）改为“河南洛阳”；

 在表stu_grade中，将成绩（grade）小于60分的所有同学成绩增加10%；

4． 利用图形化的方法删除数据表stu_grade中成绩（grade）小于60分的选课记录；

5． 利用INSERT INTO命令向学生信息表stu_info、课程信息表course_info和学生成绩表stu_grade中添加数据，其数据内容参考下表；

6． 利用UPDATE命令修改表数据：

 在表stu_info中，将学号（stu_id）为“2007070102”同学的院系（sdept）改为“会计学院”；

 在数据表stu_grade中，将信息学院成绩（grade）小于60分的所有同学成绩置空；

7． 利用DELETE命令删除表数据：

 在表stu_info中，删除所有籍贯（address）为“河南洛阳”的同学信息；

 在表stu_grade中，删除“张元选修操作系统”这一选课信息；

8． 假设有表“借阅(借书证号，书名，借阅日期，是否到期) ”，修改表中各记录在字段“是否到期”上的值，用借阅日期与当前日期比较，当差值大于规定期限（如60天）时，将字段“是否到期”的值由“否”（默认值）改为“是”。

河南财经政法大学计算机与信息工程学院 第 7 页 共 17 页

实验报告

学生信息表（stu_info）

stu_id name birthday sex address mark major sdept

2007070101 张元

2007070102 张红

2007070103 王明

2007070104 李伟

2007070201 郑澜

2007070202 赵恒

2007070203 张兰

2007080101 李伟

2007080102 钱丽

2007080201 孙楠

课程信息表（stu_info）

course_id

701

702

703

704

706

801

802

803

804

1985-10-09 男 河南郑州 576 计算机科学与技术 信息学院

1985-01-14 女 河南开封 565 计算机科学与技术 信息学院

1986-07-08 男 河南洛阳 570 计算机科学与技术 信息学院

1986-03-11 男 河南郑州 564 计算机科学与技术 信息学院

1985-12-01 女 河南平顶山 567

1986-02-03 男 河南周口 566

1986-04-06 女 河南许昌 571

1985-09-12 男 河南郑州 578

1985-11-23 女 河南安阳 573

1986-11-19 男 河南南阳 578

电子商务

电子商务

电子商务

会计学

会计学

财务管理

信息学院

信息学院

信息学院

会计学院

会计学院

会计学院

course_name

计算机基础

操作系统

计算机网络

数据库原理

Java

宏观经济学

初级会计

财政学

会计电算化

course_type

考试

考试

考试

考查

考查

考试

考试

考试

考查

course_mark

3

4

4

3

3

4

4

3

3

course_time

50

50

50

50

40

50

50

50

学生成绩表（stu_grade）

stu_id

2007070101

course_id

701

grade

75

stu_id

2007070104

course_id

702

grade

88

河南财经政法大学计算机与信息工程学院 第 8 页 共 17 页

实验报告

2007070101

2007070101

2007070102

2007070102

2007070103

2007070104

702

703

701

702

701

701

81

96

85

74

55

35

2007080101

2007080101

2007080102

2007080102

2007080201

2007080201

701

802

801

802

803

804

91

87

50

75

82

实验数据记录及分析（或程序及运行结果）

1-2.按照如图所示建立学生信息表stu_info、课程信息表course_info和学生成绩表stu_grade

3.打开表stu_info, 表stu_grade，直接在原表中进行修改相应信息

4. 单击右键直接删除表stu_grade中成绩（grade）小于60分的选课记录

5.插入语句：

insert into stu_info values(‘2007070101’,’ 张元’,’ 1985-10-09’,’ 男’,’

河南郑州’,’ 576’,’ 计算机科学与技术’,’ 信息学院’)

insert into course_info values（’ 701’,’ 计算机基础’,’ 考试’,’ 3’,’ 50’）

insert into stu_grade values(‘2007070104’,’ 701’,’ 35’)

按照该语句向三个表中分别插入信息

stu_info set sdept=’ 会计学院’ where stu_id=’ 2007070102’

update stu_grade set grade=NULL where grade<60

from stu_info where address=’ 河南洛阳’

Delete from stu_grade where stu_id in (select stu_id from stu_info

where name=’ 张元’) and course_id in (select course_id from

河南财经政法大学计算机与信息工程学院 第 9 页 共 17 页

实验报告

course_info=’ 操作系统’)

‘借阅’ set ‘是否到期’=’ 是’ where getdate()-getdate(‘借阅日期’)>60

河南财经政法大学计算机与信息工程学院 第 10 页 共 17 页

实验报告

评语：

日期： 年 月 日

实验五：数据查询

实验目的

1.

2.

3.

4.

掌握SELECT语句的基本语法；

掌握模糊查询的用法；

掌握连接查询的用法；

掌握子查询的用法。

实验内容

针对数据库student中的数据表，完成下列查询：

1. SELECT基本使用

 查询每位同学的所有信息；

 查询学号为“2007070103”的同学的姓名、性别、籍贯和院系信息；

 查询每位女同学的学号、姓名和院系信息，并将结果中各列的标题指定为学号、姓名和院系；

 查询每位同学的年龄信息；

 查询所有其籍贯含有“阳”字的同学的姓名、性别和籍贯信息；

 查询选修了“701”号课程且成绩在70~80之间的同学的学号；

2. 嵌套查询

 查询选修“计算机基础”号课程的同学的学号、姓名和院系信息；

 查询没有选修“701”号课程的同学的学号、姓名和院系信息；

 查询比会计学院所有入学成绩都高的同学的学号、姓名、专业和院系信息；

3. 连接查询

 查询每位同学的学号、姓名、课程名和成绩信息；

 查询入学成绩大于575分的同学的学号、姓名、课程名和成绩信息；

 查询成绩在90分以上的同学的学号、姓名、课程名和成绩信息；

4. 数据汇总

 查询信息学院同学的入学平均分；

 查询全体同学的入学最高分和最低分；

 查询会计学院同学的总人数；

 查询所有其籍贯含有“阳”字的同学人数；

 查询学号为“2007070101”的同学的选课门数、平均分和总分；

河南财经政法大学计算机与信息工程学院 第 11 页 共 17 页

实验报告

5. GROUP BY

 查询每个院系的总人数；

 查询每位同学的选课门数和平均分；

 查询平均分在80分以上的同学的学号、姓名、选课门数和平均分；

 查询专业人数在3人以上（含3人〕的专业名称及其所在院系；

6. ORDER BY

 将信息学院同学的信息按入学成绩由高到低排列输出；

 查询选修“计算机基础”课程的每位同学的学号、姓名和成绩信息，并按成绩由低到高排列输出。

实验数据记录及分析（或程序及运行结果）

⑴ SELECT基本使用

① Select * from stu_info

②Select name,sex,address,sdept from stu_info where stu_id=’2007070103’

③ Select stu_id 学号,name 姓名,sdept 院系from stu_info where sex=’女’

④ Select stu_id,name,year(getdate())-year(birthday) 年龄 from stu_info

⑤ Select name,sex,address from stu_info where address like ‘%阳%’

⑥ Select stu_id from stu_grade where course_id=’001’ and grade between 70 and

80

（2）嵌套查询

① Select stu_id,name,sdept from stu_info where stu_id in (select stu_id from

stu_grade where course_id in(select course_id from course_info where

course_name=’计算机基础’))

② Select stu_id,name,sdept from stu_info where stu_id not in (select stu_id from

stu_grade where course_id=’701’)

③ Select stu_id,name,sdept from stu_info where mark>all (select mark from stu_info

where sdept=’会计学院’)

（3）连接查询

①Select stu__id,name,course_name,grade from

stu_info,course_info,stu_grade where stu__id=stu__id and

course__id=stu__id

②Select stu__id,name,course_name,grade from

stu_info,course_info,stu_grade where stu__id=stu__id and

course__id=stu__id and mark>575

③Select stu__id,name,course_name,grade from

stu_info,course_info,stu_grade where stu__id=stu__id and

course__id=stu__id and grade>90

（4）数据汇总

① Select avg(mark) from stu_info where sdept=’信息学院’

② Select max(mark) 入学最高分,min(mark) 入学最低分 from stu_info

③ Select count(*) from stu_info where sdept=’会计学院’

④ Select count(*) from stu_info where address like ‘%阳%’

⑤Select stu_id,sum(grade) from stu_grade where stu_id=’2007070101’

（5）GROUP BY

河南财经政法大学计算机与信息工程学院 第 12 页 共 17 页

实验报告

① Select sdept,count(*) from stu_info group by sdept

② Select stu_id,count(*),avg(grade) from stu_grade group by stu_id

③Select stu__id,name,count(*),avg(grade) from stu_info,stu_grade where

stu__id=stu__id group by stu__id，name having

avg(grade)>=80

④Select maijor,sdept from stu_info group by maijor,sdept having count(*)>=3

（6）ORDER BY

① Select * from stu_info where sdept=’信息学院’ order by mark desc

②Select stu__id,name,grade from stu_info,course_info,stu_grade where

stu__id=stu__id and course__id=stu__id and

course_name=’计算机基础’ order by grade

河南财经政法大学计算机与信息工程学院 第 13 页 共 17 页

实验报告

河南财经政法大学计算机与信息工程学院 第 14 页 共 17 页

实验报告

评语：

日期： 年 月 日

实验六：索引

实验目的

1.

2.

3.

4.

掌握索引的概念；

掌握使用图形化的方法创建、删除索引；

掌握使用T-SQL命令创建、删除索引；

掌握索引分析的方法。

实验内容

1. 启动SQL Server服务；

2. 启动SQL Server 2008管理器，利用图形化的方法创建下列索引：

 对学生信息表stu_info中的name列创建非聚集唯一索引idx_name；

 对学生信息表stu_info中的sex列创建聚集索引idx_sex；

 对学生成绩表stu_grade中的stu_id、course_id列创建复合索引idx_stu_course_id；

3. 利用图形化的方法删除索引idx_stu_course_id；

4. 利用T-SQL命令重复以上工作；

5. 分析索引建立前后对查询效率的影响，写出分析结果。

实验数据记录及分析（或程序及运行结果）

1启动SQL Server服务

2 选择需要建立索引的表，右击索引项选择新建索引，在索引名称中输入idx_name，索引类型选择非聚集，并选择唯一项单击添加即可。

3 同样在索引名称中输入idx_sex，并选择索引类型为聚集。

显示stu_grade的表结构，单击stu_id选择索引和键设置列为stu_id、course_id并在标识中4输入idx_stu_course_id，则为表创建了一个复合索引。

4 打开索引与键的对话框，选择要删除的索引idx_stu_course_id，并单击删除则删除了所创建的索引。

Create unique index idx_sex on stu_info(sex);

Create unique index idx_name on stu_info(name);

Create unique index idx_stu_course_id on stu_grade(stu_id,course_id);

Drop index idx_stu_course_id

5 使用索引以后，使用户的查询效率大大提高，并提高了计算机的运行效率，可以快速访问数据库表中的特定信息。

河南财经政法大学计算机与信息工程学院 第 15 页 共 17 页

实验报告

评语：

日期： 年 月 日

实验七：视图

实验目的

1. 理解视图的重要性；

2. 掌握视图的建立；

3. 掌握视图的使用。

实验内容

1. 启动SQL Server服务；

2. 启动SQL Server 2008管理器，利用图形化的方法创建下列视图：

 视图view_male，包含学生信息表中所有男生信息；

 视图view_stu_grade，包含每个同学的学号、姓名、课程名和成绩信息；

 视图view_avg，包含每个同学的学号、姓名、平均成绩信息

 利用图形化的方法删除视图view_male；

3利用CREATE VIEW命令创建下列视图：

 视图view_female，包含学生信息表中所有女生信息；

 视图view_count，包含每个院系的名称和学生人数信息；

 视图view_sum，包含每个同学的学号、姓名、课程总成绩信息；

3. 利用DROP VIEW命令删除视图view_female；

4. 通过视图，查询下列信息：

 查询“信息学院”的学生人数；

河南财经政法大学计算机与信息工程学院 第 16 页 共 17 页

实验报告

 查询学号“2007070101”同学的课程总成绩。

实验数据记录及分析（或程序及运行结果）

1启动SQL Server服务

2(1)

打开SQL Server 2008， 打开需要建立视图的数据库，选择视图选项卡，右击视图选择新建，弹出选择表的对话框，选择stu_info表，并进行添加

在列选项卡下选择所有列，表选择stu_info,筛选中输入sex=’男’，单击保存，输入视图的名称view_male.

(2)打开SQL Server 2008， 打开需要建立视图的数据库，选择视图选项卡，右击视图选择新建，弹出选择表的对话框，选择stu_info和stu_grade表，并进行添加,系统会自动增加表之间的连接，在列选项卡下选择所需显示的列即学号，姓名并输入AVG(Grade),则在选项卡中自动增加分组依据，在表选项卡下选择所对应的表，然后可以执行SQL，并单击保存输入视图的名称。

(3)打开SQL Server 2008， 打开需要建立视图的数据库，选择视图选项卡，右击视图选择新建，弹出选择表的对话框，选择stu_info和stu_grade和course_info表，并进行添加,系统会自动增加表之间的连接，在列选项卡下选择所需显示的列，在表选项卡下选择所对应的表，然后可以执行SQL，则在结果中显示满足条件的信息，然后点击保存，输入视图的名称view_stu_grade.

(4)打开视图选项卡，在其下面找到view_male,并右击选择编辑前1000行则会显现view_male 中的所有信息。

3 选择所要删除的视图，右击视图单击删除。

4 (1)Create view view_female as select * from stu_info where sex=’女’

(2)Create view view_count as select sdept,count(*)from stu_info group by sdept,count(*)

(3)Create view view_sum as select stu_id,name,sum(grade) from stu_info,stu_grade where

stu__id=stu__id

5 Drop view view_female

6(1)Creat view view_xx select count(*) from stu_info where sdept=’信息学院’

(2)Create view view_mmSelect count(*) from stu_info,stu_grade

stu__id=stu__id and stu_id=’2007070101’

where

河南财经政法大学计算机与信息工程学院 第 17 页 共 17 页

实验报告

河南财经政法大学计算机与信息工程学院 第 18 页 共 17 页

实验报告

评语：

日期： 年 月 日

实验八：数据库安全

实验目的

1.

2.

3.

4.

掌握数据库的安全性能；

掌握使用用户实现数据库安全性的方法；

掌握使用角色实现数据库安全性的方法；

掌握权限分配的方法。

实验内容

1.

2.

3.

4.

5.

6.

启动SQL Server服务；

以你的学号创建一个登录帐号；

在数据库student中为刚创建的登录帐号创建用户User1；

给用户User1授予对学生信息表进行插入、修改和删除记录的权利；

在数据库student中创建角色Role1；

给角色Role1授予对学生信息表进行查询和插入记录的权利，禁止对学生信息表进行修改和删除记录的权利；

7. 将用户User1加入到角色Role1中；

8. 你最后具有什么样的权限？试验证之。

实验数据记录及分析（或程序及运行结果）

1启动SQL Server服务。

2在 安全性 选项卡下选择登录名，右击选择创建登录名，输入登录名为：aaa，选择SQL身份验证，输入密码，并取消 强制实施密码策略。

3在需要创建的数据库下面选择 安全性 选项卡，单击打开选择用户右击选择新建用户输入用户名以及登录名为：aaa。

4新建查询，输入代码“grant select to aaa”

5选择文件——断开与对象资源管理器的连接——连接对象资源管理器 服务器选择local,身份验证选择SQL身份验证，输入登录名及密码：aaa.

6进入aaa用户下，新建查询，输入“select * from stu_info ”则会显示出查询结果，但输入insert 语句系统提示错误

河南财经政法大学计算机与信息工程学院 第 19 页 共 17 页

实验报告

评语：

日期： 年 月 日

实验九：数据库维护

实验目的

1. 掌握导入/导出的使用方法；

2. 掌握备份/恢复（还原）的使用方法。

实验内容

1.

2.

3.

4.

5.

启动SQL Server服务；

将数据库student中的学生信息表导出到excel文件中；

将excel文件中的数据导入到数据库student中；

对数据库student进行完整备份；

恢复数据库student。

实验数据记录及分析（或程序及运行结果）

1启动SQL Server服务

2选择student_bak数据库，右击选择任务选项，进入SQL Server导入导出向导，选择数据源，服务器名称，并选择数据库student_bak，单击下一步。

3创建excel文件，选择导出到的位置选择excel文件

4 选择student_bak数据库，右击选择任务选项，进入SQL Server导入导出向导，选择数据源excel，单击下一步。

5选择导出到的位置选择SQL Server

6选择student_bak数据库，右击选择任务选项，选择备份进入备份数据库的对话框，选择备份到的位置，并确定

7选择student_bak数据库，右击选择任务选项，选择还原选项。

则数据库student以同样的方法使数据库得到还原。

河南财经政法大学计算机与信息工程学院 第 20 页 共 17 页

实验报告

评语：

日期： 年 月 日

河南财经政法大学计算机与信息工程学院 第 21 页 共 17 页