数据库作业

create database ex3_2;
use ex3_2;
create table if not exists S(
    SNO varchar(10) PRIMARY KEY,
    SNAME varchar(10),
    STATUS int,
    CITY varchar(10)
) engine=innodb default charset=utf8;

create table if not exists P(
    PNO varchar(10) PRIMARY KEY,
    PNAME varchar(10),
    COLOR varchar(10),
    WEIGHT int
)engine=innodb default charset=utf8;

create table if not exists J(
    JNO varchar(10) PRIMARY KEY ,
    JNAME varchar(10),
    CITY varchar(10)
)engine=innodb default charset=utf8;

create table if not exists SPJ(
    SNO varchar(10) ,
    PNO varchar(10) ,
    JNO varchar(10) ,
    QTY int,
    foreign key(SNO) references S(SNO),
    foreign key(PNO) references P(PNO),
    foreign key(JNO) references J(JNO),
    primary key(SNO,PNO,JNO)
)engine=innodb default charset=utf8;

insert into s values('s1','精益',20,'天津'),
('s2','盛锡',10,'北京'),
('s3','东方红',30,'北京'),
('s4','丰泰盛',20,'天津'),
('s5','为民',30,'上海');
insert into p values('p1','螺母','红',12),
('p2','螺栓','绿',17),
('p3','螺丝刀','蓝',14),
('p4','螺丝刀','红',14),
('p5','凸轮','蓝',40),
('p6','齿轮','红',30);
insert into j values('j1','三建','北京'),
('j2','一汽','长春'),
('j3','弹簧厂','天津'),
('j4','造船厂','天津'),
('j5','机车厂','唐山'),
('j6','无线电厂','常州'),
('j7','半导体厂','南京');
insert into spj 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',100),
('s2','p5','j2',200),
('s3','p1','j1',200),
('s3','p3','j1',100),
('s4','p5','j1',300),
('s4','p6','j3',200),
('s5','p6','j4',100),
('s5','p2','j4',100),
('s5','p3','j1',200),
('s5','p6','j2',200),
('s5','p6','j4',500);