《数据库原理与应用》实验报告书II.doc

《《数据库原理与应用》实验报告书II.doc》由会员分享，可在线阅读，更多相关《《数据库原理与应用》实验报告书II.doc（46页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库原理与应用实验报告II（20112012学年第二学期）班 级： 外包 学 号： 姓 名： 梁宇富 教 师： 陈培正 医药信息工程学院数据决策2012年2月目 录实验一 熟悉ACCESS及SQL Server 2005的基本操作1实验二 数据库的附加/分离、备份/还原与导入/导出1实验三 数据库、表的创建、修改及删除.3实验四 简单查询3实验五 连接查询和嵌套查询7实验六 带函数查询和综合查询5实验七 数据的插入、修改、删除.9实验八 视图的操作、索引的创建与删除7实验九 SQL Server数据库的安全性控制11实验十 数据完整性10实验十一 触发器的使用13实验十二 熟悉Power D

2、esigner数据库设计13实验十三 Power Designer中概念模型与物理模型的设计. 15实验十四 Transact-SQL编程14实验十五 存储过程的使用16实验十六 用visual studio连接数据库16附录：SchooI数据库说明17实验一 熟悉ACCESS及SQL Server 2005的基本操作一、实验目的 1熟知机房用机安全规则。2通过上机操作，加深对数据库系统理论知识的理解；通过使用具体的DBMS，了解一种实际的数据库管理系统，并掌握其操作技术；通过对实际题目的上机实验，提高手动能力，提高分析问题和解决问题的能力。3用ACCESS创建数据库，体会数据库的功能。4熟悉

3、SQL Server2005企业版的安装过程。掌握SQL Server 2005相关服务的启动、退出。5初步了解数据库的概念，用ACCESS创建数据库，体会数据库的功能。 6熟悉SQL Server Management Studio环境，掌握数据库服务器的注册、配置、连接等操作。注意：每次实验过程中创建的各种数据库及其他一切档案，建议同学们都保存好，以备接下来的实验课中使用。每次上课前，请也请带上之前实验课的数据备份。二、实验内容及要求1上网搜索能够正常安装的SQL Server2005的软件。有条件的同学，课后可在个人电脑上安装SQL Server2005，建议XP操作系统安装个人版，Se

4、rver操作系统安装企业版。SQL Server2005的安装说明见SQL Server 2005精简版的安装或上网搜索相应电子教程。安装过程请参看实验指导或者相关视频。2了解数据库的相关概念。通过实践初步了解使用SQL Server Management的使用。3请根据联机丛书查询如何“创建数据库”，查询内容包括创建数据库前的准备工作，创建数据库的命令，以及数据库文件的组成。请把你的结果写在下面。4创建服务器组，在此组上查看有哪些数据库及数据库对象，请记录下来。5利用ACCESS创建数据库。(1)。在ACCESS中创建一个“学生-课程数据库”，含以下三个表：学生(学号，姓名，性别，年龄，所在

5、系) 课程(课程号，课程名) 选课(学号，课程号，成绩)每个表输入10条数据。(2)。数据库保存在以“自己学号+名字”命名的文件夹中。请掌握ACCESS创建数据库的方法，体会数据库的二维结构，请写下建立数据库的主要步骤。6。掌握SQL Server2005的启动、退出、暂停，组成配置，基本工具及常用操作。请简要叙述SQL Server2005的基本组件和基本工具。三、实验小结结合本次实验，思考以下问题：1通讯录为什么要采用这种由行和列二维结构组成的格式？其中每一列表示什么意思，每一行又表示什么意思？2比较分析ACCESS和SQL SERVER两种数据库，各适用于什么情况？ 3常用数据库有哪些？

6、当下比较流行的又有哪些？实验二 数据库的附加/分离、备份/还原与导入/导出一、实验目的1、理解备份的基本概念，掌握各种备份数据库的方法。2、掌握如何从备份中还原数据库。3、掌握数据库中各种数据的导入导出。4、掌握数据库的附加与分离，理解数据库的附加与分离的作用。二、实验内容1、使用老师给的数据库文件“(中)学生课程选课_Data.MDF”在服务器上附加一个(中)学生课程选课数据库。2、完成对该数据库的备份。3、更改该数据库内容，比如，删除一个表，或者在数据库中增加一个新表。4、还原该数据库，使它回到刚刚附加的时候的内容。5、分离该数据库，并把它拷到自己带的U盘，回去后试着在自己机器上安装sql

