MySQL数据库入门与实践(七)：表的连结

素梦秋影

已于 2024-08-18 21:17:07 修改

阅读量702

点赞数 14

分类专栏： MySQL 文章标签：数据库 mysql

于 2024-08-18 21:15:00 首次发布

本文链接：https://blog.csdn.net/qq_47370197/article/details/141272781

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一. 集合运算

前面说到，每个查询就是取了整张表的一个子集或者是衍生出的一个子集，总之可以把 select 操作看成是返回了一个集合，那么集合中的交并补就可以拿来运算，

1.加法运算

对应集合中的并集运算，使用的是 union 关键词

CREATE TABLE Product2
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));

INSERT INTO Product2 VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, '2008-09-20');
INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);
INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');

下面将 product 表和 product2 进行合并

SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;

在这里插入图片描述
可以看出 union 运算是将得到的两张表按照上下顺序叠加在一起，但是也要注意列名得取得一致。
值得注意的是，union运算会自动将重复的行去重。需要注意的事项：

作为运算对象的记录的列数必须相同，不能一个挑了两项，另一个表挑了三项
作为运算对象的记录中列的类型必须一致，也就是不能是一个数值型，一个日期型
可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次，并且在最后面使用。

SELECT product_id, product_name
FROM Product
WHERE product_type = '厨房用具'
UNION
SELECT product_id, product_name
FROM Product2
WHERE product_type = '厨房用具'
ORDER BY product_id;

上面说到 union 运算会自动将重复的行去重，那么如果不想进行去重的话有没有办法呢？也是有的，但是需要使用 all 关键字。

SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;

在这里插入图片描述
可以发现0003 这一行并没有去重。

2.交运算

有了并运算，必然有交运算，intersect,和union的用法相同：

SELECT product_id, product_name
FROM Product
intersect
SELECT product_id, product_name
FROM Product2;

在这里插入图片描述

3.差运算

其实差运算可以有并运算和 not 合并得到，但是为了方便，有对应的关键字：except

SELECT product_id, product_name
FROM Product
except
SELECT product_id, product_name
FROM Product2;

在这里插入图片描述
需要说明的是 except 运算会和两个集合的顺序相关，很明显 A-B 和B-A这俩就不是一个集合

SELECT product_id, product_name
FROM Product2
except
SELECT product_id, product_name
FROM Product;

在这里插入图片描述

二.表的连结

表的集合运算说的是将两个表上下叠起来，而表的连结则是把两张表左右连起来，可以发现左右连起来明显难度大得多
在这里插入图片描述

1.内连结

对于之前的两张表，product 和shop_product
在这里插入图片描述

这两张表的列名不同，但是我们在子查询作为 in 参数的时候干过这件事，通过关联子查询将两张表用 where联系起来了。
那我们可以通过两张表共同的列 product_id 把两张表联系起来。

select p.product_id,p.product_name,p.product_type ,p.sale_price ,
p.purchase_price,p.regist_date ,sp.shop_id ,sp.shop_name ,sp.quantity 
from product as p inner join shopproduct as sp
on p.product_id =sp.product_id

在这里插入图片描述
需要的注意的有以下三点：

from 子句中有两张表，这两张表通过 inner join 连在了一起，一般为了方便起见，我们会给每个表去一个别名。
from 子句后面的 on 子句表示的是两张表的连结条件，有点类似于关联子查询中的 where 条件。它能起到与where 相同的作用,如果需要指定多个连结条件，则需要使用 and 和 or 来进行连接。
select 子句中，像 SP.shop_id 和 P.sale_price 这样使用“< 表的别名 >.< 列名 >”的形式来指定列，这是为了避免混淆。
inner join 是将两张表连在一起，两张表连在一起后仍然还是一张表，那也可以对这张表进行率和分组，比如和where的连用。

