商品数据库.docx

create database 商品管理数据库gouse 商品管理数据库gocreate table 客户信息表(客户编号nchar(8) not null primary key,客户姓名nvarchar(5) not null ,联系电话nvarchar(11) not null Unique,地址 nvarchar(30) not null Default辽宁沈阳,邮箱 nvarchar(20) null 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 references 商品类型表(商品类型编号),商品名称nvarchar (20) not null,商品单位nchar (2) not null Default个,产地 nvarchar (30) not null Default辽宁沈阳)create table 进货信息表(进货编号int not null primary key identity (1,1),商品编号nchar (8) not null references 商品信息表(商品编号),进货单价decimal (6,2) not null check(进货单价=0),进货数量int not null check(进货数量0), 进货金额 decimal ,进货日期 datetime not null Default getdate() )create table 销售信息表(销售编号int not null primary key identity (1,1),商品编号nchar (8) not null references 商品信息表(商品编号),销售单价decimal (6,2) not null check(销售单价=0),销售数量int not null check(销售数量0), 销售金额 decimal ,销售日期 datetime not null Default getdate(),客户编号nchar (8) not null references 客户信息表(客户编号)create table 库存信息表(库存编号int not null primary key identity (1,1),商品编号nchar (8) not null references 商品信息表(商品编号),库存数量int not null check(库存数量=0)gouse 商品管理数据库goinsert into 客户信息表(客户编号,客户姓名,联系电话,地址,邮箱) select20130001,张峰辽宁沈阳, union all select20130002,赵小天辽宁大连, union all select20130003,钱成辽宁锦州, union all select20130004,孙飞辽宁沈阳, union all select20130005,李小明辽宁盘连, union all select20130006,周笑辽宁大连, gouse 商品管理数据库goinsert into 商品类型表(商品类型编号,商品类型名)selectRZL001,日杂类 union allselectSPL001,饮料类 union allselectSPL002,食品类 union allselectWJL001,文具类 gouse 商品管理数据库goinsert into 商品信息表(商品编号,商品类型编号,商品名称,商品单位,产地) select11110001,SPL001,可口可乐,个,辽宁沈阳 union all select11110002,SPL001,矿泉水,个,辽宁沈阳 union all select11110003,SPL001,雪花啤酒,个,辽宁沈阳 union all select11110004,SPL001,青岛啤酒,个,辽宁沈阳 union all select22220001,SPL002,德芙巧克力（牛奶）,个,辽宁沈阳 union all select22220002,SPL002,士力架,个,辽宁沈阳 union all select22220003,SPL002,彩虹糖,袋,辽宁沈阳 union all select33330001,WJL001,中华铅笔,个,辽宁沈阳 union all select33330002,WJL001,大演算,个,辽宁沈阳 union all select33330003,WJL001,便签本,个,辽宁沈阳 union all select33330004,WJL001,曲别针,盒,辽宁沈阳 union all select33330005,WJL001,真彩签字笔（黑）,个,辽宁沈阳 union all select33330006,WJL001,真彩签字笔（红）,个,辽宁沈阳 union all select33330007,WJL001,鸵鸟钢笔水（深蓝）,个,辽宁沈阳 union all select33330008,WJL001,鸵鸟钢笔水（纯蓝）,个,辽宁沈阳 union all select44440001,RZL001,雕牌透明皂,个,辽宁沈阳 union all select44440002,RZL001,中华牙膏,个,辽宁沈阳 union all select44440003,RZL001,心相印纸巾（无味*10）,个,辽宁沈阳 union all select44440004,RZL001,飘柔洗发（ml）,个,辽宁沈阳 union all select44440005,RZL001,枪手杀虫剂（ml）,个,辽宁沈阳 union all select44440006,RZL001,金鸡鞋油,个,辽宁沈阳gouse 商品管理数据库goinsert into 销售信息表(商品编号,销售单价,销售数量,销售金额,销售日期,客户编号) select 11110001,3.00,50,150.00,2012-12-01,20130001 union allselect 22220001,8.00,50,400.00,2012-12-01,20130003 union allselect 22220002,3.50,50,175.00,2012-12-11,20130001 union allselect 22220003,3.50,50,175.00,2012-12-01,20130002 union allselect 11110003,2.50,40,100.00,2012-12-20,20130005 union allselect 33330001,0.80,20,16.00,2012-12-20, 20130001gouse 商品管理数据库goinsert into 库存信息表(商品编号,库存数量)select 11110001,50 union allselect 11110002,200 union allselect 11110003,160 union allselect 22220001,170 union allselect 22220002,850 union allselect 22220003,400 union allselect 33330001,200 union allselect 33330002,580 union allselect 33330003,350 union allselect 33330004,300 union allselect 33330005,200 union allselect 33330006,110 union allselect 33330007,250 union allselect 44440001,200 union allselect 44440002,100 union allselect 44440003,150 union allselect 44440004,100 union allselect 44440005,100 union allselect 44440006,200 gouse 商品管理数据库goinsert into 进货信息表select11110001,2.00,100,200.00,2012-11-01 union allselect11110002,1.00,200,200.00,2012-11-01 union allselect11110003,2.00,200,400.00,2012-11-01 union allselect22220001,6.00,10,60.00,2012-11-01 union allselect22220002,2.00,300,600.00,2012-11-01 union allselect22220003,2.00,150,300.00,2012-11-01 union allselect33330002,0.40,150,60.00,2012-11-01 union allselect22220001,6.00,10,60.00,2012-11-02 union allselect22220002,2.00,300,600.00,2012-11-02 union allselect22220003,2.00,150,300.00,2012-11-02 union allselect33330002,0.40,150,60.00,2012-11-02 union allselect22220001,6.00,100,600.00,2012-11-02 union allselect33330002,0.80,150,120.00,2012-11-02 union allselect33330001,0.90,200,180.00,2012-11-02 union allselect33330003,0.60,350,210.00,2012-11-02 union allselect33330004,2.00,150,300.00,2012-11-02 union allselect33330005,1.00,200,200.00,2012-11-02 union allselect33330006,1.00,110,111.00,2012-11-03 union allselect33330007,3.00,100,300.00,2012-11-03 union allselect33330007,3.00,150,450.00,2012-11-03 union allselect44440001,2.00,200,400.00,2012-11-03 union allselect44440002,3.00,100,300.00,2012-11-03 union allselect44440003,5.00,150,750.00,2012-11-03 union allselect44440004,23.00,100,2300.00,2012-11-04 union allselect44440005,16.00,100,1600.00,2012-11-04 union allselect44440006,2.00,200,400.00,201