大白糖奶兔的Blog
大白糖奶兔的Blog
数据库第三章-连接查询

连接查询

  • 连接查询:同时涉及多个表的查询
  • 连接条件或连接谓词:用来连接两个表的条件

一般格式

[<表名1>.]<列名1> = [<表名2>.]<列名2>
连接字段:连接谓词中的列名称
连接条件中的各连接字段类型必须是可比的,但名字不必是相同的

等值与非等值连接查询

等值连接,连接运算符为=

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_366ef409b5e00bee8484dd5191cd1040.jpg
select student.*,sc.*
from student,sc
where student.sno = sc.sno;

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

前面是student表中的内容,后面是course表中的内容
这个连接不是自然连接(自然连接会把相同的两列(例如sno)自然合并)
关系代数表达式中的自然连接在select语句中没有简单的写法,select语句只能实现等值连接,不能直接实现自然连接

自然连接

对上面的例子用自然连接完成

select student.sno,sname,ssex,sage,sdept,cno,grade
from student,sc
where student.sno = sc.sno;
# 因为student表和sc表中都有sno属性,所以在属性前加表名
# 但是其他属性只有在各自的表中有,所以不需要加表名
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_ee4e70bb7024d3f0ba5120f240b5b818.jpg

自身连接

  • 一个表与其自己进行连接
  • 需要给表起别名以示区别
  • 由于所有属性都是同名属性,因此必须使用别名前缀

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

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

select first.cno,second.cpno
from course first,course second
where first.cpno = second.cno;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_e99c796489a3615448c44165a1486a07.jpg

外连接

  • 普通连接操作只输出满足连接条件的元组
  • 外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_4eb5823298b2e5154ba649ac1ae7e6c9.jpg
select student.sno,sname,ssex,sage,sdept,cno,grade
from student left outer join sc
on(student.sno=sc.sno);

左外连接sc表,左边的表为student,以student为中心连接sc表
将sc表中不满足条件(student.sno = sc.sno)的数据也一并输出

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

复合条件连接

  • where子句中含多个连接条件

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

···sql
select student.sno,sname
from student,sc
where student.sno = sc.sno and cno = '2' and grade > 90;
···

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_dc2d4c4bbc19e0d7087547644c78f9bb.jpg
select student.sno,sname,cname,grade
from student,course,sc
where student.sno = sc.sno and sc.cno = course.cno;

首先思考需要用到什么表:
student(学号、姓名)course(选修的课程名)sc(成绩)
再思考是否需要连接:
首先sc表中的sno要和student的sno连接起来,用来显示学号、姓名
其次sc表中每一位学生选课的cno要和course的cno等值连接,用来输出课程名称
也就是以sc表为中心
用sno和student连接
用cno和course连接

嵌套查询

概述

  • 一个select-from-where语句成为一个查询块
  • 将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询
    https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_0f69300c3d1cd2bdb8594574f4977930.jpg
select sname  #外层查询(父查询)
from student
where sno in ( #内层查询(子查询)
select sno
from sc
where cno='2'
);

需要使用的表:student,sc
先在sc表中用cno='2'查询出选修了2号课程的学生的学号集合
再用常量集合在另一个select中用In关键字在student中查询出姓名

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

不相关子查询

  • 子查询的查询条件不依赖与父查询
  • 由里向外逐层处理。子查询的查询结果用于建立父查询的查询条件
  • 不相关体现在,执行子查询的时候用不到父查询这张表,不依赖于外层;
  • 如果子查询的where语句中,需要父查询的表构成条件就叫相关子查询

相关子查询

  • 子查询的查询条件依赖于父查询
  • 先驱外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若where子句返回值为真,则取此元组放入结果表
  • 然后再取外层表的下一个元组

一、带有IN谓词的子查询

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

连接查询

select second.sno,second.sname,second.sdept
from student first,student second
where first.sname = '刘晨' and second.sdept=first.sdept;

思考过程:需要什么表?需要几张表?需要什么属性?连接条件?

嵌套查询(不相关子查询)

select sno,sname,sdept
from student
where sdept in(
select sdept
from student
where sname='刘晨'
);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_bd0b81fb3b50e27d0a6cfa82fa56c253.jpg

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

