《数据库系统原理》课程基本实验设置SQL SERVER.docx

1、数据库系统原理课程基本实验设置SQL SERVER目 录第一章 课程实验内容 2实验一 SQL SERVER安装、数据库创建与维护实验 2实验目的 2实验内容 2实验与要求 3实验步骤 3实验报告要求 3实验二 数据库表/视图的创建与维护实验 4实验目的 4实验内容 4实验环境 4实验要求 4实验步骤 4实验总结 5实验三 数据查询实验 5实验目的 5实验内容 5实验要求 5实验四 数据库接口实验 6实验目的 6实验内容 7实验环境 7实验步骤 7实验要求 7实验总结 8实验五 数据库完整性与安全性实验 8实验目的 8实验内容 9实验六 数据查询分析实验 9实验目的 9实验内容 10实验步骤

2、10实验总结 11实验七 数据库的事务创建与运行实验 11实验目的 11实验内容与要求 11实验总结 11实验八 数据库的备份和恢复实验 11实验目的 11实验背景知识 12实验内容 14实验步骤 14实验总结 14 第一章 课程实验内容实验一 SQL SERVER安装、数据库创建与维护实验实验目的1通过对SQL SERVER2000的安装和简单使用：（1）了解安装SQL SERVER2000的软硬件环境和安装方法；（2）熟悉SQL SERVER2000相关使用；（3）熟悉SQL SERVER2000的构成和相关工具；（4）通过SQL SERVER2000的使用来理解数据库系统的基本概念。2通

3、过创建数据库、并进行相应的维护，了解并掌握SQL SERVER数据库的创建和维护的不同方法和途径，并通过这一具体的数据库理解实际数据库所包含的各要素。实验内容1SQL SERVER2008 安装（1） 在Windows 7/XP上安装并运行SQL SERVER2008。 （2） 练习启动和停止数据库服务。 （3） 通过SQL SERVER Enterprise Manager连接数据库。（4） 熟悉SQL SERVER Enterprise Manager的各项功能，了解SQL SERVER的主要对象。 (5) 了解SQL SERVER在安装时自动创建的数据库和几类系统表。2数据库创建与维护（

4、1） 创建“学生选课”数据库,。要求主文件组包含主要文件和次要文件，建立两个次要文件组，分别包含两个次要文件，要求有相应的日志文件；（2） 对数据库属性和参数进行查询、相应的修改和维护，内容包括：调整数据库的大小，完成数据库大小的增加、减小；增加文件组；修改日志文件的最大值；查看数据库的属性值；（3） 练习数据库的删除等维护；（4） 用Enterprise Manager管理工具和交互式的Transact_SQL语句分别完成以上操作。实验与要求1SQL Server的安装实验要求学生在机器上安装SQL Server数据库系统，为后续各个实验搭建实验环境；2数据库创建与维护实验则要求面向具体应用

5、领域，利用SQL Server相关机制，创建并维护数据库系统，为后续各个实验提供前期准备；3要求学生根据以上要求确定实验步骤，独立完成以上实验内容。并在安装和数据库运行后熟悉SQL SERVER 的各种运行管理；4实验完成后完成实验报告。实验步骤使用SQL SERVER Enterprise Manager熟悉 SQL Server的安装过程。实验报告要求1. 实验目的：描述本次实验的目的。2. 实验平台及环境：介绍自己实验所基于的软硬件环境及所用到的工具。3. 实验内容：介绍本次实验的具体内容。4. 实验步骤：根据实验内容和实验过程情况介绍实验步骤。5. 实验结果及分析： 1）对实验结果进行

6、总结和分析； 2）给出完成以上实验内容的Transact_SQL语句，并对相应的SQL语句进行分析； 3）说明建立的数据库的相关内容，和维护后的相关属性的改变。6. 实验小结： 总结本次实验遇到的问题与心得。实验二 数据库表/视图的创建与维护实验实验目的1 通过进行数据库表的建立操作，熟悉并掌握SQL SERVER数据库表的建立方法，理解关系数据库表的结构，巩固SQL标准中关于数据库表的建立语句；2 通过进行数据库表数据的增加、删除和插入等维护操作，熟悉并掌握SQL SERVER数据库数据的操作方法，巩固SQL中关于数据维护的语句；3 通过对SQL SERVER中建立、维护视图的实验，熟悉SQ

