数据库实验报告.docx

数据库实验报告.docx

《数据库实验报告.docx》由会员分享，可在线阅读，更多相关《数据库实验报告.docx（44页珍藏版）》请在冰豆网上搜索。

数据库实验报告

数据库原理

实验报告

学号：

4110115

姓名：

王善斌

提交日期：

2013-06-20

成绩：

东北大学秦皇岛分校

【实验内容】

2在企业管理器中创建一个数据库，要求如下：

（1）数据库名称Test1。

（2）主要数据文件：

逻辑文件名为Test1_data1，物理文件名为Test1_data1.mdf，初始容量为1MB，最大容量为10MB，增幅为1MB。

（3）次要数据文件：

逻辑文件名为Test1_data2，物理文件名为Test1_data2.ndf，初始容量为1MB，最大容量为10MB，增幅为1MB。

（4）事务日志文件：

逻辑文件名为Test1_log1，物理文件名为Test1_log1.ldf，初始容量为1MB，最大容量为5MB，增幅为512KB。

3在查询分析器中创建一个数据库，要求如下：

（1）数据库名称Test2。

（2）主要数据文件：

逻辑文件名为Test2_data1，物理文件名为Test2_data1.mdf，初始容量为1MB，最大容量为10MB，增幅为1MB。

（3）次要数据文件：

逻辑文件名为Test2_data2，物理文件名为Test2_data2.ndf，初始容量为1MB，最大容量为10MB，增幅为1MB。

（4）事务日志文件：

逻辑文件名为Test2_log1，物理文件名为Test2_log1.ldf，初始容量为1MB，最大容量为5MB，增幅为1MB。

createdatabasetest2

onprimary（name=test2_data1,

filename='d:

\sqlex\test2_data1.mdf',

size=1,

maxsize=10,

filegrowth=1）,

（name=test2_data2,

filename='d:

\sqlex\test2_data2.ndf',

size=1,

maxsize=10,

filegrowth=1）

logon（name=test2_log,

filename='d:

\sqlex\stu_log1.ndf',

size=1,

maxsize=5,

filegrowth=2）

4.在查询分析器中按照下列要求修改第3题中创建的数据库test2

（1）主要数据文件的容量为2MB，最大容量为20MB，增幅为2MB。

（2）次要数据文件的容量为2MB，最大容量为20MB，增幅为2MB。

事务日志文件的容量为1MB，最大容量为10MB，增幅为2MB

alterdatabasetest3

modifyname=test2

alterdatabasetest2

modifyfile（name=test2_data2,

filename='d:

\sqlex\test2_data2.ndf',

size=2,

maxsize=20,

filegrowth=2）

alterdatabasetest2

modifyfile（name=test2_log,

filename='d:

\sqlex\test2_log.ldf',

size=1,

maxsize=10,

filegrowth=1）

alterdatabasetest2

modifyfile（name=test2_data1,

size=2,

maxsize=20,

filegrowth=2）

4.数据库更名：

把test1数据库更名为new_test1

alterdatabasetest1

modifyname=new_test1

5.在企业管理器中删除new_test1数据库，在查询分析器中删除test2数据库。

在企业管理器中删除new_test1前

在企业管理器中删除new_test1后

在查询分析器中删除test3前

在查询分析器中删除test3后

dropdatabasetest3

6.为sql示例数据库northwind创建一个备份：

northwindBK，并使用该备份文件恢复northwind数据库。

execsp_addumpdevice'disk','northwindBK','d:

\数据库备份\MyNwind-1.dat'

BACKUPDATABASEnorthwindTOnorthwindBK

实验二创建并管理表

1.创建数据库，包含如下表，创建这些表并按要求定义约束

usestudentinfo

go

createtablestudent（

student_idchar（10）primarykey,

student_namechar（10）notnull,

sexchar

（1）notnull,

ageintnull,

departmentchar（15）default'电子信息系'）

usestudentinfo

go

createtablecourse（

course_idchar（6）primarykey,

course_namechar（20）notnull,

precouldchar（6）null,

creditsnumeric（3,1）notnull）

usestudentinfo

go

createtablescore（

student_idchar（10）,

course_idchar（6）notnull,

gradenumeric（3,1）null,

primarykey（student_id,course_id）,

foreignkey（student_id）referencesstudent（student_id）,

foreignkey（course_id）referencescourse（course_id））

以下为各个表的数据

Students表数据

Student_id

Student_name

sex

age

department

20010101

Jone

M

19

Computer

20010102

Sue

F

20

Computer

20010103

Smith

M

19

Math

20030101

Allen

M

18

Automation

20030102

deepa

F

21

Art

Course表数据

Course_id

Course_name

PreCouId

Credits

C1

English

4

C2

Math

C5

2

C3

database

C2

2

Score表数据

Student_id

Course_id

Grade

20010101

C1

90

20010103

C1

88

20010102

C2

94

20010102

C2

62

2.增加，修改，删除字段，要求

（1）为表student增加一个memo（备注）字段，类型为varchar（200）

