SQL （十二）创建高级联结

迈不过友情╰ 2023-02-21 14:20 174阅读 0赞

![在这里插入图片描述][20200701103757711.png]![在这里插入图片描述][20200701115823532.png]

### 文章目录 ###

*  表的别名（AS关键字）
 *   *  为什么给表起别名
     *   *  示例：在一条select语句多次引用一个表
         *   *  使用子查询
             *  使用自联结搭配表别名
     *  和列别名的区别：不返回到客户端；只在查询时使用
 *  其他联结（除内联结外）
 *   *  自联结 self join
     *  自然联结 natural join：可能永远也不会用到不是自然联结的内联结
     *  外联结 outer join
     *   *  示例：左外联结和右外联结
         *  全外联结（mysql不支持）
 *  带聚集函数的联结
 *  使用联结的注意要点

# 表的别名（AS关键字） #

之前只用as给计算字段起过别名，由于有时候不区分计算字段和列，所以也可以说as可以用于给列起别名。总之，as可以给列和计算字段起别名，还可以给表起别名。

select concat(vend_name, '(', vend_country, ')') 
    as vend_title
    from vendors
    order by vend_name;

![在这里插入图片描述][20200701104719806.png]

> 计算字段必须用as起别名，因为不起名字就是匿名的，没法用。  
> 但是给表起别名则是真的别名，可选的，不起人家也有名字。

select cust_name, cust_contact
    from customers as C, orders as O, orderItems as OI
    where prod_id = 'RGAN01'
    AND OI.order_num = O.order_num
    and C.cust_id = O.cust_id;

![在这里插入图片描述][20200701104909970.png]  
![在这里插入图片描述][20200701104944266.png]  
![在这里插入图片描述][2020070110500076.png]

## 为什么给表起别名 ##

![在这里插入图片描述][20200701105324296.png]  
一直觉得这种起别名的方式很类似C和C++的typedef关键字，所以作用当然也有类似的点：缩短代码。

![在这里插入图片描述][20200701105405773.png]  
很难理解，用一个例子来说明：

### 示例：在一条select语句多次引用一个表 ###

太牛了

假设，我们要给和Jim Jones同一公司的所有顾客发邮件，那么就要找出和Jim Jones同公司的所有顾客，假设我们只找名字，邮箱先不说。

那么首先应该根据Jim Jones来找到Jim Jones工作的公司，然后再去找所有在这个公司工作的顾客。

#### 使用子查询 ####

最内层的查询：找到Jim Jones工作的公司

select cust_name
    from Customers
    where cust_contact = "Jim Jones";

![在这里插入图片描述][20200701110520796.png]  
结果是fun4all公司。

然后直接加上外层查询，写成嵌套的样子：

select cust_id, cust_name, cust_contact
    from Customers
    where cust_name in (select cust_name
    					from Customers
    					where cust_contact = "Jim Jones");

![在这里插入图片描述][20200701110740135.png]  
于是就找到了Dennise这个顾客。

#### 使用自联结搭配表别名 ####

select c1.cust_id, c1.cust_name, c1.cust_contact
    from Customers as c1, Customers as c2 -- 牛不牛逼
    where c2.cust_contact = "Jim Jones"
    and c1.cust_name = c2.cust_name;

注意别名虽然在第二行才定义，但是第一行就在用了，因为这是一个语句，分行只是格式而已。

第一个联结条件用于过滤出c2表中所有联系人为Jim Jones的行，其实就一行，即公司名cust\_name为fun4all的那行；

第二个条件再过滤出c1表中所有和c2表中过滤出的行的公司名相同的行，于是就得到这两行结果。

注意最后显示的是c1表的过滤结果，这个是重要的，因为c2被过滤的只剩一行了  
![在这里插入图片描述][20200701111155960.png]  
如果把第一行代码改为c2

select c2.cust_id, c2.cust_name, c2.cust_contact
    from Customers as c1, Customers as c2 -- 牛不牛逼
    where c2.cust_contact = "Jim Jones"
    and c1.cust_name = c2.cust_name;

