信息系统与数据库技术全书实验参考答案.doc

1、10.1 实验一 SQL Server 数据库基本操作一实验目的本实验主要了解 Microsoft SQL Server 2000 中各组件的主要功能和基本使用方法；了解 SQL Server 数据库的逻辑结构和物理结构；了解数据表的结构特点以及索引的基本概念。通过本实验，读者将学会在“企业管理器”中创建和修改数据库和数据表结构、对数据表进行数据的添加、删除和更新操作以及在数据表上创建字段索引的操作。二实验环境 Microsoft SQL Server 2000 企业管理器三实验内容1创建一个名称为 Book 的数据库，支持教材订购信息管理，要求：（1）将主数据库文件 Book_Data.MD

2、F 放置在的 D:DBF 文件夹中，文件大小为5MB，最大值为 50MB，文件增量为 2MB。（2）将事务日志文件 Book_Log.LDF 放置在的 D:DBF 文件夹中，文件大小为 5MB，最大值为 100MB，文件增量为 1MB。实验参考步骤：1）在 D:下创建 DBF 文件夹。2）在企业管理器窗口右单击“数据库”或任一数据库名，从快捷菜单中选择“新建数据库”命令，出现“数据库属性”对话框。3) 在“数据库属性”对话框中选择 “常规”选项卡，在名称文本框中输入：Book；4) 在“数据库属性”对话框中选择“数据文件”选项卡，设置数据文件的位置、大小、最大值和文件增量等属性；5) 在“数据

3、库属性”对话框中选择“事务日志”选项卡，设置事务日志文件的位置、大小、最大值和文件增量等属性。2在上题所建数据库 Book 中建立 5 张数据表结构并分别向数据表插入记录。实验参考步骤：在企业管理器中展开 Book 数据库。右击数据库文件夹下的“表”项，从弹出的快捷菜单中选择“新建表”命令，弹出“表设计”界面，分别建立张表的结构，并且为各个表建立主关键字。3创建 T_Teacher、T_Academy 、T_BookInfo、T_Publisher 和 T_BookOrder个表之间的关系。实验参考步骤：1) 在企业管理器中展开 Book 数据库。右击数据库文件夹下的“关系图”项，从弹出的快捷

4、菜单中选择“新建数据库关系图”命令。2) 通过“创建数据库关系图向导 ”新建关系图。4任选两个数据表插入 3-5 条记录（例如 T_BookInfo、 T_BookOrder、T_Teacher） ，各数据表记录如表 9-xb）所示。实验参考步骤：在企业管理器中展开 Book 数据库中的表文件夹，右单击需插入记录的数据表，从弹出的快捷菜单中选择“打开表返回所有行”命令。在数据表显示界面中依次输入记录。将 T-teacher 表“TeacherName”字段的类型改为 Varchar(16)。为 T_BookOrder 表添加约束条件，使得“StuBookNum”和“TeaBookNum”的取值

5、都大于 0；“OrderTime”大于 2007年。实验参考步骤：1) 在企业管理器中展开 Book 数据库中的表文件夹，右击 T-teacher 表，选择“表设计” ，进入 T-teacher表结构设计界面，修改“TeacherName”字段的类型。2) 在企业管理器中展开 Book 数据库表文件夹，右击 T_BookOrder 表，选择“表设计” ，进入T_BookOrder 表结构设计界面，右单击 T_BookOrder 表结构设计界面，从弹出的快捷菜单中选择“CHECK 约束”命令，在“属性 ”对话框的“CHECK 约束”选项卡中点击 “新建”按钮分别设置约束条件。6练习记录数据的添加

6、、删除和更新操作。在 T_Teacher 表中添加记录“01010117、黄蓉、13、女、1977-9-21、副教授、63478445” 。将 T_Teacher 表中 TeacherCode 为“01010113”的记录删除；将 TeacherCode 为“01010116”记录的 title 字段内容更新为“教授” 。实验参考步骤：在企业管理器中展开 Book 数据库表文件夹，右击 T-teacher 表，选择“打开表返回所有行” ，进入 T-teacher 表数据行显示界面，进行数据的添加、删除和更新操作。7在企业管理器中练习索引的创建和删除。在“TeacherName” （降序）和“A

7、cadCode”（升序）上建立一个名称为“Name_Code”的普通索引。删除“Name_Code ”索引。实验参考步骤：方法：在企业管理器中展开 Book 数据库表文件夹，右击 T_Teacher 表，选择“表设计” ，进入 T_Teacher 表结构设计界面，右单击 T_Teacher 表结构设计界面，从弹出的快捷菜单中选择“索引键”命令，在“属性”对话框的“索引键”选项卡中点击“新建”按钮进行“Name_Code”索引的建立。方法：在企业管理器中展开 Book 数据库表文件夹，右击 T-teacher 表，选择“所有任务管理索引” ，打开“管理索引”窗口，进行索引的建立和编辑。10.2

