万年流转 万年流转
关注数: 200 粉丝数: 68 发帖数: 7,478 关注贴吧数: 63
中过从 create database 订货数据库 create schema xyz create table xyz.仓库 (仓库号 char(6) primary key check(substring(仓库号,1,2)>='AA'and substring(仓库号,1,2)<='ZZ'and substring(仓库号,3,4)>='0000'and substring(仓库号,3,4)<='9999'), 城市 char(10) unique not null, 面积 numeric check(面积>=50) ) create table xyz.职工 (职工号 char(8) primary key check (right(职工号,2)>='00' and right(职工号,2)<='99'), 仓库号 as convert(char(6),left(职工号,6)) persisted foreign key references xyz.仓库(仓库号), 经理 char(8) foreign key references xyz.职工(职工号), 工资 money check(工资 between 1000 and 10000) ) create table xyz.供应商 (供应商号 char(4) primary key check(left(供应商号,1)>'R'and left(供应商号,1)<'T'and Right(供应商号,3)>='000' and Right(供应商号,3)<='999'), 供应商名 char(16), 地址 char(30) ) create table xyz.订购单 (职工号 char(8) not null foreign key references xyz .职工(职工号), 供应商号 char(4) foreign key references xyz.供应商(供应商号), 订购单号 char(6) primary key check(left( 订购单号,1)>'N' and left( 订购单号,1)<'P'and substring(订购单号,2,1)>'Q'and substring(订购单号,2,1)>'S'and right(订购单号,4)>=‘0000’ and Right(订购单号,4)<='9999'), 订购日期 datetime, 金额 money default null ) create table xyz.订购单明细 (订购单号 char(6) not null foreign key references xyz.订购单(订购单号), 序号 numeric(2) primary key, 产品名称 char(20), 单价 money default null check(单价>0), 数量 int not null check(数量>0) ) insert into xyz.仓库 values('AB1101','赣州',500) insert into xyz.仓库 values('CD1102','广州',1000) insert into xyz.仓库 values('EF1103','惠州',1200) insert into xyz.仓库 values('GH1104','上海',2000) insert into xyz.仓库 values('RS1105','北京',3000) insert into xyz.职工 values('AB110123','罗鹏',1500) insert into xyz.职工 values('AB110124','杨靖',2000) insert into xyz.职工 values('CD110230','黄蓉',3500) insert into xyz.职工 values('CD110238','杨龙',4500) insert into xyz.职工 values('EF110326','俞婷',2500) insert into xyz.职工 values('EF110363','黄婷',7500) insert into xyz.职工 values('GH110452', '廖晨',1500) insert into xyz.职工 values('GH110423','舒怡',6500) insert into xyz.职工 values('RS110518','杨进',1700) insert into xyz.职工 values('RS110523','钟鑫 ',1800) insert into xyz.供应商 values('S123','李四','赣州') insert into xyz.供应商 values('S456','杨大帅','吉安') insert into xyz.供应商 values('S789','杨过','惠州') insert into xyz.供应商 values('S110','罗鹏','吉安') insert into xyz.供应商 values('S129','古拯','安远') insert into xyz.订购单 values('AB110123','S123','OR1101','20111102',1000) insert into xyz.订购单 values('AB110124','S456','OR1102','20111103',1200)
首页 1 2 3 4 下一页