本文深入讲解SQL中的表运算，包括表的加减法、不同类型的连接操作及其应用场景，并通过实例展示了如何利用SQL解决实际问题。
1 表的加减法
1.1 知识点梳理
1.2 练习题
1.2.1 找出 product 和 product2 中售价高于 500 的商品的基本信息。
--UNION
SELECT *
FROM product
WHERE sale_price > 500
UNION
SELECT *
FROM product2
WHERE sale_price > 500;
1.2.2 商店决定对product表中利润低于50%和售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集。
--UNION ALL
SELECT *
FROM product
WHERE sale_price<1000
UNION ALL
SELECT *
FROM product
WHERE sale_price<1.5 * purchase_price;
1.2.3 使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性.
例如, 以下代码可以正确执行, 说明时间日期类型和字符串,数值以及缺失值均能兼容。
运行结果：
1.2.4 使用NOT谓词进行集合的减法运算, 求出product表中, 售价高于2000,但利润低于30%的商品, 结果应该如下表所示。
SELECT*
FROM product
WHERE sale_price > 2000
AND product_id NOT IN(SELECT product_id
FROM product
WHERE sale_price > 1.3 * purchase_price);
1.2.5 使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品。
SELECT *
FROM product
WHERE sale_price<1500
AND sale_price>1.5*purchase_price;
1.2.6 使用product表和product2表的对称差来查询哪些商品只在其中一张表。
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);
1.2.7 借助上述对称差的实现方式, 求product和product2的交。
SELECT *
FROM product
--product和product2 的并集
WHERE product_id IN (SELECT product_id FROM product
UNION SELECT product_id FROM product2)
--减去A-B和B-A的并集
AND product_id NOT IN( SELECT product_id FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION SELECT product_id FROM product2
WHERE product_id NOT IN (SELECT product_id FROM product));
2 连结
2.1 知识点梳理
2.2 内连结 练习题
2.2.1 找出每个商店里的衣服类商品的名称及价格等信息. 希望得到如下结果:
--先连结两个表，再选出衣服
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 product_type="衣服"
--先选出衣服，再连结
SELECT SP.shop_id, SP.shop_name, SP.product_id
,P.product_name, P.product_type, P.purchase_price
FROM shopproduct AS SP
--从 product 表找出衣服类商品的信息
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;
2.2.2 分别使用连结两个子查询和不使用子查询的方式, 找出东京商店里, 售价低于 2000 的商品信息,希望得到如下结果。
--使用子查询
SELECT *
FROM
(SELECT *
FROM shopproduct
WHERE shop_name = '东京') AS SP
INNER JOIN
(SELECT *
FROM product
WHERE sale_price < 2000) AS P
ON SP.product_id = P.product_id;
--不用子查询
SELECT P.*,SP.*
FROM shopproduct AS SP
INNER JOIN product AS p
ON SP.product_id = P.product_id
WHERE shop_id ='000A'
AND sale_price<2000;
2.2.3 每个商店中, 售价最高的商品的售价分别是多少?
SELECT SP.shop_id
,SP.shop_name
,MAX(P.sale_price) AS max_price
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
GROUP BY SP.shop_id,SP.shop_name;
**思考题:**上述查询得到了每个商品售价最高的商品, 但并不知道售价最高的商品是哪一个.如何获取每个商店里售价最高的商品的名称和售价?
2.2.4 每类商品中售价最高的商品都在哪些商店有售?
我再想想
2.2.5 分别使用内连结和关联子查询每一类商品中售价最高的商品。
--内连结
SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price,p2.max_sale_price
FROM product AS p1
INNER JOIN
(SELECT product_type, MAX(sale_price) AS max_sale_price
FROM product
GROUP BY product_type) AS p2
ON p1.product_type=p2.product_type
AND p1.sale_price=p2.max_sale_price;
--关联子查询
SELECT product_type,product_name,sale_price
FROM product AS p1
WHERE sale_price = (SELECT MAX(sale_price)
FROM product AS p2
WHERE p1.product_type = p2.product_type
GROUP BY product_type);
2.2.6 试写出与以下自然连结等价的内连结。
SELECT *
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id;
2.2.7 使用内连结求 product 表和 product2 表的交集。
SELECT P1.*
FROM product AS P1
INNER JOIN product2 AS P2
ON P1.product_id = P2.product_id
2.3 外连结 练习题
2.3.1 基本语法
-- 左连结
FROM <tb_1> LEFT OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL OUTER JOIN <tb_2> ON <condition(s)>
2.3.2 练习题 使用外连结从shopproduct表和product表中找出那些在某个商店库存少于50的商品及对应的商店.希望得到如下结果.
注意高压锅和圆珠笔两种商品在所有商店都无货, 所以也应该包括在内。
SELECT P.product_id
,P.product_name
,P.sale_price
,SP.shop_id
,SP.shop_name
,SP.quantity
FROM product AS P
LEFT OUTER JOIN-- 先筛选quantity<50的商品
(SELECT *
FROM shopproduct
WHERE quantity < 50 ) AS SP
ON SP.product_id = P.product_id
2.3.3 练习题:（非等值连结）
**希望对 product 表中的商品按照售价赋予排名。
一个从集合论出发,使用自左连结的思路：, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数. 例如, 对于价格最高的商品。**注 1: COUNT 函数的参数是列名时, 会忽略该列中的缺失值, 参数为 * 时则不忽略缺失值.
注 2: 上述排名方案存在一些问题–如果两个商品的价格相等, 则会导致两个商品的排名错误, 例如, 叉子和打孔器的排名应该都是第六, 但上述查询导致二者排名都是第七. 试修改上述查询使得二者的排名均为第六.
注 3: 实际上, 进行排名有专门的函数, 这是 MySQL 8.0 新增加的窗口函数中的一种(窗口函数将在下一章学习), 但在较低版本的 MySQL 中只能使用上述自左连结的思路.
SELECT product_id
,product_name
,sale_price
,COUNT(p2_id) AS rank
FROM (--使用自左连结对每种商品找出价格不低于它的商品
SELECT P1.product_id
,P1.product_name
,P1.sale_price
,P2.product_id AS P2_id
,P2.product_name AS P2_name
,P2.sale_price AS P2_price
FROM product AS P1
LEFT OUTER JOIN product AS P2
ON P1.sale_price <= P2.sale_price
) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY rank;
2.3.4.1 练习题:
请按照商品的售价从低到高,对售价进行累计求和[注:这个案例缺少实际意义, 并且由于有两种商品价格相同导致了不必要的复杂度, 但示例数据库的表结构比较简单, 暂时未想出有实际意义的例题]
SELECT product_id, product_name, sale_price
,SUM(P2_price) AS cum_price
FROM
(SELECT P1.product_id, P1.product_name, P1.sale_price
,P2.product_id AS P2_id
,P2.product_name AS P2_name
,P2.sale_price AS P2_price
FROM product AS P1
LEFT OUTER JOIN product AS P2
ON ((P1.sale_price > P2.sale_price)
OR (P1.sale_price = P2.sale_price
AND P1.product_id<=P2.product_id))
ORDER BY P1.sale_price,P1.product_id) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY sale_price,cum_price;
注意：考察我们建立自左连结的本意, 是要找出满足:1.比该商品售价更低的, 或者是 2.该种商品自身,以及 3.如果 A 和 B 两种商品售价相等,则建立连结时, 如果 P1.A 和 P2.A,P2.B 建立了连接, 则 P1.B 不再和 P2.A 建立连结。
2.3.4.2练习题:试将上述查询改用关联子查询实现.
SELECT P1.product_id
,P1.product_name
,P1.sale_price,
(SELECT SUM(sale_price)
FROM product AS p2 WHERE P1.sale_price > p2.sale_price
OR (p1.sale_price = sale_price AND p1.product_id >=p2.product_id)
ORDER BY p1.sale_price,p1.product_id) AS sale_sum
FROM product AS p1
ORDER BY sale_sum;
2.4 交叉连结—— CROSS JOIN(笛卡尔积)
2.4.1 基本语法
-
两个集合做笛卡尔积:使集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合;
-
数据库表(或者子查询)的并,交和差都是在纵向上对表进行扩张或筛选限制等运算, 这要求表的列数及对应位置的列的数据类型"相容">这些运算并不会增加新的列。 而交叉连接(笛卡尔积)则是在横向上对表进行扩张,
即增加新的列, 这一点和连结的功能一致； -
没有ON子句的限制, 会对左表和右表的每一行进行组合, 经常会导致很多无意义的行出现在检索结果中。
交叉连结语法
-- 1.使用关键字 CROSS JOIN 显式地进行交叉连结
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
FROM shopproduct AS SP
CROSS JOIN product AS P;
--2.使用逗号分隔两个表,并省略 ON 子句
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
FROM shopproduct AS SP , product AS P;
对满足相同规则的表进行交叉连结的集合运算符是 CROSS JOIN （笛卡儿积）。进行交叉连结时无法使用内连结和外连结中所使用的ON 子句,这是因为交叉连结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积。
2.4.2 连结与笛卡儿积的关系
- 笛卡儿积可以视作一种特殊的连结(事实上笛卡儿积的语法也可以写作 CROSS JOIN), 这种连结的 ON 子句是一个恒为真的谓词；
- 在对笛卡儿积进行适当的限制之后, 也就得到了内连结和外连结。
- 虽然对笛卡尔积进行适当限制后会得到相同结果，仍不建议使用，主要原因如下：无法马上判断出什么种类的连结；无法短时间内分辨出连结条件和限制条件；语法过时。
3 小结
这块内容太多了，昨天又被导师喊去耽误了一天。本小白熬夜赶工也没能完全掌握，笔记留着有空再整理整理吧。
扫一扫
813
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
