Mysql外键与连结

外键是某个表中的某一列，它包含另一个表的主键值，定义了两个表之间的关系，维护了引用完整性,外键定义在其中的表叫做参照表（从表，引用表），外键所指向的表叫做被参照表（主表），参照表跟随被参照表更改, 必须保证这个引用表的外键与主表的主键更新一致。如下，products是参照表，vendors是被参照表，外键保证，确保products表中插入的vend_id都在vendors中作为主键出现了，所以确保了products中的vend_id集合是vendors中的vend_id的子集。

创建外键的语句，在表products的vend_id列上建立表vendors(vend_id)的外键：

1 在建表时定义：FOREIGN KEY （vend_id）REFERENCES vendors(vend_id)

2 修改表：alter table products add constraint fk_products_vendors foreign key(vend_id) references vendors(vend_id);

以下结构对于外键是允许的：

1 一个外键可以包含1个或多个列，如果外键包含两个列，则意味着被参照表的主键必须也包含两列或以上，即外键必须是被参照表的主键的子集

2 一个列可以是几个不同的外键的组合部分

3 一个主键中列的子集，或者主键中列的整个集合，可以构成一个外键。

参照表和被参照表可以是同一个表，这种叫做自参照完整性

外键上的动作（保持从表的外键与参照的主表的主键一致）：

默认动作是on update restrict; on delete restrict;意思是如果更新或删除vendors的vend_id(在products中已经出现的vend_id)则动作会被拒绝，如果向products中添加一个vendors中没有的vend_id则这个动作也被拒绝；

但如果动作是cascade:  on update cascade; on delete cascade;则如果更改或删除vendors中的vend_id的行，则products中对应的行更新或删除。

set null:如果vendors表中删除一个vend_id,则products中对应的行的vend_id被置为空值， SET DEFAULT：设默认值

连结从多个表中返回一组输出，连结在运行时选择关联的表中正确的行。没有连结条件的表关系返回的结果是笛卡尔积。行数为表的行数之积。

内部连结也成为等值连结，基于两个表之间的相等测试，最常使用，语法inner join...on...

select vend_name, prod_name, prod_price from vendors inner join products on  vendors.vend_id = products.vend_id; 

等价于：

select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id;

在对表进行联结时，至少有一个列出现在不止一个表中（被联结的列）。标准的联结（内部联结）返回所有数据，甚至相同的列多次出现。

如果使用内部连结，需要自己避免输出重复列工作，如

SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB';

以上通配符只对第一个表示用，其他所有列明确列出，没有重复的列被被检索出来。

等值连结会包括重复的行，如vend_id在products与vendors中都有出现，如果select *则重复出现：

mysql> select * from products inner join vendors on products.vend_id = vendors.vend_id limit 1\G
*************************** 1. row ***************************
     prod_id: ANV02
     vend_id: 1001
   prod_name: 1 ton anvil
  prod_price: 9.99
   prod_desc: 1 ton anvil, black, complete with handy hook and carrying case
     vend_id: 1001
   vend_name: Anvils R Us
vend_address: 123 Main Street
   vend_city: Southfield
  vend_state: MI
    vend_zip: 48075
vend_country: USA

如果在这种情况下要避免重复列，则需要自己避免，一个表可以使用*，另外一个表显示指定需要输出的列，如果要避免出现重复的列，有以下方法：

1 使用using关键字代替on, using(common list)直接代替相等操作

mysql> select * from products inner join vendors using(vend_id) limit 1\G

2 使用自然连结，natural join (table),自然联结排除多次出现，使每个列只返回一次，

mysql> select * from products natural join vendors limit 1\G
*************************** 1. row ***************************
     vend_id: 1001
     prod_id: ANV02
   prod_name: 1 ton anvil
  prod_price: 9.99
   prod_desc: 1 ton anvil, black, complete with handy hook and carrying case
   vend_name: Anvils R Us
vend_address: 123 Main Street
   vend_city: Southfield
  vend_state: MI
    vend_zip: 48075
vend_country: USA

即以下的语法等价：

natural join = inner join..using(common_list)
natural left/right join = left/right join using(common_list)

而且可以连结3个表4个表，都是natural join(table),都可以去重。例如对于四个表的合并，以下两句等价，而且可以去重
natural:select * from virus natural join virus_head natural join virus_sec natural join virus_dll;
inner join using:select * from virus inner join virus_head using(file_id, file_name) inner join virus_sec using(file_id, file_name) inner join virus_dll using(file_id, file_name)
以下不能去重
inner join on:select * from virus inner join virus_head on virus.file_id=virus_head.file_id and virus.file_name = virus_head.file_name inner join virus_sec on virus.file_id=virus_sec.file_id and virus.file_name = virus_sec.file_name inner join virus_dll on virus.file_id=virus_dll.file_id and virus.file_name = virus_dll.file_name

自联结：使用表别名,

例找出与id为'DTNTR'相同的生产厂商生产的产品：

子查询：SELECT prod_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR')

等价的自联结形式，将这个表利用表别名表示成两个表，从而表示成等值连结：

SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNRR';

p1.vendi_id = p2.vend_id

用来连接这两个别名表（其实是一个表）, 用p2.prod_id = 'DTNRR'来过滤

外部联结：联结中包含了在相关表中没有关联行的行。

SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

LEFT OUTER JOIN表明选择左边表的所有行，RIGHT OUTER JOIN：选择右边表的所有的行

OUTER JOIN:指定联结的类型，必须指出LEFT或RIGHT，表明从LEFT或RIGHT的表中选择所有行。

使用带聚集函数的联结：

SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;

在一个连结中可以包含多个表，甚至对于每个连结可以采用不同的联结类型。虽然这样做是合法的，但应该在一起使用前分别测试每个联结。可以排除故障。

组合查询UNION：将多条SELECT语句组合成一个结果集。

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) ORDER BY vend_id, prod_price;

UNION中的每个查询必须包含相同的列，表达式或聚集函数（各列可以以不同的顺序出现），数据类型必须兼容，如果不相同则可以隐式转换就可以。

UNION默认自动取消了重复的行，与where子句效果相同，如果要保留全部，则用UNION ALL保留所有的行。

对结果集排序，必须出现在最后一条SELECT语句之后，并且这个排序是针对整个结果集排序的，如

mysql> 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) order by vend_id, prod_price;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | ANV01   |       5.99 |
|    1002 | OL1     |       8.99 |
|    1003 | FC      |       2.50 |
|    1003 | TNT1    |       2.50 |
|    1003 | SLING   |       4.49 |
+---------+---------+------------+
8 rows in set (0.00 sec)