先建立四个关系:S,P,J,SPJ 分别代表供应商表,零件表,工程项目表,供应情况表
建立S表:
CREATE TABLE S
(
SNO CHAR(3) primary key,
SNAME CHAR(10),
STATUS CHAR(2),
CITY CHAR(10)
)charset gbk;
建立P表:
CREATE TABLE P
(
PNO CHAR(3) primary key,
PNAME CHAR(10),
COLOR CHAR(4),
WEIGHT INT
)charset gbk;
建立J表:
CREATE TABLE J
(
JNO CHAR(3) primary key,
JNAME CHAR(10),
CITY CHAR(10)
)charset gbk;
建立SPJ表:
CREATE TABLE SPJ
(
SNO CHAR(3),
PNO CHAR(3),
JNO CHAR(3),
QTY INT,
primary key(SNO,PNO,JNO),
foreign key (SNO) references S(SNO),
foreign key (PNO) references P(PNO),
foreign key (JNO) references J(JNO)
)charset gbk;
插入数据:
INSERT INTO S VALUES('S1',