表的加减法
什么是集合运算
- 集合在数学领域表示“(各种各样的)事物的总和”,在数据库领域表示记录的集合
- 表、视图和查询的执行结果都是记录的集合
- 集合运算,就是对满足同一规则的记录进行的加减等四则运算
- 进行集合运算的运算符称为集合运算符
表的加法
- UNION(并集)
- 集合运算符会除去重复的记录
-- 使用UNION对表进行加法运算 SELECT product_id, product_name FROM Product UNION SELECT product_id, product_name FROM Product2;
集合运算的注意事项
- ①作为运算对象的记录的列数必须相同
- ②作为运算对象的记录中列的类型必须一致
- ③可以使用任何SELECT语句,但ORDER BY子句只能在最后使用一次
-- 列数不一致时会发生错误 SELECT product_id, product_name FROM Product UNION SELECT product_id, product_name, sale_price FROM Product2; -- 数据类型不一致时会发生错误 SELECT product_id, sale_price FROM Product UNION SELECT product_id, regist_date FROM Product2; -- ORDER BY子句只在最后使用一次 SELECT product_id, product_name FROM Product WHERE product_type = '厨房用具' UNION SELECT product_id, product_name FROM Product2 WHERE product_type = '厨房用具' ORDER BY product_id;
包含重复行的集合运算
- ALL选项
- 在集合运算符中使用ALL选项,可以保留重复行
-- 保留重复行 SELECT product_id, product_name FROM Product UNION ALL SELECT product_id, product_name FROM Product2;
选取表中公共部分
- INTERSECT(交集)
- INTERSECT 应用于两张表,选取出它们当中的公共记录
-- 使用INTERSECT选取出表中公共部分 SELECT product_id, product_name FROM Product INTERSECT SELECT product_id, product_name FROM Product2 ORDER BY product_id;
记录的减法
- EXCEPT(差 集)
- 减法运算中减数和被减数的位置不同,所得到的结果也不相同
-- 使用EXCEPT对记录进行减法运算 SELECT product_id, product_name FROM Product EXCEPT SELECT product_id, product_name FROM Product2 ORDER BY product_id;
联结(以列为单位对表进行联结)
什么是联结
- 联结(JOIN)运算
- 将其他表中的 列添加过来,进行“添加列”的运算
- 实际上,期望得到的数据往往会分散在不同的表之中。使用联结就可以从多张表(3 张以上的表也没关系) 中选取数据了
内联结
INNER JOIN
所谓联结运算,以A中的列作为桥梁,将 B中满 足同样条件的列汇集到同一结果之中
内联结要点
①FROM子句
- FROM ShopProduct AS SP INNER JOIN Product AS P
- 进行联结时需要在FROM子句中使用多张表
②ON子句
- ON SP.product_id = P.product_id
- 在 ON 之后指定两张表联结所使用的列(联结键)
- 进行内联结时必须使用ON子句,并且要书写在FROM和WHERE之间
③SELECT子句
- SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
- 使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写
内联结和WHERE子句结合使用
- 联结运算将满足相同规则的表联结起来时,WHERE、 GROUP BY、HAVING、ORDER BY 等工具都可以正常使用
- 将联结之后的结果想象为新创建出来的一张表,对这张表使用 WHERE 子句等工具
- 这张“表”只在 SELECT 语句执行期间存在,SELECT 语 句执行之后就会消失。如果希望继续使用这张“表”,将它创建成视图
-- 将两张表进行内联结 SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price FROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id = P.product_id; -- 内联结和WHERE子句结合使用 SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price FROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id = P.product_id WHERE SP.shop_id = '000A';
外联结
OUTER JOIN
外联结要点
①选取出单张表中全部的信息
②每张表都是主表吗
把哪张表作为主表,最终的结果中会包含主表内所有的数据
指定主表的关键字是 LEFT 和 RIGHT
- 使用LEFT 时FROM 子句中写在左侧的表是主表,使用RIGHT 时右侧的表是主表
3张以上的表的联结
交叉联结
CROSS JOIN(笛卡 儿积)
- 对满足相同规则的表进行交叉联结的集合运算符
进行交叉联结时无法使用内联结和外联结中所使用的ON 子句, 这是因为交叉联结是对两张表中的全部记录进行交叉组合,因此结果中 的记录数通常是两张表中行数的乘积
联结的特定语法和过时语法
- 对于联结的过时语法和特定语法,虽然不建议使用,但还是希望能够读懂。
-- 将两张表进行外联结 SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P ON SP.product_id = P.product_id; -- 对3张表进行内联结 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 SP.shop_id, SP.shop_name, SP.product_id, P.product_name FROM ShopProduct AS SP CROSS JOIN Product AS P; -- 使用过时语法的内联结 SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price FROM ShopProduct SP, Product P WHERE SP.product_id = P.product_id AND SP.shop_id = '000A';