《SQL必知必会》(12，13)：联结表、创建高级联结

本文链接：https://blog.youkuaiyun.com/qq_43600467/article/details/114156061

文章目录

第十二课：联结表
第十三课：创建高级联结表

第十二课：联结表

12.1 联结

联结(join) ，是在SQL的select 能执行的重要的操作。很重要！！

12.1.1 关系表

先举个栗子，有个药品的表A，里边每一行存着某一类的药品名称，还有一个表B 存放着每个药品的详情。
显然，A和B 产生了关联，如果要删除表A中的某个药品，那么相应的也要在B中级联删除。
通常我们用唯一的标识来进行关联，唯一的标识称为主键(primary key)。

可伸缩 (scale)
能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好(scale well)。

12.1.2 为什么使用联结

将数据分解为多个表，能更有效的存储，更方便管理，就像我们的项目程序分为多个包，dao service properties等等。。
且可伸缩性更好。
凡事皆有因，万事皆有果。
我么们要的数据不在一个表中，那我们怎么通过一条select语句来检索出数据呢？
答案是联结！

12.2 创建联结

创建联结很简单，：指定要联结的表以及关联他们的方式即可。
在这里插入图片描述第二行，是指定要联结的表，第三行是联结的方式。
在这里还是要注意上回说到的 完全限定列名。

12.2.1 where 子句的重要性

我们之前看的是，where子句是过滤条件，在这联结看似有些奇怪，其实并不。
数据库表的联结，是在运行时(run time)构造的。在数据库表的定义中，没有指定DBMS如何联结。
联结两个表时，实际要做的是将第一个表中每一行与第二个表中每一行配对。
where子句作为过滤条件，只包含匹配给定的条件的行。如果没有where子句，那么表A和B 将会每一行都配对，而不管逻辑上是否能配到一起。

笛卡尔积( cartesian product)
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

如下：
在这里插入图片描述还有很多行。。。。
所以这不是我们想要的，匹配的都是错的，没有条件限制，根据笛卡尔积联系到了一块，还有个专业名词叫 叉联结(cross join)

12.2.2 内联结

目前使用的联结称为等值联结(equijoin)，是基于两个表之间的相等测试。这种联结也成为内联结(inner join)。
我们可以用稍微不同的语法来表明联结的类型。下面的例子和之前的效果完全相同：
在这里插入图片描述联结条件是用特定的on子句而不是where。传递给on的实际条件与传递给where的相同。

12.2.3 联结多个表

SQL不限制一条select语句中可以联结表的数量。创建联结的规则也相同。
在这里插入图片描述这个例子显示订单20007中的物品。订单物品存储在OrderItems表中。每个产品按其产品ID存储，它引用Products表中的产品。这些产品通过供应商ID联结到Vendors表中相应的供应商，供应商ID存储在每个产品的记录中。这里的FROM子句列出三个表，WHERE子句定义这两个联结条件，而第三个联结条件用来过滤出订单20007中的物品。

注意！
联结很耗性能，因此不要联结不必要的表，联结的越多，性能下降的越厉害。

回顾上次讲的例子。
在这里插入图片描述显然子查询并不是最好的解决方法

SELECT
	cust_name,
	cust_contact 
FROM
	Customers,
	Orders,
	OrderItems 
WHERE
	Customers.cust_id = Orders.cust_id 
	AND OrderItems.order_num = Orders.order_num 
	AND prod_id = 'RGAN01';

在这里插入图片描述

12.3 小结

联结是SQL中一个最重要、最强大的特性，有效地使用联结需要对关系数据库设计有基本的了解。本课在介绍联结时，讲述了一些关系数据库设计的基本知识，包括等值联结（也称为内联结）这种最常用的联结。

第十三课：创建高级联结表

13.1 使用表别名

SQL 除了对列名和计算字段使用别名，还允许给表名起别名。

SELECT
	cust_name,
	cust_contact 
FROM
	Customers AS C,
	Orders AS O,
	OrderItems AS OI 
WHERE
	C.cust_id = O.cust_id 
	AND OI.order_num = O.order_num 
	AND prod_id = 'RGAN01'

表别名只在查询执行中使用。与列别名不同，表别名不返回到客户端。

13.2 使用不同类型的联结

迄今为止，我们知道了内联结或等值联结的简单联结。还有三种其他联结：自联结(self-join)、自然联结(natural join)和外联结(outer join)。

13.2.1 自联结

使用表别名的一个重要原因是能在一条SQL中多次引用相同的表。
假如要给与Jim Jones同一公司的所有顾客发送一封信件。这个查询要求首先找出Jim Jones工作的公司，然后找出在该公司工作的顾客。下面是解决此问题的一种方法:
在这里插入图片描述这是我们前边看的解决方法，使用了子查询。内部select语句返回Jim的cust_name，用于外部检索的where子句中。

使用联结
在这里插入图片描述

提示:用自联结而不用子查询
自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多DBMS处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

13.2.2 自然联结

基本上内联结都是自然联结。
只要是进行联结，某一列比如id，绝不可以只在一个表中出现，只有两个或多个表中有相同的列才能进行有效的联结。
标准的联结返回所有的数据，也就是前面说的笛卡尔积，相同的列会出现一次或多次。自然连接排除多次出现，每一列只返回一次。
在这里插入图片描述

13.2.3 外联结

一般联结是将一个表中的行与另一个表中的行进行关联。但是有时候我们需要没有关联的行，比如
列出所有产品以及订购数量，包括没有人订购的产品。
解：
我们可以通过人name来关联表检索出产品以及数量，至于没有人订购的以我们之前学的，还解决不了。
在这里插入图片描述这个图讲的还比较清楚，说白了就是，哪外联结就是以哪为主，左外就是以左边的表为主，也就是保全左表，右边符合条件的联结，其他为null

外联结和内连接语法类似：
在这里插入图片描述跟内连接不同的是，外联结有左外连接( left outer join ) 右外连接( right outer join ) 全外连接 ( full outer join)。

13.3 使用带聚集函数的联结

在这里插入图片描述 GROUP BY子句按顾客分组数据,因此,函数调用COUNT(Orders.order_num)对每个顾客的订单计数，将它作为num_ord返回。
聚集函数也可以方便地与其他联结一起使用。请看下面的例子:

13.4 使用联结和联结条件

注意所使用的联结类型。一般我们使用内联结,但使用外联结也有效。
关于确切的联结语法，应该查看具体的文档，看相应的DBMS支持何种语法（大多数DBMS使用这两课中描述的某种语法)。
保证使用正确的联结条件（不管采用哪种语法)，否则会返回不正确
的数据。
应该总是提供联结条件，否则会得出笛卡儿积。
在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。