连接查询

select student.sno,sname
from student,sc,course
where course.cname = '信息系统' and course.cno = sc.cno and sc.sno = student.sno;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_ac4241688856b195a8435d32c138558e.jpg

嵌套查询(不相关子查询)

select sno,sname
from student
where sno in(
select sno
from sc
where cno in (
select cno
from course
where cname='信息系统'
 )
);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_366441b4e6f7299c0758bc7c281aa4d7.jpg

思路非常清晰:
需要的属性是:学生学号、姓名
从哪找,从student表中找
找什么样的学生?找选修了信息系统的学生
怎么找?'信息系统'这个数据只有course找
如何在sc中找选修了'信息系统'的学生?依靠course.cno = sc.cno,集合中都是选修了‘信息系统’的学生信息,再用sc.sno=student.sno对应到学生的学号和姓名

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

二、带有比较运算符的子查询(相关子查询)

  • 当能确切知道内层查询返回单值时,可用比较运算符(<、>、=、>=、<=、!或<>)
  • 与any或all谓词配合使用
  • 最多只能返回一个值
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_80829d372560f86118b1f6040415cdbd.jpg
select sno,sname,sdept
from student
where sdept = (
select sdept
from student
where sname = '刘晨'
);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_32b5a04944693b678faece7e84d92401.jpg
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_d0da99258c6b315df3ebddbe337b9feb.jpg
select sno,cno
from sc x
where grade >= (
select avg(grade)
from sc y
where y.sno = x.sno
);

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

这是一个相关子查询
同一张表子查询的执行依赖父查询,所以要起别名
先从外表提取第一行的sno值
在子查询中,子表的sno就是父表的sno的值,然后进行查询,返回平均成绩

例如第一行学号是201215121
那x.sno = y.sno = 201215121
子表返回的平均成绩再与外表的grade进行比较,如果一个人有多科如(201215122)那么每一科的成绩就会分别与平均成绩比较
符合条件进行输出,不符合条件继续下一条查询201215122,

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

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

基于派生表的查询

select sc.sno,cno
from sc,
(select sno,avg(grade)
from sc
group by sno
)as avg_sc(sno,avg_grade)
where sc.sno = avg_sc.sno and sc.grade >= avg_sc.avg_grade;

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

这是一个基于派生表的查询
from语句后面跟着的第二个值,是一个用as关键字创建的,由sc的sno和由sc中以sno为组的平均成绩聚集函数值组成的一个表avg_sc,包含sno和avg_grade(聚集函数不能作为字段名)两个字段
在新表和旧表根据条件进行自然连接,满足条件进行输出

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

三、带有ANY(SOME)或ALL谓词的子查询

  • any:任意一个值
  • all:所有值
  • 需要配合使用比较运算符,如:
    > any:大于子查询结果中的某个值
    > all:大于子查询结果中的所有值

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

方法一:

select sname,sage
from student
where sage < any(
select sage
from student
where sdept='CS'
)
and sdept <> 'CS';

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

方法二:
换一种想法,只要比最大的计算机系的学生小,那和计算机系任意一个比年龄都是最小的

select sname,sage
from student
where sage < (
select max(sage)
from student
where sdept='CS'
)
and sdept <> 'CS';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_88942947eb4588316e6b5114665dd207.jpg

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

方法一:

select sname,sage
from student
where sage < all(
select sage
from student
where sdept = 'CS'
)
and sdept <> 'CS';

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

适当的换行可以理清思路
先确定用student表,再确定比较什么属性sage
用比较运算符连接一个查询结果的集合
满足条件输出,不满足条件pass

方法二:

select sname,sage
from student
where sage < (
select min(sage)
from student
where sdept='CS'
)
and sdept <> 'CS';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_c762bc89af015480ded983abfe55b549.jpg

四、带有EXISTS谓词的子查询 (★难点★)

exists

  • 带有exists谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”
  • exists后面接的是select语句
  • 若内层查询结果非空,则外层的where子句返回真值
  • 若内层查询结果为空,则外层的where子句返回假值

