CREATE TABLE j (
jno char(3) NOT NULL, -- 工程项目号
jname varchar(10) DEFAULT NULL,
city varchar(10) DEFAULT NULL,
PRIMARY KEY (jno)
);
CREATE TABLE p (
pno char(3) NOT NULL, -- 零件号
pname varchar(10) DEFAULT NULL,
color char(2) DEFAULT NULL,
weight int DEFAULT NULL,
PRIMARY KEY (pno)
);
CREATE TABLE s (
sno char(3) NOT NULL, -- 供应商号
sname varchar(10) DEFAULT NULL,
status char(2) DEFAULT NULL,
city varchar(10) DEFAULT NULL,
PRIMARY KEY (sno)
);
CREATE TABLE spj (
sno char(3) NOT NULL,
pno char(3) NOT NULL,
jno char(3) NOT NULL,
qty int DEFAULT NULL,
PRIMARY KEY (sno,pno,jno),
CONSTRAINT fk_jno FOREIGN KEY (jno) REFERENCES j (jno),
CONSTRAINT fk_pno FOREIGN KEY (pno) REFERENCES p (pno),
CONSTRAINT fk_sno FOREIGN KEY (sno) REFERENCES s (sno)
);
insert into s values('s1','精益','20','天津');
insert into s values('s2','盛锡','10','北京');
insert into s values('s3','东方红','30','北京');
insert into s values('s4','丰泰盛','20','天津');
insert into s values('s5','为民','30','上海');
insert into p values('p1','螺母','红',12);
insert into p values('p2','螺栓','绿',17);
insert into p values('p3','螺丝刀','蓝',14);
insert into p values('p4','螺丝刀','红',14);
insert into p values('p5','凸轮','蓝',40);
insert into p values('p6','齿轮','红',30);
insert into j values('j1','三建','北京');
insert into j values('j2','一汽','长春');
insert into j values('j3','弹簧厂','天津');
insert into j values('j4','造船厂','天津');
insert into j values('j5','机车厂','唐山');
insert into j values('j6','无线电厂','南京');
insert into j values('j7','半导体厂','南京');
insert into j values('j8','电子元件厂','南京');
insert into spj values('s1','p1','j1',200);
insert into spj values('s1','p1','j2',700);
insert into spj values('s1','p1','j4',700);
insert into spj values('s1','p2','j4',100);
insert into spj values('s1','p2','j5',400);
insert into spj values('s1','p3','j2',200);
insert into spj values('s1','p3','j4',100);
insert into spj values('s2','p3','j5',50);
insert into spj values('s2','p3','j6',400);
insert into spj values('s3','p1','j1',100);
insert into spj values('s3','p3','j1',200);
insert into spj values('s4','p1','j1',200);
insert into spj values('s4','p2','j1',100);
insert into spj values('s4','p3','j1',300);
insert into spj values('s4','p5','j4',200);
insert into spj values('s4','p6','j2',100);
insert into spj values('s5','p2','j2',154);
insert into spj values('s5','p3','j1',20);
insert into spj values('s5','p4','j2',50);
insert into spj values('s5','p6','j3',125);
insert into spj values('s5','p6','j4',52);
原创不易,请勿转载(
本不富裕的访问量雪上加霜) 博主首页:https://blog.csdn.net/qq_45034708