SQL Server数据库实训

1

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

实验环境

1．软件需求

（1）操作系统：Windows 2000 Professional，或者Windows XP （2）数据库管理系统：SQL Server2000

（3）应用开发工具：自选VC++、visual Studio、myEclipse等 （4）其它工具：Word 2．硬件需求 （1）PC机 （2）网络环境

2

基本需求信息

一、对某商场采购销售管理进行调研后，得到如下基本需求信息： 该商场有多名工作人员（主要是采购员和销售员），主要负责从供应商处采购商品，而后将商品销售给客户。采购员主要负责根据商场的销售情况确定要采购的商品，并与供应商联系，签订采购单。销售员主要负责将采购来的商品销售给客户，显然一个客户一次可能购买多种商品。一个供应商可以向该商场供应多种商品，而一种商品也可以由多个供应商供应。 商场的管理者每个月需要对该月已采购的商品和已销售的商品进行分类统计，对采购员和销售员的业绩进行考核，对供应商和客户进行等级评定，并计算商场利润。 二、E-R图

职 员1mn签 订1签 订n采购单1明 细m采购明细p包 含1签 订1供应商n供 应m商 品1单价客 户1签 订p销售单1明 细n销售明细m包 含 三、需要建立的数据表如下

1． 供应商表：供应商ID，供应商名称，地区，信誉等级 2． 供应表：供应商ID，商品ID，商品单价

3． 商品表：商品ID，商品名称，商品库存量，商品均价

4． 采购单表：采购单ID，采购员ID，供应商ID，采购总金额，签订日期 5． 采购明细表：采购单ID，商品ID，采购数量，商品单价

6． 销售单表：销售单ID，销售员ID，客户ID，销售总金额，签订日期 7． 销售明细表：销售ID，商品ID，销售数量，商品单价，单价折扣 8． 客户表：客户ID，客户名称，联系电话，客户等级 9． 职员表：职员ID，职员姓名，职员类型

3

实验一SQL Server安装及管理工具使用

一、实验目的与要求

1． 熟悉SQL Server的基本安装与配置过程

2． 熟悉SQL Server企业管理器的主要功能、对数据库的组织和管理方法 3． 熟悉SQL Server查询分析器的主要功能、对数据库的组织和管理方法 3．掌握基本表、主码等基本概念

4．掌握SQL语言的数据定义功能，要求能使用企业管理器和查询分析器两种方法来建立数据库、以及数据表。

5．掌握修改和删除表结构的方法

二、实验内容

1．学习使用SQL Server的基本管理工具：企业管理器和查询分析器 2．掌握SQL Server默认安装的各个数据库的主要作用 （1）master数据库 （2）model数据库 （3）msdb数据库 （4）tempdb数据库

3．使用企业管理器和查询分析器建立、删除数据库 （1）建立一个名称为supermarket的数据库

（2）查看与新生成的数据库相关的内容

（3）删除名称为supermarket的数据库

点击supermarket右键，选择“删除操作”。

4

4． 使用企业管理器和查询分析器建立、删除数据表

create database supermarket drop database supermarket

（1）在已经建立的supermarket数据库的基础上，建立以下数据表（参考附录A）： （a）商品表 （b）供应商表 （c）供应表 （d）采购单表 （e）采购明细表 （f）销售单表 （g）销售明细表 （h）客户表 （i）职员表

（2）使用企业管理器查看新生成表的结构和内容 commodity

customer

Employee

5

sale

saleDetail

Stock

stockDetail

6

Supplyier

supplying

（3）分别使用企业管理器和查询分析器修改表结构

（a）在商品表中加入描述商品信息的属性 a.在企业管理器下

b.在查询分析器下

ALTER TABLE commodity

ADD characteristics varchar(50) NULL

（b）将客户表中的客户联系电话（CUtelephone）的数据类型改为varchar(20)

（4）分别使用企业管理器和查询分析器删除基本表

