数据库原理实验六：综合应用

实验目的

根据数据库设计步骤完成简单应用的设计和创建，了解数据在前后台的交互。

实验内容

1、建立一个数据库和五张表的表结构。

2、根据表结构使用SQL语句添加相应约束。

（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())		操作者

create table employee
(
emp_no char(5) primary key,
emp_name char(10) not null,
Sex char(1) not null,
Dept char(4) not null,
Title char(6) not null,
data_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		邮政编码

create table customer
(
cust_id char(5) 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		发票号码

create table sales
(
order_no int 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,
incoice_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		订单日期

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 primary_sale primary key(order_no,prod_id)
)

（5）/*产品名称表product */

prod_id	char(5)	Not null	primary key	产品编号
prod_name	char(20)	Not null		产品名称

create table product
(
prod_id char(5) not null primary key,
prod_naem char(20) not null
)

3、录入数据并实现实现如下查询

（1）查找定单金额高于20000的客户编号；

select cust_id 
from sales 
where tot_amt>20000

（2）选取销售数量最多的前5条订单订单号、数量；

select top 5 order_no,Qty 
from sale_item 
order by Qty desc

（3）显示sale_item表中每种个别产品的订购金额总和，并且依据销售金额由大到小排

         来显示出每一种产品的排行榜；

select prod_id, sum(Qty*unit_price) '金额' 
from sale_item 
group by prod_id 
order by '金额' desc

（5）计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序；

select "s2".月份,sum("s2".tot_amt) '销售金额总和',"s1".prod_id '产品编号'
from sale_item "s1"
join (select month(order_date) '月份',order_no,tot_amt from sales) "s2"
on "s1".order_no="s2".order_no
group by "s2".月份,"s1".prod_id
order by "s2".月份,"s1".prod_id

（6）检索单价高于2400元的的产品编号、产品名称、数量、单价及所在订单号；

select s.prod_id, product.prod_name, s.Qty, s.unit_price, s.order_no 
from product,sale_item s
where s.unit_price> 2400 and product.prod_id=s.prod_id

（7）计算每一产品销售数量总和与平均销售单价；

select sum(Qty)'销售数量', avg(unit_price)'平均销售单价'
from sale_item

（8）创建一个视图，该视图只含上海客户信息，即客户号、客户姓名、住址。

create view view_name AS
select cust_id,cust_name,Addr from customer where Addr='上海'

实验总结

1. 设置主键，自动为 not null

2. unique和主键区别：
unique：唯一并且 一张表可设置多个unique 可空 但是只能有一行数据空
主键： 唯一并且 一张表只能有一个主键

3. 主键可通过 constraint 主键名 primary key（列，列）来设置组合键

4. 给表取别名的时候 不能用单引号，要用双引号或者不用引号
而给列取别名的时候可以选择单引号 或者 as 连接词 或者不用引号

5. 视图是为了保存一张表 下次查找该表 可直接 使用 如本实验中：

select * from view_name 

即可查看 视图