用SQL语句建立第2章习题6的4个表:针对建立的4个表用sql完成第2章习题6的查询
第三章第4和5题1创建表2插入表3第三章第4题1求供应工程J1零件的供应商号码SNO2求供应工程J1零件p1的供应商号码SNO3求供应工程J1零件为红色的供应商号码SNO4求没有使用天津供应商生产的红色零件的工程号5求至少用了供应商s1所供应的全部零件的的工程号JNO4第三章第5题1找出所有供应商的姓名和所在城市2找出所有零件的名称,颜色,重量3找出使用供应商s1所供应零件的工程号码4找出工程项目
·
建表语句和插入表转载于:
https://blog.csdn.net/weixin_42545675/article/details/100513076
1创建表
CREATE TABLE S(
SNO CHAR(3) PRIMARY KEY,
SNAME CHAR(10),
STATUS CHAR(2),
CITY CHAR(10));
CREATE TABLE P(
PNO CHAR(3),
PNAME CHAR(10),
COLOR CHAR(4),
WEIGHT INT,
PRIMARY KEY(PNO));
CREATE TABLE J(
JNO CHAR(3),
JNAME CHAR(10),
CITY CHAR(10),
PRIMARY KEY(JNO));
CREATE TABLE SPJ(
SNO CHAR(3),
PNO CHAR(3),
JNO CHAR(3),
QTY INT,
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO) REFERENCES S(SNO),
FOREIGN KEY(PNO) REFERENCES P(PNO),
FOREIGN KEY(JNO) REFERENCES J(JNO)
);
2插入表
INSERT
INTO S(SNO,SNAME,STATUS,CITY)
VALUES('S1','精益','20','天津');
INSERT
INTO S
VALUES('S2','盛锡','10','北京');
INSERT
INTO S
VALUES('S3','东方红','30','北京');
INSERT
INTO S
VALUES('S4','丰泰盛','20','天津');
INSERT
INTO S
VALUES('S5','为民','30','上海');
INSERT
INTO P
VALUES('P1','螺母','红',12);
INSERT
INTO P
VALUES('P2','螺栓','绿',17);
INSERT
INTO P
VALUES('P3','螺丝刀','蓝',14);
INSERT
INTO P
VALUES('P4','螺丝刀','红',14);
INSERT
INTO P
VALUES('P5','凸轮','蓝',40);
INSERT
INTO P
VALUES('P6','齿轮','红',30);
INSERT
INTO J
VALUES('J1','三建','北京');
INSERT
INTO J
VALUES('J2','一汽','长春');
INSERT
INTO J
VALUES('J3','弹簧厂','天津');
INSERT
INTO J
VALUES('J4','造船厂','天津');
INSERT
INTO J
VALUES('J5','机车厂','唐山');
INSERT
INTO J
VALUES('J6','无限电厂','常州');
INSERT
INTO J
VALUES('J7','半导体厂','南京');
INSERT INTO SPJ VALUES('S1','P1','J1',200);
INSERT INTO SPJ VALUES('S1','P1','J3',100);
INSERT INTO SPJ VALUES('S1','P1','J4',700);
INSERT INTO SPJ VALUES('S1','P2','J2',100);
INSERT INTO SPJ VALUES('S2','P3','J1',400);
INSERT INTO SPJ VALUES('S2','P3','J2',200);
INSERT INTO SPJ VALUES('S2','P3','J4',500);
INSERT INTO SPJ VALUES('S2','P3','J5',400);
INSERT INTO SPJ VALUES('S2','P5','J1',400);
INSERT INTO SPJ VALUES('S2','P5','J2',100);
INSERT INTO SPJ VALUES('S3','P1','J1',200);
INSERT INTO SPJ VALUES('S3','P3','J1',200);
INSERT INTO SPJ VALUES('S4','P5','J1',100);
INSERT INTO SPJ VALUES('S4','P6','J3',300);
INSERT INTO SPJ VALUES('S4','P6','J4',200);
INSERT INTO SPJ VALUES('S5','P2','J4',100);
INSERT INTO SPJ VALUES('S5','P3','J1',200);
INSERT INTO SPJ VALUES('S5','P6','J2',200);
INSERT INTO SPJ VALUES('S5','P6','J4',500);
3第三章第4题
1求供应工程J1零件的供应商号码SNO
select distinct sno
from spj
where jno='J1';
2求供应工程J1零件p1的供应商号码SNO
select distinct sno
from spj
where jno='J1' and pno='P1';
3求供应工程J1零件为红色的供应商号码SNO
嵌套查询
select sno
from spj
where jno='j1'
and pno in /*找出红色的零件号码pno*/
(select pno
from p /*从p表中找*/
where color='红');
或 用连接查询
select sno
from spj,p
where p.pno = spj.pno and -- 连接两个表
jno='J1' and p.color='红';
4求没有使用天津供应商生产的红色零件的工程号
工程表J中不包含: 1和2为嵌套关系
1天津的供应商生产的
2红色的零件
select jno
from j -- 从j表入手,以包含那些尚未使用任何零件的工程号
where not exists
(select *
from spj
where spj.jno = j.jno
and sno in
(select sno -- 天津供应商的sno
from s
where city='天津')
and pno in
(select pno -- 红色零件的pno
from p
where color='红'));
或
select Jno
from j
where not exists
(select *
from spj,s,p
where spj.sno =s.sno and spj.jno = j.jno
and spj.pno = p.pno -- 两个and,三个等于用于,连接三个表
and s.city = '天津'
and p.color = '红');
5求至少用了供应商s1所供应的全部零件的的工程号JNO
select distinct jno
from spj spjz
where not exists -- 这是一个相关子查询
(select * -- 父查询和子查询均引用了spj表
from spj spjx -- 用别名spjz,spjx将父查询与子查询分开
where sno='s1'
and not exists
(select * -- 用别名spjy与父查询中spj表分开
from spj spjy
where spjy.pno = spjx.pno
and spjy.jno = spjz.jno));
select * -- 父查询和子查询均引用了spj表
from spj spjx -- 用别名spjz,spjx将父查询与子查询分开
where sno='s1'
and not exists
(select * -- 用别名spjy与父查询中spj表分开
from spj spjy
where spjy.pno = spjx.pno); -- 查询结果为0
会循环显示sno不是s1的项目
select *
from spj spjx ,spj spjy -- 用别名spjz,spjx将父查询与子查询分开
where not spjx.sno='s1' and not spjy.pno = spjx.pno;
4第三章第5题
1找出所有供应商的姓名和所在城市
select Sname,city
from s
2找出所有零件的名称,颜色,重量
select Pname,color,weight
from p;
3找出使用供应商s1所供应零件的工程号码
select jno
from spj
where sno='s1';
4找出工程项目j2使用的各种零件的名称及其数量
select Pname,QTy
from spj,p
where spj.pno = p.pno
and spj.jno='j2';
5找出上海厂商供应的所有零件号码
select distinct Pno
from spj
where sno in
(select sno
from s
where s.city='上海');
select distinct pno
from s,spj
where spj.sno=s.sno
and s.city='上海';
6找出使用上海产的零件的工程名字
select Jname
from j
where jno in
(select jno
from s,spj
where s.sno=spj.sno
and s.city='上海');
或
select jname
from j,s,spj
where j.jno=spj.jno and s.sno=spj.sno
and where s.city='上海');
7找出没有使用天津产的零件的工程号
select jno
from j
where not exists
(select *
from spj
where spj.jno=j.jno
and sno in
(select sno
from s
where 1=1 /*s.sno=spj.sno 可有可无*/
and city='天津'));
或
select jno
from j
where not exists
(select *
from spj,s
where spj.jno=j.jno
and spj.sno=s.sno
and s.city='天津');
8把全部红色零件的颜色改为蓝色
update p
set color='蓝'
where color='红';
9由s5供给j4的零件p6改为由s3供应
update spj
set sno='s3'
where sno='s5' and jno='j4' and pno='p6';
10从供应商关系删除s2的记录,并从供应关系中删除相应的记录
先删spj表中的内容
再删s表中的内容
因为spj表依赖s表
delete from spj where sno='s2';
delete from s where sno='s2';
11请将(s2,j6,p4,200)插入供应情况关系
insert into spj
values('s2','p4','j6',200);
开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!
更多推荐
已为社区贡献4条内容
所有评论(0)