7、L SERVER中对视图的操作方法和途径，理解和掌握视图的概念。 实验内容 建立相应的表及视图，并熟悉基本操作，例如数据增删改、表结构修改等。实验环境 C/S结构，SQL Server企业版服务器及客户端； 数据库存储在服务器上，表和视图定义也存储在服务器上，同学可通过网络使用客户端连接到服务器上进行操作。实验要求 本实验内容比较繁多，要求同学一定要进行完整实验并做出详尽记录。实验步骤1 熟悉课程实验背景知识；2 根据物理模式使用SQL Server创建相应的表（假设数据库已在上次实验做好，如果没有做好，就需要先建立自己的数据库）；3 将教师提供的数据导入表；4 对每个表都尝试增加、删除或者修

8、改一条记录，看看是否能够成功，原因何在？5 自行设置过滤条件和属性，对每一张表做一个视图；6 尝试对视图进行修改，看看是否能够成功，原因何在？实验总结 在实验中有哪些重要问题或者事件？你如何处理的？你的收获是什么？有何建议和意见等等。实验三 数据查询实验实验目的通过对实验二中建立的数据库关系表和视图的各种查询的操作，加深对SQL语言和Transact SQL查询语言的了解，掌握相关查询语句的语法和使用方法。实验内容数据库关系表查询：（1） 简单的查询操作，包括单表的查询、选择条件、结果排序等的练习；（2） 多表的连接查询，包括等值连接、自然连接等；（3） 复杂的查询操作，包括使用分组函数等库函

9、数的查询操作；（4） 练习带有IN、比较符的嵌套查询。具体内容包括：1. 简单查询:(1) 查询班号为g99401班的学生的学号和姓名； (2) 查询“数据库开发技术”课程的学分； (3) 查询选修了课程编号为“dep04_s003”的学生的学号和成绩，并将成绩按降序输出； (4) 查询学号为“g9940205”的学生选修的课程编号和成绩； (5) 查询选修了课程编号为“dep04_s001”且成绩高于85分的学生的学号和成绩。2. 在多表连接的查询实验中，在SQLSERVER提供的交互式语言环境下用Transact SQL语句完成以下查询操作： (1)查询选修了课程编号为“dep04_s00

10、2”且成绩高于85分的学生的学号、姓名和成绩； (2)查询所有学生的学号、姓名、选修的课程名称和成绩； (3)查询计算机科学系林红同学选修的课程名称、学分和成绩。(考试成绩60有学分，否则无学分。)3. 在复杂查询实验中，在SQLSERVER提供的交互式语言环境下用Transact SQL语句完成以下查询操作： (1)查询至少选修了三门课程的学生的学号和姓名； (2)查询选修课程号为“dep04_b001”的学生的平均成绩； (3)查询所有学生的学号和他选修课程的最高成绩，要求他的选修课程中没有成绩为空的。(4)查询严为老师2001/2002学年教的软件开发技术课程的最高成绩及此学生的学号、姓

11、名、班级。(5)查询数据库开发技术课程用过的教材名称，作者和出版社。(6)查询计算机科学系讲授过数据库开发技术的老师姓名和职称。4. 在嵌套查询实验中，在SQLSERVER提供的交互式语言环境下用Transact SQL语句完成以下查询操作，要求写嵌套查询语句： (1)查询选修了电磁波工程的学生的学号和姓名； (2)查询没有选修电磁波工程的学生的学号和姓名； (3)查询至少选修了学号为“g9940201”的学生所选修的所有课程的学生的学号和姓名。5. 建立如下视图： 学生选修课程信息视图，包括以下内容：1）学生学号、姓名、所在系、授课老师姓名、课程名称、课程教材名称、出版社、学分、选课成绩 2

12、）修改以上视图，增加学生所在班级信息。 3）对以上视图进行相关的查询操作： (1) 查询选修了计算机基础的学生的学号和成绩； (2) 查询所有学生的学号、姓名、选修的课程名称和成绩； (3) 查询选修了乔红老师课程的学生的学号和姓名、课程名称。实验要求(1) 分别用Enterprise Manager和Transact_SQL语句完成以上操作(2) 要求学生独立完成以上内容。(3) 实验完成后完成要求的实验报告内容。实验四 数据库接口实验实验目的1通过实验了解通用数据库应用编程接口ODBC的基本原理和实现机制，熟悉主要的ODBC接口的语法和使用方法；2利用C语言(或其它支持ODBC接口的高级程

13、序设计语言)编程实现简单的数据库应用程序，掌握基于ODBC的数据库访问的基本原理和方法。3尝试使用SQL Server上的工具以特定格式导出数据，初步了解现代程序设计辅助工具的使用，加深对接口和数据库与外界的联系的认识。实验内容 本实验内容主要是如何通过数据库接口访问（包括增、删、改）数据库中的数据。 要求能够通过编写程序或者使用SQL Server工具访问到数据。该实验的重点在于ODBC数据源配置和工具使用，而不在于编写有一定复杂度的程序。实验环境 C/S结构，SQL Server企业版服务器及客户端。 数据库存储在服务器上，表和视图定义也存储在服务器上，但同学通过网络使用客户端连接到服务器

