1:创建数据库及日志文件
create database 销售信息管理系统 on (
name='SaleInfoDataBase',
filename='c:SaleInfoDataBase.mdf',
size=3MB,
maxsize=20MB,
filegrowth=3MB
)
log on
(
name='SaleInfoDataBase_Log',
filename='c:SaleInfoDataBase_Log.ldf',
size=2MB,
maxsize=20MB,
filegrowth=10%
)
2:建表
use 销售信息管理系统
Go
create table saler(
id char(10) primary key,
name varchar(10),
phone char(11) unique,
saddr varchar(50)
);
create table product(
id char(10) primary key,
name varchar(20),
addr varchar(30),
price numeric(8,2),
number int default 0
);
create table sp(
sid char(10)foreign key references saler(id),
pid char(10)foreign key references product(id),
sptime date ,
spnumber int default 0,
primary key(sid,pid)
);
create table summarize(
sid char(10) foreign key references saler(id),
sumnumber int,
maxnumber int,
minnumber int,
avgnumber int
);
3：插入数据
use 销售信息管理系统
Go
insert into saler values('2015010702','张三','15073207321','湖南省长沙市长沙县1号'),('2018052402','李四','15073207322','湖南省长沙市长沙县2号'),('2018052403','王五','15073207323','湖南省长沙市长沙县3号');
insert into product values('2017010101','矿泉水',2.00,100),('2017010102','餐巾纸',1.00,200),('2017010103','雨伞',20.00,50);
insert into sp values('2015010702','2017010101','2017-03-02',12),('2018052402','2017010102','2017-03-05',8),('2018052403','2017010103','2017-03-03',5)
insert into summarize (sid,sumnumber,maxnumber,minnumber,avgnumber)select sid,SUM(spnumber),MAX(spnumber),MIN(spnumber),AVG(spnumber)from sp group by sid
4：创建视图
create view getsumnumber as select SUM(spnumber)as sumnumber from sp
5:查询语句
查询所有销售人员信息
Select * from saler
查询所有产品语句
Select * from product
查询所有销售记录语句
Select * from sp
查询所有销售总结语句
Select * from summarize
查询销售业绩最高的销售员
select top 1 sid as 员工号,SUM(spnumber) as 销售量 from sp group by sid
查询销售业绩最高的销售员
select top 1 sid as 员工号,SUM(spnumber) as 销售量 from sp group by sid order by 销售量 ASC
查询销售业绩最高的产品
select top 1 pid as 员工号,SUM(spnumber) as 销售量 from sp group by pid
查询销售业绩最高的产品
select top 1 pid as 员工号,SUM(spnumber) as 销售量 from sp group by pid order by 销售量 ASC