欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-21 20:59:27
用sqlserver开发一个项目的实战分享 --实训大作业(sale数据库) --单元5(创建数据库) --创建名为sale的销售数据库。该数据表有一个名为sale.mdf的主数据文件和...

用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)+'种产品'