skyADMIN

数据库学习笔记(二)——SPJ表

SPJ表创建:
[cc lang=”sql”]
create table j(
jno char(4) primary key,
jname char(10),
city char(10)
);
create table p(
pno char(4) primary key,
pname char(10),
color char(4),
weight smallint
);
create table s(
sno char(4) primary key,
sname char(10),
status smallint,
city char(10)
);
create table spj(
sno char(4),
pno char(4),
jno char(4),
qty int,
primary key (sno,pno,jno),
foreign key (sno) references s(sno),
foreign key (jno) references j(jno),
foreign key (pno) references p(pno));

[/cc]

S表插入数据:
[cc lang=”sql”]
INSERT INTO s (sno, sname, status, city) VALUES
(‘S1’, ‘精益’, 20, ‘天津’),
(‘S2’, ‘盛锡’, 10, ‘北京’),
(‘S3’, ‘东方红’, 30, ‘北京’),
(‘S4’, ‘丰盛泰’, 20, ‘天津’),
(‘S5’, ‘为民’, 30, ‘上海’);

[/cc]

P表插入数据:
[cc lang=”sql”]
INSERT INTO p (pno, pname, color, weight) VALUES
(‘P1’, ‘螺母’, ‘红’, 12),
(‘P2’, ‘螺栓’, ‘绿’, 17),
(‘P3’, ‘螺丝刀’, ‘蓝’, 14),
(‘P4’, ‘螺丝刀’, ‘红’, 14),
(‘P5’, ‘凸轮’, ‘蓝’, 40),
(‘P6’, ‘齿轮’, ‘红’, 30);

[/cc]

J表插入数据:
[cc lang=”sql”]
INSERT INTO j (jno, jname, city) VALUES
(‘J1’, ‘三建’, ‘北京’),
(‘J2’, ‘一汽’, ‘长春’),
(‘J3’, ‘弹簧厂’, ‘天津’),
(‘J4’, ‘造船厂’, ‘天津’),
(‘J5’, ‘机车厂’, ‘唐山’),
(‘J6’, ‘无线电厂’, ‘常州’),
(‘J7’, ‘半导体厂’, ‘南京’);

[/cc]

SPJ表插入数据:
[cc lang=”sql”]
INSERT INTO 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);

[/cc]

求供应工程J1零件的供应商号码SNO:
[cc lang=”sql”]
select distinct sno 供应商号码 from spj where jno=’j1’
[/cc]
效果如图:

求供应工程J1零件P1的供应商号码SNO:
[cc lang=”sql”]
select distinct sno 供应商号码 from spj where jno=’j1′ and pno=’p1′
[/cc]
效果如图:

求供应工程J1零件为红色的供应商号码SNO:
[cc lang=”sql”]
select sno 供应商号码 from spj where jno=’j1′ and pno in(select pno from p where color=’红’)
[/cc]
效果如图:

求没有使用天津供应商生产的红色零件的工程号JNO:
[cc lang=”sql”]
select jno from j where jno not in( select jno from spj,p,s where spj.sno=s.sno and s.city=’天津’ and spj.pno=p.pno and p.color=’红’)
[/cc]
效果如图:

[cc lang=”sql”]
–1
select sname 供应商,city 所在城市 from s
–2
select pname 零件名称,color 颜色,weight 重量 from p
–3
select jno 工程号 from spj where sno=’s1′
–4
select pname 零件名称,qty 数量 from p,spj where p.pno=spj.pno and jno=’j2′
–5
select distinct pno 零件号码 from spj where sno in (select sno from s where city=’上海’)
–6
select distinct jno 工程名 from spj where sno in (select sno from s where city=’上海’)
–7
select distinct jno 工程号码 from spj where sno not in(select sno from s where city=’天津’)
–8
update p set color=’蓝色’ where color=’红色’
–9
update spj set sno=’s3′ where pno=’p6′ and jno=’j4′
–10
delete from s,spj where sno=’s2′
–11
insert into spj (sno,jno,pno) values(‘s2′,’j6′,’p4’,400)

[/cc]

码字很辛苦,转载请注明来自环宇博客《数据库学习笔记(二)——SPJ表》

评论