（a）使用企业管理器删除客户表 点击customer右键，点击删除操作

（b）使用查询分析器删除商品表 drop table customer

三、自我测试

1． 使用企业管理器新建一个名称为“test”的数据库 2． 使用查询分析器在test数据库建立以下数据表

（1）学生表：学号，姓名，性别，出生日期，系别

7

（2）课程表：课程编号，课程名称，学分 （3）选课表：学号，课程号，成绩 create database test create 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, studentNo 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

8

实验二 数据插入、修改和删除

一、实验目的与要求

1．掌握SQL Server的数据更新功能，能使用INSERT、UPDATE、DELETE命令对数据表中的数据进行更新

2．掌握INSERT…SELECT和SELECT…INTO语句的使用方法

二、实验内容

1．使用INSERT命令向基本表中插入数据（参考附录B） （1）向供应商表中插入如下数据记录： insert into supplier values (

'A001','神州数码有限公司','北京','1' )

（2）向商品表中插入如下数据记录

（3）向职员表中插入如下数据记录

（4）向供应表中插入如下数据记录

9

（5）使用企业管理器分别向客户表、采购表、采购明细表、销售表、销售明细表中添加相

应记录。

客户表

采购单表

采购明细表

销售单表

销售明细表

10

2．使用UPDATE命令修改基本表中的数据

1）将编号为‘F70006’的商品的单价改为12.00元 update supplying set price=12.00 where cid='F70006'

（2）将名称为‘家乐福’的客户的等级改为VIP客户。 update customer set CUlevel=2

where CUname='家福乐'

（3） 将编号为‘A102’ 供应商供应商品的价格全部打八折 update saleDetail set SDdiscount = 0.8

from supplying a, commodity b, saleDetail c

where a.Cid=b.Cid and a.Sid='A102' and c.Cid=b.Cid 3．使用DELETE命令删除基本表中的数据 （1）删除采购明细表中的所有记录 delete from stockDetail

（2）从采购单表中删除2005年以前签订的采购单 delete from stock

where year(CGdate)<=2005

4．使用SELECT…INTO语句向表中成批插入记录

（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_commodity select *

from supplying where Sid='A102'

11

（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_Customer select *

from customer where CUlevel=2

5．使用INSERT…SELECT语句向表中成批插入记录

（1）新建一个名称为‘achievement’的表，其属性包括销售单ID，销售单总金额，销售日

期

create table achievement (

SAid char(12) PRIMARY KEY, --销售单ID SAmoney float ,--销售单总金额 SAdate datetime ,---销售日期 )

（2）将编号为‘S0002’的销售员签订的销售单信息存储到achievement表中。 insert into achievement(SAid,SAmoney,SAdate) select SAid,SAmoney,SAdate from sale

where SAid in ( select SAid from sale where Eid= 'S0002') 三、自我测试

1．从采购明细表中删除所有从供应商‘A153’处采购的编号为‘F70006’商品的明细信息 delete from stockDetail

where Cid in(select Cid from stockDetail where Cid='F70006')

2．从销售明细表中删除2005年以前签订的销售单的销售明细记录 delete from saleDetail

where SAid in(select b.SAid from saleDetail a,sale b where a.SAid=b.SAid and year(SAdate)<'2005')

12

实验三 单表查询

一、实验目的与要求

1．掌握基本的SELECT…FROM语句的用法

2．能应用SELECT语句及其子句解决常见的查询问题 3．掌握模糊查询的使用方法

4．能应用聚集函数和GROUP BY子句进行统计查询

二、实验内容

1． 查找所有商品的名称和库存信息 2． select Cname,Cstorage 3． from commodity

4．

5． 查找‘北京’地区的供应商 6． select *

7． from supplier

8． where Sarea='北京'

9． 查找所有销售人员的基本信息

select *

from Employee

10． 查找签订日期在‘2005-5-1’和‘2006-3-1’之间签订的采购单的基本信息

