大白糖奶兔的Blog
大白糖奶兔的Blog
数据库第三章-Practice(多表查询)

查询语句

(1)查询每个工程项目及其零件使用情况。(两表连接)

select j.jno,j.jname,j.city,sno,pno,qty
from j,spj
where j.jno=spj.jno;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_d2065696970c27c19249930e4752632a.jpg

(2)查询每个工程项目及其零件使用情况,包括没有使用零件的工程项目。(两表外连接)

select *
from j
left outer join spj
on (j.jno=spj.jno);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_e7664deb3cd81df5831f206c1469e0bd.jpg

(3)查询使用供应商S1供应的零件P1的工程号和工程名(两种方法:连接,嵌套)

连接查询

select j.jno,jname
from j,spj
where sno='S1' and pno='P1' and spj.jno=j.jno;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_2d582f0ec363576259bf8cecc2671a52.jpg

嵌套查询

select jno,jname
from j
where jno in (
select jno
from spj
where sno='S1' and pno='P1'
);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_bfc8e289563fbe0020d202ea2d45586b.jpg

(4)查询每个工程项目的工程号、工程名、使用的零件名、零件的供应商名及数量(多表连接)

select j.jno,jname,pname,sname,qty
from spj,s,p,j
where j.jno=spj.jno and s.sno=spj.sno and p.pno=spj.pno;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_73f2b34b433bb5eb0694254212499696.jpg

(5)查询与供应商“东方红”在同一城市的供应商名(同表嵌套)

select second.sname
from s first,s second
where first.sname='东方红' and first.city=second.city;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_78eb20cd373edb5b64ecb9bf6e2c52f1.jpg

(6)查询与供应商“精益”在同一城市的工程项目名(异表嵌套)

个人答案

select jname
from j
where jno in (
select jno
from spj,s first,s second
where first.sname='精益' and first.city=second.city and second.sno = spj.sno
);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_bbac2a0eb92e168f8d8d8d27072c445c.jpg

正确答案:

select jname
from j
where city in (
select city
from s
where sname='精益'
);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_865fe99f9b65c0e6961c2fc09efc7239.jpg

错误查询,

查询了与精益同城市的所有供应商号对应的工程项目名
这是一个不相关子查询,在里层的查询语句中先找出和精益同城市的供应商号,作为和spj表连接的条件找出对应的工程项目号,再在外层连接j表输出对应的工程项目名

正确思路:

在s表中查出精益所在城市,并在外层查询中查出城市为精益所在城市的工程向明

(7)查询供应了“螺丝刀”的供应商号和供应商名(两种方法:连接,嵌套)

连接查询:

select distinct s.sno,sname
from s,p,spj
where p.pname='螺丝刀' and p.pno=spj.pno and spj.sno=s.sno;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_e9252de6fba91d39a27053db1f529b14.jpg

嵌套查询

select sno,sname
from s
where sno in (
select sno
from spj
where pno in (
select pno
from p
where pname='螺丝刀'
 )
);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_4c862636525c5a3582c5ea6c0da17752.jpg

嵌套查询的结果和连接查询没加distinct的有些区别,分解语句测试一下

select pno
from p
where pname='螺丝刀';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_3c4fb49eb543a5756d1e72095c1ef9eb.jpg
select sno
from spj
where pno='P3' or pno='P4';

正因为这条语句,在中间那层spj表的嵌套语句不能把pno in 改成pno =
因为pno不止一个

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_cd69b121233496ce96fad081cbb7eda0.jpg
select sno,sname
from s
where sno in ('S2','S2','S2','S2','S3','S5');
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_d43f9537426278e6b89a007f201f22a0.jpg

我本以为S2的信息会输出四次,但是貌似并不是

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_438a99e72085eec5dae22f8b648ffde8.jpg

经过测试

select sno,sname
from s
where sno in ('S2','S2','S2','S2','S3','S5');

这个语句,等同于

select sno,sname
from s
where sno = 'S2' or sno = 'S2' or sno = 'S2' or sno = 'S2' or sno = 'S3' or sno = 'S5';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_131d34191ea21d8cad2fc791ad2d9aee.jpg

思路:先在p表找出螺丝刀的零件号,用来作为在spj表中找供应商号的条件,然后以供应商号为连接条件输出供应商名,还是蛮简单的

(8)查询使用零件P1总数量超过300的所有工程的工程号和工程名(两种方法:连接,嵌套)

连接查询

select j.jno,jname
from j,spj
where j.jno=spj.jno and pno='P1'
group by spj.jno
having sum(qty) > 300;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_1981444815fdcd18790c3a661fcd6f8f.jpg

这里的group by 后面的jno不能用
select子句中出现的不带聚集函数的属性名,必须是group by子句中有的属性名

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_fb3227141b4fe9c7f9b149045e252851.jpg

嵌套查询

select jno,jname
from j
where jno in(
select jno
from spj
where pno='P1'
group by jno
having sum(qty) > 300
);

