数据库实验.docx
《数据库实验.docx》由会员分享，可在线阅读，更多相关《数据库实验.docx（22页珍藏版）》请在冰点文库上搜索。
数据库实验
数据库原理实验指导
实验前准备：
请设计一个企业销售管理据库，其中需要保存的信息如下：
员工信息，包括：
员工编号、员工姓名、性别、所属部门、职称、到职日、生日、薪水、填表日期；
客户信息，包括：
客户号，客户名称，客户住址，客户电话、邮政编码；
产品信息，包括：
产品编号，产品名称；
员工和客户可以签订订单，每签订一个订单，就要保存订单信息，包括：
订单编号、客户号、业务员编号、订单金额、订货日期、出货日期、发票号码。
此外，每个订单可能涉及到多种产品，每种产品可能被多个订单订购。
因此需要每个订单中每类产品的销售明细，包括每种产品的销售数量、单价、订单日期；
要求：
（1）给出系统的ER图（可以用word或其它画图工具，如Visio画），要求画出所有的实体，联系，属性以及联系的类型；
（2）将ER图转换为关系模型；
实验一
实验名称：
数据定义（2课时）
一、实验目的
1、理解数据库模式的概念，通过使用SQLSERVER企业管理器或者MySql建立数据库和基本表。
模式为人事表、客户表、销售表、销售明细表、产品表。
熟悉SQLSERVER企业管理器的使用，并将得到的表生成脚本，然后保存。
2、理解上述基本表之间的关系，建立关系表。
3、掌握修改表结构的基本方法
4、掌握索引和视图的创建方法
二、实验环境
MSSQLSERVER或者MySql。
三、实验内容与步骤
1、建立一个数据库和五张表的表结构。
（1）/*员工人事表employee*/
emp_no
char（5）
Notnull
primarykey
员工编号
emp_name
char（10）
Notnull
员工姓名
sex
char
（1）
Notnull
性别
dept
char（4）
null
所属部门
title
char（6）
null
职称
date_hired
datetime
null
到职日
birthday
datetime
Null
生日
salary
int
null
薪水
addr
char（50）
null
住址
Mod_date
datetime
Default（getdate（））
操作日期
Createdatebasesale;
createtableemployee（
emp_nochar（5）Notnullprimarykey,
emp_namechar（10）Notnull,
sexchar
（1）Notnull,
deptchar（4）null,
titlechar（6）null,
date_hireddatetimenull,
birthdaydatetimenull,
salaryintnull,
addrchar（50）null,
Mod_datedatetimeDefault0,
constraintc1check（性别in（'男','女'））
）;
（2）/*客户表customer*/
cust_id
char（5）
Notnull
primarykey
客户号
cust_name
char（20）
Notnull,
客户名称
addr
char（40）
Notnull,
客户住址
tel_no
char（10）
Notnull,
客户电话
zip
char（6）
null
邮政编码
createTABLEcustomer（
cust_idchar（5）Notnullprimarykey,
cust_namechar（20）Notnull,
addrCHAR（40）Notnull,
tel_nochar（10）Notnull,
zipchar（6）null
）;
（3）/*销售主表sales*/
order_no
Char（5）
Notnull
primarykey
订单编号
cust_id
char（5）
Notnull,
客户号
sale_id
char（5）
Notnull,
业务员编号
tot_amt
numeric（9,2）
null,
默认0
订单金额
order_date
datetime
null,
订货日期
ship_date
datetime
null,
出货日期
invoice_no
char（10）
null
发票号码
createTABLEsales（
order_nochar（5）Notnullprimarykey,
cust_idchar（5）Notnull,
sale_idCHAR（5）Notnull,
tot_amtnumeric（9,2）null,
order_datedatetimenull,
ship_datedatetimenull,
invoice_nochar（10）null
）;
（4）/*销货明细表sales_item*/
order_no
Char（5）
Notnull,
primarykey
订单编号
prod_id
char（5）
Notnull,
产品编号
qty
int
Notnull
默认0
销售数量
unit_price
numeric（9,2）
Notnull
默认0
单价
order_date
datetime
null
订单日期
createTABLEsales_item（
order_nochar（5）Notnull,
prod_idchar（5）Notnull,
primarykey（order_no,prod_id）,
qtyINTNotnull,
unit_pricenumeric（9,2）Notnull,
order_datedatetimenull,
CONSTRAINTFK_1FOREIGNkey（order_no）REFERENCESsales（order_no）,
CONSTRAINTFK_2FOREIGNkey（prod_id）REFERENCESproduct（prod_id）
）;
（5）/*产品名称表product*/
prod_id
char（5）
Notnull
primarykey
产品编号
prod_name
char（20）
Notnull
产品名称
createTABLEproduct（
prod_idchar（5）Notnullprimarykey,
prod_namechar（20）Notnull
）;
2、建立5张表的关系图
3、修改表结构，通过SQL语句修改表约束。
①?
在表employee加入CHECK约束：
输入的员工编号必须以E开头的5位数编号，性别只能为M/F。
//check对于MySQL不起作用，只能在插入数据的基础上修改
usesale;
ALTERTABLEemployeeaddCHECK（sexLIKE'E%'）;
UPDATEemployee
SETsex='F'
WHEREsex='女'
;
UPDATEemployee
SETsex='M'
WHEREsex='男'
;
为销售主表sales中的发票编号字段建立UNIQUE约束。
ALTERTABLEsalesMODIFYinvoice_noCHAR（10）UNIQUENULL;
所有性别属性，限制取值为f或m
4、员工表建立唯一索引：
emp_no属性、升序；员工表建立聚集索引：
emp_name属性、升序；
CREATEUNIQUEINDEXemp_indexONemployee（emp_noASC）;
//MYSQL不支持此类语法
5、创建视图：
①?
视图只含上海客户信息，即客户号、客户姓名、住址。
CREATEVIEW上海客户信息AS
SELECTcust_id,cust_name,addr
FROMcustomer
WHEREaddrLIKE'上海%';
有两个基本表employee和sales，创建一个视图，该视图包含相同业务员的编号、姓名、订单号、销售总金额。
CREATEVIEWemp_salesAS
SELECTsale_id,emp_name,order_no,tot_amt
FROMemployee,sales
WHEREemployee.emp_no=sales.sale_id;
四、实验报告
实验二
实验名称：
数据操纵（4课时）
一．实验目的
1、要求学生熟练掌握添加、修改、删除数据的操作。
2、要求学生熟练掌握数据查询操作。
二、实验环境
MSSQLSERVER2000或者MySQL
三、实验内容与步骤
1.数据更新
①在每个表中插入若干条记录；
--employee
INSERTemployeeVALUES（'E0001','赵三','男','销售部','经理','2013/3/4','1992/3/4',8000,'杭州','2013/4/2'）;
INSERTintoemployeevalues（'E0002','赵四','M','销售部','成员','2017/3/5','1994/2/3',2500,'泰州','2018/4/2'）;
INSERTemployeeVALUES（'E0003','钱四','男','销售部','组长','2015/3/4','1991/3/4',8000,'杭州','2015/4/2'）;
INSERTemployeeVALUES（'E0004','钱行','男','后勤部','经理','2014/8/4','1981/9/8',10000,'杭州','2015/4/2'）;
INSERTemployeeVALUES（'E0005','欧阳泽明','男','后勤部','组长','2016/8/4','1986/7/8'7000,'杭州','2017/4/2'）;
INSERTemployeeVALUES（'E0006','欧阳凤','女','后勤部','成员','2012/5/4','1989/3/5',7000,'杭州','2016/4/2'）;
INSERTemployeeVALUES（'E0007','欧阳峰','男','宣传部','副经理','2013/5/4','1989/3/9',9000,'泰州','2016/4/2'）;
INSERTemployeeVALUES（'E0008','欧阳创正','男','宣传部','成员','2017/8/8','1999/7/25',4000,'泰州','2016/4/2'）;
INSERTemployeeVALUES（'E0009','王阳凤','女','宣传部','经理','2012/7/13','1985/3/6',9090,'杭州','2016/4/2'）;
INSERTemployeeVALUES（'E0010','王航','女','联络部','成员','2017/5/4','1997/8/15',4000,'扬州','2016/4/2'）;
INSERTemployeeVALUES（'E0011','王凤','女','联络部','经理','2015/7/13','1989/3/6',9090,'扬州','2016/4/2'）;
INSERTemployeeVALUES（'E0012','王立','男','联络部','副经理','2013/8/14','1989/5/19',9000,'泰州','2016/4/2'）;
--customer
--sales
--product
INSERTproductVALUES（'C0001','短袖'）;
INSERTproductVALUES（'C0002','短裤'）;
INSERTproductVALUES（'C0003','长袖'）;
INSERTproductVALUES（'C0004','牛仔裤'）;
INSERTproductVALUES（'C0005','七分裤'）;
INSERTproductVALUES（'C0006','五分裤'）;
INSERTproductVALUES（'C0007','外套'）;
INSERTproductVALUES（'C0008','短裙'）;
INSERTproductVALUES（'C0009','连衣裙'）;
INSERTproductVALUES（'C0010','衬衫'）;
--sales_item
INSERTsales_itemVALUES（'S0010','C0001',77,1,'2018/5/9'）;
INSERTsales_itemVALUES（'S0009','C0002',146,12,'2018/5/9'）;
INSERTsales_itemVALUES（'S0008','C0003',126,18,'2018/5/9'）;
INSERTsales_itemVALUES（'S0007','C0004',124,9,'2018/5/9'）;
INSERTsales_itemVALUES（'S0006','C0005',128,12,'2018/5/9'）;
INSERTsales_itemVALUES（'S0005','C0006',124,42,'2018/5/9'）;
INSERTsales_itemVALUES（'S0004','C0007',446,52,'2018/5/9'）;
INSERTsales_itemVALUES（'S0003','C0008',846,12,'2018/5/9'）;
INSERTsales_itemVALUES（'S0002','C0009',1277,22,'2018/5/9'）;
INSERTsales_itemVALUES（'S0001','C0010',2240,30,'2018/5/9'）;
将所有员工的薪水增加100；
UPDATEemployeeSETsalary=salary+100;
将产品名称为'A'的产品的单价改为10//A为短袖
UPDATEsales_item
SETunit_price=10
WHEREprod_id=（
SELECTprod_id
FROMproduct
WHEREprod_name='短袖'）;
删除所有女性员工销售记录；
DELETEsales_item
FROMsales
LEFTJOINemployeeONsales.sale_id=employee.emp_no
INNERJOINsales_itemONsales.order_no=sales_item.order_no
WHEREsex='f';
DELETEsales
FROMsales
LEFTJOINemployeeONsales.sale_id=employee.emp_no
WHEREsex='f';
删除订单金额小于100000的订单。
//小于6000的订单
DELETEFROMsales_item
WHEREorder_noIN（
SELECTorder_no
FROMsales
WHEREtot_amt<6000）;
DELETEFROMsales
WHEREtot_amt<6000;
删除sales表中作废的订单（其发票号码为I000000004）。
（注意：
约束）
DELETEFROMsales_item
WHEREorder_noIN（
SELECTorder_no
FROMsales
DELETEFROMsales
2.数据查询
（1）查找所有经理的姓名、职称、薪水。
SELECTemp_name,title,salary
FROMemployee
WHEREtitle='经理';
（2）找出姓“王”并且姓名的最后一个字为“功”的员工。
//最后一个字为凤
SELECTemp_name
FROMemployee
WHEREemp_nameLIKE'王%'ANDemp_nameREGEXP'凤$';
（3）查找住在上海或北京的女员工，并显示其姓名、所属部门、职称、住址。
INSERTemployeeVALUES（'E0013','王张','f','后勤部','成员','2013/8/14','1989/2/11',5600,'北京','2016/4/2'）;
INSERTemployeeVALUES（'E0014','顾郝康','f','销售部','成员','2013/8/14','1989/5/21',6000,'上海','2016/4/2'）;
//插入两条数据
SELECTemp_name,dept,title,addr
FROMemployee
WHEREsex='f'AND（addr='上海'ORaddr='北京'）;
（4）在表sales中挑出销售金额大于等于10000元订单//大于7000元订单
SELECT*
FROMsales
WHEREtot_amt>7000;
（5）选取订单金额最高的前10%的订单数据。
//已恢复原始删除数据
SELECT*
FROMsales
ORDERBYtot_amt
LIMIT0,（（SELECTCOUNT（*）FROMsales）*0.2）;
（6）查找出职称为经理或职称为职员的女员工的信息。
//成员
SELECT*
FROMemployee
WHEREsex='f'AND（title='经理'ORtitle='成员'）;
（7）计算出一共销售了几种产品。
SELECTCOUNT（DISTINCTprod_id）
FROMsales_item;
（8）显示sale_item表中每种个别产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。
SELECTprod_id,SUM（qty*unit_price）ASsale_money
FROMsales_item
GROUPBYprod_id
ORDERBYsale_money;
（9）计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。
SELECTprod_id,SUM（qty*unit_price）ASsale_money,order_date
FROMsales_item
GROUPBYprod_id
ORDERBYorder_dateDESC,prod_idDESC;
（10）由sales表中查找出销售金额最高的订单。
SELECTMAX（tot_amt）
FROMsales;
（11）由sales表中查找出订单金额大于“E0013业务员在1996/10/15这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。
//E0008，2018-05-24
SELECTsale_id,tot_amt
FROMsales
WHEREtot_amt>（
SELECTtot_amt
FROMsales
WHEREsale_id='E0008'ANDorder_date='2018-05-24'）;
（12）找出公司女业务员所接的订单。
SELECTemp_name,sex,sale_id,order_no,tot_amt
FROMsales,employee
WHEREsex='f'ANDsales.sale_id=employee.emp_no;
（13）找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。
INSERTemployeeVALUES（'E0015','王立','f','销售部','成员','2015/8/14','1989/5/21',5000,'上海','2016/4/2'）;//已插入姓名相同的员工
SELECT*
FROMemployeea,employeeb
WHEREa.emp_name=b.emp_nameANDa.emp_no<>b.emp_no;
（14）找出目前业绩未超过20000元的员工。
//8000元
SELECTemp_no,emp_name
FROMemployee
WHEREemp_noIN（
SELECTsale_id
FROMsales
WHEREtot_amt<8000）;
（15）计算公司内各个部门的工资支出总和。
SELECTdept,SUM（salary）
FROMemployee
GROUPBYdept;
（16）计算每一产品销售数量总和与平均销售单价。
SELECTprod_id,SUM（qty）,avg（unit_price）
FROMsales_item
GROUPBYprod_id;
四、实验报告
实验三
实验名称：
T-SQL编程（2课时）
一、实验目的
1、掌握T-SQL编程方法。
2、掌握触发器、存储过程创建方法和原理
二、实验环境
MSSQLSERVER2000以上版本
三、实验内容与步骤
（1）T-SQL的流程控制语句使用
编写程序完成以下功能，在查询分析器中执行程序，并记录结果。
●在employee表中求某个部门年龄最大和最小的员工的信息，包括：
编号，姓名，年龄。
//每个部门
SELECTdept,emp_no,emp_name,MAX（2018-YEAR（birthday））最大年龄
FROMemployee
GROUPBYdept;
SELECTdept,emp_no,emp_name,MIN（2018-YEAR（birthday））最小年龄
FROMemployee
GROUPBYdept;
●在employee表中先插入三条新记录，其中的dept字段的值为NULL，要求对记录进行查询时，对应的NULL值在显示时显示为“未分配”
SELECTemp_no,emp_name,
（CASE
WHENdept=''THEN'未分配'
ELSEdept
END）AS分配情况
FROMemployee;
●查询employee表中年龄，并根据年龄输出所属阶段（青年、中年、老年）。
SELECTemp_n
升级会员 