--建立"商品管理数据库"数据库--

create database 商品管理数据库

on(name='商品管理数据库_m',

filename='D:\商品管理系统\商品管理数据库_m.mdf',

size=6mb,filegrowth=1mb,maxsize=unlimited)

log on(name='商品管理数据库_l',

filename='D:\商品管理系统\商品管理数据库_l.ldf',

size=1,filegrowth=10%,maxsize=3)

go

use 商品管理数据库

--建立"客户信息表"数据表--

create table 客户信息表

(客户编号 nchar(8)not null primary key,

客户姓名 nvarchar(5)not null,

联系电话 nvarchar(11)not null,

地址 nvarchar(30)not null,

邮箱 nvarchar(20)null)

--为"客户信息表"的"联系电话"设置唯一约束--

alter table 客户信息表

add constraint UN_客户信息表_联系电话

unique (联系电话)

--为"客户信息表"的"地址"设置默认约束--

alter table 客户信息表

add constraint DF_客户信息表_地址

default '辽宁沈阳'for 地址

--为"客户信息表"的"邮箱"设置检查约束--

alter table 客户信息表

add constraint CK_客户信息表_邮箱

check (邮箱 like '_%@_%._%')

--建立"商品类型表"数据表--

create table 商品类型表

(商品类型编号 nchar(6)not null primary key,

商品类型名 nvarchar(10)not null)

--建立"商品信息表"数据表--

create table 商品信息表

(商品编号 nchar(8)not null primary key,

商品类型编号 nchar(6)not null,

商品名称 nvarchar(20)not null,

商品单位 nchar(2)not null,

产地 nvarchar(30)not null)

--为"商品信息表"的"商品类型编号"设置外键约束--

alter table 商品信息表

add constraint FK_商品类型表_商品信息表_商品类型编号

foreign key (商品类型编号)

references 商品类型表(商品类型编号)

--为"商品信息表"的"商品单位"设置默认约束--

alter table 商品信息表

add constraint DF_商品信息表_商品单位

default '个'for 商品单位

--为"商品信息表"的"产地"设置默认约束--

alter table 商品信息表

add constraint DF_商品信息表_产地

default '辽宁沈阳'for 产地

--建立"进货信息表"数据表--

create table 进货信息表

(进货编号 int not null primary key,

商品编号 nchar(8)not null,

进货单价 decimal(6,2)not null,

进货数量 int not null,

进货金额 decimal,

进货日期 date not null)

--为"进货信息表"的"商品编号"设置外键约束--

alter table 进货信息表

add constraint FK_商品信息表_进货信息表_商品编号

foreign key (商品编号)

references 商品信息表(商品编号)

--为"进货信息表"的"进货单价"设置检查约束--

alter table 进货信息表

add constraint CK_进货信息表_进货单价

check (进货单价>=0)

--为"进货信息表"的"进货数量"设置检查约束--

alter table 进货信息表

add constraint CK_进货信息表_进货数量

check (进货数量>0)

--为"进货信息表"的"进货金额"设置？？约束--

--为"进货信息表"的"进货日期"设置默认约束--

alter table 进货信息表

add constraint DF_进货信息表_进货日期

default getdate()for 进货日期

--建立"销售信息表"数据表--

create table 销售信息表

(销售编号 int not null primary key,

商品编号 nchar(8)not null,

销售单价 decimal(6,2)not null,

销售数量 int not null,

销售金额 decimal,

销售日期 date not null,

客户编号 nchar(8)not null)

--为"销售信息表"的"商品编号"设置外键约束--

alter table 销售信息表

add constraint FK_商品信息表_销售信息表_商品编号

foreign key (商品编号)

references 商品信息表(商品编号)

--为"销售信息表"的"销售单价"设置检查约束--

alter table 销售信息表

add constraint CK_销售信息表_销售单价

check (销售单价>=0)

--为"销售信息表"的"销售数量"设置检查约束--

alter table 销售信息表

add constraint CK_销售信息表_销售数量

check (销售数量>0)

--为"销售信息表"的"销售金额"设置？？约束--

--为"销售信息表"的"销售日期"设置默认约束--

alter table 销售信息表

