数据库-实验报告.doc

数据库原理实验指导

实验一 1
实验二 2
实验三 3
实验四 4
实验五 5
实验六 6
实验七 6
实验八 7

实验一
实验名称：建立数据库和基本表结构（2课时）
一、实验目的
理解数据库模式的概念，通过使用SQL SERVER企业管理器或者My Sql建立数据库和基本表。模式为人事表、客户表、销售表、销售明细表、产品表。熟悉SQL SERVER企业管理器的使用，并将得到的表生成脚本，然后保存。
另外，帮助学生理解上述基本表之间的关系，从而正确、合理地建立表结构，并输入相应记录信息。
二、实验环境
MS SQL SERVER或者My Sql。
三、实验示例
1、 使用SQL建立数据库。
例如：create database studentdb

2、 使用SQL脚本建立基本表结构。
例如：
create table employee1(
emp_no char(5) not null,
emp_name char(10) not null,
emp_sex char(1) not null,
emp_phone char (13) not null,
emp_add varchar(60) not null,
emp_salary char(5) not null,
)
四、实验内容与步骤
建立一个数据库和五张表的表结构。
（1）/*员工人事表employee */
emp_no
char(5)
Not null
primary key
员工编号
emp_name
char(10)
Not null

员工姓名
sex
char(1)
Not null

性别
dept
char(4)
Not null

所属部门
title
char(6)
Not null

职称
date_hired
datetime
Not null

到职日
birthday
datetime
Null

生日
salary
int
Not null

薪水
addr
char(50)
null

住址
Mod_date
datetime
Default(getdate())
操作者

（2）/*客户表customer */
cust_id
char(5)
Not null
primary key
客户号
cust_name
char(20)
Not null,

客户名称
addr
char(40)
Not null,

客户住址
tel_no
char(10)
Not null,

客户电话
zip
char(6)
null

邮政编码

（3）/*销售主表sales */
order_no
int
Not null
primary key
订单编号
cust_id
char(5)
Not null,

客户号
sale_id
char(5)
Not null,

业务员编号
tot_amt
numeric(9,2)
Not null,

订单金额
order_date
datetime
Not null,

订货日期
ship_date
datetime
Not null,

出货日期
invoice_no
char(10)
Not null

发票号码

（4）/*销货明细表sale_item */
order_no
int
Not null,
primary key
订单编号
prod_id
char(5)
Not null,
产品编号
qty
int
Not null

销售数量
unit_price
numeric(9,2)
Not null

单价
order_date
datetime
null

订单日期

（5）/*产品名称表product */
prod_id
char(5)
Not null
primary key
产品编号
prod_name
char(20)
Not null

产品名称
五、实验报告
create database studentdb

create table employee(
emp_no char(5) not null primary key,
emp_name char(10) not null,
sex char(1) not null,
dept char(4) not null,
title char(6) not null,
date_hired datetime not null,
birthday datetime null,
salary int not null,
addr char(50) null,
Mod_date datetime default(getdate())
)

create table customer(
cust_id char(5) not null primary key,
cust_name char(20) not null,
addr char(40) not null,
tel_no char(10) not null,
zip char(6) null
)

create table sales(
order_no int not null primary key,
cust_id char(5) not null,
sale_id char(5) not null,
tot_amt numeric(9,2) not null,
order_date datetime not null,
ship_date datetime not null,
invoice_no char(10) not null
)

create table sale_item(
order_no int not null,
prod_id char(5) not null,
qty int not null,
unit_price numeric(9,2) not null,
order_date datetime null
constraint SaleItemPrimary primary key (order_no, prod_id)
)

create table product(
prod_id char(5) not null primary key,
prod_name char(20) not null
)
实验二
实验名称：添加表记录（2课时）
一、实验目的
掌握修改表结构的基本方法，熟练使用INSERT语句为每张表追加表记录。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
例如：
insert into employee
(emp_no,emp_name,sex,dept,title,date_hired,salary)
values('E0025','彭江','M','业务','职员',1900/01/21,43200)
四、实验内容与步骤
初步理解表之间的关系，用insert命令输入有效记录（每张表至少需要5条记录）。
五、实验报告
insert into employee
(emp_no,emp_name,sex,dept,title,date_hired,salary)
values ('E0001','张三','M','业务','职员','2000/01/01',11000)

