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