4.1 表的加减法
4.1.1 什么是集合运算
4.1.2 表的加法–UNION
4.1.2.1 UNION-并集运算
既可以对两张表进行并集运算，也可以对同一张表进行计算
mysql> select product_id,product_name
-> from product
-> union
-> select product_id,product_name
-> from product2;
-
练习
假设连锁店想要增加毛利率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集。
image.png
# 方法1
mysql> select product_id,product_type,sale_price,purchase_price
-> from product
-> where sale_price>1.5*purchase_price
-> union
-> select product_id,product_type,sale_price,purchase_price
-> from product
-> where sale_price<800;
# 方法2
mysql> select product_id,product_type,sale_price,purchase_price
-> from product
-> where sale_price>1.5*purchase_price or sale_price<800;
以上两种代码运行的结果是一样的，说明union和or具有相同的效果，主要区别在于union可以同时查询两个表或多个表，但是or只能在一个表中进行查询操作。
4.1.2.3 包含重复行的集合运算 UNION ALL
我们发现, SQL 语句的 UNION 会对两个查询的结果集进行合并和去重, 这种去重不仅会去掉两个结果集相互重复的, 还会去掉一个结果集中的重复行. 但在实践中有时候需要不去重的并集, 在 UNION 的结果中保留重复行的语法其实非常简单,只需要在 UNION 后面添加 ALL 关键字就可以了。
mysql> select product_id,product_name
-> from product
-> union all
-> select product_id,product_name
-> from product2;
4.1.4 差集,补集与表的减法
4.1.4.1 MySQL 8.0 还不支持 EXCEPT 运算
MySQL 8.0 还不支持表的减法运算符 EXCEPT. 不过, 借助前面学过的NOT IN 谓词, 我们同样可以实现表的减法
找出只存在于product表但不存在于product2表的商品。
mysql> select * from product
-> where product_id not in (select product_id from product2)
-> ;
使用 NOT IN 谓词, 基本上可以实现和SQL标准语法中的EXCEPT运算相同的效果.
4.1.4.4 INTERSECT 与 AND 谓词
使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品,查询结果如下所示.
image.png
mysql> select * from product
-> where sale_price < 1500
-> and sale_price > 1.5*purchase_price;
4.1.5 对称差
两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合。
但由于在MySQL 8.0 里, 由于两个表或查询结果的并不能直接求出来, 因此并不适合使用上述思路来求对称差. 好在还有差集运算可以使用. 从直观上就能看出来, 两个集合的对称差等于 A-B并上B-A, 因此实践中可以用这个思路来求对称差.
使用product表和product2表的对称差来查询哪些商品只在其中一张表, 结果类似于:
image.png
mysql> select * from product
-> where product_id not in (select product_id from product2)
-> union
-> select * from product2
-> where product_id not in (select product_id from product);
4.2 连结 (JOIN)
截至目前,本书中出现的示例(除了关联子查询)基本上都是从一张表中选取数据,但实际上,期望得到的数据往往会分散在不同的表之中, 这时候就需要使用连结了.
之前在学习关联子查询时我们发现, 使用关联子查询也可以从其他表获取信息, 但连结更适合从多张表获取信息.
4.2.1 内连结(INNER JOIN)
我们先来分别观察所涉及的表, product 表保存了商品编号,商品名称,商品种类等信息,这个表可以提供关于衣服种类的衣服的详细信息, 但是不能提供商店信息.
我们接下来观察 shopproduct 表, 这个表里有商店编号名称,商店的商品编号及数量. 但要想获取商品的种类及名称售价等信息,则必须借助于 product 表.
所以问题的关键是, 找出一个类似于"轴"或者"桥梁"的公共列, 将两张表用这个列连结起来. 这就是连结运算所要作的事情.
我们来对比一下上述两张表, 可以发现, 商品编号列是一个公共列, 因此很自然的事情就是用这个商品编号列来作为连接的“桥梁”，将product和shopproduct这两张表连接起来。
mysql> select sp.shop_id,sp.shop_name,sp.product_id,p.product_name,p.product_type,p.sale_price,sp.quantity
-> from shopproduct as sp
-> inner join product as p
-> on sp.product_id = p.product_id;
关于内连结,需要注意以下三点:
- 要点一: 进行连结时需要在 FROM 子句中使用多张表.
之前的 FROM 子句中只有一张表, 而这次我们同时使用了 shopproduct 和 product 两张表,使用关键字 INNER JOIN 就可以将两张表连结在一起了:
FROM shopproduct AS SP INNER JOIN product AS P
- 要点二:必须使用 ON 子句来指定连结条件.
在进行内连结时 ON 子句是必不可少的(大家可以试试去掉上述查询的 ON 子句后会有什么结果).
ON 子句是专门用来指定连结条件的, 我们在上述查询的 ON 之后指定两张表连结所使用的列以及比较条件, 基本上, 它能起到与 WHERE 相同的筛选作用, 我们会在本章的结尾部分进一步探讨这个话题.
- 要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用.
当两张表的列除了用于关联的列之外, 没有名称相同的列的时候, 也可以不写表名, 但表名使得我们能够在今后的任何时间阅读查询代码的时候, 都能马上看出每一列来自于哪张表, 能够节省我们很多时间.
但是, 如果两张表有其他名称相同的列, 则必须使用上述格式来选择列名, 否则查询语句会报错.
我们回到上述查询所回答的问题. 通过观察上述查询的结果, 我们发现, 这个结果离我们的目标: 找出东京商店的衣服类商品的基础信息已经很接近了. 接下来,我们只需要把这个查询结果作为一张表, 给它增加一个 WHERE 子句来指定筛选条件.
4.2.1.2 结合 WHERE 子句使用内连结
如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边.
例如, 对于上述查询问题, 我们可以在前一步查询的基础上, 增加 WHERE 条件.
增加 WHERE 子句的方式有好几种,我们先从最简单的说起.
- 第一种增加 WEHRE 子句的方式, 就是把上述查询作为子查询, 用括号封装起来, 然后在外层查询增加筛选条件.
mysql> SELECT *
-> FROM (-- 第一步查询的结果
-> SELECT SP.shop_id
-> ,SP.shop_name
-> ,SP.product_id
-> ,P.product_name
-> ,P.product_type
-> ,P.sale_price
-> ,SP.quantity
-> FROM shopproduct AS SP
-> INNER JOIN product AS P
-> ON SP.product_id = P.product_id) AS STEP1
-> WHERE shop_name = '东京'
-> AND product_type = '衣服' ;
- 第二种写法：但实际上, 如果我们熟知 WHERE 子句将在 FROM 子句之后执行, 也就是说, 在做完 INNER JOIN … ON 得到一个新表后, 才会执行 WHERE 子句, 那么就得到标准的写法:
mysql> select sp.shop_id, sp.shop_name, sp.product_id,
-> p.product_name, p.product_type, p.sale_price, sp.quantity
-> from shopproduct as sp
-> inner join product as p
-> on sp.product_id = p.product_id
-> where sp.shop_name = '东京'
-> and p.product_type = '衣服';
我们首先给出上述查询的执行顺序:
FROM 子句->WHERE 子句->SELECT 子句
也就是说, 两张表是先按照连结列进行了连结, 得到了一张新表, 然后 WHERE 子句对这张新表的行按照两个条件进行了筛选, 最后, SELECT 子句选出了那些我们需要的列.
- 第三种写法：一种不是很常见的做法是,还可以将 WHERE 子句中的条件直接添加在 ON 子句中, 这时候 ON 子句后最好用括号将连结条件和筛选条件括起来.
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
FROM shopproduct AS SP
INNER JOIN product AS P
ON (SP.product_id = P.product_id
AND SP.shop_name = '东京'
AND P.product_type = '衣服') ;
但上述这种把筛选条件和连结条件都放在 ON 子句的写法, 不是太容易阅读, 不建议大家使用.
- 第四种写法：先连结再筛选的标准写法的执行顺序是, 两张完整的表做了连结之后再做筛选,如果要连结多张表, 或者需要做的筛选比较复杂时, 在写 SQL 查询时会感觉比较吃力. 在结合 WHERE 子句使用内连结的时候, 我们也可以更改任务顺序, 并采用任务分解的方法,先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来.
mysql> select sp.shop_id, sp.shop_name, sp.product_id,
-> p.product_name, p.product_type, p.sale_price, sp.quantity
-> from ( select * from shopproduct where shop_name = '东京') as sp
-> inner join (select * from product where product_type = '衣服') as p
-> on sp.product_id = p.product_id;
练习题
找出每个商店里的衣服类商品的名称及价格等信息. 希望得到如下结果:
- 方法1：不使用子查询
mysql> select sp.shop_id, sp.shop_name, sp.product_id,
-> p.product_name, p.product_type, p.purchase_price
-> from shopproduct as sp
-> inner join product as p
-> on sp.product_id = p.product_id
-> where p.product_type = '衣服';
- 方法2：使用子查询
mysql> select sp.shop_id, sp.shop_name, sp.product_id,
-> p.product_name, p.product_type, p.purchase_price
-> from shopproduct as sp
-> inner join
-> (select product_id, product_name, product_type, purchase_price
-> from product
-> where product_type = '衣服') as p
-> on sp.product_id = p.product_id;
上述第二种写法虽然包含了子查询, 并且代码行数更多, 但由于每一层的目的很明确, 更适于阅读, 并且在外连结的情形下, 还能避免错误使用 WHERE 子句导致外连结失效的问题, 相关示例请参考 4.2.2.3 章节内容。
练习
分别使用连结两个子查询和不使用子查询的方式, 找出东京商店里, 售价低于 2000 的商品信息,希望得到如下结果.
mysql> select sp.*, p.*
-> from shopproduct as sp
-> inner join product as p
-> on sp.product_id = p.product_id
-> where shop_id = '000A' and sale_price < 2000;