连接条件(pno='P1')不可以一起写入having子句中,必须分开

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_8c7e99c0d3173fbc690c06036285e3e0.jpg

(9)查询每种颜色的零件中重量大于该颜色零件平均重量的零件信息(两种方法:相关子查询,基于派生表的查询)

相关子查询

select *
from p x
where weight > (
select avg(weight)
from p y
where x.color = y.color
);

外层有多少行,内层查询就执行多少次
内层查询平均重量,与外层的每一个元数据比较,连接条件是颜色相同

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_57cab8d8d476eba20c57c863b60773e3.jpg

基于派生表的查询

select pno,pname,p.color,weight
from p,
(select color,avg(weight)
from p
group by color
)
as p_avg(color,avg_weight)
where weight > p_avg.avg_weight;

from的第二个参数是一条select查询语句使用as关键字形成的新表

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_cf877542010e683f4f266ecfbe24d3f6.jpg

(10)查询使用了S2供应的P3的工程号与使用了S3供应的P3的工程号的并集。(两种方法:集合查询,非集合查询)

集合查询

select jno
from spj
where sno='S2' and pno='P3'
union
select jno
from spj
where sno='S3' and pno='P3';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_08a1c5777231795a49fe7d520004536c.jpg

非集合查询

select distinct jno
from spj
where (sno='S2' and pno='P3') or (sno='S3' and pno='P3');

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_82c628dedd2e2d98db7f0b790ff57879.jpg

union关键字会自动合并相同元组

(11)查询使用了S2供应的P3的工程号与使用了S3供应的P3的工程号的交集。

集合查询

select jno
from spj
where sno='S2' and pno='P3'
intersect
select jno
from spj
where sno='S3' and pno='P3';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_994388f671beb183a5802e15dbfef15e.jpg

非集合查询(连接查询)

select jno
from spj
where (sno='S2' and pno='P3') and jno in(
select jno
from spj
where sno='S3' and pno='P3'
);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_50e092da126d5a6b86fedd5046f62962.jpg

(12)查询使用了S2供应的P3的工程号与使用了S3供应的P3的工程号的差集。

集合查询

select jno
from spj
where sno='S2' and pno='P3'
except
select jno
from spj
where sno='S3' and pno='P3';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_f00ce8cab5c319f952f6739acb3fdd1e.jpg

连接查询

select jno
from spj
where sno='S2' and pno='P3' and jno not in(
Select jno
from spj
where sno='S3' and pno='P3'
);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_7f455beb3d231f2c69bf14b249b4d0d9.jpg

(13)找出没有使用天津供应商生产的红色零件的工程号。(减法查询)

连接查询

select distinct jno
from spj
where jno not in (
select jno
from spj,s,p
where s.city='天津' and s.sno=spj.sno and p.color='红' and p.pno=spj.pno
);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_2f5e644490cb6f9960ba2eee794a2a66.jpg

减法查询(集合查询)

select jno
from spj
except
select jno
from spj,s,p
where s.city='天津' and s.sno=spj.sno and p.color='红' and p.pno=spj.pno;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_6d9bbb5fb82ce7ce6c43a81053d4f1c6.jpg

?(14)查询至少使用了供应商S1供应的全部零件的工程号。(除法查询)

select jno
from spj
except
select jno
from spj
where not exists

(15)查询给所有工程都供应了零件的供应商号。(除法查询)

select distinct sno
from spj
where not exists(
select *
from j
where not exists(
 select *
 from spj
 where spj.sno=s.sno and spj.jno=j.jno
 )
);

(16)查询使用了所有零件的工程号。(除法查询)

(17)查询所有工程都使用了的零件号。(除法查询)

(18)查询供应了所有零件的供应商号。(除法查询)

(19)查询所有供应商都供应了的零件号。(除法查询)

(20)查询使用了所有供应商的零件的工程。(除法查询)

(21)把全部红色零件的颜色改成蓝色。

update 

(22)由S5供给J4的零件P6改为由S3供应。

update
set 
where sno='S5' and 

(23)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录;

delete
from s
where sno='S2' cascade;

(24)请将(S2,J6,P4,200)插入供应情况关系。

inser into spj(sno,jno,pno,qty)
values('S2','J6','P4',200);

(25)请为三建工程项目建立一个供应情况的视图,包括SNO,PNO,QTY。针对该视图完成下列查询:

(0)创建视图

create view

(1)找出三建工程项目使用的各种零件代码及其数量;

(2)找出供应商S1的供应情况。

发表评论

textsms
account_circle
email

大白糖奶兔的Blog

数据库第三章-Practice(多表查询)
查询语句 (1)查询每个工程项目及其零件使用情况。(两表连接) select j.jno,j.jname,j.city,sno,pno,qty from j,spj where j.jno=spj.jno; (2)查询每个工程项目及其零件使用情况…
扫描二维码继续阅读
2020-08-12
Title - Artist
0:00