用SQLSERVER开发一个项目的实战分享

用sqlserver开发一个项目的实战分享

--实训大作业（sale数据库）

--单元5（创建数据库）
--创建名为sale的销售数据库。该数据表有一个名为sale.mdf的主数据文件和名字为sale_log.ldf的事务日志文件。
--主数据文件容量为4mb，事务日志文件容量为10mb，数据文件和日志文件的最大容量为20mb，文件增量为1mb。
use master
go
create database sale
on
primary
(name = sale,
 filename = 'd:\sale.mdf',
 size = 5mb,
 maxsize = 20mb,
 filegrowth = 1mb)
log on
(name = sale_log,
 filename = 'd:\sale_log.ldf',
 size = 10mb,
 maxsize = 20mb,
 filegrowth = 1mb
)
go


--单元6（创建数据表）
--创建客户信息表customer
use sale
go
create table customer
(cusno nvarchar(3) not null,
 cusname nvarchar(10) not null,
 address nvarchar(20) null,
 tel nvarchar(20) null
)
go

--创建产品表信息product
use sale
go
create table product
(prono nvarchar(5) not null,
 proname nvarchar(20) not null,
 price decimal(8,2) not null,
 stocks decimal(8,0) not null
)
go

--创建入库表信息proin
use sale
go
create table proin
(inputdate datetime not null,
 prono nvarchar(5) not null,
 quantity decimal(6,0) not null
)
go

--创建销售表proout结构
use sale
go
create table proout
(saledate datetime not null,
 cusno nvarchar(3) not null,
 prono nvarchar(5) not null,
 quantity decimal(6,0) not null
)
go

--创建数据表数据
--创建customer表数据
 use sale
 go
 insert into customer
 select '001','杨婷','深圳','0755-22221111'
 union
 select '002','陈萍','深圳','0755-22223333'
 union
 select '003','李东','深圳','0755-22225555'
 union
 select '004','叶合','深圳','0755-22227777'
 union
 select '005','谭新','深圳','0755-22229999'
 go

 --创建product表数据
 use sale
 go
 insert into product
 select '00001','电视','3000.00','800'
 union
 select '00002','空调','2000.00','500'
 union
 select '00003','床','1000.00','300'
 union
 select '00004','餐桌','1500.00','200'
 union
 select '00005','音响','5000.00','600'
 union
 select '00006','沙发','6000.00','100'
 go

 --创建proln数据
 use sale
 go
 insert into proin
 select '2006-1-1','00001','10'
 union
 select '2006-1-1','00002','5'
 union
 select '2006-1-2','00001','5'
 union
 select '2006-1-2','00003','10'
 union
 select '2006-1-3','00001','10'
 union
 select '2006-2-1','00003','20'
 union
 select '2006-2-2','00001','10'
 union
 select '2006-2-3','00004','30'
 union
 select '2006-2-3','00003','20'
 go

 --创建proout数据
 use sale
 go
 insert into proout
 select '2016-1-1','001','00001','10'
 union
 select '2016-1-3','001','00001','5'
 union
 select '2016-1-3','001','00001','5'
 union
 select '2016-2-1','001','00001','10'
 union
 select '2016-2-2','001','00001','10'
 union
 select '2016-2-3','001','00001','20'
 union
 select '2016-3-2','001','00001','10'
 union
 select '2016-3-2','001','00001','30'
 union
 select '2016-3-3','001','00001','20'
 go
 


 --单元7（实施数据完整性）
--使用alter table语句为sale数据库中增加主键和外键约束
use sale
go
alter table customer
add constraint pk_customer primary key(cusno)
go
alter table product
add constraint pk_product primary key(prono)
go
alter table proin
add constraint fk_proin_product foreign key(prono)
references product (prono)
go
alter table proout
add constraint pk_proout_customer foreign key (cusno)
references customer(cusno)
go
alter table proout
add constraint pk_proout_product foreign key (prono)
references product(prono)
go

--约束客户表customer的cusno列长度为3，产品表product的prono列值长度为5
use sale
go
alter table customer
add constraint ck_customer_cusno check(len(cusno)=3)
go
alter table product
add constraint ck_product_prono check(len(prono)=5)
go

--对产品表product的stocks列、price列、入库表proin的quantity列、销售表proout的quantity列值进行约束，使其值必须大于0
use sale
go
alter table product
add constraint ck_product_stocks check(price>0)
go
alter table proin
add constraint ck_proin_quantity check(quantity>0)
go
alter table proout
add constraint ck_proout_quantity check(quantity>0)
go

--对销售表proout的saledate列进行约束，当不输入值时，系统默认其值为系统当前日期。
use sale
go
create default currentdate
as getdate()
go
exec sp_bindefault currentdate,'proout.saledate'
go



--单元8（实现索引）
--用户按照cusname(客户姓名)查询客户信息，希望提高查询速度。
use sale
go
create index ix_customer
on customer(cusname)
go

--用户按照proname(产品名称)查询客户信息，希望提高查询速度。
use sale
go
create index ix_product
on product(proname)
go

--用户按照saledate(销售日期)查询客户信息，希望提高查询速度。
use sale
go
create index ix_saledate
on proout(saledate)
go



--单元9（t-sql语言编程基础）
use sale
go
declare @sum int
set @sum = (select count(*) from product)
print '共有'+convert(char(2),@sum)+'种产品'