AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

SQL 基础（六）多关系连接查询

2022-06-07 389
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： SQL 基础（六）多关系连接查询

文章目录

多关系表连接查询

连接查询：一个查询需要对多张表操作，查询结果称表之间的连接；连接关系通过字段值体现，称为连接字段

当我们查询的数据、字段值分布在不同的表中时，这种情况下需要使用多关系表的连接查询

连接类型：内连接（INNER JOIN）、外连接（OUTER JOIN）、交叉连接（）、自然连接（）

连接谓词：连接两个表的条件

内连接查询

关键字（INNER JOIN），功能：仅返回连接条件为真的行，有 from 和 where 字句两种方式

这里要注意，两张表连接时，同名属性需要使用前缀区分（列名唯一不需要）

实现内连接的三种方式举例：

-- method1
select t.tno,tn,cno
from t,tc
where (t.tno=tc.tno) and (tn='XX') -- 连接条件、查询条件
-- method2
select t.tno,tn,cno -- 前缀指名tno为t表属性
from t inner join tc -- inner join 内连接关键字
on t.tno=tc.tno -- 连接条件
where (tn='XX')
-- method3
select r1.tno,r2.tn,r1.cno -- 授课关系 教师号、教室关系 姓名、授课关系 课程号
from
(select tno,cno from tc) as r1
inner join
(select tno,tn from t where tn='XX') as r2
on r1.tno=r2.tno

以上方法中，method1 比较简单且常用 ，建议重点掌握

两张表连接

查询每位学生及选修课程信息

1.from 子句实现

select *
from tb_student inner join tb_score -- from 表1名称 连接类型(内连接 inner join) 表2名称
on tb_student.sno=tb_score.sno -- 同名属性需要加表名称(否则会出错,系统无法识别)

以上代码可简化为

select *
from tb_student sc inner join tb_score s -- 给表起别名，简化代码量
on sc.sno=s.sno

执行结果中发现存在相同列(学号)，说明为等值连接，未删去重复列

2.where 子句实现

select *
from tb_student s,tb_score sc
where s.sno=sc.sno

查询每名同学成绩,包含学号,课程编号,成绩,课程名称信息

select sno,sc.cno,cn,score
from tb_score sc, tb_course c
where sc.cno=c.cno

多张表连接

1.from 子句

select s.sno,sn,cn,score
from tb_student s join tb_score sc
on (s.sno=sc.sno) join tb_course c
on (sc.cno=c.cno)

2.where 子句

select s.sno,sn,cn,score
from tb_student s,tb_score sc,tb_course c
where s.sno=sc.sno and sc.sno=c.cno

外连接查询

外连接中，符合连接条件的数据返回到结果集，不符合连接条件的列会被系统用 NULL 填充，再返回结果集

*注：bit 类型无 NULL 值，会填充 0 后返回结果集中

使用主表所在的方向位置判断连接类型，例如：主表在左，即为左外连接

复习下关系运算中，连接的相关知识

那么上图两张表分别进行外、左外、右外连接后的结果为

举例：查询所有学生选课情况,包括未选课学生信息

左外连接 left join

-- left join
select * from tb_student s left join tb_score sc
on (s.sno=sc.sno)

右外连接 right join

-- right join
select * from tb_score sc right join tb_student s
on (sc.sno=s.sno)

完全外连接 full join

-- full join
select * from tb_student s full join tb_score sc
on (s.sno=sc.sno)

交叉连接查询

又称 “笛卡尔乘积” 连接，实际应用中很少使用

考虑到计算机性能，执行效率也不大相同

这里主要知道关键字为 CROSS JOIN，结果集行数为两张表行数乘积，列数为两表列数总和，然后参考下下方示例即可

-- 对学生表和成绩表进行 交叉查询
select *
from tb_student cross join tb_score

自连接查询

如果我们要查询的结果集中，所包含的信息均在同一张信息表中，这样的查询方式称为自连接查询

示例：查询工资表中，所有比 XXX 工资高的同事姓名、工资和 XXX 的工资

-- method1 
select x.tn,x.sal as sal_a,y.sal as sal_b
from t as x,t as y -- self connect
where x.sal > y.sal and y.tn='XXX'
-- method2 inner join1
select x.tn.x.sal,y.sal
from t as x,t as y
where (x.sal>y.sal) and y.tn='XXX' -- where ... and
-- method3 inner join2
select x.tn,x.sal,y.sal
from t as x inner join t as y
on x.sal > y.sal and y.tn='XXX' -- inner join ... on
-- method4 inner join3
select r1.tn,r1.sal,r2.sal
from
(select tn,sal from t) as r1
inner join
(select tn,sal from t where tn='XXX') as r2
on r1.sal > r2.sal -- inner join ... on