add constraint DF_销售信息表_销售日期

default getdate()for 销售日期

--为"销售信息表"的"客户编号"设置外键约束--

alter table 销售信息表

add constraint FK_客户信息表_销售信息表_客户编号

foreign key (客户编号)

references 客户信息表(客户编号)

--建立"库存信息表"数据表--

create table 库存信息表

(库存编号 int not null,

商品编号 nchar(8)not null,

库存数量 int not null)

--为"库存信息表"的"商品编号"设置外键约束--

alter table 库存信息表

add constraint FK_商品信息表_库存信息表_商品编号

foreign key (商品编号)

references 商品信息表(商品编号)

--为"库存信息表"的"库存数量"设置检查约束--

alter table 库存信息表

add constraint CK_库存信息表_库存数量

check (库存数量>=0)

--添加表数据--

--添加"客户信息表"数据--

insert 客户信息表 values('','张峰','','辽宁沈阳','zhf@163.com')

insert 客户信息表 values('','赵小天','','辽宁大连','zxt@163.com')

insert 客户信息表 values('','钱成','','辽宁锦州','qc@163.com')

insert 客户信息表 values('','孙飞','','辽宁沈阳','sf@163.com')

insert 客户信息表 values('','李小明','','辽宁盘锦','lxm@163.com')

insert 客户信息表 values('','周笑','','辽宁大连','zx@163.com')

--把查询到"客户信息表"的表数据生成(添加到)一张新数据表"客户信息表01"--

select * into 客户信息表01 from 客户信息表

--把查询到"客户信息表"的表数据添加到一张已存在的数据表"客户信息表01"中--

insert into 客户信息表01 select*from 客户信息表

--添加"商品类型表"数据--

insert 商品类型表 values('RZL001','日杂类')

insert 商品类型表 values('SPL001','饮料类')

insert 商品类型表 values('SPL002','食品类')

insert 商品类型表 values('WJL001','文具类')

--添加"商品信息表"数据--

insert 商品信息表 values('','SPL001','可口可乐','个','辽宁沈阳')

insert 商品信息表 values('','SPL001','矿泉水','个','辽宁沈阳')

insert 商品信息表 values('','SPL001','雪花啤酒','个','辽宁沈阳')

insert 商品信息表 values('','SPL001','青岛啤酒','个','辽宁沈阳')

insert 商品信息表 values('','SPL002','德芙巧克力(牛奶)','个','辽宁沈阳')

insert 商品信息表 values('','SPL002','士力架','个','辽宁沈阳')

insert 商品信息表 values('','SPL002','彩虹糖','盒','辽宁沈阳')

insert 商品信息表 values('','WJL001','中华铅笔','个','辽宁沈阳')

insert 商品信息表 values('','WJL001','大演算','个','辽宁沈阳')

insert 商品信息表 values('','WJL001','便签本','个','辽宁沈阳')

insert 商品信息表 values('','WJL001','曲别针','盒','辽宁沈阳')

insert 商品信息表 values('','WJL001','真彩签字笔(黑)','个','辽宁沈阳')

insert 商品信息表 values('','WJL001','真彩签字笔(红)','个','辽宁沈阳')

insert 商品信息表 values('','WJL001','鸵鸟钢笔水(深蓝)','个','辽宁沈阳')

insert 商品信息表 values('','WJL001','鸵鸟钢笔水(纯蓝)','个','辽宁沈阳')

insert 商品信息表 values('','RZL001','雕牌透明皂','个','辽宁沈阳')

insert 商品信息表 values('','RZL001','中华牙膏','个','辽宁沈阳')

insert 商品信息表 values('','RZL001','心相印纸巾(无味*10)','个','辽宁沈阳')

insert 商品信息表 values('','RZL001','飘柔洗发(240ml)','个','辽宁沈阳')

insert 商品信息表 values('','RZL001','枪手杀虫剂(200ml)','个','辽宁沈阳')

insert 商品信息表 values('','RZL001','金鸡鞋油','个','辽宁沈阳')

--添加"进货信息表"数据--

insert 进货信息表 values(1,'',2.00,100,200.00,'2012-11-01')

