1.建表

create database homework;

s表

create table S( 

SNO char(30) ,

sname char(30),

status int,

city char(30),

primary key (sno)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

P表

create table P( 

PNO char(30) ,

pname char(30),

color char(30),

weight int,

primary key (pno)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

J表

create table J( 

JNO char(30) ,

Jname char(30),

city char(30),

primary key (JNO)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

SPJ表

create table SPJ( 

SNO char(30) ,

PNO char(30),

JNO char(30),

QTY INT

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter table SPJ add  foreign key(SNO) references S(SNO);

alter table SPJ add  foreign key(PNO) references P(PNO);

alter table SPJ add  foreign key(JNO) references J(JNO);

S表插入数据

insert into S (SNO ,sname,status,city)
values('S1','精益','20','天津');
insert into S (SNO ,sname,status,city)
values('S2','盛锡','10','北京');
insert into S (SNO ,sname,status,city)
values('S3','东方红','30','北京');
insert into S (SNO ,sname,status,city)
values('S4','丰泰盛','20','天津');
insert into S (SNO ,sname,status,city)
values('S5','为民','30','上海');

P表插入数据

insert into P(PNO ,pname,color,weight)
values('P1','螺母','红','12');
insert into P(PNO ,pname,color,weight)
values('P2','螺栓','绿','17');
insert into P(PNO ,pname,color,weight)
values('P3','螺丝刀','蓝','14');
insert into P(PNO ,pname,color,weight)
values('P4','螺丝刀','红','14');
insert into P(PNO ,pname,color,weight)
values('P5','凸轮','蓝','40');
insert into P(PNO ,pname,color,weight)
values('P6','齿轮','红','30');

J表插入数据

insert into J(JNO ,jname,city)
values('J1','三建','北京');
insert into J(JNO ,jname,city)
values('J2','一汽','长春');
insert into J(JNO ,jname,city)
values('J3','弹簧厂','天津');
insert into J(JNO ,jname,city)
values('J4','造船厂','天津');
insert into J(JNO ,jname,city)
values('J5','机车厂','唐山');
insert into J(JNO ,jname,city)
values('J6','无线电厂','常州');
insert into J(JNO ,jname,city)
values('J7','半导体厂','南京');

SPJ表插入数据