数据库原理及应用实验指导书(wxq).doc

数据库原理及应用实验指导书数据库技术是当前计算机领域中应用最广泛、发展最迅速的技术，一门理论和实际紧密相连的技术，上机实验是教学中的必要环节。主要目标是：（1） 通过上机操作，加深对数据库系统理论知识的理解；（2） 通过使用具体的DBMS，了解一种实际的数据库管理系统并掌握其操作技术；（3） 通过实际题目的上机实验，提高动手能力，提高分析问题和解决问题的能力。主要实验环境：操作系统Microsoft Windows 2000/XP；数据库管理系统Microsoft SQL Server 2000个人/专业中文版。实验一 熟悉SQL Server 2000的开发环境（2学时） 1实验目的（1）了解SQL Server系统安装；（2）了解SQL Server系统构成；（3）了解系统各模块的作用。2 实验内容（1）SERVER 2000 中文版软件安装；（2）SERVER 2000系统构成。3 实验步骤（1）SERVER 2000 中文版软件安装（以教师演示为主）；（2）系统构成：SQL server服务；企业管理器；查询分析器。实验二 数据库设计（2学时）1实验目的 （1） 深入理解关系模型的性质，掌握将现实世界的事物转化成E-R图的基本技巧以及将E-R图转化成关系表的方法；（2） 熟练掌握和使用SQL、Transact-SQL、SQL Server企业管理器创建数据库、表、索引和修改表结构；（3） 学会使用SQL Server查询分析器接受Transact-SQL语句和进行结果分析。2实验内容(1) 使用E-R图表示对系统的数据抽象情况，使用数据字典对E-R图的实体和联系进行详细说明；(2) 描述系统需要的基本表及属性、视图和索引，对基本表的主码、候选码、外码等进行说明，对基本表中数据的约束条件进行说明；(3) 利用SQL Server企业管理器创建数据库并查看数据库属性；(4) 定义基本表及结构，定义表的主码和基本约束条件，并为主码建立索引；(5) 查看基本表结构，并修改表结构。3实验步骤（1） 依据实验要求创建相应的概念模型、即E-R图的设计，提取出图书读者数据库中的实体、根据调查分析分别确定各个实体的属性、并确定实体之间的关系；（2） 根据将创建完成的图书读者数据库的E-R图转化成关系表；（3） 使用SQL Server企业管理器按教材第6章表6-1创建数据库；执行“开始”|“程序”|“Microsoft SQL Server”|“企业管理器”命令，即可启动企业管理器。在企业管理器窗口中，依次展开“Microsoft SQL Server”，“SQL Server组”，在SQL Server组下的列表中选择将被使用的服务器并展开，选中“数据库”并右击，在弹出的快捷菜单中执行“新建数据库”命令，此时将打开“数据库属性”对话框，在“常规”选项卡的“名称”文本框中输入要创建数据库的名称，在其它的选项卡中设置数据库的其它属性，点击“确定”按钮创建数据库。企业管理器界面如图2-1所示：图2-1 企业管理器（4） 使用企业管理器创建表。在企业管理器中展开“数据库”，选择要在其中创建表的数据库“图书读者”并展开，在列表中选择“表”，然后右击，在弹出的快捷菜单中执行“新建表”命令。此时打开表设计窗口，如图2-2所示：在此设置每个字段的基本属性，包括字段名（列名）、数据类型、长度和允许空值等。设置好后单击工具栏上的“保存”按钮完成表的设计，最后关闭表设计器。图2-2表设计器图书读者数据库的表结构：图书（书号，类别，出版社，作者，书名，定价）；读者（编号，姓名，单位，性别，电话）；借阅（书号，读者编号，借阅日期）。学生课程数据库的表结构：学生（学号，姓名，年龄，性别，所在系）；课程（课程号，课程名，先行课）；选课（学号，课程号，成绩）。实验三 数据库操纵技术（2学时）1 实验目的 （1） 要求学生熟练掌握使用SQL、Transact-SQL和SQL企业管理器向数据库输入数据、修改数据和删除数据的操作；（2） 掌握SQL Server查询分析器的使用方法，加深对SQL和Transact-SQL语言查询语句的理解；（3） 熟练掌握简单表的数据查询、数据排序，数据连接查询及嵌套查询的操作方法；（4） 掌握SQL Server中的视图创建向导和图表创建向导的使用方法，加深对视图和SQL Server图表作用的理解。2 实验内容（1） 数据库基本表数据的输入、修改和删除操作；（2） 数据库的简单查询操作。包括投影、选择条件表达、数据排序和使用临时表等；（3） 数据库的连接查询操作。包括等值连接、自然连接、求笛卡尔积、一般连接、外连接、内连接等；（4） 在SQL Server查询分析器中，使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作；（5） 创建、查看、修改和删除视图；（6） 创建、编辑和删除数据库图表。3 实验步骤（1） 使用SQL命令创建数据库执行“开始”|“程序”|“Microsoft SQL Server”|“查询分析器”命令，启动查询分析器。出现图3-1所示的“连接到SQL Server”对话框。图3-1 连接到SQL Server服务器如果使用windows身份验证，可选中“Windows身份验证”单选项，然后单击“确定”按钮，即可使用windows当前用户账户连接到SQL Server服务器。如果选中“SQL Server身份验证”单选项，则需要输入登录名和密码。然后单击“确定”按钮，进入查询分析器主界面，如图3-2所示：图3-2 SQL查询分析器在查询分析器编辑窗口中输入如下语句：CREATE DATABASE bookON (NAME=book_data, FILENAME=D:sql_databook_data.mdf, SIZE=10, FILEGROWTH=10%)LOG ON(NAME=book_log,FILENAME=D:sql_databook_log.ldf,SIZE=5,FILEGROWTH=10%)注意文件的存储路径，必须是磁盘上已存在的目录。点击工具栏中的运行按钮，完成数据库的创建。（2） 使用SQL命令创建表 要使用SQL语句创建表customer，可在查询分析器中输入如下语句：CREATE TABLE customer(customerid INT PRIMARY KEY,customername VARCHAR(20) NOT NULL,loginname VARCHAR(20) NOT NULL,password VARCHAR(20) NOT NULL,logindate DATETIME,address VARCHAR(40),tel VARCHAR(40),note VARCHAR(100)单击工具栏中的运行按钮，完成表customer的创建。依此方法创建其它表，注意创建表前应选择好正确的数据库。（3） 使用企业管理器添加、修改和删除表数据在企业管理器窗口中，依次展开“Microsoft SQL Server”、“SQL Server组”、“服务器”和“数据库”，选中数据库“图书读者”并展开。在数据库的列表中选择“表”并展开，则在内容窗口中显示数据库“图书读者”中的表，在要执行数据操作的表上单击右键，在弹出的快捷菜单中执行“打开表”|“返回所有行”命令或“返回首行命令，如图3-3所示：图3-3 打开数据表 打开表后，即可对表中的数据进行录入、修改、删除操作，如图3-4所示：图3-4 打开的数据表要添加数据，在表格中，将插入点定位到最后一行，即可添加。要修改数据，只需将插入点定位到需要修改行的字段中。要删除数据，首先单击该行最左端的行选择按钮选中该行，然后按delete键，或右击该行，在弹出的快捷菜单中选择“删除”命令。（4） 使用SQL语句添加、修改和删除表数据打开查询分析器，输入如下SQL语句向表中添加数据：INSERT INTO customerVALUES(2,李四,lishi,123456,getdate(),四川成都市人民南路21号,02884672345,NULL)点击工具栏中的运行按钮，执行刚才输入的insert命令。输入如下SQL语句修改表中数据：UPDATE customer SET password=iamaboy WHERE customerid=2输入如下SQL语句删除表中的数据：DELETE FROM customer WHERE customerid=1在每执行完一条命令后，可使用select命令“select * from customer”查询数据。如果只需要执行某一条命令，可选中命令文本，然后点击按钮，如图3-5所示：图3-5 在查询分析器中输入SQL命令（5） 使用企业管理器修改、删除数据库和表打开企业管理器，如果要修改数据库，右击需要修改的数据库，从弹出的快捷菜单中选择“属性”，可对数据库的一些属性进行修改。如果要删除数据库，在弹出的快捷菜单中选择“删除”。如果要修改表，右击要修改的表，从弹出的快捷菜单中选择“设计表”命令。如要删除表，在弹出的快捷菜单中选择“删除”。（6） 简单查询操作 以pubs数据库为例，使用企业管理器完成以下的查询语句：1) 查询authors表中的所有作家的信息以及仅查询作家姓名的查询； 参考命令： select au_lname from authors 2) 查询出Publishers表中的前10个或前面10的出版社信息。 参考命令： select top 10 * from publishers3) 查询Publishers表中出版社所在的不同的国家。参考命令： select distinct country from publishers4) 将titles表中价格按90价格显示，并且将价格列名改为“调价后价格”。 参考命令： select title_id,title,type,pub_id,price*0.9 as 调价后价格 from titles5) 查询authors表中的作家信息，并以姓名、电话、住址、城市作为列名。 参考命令： select au_lname,au_fname,phone,address,city from authors6) 查询sales表，数量在10到30之间的销售信息。参考命令：select * from sales where qty between 10 and 307) 从titles表中找出价格大于15的图书的信息，并按照升序和降序显示。 参考命令：select * from titles where price=15 order by price asc (升序)select * from titles where price=15 order by price desc (降序)8) 找出以“Publishing”字符串结尾的出版社。参考命令：select * from publishers where pub_name like %Publishing9) 从titles表中查询每类(type字段)图书的数量。参考命令：select type as 类型 ,count(*) as 数量 from titles group by type10) 查询图书数量大于3的图书类型名。参考命令：select type as 类型 ,count(*) as 数量 from titles group by type having count(*)311) 列出一次定购量大于图书当年销量5%的书店标识及图书标识。 参考命令：select stor_id,title_id from sales x where qty=(select sum(qty) from sales where year(ord_date)=year(x.ord_date)*0.05（7） 复杂查询操作1) 查询每个学生选修课程的名称和成绩，显示的列包括学号、课程名、成绩。参考命令：Select 学号,课程名,成绩 from 选课,课程 Where 课程.课号=选课.课号或Select 学号,课程名,成绩 from 选课 inner join 课程 on 课程.课号=选课.课号2) 求选修C+课程的所有学生的成绩。参考命令：Select 学号,成绩 from 选课,课程 Where 选课.课号=课程.课号 and 课程名=C+3) 求每门课程的选课人数，只显示被选课程。参考命令：Select 课程.课号,课程名,count(学号) as 人数 from 课程,选课Where 课程.课号=选课.课号 Group by 课程.课号,课程名4) 查询每个学生的学号、姓名、选修的课程名及成绩。参考命令：Select 学生.学号,姓名,课程名,成绩 from 学生,选课,课程 Where 学生.学号=选课.学号 and 选课.课号=课程.课号或Select 学生.学号,姓名,课程名,成绩 from 学生 inner join 选课 on 学生.学号=选课.学号 inner join 课程 on 选课.课号=课程.课号5) 查询每个同学的学号，姓名，总学分。（成绩及格的才能拿到学分）参考命令：Select 学生.学号,姓名,sum(学分) as 总学分 from 学生,选课,课程 where 学生.学号=选课.学号 and 选课.课号=课程.课号 and 成绩=60 group by 学生.学号,姓名6) 查询每个学生所选课程的课号和成绩，需要显示未选课的学生。参考命令：select 学生.学号,姓名,课号,成绩 from 学生,选课 where 学生.学号*=选课.学号或select 学生.学号,姓名,课号,成绩 from 学生 left outer join 选课 on 学生.学号=选课.学号7) 求每门课程的选课人数。参考命令：Select 课程.课号,课程名,count(学号) as 人数from 课程,选课 Where 课程.课号*=选课.课号 Group by 课程.课号,课程名8) 查询选修“数据结构”的学生的学号和姓名。参考命令：Select 学号,姓名 from 学生 where 学号 in (select 学号 from 选课 where 课号 in (select 课号 from 课程 where 课程名=数据结构)9) 找出每个学生超过他所有的选修课程的平均成绩的课程号。参考命令：select 学号,课号 from 选课 xwhere 成绩=(select avg(成绩) from 选课 where 学号=x.学号) 10) 根据学生表创建一个新学生表，该表包括学号、姓名两列。参考命令：select 学号，姓名 into 新学生表 from 学生 11)基