564501124《数据库技术及应用》基础实验指导

1、数据库技术及应用基础实验指导实验环境1软件需求（1）操作系统：windows 2000 professional，或者windows xp（2）数据库管理系统：sql server2000（3）应用开发工具：自选vc+、visual studio、myeclipse等（4）其它工具：word2硬件需求（1）pc机（2）网络环境基本需求信息一、对某商场采购销售管理进行调研后，得到如下基本需求信息： 该商场有多名工作人员（主要是采购员和销售员），主要负责从供应商处采购商品，而后将商品销售给客户。采购员主要负责根据商场的销售情况确定要采购的商品，并与供应商联系，签订采购单。销售员主要负责将采购来的商

2、品销售给客户，显然一个客户一次可能购买多种商品。一个供应商可以向该商场供应多种商品，而一种商品也可以由多个供应商供应。 商场的管理者每个月需要对该月已采购的商品和已销售的商品进行分类统计，对采购员和销售员的业绩进行考核，对供应商和客户进行等级评定，并计算商场利润。二、e-r图三、需要建立的数据表如下1 供应商表：供应商id，供应商名称，地区，信誉等级2 供应表：供应商id，商品id，商品单价3 商品表：商品id，商品名称，商品库存量，商品均价4 采购单表：采购单id，采购员id，供应商id，采购总金额，签订日期5 采购明细表：采购单id，商品id，采购数量，商品单价6 销售单表：销售单id，销

3、售员id，客户id，销售总金额，签订日期7 销售明细表：销售id，商品id，销售数量，商品单价，单价折扣8 客户表：客户id，客户名称，联系电话，客户等级9 职员表：职员id，职员姓名，职员类型实验一sql server安装及管理工具使用一、实验目的与要求1 熟悉sql server的基本安装与配置过程2 熟悉sql server企业管理器的主要功能、对数据库的组织和管理方法3 熟悉sql server查询分析器的主要功能、对数据库的组织和管理方法3掌握基本表、主码等基本概念4掌握sql语言的数据定义功能，要求能使用企业管理器和查询分析器两种方法来建立数据库、以及数据表。5掌握修改和删除表结构

4、的方法二、实验内容1学习使用sql server的基本管理工具：企业管理器和查询分析器2掌握sql server默认安装的各个数据库的主要作用 （1）master数据库（2）model数据库（3）msdb数据库（4）tempdb数据库3使用企业管理器和查询分析器建立、删除数据库 （1）建立一个名称为supermarket的数据库 （2）查看与新生成的数据库相关的内容 （3）删除名称为supermarket的数据库点击supermarket右键，选择“删除操作”。4 使用企业管理器和查询分析器建立、删除数据表create database supermarketdrop database sup

5、ermarket（1）在已经建立的supermarket数据库的基础上，建立以下数据表（参考附录a）： （a）商品表（b）供应商表（c）供应表（d）采购单表（e）采购明细表（f）销售单表（g）销售明细表（h）客户表（i）职员表（2）使用企业管理器查看新生成表的结构和内容commoditycustomeremployeesalesaledetailstockstockdetailsupplyiersupplying（3）分别使用企业管理器和查询分析器修改表结构（a）在商品表中加入描述商品信息的属性a.在企业管理器下b.在查询分析器下alter table commodityadd charact

6、eristics varchar(50) null（b）将客户表中的客户联系电话（cutelephone）的数据类型改为varchar(20)（4）分别使用企业管理器和查询分析器删除基本表（a）使用企业管理器删除客户表点击customer右键，点击删除操作（b）使用查询分析器删除商品表drop table customer三、自我测试1 使用企业管理器新建一个名称为“test”的数据库2 使用查询分析器在test数据库建立以下数据表（1）学生表：学号，姓名，性别，出生日期，系别 （2）课程表：课程编号，课程名称，学分（3）选课表：学号，课程号，成绩create database testcre