14、上进行操作。实验步骤1. 实验准备：(a) 以教科书第四章关于SQL语言相关内容为基础，课后查阅、自学ODBC接口有关内容，包括ODBC的体系结构、工作原理、数据访问过程、主要API接口的语法和使用方法等。(b) 以实验二建立的数据库为基础，编写 C语言(或其它支持ODBC接口的高级程序设计语言) 数据库应用程序，按照如下步骤访问数据库i. Step1. ODBC初始化，为ODBC分配环境句柄ii. Step2. 建立应用程序与ODBC数据源的连接iii. Step3. 利用SQLExecDirect语句，实现数据库应用程序对数据库中表（有数据）进行数据查询、修改、删除、插入等操作。要求先打印

15、出所有记录，然后删除一行，再打印一次，进行修改，再打印一次，最后插入，再打印一次。iv. Step4. 结束数据库应用程序 因为这不是程序设计练习，所以只针对一张表就可以了。我们也不要求做出很好的界面，和允许用户选择如何操作数据，只要是完成基本功能就可以了。2. 在Windows控制面板中通过管理工具下的ODBC数据源工具在客户端新建连接到SQL Server服务器的ODBC数据源，测试通过后保存，注意名字要和应用程序中引用的数据源一样。3. 编译程序并调试通过。4. 利用数据库管理工具中的Web 向导，创建一个查询MS表的信息网页。实验要求1要求所编写的数据库访问应用程序中使用到以下主要的O

16、DBC API函数：(1) SQLALLocEnv：初始化ODBC环境，返回环境句柄(2) SQLALLocConnect：为连接句柄分配内存并返回连接句柄(3) SQLConnect：连接一个SQL数据资源 (4) SQLDriverConnect连接一个SQL数据资源，允许驱动器向用户询问信息(5) SQLALLocStmt 为语句句柄分配内存, 并返回语句句柄 (6) SQLExecDirect 把SQL语句送到数据库服务器，请求执行由SQL语句定义的数据库访问(7) SQLFetchAdvances将游标移动到到查询结果集的下一行(或第一行)(8) SQLGetData 按照游标指向的

17、位置，从查询结果集的特定的一列取回数据(9) SQLFreeStmt 释放与语句句柄相关的资源(10) SQLDisconnect 切断连接(11) SQLFreeConnect 释放与连接句柄相关的资源(12) SQLFreeEnv 释放与环境句柄相关的资源实验总结 在实验中有哪些重要问题或者事件？你如何处理的？你的收获是什么？有何建议和意见等等。实验五 数据库完整性与安全性实验实验目的1. 通过对完整性规则的定义实现，熟悉了解SQL SERVER中完整性保证的规则和实现方法，加深对数据完整性的理解。2. 通过对安全性相关内容的定义，熟悉了解SQL SERVER中安全性的内容和实现方法，加深

18、对数据库安全性的理解实验内容完整性实验与要求：1. 分别定义数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；2. 向学生表插入具有相同学号的数据，验证其实体完整性约束；3. 向学生表中插入一条数据，班级号是学生表的外键，验证参照完整性约束；4. 删除教师表中的所有数据，验证参照完整性约束；5. 定义存储过程，完成查询学生表中学号=g9940206的行，并执行。6. 定义触发器，当向学生表插入一条记录时，将所有学生的学号值加1；并对其进行测试分别用Enterprise Manager和T_SQL完成以上操作。安全性实验内容与要求：1. 定义一新的登陆帐号、数据库用户，并授予其访问

19、学生选课数据库的读权限；2. 分别采用Windows NT/2000验证方式和Windows NT/2000及SQL Server混合验证方式用不同的用户进行登录连接；3. 分别用sa用户和新定义的用户访问通信数据库，并对其中的学生表数据进行修改；4. 创建数据库角色，并授予访问通信数据库的读、写权限；5. 将角色赋予（1）中定义的用户，建立用户和角色联系；6. 再次用此用户访问通信数据库，并对其中的学生表数据进行修改。分别用Enterprise Manager和Transact_SQL语句分别完成以上内容。实验六 数据查询分析实验实验目的1. 熟悉了解SQL SERVER中查询分析器的使用，