insert 进货信息表 values(2,'',1.00,200,200.00,'2012-11-01')

insert 进货信息表 values(3,'',2.00,200,400.00,'2012-11-01')

insert 进货信息表 values(4,'',6.00,10,60.00,'2012-11-01')

insert 进货信息表 values(5,'',2.00,300,600.00,'2012-11-01')

insert 进货信息表 values(6,'',2.00,150,30.00,'2012-11-01')

insert 进货信息表 values(7,'',0.40,150,60.00,'2012-11-01')

insert 进货信息表 values(8,'',6.00,10,60.00,'2012-11-02')

insert 进货信息表 values(9,'',2.00,300,600.00,'2012-11-02')

insert 进货信息表 values(10,'',2.00,150,300.00,'2012-11-02')

insert 进货信息表 values(11,'',0.40,150,60.00,'2012-11-02')

insert 进货信息表 values(12,'',6.00,100,600.00,'2012-11-02')

insert 进货信息表 values(13,'',0.80,150,120.00,'2012-11-02')

insert 进货信息表 values(14,'',0.90,200,180.00,'2012-11-02')

insert 进货信息表 values(15,'',0.60,350,210.00,'2012-11-02')

insert 进货信息表 values(16,'',2.00,150,300.00,'2012-11-02')

insert 进货信息表 values(17,'',1.00,200,200.00,'2012-11-02')

insert 进货信息表 values(18,'',1.00,110,110.00,'2012-11-03')

insert 进货信息表 values(19,'',3.00,100,300.00,'2012-11-03')

insert 进货信息表 values(20,'',3.00,150,450.00,'2012-11-03')

insert 进货信息表 values(21,'',2.00,200,400.00,'2012-11-03')

insert 进货信息表 values(22,'',3.00,100,300.00,'2012-11-03')

insert 进货信息表 values(23,'',5.00,150,750.00,'2012-11-03')

insert 进货信息表 values(24,'',23.00,100,2300.00,'2012-11-04')

insert 进货信息表 values(25,'',16.00,100,1600.00,'2012-11-04')

insert 进货信息表 values(26,'',2.00,200,400.00,'2012-11-04')

insert 进货信息表 values(27,'',2.00,150,300.00,'2012-11-04')

insert 进货信息表 values(28,'',6.00,100,600.00,'2012-11-04')

insert 进货信息表 values(29,'',2.00,300,600.00,'2012-11-04')

insert 进货信息表 values(30,'',2.00,150,300.00,'2012-11-04')

insert 进货信息表 values(31,'',0.70,150,05.00,'2012-11-04')

--添加"销售信息表"数据--

insert 销售信息表 values(1,'',3.00,50,150.00,'2012-12-01','')

insert 销售信息表 values(3,'',8.00,50,400.00,'2012-12-01','')

insert 销售信息表 values(4,'',3.50,50,175.00,'2012-12-11','')

insert 销售信息表 values(5,'',3.50,50,175.00,'2012-12-11','')

insert 销售信息表 values(6,'',2.50,40,100,'2012-12-20','')

insert 销售信息表 values(7,'',0.08,20,16.00,'2012-12-20','')

--添加"库存信息表"数据--

insert 库存信息表 values(1,'',50)

insert 库存信息表 values(2,'',200)

insert 库存信息表 values(3,'',160)

insert 库存信息表 values(4,'',170)

insert 库存信息表 values(5,'',850)

insert 库存信息表 values(6,'',400)

insert 库存信息表 values(7,'',200)

insert 库存信息表 values(8,'',580)

insert 库存信息表 values(9,'',350)

insert 库存信息表 values(10,'',300)

insert 库存信息表 values(11,'',200)

insert 库存信息表 values(12,'',110)

insert 库存信息表 values(13,'',250)

insert 库存信息表 values(14,'',200)

insert 库存信息表 values(15,'',100)

insert 库存信息表 values(16,'',150)

insert 库存信息表 values(17,'',100)

insert 库存信息表 values(18,'',100)

insert 库存信息表 values(19,'',200)

-----------------分割线-------------------------------------------------------------------------------

---函数