insert into employee
(emp_no,emp_name,sex,dept,title,date_hired,salary)
values ('E0002','李四','F','人事','部长','2000/01/02',12000)

insert into employee
(emp_no,emp_name,sex,dept,title,date_hired,salary)
values ('E0003','王功','M','市场','总监','2000/01/03',13000)

insert into employee
(emp_no,emp_name,sex,dept,title,date_hired,salary)
values ('E0004','刘民','F','开发','主管','2000/01/04',14000)

insert into employee
(emp_no,emp_name,sex,dept,title,date_hired,salary)
values ('E0005','高尚','M','管理','经理','2000/01/05',15000)

insert into customer
(cust_id,cust_name,addr,tel_no)
values('C0001','刘一','上海','11111111')

insert into customer
(cust_id,cust_name,addr,tel_no)
values('C0002','孙二','上海','22222222')

insert into customer
(cust_id,cust_name,addr,tel_no)
values('C0003','张三','上海','33333333')

insert into customer

(cust_id,cust_name,addr,tel_no)
values('C0004','李四','上海','44444444')

insert into customer
(cust_id,cust_name,addr,tel_no)
values('C0005','王五','上海','55555555')

insert into sales
(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)
values(1,'C0001','E0001',5000,'2010/10/01','2010/11/01','I000000001')

insert into sales
(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)
values(2,'C0002','E0002',8000, '2010/10/02','2010/11/02','I000000002')

insert into sales
(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)
values(3,'C0003','E0003',10000, '2010/10/03','2010/11/03','I000000003')

insert into sales
(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)
values(4,'C0004','E0004',12000, '2010/10/04','2010/11/04','I000000004')

insert into sales
(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)
values(5,'C0005','E0005',15000, '2010/10/05','2010/11/05','I000000005')

insert into sale_item
(order_no,prod_id,qty,unit_price, order_date)
values(1,'P0001',110,1000,'2010/10/01')

insert into sale_item
(order_no,prod_id,qty,unit_price, order_date)
values(2,'P0002',120,2000,'2010/10/02')

insert into sale_item
(order_no,prod_id,qty,unit_price, order_date)
values(3,'P0003',130,3000,'2010/10/03')

insert into sale_item
(order_no,prod_id,qty,unit_price, order_date)
values(4,'P0004',140,2000,'2010/10/04')

insert into sale_item
(order_no,prod_id,qty,unit_price, order_date)
values(5,'P0005',150,1000,'2010/10/05')

insert into product
(prod_id,prod_name)
values('P0001','零件1')

insert into product
(prod_id,prod_name)
values('P0002','零件2')

insert into product
(prod_id,prod_name)
values('P0003','零件3')

insert into product
(prod_id,prod_name)
values('P0004','零件4')

insert into product
(prod_id,prod_name)
values('P0005','零件5')

实验三
实验名称： 数据库的更新实验（2课时）
一． 实验目的
本实验的目的是要求学生熟练掌握修改数据和删除数据的操作。

二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
1. 修改操作语句的一般格式为
UPDATE 表名
SET <列名>=<表达式>[,<列名>=<表达式>]……
[where <条件>];
其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句用于指定修改方法，即用<表达式>的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。

2. 删除语句的一般格式为
DELETE
FROM <表名>
[WHERE <条件>];
其功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，则表示要删除表中的所有元组，但表的定义仍在字典中。

四、实验内容与步骤

1、 将员工张三的薪水改为10000。
2、 将所有员工的薪水增加100。
3、 删除所有女性员工的记录。
4、 删除订单金额小于100000的订单。

五、实验报告
1.
update employee
set salary=10000
where emp_name='张三'

2.
update employee
set salary=salary+100

3.
delete from employee
where sex='F'

4.
delete from sales
where tot_amt<10000