8、实验二 T-SQL 语言基本操作一实验目的本实验主要了解 T-SQL 语句以及在查询分析器中执行 T-SQL 语句的方法。通过本实验，读者将了解使用 T-SQL 语句创建数据库和数据表；掌握数据库查询语句 Select 语句的基本语法及使用方法；掌握对数据表进行数据插入、修改和删除的 T-SQL 语句；了解 T-SQL 语言的各种运算符、控制语句的功能并学会简单的程序编写。二实验环境 Microsoft SQL Server 2000 查询分析器和 Microsoft SQL Server 2000 企业管理器 Book 数据库三实验内容1利用企业管理器中提供的“生成 SQL 脚本”命令编写

9、Book 数据库中数据表对象“T_Teacher”、 “T_BookOrder”、 “T_BookInfo”、 “T_Publisher”、 “T_Academy”创建的脚本文件 D:Table.sql。实验参考步骤：1) 在企业管理器右单击 Book 数据库，选择“所有任务生成 SQL 脚本”菜单，打开“生成 SQL 脚本”窗口，点击“全部显示”按钮。2) 按要求添加写入脚本的对象，点击“确定”按钮，打开“另存为”对话框，设置脚本文件名称。2在查询分析器中使用 T-SQL 语句完成实验一中的第 1 题。CREATE DATABASE BookON(NAME=Book_Data,FILENAM

10、E=D:DBFBOOK_Data.MDF,SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=2MB)LOG ON(NAME=Book_log,FILENAME=D:DBFBook_Log.LDF,SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=1MB)3编写 SELECT 语句，建立以下查询1）显示 T_BookInfo 表中所有字段的值。SELECT *FROM T_BookInfo2）查询 T_Teacher 表中所有记录的 TeacherCode、TeacherName、EnterDate 和 Title 字段信息。USE BookSELECT Tea

11、cherCode,TeacherName,EnterDate,TitleFROM T_Teacher3）查询男教师的姓名(TeacherName)，显示时将字段名 TeacherName 改为“姓名” 。SELECT TeacherName AS 姓名 FROM T_TeacherWHERE Sex=男 4）查询 2007 年以后出版的教材名称(BookName)、出版日期 (PublishDate)和库存量(StockNum)，显示时字段标题分别为：教材名称、出版日期和库存量。SELECT BookName AS 教材名称,PublishDate AS 出版日期,StockNum AS 库存

12、量FROM T_BookInfoWHERE year(PublishDate)=2007或SELECT BookName AS 教材名称,PublishDate AS 出版日期,StockNum AS 库存量FROM T_BookInfoWHERE PublishDate=2007-01-015）查询所有教材的出版社代码(PublisherCode) ，将重复的记录去掉。SELECT DISTINCT PublisherCode FROM T_BookInfo6）查询学生订书量（StuBookNum）最大的 3 个记录，显示教材代号(BookCode)和学生订书量（StuBookNum ） 。

13、SELECT top 3 BookCode,StuBookNumFROM T_BookOrderORDER BY StuBookNum DESC7）查询学生订书量（StuBookNum）在 150 到 250 之间的教材信息。SELECT BookCode,TeacherCode,StuBookNum,OrderTimeFROM T_BookOrderWHERE ( StuBookNum BETWEEN 150 AND 250)8）查询每一种教材的学生订书总量。SELECT BookCode AS 教材代号 ,SUM(StuBookNum) AS 学生订书数量FROM T_BookOrderG