代码：

useyzw

go

altertablestudentaddmemovarchar（200）

（2）.将MEMO字段的数据类型更改为varchar（300）

代码：

useyzw

go

altertablestudentaltercolumnmemovarchar（300）

（3）．删除memo字段

useyzw

go

altertablestudentdropcolumnmemo

3.向表中插入数据验证约束

useyzw

go

altertablescore

addconstraintgradecheck（grade>0andgrade<100）

//建立了一个约束条件

可以知道约束条件起作用了。

三数据更新

实验内容：

在已经建立的studentinfo数据库和3个students、courses、score基础上完成下列操作。

1.向students表添加一个学生记录，学号为20010112，性别为男，姓名为stefen，年龄25岁，所在系为艺术系art。

useyzw

go

insertstudent

values（'20010112','stefen','M','25','Art'）

2.向score表添加一个选课记录，学生学好为20010112，所选课程号为C2

useyzw

go

insertscore

values（'20010112','C2',null）

3.建立临时表tempstudent,结构与students结构相同，其记录均从student表获取

useyzw

go

createtabletempstudent（

student_idchar（10）primarykey,

student_namechar（10）notnull,

sexchar

（1）notnull,

ageintnull,

departmentchar（15））

insertintotempstudent

selectstudent_id,student_name,sex,age,department

fromstudent

4，将所有学生的成绩加5分

updatescore

setgrade=grade+5

5.将姓名为sue的学生所在系改为电子信息系

updatestudent

setdepartment='电子信息系'

wherestudent_name='sue'

6.将选课的database的学生成绩加10分

updatescore

setscore.grade=score.grade+10

wherescore.course_id='C3'

7.删除所有成绩为空的选修记录

Deletescore

Wheregrade=’null’

8.删除学生姓名为deepa的学生记录

由于下表有调用因而删除不掉

四数据查询

1、查询全体学生的学号、姓名、所在系，并为结果集的各列设置中文名称。

selectstudent_id'学号',student_name'姓名',sex'性别',age'年龄',department'学院'

fromstudent

2.查询全体学生的选课情况，并为所有成绩加5分

select*

fromscore

updatescore

setgrade=grade+5

3.显示所有选课学生的学号，去掉重复行

selectdistinctstudent.student_id

fromstudent

4.查询选课成绩大于80分的学生

selectscore.student_id

fromscore

wheregrade>80

5.查询年龄在20到30之间的学生学号，姓名，所在系

selectstudent.student_id,student.student_name,student.department,student.age

fromstudent

whereagebetween20and30

6.查询数学系、电子信息系、艺术系的学生学号，姓名。

selectstudent.student_id,student.student_name

fromstudent

wheredepartmentin（'math','电子信息系','Art'）

7.查询姓名第二个字符为u并且只有3个字符的学生学号，姓名

selectstudent.student_id,student.student_name

fromstudent

wherestudent_namelike'_u_'

8.查询所有以S开头的学生。

selectstudent.student_id,student.student_name

fromstudent

wherestudent_namelike'S%'

9.查询姓名不以S、D、或J开头的学生

selectstudent.student_id,student.student_name

fromstudent

wherestudent_namenotlike'S%'andstudent_namenotlike'D%'andstudent_namenotlike'J%'

10查询没有考试成绩的学生和相应课程号（成绩值为空）

selectstudent_id,course_id

fromscore

wheregradeisnull

11.求年龄大于19岁的学生的总人数

selectcount（*）'总数'

fromstudent

whereage>19

12别求选修了c1语言课程的学生平均成绩、最高分、最低分学生。

selectAVG（grade）'平均成绩',max（grade）'最高分',min（grade）'最低分'

fromscore

wherecourse_id='c1'

13.求学号为20010101的学生总成绩

selectsum（grade）'总成绩'

fromscore

wherestudent_id='20010101'

14求每个选课学生的学号，姓名，总成绩

selectstudent.student_id,student_name,score.grade

fromstudent,score

wherestudent.student_id=score.student_id

15.查询所有姓李且全名为3个汉字的学生姓名，学号，性别

selectstudent_id,student_name,sex

fromstudent

wherestudent_name='李__'

16.求课程号及相应课程的所有的选课人数

selectcourse_id,count（*）'Sum'

fromscore,student

wherestudent.student_id=score.student_id

groupbycourse_id

17查询选修了3门以上课程的学生姓名学号

selectstudent_id,student_name

fromstudent

whereEXISTS（

select*

fromscore

wherescore.student_id=student.student_id

groupbystudent_idhavingcount（*）>=3

）

18.查询每个学生基本信息及选课情况

select*

fromstudent,course

2.查询每个学生学号姓名及选修的课程名、成绩

selectstudent.student_id,student.student_name,course_id,grade

fromstudent,score

wherestudent.student_id=score.student_id

3.求电子信息系选修课程超过2门课的学生学号姓名、平均成绩并按平均成绩降序排列

4.查询与sue在同一个系学习的所有学生的学号姓名

