数据库原理实验手册
实验一
实验名称：熟悉SQL SERVER 2000环境
一、实验目的
熟悉SQL SERVER 2000提供的服务管理器、企业管理器、查询分析器、客户端和服务器端网络实用工具等常用管理工具的使用。了解Windows身份验证和SQL Server身份验证这两种连接数据库服务器方式的不同，并能够使用某种连接方式登陆上SQL SERVER数据库服务器。学会使用SQL SERVER的联机丛中寻求帮助。
二、实验原理
使用SQL SERVER 2000提供的各种常用管理工具进行有关的操作。
三、实验设备
安装有SQL SERVER 2000的计算机。
四、实验内容
1．启动SQL SERVER 服务。
2．打开SQL SERVER的企业管理器，连接上SQL SERVER服务器。展开左边树状窗口的各级结点，观察右边内容窗口的变化。
3．在SQL SERVER的企业管理器的树状窗口或内容窗口中选择某一项，点击鼠标右键，了解快捷菜单中的有关功能。
4．使用Windows身份验证的连接方式打开SQL SERVER的查询分析器，在查询窗口用USE命令打开样例数据库pubs（也可以从工具栏中的数据库下拉列表中选择pubs数据库）。
5．在查询窗口输入exec sp_help，运行后察看结果。
6．在查询窗口输入select * from authors ，运行后察看结果。
7．在SQL SERVER的查询分析器的“帮助”菜单中点击“目录与索引”，然后在SQL SERVER的联机丛中选“索引”，输入要查找的关键字“sp_help”、
“exec”、“select”，了解它们的含义。
8．在SQL SERVER的企业管理器中创建数据库S_T，并在该数据库中创建表student、course和sc，在创建表的同时可以创建各种约束。这三个表的结构可以参考书上85页的例5、例6和例7。
9．向各个表输入数据，各个表的数据可参考书56页的图2.4。
10．在SQL Server的查询分析器中打开S_T数据库，输入书中91页开始的例题，实现对数据库的查询。
实验二
实验名称：创建和修改数据库
一、实验目的
熟悉SQL SERVER的企业管理器和查询分析器的用户界面，掌握用企业管理器和查询分析器创建数据库，修改数据库和删除数据库的方法。了解数据库的三种文件类型：主数据文件（.mdf）、次要数据文件（.ndf）和事务日志文件（.ldf）。
二、实验原理
在企业管理器中实现对数据库的管理及使用T—SQL语句CREATE DATABASE、ALTER DATABASE及DROP DA TABASE来实现数据库的创建、修改及删除。
三、实验设备
安装有SQL SERVER 2000的计算机。
四、实验示例
1．创建名称为testdb的数据库，数据库中包含一个数据文件，逻辑文件名为testdb_data，磁盘文件名为testdb.mdf，文件初始容量为2MB，最大容量为8MB，文件容量递增值为1MB；事务日志文件的逻辑文件名为testdb_log，磁盘文件名为testdb_log.ldf，文件初始容量为1MB，最大容量为5MB，文件容量递增值为1MB。
CREATE DA TABASE testdb
ON
( NAME=testdb_data,
FILENAME='d:\DATA\testdb.mdf',
SIZE=2MB,
MAXSIZE=8MB,
FILEGROWTH=1MB )
LOG ON
(NAME=’testdb_log’,
FILENAME='d:\DATA\testdb_log.ldf',
SIZE=1MB,
MAXSIZE=5MB,
FILEGROWTH=1MB )
2．对testdb数据库进行修改：添加一个数据文件，逻辑文件名为testdb2_data，磁盘文件名为testdb2_data.ndf，文件初始容量为1MB，最大容量为5MB，文件容量递增值为1MB。
ALTER DATABASE testdb
ADD FILE
(NAME=testdb2_data,
FILENAME='d:\DATA\testdb2.ndf',
SIZE=1MB,
MAXSIZE=5MB,
FILEGROWTH=1MB )
五、实验内容
分别使用SQL SERVER 2000企业管理器和T—SQL语句，按下列要求创建、修改和删除用户数据库。
1．删除数据库S_T，在查询分析器中用命令采用默认设置创建数据库S_T（只需给出数据库名）。
2．创建名称为company的数据库，数据库中包含一个数据文件，逻辑文件名为company_data，磁盘文件名为company_data.mdf，文件初始容量为5MB，最大容量为15MB，文件容量递增值为1MB；事务日志文件的逻辑文件名为company_log，磁盘文件名为company_log.ldf，文件初始容量为5MB，最大容量为10MB，文件容量递增值为1MB。
3．对该数据库进行修改：添加一个数据文件，逻辑文件名为company2_data，磁盘文件名为company2_data.ndf，文件初始容量为1MB，最大容量为5MB，文件容量递增值为1MB；将日志文件company_log的最大容量增加为15MB，文件容量递增值为2MB。
4．在company数据库中添加一个文件组TempGroup，并向该文件组中添加一个容量为3MB，最大容量为10MB，递增量为1MB的数据文件，该数据文件的逻辑文件名为company3_data，磁盘文件名为company3_data.ndf。
5．在company数据库中删除数据文件company2_data。
6．删除数据库company。
7．采用默认设置创建数据库company。
实验三
实验名称：创建和修改数据表
一、实验目的
熟悉有关数据表的创建和修改等工作，理解数据库模式的概念，了解主键约束、外键约束、UNIQUE约束和CHECK约束的创建和应用。要求学生熟练掌握使用企业管理器和T—SQL语句CREATE TABLE、ALTER TABLE及DROP TABLE语句对数据表进行管理。
二、实验原理
在企业管理器中实现表的创建和修改等工作及使用T—SQL语句CREATE TABLE、ALTER TABLE及DROP TABLE来实现数据表的创建、修改及删除。
三、实验设备
安装有SQL SERVER 2000的计算机。
四、实验示例
两个示例：
一个是书上的学生选课数据库S_T，S_T数据库中有学生表student，课程表course和学生选课表sc。各表的结构如下：
学生表student
课程表course
学生选课表sc
另一个示例是某公司的产品销售数据库company，company数据库中存在人事表employee、客户表customer、销售表sales、销售明细表sale_item、产品表product。各表的结构如下：
员工人事表employee
客户表customer
销售主表sales
销货明细表sale_item
产品名称表product
1．创建表及相应的约束
create table employee1(
emp_no char(5) not null check(emp_no like '[E-F][0-9][0-9][0-9][0-9]'),
emp_name char(10) not null,
emp_sex char(1) not null check(emp_sex in ('m','f')),
emp_phone char (13) not null,
emp_add varchar(60) not null,
emp_salary char(5) not null check (emp_salary between 60000 and 80000)
)
go
2．修改表
alter table employee
add constraint ck_phone check (phone like ’ [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9][0-9]’)
五、实验内容
分别在SQL SERVER 2000企业管理器和在查询分析分析器中使用T—SQL语句完成以下操作：
针对S_T数据库：
1．创建student表，设置Sno为主键；在Sno列上创建核查约束，要求学号各个位上只能取数字字符；在Ssex列上创建核查约束，要求性别只能取“男”或“女”；在Sage 列上创建核查约束，要求学生的年龄只能在14至38之间。2．创建course表，设置列Cno为主键；在Cno列上创建核查约束，要求课程号只能取数字字符。
3．创建sc表，设置主键为Sno与Cno的组合；在Grade列上创建核查约束，限定成绩只能在0-100。
4．修改course表，在表中为Cpno列添加一外键约束Fk_Cpno，它参照了course 表的主键Cno。
5．修改sc表，在表中为Sno添加一外键约束Fk_Sno，它参照了student表中的主键Sno；为Cno添加外键约束Fk_Cno，它参照了course表中的主键Cno。
针对company数据库：
1．在数据库company中创建以上五张表，并设置各表的主键。
2．在销售主表sales中添加字段“发票号码”invoice_no，char(10)，NOT NULL。3．添加外键约束：
a)在销售主表sales的业务员编号字段sale_id上添加外键约束，参照字段
为员工表employee中的字段员工编号emp_no，约束名为FK_sale_id。
b)在销售主表sales的客户号字段cust_id上添加外键约束，参照字段为客
户表customer中的字段客户号cust_id，约束名为FK_cust_id。
c)在销售明细表sale_item的订单编号字段order_no上添加外键约束，参照
字段为销售主表sales中的字段订单编号order_no，约束名为
FK_order_no。
d)在销售明细表sale_item的产品编号字段prod_id上添加外键约束，参照
字段为产品名称表product中的产品编号字段prod_id，约束名为
FK_prod_id。
4．添加核查约束：
a) 将员工表employee中的薪水字段salary的值限定在1000至10000间，约
束名为CK_salary。
b) 将员工表employee中的员工编号字段emp_no设定为以“E”字母开头, 后
面跟5位数的编号，约束名为CK_emp_no。
c)将员工表employee中的性别字段设定这取值只能是“男”和“女”。约
束名为CK_sex。
d)将销售主表sales中的发票号码字段invoice_no设定为以“I”字母开头，
后面跟9位数的编号，约束名为CK_inno。
5．为销售主表sales中的字段发票号码invoice_no设置为唯一约束，约束名为UN_inno。
实验四
实验名称：简单的单表查询
一、实验目的
熟练掌握用SELECT语句实现简单的单表查询。掌握SELECT子句、FROM子句、WHERE子句及ORDER BY 子句的用法。
二、实验原理
用SELECT语句实现简单的单表查询。在SELECT子句中用TOP关键字来限制返回到结果集中的记录数目，用DISTINCT关键字从结果集中去掉重复的记录。WHERE子句中用关系比较符、[NOT] BETWEEN、[NOT] IN、LIKE、IS [NOT] NULL及逻辑运算符构成查询条件，对结果集中的记录进行筛选。ORDER BY 子句将根据查询结果集中一个或多个字段对查询结果进行排序。
三、实验设备
安装有SQL SERVER 2000的计算机。
四、实验示例
1．查找表中所有姓刘的职工的工号，姓名，部门，薪水
select emp_no,emp_name,dept,salary
from employee
where emp_name like '刘%'
2．查找所有定单金额高于20000的所有客户编号
select cust_id
from sales
where tot_amt>20000
3．查找业务部或会计部的女员工的基本信息。
select emp_no,emp_name,dept
from employee
where sex='女' and dept in ('业务','会计')
4．选取编号界于‘C0001’和‘C0004’的客户编号、客户名称、客户地址。select cust_id,cust_name,addr
from customer
where cust_id between 'C0001' AND 'C0004'
5．选取销售数量最多的前5条订单订单号、数量。
select top 5 order_no,qty
from sale_item
order by qty desc
6．计算出sale_item表中每一笔销售数据的销售金额，并按照销售金额的大小排序。
select prod_id,qty,unit_price,(qty*unit_price)tot_amt
from sale_item
order by tot_amt desc
五、实验内容
运行查询文件company.sql，生成上机必要的数据，然后完成以下操作。
1、查找所有经理的姓名、职称、薪水。
2、在销售主表sales中查找销售金额大于等于10000元的订单。
3、在员工表employee中查找薪水在4000至8000元之间的员工。
select *
from employee
where salary between 4000 and 8000
4、在员工表employee中查找住址为上海、北京、天津这三个城市的员工。
5、在客户表customer中查找住址不在上海、北京、天津这三个城市的客户。
6、在员工表employee中查找姓“王”用姓名最后一个字为“功”的员工。
7、在客户表customer中查找姓“刘”的客户名称、电话。
select cust_name,tel_no
from customer
where cust_name like '刘%'
8、查找出职称为“经理”或“职员”的女工的信息。
9、查找薪水最高的前三条员工记录。
10、查找订单金额最高的前10%的订单记录。
select top 10 percent *
from sales
order by tot_amt desc
11、查找员工表中所属部门。（去掉重复记录）
select distinct emp_no,emp_name,dept
from employee
12、查找员工表中的所有记录，并按薪水由低到高进行排序。
select *
from employee
order by salary asc
实验五
实验名称：复杂的单表查询
一、实验目的
熟练掌握SELECT查询语句中的Group by 子句、Having子句的用法，以及汇总函数的使用。
二、实验原理
在查询语句中用Group by子句进行分组；
用Having子句对分组进行筛选。
使用MAX（），MIN（），COUNT（），SUM（），A VG（）等函数在查询结果集中生成汇总值。
三、实验设备
安装有SQL SERVER 2000的计算机。
四、实验示例
1、统计表中员工的薪水在40000-60000之间的人数
select count(*) as 人数
from employee
where salary between 40000 and 60000
2、计算'P0001'产品的平均销售单价
select avg(unit_price)
from sale_item
where prod_id='P0001'
3、找出目前销售业绩超过40000元的业务员编号及销售业绩，并按销售业绩从大到小排序。
Select sale_id ,sum(tot_amt)
from sales
group by sale_id
having sum(tot_amt)>40000
order by sum(tot_amt) desc
4、查询订购了三种以上产品的订单号。
Select order_no from sale_item
Group by order_no
Having count(*)>3
五、实验内容
1、在员工表employee中统计员工人数。
select count(*) 人数
from employee
2、统计各部门员工的员工人数及平均薪水。
select count(*) 人数,avg(salary) 平均工资
from employee
3、查询销售业绩超过10000元的员工编号。
select sale_id
from (select sale_id,sum(tot_amt) amount
from sales
group by sale_id) a
where a.amount > 10000
4、计算每一产品销售数量总和与平均销售单价。
select prod_id,sum(qty) tot_amount,sum(unit_tot)/sum(qty) average_unit_price
from (select prod_id,qty,qty*unit_price unit_tot
from sale_item
) a
group by prod_id
5、统计各部门不同性别、或各部门、或不同性别或所有员工的平均薪水。（在GROUP BY 子句中使用CUBE关键字）
6、统计各部门不同性别、或各部门或所有员工的平均薪水。（在GROUP BY 子
句中使用ROLLUP关键字）
7、计算出一共销售了几种产品。
select count(prod_id)
from (select distinct prod_id
from sale_item) a
8、显示sale_item表中每种产品的订购金额总和，并且依据销售金额由大到小排
列来显示出每一种产品的排行榜。
select prod_id,sum(qty) tot_amount,sum(unit_tot) tot_amount
from (select prod_id,qty,qty*unit_price unit_tot
from sale_item
) a
group by prod_id
order by sum(unit_tot) desc
9、计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排
序。
select prod_id,month,sum(unit_amount) month_amount
from (select prod_id,qty*unit_price unit_amount,month(order_date) month
from sale_item) a
group by prod_id,month
order by prod_id asc,month_amount desc
10、查询每位业务员各个月的业绩，并按业务员编号、月份降序排序
select sale_id,sum(tot_amt)month_amount,month(order_date) month
from sales
group by sale_id,month(order_date)
order by sale_id desc,month desc
实验六
实验名称：连接查询
一、实验目的
掌握使用连接的方法从多个表中查询数据。理解内连接、外连接（包括左外连接、右外连接和全外连接）、自身连接的概念和使用。要求学生熟练掌握在FROM子句和在WHERE子句中指定连接条件的这两种方法。
二、实验原理
在查询语句的FROM子句中用以下形式实现各种连接操作：
●FROM 表1 [INNER] JOIN 表2 ON 表1．列名=表2．列名（实
现内连接）
●FROM 表1 LEFT [OUTER] JOIN 表2 ON 表1．列名=表2．列
名（实现左外连接）
●FROM 表1 RIGHT [OUTER] JOIN 表2 ON 表1．列名=表2．列
名（实现右外连接）
●FROM 表1 FULL [OUTER] JOIN 表2 ON 表1．列名=表2．列
名（实现全外连接）
●FROM 表1 AS 别名1 JOIN 表1 AS 别名2 ON 别名1．列名=
别名2．列名（实现自身连接）
在查询语句的WHERE子句中用以下形式实现各种连接操作：
●FROM 表1，表2 WHERE 表1．列名=表2．列名（实现内连接）
●FROM 表1，表2 WHERE 表1．列名*=表2．列名（实现左外连接）●FROM 表1，表2 WHERE 表1．列名=*表2．列名（实现右外连接）●FROM 表1 AS 别名1 ，表1 AS 别名2 WHERE 别名1．列名=别名
2．列名（实现自身连接）
三、实验设备
安装有SQL SERVER 2000的计算机。
四、实验示例
1、检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名
称、数量、单价。
select a.prod_id,a.qty,a.unit_price,b.prod_name
from sale_item as a inner join product as b /*如果改成left join/right join 试分析结果*/
on (a.prod_id=b.pro_id) and a.qty>2
order by a.prod_id
2、查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。select
a.emp_name,a.sex,a.title,a.salary,a.addr,
b.emp_name,b.sex,b.title,b.salary,b.addr
from employee as a inner join employee as b
on (a.emp_no!=b.emp_no) and (a.emp_name>b.emp_name) and (a.addr=b.addr)
3、查找商品名称为14寸显示器商品的销售情况，显示该商品的编号、销售数量、单价和金额
select a.prod_id,qty,unit_price,unit_price*qty totprice
from sale_item a,product b
where a.prod_id=b.prod_id and prod_name='14寸显示器'
五、实验内容
1、查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪
水、住址。
select
a.emp_name,a.sex,a.title,a.salary,a.addr,
b.emp_name,b.sex,b.title,b.salary,b.addr from employee as a inner join employee as b
on (a.emp_no!=b.emp_no) and (a.dept=b.dept) and (a.addr=b.addr) and a.sex = '女' and a.sex = b.sex
2、检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单
价。
select a.prod_id,b.prod_name,a.qty,a.unit_price
from sale_item a,product b
where a.prod_id = b.prod_id
group by a.prod_id,b.prod_name,a.qty,a.unit_price
3、检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、
产品名称、数量、单价。
select a.prod_id,b.prod_name,a.qty,a.unit_price
from sale_item a,product b
where a.prod_id = b.prod_id and a.unit_price > 2400
group by a.prod_id,b.prod_name,a.qty,a.unit_price
4、查询在每张订单中订购金额超过24000元的客户名及其地址。
select b.cust_name,b.addr
from sales a,customer b
where tot_amt > 24000 and a.cust_id = b.cust_id
5、查找有销售记录的客户编号、名称和订单总额
select b.cust_id,b.cust_name,a.tot_amt
from sales a,customer b
where a.cust_id = b.cust_id
6、每位客户订购的每种产品的总数量及平均单价，并按客户号，产品号从小到
大排列。
select x.cust_id, x.prod_id,sum(qty) sum_qty,avg(unit_price) avg_price
from (select cust_id,prod_id,qty,unit_price
from sales a,sale_item b
where a.order_no = b.order_no) x
group by x.cust_id,x.prod_id
order by cust_id asc,prod_id asc
7、查找在1997年中有销售记录的客户编号、名称和订单总额
select a.cust_id,cust_name,tot_amt
from customer a,sales b
where a.cust_id = b.cust_id and year(b.order_date) = 1997
8、分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item
表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。
左外:
select product.prod_id,product.prod_name,qty,unit_price
from product left join sale_item on(product.prod_id = sale_item.prod_id and sale_item.unit_price > 2400)
右外:
select product.prod_id,product.prod_name,qty,unit_price
from product right join sale_item on(product.prod_id = sale_item.prod_id and sale_item.unit_price > 2400)
全外：
select product.prod_id,product.prod_name,qty,unit_price
from product full join sale_item on(product.prod_id = sale_item.prod_id and
sale_item.unit_price > 2400)
实验七
实验名称：嵌套查询
一、实验目的
掌握SELECT语句的嵌套使用，实现多表的复杂查询，进一步理解SELECT语句的高级使用方法。
二、实验原理
使用嵌套查询时，先用内查询（子查询）挑选出部分数据，以作为外查询（主查询）的数据来源或搜索条件。包含子查询的语句通常采用以下格式：
WHERE 表达式[NOT] IN （子查询）
WHERE 表达式比较运算符[ANY|ALL] （子查询）
WHERE [NOT] EXISTS （子查询）
其中前两种又称为不相关子查询，子查询的查询条件不依赖其父查询，所以可以先求出子查询的结果，然后由内到外逐层求解。最后一种为相关子查询，其子查询的查询条件依赖于外层父查询的某个属性值，所以不能先一次性地求出子查询的结果。
三、实验设备
安装有SQL SERVER 2000的计算机。
四、实验示例
1、由employee表中查找出薪水最高的员工信息。
select *
from employee
where salary=
(select max(salary )
from employee )
2、由sales表中查找出订单金额大于“E0013业务员在1996/10/15这天所接每一张订单的金额”的所有订单。