一、表的加减法
UNION
把两张表合并，UNION 等集合运算符通常都会除去重复的记录
使用UNION ALL 就可以不去重了
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;
将两个表中的字段进行连接——内连接（inner join）
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 子句中只有一张表, 使用关键字 INNER JOIN 就可以将ShopProduct 和 Product 两张表连结在一起:
FROM ShopProduct AS SP INNER JOIN Product AS P
要点二:必须使用 ON 子句来指定连结条件.
ON 子句是专门用来指定连结条件的, 我们在上述查询的 ON 之后指定两张表连结所使用的列以及比较条件, 基本上, 它能起到与 WHERE 相同的筛选作用, 我们会在本章的结尾部分进一步探讨这个话题.
要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用.
当两张表的列除了用于关联的列之外, 没有名称相同的列的时候, 也可以不写表名, 但表名可以提升可读性间
但是, 如果两张表有其他名称相同的列, 则必须使用上述格式来选择列名, 否则查询语句会报错.
结合 WHERE 子句使用内连结——对内连接结果进行筛选
方法一：
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 = '衣服' ;
方法二：
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 = '衣服') ;
找出那些售价高于该类商品平均价格的商品.
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,AVG(sale_price) AS avg_price
FROM Product
GROUP BY product_type) AS P2
ON P1.product_type = P2.product_type
WHERE P1.sale_price > P2.avg_price;
自然连结——它其实是内连结的一种特例--当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件.（它会将两个表中的公共部分提到前面）
SELECT * FROM shopproduct NATURAL JOIN Product
使用内连结求 Product 表和 Product2 表的交集.
SELECT P1.*
FROM Product AS P1
INNER JOIN Product2 AS P2
ON (P1.product_id = P2.product_id
AND P1.product_name = P2.product_name
AND P1.product_type = P2.product_type
AND P1.sale_price = P2.sale_price
AND P1.regist_date = P2.regist_date)
外连接-左连接
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
FROM Product AS P
LEFT OUTER JOIN ShopProduct AS SP
ON SP.product_id = P.product_id;
最终的结果中会包含主表内所有的数据.指定主表的关键字是 LEFT 和 RIGHT.顾名思义,使用 LEFT JOIN 时 FROM 子句中写在左侧的表是主表,使用 RIGHT JOIN时右侧的表是主表
多表内连接——本质上是将内连接运用到多个表
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';
多表外连接——本质上是将内连接运用到多个表
SELECT P.product_id
,P.product_name
,P.sale_price
,SP.shop_id
,SP.shop_name
,IP.inventory_quantity
FROM Product AS P
LEFT OUTER JOIN ShopProduct AS SP
ON SP.product_id = P.product_idLEFT OUTER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id
On子句进阶——非等值连结
每种商品使用自左连结, 找出比该商品售价价格更低或相等的商品
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
ORDER BY P1.sale_price,P1.product_id
交叉连接
无论是外连结内连结, 一个共同的必备条件就是ON 子句, 用来指定连结的条件. 如果不适用, 结果会有很多行. 在连结去掉 ON 子句, 就是交叉连结(CROSS JOIN)
-- 1.使用关键字 CROSS JOIN 显式地进行交叉连结SELECT SP.shop_id——用法1
,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——用法2
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
FROM ShopProduct AS SP , Product AS P;
连结的特定语法和过时语法
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
WHERE SP.product_id = P.product_id;