7、ate table student( studentno char(7) not null, sname char(20) null, sext char(10) null, brithday datetime null, sdepartment char(20) null) create table course( courseno char(10) not null, cname char(20) null, credithour numeric(1) default 0 null)create table scourse( courseno char(10) not null, stud

8、entno char(7) not null, grade int null)3 使用查询分析器对表进行修改（1） 将学生表中的性别数据类型改为整型alter table student alter column sext int （2） 为学生表增加一个新的属性列，用于存放学生的专业信息alter table student add professional varchar(50) null实验二 数据插入、修改和删除一、实验目的与要求1掌握sql server的数据更新功能，能使用insert、update、delete命令对数据表中的数据进行更新2掌握insertselect和selec

9、tinto语句的使用方法二、实验内容1使用insert命令向基本表中插入数据（参考附录b）（1）向供应商表中插入如下数据记录：insert into supplier values(a001,神州数码有限公司,北京,1 )（2）向商品表中插入如下数据记录（3）向职员表中插入如下数据记录（4）向供应表中插入如下数据记录（5）使用企业管理器分别向客户表、采购表、采购明细表、销售表、销售明细表中添加相应记录。客户表采购单表采购明细表销售单表销售明细表2使用update命令修改基本表中的数据1）将编号为f70006的商品的单价改为12.00元 update supplyingset price=12.

10、00where cid=f70006（2）将名称为家乐福的客户的等级改为vip客户。 update customerset culevel=2where cuname=家福乐（3） 将编号为a102 供应商供应商品的价格全部打八折 update saledetailset sddiscount = 0.8from supplying a, commodity b, saledetail cwhere a.cid=b.cid and a.sid=a102 and c.cid=b.cid 3使用delete命令删除基本表中的数据（1）删除采购明细表中的所有记录 delete from stockd

11、etail（2）从采购单表中删除2005年以前签订的采购单delete from stockwhere year(cgdate)=20054使用selectinto语句向表中成批插入记录（1）将供应商a102供应的商品和供应价格存到一个名字为a102_commodity的表中create table a102_commodity( sid char(10), -供应商id cid char(10), -商品id price float, -商品单价 primary key (sid,cid) )insert into a102_commodityselect * from supplying

12、where sid=a102（2）将所有的vip客户信息存储到一个名字为vip_customer的表中create table vip_customer( cuid char(10) primary key, -客户id cuname char(30), -客户姓名 cutelephone char(16), -客户联系电话 culevel int -客户等级：1，普通客户；2，vip客户)insert into vip_customerselect * from customer where culevel=25使用insertselect语句向表中成批插入记录（1）新建一个名称为achie