子查询

又称嵌套查询，形式是在 WHERE 中再次包含 SELECT - FROM - WHERE 的查询

程序从内向外执行 SQL 语句，外部查询称为父查询，父查询需要接收子查询（嵌套查询）的结果

普通子查询

普通子查询仅执行一次

返回一个值

该例子解释父级查询需要子查询结果的概念

select tno,tn
from t
where prof=(select prof
      from t
      where tn='XXX')

示例中，prof 的值由子查询查出结果后返回给父查询做结果，上述语句等价为

select tno,tn
from t
where prof=‘子查询 prof 值’

返回一组值

比较运算符仅适用于查询所需返回值为单个值得情况，当返回结果为集合时不再使用，可使用如下方式

ANY

ANY：任何一个

示例：查询讲授课程号为 c5 的教师姓名

select tn -- 姓名
from t 
where (tno = ANY (select tno -- 教师号
          from tc
          where cno='c5')) -- 课程号

首先执行子查询，找到讲授 c5 课程的教师号，父级查询根据教师号再查询教师姓名

意思是，tno 是子查询结果集中的任（ANY）一个

IN

和 ANY 一样的例子，我们也可以使用 IN 实现

select tn -- 姓名
from t 
where (tno IN (select tno -- 教师号
          from tc
          where cno='c5')) -- 课程号

这里的意思是，tno 在（IN）子查询结果集中

ALL

ALL：全部

示例：查询其他系中比计算机系所有教师工资都高的教师姓名和工资

-- 使用 ALL
select tn,sal -- 姓名、工资
from t
where (sal > ALL (select sal
            from t
            where dept='计算机'))
    and (dept <> '计算机') -- 父级条件限制 “其他系” 也即为：不属于 ”计算机系”
-- 使用聚合函数 MAX()
select tn,sal 
from t
where (sal > (select MAX(sal)
        from t
        where dept='计算机'))
    and (dept <> '计算机')

相关子查询

由上面的内容我们知道，子查询程序执行顺序是由内到外，也就是说父级需要子级的消息返回

但是，我们同样会遇到子查询需要父查询相关信息的情况，这样的情况我们称之为相关子查询

示例：查询不讲授课程号为 c5 的课程的教师姓名

-- method1 <> ALL
select distinct tn -- 姓名去重
from t
where ('c5' <> ALL (select cno -- 课程号 
          from tc
          where tno=t.tno)) -- tno 教师号
-- method2 NOT IN
select distinct tn
from t
where ('c5' NOT IN (select cno 
          from tc
          where tno=t.tno)) 
-- method3 NOT EXISTS
select tn
from t
where NOT EXISTS (select *
          from tc
          where tno=t.tno and cno='c5')

该例中，子查询判断课程号 cno 时，需要数据表 t 中教师号 tno 信息，为相关子查询

集合运算查询

在各个子查询对应数据条目和数据类型一致的条件下，可以使用 UNION 关键字将不同的查询得到的数据组合起来

且 UNION 会自动删除重复数据行

使用方法比较简单，给出一个例题供参考

select sno,sum(score)
from tb_a
where(sno='001')
group by sno
UNION
select sno,sum(score)
from tb_b
where(sno='002')
group by sno

上面的 SQL 语句实现：将从 tb_a 中查询出学号为 001 同学的学号和总成绩信息和从 tb_b 中查询出学号为 002 同学的学号和总成绩信息合并为一个结果集

存储查询结果

此处“存储”的含义是指将 A 表中查询的数据结果集存储到其他表，B 表中

我们使用 SQL 语句查询到的结果，仅临时导出让用户（我们）看到，并未真正影响（存储）到对应数据库中，那如何实现查询结果的存储呢？

语法格式为：

SELECT ... INTO ...

具体实现如下

selelct sno,sum(score) -- 学号,总成绩
into tb_b 
from tb_a
group by sno -- 按照学号分组

上面的 SQL 语句实现从 tb_a 中查询出学号和总成绩信息并存放到 tb_b 表中