select p.product_id,p.product_name,p.product_type ,p.sale_price ,
p.purchase_price,p.regist_date ,sp.shop_id ,sp.shop_name ,sp.quantity 
from product as p inner join shopproduct as sp
on p.product_id =sp.product_id 
where SP.shop_id = '000A';

只需要注意这地方where的顺序是在from 后面就可以了，其实我们只要把 from inner join 这个整体看成一个整体，那么别的命令和与之前说的就没有区别。
当然，这张“表”只在 select 语句执行期间存在， select 语句执行之后就会消失。如果希望继续使用这张“表”，还是将它创建成视图吧。

2.外连结

所谓外连结，就是把内连结中的 inner join 换成 left outer join和right outer join 来看下面这个例子：

select p.product_id,p.product_name,p.product_type ,p.sale_price ,
p.purchase_price,p.regist_date ,sp.shop_id ,sp.shop_name ,sp.quantity 
from product as p left outer join shopproduct as sp
on p.product_id =sp.product_id;

在这里插入图片描述
可以发现多出来的两行分别是第10行和第15行，这两行为什么在内连结中没有体现呢，其实也就是 sp这个表并没有0005和0008 这两个id 的信息，也就是 inner join 查询的是对于某个列名，两个表都有的那些行。而对于 left outer 和 right outer 他会显示出其中一张的所有行，这就是左右的区别，也就是这里面存在一个主表，left outer join 就是以左边的表为主表，right outer join 则是以右边的表为主表，主表的所有行都会显示，而对于主表中有，副表中没有的行，会将对副表的列赋值为 null ,而对应附表中有，主表中没有的行则不会显示，比如上面的代码如果改成右主表：

select p.product_id,p.product_name,p.product_type ,p.sale_price ,
p.purchase_price,p.regist_date ,sp.shop_id ,sp.shop_name ,sp.quantity 
from product as p right outer join shopproduct as sp
on p.product_id =sp.product_id;

在这里插入图片描述
则还是13行数据。
对于多张表的连结，其实也是类似的道理，我们可以先创立一张表：

CREATE TABLE InventoryProduct
( inventory_id CHAR(4) NOT NULL,
product_id CHAR(4) NOT NULL,
inventory_quantity INTEGER NOT NULL,
PRIMARY KEY (inventory_id, product_id));

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0001', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0002', 120);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0003', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0004', 3);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P001','0005',0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001','0006',99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0007', 999);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0008', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0001', 10);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0002', 25);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0003', 34);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0004', 19);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0005', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0006', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0007', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0008', 18);

我们将三张表连在一起，注意其中的连结条件：

select p.product_id,p.product_name,p.product_type ,p.sale_price ,
p.purchase_price,p.regist_date ,sp.shop_id ,sp.shop_name ,sp.quantity ,ip.inventory_id ,ip.inventory_quantity 
from product as p inner join shopproduct as sp on p.product_id =sp.product_id
                  inner join inventoryproduct as ip where ip.product_id =p.product_id ;

在这里插入图片描述
这里其实是这样的格式：

from table1 
     inner join table2 on condition1 
     inner join table3 on condition2
     left outer join table4 on condition3

注意这时候其实也是按照从上到下的顺序依次进行内连结和外连结。

3.交叉连结

所谓的交叉连结，就是笛卡尔连结，很明显的数学定义，就是对于第一张表的每一行，和第二张表的每一行做连结，所以笛卡尔连结后后有 m*n行数据。

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP CROSS JOIN Product AS P;

在这里插入图片描述
可以看到最后的数据很多，这里面并没有 on 的连结条件。交叉连结其实并没有什么实用价值。

总结

本小节讲了集合的运算，union，intersect，except 以及连结 inner join,left outer join ,right outer join ,cross join。

素梦秋影

关注

14
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL数据库入门与实践(七)：表的连结

本小节讲了集合的运算，union，intersect，except 以及连结 inner join,left outer join ,right outer join ,cross join。
复制链接

扫一扫