7、 server 2005，然后再重复操作一遍。6、把“(中)学生课程选课”数据库中的“计算机系学生”表转换到ACCESS数据库中，尝试将其导出到EXCEL表格中。7、把实验一创建的ACCESS数据库“学生-课程数据库”导入到SQL Server 2005中。注意：每次实验过程中创建的各种数据库及其他一切档案，建议同学们都保存好，以备接下来的实验课中使用。每次上课前，请也请带上之前实验课的数据备份。三、实验要求1、完成对数据库“学生课程选课”的附加分离、备份还原与导入导出。2、进行“实验内容”中的2-4，在完成3和4后，数据库分别有什么变化？四、实验小结 1. 完整备份和完整差异备份有什么区别?

8、2. 什么情况下应该使用数据库的备份和还原？什么情况下应该使用数据库的数据导入和导出？3.什么时候需要使用数据库的附加和分离？附加和分离的关系是什么？4.以后上实验课你会使用以上技术吗？你会选择哪一种？为什么？实验三 数据库、表的创建、修改及删除一、实验目的1进一步掌握Management Studio的使用；了解SQL Server数据库的逻辑结构和物理结构。2. 掌握SQL图形用户界面创建、修改、删除数据库、表；掌握SQL中Create Database、Drop Database命令的使用。3. 掌握如何在表中添加、修改、删除数据；掌握SQL中Create Table、Alter Tab

9、le、Drop Table命令的使用。4理解SQL中的标识列、计算列和各种约束。5了解表的结构特点；掌握在SSMS(SQL Server Management Studio)中修改表的定义。6掌握使用T-SQL命令修改表的定义、删除表。二、实验内容1.用Management Studio创建数据库（1）创建数据库bookdb，bookdb数据库采用系统提供的默认设置；查看数据库相关参数设置。（2）删除bookdb数据库。 （3）使用T-SQL命令创建数据库EDUC，EDUC如下表：数据文件日志文件逻辑名EDUC_datEDUC_log物理文件名C:EDUC_DAT.MDFC: EDUC_LOG

10、.LDF初始长度4MB1MB最大长度10MB无限制增量1MB10 在EDUC中创建三个表，分别为Student，Course，SC。三个表的的字段名、类型以及长度请参看下表：Student：字段名类型长度主键允许空含义SnoVarchar20YN学号SnameVarchar10N姓名SageInt年龄SsexVarchar2性别SdeptVarchar30所在系Course：字段名类型长度主键允许空含义CnoVarchar15YN课程号CnameVarchar30课程名CpnoVarchar15先行课CcreditInt学分SC：字段名类型长度主键允许空含义SnoVarchar20YN学号Cn

11、oVarchar15YN课程号GradeDecimal9,2成绩2.请在各表中添加数据对于Student表，请自行输入如下数据3.请把student表中的0110号同学的年龄改为25。4.请删除学号为0106号的同学。5.请把上述操作在使用SQL语句完成一遍。包括创建数据库EDUC和删除数据库，创建数据表student、course、sc。6使用SQL语句对数据库EDUC中的数据表的定义进行修改。注意：每次实验过程中创建的各种数据库及其他一切档案，建议同学们都保存好，以备接下来的实验课中使用。每次上课前，请也请带上之前实验课的数据备份。三、实验要求（一）、数据库、表的创建及删除1.将数据库bo

