数据库原理实验教程全套电子整本书电子教案教学教程整套课件.ppt

数据库原理实验教程实验目录实验1 安装SQL Server 2005实验2 数据库设计实验3 创建数据库和表实验4 数据正确性约束实验5 数据管理实验6 SQL语句实验7 数据库安全控制实验目录实验8 T-SQL基本语句实验9 存储过程实验10 触发器实验11 数据库备份及还原实验12 代理、作业实验13 事务处理实验1安装SQLServer2005【实验目的】了解SQL Server 2005不同版本，及各版本的特点。掌握SQL Server 2005的安装过程，搭建实验环境。【实验要求】安装SQL Server 2005开发版。【实验内容】SQL Server 2005 的不同版本 SQLServer2005EnterpriseEdition（32位和64位）SQLServer2005EvaluationEdition（32位和64位）SQLServer2005StandardEdition（32位和64位）SQLServer2005WorkgroupEdition（仅适用32位）SQLServer2005DeveloperEdition（32位和64位）SQLServer2005ExpressEdition（仅适用32位）SQLServer2005CompactEdition（仅32位）SQLServer2005RuntimeEdition（32位和64位）【实验内容】SQL Server 2005 开发板的安装过程 安装环境：windows xp。在不同的操作系统中，安装过程不尽相同。要安装的组件：可以通过“高级”界面，确定详细的安装内容。身份验证模式：考虑到后面安全性实验，应选择“混合模式”。实验2数据库设计【实验目的】掌握数据库结构设计的基本理论。设计数据库的概念模型和逻辑模型。熟悉E-R图设计的基本方法和步骤。实验2数据库设计【实验要求】数据库设计的基本步骤是：需求分析；概念结构设计；逻辑结构设计；物理结构设计；数据库实施：数据库运行和维护。实验2数据库设计【实验要求】某书城是一个图书批发销售部门。其主要业务是从各出版社及国外引进图书，以批发的方式销售给书店。图书具有多种类别，存放在仓库中，由仓库保管员管理。单位业务员分为采购和销售两个部门，采购人员负责从出版社引进图书，销售人员将图书销售给书店。销售的图书由物流公司负责送货。请设计该公司图书管理系统的概念结构和逻辑结构。【实验内容】实体 出版社：出版社编号，出版社名称，地址，联系人，电话图书分类：分类号，类别名称仓库：仓库编号，仓库名称物流公司：物流公司编号，物流公司名称，联系人，电话作者：作者编号，姓名部门：部门编号，部门名称图书：图书编号，图书名称，价格，库存量，出版社编号，作者编号，分类号，仓库编号书店：书店编号，书店名称，地址，电话员工：员工编号，姓名，部门编号，基本工资订单：订单编号，物流公司编号，订货时间，送货时间，书店编号，员工编号，金额【实验内容】联系 一对多：图书分类与图书作者与图书仓库与图书出版社与图书部门与员工员工与订单书店与订单物流公司与订单多对多：订单明细：订单编号，图书编号，数量进货记录：图书编号，员工编号，出版社编号，数量【实验内容】E-R图 图书分类图书属于1n作者著作1n仓库存放n1员工部门属于n1出版社出版1n购书mmn订单销售n1书店明细购买mnn1物流公司送货n1【实验内容】设计逻辑结构 概念结构（E-R图）向逻辑结构转换的基本原则是：一个实体对应一个关系模式；一对多的联系不需要转换为关系模式，通过“多”方使用外键来描述；多对多的联系要转换为一个关系模式。由此，该管理系统包含10个实体，两个多对多联系，需要建立12个关系模式。实验三创建数据库和表【实验目的】掌握创建数据库和表的方法。数据库和表的创建可以通过SQL Server的工具实现，也可以通过SQL命令实现。本实验教材更多的使用SQL命令进行操作，以便更好的学习SQL命令的使用。【实验要求】按照上述数据库设计中所创建的数据模型，建立数据库。【实验内容】登录服务器，并创建查询使用SQL命令创建数据库 create database ebook on primary (name=ebook_data,filename=d:dbebookebook_Data.MDF,size=5mb,filegrowth=10%)log on(name=ebook_log,filename=d:dbebookebook_Log.LDF,size=5mb,filegrowth=10%);使用SQL命令创建表【实验内容】建立数据库关系图 实验四数据正确性约束【实验目的】了解SQL语句中有关数据正确性检查的方法。掌握约束、规则、默认值等方法的应用。实验四数据正确性约束【实验说明】约束的功能是对字段内的数据进行检查，只有符合约束的数据才可以存储，故约束是保证数据正确的工具。规则是单独存在的对象，每个字段或用户定义的数据类型都可绑定至一个规则。SQL Server会在存储数据时使用所有规则及约束执行检查操作。默认值的目的是存储新纪录时，若字段内没有数据，就以默认值为此列进行赋值。实验四数据正确性约束【实验要求】使用约束，设置订单表中的送货时间大于订货时间使用规则，设置ebook中各数据表主键的输入格式设置进货信息表的数量默认值为50【实验内容】创建约束ALTER TABLE 订单 WITH CHECK ADD CONSTRAINT CK_时间 CHECK(订货时间=送货时间)【实验内容】使用部门编号规则（其他略）CREATE RULE eb_ru_部门编号AS value like d0-90-9;goEXEC sp_bindrule eb_ru_部门编号,部门.部门编号,futureonly;-EXEC sp_unbindrule 部门.部门编号,futureonly-drop rule eb_ru_部门编号【实验内容】设置进货量的默认值CREATE DEFAULT eb_df_进货量 AS 50goEXEC sp_bindefault eb_df_进货量,进货信息.数量,futureonly-EXEC sp_unbindefault 进货信息.数量,futureonly-drop DEFAULT eb_df_进货量实验五数据管理【实验目的】建立数据表后，需要进行数据录入、导入及维护的数据管理工作。本实验主要学习数据管理的一些常用方法。【实验要求】输入数据。导入外部数据。导入其他数据库中的数据表。导入完整数据库【实验内容】使用Transact-SQL插入数据use ebook-将数据插入“部门”表Insert into 部门 values(d01,业务部);insert into 部门 values(d02,销售部);insert into 部门 values(d03,仓库);其他表的数据插入（略）【实验内容】导入外部数据在输入数据表中的数据时，常常需要直接导入已存在于外部文本文件或Excel文件中的数据，这时可以不必重新录入数据，而将已存在的数据直接导入到数据表中。现假设订单明细中的数据已经存在于文本文件“D:db订单明细.txt”中，将其导入到“订单明细”数据表中。【实验内容】导入外部数据delete from 订单明细;select*into 订单明细_in from OpenRowset(MSDASQL,Driver=Microsoft Text Driver(*.txt;*.csv);DefaultDir=D:db;,select*from 订单明细.txt)insert into 订单明细select*from 订单明细_in;select*from 订单明细;drop table 订单明细_in;【实验内容】导入其他数据库中的数据表 在其他数据库（可以是本服务器中的数据库，也可以是其他服务器的数据库；可以是SQL Server的数据库，也可以是Access等其他类型的数据库）中已存在的数据表，也可以直接导入到当前数据库中，该操作可以通过SQL Server 2005提供“导入、导出”向导来操作完成。【实验内容】导入其他数据库中的数据表 启动“导入和导出”向导选择数据源选择目标制定数据源的表、视图或查询执行导入【实验内容】导入完整数据库从原服务器分离数据库复制数据源（数据文件和日志文件）在目标服务器，附加数据库实验六SQL语句【实验目的】掌握SQL语句的基本使用方法。掌握SQL 查询、维护（插入、更新、删除）等常用语句。实验六SQL语句【实验要求】1、查询库存情况。（仓库名称，图书名称，数量）。2、查询每个书店的购书明细。（书店名称，订单编号，图书名称，单价，数量，金额）。3、查询从哪个出版社购进了非该出版社出版的图书。4、查询每笔订单的销售金额。5、生成每笔订单的销售金额。6、统计每个物流公司配送的图书总金额。7、查询下过订单的书店信息。8、查询没有购买纪录的书店信息。实验六SQL语句【实验要求】9、查询进货明细。（姓名，出版社名称，图书名称，数量）。10、查询购书金额最大的订单信息。11、查询总购书金额最大的书店信息。12、查询销售人员的销售总金额，按金额降序排列。13、查询哪类图书的销量最大。14、查询销量最小的5种书。15、查询购买过本公司包含的天津出版的全部图书的用户信息。16、删除“高等教育出版社”信息。【实验内容】查询库存情况。Select 仓库名称,图书名称,库存量from 仓库信息,图书信息where 图书信息.仓库编号=仓库信息.仓库编号;【实验内容】查询每个书店的购书明细。select 名称,订单.订单编号,图书名称,单价,数量,数量*单价 金额from 订单,订单明细,图书信息,书店信息where 订单.书店编号=书店信息.书店编号and 订单.订单编号=订单明细.订单编号and 订单明细.图书编号=图书信息.图书编号order by 名称;【实验内容】查询从哪个出版社购进了非该出版社出版的图书。select 进货信息.出版社编号,图书名称,图书信息.出版社编号from 出版社信息,进货信息,图书信息where 出版社信息.出版社编号=进货信息.出版社编号and 进货信息.图书编号=图书信息.图书编号and 图书信息.出版社编号 进货信息.出版社编号;【实验内容】查询每笔订单的销售金额。select 订单.订单编号,sum(数量*单价)金额from 订单,订单明细,图书信息where 订单.订单编号=订单明细.订单编号 and 订单明细.图书编号=图书信息.图书编号group by 订单.订单编号【实验内容】生成每笔订单的销售金额。UPDATE 订单 SET 金额=(SELECT SUM(数量*单价)FROM 订单明细,图书信息WHERE 订单明细.订单编号=订单.订单编号AND 图书信息.图书编号=订单明细.图书编号)【实验内容】统计每个物流公司配送的图书总金额。select 物流公司.物流公司编号,物流公司名称,sum(金额)金额from 物流公司,订单where 物流公司.物流公司编号=订单.物流公司编号group by 物流公司.物流公司编号,物流公司名称【实验内容】查询下过订单的书店信息。select distinct 名称from 书店信息,订单where 书店信息.书店编号=订单.书店编号【实验内容】查询没有购买纪录的书店信息。select distinct 名称from 书店信息 left outer join 订单 on 书店信息.书店编号=订单.书店编号where 订单.书店编号 is null【实验内容】查询进货明细。select 姓名,出版社名称,图书名称,数量from 员工信息,出版社信息,图书信息,进货信息where 员工信息.员工编号=进货信息.员工编号 and 出版社信息.出版社编号=进货信息.出版社编号 and 图书信息.图书编号=进货信息.图书编号order by 姓名【实验内容】查询购书金额最大的订单信息。select 订单编号,金额from 订单where 金额=(select max(金额)from 订单)【实验内容】查询总购书金额最大的书店信息。select top 1 书店信息.书店编号,名称,sum(数量*单价)金额from 订单,订单明细,图书信息,书店信息where 订单.订单编号=订单明细.订单编号 and 订单明细.图书编号=图书信息.图书编号 and 书店信息.书店编号=订单.书店编号group by 书店信息.书店编号,名称order by 金额 desc【实验内容】查询销售人员的销售总金额，按金额降序排列。select 员工信息.员工编号,姓名,sum(数量*单价)金额from 订单,订单明细,图书信息,员工信息where 订单.订单编号=订单明细.订单编号 and 订单明细.图书编号=图书信息.图书编号 and 员工信息.员工编号=订单.员工编号group by 员工信息.员工编号,姓名order by 金额 desc【实验内容】查询哪类图书的销量最大。select 图书分类.分类号,类别名称,sum(数量)总量from 图书分类,图书信息,订单,订单明细where 图书信息.分类号=图书分类.分类号and 订单.订单编号=订单明细.订单编号and 订单明细.图书编号=图书信息.图书编号group by 图书分类.分类号,类别名称order by 总量 desc【实验内容】查询销量最小的5种书。select top 5 图书信息.图书编号,图书名称,sum(数量)总量from 图书信息 left outer join 订单明细 on 图书信息.图书编号=订单明细.图书编号left outer join 订单 on 订单.订单编号=订单明细.订单编号group by 图书信息.图书编号,图书名称order by 总量【实验内容】查询购买过本公司包含的天津出版的全部图书的用户信息。select 书店信息.书店编号,名称from 书店信息where not exists(select*from 图书信息,出版社信息 where 图书信息.出版社编号=出版社信息.出版社编号and 地址 like%天津%and not exists(select*from 订单,订单明细 where 订单.订单编号=订单明细.订单编号and 订单明细.图书编号=图书信息.图书编号and 书店信息.书店编号=订单.书店编号)【实验内容】删除“高等教育出版社”信息。delete from 订单明细where 图书编号 in(select distinct 图书编号 from 图书信息,出版社信息 where 图书信息.出版社编号=出版社信息.出版社编号and 出版社名称=高等教育出版社)delete from 进货信息where 出版社编号 in(select distinct 出版社编号 from 出版社信息 where 出版社名称=高等教育出版社)【实验内容】删除“高等教育出版社”信息。deletefrom 图书信息where 出版社编号 in(select 出版社编号 from 出版社信息 where 出版社名称=高等教育出版社)deletefrom 出版社信息where 出版社名称=高等教育出版社实验七数据库安全控制【实验目的】学习数据库安全控制的基本概念。了解SQL Server的安全机制、身份验证模式和用户许可权限管理方法。熟悉并学习角色、权限的具体使用方法。学习通过平台操作和SQL语句两种方式完成实验内容。实验七数据库安全控制【实验说明】验证模式包括Windows验证模式和混合模式。管理登录名。每位用户必须用登录名连接服务器，取得访问权限。实验七数据库安全控制【实验说明】用户管理。用户是服务器针对数据库的权限设置。管理人员可以自定义用户，也可以设置权限。登录名与用户名是一对多的关系，一个服务器的登录名可以映射到多个数据库的用户，但在一个数据库中只能有一个映射。使用角色。角色是拥有访问权限的组，一个用户可隶属于多个角色，隶属之后即拥有该角色的权限。设置权限。可以通过角色或直接向用户授权。实验七数据库安全控制【实验要求】为员工信息表中的三个部门各创建一个登录名。创建ebook数据库用户定义角色设置权限【实验内容】创建登录名 在对象资源管理器中创建登录名使用SQL语言创建登录名use masterCREATE LOGIN SusanWITH PASSWORD=1234,DEFAULT_DATABASE=eBook,DEFAULT_LANGUAGE=简体中文,CHECK_EXPIRATION=ON,CHECK_POLICY=ON【实验内容】创建用户 在对象资源管理器中创建用户使用SQL语句创建数据库用户 USE ebookCREATE USER Susan_uibe FOR LOGIN Susan WITH DEFAULT_SCHEMA=dbo【实验内容】定义角色 在对象资源管理器中创建角色同时，设置拥有该角色的用户。【实验内容】设置权限 通过Grant命令授权通过Revoke命令收回权限实验八T-SQL基本语句【实验目的】掌握T-SQL的基本概念。学习T-SQL语言的基本语句结构。实验八T-SQL基本语句【实验说明】Transact-SQL是SQL Server提供的内附语言，不是SQL的标准内容，是标准SQL语言的扩展，是为了编程方便而增加的语言元素，所有SQL Server的处理操作都可通过T-SQL来完成。由于Transact-SQL是扩展部分，不同的DBMS厂商提供的内容、语法结构也不尽相同，Oracle提供的扩展语言称为PL/SQL。T-SQL的组成包括：基本语句、系统资源、数据定义语言、数据操纵语言等。实验八T-SQL基本语句【实验要求】公司按订单的销售金额给书店进行折扣，假设折扣规律如下订单金额小于1000元，折扣0.8订单金额小于2000元而大于1000元，折扣0.75订单金额小于3000元而大于2000元，折扣0.7订单金额小于4000元而大于3000元，折扣0.65订单金额大于等于4000元，折扣0.6统计每笔订单的实际成交金额实验八T-SQL基本语句【实验要求】查询ebook数据库中每个数据表的空间使用情况按比例调整员工的基本工资提高公司员工的基本工资水平，使平均基本工资不小于5200元同时工资的最高上限不超过8000并显示调整后员工的工资情况【实验内容】按订单的销售金额进行折扣，并显示实际成交金额。select 订单.订单编号,成交金额=case when 金额 1000 then 金额*.8 when 金额 1000 then 金额*0.75 when 金额 2000 then 金额*0.7 when 金额 3000 then 金额*0.65 when 金额=4000 then 金额*0.6 endfrom 订单【实验内容】查询ebook数据库中每个数据表的空间使用情况。Declare next char(20)Select next=While next is not nullBeginSelect next=min(name)From sysobjects where type=Uand namenextEXEC sp_spaceused nextEnd【实验内容】查询ebook数据库中每个数据表的空间使用情况。说明：next=与 next is null 是不同的；Sysobjects为系统表，即数据字典，记录了数据库所有对象的信息；sp_spaceused 为存储过程，通过该过程查询数据表的使用情况。【实验内容】按比例调整员工的基本工资 While(select avg(基本工资)from 员工信息)8000Endselect avg(基本工资)平均工资 from 员工信息 Select*from 员工信息实验九存储过程【实验目的】掌握存储过程的基本概念学习存储过程的建立和使用方法实验九存储过程【实验说明】存储过程是存储在SQL Server数据库中的程序，即存储在“后台”中，通过T-SQL语句编写而成。其优点包括：可进行模块化的程序编写；可加快执行速度；可减少网络负担；可提供安全机制。存储过程可分为系统存储过程；本地存储过程；临时存储过程；远程存储过程及系统扩展存储过程。只有系统数据库才有扩展存储过程。系统存储过程是SQL Server内置的存储过程，在前面的实验中已多次使用，内置的存储过程均以“sp_”开头。除了使用内置的存储过程外，用户可以根据应用的需要，自定义存储过程，即本地存储过程。实验九存储过程【实验要求】建立存储过程，查询某种书的库存量。建立存储过程，查询某个员工的销售业绩。创建存储过程，查询某时间段的某书店的订单信息。【实验内容】建立存储过程，查询某种书的库存量 CREATE PROCEDURE eb_sp_图书库存BookID varchar(4)ASSELECT 图书编号,图书名称,库存量FROM 图书信息WHERE 图书编号=BookIDGOexec eb_sp_图书库存 b006【实验内容】建立存储过程，查询某个员工的销售业绩 CREATE PROCEDURE eb_sp_员工销售业绩EmpID varchar(3)ASselect 员工信息.员工编号,sum(数量*单价)金额from 订单,订单明细,图书信息,员工信息where 订单.订单编号=订单明细.订单编号 and 订单明细.图书编号=图书信息.图书编号 and 员工信息.员工编号=订单.员工编号 and 员工信息.员工编号=EmpIDgroup by 员工信息.员工编号goexec eb_sp_员工销售业绩 e07【实验内容】创建存储过程，查询某时间段的某书店的订单信息 create proc eb_sp_订单查询 startdate datetime,enddate datetime,sno varchar(3)=s05as if(startdate is null or enddate is null)begin raiserror(时间为空,5,5)return end select*from 订单 where 订货时间 between startdate and enddate and 书店编号=sno【实验内容】创建存储过程，查询某时间段的某书店的订单信息 exec eb_sp_订单查询 2011-3-20,2011-4-20,s04exec eb_sp_订单查询 2011-3-20,2011-4-20 exec eb_sp_订单查询 2011-3-20,null实验十触发器【实验目的】掌握触发器的基本概念。学习SQL触发器的建立、修改、删除的基本方法。通过SQL语句建立触发器并验证。实验十触发器【实验说明】触发器是一种特殊类型的存储过程，只要数据表中的相关数据一经修改就会自动执行该存储过程。触发器的功能是响应INSERT、UPDATE或DELETE等语句的执行并产生相应操作。触发器的三个重要组成部分：名称、作用位置和作用时刻。触发器的作用时刻有两种组合，一种是FOR、AFTER、INSTEAD OF，另一种是INSERT、UPDATE及DELETE。两者的组合成为触发器的启动时刻。若第一种省略，以AFTER为默认值。在触发器中可以使用两个特殊的数据表：inserted；deleted。实验十触发器【实验说明】Inserted数据表会存储被insert和update语句影响的记录副本。在插入或更新数据时，新的记录会同时添加至触发器作用的数据表与inserted数据表中。Inserted数据表中的记录即为触发器作用数据表中新加入记录的副本。Deleted数据表会存储由于delete和update语句而影响的记录副本。在delete或update语句执行时，触发器数据表中被删除的数据会传送到deleted数据表，正常情况下，deleted数据表与触发器作用的数据表不会有相同的记录。实验十触发器【实验要求】在“订单明细”中进行 增删改 操作时，自动更新订单中的金额。自动更新图书信息的库存量。【实验内容】在“订单明细”中进行 增删改 操作时，自动更新订单中的金额。创建“订单明细”数据表的“插入”触发器创建“订单明细”数据表的“删除”触发器创建“订单明细”数据表的“更新”触发器验证触发器【实验内容】自动更新图书信息的库存量。创建“进货信息”数据表的“插入”触发器创建“进货信息”数据表的“删除”触发器创建“进货信息”数据表的“更新”触发器验证触发器实验十一数据库备份及还原【实验目的】了解故障的种类及特点、数据库备份的种类、理解备份设备的概念。掌握备份与还原的基本概念及其重要性。了解SQL Server 2005的备份机制。学习并掌握数据库备份的方法和内容。学习使用系统提供的相应工具。实验十一数据库备份及还原【实验要求】通过平台操作进行数据库的备份及还原。使用SQL语句完成数据库的备份及还原。【实验内容】执行ebook-完整数据库的备份及还原 备份数据库 删除原数据库执行还原【实验内容】通过T-SQL命令完成备份及恢复过程 备份数据库 backup database ebook to disk=d:dbebook_backebook.bakwith password=1234,stats=10,init删除原数据库use masterdrop database ebook【实验内容】通过T-SQL命令完成备份及恢复过程 执行还原restore database ebook from disk=d:dbebook_backebook.bak with password=1234,move ebook_data to d:dbebookebook_Data.MDF,move ebook_log to d:dbebookebook_Log.LDF 实验十二代理、作业【实验目的】理解SQL Server代理的含义掌握作业的创建方法掌握数据库维护计划的操作方法实验十二代理、作业【实验说明】SQL Server代理程序，相当于一个辅助管理员，可以处理重复任务，主要包括：运行在特定时间或间隔发生的SQL Server任务；检测在特定的情况下要执行的操作；运行管理员定义的重复的任务。作业，是由SQL Server代理程序按顺序执行的一系列指定的操作，包括运行T-SQL脚本、命令程序和Microsoft ActiveX脚本。可以创建作业来执行经常重复和可调度的任务，还可以产生警报来通知用户系统的状态。实验十二代理、作业【实验要求】创建代理，定期备份数据库【实验内容】创建作业实验十三事务处理【实验目的】复习事务处理的基本概念。了解SQL Server 2005的事务机制。了解并学习事务的启动、结束等设计方法。实验十三事务处理【实验说明】事务是通过工作的单个逻辑单元（Logic Unit）执行的一系列相关操作。在SQL Server中，每一个工作逻辑单元都必须呈现出四种属性：原子性。一致性。隔离性。持续性。实验十三事务处理【实验说明】三种事务模式显式事务自动提交事务隐式事务结束事务。设计人员可以使用COMMIT或ROLLBACK来结束事务。实验十三事务处理【实验要求】建立存储过程，将某订单的送货时间改为当前时间，同时按订单明细将该订单的所有购书数量从库存中减去。实验十三事务处理【实验内容】建立存储过程在“订单”数据表的“送货时间”中存放当天日期并获取“订单明细”的出货明细及数量再从“商品信息”数据表的“库存量”字段减去出库数量且这两个操作必须全部更新无误，若有一个出错，则进行还原。查询某订单详细信息执行存储过程重新查询该订单信息