数据库原理与应用实验指导书二

高荣芳编计算机工程实验室2011年9月目录TOC\o"1-1"\h\z1实验前的准备12实验1管理数据库文件和简单的SQL查询操作153实验2SQL查询操作、SQL数据更新及视图操作184实验3SQL数据控制205实验4图书借阅信息系统数据库模式设计及应用221实验前的准备1.1课程实验标准《数据库原理与应用》是计算机科学与技术专业及相近各专业的一门重要基础理论课程。课程概括性的介绍了数据库技术发展历程，详细介绍了数据库系统的基本概念、基本原理、基本方法，阐述了数据库设计、实现的基本过程，同时也介绍了数据库系统的最新进展。通过本课程的学习，使学生牢固掌握数据库系统的基本概念和基本原理，熟悉数据库系统的主要实现方法，能够根据应用需要分析设计实用的数据库管理系统。《数据库原理与应用》是一门实践性很强的课程，通过实验实践，配合课堂教学内容加深对数据库系统的基本概念、基本原理、基本方法的理解，掌握数据库系统设计的基本思想和基本步骤，熟悉关系数据库的标准语言SQL，并对关系数据库系统的某一典型系统（如SQLServer2000）有较深入的了解，使学生对数据库系统有一个完整的、全面的认识。课程实验的目标《数据库原理与应用》课程上机实验的主要目标是：通过上机操作，加深对数据库系统理论知识的理解。通过使用具体的数据库管理系统，了解一种实际的数据库管理系统，并掌握其操作技术。通过实验题目的上机实践，提高学生的实践动手能力，提高学生的分析问题和解决问题的能力。主要软件实验环境操作系统为MicrosoftWindows2000或更高级的版本。数据库管理系统为MicrosoftSQLServer2000标准版或更高级的版本。课程实验的基本要求(1)每次实验前，教师需要向学生讲清楚本次实验的目的和基本要求；学生应当先弄清楚相关的理论知识，预习实验内容、方法和步骤，认真准备好实验程序和数据，避免出现盲目上机的行为。(2)实验1人1组，在规定的时间内，由学生独立完成，出现问题时，教师要做好指导，但不能包办代替。(3)任课教师要在实验课前清点学生人数，实验中按要求做好学生实验情况及结果记录，实验后认真填写实验记录、批改实验报告。(4)学生最好自备计算机，课下能多做练习，以便能够熟悉和精通实验内容。4.实验项目设置及考核本课程设置了八个实验项目，共计20学时。具体情况如下：实验1数据库和数据表基本操作4学时实验2数据查询2学时实验3索引及视图操作2学时实验4存储过程和触发器2学时实验5T-SQL程序设计2学时实验6数据库的完整性控制2学时实验7安全管理2学时实验8图书借阅信息系统数据库模式设计及应用4学时本课程实验采用实验结果检查登记和课后实验报告相结合的方式，在满足设计要求的前提下，应用多种测试数据进行验证，检查结果的正确性。实验成绩按优秀、良好、中等、及格和不及格五级制记载。1.2SQLServer2000简介MicrosoftSQLServer起源于SybaseSQLServer。1988年，由Sybase公司、Microsoft公司和Asbton-Tate公司联合开发、运行于OS/2操作系统上的SQLServer诞生了。1992年，两公司将SQLServer移植到了WindowsNT操作系统上。之后，Microsoft公司致力于WindowsNT平台的SQLServer开发，而Sybase公司则致力于UNIX平台的SQLServer的开发。SQLServer2000是运行于Windows操作系统的的关系型数据库管理系统，是新一代电子商务、数据仓库和数据库解决方案。它功能强大、操作简便，用户能方便快捷地管理数据库开发应用程序。1.2.1SQLServer2000的主要版本1.企业版EnterpriseEdition支持所有的SQLServer2000特性,可作为大型Web站点、企业OLTP联机事务处理以及数据仓库系统等的产品数据库服务器。2.标准版StandardEdition用于小型的工作组或部门。3.个人版PersonalEdition用于单机系统或客户机。4.开发版DeveloperEdition用于程序员开发应用程序。这些程序需要SQLServer2000作为数据存储设备。等等……。1.2.2SQLServer2000的安装下面以在Windows2000professional上安装SQLServer2000PersonalEdition为例，详细介绍其安装过程。步骤如下：1.将SQLServer2000的光盘放入光驱，则会出现安装程序启动画面；2.选择安装SQLServer2000组件，出现安装组件画面；3.选择安装数据库服务器，出现SQLServer安装向导，点击下一步；在计算机名对话框中，选择本地计算机，点击下一步；5.在安装选择对话框中，选择创建新的SQLServer实例，点击下一步；6.在用户信息对话框中，输入用户、公司，点击下一步；7.在软件许可证协议对话框中，点击是；8.在安装定义对话框中，选择服务器和客户端工具，点击下一步；9.在实例名对话框中，安装默认方式的实例，点击下一步；10.在安装类型对话框中，选择典型安装，点击下一步；11.在服务账户对话框中，接受默认选项；在服务设置中，选择使用本地系统账户，点击下一步；12.在身份验证模式对话框中，选择混合模式，并为sa添加密码，点击下一步；13.当完成以上选择后，出现开始复制文件对话框，点击下一步，安装程序开始复制文件，安装完成。1.2.3SQLServer2000客户端工具介绍SQLServer安装成功后，可在开始菜单中看到客户端工具，如下图所示：在开始菜单中打开服务管理器，按开始/继续按钮，启动SQLServer服务。启动启动SQLServer服务暂停暂停SQLServer服务停止停止SQLServer服务2.企业管理器在开始菜单中打开企业管理器，展开树状目录，可创建新表、添加数据、新建用户等。选择对象选择对象(表、视图、存储过程、用户、角色……)，点击右键可进行相应的操作在开始菜单中打开查询分析器，先选择Northwind数据库，并在在编辑器窗格中创建脚本，执行脚本，显示执行结果。选择数据库选择数据库编辑器窗格编辑器窗格执行脚本执行脚本1.2.4查询分析器介绍SQLServer2000的查询分析器是一种特别用于交互式执行SQL语句和脚本的极好的工具。下面简单介绍查询分析器的启动，如何在查询分析器中实现建表、插入记录、查询记录等操作。1.启动数据库服务软件SQLServer2000的查询分析器：在程序菜单中选择MicrosoftSQLServer，如下图所示：再选中查询分析器，点击查询分析器后，出现连接到SQLServer窗口：点击按钮，出现选择服务器窗口选择本地服务(Local)，点击确定按钮。再点击连接到SQLServer窗口的确定按钮。出现SQL查询分析器主界面，如下图所示。选择查询菜单，点击更改数据库，如下图所示。出现选择数据库窗口，如下图所示。选择在上次实验中建立的数据库Test，点确定按钮。2．在查询分析器中建立表：在查询分析器的查询窗口中输入SQL语句，如下图所示。点击按钮，执行该SQL语句，在查询窗口下部出现一个输出窗口，如下图所示。提示命令成功完成，或者报告出错信息。3.在查询分析器中向表添加数据：在查询分析器的查询窗口中输入SQL语句，如下图所示。点击按钮，执行该SQL语句，在查询窗口下部出现一个输出窗口，如下图所示。使用查询分析器从表中查询数据在查询分析器的查询窗口中输入SQL语句，如下图所示。点击按钮，执行该SQL语句，在查询窗口下部出现一个输出窗口，如下图所示。1.3实验报告格式实验名称（居中）实验目的实验环境实验内容实验步骤实验总结2实验1数据库和数据表基本操作一、实验目的巩固数据库的基础知识。掌握利用企业管理器和使用Transact-SQL创建数据库、表和修改、删除数据库、表等。掌握使用SQLServer查询分析器接收Transact-SQL语句和进行结果分析、保存。掌握使用SQLServer查询分析器向已经建好的表中添加信息、修改信息、删除信息和进行信息的简单查询。二、实验环境硬件环境：PC机软件环境：操作系统为MicrosoftWindows2000或以上版本。数据库管理系统为MicrosoftSQLServer2000标准版或企业版。三、实验内容熟悉SQLServer的安装步骤和环境。熟悉SQLServer企业管理器和查询分析器工具的基本使用方法。利用企业管理器和使用Transact-SQL创建数据库、表和修改、删除数据库、表等。查看数据库属性，查看和修改表结构和表内容。练习使用SELECT、INSERT、UPDATE语句对数据表进行基本的操作。四、实验步骤熟悉SQLServer2000安装步骤和环境。使用企业管理器完成：建立学生_课程数据库;查看学生_课程数据库的属性，并进行修改，使之符合要求;在建好的学生_课程数据库中建立学生、课程和选课3个表，其表结构为：学生S（学号，姓名，性别，年龄，所在专业名称）课程C（课程号，课程名，任课教师名，开设的学期）选课SC（学号，课程号，成绩）)要求为每个表定义其主键，每个字段选择合适的数据类型，设置是否允许空值和默认值，给每个表中至少插入5条学生信息等；举例：学生表S学号姓名性别年龄所在专业名称200213808李枫女22软件工程200225645张强男29软件工程200275613李平男30英语200213808王依萍女18网络工程200224119胡东男24网络工程200213828李小玲女20英语……………课程表C课程号课程名任课教师名开设的学期720数据结构朱虹5730离散数学赵强4825高等数学朱虹2621英语李兰1623英语写作李兰2…………选课表SC学号课程号成绩200213808720852002256457306820021380873070200275613621902002138286217820022564582586200213808825692002756137308420021382873089………向各表中输入数据，查看各表的结构和内容；修改数据库、表等；删除数据库、表等。通过查询分析器，使用Transact-SQL完成：建立学生_课程数据库;在建好的学生_课程数据库中建立学生、课程和选课3个表;查看各表的结构；修改数据库、表等；删除数据库、表等；将使用Transact-SQL完成的建立数据库、表等操作的语句序列保存；在学生表中至少再插入10条新生信息；在课程表中至少再插入5门新课程信息；在选课表中至少再插入10条学生选课信息；检索“软件工程”专业女生的学生的学号和姓名；检索全体学生的姓名，年龄和所在专业；将“软件工程”专业学生全部课程成绩全部提高5分；(10)删除学号为“200213808”的退学学生的有关信息。五、实验总结要求写出通过查询分析器，使用Transact-SQL完成操作的语句序列。分析实验结果，分析实验中出现的问题及解决方法,书写实验体会等。3实验2数据查询一、实验目的加深对SQL语言和Transact-SQL语言的查询语句等数据操纵语句的理解。熟练掌握SQLServer查询分析器的使用方法。熟练掌握数据的简单查询、连接查询、嵌套查询和集合查询；熟练掌握数据查询中的分组统计、排序等操作；熟练掌握多表数据之间的内连接、外连接等操作；熟练掌握利用查询进行的数据的增加、修改和删除操作。二、实验环境硬件环境：PC机软件环境：操作系统为MicrosoftWindows2000或以上版本。数据库管理系统为MicrosoftSQLServer2000标准版或企业版。三、实验内容熟悉SQLServer企业管理器和查询分析器工具的使用方法。利用Transact-SQL实现数据的简单查询、连接查询、嵌套查询和集合查询；利用Transact-SQL实现数据查询中的分组统计、排序等操作；利用Transact-SQL实现多表数据之间的内连接、外连接等操作；利用Transact-SQL实现通过查询进行的数据的增加、修改和删除操作。四、实验步骤熟悉SQLServer企业管理器和查询分析器工具的使用方法。利用企业管理器实现：(1)向已经建好的学生_课程数据库中的学生、课程和选课3个表中增加数据、修改数据、删除数据。(2)进入SQLServer查询分析器，首先打开利用实验1已经建立并保存过的学生_课程数据库及三个表的语句序列并执行，然后利用Transact-SQL实现：1)检索未选修任何课程的学生学号；检索赵老师所授的课程号，课程名；检索所有姓李的同学的基本信息；检索选修“数据结构”课程的学生的学号；检索年龄介于张强同学年龄和28岁之间的学生的基本信息；检索至少选修了一门朱虹所讲授课程的学生姓名，学号；将学生表中查询的数据保存到一张临时表newtable中。2)统计所有学生选课的门数；统计教师李兰所授每门课程学生的平均成绩；统计所有选修人数多于3人的课程号和选课人数，并按人数降序排列，若人数相等则按课程号升序排列；检索“高等数学”成绩高于该课平均成绩的同学学号；检索所有成绩不及格的同学的学号，姓名和课程号。3）将“软件工程”专业选修课程号为“720”的成绩全部提高5分；创建一个新表，表名为new,用来存放成绩大于60分得学生信息，此表包括：学号，姓名，课程名和成绩。然后从利用实验1已经建立并保存过的学生表、课程表、选课表中查找相关数据信息装入此表中。4）检索所有学生的学号、姓名、课程号和成绩（注：没选某门课的学生成绩相应为NULL）。实验总结要求使用Transact-SQL写出实验步骤中要求的对数据表的各种操作的语句序列；上机调试各种操作的语句序列，查看其结果，分析实验结果，总结实验中出现的问题及解决方法，书写实验体会等。4实验3索引及视图操作一、实验目的1.加深对索引和视图概念的理解。2.熟练掌握索引的创建和删除。3.熟练掌握视图的创建、修改和删除。4.熟练运用视图进行各种检索操作。二、实验环境硬件环境：PC机软件环境：操作系统为MicrosoftWindows2000或以上版本。数据库管理系统为MicrosoftSQLServer2000标准版或企业版。三、实验内容1.熟悉运用SQLServer企业管理器和查询分析器进行索引的创建和删除。2.熟悉运用SQLServer企业管理器和查询分析器进行视图的创建、删除和修改。四、实验步骤1.利用实验1已经建立并保存过的学生表、课程表，学生表按学号建立惟一索引，课程表按课程名建立惟一索引；2.利用实验1已经建立并保存过的学生表和选课表建立一个“网络工程”专业学生成绩视图JSGV(sno,cno,grade)；利用视图JSGV查询“网络工程”专业的学生选课多于2门的学生学号；验证能否利用视图JSGV中插入一条“网络工程”专业学生选修“高等数学”课程的记录，并查询结果信息；删除视图JSGV。3.利用实验1已经建立并保存过的学生表、课程表、选课表建立视图TOTAL(sno,sname,cname,grade)；利用视图TOTAL统计各门课程的选课人数，结果显示课程号、课程名及选课人数。4.验证能否利用视图TOTAL修改选修了“高等数学”课程的学生的成绩。五、实验总结要求使用Transact-SQL写出实验步骤中要求完成的索引、视图的创建、删除、查询和统计操作。写出对验证的实验结论。5实验4存储过程和触发器一、实验目的加深和巩固对存储过程和触发器概念的理解。掌握触发器的简单应用。掌握存储过程的简单应用。二、实验环境硬件环境：PC机软件环境：操作系统为MicrosoftWindows2000或以上版本。数据库管理系统为MicrosoftSQLServer2000标准版或企业版。三、实验内容1.熟悉运用SQLServer企业管理器和查询分析器进行存储过程的创建和删除。2.熟悉运用SQLServer企业管理器和查询分析器进行触发器的创建和删除。四、实验步骤class_info,当执行该过程时，只要给出学生的姓名，就能查到他们的班级名称。使用存储过程class_info查找学生“张强”的信息。2.删除存储过程class_info3.使用触发器实现S,SC表的级联删除4.在数据库中创建一个触发器，当向S表中插入一条记录时，检查该记录的学号在S表中是否存在，如果有则不允许插入。5.创建基于学生表的插入触发器,当向学生表插入一条记录时,返回一条信息:“欢迎新同学”。6.为S表创建触发器s_insert,当向S表中插入数据时,要求学号必须以"2002"开头，否则取消插入操作。五、实验总结要求使用Transact-SQL写出实验步骤中要求完成的存储过程和触发器的创建和使用，上机调试并查看其结果，分析实验结果，总结实验中出现的问题及解决方法，书写实验体会等。6实验5T-SQL程序设计一、实验目的熟练掌握变量的定义和赋值。掌握各种运算符。掌握流程控制语句，尤其是条件语句和循环预语句。二、实验环境硬件环境：PC机软件环境：操作系统为MicrosoftWindows2000或以上版本。数据库管理系统为MicrosoftSQLServer2000标准版或企业版。三、实验内容1．变量的定义与输出。2.条件结构的使用。四、实验步骤_name的局部变量，并在select语句中使用该变量查找“张强”同学所选课程的成绩情况，给出相应的语句段和运行结果。2.将选修课程号为720的同学的成绩增加3分，同时使用全局变量@@rowcount检查获得更新的数据行数目。3.利用学生_课程数据库中的SC表查找学生的成绩信息并判断是否有某学生的某门课程的成绩在90分以上的信息，若有，则显示学号、姓名、课程名和成绩，否则输出信息，说明“不存在成绩大于90分的学生！”。从学生_课程数据库中查询所有学生选课成绩情况：姓名、课程名、成绩。要求：将学生的百分制转换为5级评分制，成绩大于等于90显示为“优秀”，成绩在80-89分显示为“良好”，“70-79”分显示为“中等”，成绩在60-69显示为“及格”，60以下显示为“不及格”，没成绩的显示为“未考”。并且输出记录按下列要求排序：先按学号升序，再按课程号升序，最后按成绩降序。利用学生_课程数据库中的SC表，编程实现：如果所有学生所有课程的平均成绩高于80分，使用while循环就将每门成绩减5分，然后查找所有学生所有课程中的最高分，如果最高分大于或等于85分，while循环重新启动并再次将每门成绩减5分并继续查找所有学生所有课程中的最高分，一直循环到最高分低于85分循环停止，然后退出。五、实验总结要求使用Transact-SQL写出实验步骤中要求完成的任务，上机调试并查看其结果，分析实验结果，总结实验中出现的问题及解决方法，书写实验体会等。7实验6数据库的完整性控制一、实验目的1.加深对数据库完整性控制作用的认识和各种完整性约束概念的理解。2.熟练掌握数据库完整性约束方法。二、实验环境硬件环境：PC机软件环境：操作系统为MicrosoftWindows2000或以上版本。数据库管理系统为MicrosoftSQLServer2000标准版或企业版。三、实验内容1.熟悉利用企业管理器为列创建DEFAULT约束、为列创建CHECK约束、为列创建UNIQUE约束、为列创建不允许为空约束、创建PRIMARYKEY约束、创建FOREIGNKEY约束。2.熟悉利用企业管理器将各种完整性约束删除。3.熟悉利用SQL为列创建DEFAULT约束、为列创建CHECK约束、为列创建UNIQUE约束、为列创建是否为空、创建PRIMARYKEY约束、创建FOREIGNKEY约束。4.熟悉利用SQL将各种完整性约束删除。四、实验步骤1．在实验1中已经对学生_课程数据库中建立了学生、课程和选课3个表，其表结构为：学生S（学号，姓名，性别，年龄，所在专业名称）课程C（课程号，课程名，任课教师名，开设的学期）选课SC（学号，课程号，成绩）利用企业管理器增加如下的完整性约束：（1）限定学生“性别”列的值只能是“男”或“女”的CHECK约束；（2）为“性别”列上创建一个默认约束，默认值为“男”；（3）为“姓名”列上创建一个惟一性约束（即UNIQUE约束）；（4）将“年龄”列设置为允许为空字段。2．利用企业管理器建立学生、课程和选课3个表的表级约束（包括主键约束和外键约束）。3.利用企业管理器删除已建立的各种完整性约束。4．利用Transact-SQL以列约束形式为现有的学生S表在“学号”属性列上创建主键。5.利用Transact-SQL为现有的学生S表中的“性别”列上创建其值只能是“男”或“女”的CHECK约束。6.利用Transact-SQL为现有的课程C表中“课程名”列上创建一个惟一性（即UNIQUE）约束。7.利用Transact-SQL创建现有学生S表与选课SC表之间的参照完整性约束。8.利用Transact-SQL删除学生S表中的“性别”列上创建的CHECK约束。9.利用Transact-SQL自由创建一新表，并在建表同时创建主键约束和域完整性约束。10.学生_课程数据库中个表之间的关联关系图。五、实验总结要求使用Transact-SQL写出实验步骤4-9中要求完成的任务，上机调试并查看其结果。分析实验步骤要求的所有实验结果，总结实验中出现的问题及解决方法，书写实验体会等。8实验7安全管理一、实验目的1.加深和巩固对数据库安全性管理的认识。2.掌握数据库安全性管理方法，并通过企业管理器实现登录帐户管理、服务器角色管理、数据库角色管理、用户帐户管理等数据的安全性控制功能。3.掌握备份数据库、恢复数据库操作的方法。二、实验环境硬件环境：PC机软件环境：操作系统为MicrosoftWindows2000或以上版本。数据库管理系统为MicrosoftSQLServer2000标准版或企业版。三、实验内容1.熟悉SQLServer企业管理器的数据库安全性管理方法，创建及管理登录帐号，设置服务器角色，添加数据库的用户，设置数据库角色等。2.熟悉SQLServer企业管理器的数据备份/恢复功能。四、实验步骤1.熟悉SQLServer企业管理器的数据库安全性管理方法；熟悉各种服务器角色、数据库角色的含义，熟悉创建及管理登录帐号，设置服务器角色的步骤，熟悉添加数据库的用户，设置数据库角色的步骤等。2.分别创建三个登录帐号：readUser、writeUser和test；只给test授予DatabaseCreators的服务器角色；给readUser对于