not exists

  • exists后面接的是select语句
  • 若内层查询结果非空,则外层的where子句返回假值
  • 若内层查询结果为空,则外层的where子句返回真值
  • 由exists引出的子查询,其目标列表达式通常都用*,因为带exists的子查询只返回真假,给出列名没有意义

必须用exists的查询:写关系代数表达式中用除法实现的查询,而且是双重not exists实现查询

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

直接描述很难描述出要查询的内容,采用迂回曲折的描述方式,用一个双重否定的方式描述查询过程
先在student表中查询指定条件的学生(会用到student表)
对于这些学生,不存在course表中里有但没有选的课程(会用到course表)
描述学生选课的表(会用到sc表)

select sname
from student
where not exists(
 select *
 from course
 where not exists(
 select *
 from sc
 where sno=student.sno and cno=course.cno
 )
);

这是一个相关子查询的三层嵌套查询的语句
最里层查询的是学生的所有选课信息
中间层查询的是课程表中不存在学生没有选但是course表中有的课
最外层查询的是存在或者不存在的学生姓名,如果该学生有course表中有但是没选的课不输出,如果没有(则选修了全部课程)则输出

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

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

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

select distinct sno
from sc x
where not exists(
 select *
 from sc y
 where y.sno = '201215122' and not exists(
 select *
 from sc z
 where z.sno = x.sno and z.cno = y.cno
 )
);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_3c45cec03f1da79e8d3433f41ece2f59.jpg

最里层查询的是该学生的所有选课信息
中间层查询的是学号为20121512的学生选了,但该学生没有选的课
最外层找的是sno
中间层找的是cno(201215122学生选课的课程号)
最里层(该学生选了且201215122也选了的学生的学号)

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

集合查询

  • 参加集合操作的各查询结果的列数必须相同,对应项的数据类型也必须相同

并操作union

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

方法一(并查询):
将查询结果通过union连接起来
将分别查询到的结果(元组)结合在一起
- union:重复的元组会自动去掉
- union all:重复元组保留

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_453c029af83642beee0ff952757d361a.jpg
select *
from student
where sdept='CS'
union
select *
from student
where sage<=19;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_12aff201370c80e4dadf96360e81232d.jpg

方法二(条件查询)

select *
from student
where sdept='CS' or sage <= 19;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_b8666fa2ea9a5244628f67d8031a04da.jpg
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_68ed7309119e28604352f0b3e86f9623.jpg
select sno
from sc
where cno='1'
union
select sno
from sc
where cno='2';
select distinct sno
from sc
where cno='1' or cno = '2';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_f1c8d921e1a60b239f62bffa9d74e133.jpg

交操作intersect

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

方法一:集合查询

select *
from student
where sdept = 'CS'
intersect
select *
from student
where sage <= 19;

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

- SQL server不支持交和差操作
方法二:条件查询

select *
from student
where sdept = 'CS' and sage <= 19 ;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_33a7ddbded344eaeefc936dcae9dd552.jpg

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

方法一:集合查询

select sno
from sc
where cno = '1'
intersect
select sno
from sc
where cno = '2';

对于这个例子,不可以这么写,因为一个元组中没有同时选两门课的学生,都是单独的

select distinct sno
from sc
where cno = '1' and cno = '2';

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

方法二:嵌套查询(不相关子查询)

select sno
from sc
where cno='1' and sno in (
select sno
from sc
where cno='2'
);

理解起来就是,选了2号课程的学号里找,有没有选了1号课程的

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

差操作except

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

方法一(集合查询):

select *
from student
where sdept='CS'
except
select *
from student
where sage <= 19;

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

方法二:

select *
from student
where sdept='CS' and sage>19;

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

满足第一个条件而不满足第二个条件的

select语句一般格式

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

发表评论

textsms
account_circle
email

大白糖奶兔的Blog

数据库第三章-连接查询
连接查询 连接查询:同时涉及多个表的查询 连接条件或连接谓词:用来连接两个表的条件 一般格式 [<表名1>.]<列名1> = [<表名2>.]<列名2> 连接字段:连接谓词中的列名称 …
扫描二维码继续阅读
2020-08-09
Title - Artist
0:00