14、ROUP BY BookCode9）查询书名为“VB 程序设计”的教材的学生总订量（StuBookNum）和教师总订量（TeaBookNum）和总订量。SELECT SUM(StuBookNum) AS 学生总订量,SUM(TeaBookNum) AS 教师总订量,(SUM(StuBookNum)+SUM(TeaBookNum) AS 总订量FROM T_BookInfo ,T_BookOrder WHERE T_BookInfo.BookCode=T_BookOrder.BookCode AND BookName=VB 程序设计或SELECT SUM(StuBookNum) AS 学生总订量

15、,SUM(TeaBookNum) AS 教师总订量,(SUM(StuBookNum)+SUM(TeaBookNum) AS 总订量FROM T_BookInfo INNER JOIN T_BookOrder ON T_BookInfo.BookCode=T_BookOrder.BookCode WHERE BookName=VB 程序设计10）显示教材的最高价、最低价、平均价、最高价与最低价之间的差值。SELECT str(Max(Price),5,2) AS 最高价,str(Min(Price),5,2)AS 最低价,str(AVG(Price),5,2) AS 平均价, str(Max(P

16、rice)-Min(Price),5,2) AS 差值FROM T_BookInfo11）查询出版社代号（PublisherCode）为 10、02、31 的出版社所出版的教材信息。要求显示教材名称（BookName ） 、作者（Author） 、价格（Price） 、库存（StockNum ）和出版社名称（Publisher） 。SELECT T_BookInfo.BookName,T_BookInfo.Author,T_BookInfo.Price,T_BookInfo.StockNum,T_Publisher.PublisherCode,T_Publisher.PublisherFROM

17、 T_Publisher,T_BookInfoWHERE T_Publisher.PublisherCode=T_BookInfo.PublisherCode AND 图 7T_Publisher.PublisherCode IN (10,02,31)或：SELECT T_BookInfo.BookName,T_BookInfo.Author,T_BookInfo.Price,T_BookInfo.StockNum,T_Publisher.PublisherCode,T_Publisher.PublisherFROM T_Publisher INNER JOIN T_BookInfoON T_

18、Publisher.PublisherCode=T_BookInfo.PublisherCode WHERE T_Publisher.PublisherCode IN (10,02,31)12）查询没有领书的教师信息。要求显示 TeacherCode（教师工号） 、TeacherName（姓名） 、AcadName（学院名称）和 Telephone（电话号码） ，并按学院名称降序排列。SELECT T_Teacher.TeacherCode,TeacherName,AcadName,TelephoneFROM T_BookOrder ,T_Teacher ,T_AcademyWHERE T_T

19、eacher.TeacherCode= T_BookOrder.TeacherCode AND T_Teacher.AcadCode= T_Academy.AcadCode AND TakeAway=0ORDER BY AcadName DESC或：SELECT T_Teacher.TeacherCode,TeacherName,AcadName,TelephoneFROM T_Academy INNER JOIN (T_Teacher INNER JOIN T_BookOrderON T_Teacher.TeacherCode=T_BookOrder.TeacherCode)ON T_Aca

20、demy.AcadCode=T_Teacher.AcadCode WHERE TakeAway=0ORDER BY AcadName DESC13）查询人文学院教师所订教材的信息。要求显示教材代码（BookCode） 、数量（StuBookNum+TeaBookNum）和订教材日期（OrderTime） 。SELECT BookCode,StuBookNum+TeaBookNum AS 数量,OrderTimeFROM T_BookOrderWHERE TeacherCode IN (SELECT TeacherCodeFROM T_TeacherWHERE AcadCode=(SELECT

21、AcadCodeFROM T_AcademyWHERE AcadName=人文学院)或：SELECT BookCode,StuBookNum+TeaBookNum AS 数量,OrderTimeFROM T_BookOrderWHERE TeacherCode IN(SELECT TeacherCodeFROM T_Teacher INNER JOIN T_AcademyON T_Teacher.AcadCode=T_Academy.AcadCodeWHERE AcadName=人文学院)14）查询姓名字符数大于等于 4 个字符的作者情况，要求显示作者姓名（Author） 、教材名称（Book

22、Name ） 。SELECT Author,BookNameFrom T_BookInfo WHERE LEN(Author)=415）查询教师工号（TeacherCode）为“01010109”的教师是否已预订教材，若已预订则显示所订教材的数量；否则显示：“该教师没有预订教材” 。DECLARE cn smallint,text varchar(100)SELECT cn=(SELECT StuBookNum+TeaBookNumFROM T_BookOrderWHERE TeacherCode=01010109)IF cn0SET text=该教师预订教材数量+str(cn)ELSE SE

23、T text=该教师没有预订教材SELECT text4使用联接关键字(JOINN) 建立查询1）查询所有教师姓名（TeacherName）及所属学院名称（AcadName） 。 （提示：本题使用INNER JOIN 完成查询）SELECT TeacherName, AcadNameFROM T_Teacher INNER JOIN T_Academy ON T_Teacher.AcadCode= T_Academy.AcadCodeORDER BY T_Teacher.AcadCode2）查询所有学院名称（AcadName）及每个学院的教师姓名（TeacherName） 。 （提示：本题使用

24、 LEFT OUTER JOIN 完成查询）SELECT AcadName,TeacherNameFROM T_Academy LEFT OUTER JOIN T_Teacher ON T_Academy.AcadCode=T_Teacher.AcadCode3）查询所有教师姓名（TeacherName）及所订教材代号（BookCode ） 。 （提示：本题使用RIGHT OUTER JOIN 完成查询）SELECT TeacherName,BookCodeFROM T_BookOrder RIGHT OUTER JOIN T_TeacherON T_BookOrder.TeacherCode

25、=T_Teacher.TeacherCode4）查询所有已订教材的教师姓名（TeacherName） 、教材代号（BookCode ）及教材名称（BookName） 。 （提示：本题使用嵌套的 INNER JOIN 完成查询）SELECT TeacherName,T_BookOrder.BookCode,BookNameFrom T_Teacher INNER JOIN (T_BookOrder INNER JOIN T_BookInfo ON T_BookOrder.BookCode=T_BookInfo.BookCode)ON T_Teacher.TeacherCode=T_BookOrd

26、er.TeacherCode5使用 SELECT 语句复制数据表1）将 T_BookOrder 表中的教师编号（ TeacherCode） 、教材代号（BookCode） 、学生用书量（StuBookNum ） 、教师用书量（ TeaBookNum）字段及对应的书名（BookName）复制到新表 NewTable1 中。SELECT TeacherCode,T_BookOrder.BookCode,BookName,StuBookNum,TeaBookNumINTO NewTable1FROM T_BookInfo ,T_BookOrder WHERE T_BookInfo.BookCode=

27、T_BookOrder.BookCode或：SELECT BookName,T_BookOrder.BookCode,StuBookNum,TeaBookNumINTO NewTable1FROM T_BookOrder INNER JOIN T_BookInfoON T_BookOrder.BookCode=T_BookInfo.BookCode2）将 T_Teacher 表中的教师姓名（TeacherName） 、性别（Sex）字段及每个教师所属的学院名称（AcadName）复制到新表 NewTable2 中。SELECT TeacherName, Sex, AcadNameINTO Ne

28、wTable2FROM T_Teacher ,T_Academy WHERE T_Teacher.AcadCode= T_Academy .AcadCode或：SELECT TeacherName, Sex, AcadNameINTO NewTable2FROM T_Teacher INNER JOIN T_Academy ON T_Teacher.AcadCode= T_Academy.AcadCode6使用 DELECT 语句练习删除操作1）删除 NewTable1 表中学生用书量（StuBookNum ）和教师用书量（TeaBookNum）之和低于 60 的记录。DELETE FROM

29、NewTable1WHERE StuBookNum+TeaBookNum602）删除 NewTable2 表中姓“王”和姓“杨”的记录。DELETE FROM NewTable2WHERE LEFT(TeacherName,1)=王OR LEFT(TeacherName,1)=杨或：DELETE FROM NewTable2WHERE TeacherName LIKE 王% OR TeacherName LIKE 杨%3）删除 NewTable1 中“体育部”教师的订书记录。DELETE FROM NewTable1WHERE TeacherCode IN (SELECT T_Teacher.

30、TeacherCodeFrom T_Teacher INNER JOIN T_AcademyON T_Teacher.AcadCode=T_Academy.AcadCodeWhere AcadName=体育部 )7使用 INSERT 语句练习插入操作1）在 NewTable2 表中添加一条记录，姓名：吴清、性别：女、学院名称：计算机科学与技术学院。INSERT INTO NewTable2(TeacherName,Sex,AcadName)VALUES(吴清,女,计算机科学与技术学院)2）将 T_Teacher 表中姓“王”和姓“杨”的记录添加到 NewTable2 表中。INSERT INT

31、O NewTable2SELECT TeacherName,Sex,AcadNameFROM T_Teacher INNER JOIN T_Academy ON T_Teacher.AcadCode=T_Academy.AcadCodeWHERE LEFT(TeacherName,1)=王 OR LEFT(TeacherName,1)=杨8使用 UPDATE 语句修改数据1）将 2000 年以前（不包括 2000 年）入校的、职称为助教的教师职称（TitleCode）往上提一级。UPDATE T_TeacherSET Title=讲师WHERE Title=助教 AND YEAR(EnterD

32、ate)20002）修改订书数量。要求：所有学院名称中包含汉字“工”的学院所属的教师所订的学生定书量减少 3 本、教师定书量增加 3 本。UPDATE T_BookOrderSET StuBookNum=StuBookNum-3,TeaBookNum=TeaBookNum+3WHERE TeacherCode IN(SELECT T_BookOrder.TeacherCodeFROM T_BookOrder,T_Academy,T_Teacher WHERE T_BookOrder.TeacherCode= T_Teacher.TeacherCode AND T_Teacher.AcadCod

33、e= T_Academy.AcadCode AND AcadName LIKE %工% )3）将订书量超过 300 本的教材库存量增加 500 本。UPDATE T_BookInfoSET StockNum=500WHERE BookCode IN (SELECT BookCode FROM T_BookOrder )10.3 实验三 SQL Server 的视图、存储过程和触发器一实验目的本实验主要了解 SQL Server 视图、存储过程和触发器的基本概念和使用方法。通过本实验，读者将学会在企业管理器中创建、修改、执行和删除存储过程的操作以及在查询分析器中执行的 T-SQL 语句；掌握触发

34、器的创建、修改和删除的操作方法和 T-SQL 语句。掌握视图的创建、修改和删除的操作方法和 T-SQL 语句。二实验环境 Microsoft SQL Server 2000 企业管理器和 Microsoft SQL Server 2000 查询分析器 Book 数据库三实验内容1创建存储过程 proc_book1，查询指定学院的教师预订教材的情况。要求显示教材名称（T_BookInfo.BookName） 、教师姓名（T_Teacher.TeacherName）和教材数量（T_BookOrder.StuBookNum+T_BookOrder.TeaBookNum） 。(1)查询条件为：学院代码

35、CREATE PROC proc_book1acode char(6)ASSELECT BookName, TeacherName,StuBookNum+TeaBookNum AS 数量FROM T_Teacher INNER JOIN (T_BookOrder INNER JOIN T_BookInfoON T_BookOrder.BookCode= T_BookInfo.BookCode)ON T_Teacher.TeacherCode=T_BookOrder.TeacherCodeWHERE T_Teacher.AcadCode=acode-执行存储过程 proc_book1，查询学院代

36、号为02的教师预订教材的信息。EXEC proc_book1 02或：EXEC proc_book1 scode=02（2）查询条件为：学院名称CREATE PROC proc_book1_2aname varchar(50)ASSELECT BookName, TeacherName,StuBookNum+TeaBookNum AS 数量FROM T_Academy,T_Teacher,T_BookOrder,T_BookInfoWHERE T_Academy.AcadCode=T_Teacher.AcadCodeAND T_Teacher.TeacherCode=T_BookOrder.T

37、eacherCodeAND T_BookInfo.BookCode=T_BookOrder.BookCodeAND T_Academy.AcadName=aname-执行存储过程 proc_book1_2，查询学院名称为人文学院的教师预订教材的信息。EXEC proc_book1_2 人文学院 或EXEC proc_book1_2 scode=人文学院2创建存储过程 proc_book2，查询指定教材的预订数量。统计条件为：教材代码CREATE PROCEDURE proc_book2tcode char(20)ASSELECT SUM(StuBookNum)+SUM(TeaBookNum)F

38、ROM T_BookOrderGROUP BY BookCodeHAVING BookCode=tcode-执行存储过程 proc_book2，统计教材代号为010004的预订信息。exec proc_book2 1000013创建添加教材信息的存储过程 proc_book3。CREATE PROCEDURE proc_book3(bookcode char(6),bookname varchar(40),publishercode varchar(2),author varchar(40),publisherdate datetime,price money,isbncode char(20

39、),stocknum smallint, booksort char(20)ASBEGIN INSERT INTO T_BookInfoVALUES(bookcode,bookname,publishercode,author,publisherdate,price, isbncode,stocknum,booksort)END-执行存储过程 proc_book3EXEC proc_book3 500001,信息系统管理技术,31,谭浩强,2007-05-11,30,760211008,30,计算机4创建存储过程 proc_book4，查询指定教材名称和出版社名称的教材所对应的教材代号和库存。

40、CREATE PROCEDURE proc_book4bookname varchar(40),publisher varchar(40),bookcode char(6) OUTPUT,stocknum smallint OUTPUTASSELECT bookcode=BookCode,stocknum=StockNumFROM T_BookInfo INNER JOIN T_Publisher ON T_Publisher.PublisherCode=T_BookInfo.PublisherCodeWHERE T_Publisher.Publisher=publisher AND BookName=bookname-执行存储过程 proc_book4DECLARE bookcode char(6),stocknum smallint EXEC proc_book4 C 语言程序设计 ,电子工业出版社,bookcode OUTPUT,stocknum OUTPUTPRINT 该教材的代号+CAST(bookcode AS char(6)PRINT 该教材的库存+STR(stocknum)5创建触发器 tri_book1，在对 T_Teacher 表进行插入、修改和删除记录时，都会自动显示表中的内容。CREATE TRIGGER tri_book1