4.1 交并补差(集合运算)
1.练习-UNION求并集
--1.毛利率超过 50%或者售价低于 800 的货物的存货量
SELECT *
FROM product
WHERE (sale_price-purchase_price)/purchase_price>0.5
UNION
SELECT *
FROM product
WHERE sale_price<800;
--2.用 UNION 或者 OR 谓词,找出毛利率不足 30%或毛利率未知的商品
SELECT *
FROM product
WHERE (sale_price-purchase_price)/purchase_price<0.3
UNION
SELECT *
FROM product
WHERE (sale_price-purchase_price)/purchase_price is null;
UNION ALL 不去重求并集
2.求对称差
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);
UNION&OR, INTERSECT&AND, EXCEPT&NOT IN
4.2 连结 JOIN
使用某种关联条件(一般是判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算
4.2.1 内连结 INNER JOIN
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>
INNER 关键词表示使用了内连结
1.结合 WHERE 子句使用内连结, 需要把 WHERE 子句写在 ON 子句的后边;
也可以更改任务顺序, 并采用任务分解的方法,先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来
--找出每个商店里的衣服类商品的名称及价格等信息
SELECT SP.shop_id,SP.shop_name,P.product_id,P.product_name,P.product_type
FROM product AS P
INNER JOIN
shopproduct AS SP
ON SP.product_id=P.product_id
WHERE P.product_type='衣服';
2.结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待
3.自连结,可以代替关联子查询
4.自然连结, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件; 使用自然连结还可以求出两张表或子查询的公共部分
SELECT * FROM shopproduct NATURAL JOIN Product
5.利用内连结求交集
4.2.2 外连结 OUTER JOIN
-- 左连结
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)>
外连结会根据外连结的种类有选择地保留无法匹配到的行。按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结。
4.2.2.1 左连结与右连结
结合 WHERE 子句使用左连结, 把WHERE子句挪到外连结之前进行
4.2.2.2 全外连结
4.2.3 多表连结
4.2.4 非等值连结
SELECT product_id,product_name,sale_price,COUNT(P2_id) AS myrank
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 JOIN product AS P2
ON P1.sale_price<=P2.sale_price) AS X
GROUP BY product_id
ORDER BY myrank;
--根据售价进行排序
4.2.5 交叉连结 CROSS JOIN
4.3 练习题
1.找出 product 和 product2 中售价高于 500 的商品的基本信息。
SELECT *
FROM product
WHERE sale_price>500
UNION
SELECT *
FROM product2
WHERE sale_price>500;
2.借助对称差的实现方式, 求product和product2的交集。
SELECT* FROM
(
SELECT * FROM product UNION SELECT * FROM product2) AS allproduct
WHERE allproduct.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));
3.每类商品中售价最高的商品都在哪些商店有售 ?
SELECT SP.shop_id,SP.shop_name,P.product_id,product_name,P.product_type,P.MAX_PRICE
FROM shopproduct AS SP
INNER JOIN
(SELECT product_id,product_name,P1.product_type,MAX_PRICE
FROM product AS P1
INNER JOIN
(SELECT product_type, MAX(sale_price) AS MAX_PRICE
FROM product
GROUP BY product_type) AS P2
ON P1.product_type=P2.product_type
WHERE sale_price=MAX_PRICE) AS P
①内连结查询每一类商品中售价最高的商品
SELECT product_id,product_name,P1.product_type,MAX_PRICE
FROM product AS P1
INNER JOIN
(SELECT product_type, MAX(sale_price) AS MAX_PRICE
FROM product
GROUP BY product_type) AS P2
ON P1.product_type=P2.product_type
WHERE sale_price=MAX_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);
5.不会呀
用关联子查询实现:在 product 表中,取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
4.4 有一个问题需要请教
SELECT product_id,product_name,sale_price,COUNT(product_name)
FROM product
GROUP BY product_id;
执行结果:
SELECT product_id,product_name,sale_price,COUNT(product_name)
FROM product
GROUP BY product_type;
执行报错:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘shop.product.product_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
表示SELECT出现了非聚合列。