CREATE DATABASE exp_db;
CREATE TABLE IF NOT EXISTS spj_s(
sno CHAR(20),
sname CHAR(20),
sta INT(10),
city CHAR(20),
PRIMARY KEY(sno)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO spj_s (sno,sname,sta,city) VALUES
('s1','精益',20,'天津'),
('s2','盛熙',10,'北京'),
('s3','东方红',30,'北京'),
('s4','丰泰盛',20,'天津'),
('s5','为民',30,'上海');
CREATE TABLE IF NOT EXISTS spj_p
(
pno CHAR(20) PRIMARY KEY,
pname CHAR(20),
color CHAR(20),
weight INT(10)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO spj_p (pno,pname,color,weight) VALUES
('p1','螺母','红',12),
('p2','螺栓','绿',17),
('p3','螺丝刀','蓝',14),
('p4','螺丝刀','红',14),
('p5','凸轮','蓝',40),
('p6','齿轮','红',30);
CREATE TABLE IF NOT EXISTS spj_j
(
jno CHAR(20) PRIMARY KEY,
jname CHAR(20),
city CHAR(20)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO spj_j (jno,jname,city) VALUES
('j1','三建','北京'),
('j2','一汽','长春'),
('j3','弹簧厂','天津'),
('j4','造船厂','天津'),
('j5','机车厂','唐山'),
('j6','无线电厂','常州'),
('j7','半导体厂','南京');
CREATE TABLE IF NOT EXISTS spj_spj
(
sno CHAR(20),
pno CHAR(20),
jno CHAR(20),
qty INT(10)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO spj_spj (sno,pno,jno,qty) VALUES
('s1','p1','j1','200'),
('s1','p1','j3','100'),
('s1','p1','j4','700'),
('s1','p2','j2','100'),
('s2','p3','j1','400'),
('s2','p3','j2','200'),
('s2','p3','j4','500'),
('s2','p3','j5','400'),
('s2','p5','j1','400'),
('s2','p5','j2','100'),
('s3','p1','j1','200'),
('s3','p3','j1','200'),
('s4','p5','j1','100'),
('s4','p6','j3','300'),
('s4','p6','j4','200'),
('s5','p2','j4','100'),
('s5','p3','j1','200'),
('s5','p6','j2','200'),
('s5','p6','j4','500');