[Mysql]表关系和表联结

表关系

先了解一个概念，外键：
在一个表中，定义一个字段，这个字段中存储的数据是另外一张表中的主键
就是在一个表中的字段，代表着这个数据属于谁

了解：
    外键实现的方式，有两种：物理外键、逻辑外键
    
    物理外键：
        就是在创建表时，就指定这个表中的字段是一个外键，并且强关联某个表中的某个字段
        需要在定义字段时，使用sql语句来实现。
        但是这种方法也有很大的缺点，就是不灵活。比如想删除某个表中的某个数据，如果
        他和另一个表有强关联，那么很可能会删除失败，或者造成其他的问题。
        
    逻辑外键：
        就是在表中创建一个普通的字段，没有强关联关系，需要通过程序逻辑来实现

Mysql中的表关系有三种：一对一，一对多，多对多

一对一

就是在一个表中的数据，对应着另外一张表中的一个数据，只能有一个。
比如将员工的工资和个人信息拆分成两个表，但是每一个记录对应的员工实际上只有一个，这就是一对一关系。

一对多

在一个表中的一条数据对应着另外一个表中的多条数据或者说在一个表中的多条数据，对应着另外一张表中一个数据。
比如种类对应多个实物。例如手机对应多个品牌。

多对多

例如一本书，有多个标签，同时每一个标签下又对应多本书

表联结实际操作

联结(join)是一种数据库操作，用于在多表中进行查询。联结的时候一定要做好条件处理，也就是where或者on的部分，不然会变成笛卡尔积的查询。（意思就是会把所有两张表结合的可能性都作为查询结果输出。比如表A有2个查询结果，表B三个，那么笛卡尔积查询就是2*3=6个查询结果）

联结的创建⾮常简单，规定要联结的所有表以及它们如何关联即可。
在引⽤的列可能出现⼆义性时，必须使⽤完全限定列名（⽤⼀个点分隔的表名和列名）。比如引用了order和product两个表，里面都有product_id这个列的时候，要使用order.product_id和product.product_id作区分。

案例： 查询出订单号为20005的订单中购买的商品及对应的产品供应商信息

1.用where的写法
select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
where products.vend_id = vendors.vend_id
and orderitems.prod_id = products.prod_id
and order_num = 20005;

2.改写为 join 的语法
select prod_name,vend_name,prod_price,quantity
from orderitems
join products on orderitems.prod_id = products.prod_id
join vendors on products.vend_id = vendors.vend_id
where order_num = 20005;

两个从理解上没有很大的困难，on和where的主要区别在于where是将select结果求出来后进行where筛选，on则是做虚拟表联结然后返回结果集

联结除了最简单的join外还有left join和right join。主要的区别是join只会找到符合条件的，举个例子，比如说我想要看某个用户的购物记录，一个表为用户，一个表为购买记录。假设用户A其实没有购买，那么在购买记录的表上就不存在A的记录。在这种情况下用join就会忽略掉A。如果这时候要求count()所有用户的购买数，包括0的时候就需要用到左右联结了。左右联结的区别是，左联结以左边的表为基准，右联结则是以右边的表。

比如这道题，就应该以用户表为基准，去关联查询订单表数据
select customers.cust_id,count(orders.order_num) as nums
from customers left join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;

如果用右联结，那么查询结果和直接join其实一样
select customers.cust_id,count(orders.order_num) as nums
from customers right join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;

当然写成下面这样也是对的
select customers.cust_id,count(orders.order_num) as nums
from orders right join customers
on customers.cust_id = orders.cust_id
group by customers.cust_id;

Union组合查询

MySQL也允许执⾏多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。
这些组合查询通常称为并（union）或复合查询（compound query）。

UNION规则

1.UNION必须由两条或两条以上的SELECT语句组成，语句之间⽤关键字UNION分（因此，如果组合4条SELECT语句，将要使⽤3个UNION关键字）。

2.UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）

3.列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值int和float类型或不同的⽇期类型，int和string就不行）。

例题：假如需要价格⼩于等于5的所有物品的⼀个列表，⽽且还想包括供应商1001和1002⽣产的所有物品。
第一种，分布查询

第二种联合查询
select vend_id,prod_id,prod_price from products where prod_price <= 5
union
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);

等效于

select vend_id,prod_id,prod_price from products where prod_price <= 5 or vend_id in(1001,1002);

注意，结果会自动去重，所以本来是9条但是只有8条。如果不想去重可以用union all代替union。