学习目标:
学习产出
加法
- UNION并集运算
和OR语句有异曲同工之处,但是出于查询效率使用UNION更好
SELECT
FROM
UNION
SELECT
FROM
练习题
SELECT *
FROM product
WHERE sale_price>500
UNION
SELECT *
FROM product2
WHERE sale_price>500;
UNION中会对查询结果集进行合并去重,如果不需要去除可以使用UNION ALL语句
差集与补集
MySQL8.0暂时不支持EXCEPT命令,我们可以用NOT IN谓词代替
交集
INTERSECT可以用AND实现
JOIN联结
内联结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>
要点一: 进行连结时需要在 FROM 子句中使用多张表.
要点二:必须使用 ON 子句来指定连结条件.
要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用
结合WHERE语句
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 子句
自然连结(NATURAL JOIN)
自动找可作为标准的列
eg:
SELECT *
FROM (SELECT product_id, product_name
FROM product ) AS A
NATURAL JOIN
(SELECT product_id, product_name
FROM product2) AS B;
外联结OUTER JOIN
按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结.
- 左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值;
- 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值;
- 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充.
- 选取出单张表中全部的信息
- 使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表.
练习题
SELECT P1.product_id
,P1.product_name
,P1.product_type
,P1.sale_price
,P2.avg_price
FROM product AS P1
INNER JOIN
(SELECT product_type,MAX(sale_price) AS avg_price
FROM product
GROUP BY product_type) AS P2
ON P1.product_type = P2.product_type``
-----------------------------------------
关联子查询
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);
--找出每个商店里的衣服类商品的名称及价格等信息.
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 = '衣服';
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 表找出衣服类商品的信息
(SELECT product_id, product_name, product_type, purchase_price
FROM product
WHERE product_type = '衣服')AS P
ON SP.product_id = P.product_id;
PART2
--T1-----------------
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
--多表联结
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
,IP.inventory_quantity
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
INNER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id
WHERE IP.inventory_id = 'P001';
PART3
SELECT p1.product_id,
p1.product_name,
sale_price,
(SELECT SUM(sale_price) FROM product AS p2 WHERE 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 cum_price
FROM product AS p1
ORDER BY cum_price