文章标签：
SQL
数据库
存储
关键词：
SQL查询
SQL连接
SQL连接查询
SQL基础
SQL关系
攻城狮杰森
目录
相关文章
学习数据库的小张
|
9月前
|
SQL 监控 关系型数据库
一键开启百倍加速！RDS DuckDB 黑科技让SQL查询速度最高提升200倍
RDS MySQL DuckDB分析实例结合事务处理与实时分析能力，显著提升SQL查询性能，最高可达200倍，兼容MySQL语法，无需额外学习成本。
学习数据库的小张
979 0
艾伦~耶格尔
|
9月前
|
SQL 存储 关系型数据库
MySQL体系结构详解：一条SQL查询的旅程
本文深入解析MySQL内部架构，从SQL查询的执行流程到性能优化技巧，涵盖连接建立、查询处理、执行阶段及存储引擎工作机制，帮助开发者理解MySQL运行原理并提升数据库性能。
艾伦~耶格尔
350 0
猿享天开
|
SQL 数据挖掘 数据库
第三篇：高级 SQL 查询与多表操作
本文深入讲解高级SQL查询技巧，涵盖多表JOIN操作、聚合函数、分组查询、子查询及视图索引等内容。适合已掌握基础SQL的学习者，通过实例解析INNER/LEFT/RIGHT/FULL JOIN用法，以及COUNT/SUM/AVG等聚合函数的应用。同时探讨复杂WHERE条件、子查询嵌套，并介绍视图简化查询与索引优化性能的方法。最后提供实践建议与学习资源，助你提升SQL技能以应对实际数据处理需求。
猿享天开
927 1
游客kufrkwrbkmpsa
|
SQL 运维 监控
SQL查询太慢？实战讲解YashanDB SQL调优思路
本文是Meetup第十期“调优实战专场”的第二篇技术文章，上一篇《高效查询秘诀，解码YashanDB优化器分组查询优化手段》中，我们揭秘了YashanDB分组查询优化秘诀，本文将通过一个案例，助你快速上手YashanDB慢日志功能，精准定位“慢SQL”后进行优化。
游客kufrkwrbkmpsa
340 2
艾伦~耶格尔
|
9月前
|
SQL 监控 关系型数据库
SQL优化技巧：让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧，涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面，帮助开发者显著提升数据库性能，解决慢查询问题，适用于高并发与大数据场景。
艾伦~耶格尔
486 2
蓝易云
|
10月前
|
SQL XML Java
通过MyBatis的XML配置实现灵活的动态SQL查询
总结而言，通过MyBatis的XML配置实现灵活的动态SQL查询，可以让开发者以声明式的方式构建SQL语句，既保证了SQL操作的灵活性，又简化了代码的复杂度。这种方式可以显著提高数据库操作的效率和代码的可维护性。
蓝易云
568 18
游客kufrkwrbkmpsa
|
SQL 索引
【YashanDB知识库】字段加上索引后，SQL查询不到结果
【YashanDB知识库】字段加上索引后，SQL查询不到结果
游客kufrkwrbkmpsa
333 0
凉凉心.
|
8月前
|
SQL 关系型数据库 MySQL
（SQL）SQL语言中的查询语句整理
查询语句在sql中占了挺大一部分篇幅，因为在数据库中使用查询语句的次数远多于更新与删除命令。而查询语句比起其他语句要更加的复杂，可因为sql是数据库不可或缺的一部分，所以即使不懂，也必须得弄懂，以上。
凉凉心.
436 0
技术自由圈/原疯狂创客圈
|
SQL 关系型数据库 MySQL
凌晨2点报警群炸了：一条sql 执行200秒！搞定之后，我总结了一个慢SQL查询、定位分析解决的完整套路
凌晨2点报警群炸了：一条sql 执行200秒！搞定之后，我总结了一个慢SQL查询、定位分析解决的完整套路
技术自由圈/原疯狂创客圈
571 4
凌晨2点报警群炸了：一条sql 执行200秒！搞定之后，我总结了一个慢SQL查询、定位分析解决的完整套路
三桥君
|
10月前
|
SQL 人工智能 数据库
【三桥君】如何正确使用SQL查询语句：避免常见错误?
三桥君解析了SQL查询中的常见错误和正确用法。AI产品专家三桥君通过三个典型案例：1）属性重复比较错误，应使用IN而非AND；2）WHERE子句中非法使用聚合函数的错误，应改用HAVING；3）正确的分组查询示例。三桥君还介绍了学生、课程和选课三个关系模式，并分析了SQL查询中的属性比较、聚合函数使用和分组查询等关键概念。最后通过实战练习帮助读者巩固知识，强调掌握这些技巧对提升数据库查询效率的重要性。
三桥君
344 0