实验四
实验名称：表的完整性约束（2课时）
一、实验目的
理解并掌握关系的完整性约束，熟练使用T-SQL语句为每张表添加表约束。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
1、
create table orders
(
order_id char(8),
p_id char(8),
p_name char(10) ,
constraint pk_order_id primary key (order_id) ,
foreign key (p_id, p_name)
References products(p_id, p_name)
)
2、
create table employee1(
emp_no char(5) not null,
emp_name char(10) not null,
emp_sex char(1) not null,
constraint emp_sexchk check(emp_sex in ('m','f')),
emp_phone char (13) not null,
emp_add varchar(60) not null,
emp_salary char(5) not null,
constraint emp_salarychk check (emp_salary between 60000 and 80000)
)
3、
create table employees
(
emp_id char(8),
emp_name char(10) ,
emp_cardid char(18),
constraint pk_emp_id primary key (emp_id),
constraint uk_emp_cardid unique (emp_cardid)
)

四、实验内容与步骤
通过SQL语句修改表约束。
①分别为每张表建立主键约束。
②为相关表建立外键约束。
③ 在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。
④为销售主表sales中的发票编号字段建立UNIQUE约束。

五、实验报告
2.
alter table sales
add constraint SalesForeign1 foreign key (cust_id) references customer(cust_id)

alter table sales
add constraint SalesForeign2 foreign key (sale_id) references employee(emp_no)

alter table sale_item
add constraint SaleItemForeign1 foreign key (prod_id) references product(prod_id)

alter table sale_item
add constraint SaleItemForeign2 foreign key (order_no) references sales(order_no)
3.
alter table employee
add constraint EmployeeCheck1 check (emp_no like 'E____')

alter table employee
add constraint EmployeeCheck2 check (sex in ('M','F'))
4.
alter table sales
add constraint SalesUnique1 unique (invoice_no)

实验五
实验名称：简单查询（2课时）
一、实验目的
理解SELECT语句的操作和基本使用方法，掌握简单查询。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
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_name,title,sex
from employee
where title='经理'or title='职员'and sex='F'
4、选取销售数量最多的前5条订单订单号、数量。
select top 5 with ties order_no,sup_id,qty
from sale_item
order by qty desc
5、按部门进行汇总，统计每个部门的总工资
select dept,sum(salary)
from employee
group by dept
四、实验内容与步骤
1、 查找所有经理的姓名、职称、薪水。
2、 查找出姓“王”并且姓名的最后一个字为“功”的员工。
3、 查找住在上海或北京的女员工，并显示其姓名、所属部门、职称、住址。
4、 在表sales中挑出销售金额大于等于10000元订单。
5、 选取订单金额最高的前10%的订单数据。
6、 查找出职称为经理或职称为职员的女员工的信息。
7、 删除sales表中作废的订单（其发票号码为I000000004）。（注意：约束）
8、计算出一共销售了几种产品。
9、显示sale_item表中每种个别产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。
10、计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。
五、实验报告
1.
select emp_name 姓名,title 职称,salary 薪水
from employee
where title='经理'

2.
select *
from employee
where emp_name like '王%功'

3.
select emp_name 姓名,dept 所属部门,title 职称,addr 住址
from employee
where addr='上海' or addr='北京'

4.
select *
from sales
where tot_amt>=10000

5.
select top 10 percent *
from sales
order by tot_amt desc

6.
select *
from employee
where (title='经理' or title='职员') and sex='F'

7.
delete from sale_item
where order_no=(
select order_no
from sales
where invoice_no='I000000004')

delete from sales
where invoice_no='I000000004'

8.
select count(distinct prod_id) 销售产品数
from sale_item

9.
select prod_name 产品名称, qty*unit_price 金额总和
from sale_item, product
where sale_item.prod_id=product.prod_id
order by qty*unit_price desc

10.
select prod_id 产品号, datepart(m,order_date) 月份, sum(qty*unit_price) 总额
from sale_item
group by prod_id, datepart(m,order_date)
order by datepart(m,order_date)

实验六
实验名称：表连接查询JOIN（2课时）
一、实验目的
理解JOIN语句的操作和基本使用方法，掌握内连接、外连接、自连接的概念和使用。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
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)
四、实验内容与步骤
1、 查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。
2、 检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。
3、 检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。
4、 分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。
五、实验报告
1.
select distinct A.emp_name 姓名, A.sex 性别, A.title 职称, A.salary 薪水, A.addr 住址
from employee A, employee B
where A.sex='F' and A.emp_no!=B.emp_no and A.dept=B.dept and A.addr=B.addr

