SQL编程学习04

一、知识点学习

1、对称差

在MySQL 8中，两个集合的对称差可以通过差集运算与并运算来实现：
A-B 并 B-A
在其他数据库中，可以使用两个集合的并集-两个集合的交集来得到对称差集

2、自然连接（natural join）

自然连接是内连接的一种特例，当两个表进行自然连接的时候，会按照两个表中都包含的列名进行等值内连接，此时不需要使用on来指定连接条件。

注意：

• 如果自然连接的两个表仅是字段名称相同，但数据类型不同，那么将会返回一个错误
• 在自然连接时，如果做连接条件的某字段值为空，则结果中不会出现该行数据。是因为自然连接做的是等值连接，但空值使用等值判断时为False，而返回的都是True的结果。

疑问：当两张表中存在两个及以上的相同列名，是做类似笛卡尔积的等值连接吗？

3、交集

• 可以使用两个集合的并集-两个集合的对称差集来实现
• 也可以使用内连接来实现

4、内连接与外链接

内连接：查询结果中只包含表内信息的连接
外连接：查询结果中包含原表中不存在（在原表之外）的信息的连接

5、交叉连接（笛卡尔积）

将两个表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积。例如表一有13条记录，表二有8条记录，则结果就有13x8=104条记录。

二、一些扩展

1、表的复制：

• 复制表的结构及内容

create table 新表表名 as select * from 旧表表名

• 只复制表的结构而不复制表的内容：

方法一：
create table 新表表名 as select * from 旧表表名 where 1=2; 

方法二：
create table 新表表名 as select * from 旧表表名
truncate table 新表表名;  -- 删除表中的内容

2、UNION 与 UNION ALL：

UNION会对结果集做去重处理，UNION ALL会保留重复记录。

需要注意的点：

• 在使用UNION和UNION ALL来做连接的时候，必须确保所要连接的两个表的字段数量（列数）保持一致。
• 当做连接的上表和下表字段名不一致的时候，是以上表的字段名作为最后查询结果的字段名。

union all的结果：

疑问：使用union的查询效率会比where高吗？

使用union时的执行时间和网络传输时间：0.00048 sec / 0.0000069 sec
使用where时的执行时间和网络传输时间：0.00038 sec / 0.0000079 sec

3、MySQL表名问题

由于看到教程中，一会写product一会写Product，就好奇难道这样不会出现问题么？
结果一尝试，惊了，MySQL中竟然表名的大小写居然没有影响。

4、inner join

• 如果左表有，但通过连接字段右表没有与之对应的，则该数据不会出现在结果表中。
• 如果通过连接字段，左表能对应到右表的多条数据，那么结果表中也会出现多行数据。

5、连接时的执行顺序

FROM --> JOIN --> WHERE --> SELECT
即，先将表进行连接处理之后，再做where条件筛选。

6、一些细节的处理

-- 查找商品库存小于50的商品以及对应的商店
-- 方式一：
select p.product_id, p.product_name, p.sale_price, sp.shop_id, sp.shop_name, sp.quantity
from product p
left join shopproduct sp
on p.product_id = sp.product_id
where sp.quantity < 50 
or sp.quantity is null;  

-- 方式二：
select p.product_id, p.product_name, p.sale_price, sp.shop_id, sp.shop_name, sp.quantity
from product p
left join (select *  from shopproduct where quantity < 50) sp   -- 作为子查询可以筛选出为空的情况
on p.product_id = sp.product_id
;

方式一很容易忘记写or sp.quantity is null的筛选条件，方式二可以避免这样的情况。
补充：在实际处理的时候，可以使用distinct查看下改列值的分布情况，就可以明确是否有NULL值了。

三、练习

在正式开始学习前，先把数据准备好：
（1）创建表product并添加数据：

CREATE TABLE product
(product_id CHAR(4) NOT NULL,
 product_name VARCHAR(100) NOT NULL