12、okdb的相关属性，填入下表：项目内容数据库所有者数据库名称数据库逻辑文件名数据文件物理存放位置数据库初始大小可用空间(数据文件最大值)数据文件增长量日志逻辑文件名日志文件物理存放位置日志文件初始大小日志文件可用空间日志文件增长量根据“二、实验内容”的要求，使用T-SQL命令创建/删除数据库EDUC，创建数据表student、course、sc。2.删除bookdb数据库。3.创建数据库EDUC。CREATE DATABASE EDUCON(NAME=EDUC_Data,FILENAME=C:EDUC.MDF, -物理文件名SIZE=4MB, /* 数据库初始大小*/MAXSIZE=10MB,

13、FILEGROWTH=1MB)LOG ON(NAME=EDUC_log,FILENAME=C: EDUC_LOG.LDF,SIZE=1MB,FILEGROWTH=10%)GO4.创建数据表Student。create table Student(Sno Varchar(20) not null, Sname Varchar(10) not null,Sage Int,Ssex Varchar(2) ,Sdept Varchar(30),primary key(Sno) 5.创建数据表Course。create table Course(Cno Varchar(15) not null,Cnam

14、e Varchar(30),Cpno Varchar(15),Ccredit int,primary key(Cno) )6.创建数据表SC。create table SC (Sno Varchar(20) not null, Cno Varchar(15) not null, Grade Decimal(9,2) )（二）、修改基本表的定义1修改列属性（1）用SSMS将Student表中的Birthday字段设为不能为空（not null）。alter table Studentalter column birthday varchar(30) not null（2） 用SQL语句将Stud

15、ent表中的属性Sno varchar(20)改成char(8)类型。ALTER TABLE Student ALTER COLUMN Sno varchar(20) NOT NULL2添加列（1）用SSMS在Course表中添加一列year，类型为varchar（4），默认置为空。alter table courseadd year varchar(4) default(null)（2）用SQL语句在year字段添加约束，year 的属性值在2006-2012之间。 ALTER TABLE Course ADD CHECK (year BETWEEN 2006 AND 2012)3删除列（1

16、）用SQL语句将Course表中的year字段删除。alter table coursedrop column year四、实验小结 1.体会创建数据库、表的两种方式，有什么不同？各有什么优缺点？各适用于什么情况？2试述T-SQL的特点。3试述T-SQL的定义功能。实验四 简单查询一、实验目的1掌握Management Studio的使用。2掌握SQL中Select命令的使用。二、实验内容及要求用SQL语句完成下列查询。使用数据库为老师提供的SCHOOL数据库。1. 统计各门课程所选修的人数，并把结果存储在一张新表中。（提示：用SelectInto，group by）SELECT Course

17、_id AS 课程,COUNT(*) AS 人数into class_sumFROM StudentGradeGROUP BY Course_id2查询男生的资料。SELECT *FROM Studentwhere Stu_sex=男3查询所有计算机系的班级信息。SELECT Class_id,class_name,Director,Monitor,Class.Depar_idFROM Class,Deparmentwhere Depar_name=计算机系 AND Class.Depar_id=Deparment.Depar_id4查询艾老师所教的课程号。SELECT distinctCou

18、rseTeacher.Course_id AS 课程号,Teac_name AS 教师名FROM CourseTeacher,Teacherwhere Teac_name LIKE 艾% AND CourseTeacher.Teac_id=Teacher.Teac_id5查询年龄小于30岁的女同学的学号和姓名。SELECT Stu_id AS 学号,Stu_name AS 姓名,年龄=year(Getdate()-YEAR(birthday)FROM StudentWHERE Stu_sex=女 AND year(Getdate()-YEAR(birthday)80ORDER BY 成绩 DE

19、SC三、实验小结1此次实验中得到的哪些经验教训、疑难问题？有什么心得或总结？。实验五 连接查询和嵌套查询一、实验目的1掌握Management Studio的使用。2掌握SQL中连接查询和嵌套查询的使用。二、实验内容及要求用SQL语句完成下列查询。使用数据库为老师提供的SCHOOL数据库。1找出所有任教“数据库”的教师的姓名。SELECT DISTINCT Teacher.Teac_nameFROM Teacher,Course,CourseTeacherWHERE Course.Course_name=数据库 AND Course.Course_id=CourseTeacher.Course

20、_id AND CourseTeacher.Teac_id=Teacher.Teac_id2取出学号为“”的学生选修的课程号和课程名。SELECT StudentGrade.Stu_id,Course.Course_id,Course.Course_nameFROM StudentGrade,CourseWHERE StudentGrade.Stu_id= AND StudentGrade.Course_id=Course.Course_id3“涂杰杰”所选修的全部课程号及成绩。（注意：school中有同名，即有两名学生叫“涂杰杰”。）SELECT Student.Stu_id,Student

21、.Stu_name,StudentGrade.Course_id,StudentGrade.GradeFROM Student,StudentGradeWHERE Student.Stu_name=涂杰杰 AND Student.Stu_id=StudentGrade.Stu_id4C语言成绩比数据结构成绩好的学生（自身连接）。SELECT Student.Stu_name,Course1.Course_name,StudentGrade1.Grade, Course2.Course_name,StudentGrade2.Gradefrom Student,Course Course1, Co

22、urse Course2,StudentGrade StudentGrade1,StudentGrade StudentGrade2where Course1.Course_name=C语言 AND Course1.Course_id=StudentGrade1.Course_id AND Course2.Course_name=数据结构 AND Course2.Course_id=StudentGrade2.Course_id AND StudentGrade1.Stu_id=StudentGrade2.Stu_id AND StudentGrade1.GradeStudentGrade2.

23、Grade AND Student.Stu_id=StudentGrade1.Stu_id5显示所有课程的选修情况（外连接）。SELECT Course.Course_id,Course.Course_name,COUNT(StudentGrade.Grade) 人数from Course left JOIN StudentGrade ON(Course.Course_id=StudentGrade.Course_id)Group by Course.Course_id,Course.Course_nameorder by 人数 desc6检索选修课程号为“0109”或“0111”的学生学号、

24、姓名和所在班级。SELECT StudentGrade.Course_id,Student.Stu_id,Student.Stu_name,Class.Class_id,Class.Class_namefrom StudentGrade,Student,Classwhere StudentGrade.Course_id in (0109,0111) and StudentGrade.Stu_id=Student.Stu_id and Student.Class_id=Class.Class_id7查询“0203”课程的最高分的学生的学号。SELECT TOP 1 StudentGrade.Co

25、urse_id,Student.Stu_id,Student.Stu_name,StudentGrade.Gradefrom StudentGrade,Studentwhere StudentGrade.Course_id=0203 and StudentGrade.Stu_id=Student.Stu_idorder by StudentGrade.Grade desc8没有选修以“01”开头的课程的学生信息。（用子查询完成。提示not in或not exists）1.SELECT Student.*from Student,Classwhere Student.Class_id not i

26、n (select Class_idfrom class where Class_id like 01%)and Student.Class_id=Class.Class_idorder by Stu_id2.SELECT Student.*from Studentwhere exists (select *from Class where Class_id not like 01%and Student.Class_id=Class.Class_id)order by Stu_id;9找出“苏贤兴”同学所学课程的名称和成绩。(请使用连接查询和嵌套查询分别来完成) 嵌套：SELECT Cour

27、se.Course_id,student.Stu_name,Course.Course_name,StudentGrade.Gradefrom Student,Course,StudentGradewhere StudentGrade.Stu_id in(select Stu_id from Studentwhere Stu_name=苏贤兴) and exists(select *where Student.Stu_id=StudentGrade.Stu_id and StudentGrade.Course_id=Course.Course_id)连接：SELECT Course.Cours

28、e_id,student.Stu_name,Course.Course_name,StudentGrade.Gradefrom Student,Course,StudentGradewhere Stu_name=苏贤兴 and Student.Stu_id=StudentGrade.Stu_id and StudentGrade.Course_id=Course.Course_id三、实验小结1思考简单查询、连接查询与嵌套查询有什么不同？连接查询与嵌套查询有何区别与联系？2此次实验中得到的哪些经验教训、疑难问题？有什么心得或总结？实验六 带函数查询和综合查询一、实验目的1掌握Managemen

29、t Studio的使用。2掌握带函数查询和综合查询的使用。二、实验内容及要求用SQL语句完成下列功能。使用数据库为老师提供的SCHOOL数据库。1统计年龄大于30岁的学生的人数。SELECT COUNT(*) 总人数FROM StudentWHERE (year(getdate()-year(Birthday)302统计数据结构有多少人80分或以上。select count(*) 人数from StudentGrade,Course where Course.Course_name=数据结构 and course.Course_id=StudentGrade.Course_id and Stu

30、dentGrade.Grade80group by StudentGrade.Course_id3统计各系开设班级的数目(系名称、班级数目)，并创建结果表。select Depar_name 系名称,COUNT(*) 班级数目 into course_sumfrom class ,Deparment where class.Depar_id=Deparment.Depar_idgroup by Deparment.Depar_id,Depar_name4找出最多人选修的三门课的课程编号，以及统计选修人数，并按人数降序排列。SELECT TOP 3 Course_id,COUNT(*) AS 选

31、修人数 FROM StudentGradeGROUP BY Course_id ORDER BY 选修人数 DESC5统计每科目的最高分、最低分，平均分、总分，并以中文列名显示。SELECT Course_name AS 课程名,MAX(Grade) 最高分,MIN(Grade) 最低分,AVG(ALL(Grade) AS 平均分,SUM(ALL(Grade) AS 总分FROM StudentGrade,CourseWHERE StudentGrade.Course_id=Course.Course_idGROUP BY Course_name6所有成绩都在70分以上的学生姓名（提示：使用子

32、查询）SELECT Stu_id,Stu_nameFROM StudentWHERE Stu_id IN(SELECT Stu_id FROM StudentGrade GROUP BY Stu_id HAVING MIN(Grade)=70)7“数据库”课程得最高分的学生的姓名、性别和所在系（提示：使用子查询）SELECT Stu_name,Stu_sexFROM Student JOIN StudentGrade ON Student.Stu_id=StudentGrade.Stu_idWHERE Grade=(SELECT MAX(Grade) -数据库课程号的最高成绩 FROM Stu

33、dentGrade JOIN Course ON StudentGrade.Course_id=Course.Course_id WHERE Course_name=数据库) AND Course_id=(SELECT Course_id FROM Course WHERE Course_name=数据库)8至少选修了两门课及以上的学生姓名和性别SELECT Stu_name,Stu_sexFROM StudentWHERE Stu_id IN (SELECT Stu_id FROM StudentGrade GROUP BY Stu_id HAVING COUNT(Course_id)=2)

34、 9检索至少选修课程“数据结构”和“C语言”的学生学号SELECT Stu_idFROM StudentGrade JOIN Course ON StudentGrade.Course_id=Course.Course_id WHERE Course_name=数据结构 AND Stu_id IN (SELECT Stu_id FROM StudentGrade JOIN Course ON StudentGrade.Course_id=Course.Course_id WHERE Course_name=C语言 )10列出所有班名、班主任、班长、系名。（请使用连接查询；进一步考虑使用外连接，

35、因为很多班级可能是没有班长的，考虑需要显示所有班级的信息）SELECT Class_name,Stu_name,Teac_name,Depar_nameFROM Class LEFT JOIN Student ON Monitor=Student.Stu_id JOIN Teacher ON Director=Teacher.Teac_id JOIN Deparment ON Class.Depar_id=Deparment.Depar_id三、实验小结1此次实验中得到的哪些经验教训、疑难问题？有什么心得或总结？实验七 数据的插入、修改、删除一、实验目的1掌握Management Studio

36、的使用。2掌握SQL中INSERT、UPDATE、DELETE命令的使用。二、实验内容及要求用SQL语句完成下列功能。使用数据库为老师提供的SCHOOL数据库。l、新开设一门课程，名叫网络安全与防火墙，学时40，编号为“0118”，主要介绍网络的安全与主要的防火墙软件。 insert into course(Course_name,Course_id,Course_hour ,Introduce)values(网络安全与防火墙,0118,40,null)2、 先建立monitor表，其结构与student表大致一样包含student表的学号、姓名、性别和班级编号，然后把班级编号为“0101”的

37、学生的相应资料插入到monitor表中。create table monitor(Stu_id varchar(9) not null ,Stu_name varchar(8) not null,Stu_sex varchar(2) not null,Class_id varchar(4) not null) INSERT INTO monitorSELECT stu_id,stu_name,stu_sex,class_idFROM studentWHERE class_id=01013、更新所有职称为“助教”的教师职称为“助理教师”。update Teacherset TechPost=助理

38、教师where TechPost=助教4、在所有经济系班级的名称前加上“经济系”三个字。UPDATE classSET class_name=经济系+class_nameWHERE Depar_id =(SELECT depar_id FROM deparment WHERE depar_name=经济系)5、学号为“”的学生的“数据结构”课程成绩改为80分。UPDATE StudentGradeSET Grade=80where Stu_id= and Course_id=(select Course_idfrom coursewhere Course_name=数据结构)6、删除stude

39、ntgrade表中所有成续不及格的记录。DELETEFROM studentgradeWHERE grade607、删除studentgrade表中学号以“99”开头的学生选修课程的记录。DELETEFROM studentgradeWHERE Stu_id like99%8、 删除课程名为“计算机导论”的课程信息和所有这门课的选课信息DELETEFROM studentgradeWHERE course_id=(SELECT course_id FROM course WHERE course_name=C语言)DELETEFROM courseteacherWHERE course_id=(SELECT course_id FROM course WHERE course_name=C语言)