20、理解数据库查询优化的基本概念。2. 通过对不同情况下查询语句的执行情况的对比分析，巩固加深查询优化的理解，并进行书写优化SQL语句的初步训练，提高编写高效SQL语句进行数据查询的能力。实验内容 实验中要进行表中记录数多少、结果集大小、有无索引、不同书写方式的等效SQL、多表连接查询等情况进行查询计划分析，并比较各种查询计划的效率优劣。实验步骤1. 建立两张表：-班级课程（课程号，班级号） -教师_课程_班级（教师编号，课程号，班级号，学期，学年，排课标识，授课地点，教材编号）书写脚本在-班级课程表上插入100条记录，在-班级课程表上插入10000条记录。2. 索引对查询的影响在学生名称上建立非

21、簇集索引，进行下列查询：对班级号查询一个范围内的所有记录（要注意结果集不大于20条记录）。对课程号进行大范围查询（就是结果集包括几乎所有记录，比如有90条以上的记录）。分析三种情况下的查询计划有何不同？下同，略。3. 表中记录数多少的影响：如果学生表中只有一条记录，重复上面的三个查询。4. 不同索引类型对查询的影响在学号表上建立簇集索引，重复上面的三个查询。如果没有不同，可能是建立簇集索引不立即导致表中记录重新排列的缘故，如何启动这种重组过程？5. 索引代价：在有一般索引、簇集索引和无索引的情况下插入数据（例如在选课情况表SC 上插入数据），比较插入的执行效率。注意不要只插入一条记录，应该插入

22、一批（比如200条索引键值比较集中的记录）记录，这样方可添满为了性能而预留的部分空间，从而测出真实的效率。6. 对相同查询功能不同查询语句的执行比较分析（1） 查询至少选修了三门课程的学生的学号和姓名；利用和不利用group by，比较其查询效率，并分析。（2） 重写后的查询一定比原始查询更优吗？通过执行分析结果。7. 查询优化除了建立适当索引，对SQL 语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。设计实现下列查询，使之运行效率最高。写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。(1)查询选修了课程编号为“dep04_s002”且成绩高于85分的学