--在“商品管理数据库”中创建一个名为“f_proinfor”的内联表值函数，当输入商品编号时返回商品信息

use 商品管理数据库

go

create function f_proinfor (@prono nchar(8))

returns table

as

return

select*from 商品信息表

where 商品编号 =@prono

go

--在“商品管理数据库”中创建一个名为“f_prosale”多语句表值函数，当输入商品编号时返回该商品的销售信息

use 商品管理数据库

go

create function f_prosale(@prono nchar(8))

returns @prosale table

(

商品号 nchar(8),

商品名 nvarchar(20),

销售数量 int,

销售时间 date

)

as

begin

insert @prosale

select p.商品编号,商品名称,销售数量,销售日期

from 商品信息表 p,销售信息表 s

where p.商品编号=s.商品编号

and p.商品编号=@prono

return

end

go

--在“商品管理数据库”中创建一个名为“f_jhslh”标量函数，当输入商品编号时显示该商品的进货总数量

use 商品管理数据库

go

create function f_jhslh(@prono nchar(8))

returns int

begin

declare @jhalh int

select @jhalh=SUM(进货数量)

from 进货信息表

where 商品编号 =@prono

return @jhalh

end

go

---索引

--为 "商品管理数据库"中“进货信息表”的“进货日期”字段创建一个名为“IX_进货信息表_进货日期”的非聚集不唯一索引

create nonclustered

index IX_进货信息表_进货日期

on 进货信息表(进货日期)

--为 "商品管理数据库"中“商品信息表”的“商品类型名”字段创建一个名为“IX_商品类型表_商品类型名”的非聚集唯一索引

create unique nonclustered

index IX_商品类型表_商品类型名

on 商品类型表(商品类型名)

---视图

--在“商品管理数据库”中查询出2012年11月2日进货商品的信息，要求显示出商品编号、商品名称、进货数量和进货日期字段信息，将结果创建到名为“v_商品_进货”的视图中

use 商品管理数据库

go

create view vs_商品_进货成功

as

select 商品信息表.商品编号,商品名称,进货数量,进货日期,'进货成功' '备注' from 商品信息表,进货信息表 where 商品信息表.商品编号=进货信息表.商品编号

and 进货日期='2012-11-02'

--在“商品管理数据库”中查询出商品名称中带“酒”的商品的详细销售信息(显示商品编号、商品名称、销售单价、销售数量、销售金额、客户姓名和销售日期)

use 商品管理数据库

go

create view v_销售信息

as select 商品信息表.商品编号,商品名称,销售单价,销售数量,销售金额,客户姓名,销售日期

from 销售信息表

join 商品信息表 on 销售信息表.商品编号=商品信息表.商品编号

join 客户信息表 on 客户信息表.客户编号=销售信息表.客户编号

where 商品信息表.商品名称 like '%酒%'

---存储过程

--创建名为“p_客户信息表_地址”的存储过程，用来实现在“商品管理数据库”的“客户信息表”中查询“辽宁沈阳”的客户信息

use 商品管理数据库

go

select*from 客户信息表 where 地址='辽宁沈阳' --创建前可以先查询一下要封装的记录

use 商品管理数据库

go

create proc p_客户信息表_地址 --创建存储过程

as

select*from 客户信息表 where 地址='辽宁沈阳'

--创建名为“p_客户信息表_地址x”的存储过程，用来实现在“商品管理数据库”的“客户信息表”中查找指定地址的客户信息

use 商品管理数据库

go

create proc p_客户信息表_地址x

@address nvarchar(30)

as

select*from 客户信息表 where 地址=@address

--创建名为“p_客户信息表_姓名”的存储过程，将查询“客户信息表”中客户编号为20130001的客户信息姓名，并将客户姓名赋值给一个输出参数的查询代码封装到该存储过程中

create proc p_客户信息表_姓名

@name nvarchar(5) output

as

set @name=(select 客户姓名 from 客户信息表 where 客户编号='')

--select @name=客户姓名 from 客户信息表 where 客户编号='20130001'

--**带输入参数的存储过程、带输出参数的存储过程 的创建和执行的总例：(创建名为“p_客户信息表_编号_姓名”的存储过程，要求将查询“客户信息表”中指定客户编号的客户姓名显示出来。并执行存储过程查看结果)