2.
select product.prod_id 产品编号, product.prod_name 产品名称, sale_item.qty 数量, sale_item.unit_price 单价
from product, sale_item
where product.prod_id=sale_item.prod_id

3.
select product.prod_id 产品编号, product.prod_name 产品名称, sale_item.qty 数量, sale_item.unit_price 单价
from product, sale_item
where product.prod_id=sale_item.prod_id and sale_item.unit_price>2400

4.
select product.prod_id 产品编号, product.prod_name 产品名称, sale_item.qty 数量, sale_item.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 产品名称, sale_item.qty 数量, sale_item.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 产品名称, sale_item.qty 数量, sale_item.unit_price 单价
from product full join sale_item on product.prod_id=sale_item.prod_id and sale_item.unit_price>2400

实验七
实验名称：嵌套查询（2课时）
一、实验目的
掌握SELECT语句的嵌套使用，实现表的复杂查询，进一步理解SELECT语句的高级使用方法。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
1、由employee表中查找出薪水最高的员工信息。
select *
from employee
where salary
(select max(salary )
from employee )
2、由sales表中查找出订单金额大于“E0013业务员在1996/10/15这天所接每一张订单的金额”的所有订单。
select *
from sales
where tot_amt>all
(select tot_amt
from sales
where sale_id='E0013'and order_date='1996/10/15')
order by tot_amt

四、实验内容与步骤
1、 由sales表中查找出销售金额最高的订单。
2、 由sales表中查找出订单金额大于“E0013业务员在1996/10/15这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。
3、 找出公司女业务员所接的订单。
4、 找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。
5、 找出目前业绩未超过20000元的员工。
6、 计算公司内各个部门的工资支出总和。
7、 计算每一产品销售数量总和与平均销售单价。
五、实验报告
1.
select *
from sales
where tot_amt=(
select max(tot_amt)
from sales)

2.
select emp_name 业务员, tot_amt 订单金额
from sales join employee on sales.sale_id=employee.emp_no
where tot_amt>any(
select tot_amt
from sales
where sale_id='E0003' and order_date='2010/10/03')

3.
select *
from sales
where sale_id in(
select emp_no
from employee

where sex='F')

4.
select distinct A.*
from employee A
where A.emp_name in(
select B.emp_name
from employee B
where A.emp_no!=B.emp_no)
order by A.emp_no

5.
select sale_id 员工号, sum(tot_amt) 业绩
from sales
group by sale_id having sum(tot_amt)<10000

6.
select dept 部门, sum(salary) 工资支出
from employee
group by dept

7.
select prod_id 产品号, sum(qty) 销售数量总和, avg(unit_price) 平均单价
from sale_item
group by prod_id

实验八
实验名称：视图的建立与使用（2课时）
一、实验目的
掌握视图的定义与工作原理。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
1、
create view emp_view as
select emp_no,emp_name,salary

from employee
where sex='f'
2、
create view sale_item_view as
select sales.order_no,sales.order_date,prod_id,qty
from sales,sale_item
where sales.order_no=sale_item.order_no

/*注意：插入数据时要考虑数据列是否有默认值或允许为空，否则INSERT失败。*/
insert into sale_item_view(order_no,order_date)
values(10010,'1996/12/20')

insert into sale_item_view(prod_id,qty)
values('p0010',8)
四、实验内容与步骤
1、 创建一个视图，该视图只含上海客户信息，即客户号、客户姓名、住址。
2、 对视图添加一条记录数据。（注意：分别查看customer表和该视图的结果。）
3、 删除视图中所有姓“王”的客户数据。
4、 通过视图修改表内某一客户的姓名。
5、 有两个基本表employee和sales，创建一个视图，该视图包含相同业务员的编号、姓名、订单号、销售总金额。
6、 将上述视图中订单号为10001的记录的销售总金额改为60000。
7、 给上述视图添加一条记录数据。
8、 删除上述视图。
五、实验报告
1.
create view customer_view as
select cust_id, cust_name, addr
from customer
where addr='上海'

2.
insert into customer_view
(cust_id, cust_name, addr)
values('C0006', '周六', '北京')
/*此操作失败：customer表中包含tel_no，且非空。而视图中不包含此字段，故无法插入。*/

3.
delete from sal