1.表的加减法
1)集合运算
UNION, intersect, except 集合运算符
交集,差集,并集,子集
2)表的加法 - UNION
UNION 将两个表合并, 并去除重复记录
UNION 与 OR
在同一个表中,UNION 语句 也可以用 OR 实现
不同表中,只能使用 UNION
SELECT *
FROM product
WHERE sale_price / purchase_price < 1.3
OR sale_price / purchase_price IS NULL;
SELECT *
FROM product
WHERE sale_price / purchase_price < 1.3
UNION
SELECT *
FROM product
WHERE sale_price / purchase_price IS NULL;
UNION 默认去重, 添加 ALL 可不去重
SELECT product_id, product_name, product_type, purchase_price, regist_date
FROM product
WHERE sale_price < 1.5 * purchase_price
UNION ALL
SELECT product_id, product_name, product_type, purchase_price, regist_date
FROM product
WHERE sale_price < 1000;
隐式数据类型转换
数据不完全相同时,通过隐式数据转换
将两个不同的列放在一起
-- 时间日期类型和字符串,数值以及缺失值均能兼容
SELECT SYSDATE(), SYSDATE(), SYSDATE()
UNION
SELECT 'chars', 123, null;
3)MySQL 8.0 不支持 交运算 INTERSECT
可以通过 INNER JOIN 来求交集
4) 差集,补集,表的减法
减法,except, 还不支持
可以使用 NOT IN 实现
SELECT *
FROM Product
WHERE product_id NOT IN
(SELECT product_id FROM Product2)
SELECT product_id, product_name, sale_price, purchase_price, regist_date
FROM product
WHERE sale_price > 2000
AND
product_id NOT IN
(SELECT product_id FROM product
WHERE
sale_price >= purchase_price * 1.3 );
AND 与 INTERSECT
对称差
两个集合A,B的对称差,
是指那些仅属于A或仅属于B的元素构成的集合
首先使用UNION求两个表的并集,
然后使用INTERSECT求两个表的交集,
然后用并集减去交集, 就得到了对称差
SELECT *
FROM Product
WHERE product_id NOT IN (SELECT product_id
FROM Product2)
仅 product 的元素
UNION
SELECT *
FROM Product2
WHERE product_id NOT IN (SELECT product_id
FROM Product)
仅 product2 的元素
SELECT *
FROM product
WHERE sale_price > purchase_price * 1.5
AND sale_price < 1500;
2.连结(JOIN)
添加列, 将其他表中的列添加到 一张表中
1)内连结 INNER JOIN
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>
内连结从两个表获取信息
注:
FROM 子句中使用多张表
使用 ON 指定连结 条件,必不可少
SELECT 最好按照 表名, 列名 格式
-- 内连结从两个表获取信息系
SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, p.product_type
, p.sale_price
, sp.quantity
FROM shop_product
as sp
INNER JOIN product
AS p
ON sp.product_id = p.product_id;
结合 WHERE 使用内连结
WHERE 语句需要写在 ON 子句后面
执行顺序
FROM 子句->WHERE 子句->SELECT 子句
SELECT *
FROM
(SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, p.product_type
, p.sale_price
, sp.quantity
FROM shop_product
as sp
INNER JOIN product
AS p
ON sp.product_id = p.product_id) AS sep1
WHERE shop_name = '东京'
AND product_type = '衣服';
-- WHERE 子句在 FROM 子句 执行之后 执行
-- 即 在内连结中,INNER JOIN 得到新的表后才执行 WHERE
SELECT p1.shop_id
, p1.shop_name
, p1.product_id
, p.product_type
, p.sale_price
, p1.quantity
FROM shop_product AS p1
INNER JOIN product AS p
ON p1.product_id = p.product_id
WHERE p1.shop_name = '东京'
AND p.product_type = '衣服';
结合 GROUP BY 使用内连结
需要根据分组区别对待
可以再内连结之前使用 GROUP BY
分组列和被聚合列 不在同一张表,且二者都为被用于
连接两张表,只能先连结,再聚合
SELECT sp.shop_id
, sp.shop_name
, MAX(p.sale_price) AS max_price
FROM shop_product AS sp
INNER JOIN product AS p
ON sp.product_id = p.product_id
GROUP BY sp.shop_id, sp.shop_name;
自连结
将一张表与自身连结
自连结可以是内连结也可以是外连结
内连结 与 关联子查询
-- 先 分类计算 平均价格
SELECT product_type
, AVG(sale_price) AS avg_price
FROM product
GROUP BY 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 条件
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
2) 外连结
根据外连结种类有选择地保留无法匹配的行
内连结只能取出 同时存在于两张表的数据
外连结,只要在一张表中就可以取出
使用 LEFT 时 FROM 子句中写在左侧的表是主表,
使用 RIGHT 时右侧的表是主表
左连结保存左表中无法用ON 匹配到的行,对应右表为缺失值
右连结保存右表中无法按ON匹配到的行对,应左表为缺失值
全外连结则会同时保存两个表中无法按照 ON子句匹配到的行
相应的另一张表中的行用缺失值填充
-- 左连结
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)>
左连结 与 右连结
由于连结时可以交换左表和右表的位置,
因此左连结和右连结并没有本质区别
SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, p.product_name
, p.sale_price
FROM product AS p
LEFT OUTER JOIN shop_product AS sp
ON sp.product_id = p.product_id;
-- 左连结 使用 WHERE
SELECT p.product_id
, p.product_name
, p.sale_price
, sp.shop_id
, sp.shop_name
, sp.quantity
FROM shop_product AS sp
RIGHT OUTER JOIN product AS p
ON sp.product_id = p.product_id
WHERE sp.quantity < 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 (
SELECT *
FROM shop_product
WHERE quantity < 50) AS sp
ON sp.product_id = p.product_id;
3.多表连结
1)多表内连结
INNER JOIN
SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, p.product_name
, p.sale_price
, ip.inventory_quantity
FROM
shop_product 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';
2) 多表外连结
OUTER JOIN
-- 多表外连结
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 shop_product AS sp
ON sp.product_id = p.product_id
LEFT OUTER JOIN InventoryProduct AS ip
ON sp.product_id = ip.product_id;
4) ON 子句进阶 -- 非等值连接
5) 交叉连结 --- 笛卡尔积
去掉 ON 子句
笛卡尔积:用集合 A 中的元素和 集合B中 元素
组成一个有序的组合
交叉笛卡尔积 是在横向对 表进行扩张,即增加新的列
功能和连结一致
没有ON 的限制,可能会出现一些无意义的检索结果
-- 交叉连结
-- 对两张表的数据进行 行 交叉组合,新的组合 行数会增多
-- 1
SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, p.product_name
, p.sale_price
FROM shop_product AS sp
CROSS JOIN product AS p;
-- 2
SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, p.product_name
, p.sale_price
FROM shop_product AS sp, product AS p;
连结与笛卡尔积
笛卡尔积可以看做一种特定连结
SELECT sp.*, p.*
FROM shop_product AS sp
CROSS JOIN product AS p;
-- 笛卡尔积 增加条件可以 变为内连结
SELECT sp.*, p.*
FROM shop_product AS sp
CROSS JOIN product AS p
WHERE sp.product_id = p.product_id;
SELECT sp.*, p.*
FROM shop_product AS sp
INNER JOIN product AS p
ON sp.product_id = p.product_id;
6)连结的语法
-- 过时语法
-- 缺点:
-- 不能判断是内连结 还是外连结
-- 连结条件在 WHERE 子句中,无法分辨连结条件
-- 可能会被替代
SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, p.product_name
, p.sale_price
FROM shop_product AS sp
, product AS p
WHERE sp.product_id = p.product_id
AND sp.shop_id = '000A';