select *

13

from stock

where CGdate between '2005-5-1'and'2006-3-1'

11． 查找2006年签订的所有销售单，要求显示销售单ID、供应商ID和销售单总金额

select SAid,Cuid,SAmoney from sale

where year(SAdate)='2006'

12． 查找每个采购员所签订的采购单的数目，显示结果按采购单数目降序排序

select Eid,count(Eid)采购单数目 from stock group by Eid

order by count(Eid) desc

13． 查询每个销售员签订的销售单总金额

select Eid,sum(SAmoney)销售单总金额 from sale, group by Eid

14． 查找2005年与每个客户签订的销售单的总金额，只显示总金额大于500000万的客

户ID和总金额

select CUid,sum(SAmoney)销售单总金额 from sale

group by CUid,SAmoney,SAdate

having year(SAdate)=2005 AND sum(SAmoney)>500000

15．

查询2005年签订的金额最大的一笔销售单，要求显示采购员ID、客户ID和销售

14

金额

select Eid,CUid,SAmoney from sale

where SAmoney in(select max(SAmoney) from sale where year(SAdate)=2005 )

10． 查询2005年与编号为‘A002’的供应商签订的所有采购单的总金额

select Sid,sum(CGmoney) from stock

where year(CGdate)=2005 AND Sid='A002' group by Sid

11． 查询名称中含有‘机’字的所有商品的ID、名称和库存量

select Cid,Cname,Cstorage from commodity

where Cname like'%机%'

12． 查找第二个汉字是‘翔’的商品名称和单价。

select Cname,price

from supplying a,commodity b

where a.Cid=b.Cid and Cname like '_翔%'

三、自我测试

1．查询2005年签订的销售合同总额排名前5名的销售员的ID和合同总额，要求结果按照合同总额降序排列

select top 5 Eid销售员ID,sum(SAmoney)合同总额 from sale

where year(SAdate)=2005 group by Eid

order by sum(SAmoney)desc

15

2．查询至少供应了3种商品的供应商的ID select Sid, count(cid)商品种数 from stockDetail a ,stock b where a.CGid=b.CGid group by a.CGid ,Sid having count(Cid)>=3

4． 查询在2006年至少已经与2名销售员签订了合同的客户ID

select CUid,count(SAid)签订合同次数 from sale

WHERE year(SAdate)=2006 GROUP BY CUid

having count(SAid)>=2

16

实验四 表连接查询

一、实验目的与要求

1．掌握表连接查询的使用方法 2．掌握子查询的使用方法

3．能使用内连接、左外连接、右外连接以及交叉连接解决相关问题 4．熟悉系统函数的使用

二、实验内容

1．查找每一个供应商供应的商品的名称，要求显示供应商名称和商品名称。 select Sid,Cname

from supplying a ,commodity b where a.Cid=b.Cid

2．使用连接查询查找与姓名为‘李云’的采购员签订采购单的供应商的名称。 select Sname

from stock a ,supplier b,Employee c where a.Sid=b.Sid and a.Eid=c.Eid and Ename='李云'

3．使用关键字IN完成查询，要求查找与姓名为‘李云’的采购员签订采购单的供应商的名称。 select Sname from supplier where Sname in (

select Sname

from stock a ,supplier b,Employee c where a.Sid=b.Sid and a.Eid=c.Eid and Ename='李云'

17

)

4．使用左外连接完成如下查询：要求查找所有采购员签订的采购合同的详细信息。 select a.Eid,CGid,Sid,CGmoney,CGdate

from Employee a left join stock b on a.Eid=b.Eid where Etype=2

5．使用左外连接完成如下查询：查找所有客户购买的商品详细信息，要求显示客户名称， 商品名称，销售数量，商品单价，没有购买商品的客户也要显示。

6．请使用内连接完成如下查询：查找每一个供应商供应的商品的种类，要求显示供应商名称，供应的商品的种类。

