关系型数据库基础之多表查询方法及简单示例(表关系、内联结(inner join on)、外联结(left join与right join)、自联结、组合查询 union)

多表查询,就是多个表之间相互关联,利用这些表之间的关联来达到由一个或是多个表查询到另外一个或多个表中数据的目的。例如:学生表中有学生所修课程的id号(此id既是课程表的主键id,又作为学生表中的外键id),课程表中有教师的姓名id,这样就能由学生表中查询到所修课程的id号,再由课程id号查询到老师的姓名。多表查询一定要弄清楚多个表之间的关联是什么,才能在数据查询时不会出现查询到的结果是错误数据的情况。下面介绍一下表之间的关系与多表查询的方法。

一、关系表

SQL(用于访问和处理数据库的标准的计算机语言)最强大的功能之一就是能在数据检索查询的执行中联结(join)表,关系表的设计就是要保证把信息分解成多个表,一个数据一个表,各个表通过某些常用的值相互关联(将一个表的主键作为外键或者是关联字段存放入另一个表中。外键:在一个表中,定义一个字段,这个字段中存储的是另外一个表的主键)。表关系:即表与表之间的关系。表关系有:

(1)、一对一
   有两个表,在A表中的某一行只与B表中的某一行相关,同时B表中的某一行,也只与A中的一行相关,这样的关系称为一对一关系。例如:每个人都有自己的身份证号码,而众多的身份证号码中,每一个号码都对应着唯一的那一个人。
(2)、一对多
   有多张表,A表中的行可以与B表中的一到多个行相关联,但是B表中的一行只能与A表中的一行相关联,这样的关系称为一对多关系。例如:一个人可以拥有多辆汽车,由这个人可以查到这人所拥有的多辆汽车,但从汽车角度进行查询,每一辆车都只对应着这一个人。
(3)、多对多
   有两个表,A表的一行可以与B表中的一到多个行相关联,同时,B表中的一行可以与A表中的一到多个行相关联,类似于这样的关系称为多对多关系。例如:学生选课,一个学生可以选修多门课程,每门课程可供多个学生选择。

二、表联结

(1)、联结是一种机制,用来在一条SELECT语句中关联表,因此称之为联结。
(2)、使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。
(3)、SQL对一条SELECT语句中可以联结的表的数量没有限制。
(4)、应该保证所有联结都有WHERE子句对笛卡尔积结果进行过滤,否则MySQL将返回比想要的数据多得多的数据(如果没有where条件,两个表将进行笛卡尔积配对不管逻辑上是否可以配在一起)。
(5)、应该保证WHERE子句的正确性。不正确的过滤条件将导致MySQL返回不正确的数据。
(6)、笛卡尔积:没有联结条件的情况下,前一个表的每一行都会与后一个表的每一行进行匹配,所以两个表进行笛卡尔积后得到的表的行总数量为:前一个表的行数*后一个表的行数。

三、内部连接

(1)、查询过滤字句where

1)、用于规定筛选的标准。
2)、可在 WHERE 子句中使用的运算符:

=  等于
<>  不等于
<  小于
> 大于
<=  小于等于
>= 大于等于
beteen  在某个范围内
like   搜索某种模式

3)、扩展:WHERE与HAVING的区别(参考网址:SQL 多表连接查询实现语句
where可以用于select、update、delete和insert into values(select * from table where …)语句中。having只能用于select语句中。
where的搜索条件是在执行group by语句进行分组之前应用。 having的搜索条件是在group by分组条件后执行的,如果where和having一起用时,where会先执行,having后执行。
having子句后面可以用集合函数(sum、count、avg、max和min),而where子句不可以。

(2)、Join on(join on =inner join on)

语法:

SELECT * FROM 表一 INNER JOIN 表二 ON 表一 . 字段名 比较运算符 表二 . 字段名

Inner ioin on用法示例:查询学生姓名为“金蝶”的全部信息
student与grade表格如图:
在这里插入图片描述
在这里插入图片描述
查询语句:

SELECT DISTINCT * FROM student INNER JOIN grade ON student.grade_id=gradeid WHERE student.name="金碟";

查询结果:
在这里插入图片描述
四、自连接

自联结——一张表进行自我连接(要对同一个表的数据进行操作时使用)。为一张表取两个别名,用来在进行自连接时对这个表的两个示例进行区分。利用AS给这个表取不同的别名,然后在语句的其它需要使用到该别名的地方用dot(点)来连接该别名和字段名。
使用自联结而不用子查询,自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的,但有时候处理自联结远比处理子查询快得多。

例如:为mst_weather取两个别名分别为: s1, s2。下面这段语句的意思是:查询后一天 temperature 比前一天高的date。mst_weather表如下:

s1与s2进行笛卡尔积:

select s1.*,s2.*
from mst_weather as s1
join mst_weather as s2 ;

结果为:

在笛卡尔积的情况下进行温度条件筛选:

select s1.id
from mst_weather as s1
join mst_weather as s2
on datediff(s1.date,s2.date) = 1
and s1.temperature > s2.temperature;

结果为:
在这里插入图片描述
五、外部连接

(1)、Left join 左连接
左连接是以左侧表为基准(左侧表的信息会全部显示出来),去关联右侧的表进行联结,在连接条件下,如果右侧表格有未关联的数据,那么结果为null

例如:
左连接语句:

SELECT * FROM grade LEFT JOIN student ON grade.gradeid=student.grade_id;

仍然是上面的student表格和grade表格,以左侧的grade 表格为基准,去关联右侧的student表格后的结果为(关联条件:grade.gradeid=student.grade_id):
在这里插入图片描述

(2)、right join 右连接
右连接是以右侧标为基准(右侧表的信息会全部显示出来),去关联左侧的表进行联结,在连接条件下,如果左侧表格有未关联的数据,那么结果为null。

例如:
右连接语句:

SELECT * FROM grade RIGHT JOIN student ON grade.gradeid=student.grade_id;

仍然是上面的student表格和grade表格以右侧的student表格为基础,去关联左侧的grade表格后的结果为(关联条件:grade.gradeid=student.grade_id):
在这里插入图片描述
六、组合查询 UNION

MySQL也允许执行多个查询(多条SELECT语句),并将结果作为单个查询结果集返回,这些组合查询通常称为并(union)或复合查询(compound query)。
UNION规则:
(1)、UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔(因此,如果组合4条SELECT语句,将要使用3个UNION关键字)。
(2)、UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含地转换的类型(例如,不同的数值类型或不同的日期类型)。
(3)、UNION从查询结果集中自动去除了重复的行(换句话说,它的行为与单条SELECT语句中使用多个WHERE子句条件一样)。这是UNION的默认行为,但是如果需要,可以改变它。如果想返回所有匹配行,可使用UNION ALL而不是UNION。SELECT语句的输出用ORDER BY子句排序。在用UNION组合查询时,只能使用一条ORDER BY子句,它必须出现在最后一条SELECT语句之后。对于结果集,不存在用一种方式排序一部分,而又用另一种方式排序另一部分的情况,因此不允许使用多条ORDER BY子句。

相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页