则显示两次jim的信息，因为c2被过滤的只剩一行，却又和c1匹配了2次  
![在这里插入图片描述][20200701111939716.png]  
![在这里插入图片描述][20200701111602426.png]

*  很多DBMS处理联结的速度比子查询快得多。所以还是尽量用联结。虽然不懂为什么会快得多，大概是底层代码原因。
 *  用表别名引用的两个表虽然是同一个表，但是DBMS却把他们完全当做两个表，这样真的很方便。

## 和列别名的区别：不返回到客户端；只在查询时使用 ##

![在这里插入图片描述][20200701105014240.png]

# 其他联结（除内联结外） #

![在这里插入图片描述][20200701105117805.png]

## 自联结 self join ##

见上一节的 示例：在一条select语句多次引用一个表

## 自然联结 natural join：可能永远也不会用到不是自然联结的内联结 ##

![在这里插入图片描述][20200701113100381.png]  
![在这里插入图片描述][20200701112241924.png]

由于没出现过不是自然联结的内联结，所以并不清楚相同列多次出现是啥样的

select C.*, O.order_num, O.order_date,
    OI.prod_id, OI.quantity, OI.item_price
    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';

可以看到select后面都是完全限定列名  
![在这里插入图片描述][20200701112946173.png]  
![在这里插入图片描述][20200701113009125.png]

## 外联结 outer join ##

![在这里插入图片描述][20200701113412673.png]

大概明白意思，因为如果一个顾客没有下订单，他这行就不会关联到orders表的某一行，于是联结时就过滤不出来他这一行。

### 示例：左外联结和右外联结 ###

假设要检索出所有顾客，以及他们的订单编号，使用内联结写代码：

select Customers.cust_id, Orders.order_num
    from Customers inner join Orders
    on Customers.cust_id = Orders.cust_id;

![在这里插入图片描述][20200701113717771.png]

*  用了完全限定列名指明返回数据来自哪个表
 *  inner join是用在from子句中的，把顾客表和订单表联结起来了
 *  联结的条件用on子句写

如果用外联结写：

select Customers.cust_id, Orders.order_num
    from Customers left outer join Orders
    on Customers.cust_id = Orders.cust_id;

![在这里插入图片描述][2020070111400424.png]  
![在这里插入图片描述][2020070111414295.png]

*  left outer join，则要选择顾客表的所有行，即没在orders表的不满足联结条件的行也过滤出来。2号顾客没下订单，也被检索出来了。
 *  如果用right，则订单表的所有行都有，但是有订单编号就一定有顾客编号，因为下单了就已经是顾客了，所以这样得到的结果和内联结一样。

select Customers.cust_id, Orders.order_num
    from Customers right outer join Orders
    on Customers.cust_id = Orders.cust_id;

![在这里插入图片描述][20200701114021308.png]

*  左外联结和右外联结可以互换，只要把表的顺序换一下就好了

select Customers.cust_id, Orders.order_num
    from Orders right outer join Customers
    on Customers.cust_id = Orders.cust_id;

![在这里插入图片描述][2020070111455546.png]  
![在这里插入图片描述][20200701114457800.png]

### 全外联结（mysql不支持） ###

![在这里插入图片描述][20200701114700431.png]  
相当于左外联结和右外联结的结合版本了

select Customers.cust_id, Orders.order_num
    from Orders full outer join Customers
    on orders.cust_id = Customers.cust_id;

代码报错，因为mysql不支持  
![在这里插入图片描述][20200701114949819.png]

# 带聚集函数的联结 #

之前学了5个聚集函数，count是其中之一

要和group by 一起用

内联结

select Customers.cust_id,
    	   count(Orders.order_num) as num_order
    from Customers inner join Orders
    on Customers.cust_id = Orders.cust_id
    group by Customers.cust_id;

![在这里插入图片描述][20200701115547147.png]  
![在这里插入图片描述][20200701115645303.png]  
外左联结