7．查找购买了编号为‘A001’的供应商供应的商品的客户名称 select CUname

from supplying b,commodity c,saleDetail d,sale e,customer f where b.Cid=c.Cid and c.Cid=d.Cid and d.SAid=e.SAid and e.CUid=f.CUid and b.Sid='A002'

8．查找销售员‘王良’在2005年签订的销售合同的详细信息。 select c.SAid,Cid,SDnumber,SDprice,SDdiscount from Employee a ,sale b,saleDetail c

where a.Eid=b.Eid and b.SAid=c.SAid and Ename='王良'and year(SAdate)=2005

18

三、自我测试

1．使用右外连接完成如下查询：要求查找所有采购员签订的采购合同的详细信息，没有签订采购单的采购员也要显示

2．查找购买了名称为‘联想集团’的供应商供应的商品的客户名称 select distinct a.Sname,CUname

from supplier a ,supplying b,commodity c,saleDetail d,sale e,customer f where 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='联想集团'

19

实验五 表综合查询使用

一、实验目的与要求

1．能熟练应用SELECT语句及其相关字句

2．能将SELECT与系统函数、IF ELSE、WHILE等语句进行综合应用并解决相关问题

二、实验内容

1．查询所有供应‘鲜橙多’的供应商的最低报价。

select Cname,MIN(price)最低报价 from supplying a,commodity b

where a.Cid=b.Cid and Cname='鲜橙多' group by a.Cid,Cname

2．查找销售员‘王良’在2005年签订的所有销售合同中每一类商品的总金额。 select distinct sum(SAmoney)同一类商品总金额,c.Eid,e.Cname from saleDetail a,sale b,Employee c,commodity e

where a.SAid=b.SAid and c.Eid=b.Eid and e.Cid=a.Cid and Ename='王良'and year(SAdate)=2005

group by a.Cid,c.Eid,e.Cname

3．汇总由姓名为‘刘明’的采购员在2005年采购的‘数码相机’的总金额。 select b.Ename,d.Cname,sum(CGmoney)总金额 from stock a,Employee b,stockDetail c,Commodity d where 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.Cname

5． 汇总由姓名为‘刘明’的采购员在2005年采购的各类商品的数量。 select Ename,SDnumber

from Employee a,stockDetail b,stock c

20

三、自我测试

1． 稍微修改supermarket数据库中的内容，使用企业管理器建立supermarket数据库的完

全备份和日志备份，并使用它们进行数据库恢复。

2．在查询分析器中使用命令进行简单地数据库备份和恢复。

36

实验十一 数据传输

一、实验目的与要求

1．了解导入导出数据的作用

2．使用DTS工具在不同的数据源之间进行数据的导入导出 3．掌握bcp等基本工具的使用

二、实验内容

1． 使用DTS将supermarket中的数据导入到其它SQL Server数据库中

2． 使用DTS将supermarket中的数据导入到Excel表中

3． 使用DTS将supermarket中的数据导入到文本文件中

4．使用DTS将外部数据源数据源导入到SQL Server数据库中

三、自我测试

1． 练习使用bcp实用工具 2． 练习使用BULK INSERT语句

37

实验十二 数据库设计与应用实例开发

一、实验目的与要求

1． 学习powerdesigner /rose等工具的使用，并能使用它进行初步的数据库建模 2． 掌握C/S(B/S)数据库系统的基本开发流程

二、实验内容

1．使用ERWin对数据库进行建模，并能将ER模型导入到SQL Server2000的数据库中

2．进行系统开发

三、自我测试

38

其他可选题目：

1． SQL Server性能优化与事件探查器 2． SQL Server代理与自动化管理 3． SQL Server事务设置

39

附录A 建表SQL语句

--1供应商表

CREATE TABLE supplier(

Sid char(10) 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), --商品名称 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, --采购单价 PRIMARY KEY(CGid,Cid) )

40

本文来源：https://www.bwwdw.com/article/2i5g.html