selectstudent.student_id,student.student_name

fromstudent

wheredepartment=（selectdepartment

fromstudent

wherestudent_name='sue'）

5.查询所有学生的选课情况，要求包括所有选修了课程的学生和没有选课的学生，显示他们的姓名学号课程号和成绩（如果有）

selectstudent.student_id,student.student_name,score.course_id,score.grade

fromstudent,score

wherestudent.student_id=score.student_id

五索引和视图

1、分别使用企业管理器和查询分析器为northwind数据库中products表建立一个聚集索引，索引字段为产品类型和产品编号。

2在已经建立的studentInfo数据库的3个表基础上，完成下列操作：

（1）建立数学系的学生视图；

createviewmathsthdentview

as

select*

fromstudent

wherestudent.department='math'

（2）建立计算机系选修了课程名为database的学生的视图，视图名为compStudentview，该视图的列名为学号、姓名、成绩

createviewcompstudentview

as

selectstudent.student_id'学号',student_name'姓名',grade'成绩'

fromstudent,score

wherestudent.department='computer'andstudent.student_id=score.student_idandscore.course_id='C3'

（3）创建一个名为studentSumview的视图，包含所有学生学号和总成绩

createviewstudentSumview

as

selectstudent.student_id,sum（grade）'sum'

fromstudent,score

wherestudent.student_id=score.student_id

groupbystudent.student_id

（4）建立一个计算机系学生选修了课程名为database并且成绩大于80分的学生视图，视图名为CompsutdentView1，视图的列为学号姓名成绩。

createviewCompstudentview1

as

selectstudent.student_id,student_name,grade

fromstudent,score

wherestudent.department='computer'andscore.course_id='C3'andgrade>80andstudent.student_id=score.student_id

（5）使用sql语句删除compsutdentview1视图。

dropviewCompstudentview1

数据库查询综合实验

1.使用查询分析器建立上述数据库和表；

图书表

读者表

借阅表

2、基于以上数据库使用sql语句完成下列对表操作：

（1）给图书表增加一列“ISBN”，数据类型为CHAR（10）；

altertable图书表

addISBNchar（10）

（2）为刚添加的ISBN列增加默认值约束，约束名为ISBNDEF，默认值为‘7111085949’；

altertable图书表

addconstraintISBNDEFdefault7111085949forISBN

（3）为读者表中“办公电话”一列增加一个CHECK约束，要求电话号码的前五位是“88320”

altertable读者表

addconstraintc1check（办公电话LIKE'88320___'）

（4）删除图书表中ISBN列增加的默认值约束

altertable图书表

dropconstraintISBNDEF

（5）删除读者表中“办公电话”列的CHECK约束

altertable读者表

dropconstraintc1

6）删除图书表中的新增ISBN列。

altertable图书表

dropcolumnISBN

3、基于以上3个表，使用sql语句完成以下数据更新操作

1）向读者表加入一个新读者，该读者的信息为，读者号：

001980，姓名：

余暇，性别：

女，办公电话：

88320564，部门：

艺术系

insertinto读者表

values（'001980','余暇','女','88320564','艺术系'）

2）向借阅表插入一个借阅记录，表示读者“王平”借阅了一本书，图书号：

TP316/ZW6，借出日期为当天日期，归还日期为空值；

insertinto借阅表

values（'001973','TP316/ZW6','2013-6-17',null）

（3）读者“王平”应在借出日期的10天之后归还该书；

update借阅表

set归还日期='2013-6-27'

where读者号='001973'and图书号='TP316/ZW6'

（4）当读者“王平”按期归还书籍后，从借阅表中删除上述借阅记录

delete

from借阅表

where读者号='001973'and图书号='TP316/ZW6'

4、针对以上3个表，完成下列单表查询

（1）查询全体图书的信息；

select*

from图书表

（2）查询全体图书信息，其中单价进行打8折操作，显示列名为“折扣价”；

select图书号,图书名,作者,出版社,0.8*单价'折扣价'

from图书表

（3）显示目前所有借阅读者信息，去掉重复行；

selectdistinct*

from借阅表

（4）显示所有单价在20-30元之间的图书信息；

select*

from图书表

where单价between20and30

（5）查询所有单价不在20-30元之间的图书信息；

select*

from图书表

where单价notbetween20and30

（6）查询机械工业出版社，科学出版社、人民邮电出版社的图书信息；

select*

from图书表

where出版社in（'机械工业出版社','科学出版社','人民邮电出版社的图书信息'）

（7）查询非人民邮电出版社的信息；

select*

from图书表

where出版社notin（'人民邮电出版社的图书信息'）

（8）查找姓名第二个字符是“建”并且只有2个字符姓名的读者信息；

select*

from读者表

where姓名like'_建'

（9）查找姓名以“王”开头的所有读者的读者号及姓名；

select读者号,姓名

from读者表

where姓名like'王%'

（10）查找以“王”、“张”、“李”开头的所有读者的读者号姓名；