23、生的学号、姓名和成绩； (2)查询所有学生的学号、姓名、选修的课程名称和成绩； (3)查询计算机科学系林红同学选修的课程名称、学分和成绩。(考试成绩60有学分，否则无学分。实验总结 在实验中有哪些重要问题或者事件？你如何处理的？你的收获是什么？有何建议和意见等等。实验七 数据库的事务创建与运行实验实验目的 通过实验，了解SQL Server数据库系统中各类数据库事务的定义机制和基于锁的并发控制机制，掌握QL Server数据库系统的事务控制机制。实验内容与要求1 定义三种模式的数据库事务（1）启动显式事务；（2）在事务内设置保存点；（3）启动隐式事务模式；（4）设置事务的隔离级别为重复读。2

24、察看事务的锁信息和隔离级别3 利用SQL语句和数据库API函数控制事务4 利用事务调试语句，查看事务相关信息，调试事务实验总结 在实验中有哪些重要问题或者事件？你如何处理的？你的收获是什么？有何建议和意见等等。实验八 数据库的备份和恢复实验实验目的1. 理解SQL Server 的数据备份和恢复机制。2. 掌握SQL Server 的数据备份和恢复的基本概念，例如备份方式（增量备份和完全备份）、备份介质（文件或者设备）等等。3. 掌握备份和恢复的实际操作，能够备份和将备份恢复，特别是能够恢复到一个新的数据库中。4. 理解备份策略或者备份计划的概念，实际书写脚本进行自动备份。实验背景知识数据库系

25、统运行时由于软硬件故障、误操作、自然灾害、病毒或者人为攻击等多种原因可导致数据库系统中的数据丢失或被破坏，为了防止这种情况严重影响数据库系统安全运行，所有数据库系统都有系统数据恢复与备份机制，SQL Server也不例外。数据库恢复是指将数据库从错误状态（如违反了数据库完整性约束）恢复到某种正确状态。数据库恢复必须依赖数据备份。数据备份是就是制作数据副本，以便在数据库遭到破坏时可以将数据副本重新加载到数据库中，以便修复数据库。数据库中数据量可能很大，所以节省备份时使用的存储介质空间可能是一个重要问题。人们提出了完全备份和增量备份两种概念。前者是独立的备份，而后者是在存在某一个完全备份的基础上，

26、下一次备份只记录自完全备份以来发生变化的数据，从而大大减少占用的介质空间。数据库中的数据往往是处于不断变化之中的，为了有效应对各种数据丢失或者损坏，仅仅靠一个备份是不够的。因此就提出了备份计划或者备份策略的问题。比如说采用如下备份策略：每年做一个完全备份，永久保留。每月做一个完全备份，保留1年。每周做一个增量备份，保留2月。每日做一个增量备份，保留1周。这就是一个备份计划，按此计划执行，如果我的数据库出了问题，不仅仅可以回到最近的备份，还可以废掉相当长时间的错误数据，回到以前某一时刻的正确状态上去。我们发现时间越久则保存下来的备份密度越稀疏，这恰好适应出大问题返回到很早以前的可能毕竟很小。这样

27、数据库的备份数量不会太多，大约是7个日备份+9个周备份+12个月备份+若干年备份，对存储介质的要求不会太高。 数据备份（1） 备份内容SQL Server数据库由系统数据库和用户数据库2部分组成，其中系统数据库包括master、msdb和model数据库。系统数据库应当被完全备份。用户数据库中的数据根据实际情况可分为关键数据和非关键数据。用户数据库备份可针对关键数据进行。（2） 备份执行者具有下列角色的成员可以执行备份操作：a. 固定的服务器角色sysadmin-系统管理员b. 固定的数据库角色db_owner数据库所有者c. 固定的数据库角色db_backupoperator允许进行数据库备

28、份的用户（3） 备份时机当系统数据库master、msdb和model被修改时, 应当进行备份。例如，当执行Create /Alter/Drop Database等T-SQL命令或系统存储过程时，master数据库将被修改，应当进行备份。对用户数据库，备份时机为：创建数据库、加载数据库、为数据库创建索引、当执行了不带日志T-SQL命令时（如BACKUP LOG WITH NO_LOG）。（4） 备份方法完全备份每次都备份整个数据库或事务日志。增量备份只备份自上次备份以来发生过变化的数据库的数据。SQL Server可以只备份数据库，也可以备份数据库和事务日志。这两种方式可以同完全备份或增量备份

29、相结合。另外，当数据库很大时，可以进行个别文件或文件组的备份，从而将数据库备份分割成多个较小的备份过程。SQL Server支持以下四种备份方法：a. 完全数据库备份b. 数据库和事务日志备份c. 差异备份d. 数据库文件或文件组备份这四种备份方法的具体执行形式参见附件二。（5） 备份周期/频率确定备份频率可考虑系统恢复的工作量和系统执行的事务量。例如，如果系统环境为联机事务处理，则应当经常备份数据库。如果系统只执行少量作业，则备份频率可低一些。不同的备份方法所对应的备份频率也不一样。如，完全数据库备份的备份频率可低一些，而增量/差异数据库备份的频率可高一些。（6） 备份操作和备份命令进行数据

30、库备份时，首先应使用企业管理器或存储过程创建用于存储备份的备份设备，然后利用企业管理器、备份向导、或T-SQL语句BACKUP进行备份。具体的创建备份设备和执行备份操作的方法和命令参见附件二。 数据恢复系统恢复包括以下2个步骤：a. 准备工作包括系统安全性检查和备份介质验证。b. 执行数据库恢复操作 使用企业管理器或T-SQL语句执行实际的数据库恢复操作。（1） 检查点（check-point）SQL Server利用检查点支持数据库恢复。系统在检查点处，从当前数据库的内存缓冲区中将所有脏数据页和脏日志页（即检查点之前修改过但尚未写回磁盘的数据页或日志）写入刷新到磁盘数据文件中。当进行数据库恢复时，只从最后一个检查点处，开始检查数据库日志，进行数据库恢复的前滚操作。事务日志的每个记录都由个唯一的日志序号LSN标示，事务的所有日志纪录链接在一起。检查点执行以下操作：a. 将标记检查点开始的纪录写入日志文件b. 将检查点纪录的信息存储在检查点日志记录链内，将这条链起点的LSN写入数据库的根页。c. 记录最小恢复日志序号MinLSNMinLSN = min 检查点起点LSN, 最旧的活动事务起点的LSN，最早的复制事务起点的LSN（该事务尚未复制到所有相关服务器）d. 记录所有未完成的活动事务列表e