create proc p_客户信息表_编号_姓名

@num nchar(8),@name nvarchar(5) output --定义了一个输入参数@num和一个输出参数@name，数据类型和取值范围与“客户信息表”中的“客户编号”字段和“客户姓名”字段一致

as

select @name=客户姓名 from 客户信息表 where 客户编号=@num

use 商品管理数据库

go

declare @num nchar(8),@name nvarchar(5) --定义了@num和@name两个用来与存储过程中的参数传递和接收值。

set @num='' --使用set命令为变量@num赋值，以便将值传给输入参数@num。

exec p_客户信息表_编号_姓名 @num,@name output --exec执行存储过程时按顺序将两个变量带入到存储过程中。

print '客户编号为'+@num+'的客户的姓名为：'+@name --print语句使用了字符串连接运算，输出详细结果。

---触发器

--(after类型触发器)(创建名为“t_客户信息表_电话”的触发器，当“商品管理数据库”中“客户信息表”的客户编号为20130001的客户联系电话修改为13600003333操作成功执行后，在结果打印一条“记录已修改！”的提示信息。

use 商品管理数据库

go

create trigger t_客户信息表_电话

on 客户信息表

after update --after类型触发器

as

print '记录已修改！'

select*from 客户信息表

--(instead of类型触发器)(创建名为“t_客户信息表_电话no”的触发器，当“商品管理数据库”中“客户信息表”的客户编号为20130001的客户联系电话修改为13600004444时，不执行修改操作，并在结果打印一条“记录未修改！”的提示信息。

use 商品管理数据库

go

create trigger t_客户信息表_电话no

on 客户信息表

instead of update

as

print '记录未修改！'

select*from 客户信息表

--(after类型触发器)(当“商品管理数据库”中有商品售出时，除了向“销售信息表”中添加销售记录之外，还应该为“库存信息表”中相应商品减掉与销售数量等值的库存数量。创建名为“t_销售表_库存表”的触发器。

use  商品管理数据库

go

create trigger t_销售表_库存表

on 销售信息表

after insert

as

update 库存信息表

set 库存数量=库存数量-(select 销售数量 from inserted)

附：(创建“学生选课数据库”的SQl语句)

--建立"学生选课数据库"数据库--

create database 学生选课数据库

on(name='学生选课_m',

filename='D:\学生选课系统\学生选课_m.mdf',

size=3mb,filegrowth=20%,maxsize=10mb),

(name='学生选课_n',

filename='D:\学生选课系统\学生选课_n.ndf',

size=2mb,filegrowth=1mb,maxsize=5mb)

log on(name='学生选课_l',

filename='D:\学生选课系统\学生选课_l.ldf',

size=1mb,filegrowth=1mb,maxsize=unlimited)

go

use 学生选课数据库

--建立"学生信息表"数据表--

create table 学生信息表

(学号 nchar(8)not null primary key,

姓名 nvarchar(5)not null,

性别 nchar(1)null,

专业班级 nvarchar(10)not null)

--为"学生信息表"的"性别"设置检查约束--

alter table 学生信息表

add constraint CK_学生信息表_性别

check (性别='男' or 性别='女')

--建立"课程信息表"数据表--

create table 课程信息表

(课程号 nchar(4)not null primary key,

课程名 nvarchar(10)not null,

学分 real not null)

--为"课程信息表"的"课程号"设置检查约束--

alter table 课程信息表

add constraint CK_课程信息表_课程号

check (课程号 like 'z%' or 课程号 like 'x%')

--为"课程信息表"的"学分"设置检查约束--

alter table 课程信息表

add constraint CK_课程信息表_学分

check (学分>=1)

--建立"选课信息表"数据表--

create table 选课信息表

(学号 nchar(8)not null,

课程号 nchar(4)not null,

成绩 real not null)

--为"选课信息表"的"学号"和"课程号"设置主键约束--

alter table 选课信息表

add constraint PK_选课信息表_课程号

primary key(学号,课程号)

--为"选课信息表"的"学号"设置外键约束--

alter table 选课信息表

add constraint FK_学生信息表_选课信息表_学号

foreign key(学号)

references 学生信息表(学号)

--为"选课信息表"的"课程号"设置外键约束--

alter table 选课信息表

add constraint FK_课程信息表_选课信息表_课程号

foreign key(课程号)

references 课程信息表(课程号)

--为"选课信息表"的"成绩"设置检查约束--

alter table 选课信息表

add constraint CK_选课信息表_成绩

check (成绩>=0 and 成绩<=100)

--添加表数据--

--添加"学生信息表"数据--

insert 学生信息表 values('','李菲','女','11摄影01班')

insert 学生信息表 values('','林斌','男','11摄影01班')

insert 学生信息表 values('','张晶晶','女','11摄影01班')

insert 学生信息表 values('','曹业成','男','11摄影01班')

insert 学生信息表 values('','梁良','男','11摄影01班')

insert 学生信息表 values('','赵晓峰','男','11摄影02班')

insert 学生信息表 values('','韩霞','女','11摄影02班')

insert 学生信息表 values('','杜晓静','女','11摄影02班')

insert 学生信息表 values('','罗飞','男','11摄影02班')

insert 学生信息表 values('','李林','女','11摄影02班')

insert 学生信息表 values('','钱伟','男','11动漫01班')

insert 学生信息表 values('','李月','女','11动漫01班')

insert 学生信息表 values('','杨华','男','11动漫01班')

insert 学生信息表 values('','朱莉','女','11动漫01班')

insert 学生信息表 values('','赵玉平','男','11动漫01班')

insert 学生信息表 values('','杨小平','女','11动漫02班')

insert 学生信息表 values('','李辉','男','11动漫02班')

insert 学生信息表 values('','张梅','女','11动漫02班')

insert 学生信息表 values('','杨斌','男','11动漫02班')

insert 学生信息表 values('','王野','男','12软件01班')

insert 学生信息表 values('','吴山','男','12软件01班')

insert 学生信息表 values('','马振峰','男','12软件01班')

insert 学生信息表 values('','韩文','女','12软件01班')

insert 学生信息表 values('','李小明','男','12网技02班')

insert 学生信息表 values('','张华','女','12网技02班')

insert 学生信息表 values('','罗远新','男','12网技02班')

insert 学生信息表 values('','高胜','男','12网技02班')

--添加"课程信息表"数据--

insert 课程信息表 values('x001','网站建设',2)

insert 课程信息表 values('x002','影视欣赏',2)

insert 课程信息表 values('x003','计算机前沿动态',2)

insert 课程信息表 values('z001','计算机应用基础',2)

insert 课程信息表 values('z002','C语言程序设计',3)

insert 课程信息表 values('z003','网络基础',2)

insert 课程信息表 values('z004','综合布线',3)

insert 课程信息表 values('z005','摄影技术',2)

insert 课程信息表 values('0z006','Photoshop',2)

insert 课程信息表 values('z007','Java程序设计',4)

--添加"选课信息表"数据--

insert 选课信息表 values('','x001',87)

insert 选课信息表 values('','x002',78)

insert 选课信息表 values('','z002',89)

insert 选课信息表 values('','x001',88)

insert 选课信息表 values('','z005',77)

insert 选课信息表 values('','z006',87)

insert 选课信息表 values('','x003',85)

insert 选课信息表 values('','z001',80)

insert 选课信息表 values('','z006',90)

insert 选课信息表 values('','x002',80)

insert 选课信息表 values('','z002',84)

insert 选课信息表 values('','z007',72)

insert 选课信息表 values('','x001',84)

insert 选课信息表 values('','z003',84)

insert 选课信息表 values('','z004',77)

----------------------分割线------------------------------------------------------------------------

--从"学生信息表"中查询"11动漫01班"学生信息，并将其添加至一张名为"动漫专业11级"的新数据表中--

select * into 动漫专业11级 from 学生信息表 where 专业班级='11动漫01班'

--从"学生信息表"中查询"11动漫02班"学生信息，并将其添加至数据表"动漫专业11级"中--

insert into 动漫专业11级 select * from 学生信息表 where 专业班级='11动漫02班'