MYSQL学习记录（8）

1.表的加减法

1.1 表的加法–UNION

  SELECT product_id,product_name,product_type,sale_price,purchase_price
  from product
  where sale_price > purchase_price*1.5
  UNION
  SELECT product_id,product_name,product_type,sale_price,purchase_price
  from product
  where sale_price <800;
--相当于取了并集，但是去掉了重复值

1.1.1. UNION与OR谓词

效力在同表联结时相同，但联结不同表时必须用UNION。

练习题 :

分别使用 UNION 或者 OR 谓词,找出毛利率不足 30%或毛利率未知的商品

  SELECT product_id,product_name,product_type,sale_price,purchase_price
  FROM product
  WHERE sale_price<purchase_price*1.3
  UNION
  SELECT product_id,product_name,product_type,sale_price,purchase_price
  FROM product
  WHERE sale_price/purchase_price is NULL;
--
  SELECT product_id,product_name,product_type,sale_price,purchase_price
  FROM product
  WHERE sale_price<purchase_price*1.3 OR sale_price/purchase_price is null;
--结果有区别，OR中没有去掉NULL项

1.1.2 包含重复行的集合运算 UNION ALL

在 UNION 的结果中保留重复行的语法,只需要在 UNION 后面添加 ALL 关键字。

UNION ALL

例如, 想要知道 product 和 product2 中所包含的商品种类及每种商品的数量, 第一步,就需要将两个表的商品种类字段选出来, 然后使用 UNION ALL 进行不去重地合并. 接下来再对两个表的结果按 product_type 字段分组计数.

-- 保留重复行
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/purchase_price < 1.5
UNION ALL
SELECT *
FROM product
WHERE sale_price<=1000;

 1.1.3 bag 模型与 set 模型

Bag 是和 set 类似的一种数学结构, 不一样的地方在于: bag 里面允许存在重复元素, 如果同一个元素被加入多次, 则袋子里就有多个该元素.

1.1.4 隐式类型转换

隐式类型转换：在MySQL中，当操作符与不同类型的对象一起使用时，会发生类型转换以使各个操作对象兼容，则会发生转换隐式。

--1与sale_price相连接
SELECT product_id, product_name, '1'
  FROM product
 UNION
SELECT product_id, product_name,sale_price
  FROM product2;

练习题:

使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性.

例如, 以下代码可以正确执行, 说明时间日期类型和字符串,数值以及缺失值均能兼容.

SELECT SYSDATE(), SYSDATE(), SYSDATE()
 
 UNION
 
SELECT 'chars', 123,  null

1.1.5 bag 的交运算

对于两个 bag, 他们的交运算会按照: 1.该元素是否同时属于两个 bag, 2.该元素在两个 bag 中的最小出现次数这两个方面来进行计算. 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的交运算结果就等于 {1,1,2}.

1.2 差集,补集与表的减法

MySQL 不支持 表的减法运算符 EXCEPT.但可使用NOT IN 谓词实现表的减法。

练习题**:**

找出只存在于product表但不存在于product2表的商品.

SELECT *
FROM product
WHERE product_id NOT IN(SELECT product_id FROM product2);

 1.2.1 EXCEPT 与 NOT 谓词

练习题:

使用NOT谓词进行集合的减法运算, 求出product表中, 售价高于2000,但利润低于30%的商品

SELECT * 
FROM product
WHERE  sale_price >2000
AND  product_id NOT IN
 (SELECT product_id  
 FROM product
 WHERE sale_price < purchase_price*1.3 );

1.2.2 EXCEPT ALL 与bag 的差

EXCEPT ALL按照出现次数做减法。

对于两个 bag, 他们的差运算会按照:

1.该元素是否属于作为被减数的 bag,

2.该元素在两个 bag 中的出现次数

这两个方面来进行计算. 只有属于被减数的bag的元素才参与EXCEP ALL运算, 并且差bag中的次数,等于该元素在两个bag的出现次数之差(差为零或负数则不出现). 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的差就等于 {1,3,5,7}.

1.2.3 INTERSECT 与 AND 谓词

对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现.