1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
| 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);
|