一.表的加减法
1. 集合运算
集合在数学领域表示“(各种各样的)事物的总和”,在数据库领域表示记录的集合。具体来说,表、视图和查询的执行结果都是记录的集合。
2. 表的加法
- 表的加法:UNION(并集):集合运算符会除去重复的记录。
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;
【例子】找出 Product 和 Product2 中售价高于 500 的商品的基本信息.
SELECT
purchase_price
FROM
Product
WHERE purchase_price>500
UNION
SELECT
purchase_price
FROM
Product2
WHERE purchase_price>500
这里发现使用UNION对两个查询结果取并集和在一个查询结果中使用WHERE子句,然后使用OR连接两个查询条件的结果相同。
这并不意味着UNION没有必要,当需要对两个不同的表取并集的时候,UNION就派上用场了。
- 若想要在集合运算结果中包含重复值,需在UNION后加ALL关键字。
-- 保留重复行
SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;
【例子】商店决定对product表中利润低于50%和售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集. 查询结果类似下表:
SELECT *
FROM Product
WHERE
sale_price < 1000 UNION ALL SELECT * FROM Product WHERE sale_price > 1.5 * purchase_price
3. 差集 补集
- 记录的减法EXCEPT,使用方法也和UNION相同,但不适用于MySQL。但使用NOT IN可以达到相同的效果。
【例子】找出只存在于Product表但不存在于Product2表的商品.
SELECT *
FROM Product
WHERE product_id NOT IN (SELECT product_id
FROM Product2)
【例子】使用NOT谓词进行集合的减法运算, 求出Product表中, 售价高于2000,但利润低于30%的商品, 结果应该如下表所示.
SELECT *
FROM Product
WHERE sale_price > 2000
AND product_id NOT IN (SELECT product_id
FROM Product
WHERE sale_price<1.3*purchase_price)
- 选取表中公共部分用INTERSECT,使用和UNION相同,但不适用于MySQL。但对于同一个表两个查询结果,它们的交集可以等价将两个查询条件适用AND连接实现
【例子】
使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品,查询结果如下所示
SELECT *
FROM product
WHERE sale_price<1500
AND sale_price >1.5*purchase_price
4. 对称差
- 对称差是指那些仅属于A或仅属于B的元素构成的集合.
- 求对称差:先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差.
【例子】使用Product表和Product2表的对称差来查询哪些商品只在其中一张表, 结果类似于:
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)
1. 作为运算对象的记录的列数必须相同;
2. 作为运算对象的记录中列的类型必须一致;
3. 可以使用任何SELECT语句,但ORDER BY子句只能在最后使用一次。
二.联结(JOIN)
什么是联结:简单来说,联结是一种机制,用来在一条SELECT语句中关联表,通过单条SELECT语句检索出存储在多个表中的数据。
1.使用表别名
别名除了用于列名和字段外,还可以给表名起别名,这么做的理由是:1. 缩短SQL语句
2. 允许在单条SELECT语句中多次使用相同的表。需要注意的是表别名只在查询中使用
2. 内联结
- 内联结 INNER JOIN:在 ON 之后指定两张表联结所使用的列(联结键)
进行内联结时必须使用ON子句,并且要书写在FROM和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;
注意:
- 进行连结时需要在 FROM 子句中使用多张表.
- 必须使用 ON 子句来指定连结条件.
- SELECT 子句中的列最好按照 表名.列名 的格式来使用.
- 如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边.
对于上个查询要求,加入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 = '衣服' ;
3. 外联结
- 许多联结将一个表中的行与另一个表中的行相关联,但有时候需要包含没有关联行的那些行,
例如:列出所有产品以及订购数量,包括没有人订购的产品。
联结包含了那些在相关表中没有关联行的行,称为外联结。
- 左外联结
左外联结的结果集中包含左表(JOIN关键字左边的表)中所有记录,然后左表按照联结条件与右表进行联结,如果右表中没有满足联结条件的记录,则结果集中右表中的相应行数数据填充为NULL。 - 右外联结
右外联结的结果集中包含满足联结条件的所有数据和右表(JOIN关键字右边的表)中不满足条件的数据,左表中的相应行数据为NULL
4. 多表联结
SQL对一条SELECT语句中可以联结的表的数量没有限制,创建联结的基本规则也相同,先列出所有表,然后定义表之间的关系。
-
【例子】
显示编号为20005的订单的所有物品,订单物品存在orderitems表。这里FROM子句列出三个表,而WHERE子句定义了这两个联结条件,第三个联结条件用来过滤出订单20005中的产品。
性能考虑 :MySQL在运行时关联指定的每个表以处理联结,但这种处理可能非常消耗资源,不要联结不必要的表,联结的表越多,性能下降的越厉害。 -
不等值联结
在WHERE子句中用来连接两个表的条件称为连接条件。如果连接条件中的连接运算符是=时,称等值连接,如果是其他的运算符,则是不等值连接。
5. 交叉联结
交叉连接不带 WHERE 子句,它返回被连接的两个表所有数据行的笛卡尔积。从一张表中循环取出每一条记录,每条记录都会去另一张表中匹配每一条记录,匹配结果一定保留(因为无条件,如果有条件,则只保留满足条件的结果)。
假设 A 表有 n 条记录,B 表有 m 条记录,则结果为 n * m 条记录。
SELECT * FROM product CROSS JOIN product2