select Customers.cust_id,
    	   count(Orders.order_num) as num_order
    from Customers left outer join Orders
    on Customers.cust_id = Orders.cust_id
    group by Customers.cust_id;

![在这里插入图片描述][20200701115611793.png]

# 使用联结的注意要点 #

![在这里插入图片描述][20200701115803869.png]

用联结条件：

select cust_name, cust_contact
    from customers as C, orders as O, orderItems as OI
    where prod_id = 'RGAN01'
    AND OI.order_num = O.order_num
    and C.cust_id = O.cust_id;

![在这里插入图片描述][20200701120034764.png]  
不用联结条件，返回笛卡尔积：

select cust_name, cust_contact
    from customers as C, orders as O, orderItems as OI;

下图不完整，但是能明白意思  
![在这里插入图片描述][20200701115946758.png]

[20200701103757711.png]: https://img-blog.csdnimg.cn/20200701103757711.png
[20200701115823532.png]: https://img-blog.csdnimg.cn/20200701115823532.png
[20200701104719806.png]: https://img-blog.csdnimg.cn/20200701104719806.png
[20200701104909970.png]: https://img-blog.csdnimg.cn/20200701104909970.png
[20200701104944266.png]: https://img-blog.csdnimg.cn/20200701104944266.png
[2020070110500076.png]: https://img-blog.csdnimg.cn/2020070110500076.png
[20200701105324296.png]: https://img-blog.csdnimg.cn/20200701105324296.png
[20200701105405773.png]: https://img-blog.csdnimg.cn/20200701105405773.png
[20200701110520796.png]: https://img-blog.csdnimg.cn/20200701110520796.png
[20200701110740135.png]: https://img-blog.csdnimg.cn/20200701110740135.png
[20200701111155960.png]: https://img-blog.csdnimg.cn/20200701111155960.png
[20200701111939716.png]: https://img-blog.csdnimg.cn/20200701111939716.png
[20200701111602426.png]: https://img-blog.csdnimg.cn/20200701111602426.png
[20200701105014240.png]: https://img-blog.csdnimg.cn/20200701105014240.png
[20200701105117805.png]: https://img-blog.csdnimg.cn/20200701105117805.png
[20200701113100381.png]: https://img-blog.csdnimg.cn/20200701113100381.png
[20200701112241924.png]: https://img-blog.csdnimg.cn/20200701112241924.png
[20200701112946173.png]: https://img-blog.csdnimg.cn/20200701112946173.png
[20200701113009125.png]: https://img-blog.csdnimg.cn/20200701113009125.png
[20200701113412673.png]: https://img-blog.csdnimg.cn/20200701113412673.png
[20200701113717771.png]: https://img-blog.csdnimg.cn/20200701113717771.png
[2020070111400424.png]: https://img-blog.csdnimg.cn/2020070111400424.png
[2020070111414295.png]: https://img-blog.csdnimg.cn/2020070111414295.png
[20200701114021308.png]: https://img-blog.csdnimg.cn/20200701114021308.png
[2020070111455546.png]: https://img-blog.csdnimg.cn/2020070111455546.png
[20200701114457800.png]: https://img-blog.csdnimg.cn/20200701114457800.png
[20200701114700431.png]: https://img-blog.csdnimg.cn/20200701114700431.png
[20200701114949819.png]: https://img-blog.csdnimg.cn/20200701114949819.png
[20200701115547147.png]: https://img-blog.csdnimg.cn/20200701115547147.png
[20200701115645303.png]: https://img-blog.csdnimg.cn/20200701115645303.png
[20200701115611793.png]: https://img-blog.csdnimg.cn/20200701115611793.png
[20200701115803869.png]: https://img-blog.csdnimg.cn/20200701115803869.png
[20200701120034764.png]: https://img-blog.csdnimg.cn/20200701120034764.png
[20200701115946758.png]: https://img-blog.csdnimg.cn/20200701115946758.png