概述

本文主要介绍表的关系及相关查询操作

P.S.：译自SQL for Beginners: Part 3 – Database Relationships¹

Database Relationships

One to One Relationships

一对一意味着两个表之间的关联只存在于从表A的任意一行至多在表B中拥有一行，其中应用到了FOREIGN KEY，用于关联从表

One to Many and Many to One Relationships

最常用的关联类型
创建一对多的关联都是关于主键（PRIMARY KEY），然后还要添加一个外键（FOREIGN KEY）建立表之间的关联
通常的方式是在一对多关联的从表（对应多的表）里插入一个外键

Many to Many Relationships

在一些情况下，关联的两边需要多个实例，即一个表的多行可以相互映射到另一个表的多行
多对多关联的好设计是充分利用join表，该表只存放两个关联表的主键值（作为外键的形式）

Self Referencing Relationships

用于一个表需要有自身的关联，类似于一对多关联

FOREIGN KEY

显示定义外键

使用到FOREIGN KEY关键字，两个关联表的对应列必须是同一数据类型

CREATE TABLE customers (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(100)
);

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  amount DOUBLE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

隐式定义外键

没有使用到外键（FOREIGN KEY）定义两个表之间的关联，但数据库仍然会将该列作为外键

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  amount DOUBLE,
  INDEX (customer_id)
);

JOIN Queries

为了从关联表里获取到数据，需要使用到JOIN查询

Cross Join

当没有条件指定时，这是一种默认的JOIN查询类型，意味着第一个表的每一行匹配到第二个表的每一行

1	SELECT * FROM customers JOIN orders;

JOIN关键字可以使用逗号代替

1	SELECT * FROM customers , orders;

Natural Join

这种JOIN查询需要两个关联表有匹配的列名，并且重复列只显示一次

1	SELECT * FROM customers NATURAL JOIN orders;

Inner Join

当一个join条件被指定时，Inner Join开始执行，查询结果类似于Natural Join，区别是Inner Join不处理重复的列

1 2	SELECT * FROM customers JOIN orders WHERE customers.customer_id = orders.customer_id;

ON语句

在把JOIN条件放在分离语句里非常有效

1
2
3

SELECT * FROM customers JOIN orders
ON (customers.customer_id = orders.customer_id)
WHERE order.amount > 15;

USING语句

类似于ON语句，但是更短；事实上更像Natural JOIN，查询结果中重复的行只显示一次

1
2
3

SELECT * FROM customers JOIN orders
USING (customer_id)
WHERE order.amount > 15;

Left(Outer) Join

Left Join是Outer Join的一种类型，如果从第二个表里没有匹配的行被找到，第一个表里的记录仍然被显示

1 2	SELECT * FROM customers LEFT OUTER JOIN orders USING (customer_id);

Note：OUTER关键字是可选的，可以只使用LEFT JOIN代替LEFT OUTER JOIN

Right(Outer) Join

RIGHT OUTER JOIN的工作机制与LEFT OUTER JOIN类似，但是表的顺序倒过来了

1 2	SELECT * FROM customers RIGHTOUTER JOIN orders USING (customer_id);

Note：可以调换两个表的顺序，达到与LEFT OUTER JOIN一样的查询结果

参考方案

1.SQL for Beginners: Part 3 – Database Relationships ↩