13、vement的表，其属性包括销售单id，销售单总金额，销售日期create table achievement(said char(12) primary key, -销售单idsamoney float ,-销售单总金额sadate datetime ,-销售日期)（2）将编号为s0002的销售员签订的销售单信息存储到achievement表中。insert into achievement(said,samoney,sadate)select said,samoney,sadatefrom salewhere said in ( select said from sale where ei

14、d= s0002)三、自我测试1从采购明细表中删除所有从供应商a153处采购的编号为f70006商品的明细信息delete from stockdetailwhere cid in(select cid from stockdetail where cid=f70006)2从销售明细表中删除2005年以前签订的销售单的销售明细记录delete from saledetailwhere said in(select b.said from saledetail a,sale b where a.said=b.said and year(sadate)50000015 查询2005年签订的金额最大

15、的一笔销售单，要求显示采购员id、客户id和销售金额select eid,cuid,samoneyfrom salewhere samoney in(select max(samoney) from sale where year(sadate)=2005 )10 查询2005年与编号为a002的供应商签订的所有采购单的总金额select sid,sum(cgmoney)from stockwhere year(cgdate)=2005 and sid=a002group by sid11 查询名称中含有机字的所有商品的id、名称和库存量select cid,cname,cstoragefro

16、m commoditywhere cname like%机%12 查找第二个汉字是翔的商品名称和单价。select cname,pricefrom supplying a,commodity bwhere a.cid=b.cid and cname like _翔%三、自我测试1查询2005年签订的销售合同总额排名前5名的销售员的id和合同总额，要求结果按照合同总额降序排列select top 5 eid销售员id,sum(samoney)合同总额from sale where year(sadate)=2005group by eidorder by sum(samoney)desc2查询至

17、少供应了3种商品的供应商的idselect sid, count(cid)商品种数 from stockdetail a ,stock b where a.cgid=b.cgid group by a.cgid ,sid having count(cid)=34 查询在2006年至少已经与2名销售员签订了合同的客户idselect cuid,count(said)签订合同次数 from salewhere year(sadate)=2006 group by cuid having count(said)=2实验四 表连接查询一、实验目的与要求1掌握表连接查询的使用方法2掌握子查询的使用方法3

18、能使用内连接、左外连接、右外连接以及交叉连接解决相关问题4熟悉系统函数的使用二、实验内容1查找每一个供应商供应的商品的名称，要求显示供应商名称和商品名称。select sid,cnamefrom supplying a ,commodity bwhere a.cid=b.cid2使用连接查询查找与姓名为李云的采购员签订采购单的供应商的名称。select snamefrom stock a ,supplier b,employee cwhere a.sid=b.sid and a.eid=c.eidand ename=李云3使用关键字in完成查询，要求查找与姓名为李云的采购员签订采购单的供应商的

19、名称。select snamefrom supplierwhere sname in(select snamefrom stock a ,supplier b,employee cwhere a.sid=b.sid and a.eid=c.eidand ename=李云)4使用左外连接完成如下查询：要求查找所有采购员签订的采购合同的详细信息。select a.eid,cgid,sid,cgmoney,cgdatefrom employee a left join stock b on a.eid=b.eidwhere etype=25使用左外连接完成如下查询：查找所有客户购买的商品详细信息，要

20、求显示客户名称，商品名称，销售数量，商品单价，没有购买商品的客户也要显示。6请使用内连接完成如下查询：查找每一个供应商供应的商品的种类，要求显示供应商名称，供应的商品的种类。7查找购买了编号为a001的供应商供应的商品的客户名称select cunamefrom supplying b,commodity c,saledetail d,sale e,customer fwhere b.cid=c.cid and c.cid=d.cid and d.said=e.said and e.cuid=f.cuid and b.sid=a0028查找销售员王良在2005年签订的销售合同的详细信息。sel

21、ect c.said,cid,sdnumber,sdprice,sddiscountfrom employee a ,sale b,saledetail cwhere a.eid=b.eid and b.said=c.said and ename=王良and year(sadate)=2005三、自我测试1使用右外连接完成如下查询：要求查找所有采购员签订的采购合同的详细信息，没有签订采购单的采购员也要显示2查找购买了名称为联想集团的供应商供应的商品的客户名称select distinct a.sname,cunamefrom supplier a ,supplying b,commodity

22、c,saledetail d,sale e,customer fwhere a.sid=b.sid and b.cid=c.cid and c.cid=d.cid and d.said=e.said and e.cuid=f.cuid and a.sname=联想集团实验五 表综合查询使用一、实验目的与要求1能熟练应用select语句及其相关字句2能将select与系统函数、if else、while等语句进行综合应用并解决相关问题二、实验内容1查询所有供应鲜橙多的供应商的最低报价。select cname,min(price)最低报价from supplying a,commodity bw

23、here a.cid=b.cid and cname=鲜橙多group by a.cid,cname2查找销售员王良在2005年签订的所有销售合同中每一类商品的总金额。select distinct sum(samoney)同一类商品总金额,c.eid,e.cnamefrom saledetail a,sale b,employee c,commodity ewhere a.said=b.said and c.eid=b.eid and e.cid=a.cid and ename=王良and year(sadate)=2005group by a.cid,c.eid,e.cname3汇总由姓名

24、为刘明的采购员在2005年采购的数码相机的总金额。select b.ename,d.cname,sum(cgmoney)总金额from stock a,employee b,stockdetail c,commodity dwhere a.eid=b.eid and c.cid=d.cid and a.cgid=c.cgid and year(cgdate)=2005 and ename=刘明and cname=数码相机group by b.ename,d.cname5 汇总由姓名为刘明的采购员在2005年采购的各类商品的数量。select ename,sdnumberfrom employe

25、e a,stockdetail b,stock cwhere a.eid=c.eid and b.cgid=c.cgid and year(cgdate)=2005 and ename=刘明group by sdnumber, ename6 查找没有供应任何一类商品的供应商的名字。select snamefrom supplier awhere not exists( select a.sidfrom supplying bwhere a.sid=b.sid)7 查找在2006年各个客户购买商品的总金额，要求结果按照购买商品的总金额降序排序select b.cuid,sum(samoney)购

26、买总金额from sale a,customer bwhere a.cuid=b.cuid and year(sadate)=2006group by b.cuidorder by sum(samoney)desc8 请使用左连接完成以下查询：查找每一个销售人员销售的商品的详细信息，要求显示销售人员姓名、销售单id、客户姓名、商品名称、销售数量、和销售单价。8查找每个采购员和每个供应商签订的合同的总金额，要求显示采购员姓名、供应商名称、和签订合同的总金额。三、自我测试1查找编号为a002的供应商没有供应的商品的名称select cnamefrom supplying a,commodity b

27、where a.cid=b.cid and cname not in (select cname from commodity x,supplying y where x.cid=y.cid and sid=a002)2分别使用union、和inner join关键字完成以下查询：查询由三高计算机公司和联想集团供应商的所有商品的商品id。select cidfrom supplier a,supplying bwhere sname=三高计算机公司and a.sid=b.sidunionselect cidfrom supplier a,supplying bwhere sname=联想集团a

28、nd a.sid=b.sid实验六 视 图一、实验目的与要求1熟悉视图的概念，以及视图与表的联系和区别2掌握视图的定义方法3掌握对视图的查询操作4掌握对视图的更新操作，并了解视图的更新条件5掌握同时对视图和表进行操作的方法二、实验内容1定义一个名称为supplying_view的视图，要求其对应的数据是由天天食品公司供应的商品id、商品单价。create view supplying_viewas select sname,cid,price from supplier a,supplying b where sname=天天食品公司select*from supplying_view2查询采

29、购员李云和供应商天天食品公司签订的所有采购单的采购单id、采购金额、签订日期，并将其建立为一个名称为stock_view_1的视图。create view stock_view_1as select ename,sname,b.cgid,cgmoney,cgdate from stock a,stockdetail b,employee c,supplier d where a.cgid=b.cgid and a.sid=d.sid and a.eid=c.eid and ename=李云 and sname=天天食品公司select *from stock_view_13建立一个名称为com

30、modity_100的视图，其对应所有的库存量小于100的商品的信息。create view commodity_100as select * from commodity where cstorage1000000select *from sale_view2使用上述建立的视图和not in关键字查询在2005年签订的销售单总额没有超过100万的销售员的i d和姓名。select eid,enamefrom employee where etype=2 and eid not in ( select eid from sale_view)实验七 触发器与存储过程一、实验目的与要求1 熟悉触发

31、器和存储过程的用途2 掌握游标的定义和使用方法3 掌握触发器和存储过程的定义以及使用方法4 能使用触发器和存储过程解决相关问题二、实验内容1定义一个触发器，其主要用于在supplying表中插入一条记录时，检查是否该记录对应的供应商id和商品id已经存在于供应商表（supplier）和商品表（commodity）中，如果存在就插入，否则不插入。2在supplying表中插入一条记录（m0073, uu505, 1660.0），执行该插入语句后，到企业管理器中查看该条记录是否已插入，并解释原因。3定义一个触发器，其主要用于在supplier表中删除记录时，将该供应商供应的所有商品的信息一起删除。

32、4在企业管理器中查看表supplier和表supplying的当前内容，在查询分析器中通过sql语句删除表supplier的一条记录，例如（a102, 联想集团, 北京, 1），再次通过企业管理器查看表supplier和表supplying的内容，看是否有所变化，解释变化的原因。5分别在查询分析器中执行系统存储过程sp_databases和sp_tables，解释这两个存储过程的作用。6创建一个存储过程，其根据指定的客户id来返回该客户在2006年签订的所有销售单的明细情况。并使用该存储过程查询编号为kb001的客户在2006年签订的所有销售单的明细情况。三、自我测试1定义一个触发器，用于当s

33、upplier表中某个供应商的id（sid）被修改后，将供应表（supplying）和采购单表（stock）中对应的供应商id也修改过来。修改supplier表中编号为a001的供应商id为bmw01，查看供应表和采购单表中相应记录的变化。create trigger k00dupton supplierfor updateas if update(sid) begin declare sid char(10) declare cur_supplier cursor for select sid from deleted open cur_supplier begin transaction

34、fetch cur_supplier into sid while(fetch_status=0) begin update supplying set supplying.sid=i.sid from inserted i,deleted d where d.sid=supplying.sid fetch cur_supplier into sid update stock set stock.sid=i.sid from inserted i,deleted d where d.sid=stock.sid fetch cur_supplier into sid end commit tra

35、n close cur_supplier deallocate cur_supplier end 2创建一个存储过程，其根据指定的客户名称来返回该客户在2006年签订的所有销售单的总金额。并使用该存储过程查询编号为kb002的客户在2006年签订的所有销售单的总金额。create procedure procedure_customer(cuid char(30),saccount float output)asbegin select saccount=sum(samoney) from sale where cuid=cuid and year(sadate)=2006 - group b

36、y cuidend declare saccount floatexecute procedure_customer kb002 , saccount outputselect saccount 实验九 sqlserver服务器配置与安全管理一、实验目的与要求1掌握sql server服务器的基本配置方式2能使用企业管理器进行链接服务器的配置，并能进行初步地分布式查询3熟悉sql server安全机制， 配置sql server的身份验证模式4掌握角色、登录、用户、许可权限等概念，并能利用企业管理器进行建立以及角色地分配二、实验内容1 注册sql server服务器48图9-12 设置sql

37、server服务器的基本属性图9-23 使用sql server服务器与客户端网络使用工具配置客户端和服务器端的网络协议图 9-34 使用企业管理器配置链接服务器并在查询分析器中进行分布式查询图9-45为sql server服务器创建新的登录，并为之分配相应的数据库角色和数据库访问权限图9-66为supermarket数据库创建新的数据库角色，并设置相应的许可权限图9-77为supermarket数据库定义新的数据库用户，并为之指定登录名、数据库角色和许可权限图9-8三、自我测试1使用新创建的登录，通过查询分析器登录到sql server服务器上，在查询分析器上进行相应地操作以验证许可权限分配

38、的情况。实验十 数据库备份与恢复一、实验目的与要求1. 了解数据库备份与恢复的作用2. 掌握数据库备份与恢复的类型与方法3. 学习使用企业管理器进行数据库的完全备份、差异备份以及日志备份4. 掌握数据库还原和恢复的区别5. 学习使用已有的数据库备份和日志备份对数据库进行恢复5初步掌握使用查询分析器进行数据库备份和恢复的方法二、实验内容1使用企业管理进行supermarket数据库的完全备份2使用企业管理器进行supermarket数据库的差异备份3使用刚建立的supermarket数据库的完全备份和差异备份进行数据库的还原和恢复三、自我测试1 稍微修改supermarket数据库中的内容，使用

39、企业管理器建立supermarket数据库的完全备份和日志备份，并使用它们进行数据库恢复。2在查询分析器中使用命令进行简单地数据库备份和恢复。实验十一 数据传输一、实验目的与要求1了解导入导出数据的作用2使用dts工具在不同的数据源之间进行数据的导入导出3掌握bcp等基本工具的使用二、实验内容1 使用dts将supermarket中的数据导入到其它sql server数据库中2 使用dts将supermarket中的数据导入到excel表中3 使用dts将supermarket中的数据导入到文本文件中4使用dts将外部数据源数据源导入到sql server数据库中三、自我测试1 练习使用bcp

40、实用工具2 练习使用bulk insert语句实验十二 数据库设计与应用实例开发一、实验目的与要求1 学习powerdesigner /rose等工具的使用，并能使用它进行初步的数据库建模2 掌握c/s(b/s)数据库系统的基本开发流程二、实验内容1使用erwin对数据库进行建模，并能将er模型导入到sql server2000的数据库中2进行系统开发三、自我测试其他可选题目：1 sql server性能优化与事件探查器2 sql server代理与自动化管理3 sql server事务设置附录a 建表sql语句-1供应商表create table supplier( sid char(10)

41、 primary key, -供应商id sname char(30), -供应商名称 sarea char(30), -供应商地区 scredit int -供应商信誉等级:1,良好；2，一般；3，较差)-2 供应表create table supplying( sid char(10), -供应商id cid char(10), -商品id price float, -商品单价 primary key (sid,cid) )-3 商品表create table commodity( cid char(10) primary key, -商品id cname char(30), -商品名称

42、cstorage int, -商品库存量 avgprice float -商品均价)-4 采购单表create table stock( cgid char(12) primary key, -采购单id eid char(6), -采购员id sid char(10), -供应商id cgmoney float, -采购金额 cgdate datetime -签订日期)-5 采购明细表create table stockdetail( cgid char(12), -采购单id cid char(10), -商品id sdnumber int, -采购数量 sdprice float, -采

43、购单价 primary key(cgid,cid) )-6 销售表create table sale( said char(12) primary key, -销售单id eid char(6), -销售员id cuid char(10), -客户id samoney float, -销售金额 sadate datetime, -签订日期)-7 采购明细表create table saledetail( said char(12), -销售单id cid char(10), -商品id sdnumber int, -销售数量 sdprice float, -销售单价 sddiscount fl

44、oat, -折扣 primary key(said,cid) )-8 客户表create table customer( cuid char(10) primary key, -客户id cuname char(30), -客户姓名 cutelephone char(16), -客户联系电话 culevel int -客户等级：1，普通客户；2，vip客户)-9 职员表create table employee( eid char(6) primary key, -职员id ename char(8), -职员姓名 etype int -职员类型：1，管理人员；2，采购人员；3，销售人员)附录

45、b 表初始化sql语句-1 供应商表insert into supplier values (a102,联想集团,北京,1)insert into supplier values (a002,三高计算机公司,上海,1)insert into supplier values (a106,玛永制衣公司,广州,2)insert into supplier values (a153,天天食品公司,南宁,2)insert into supplier values (a508,统率咨询公司,武汉,3)insert into supplier values (a001,神州数码有限公司,北京,1)-2 商品

46、表insert into commodityvalues (p0166,天翔pc机,60,5800.00)insert into commodityvalues (p9006,路由器,12,18800.00)insert into commodityvalues (pr4000,小型机,12,210000.00)insert into commodityvalues (h6880,联想1088手机,100,2400.00)insert into commodityvalues (h6122,数码相机,36,3600.00)insert into commodityvalues (s2008,财务软件,10, 8000.00)insert into commodityvalues (f70006,旺旺米饼,200,16.00)insert into commodityvalues (f72500,牛奶,1000,46.00)insert into commodityvalues (f70109,鲜橙多,600,6.00)-3 供应表insert into supplyingvalues (a102,pr4000,210000.0)insert into supplyingvalues (a1