实验目的
根据数据库设计步骤完成简单应用的设计和创建,了解数据在前后台的交互。
实验内容
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
即可查看 视图