MySQL 一对多基本操作

实体

所谓实体即为描述我们真实世界的物件，如员工、客户、订单皆属於实体。
在实务上的需求我们可以将实体转换成各种资料表(TABLE)，如员工资料表、客户资料表等。

关系

关系指一个实体在另一个实体之间关联的方式，分为一对一关系、一对多关系、多对一关系、多对多关系。
基本上，实体与关系是用来将事物模组化，并以图形表示，称作ER(Entity-Relationship)图。

其余名词介绍如下图所示。

ER图实例如下图。

键属性

指属性的值在某环境下具有的唯一性，在实体关系图我们会在键属性的名称下加上底线。

主键(Primary Key):
关系型资料库中的一条记录中有好几个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键，不许为空、重复，如身分证字号。

外键(Foreign Key):
资料表的外键是另一表的主键，外键可以重复的，可以是空值，有了他我们可以用来和其他表建立联络。

FOREIGN KEY (customer_id) REFERENCES customers(id)

在create table时，可以利用来加入外键(FK)，并且设置他参照的对象，table_name column_name，而加入外键可以用来建立与主键(PK)的关联，并且约束外键，如插入非空值时，如果主键表中没有这个值，则不能插入、更新时，不能改为主键表中没有的值等等。

CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8, 2),
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
  );

INNER JOIN可以通过结合基於联接两个表(表1和表2)且创建一个新的结果表。

所用之资料

mysql> SELECT * FROM customers;
+----+------------+-----------+----------------+
| id | first_name | last_name | email          |
+----+------------+-----------+----------------+
|  1 | Robin      | Jackman   | [email protected]  |
|  2 | Taylor     | Edward    | [email protected] |
|  3 | Vivian     | Dickens   | [email protected] |
|  4 | Harley     | Gilbert   | [email protected]  |
|  5 | jo         | jo        | [email protected] |
+----+------------+-----------+----------------+

mysql> SELECT * FROM orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
|  1 | 2001-10-12 |  99.12 |           1 |
|  2 | 2001-09-21 | 110.99 |           2 |
|  3 | 2001-10-13 |  12.19 |           1 |
|  4 | 2001-11-29 |  88.09 |           3 |
|  5 | 2001-11-11 | 205.01 |           4 |
|  8 | 2001-12-11 | 100.00 |           4 |
+----+------------+--------+-------------+

必需指定等值连接的条件，而查询结果只会返回符合连接条件的资料。

SELECT table_column1, table_column2...
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

将customers 及orders 结合，并获取所需之资料，而返回的结果条件为order的customer id需等同於customer的id，并且利用group BY去重复customers.id，并组成一组。

而条件部分的on功能等同於where。

SELECT first_name,last_name, SUM(amount) AS total 
FROM customers 
INNER JOIN orders ON orders.customer_id=customers.id 
GROUP BY customers.id;

+------------+-----------+--------+
| first_name | last_name | total  |
+------------+-----------+--------+
| Robin      | Jackman   | 111.31 |
| Taylor     | Edward    | 110.99 |
| Vivian     | Dickens   |  88.09 |
| Harley     | Gilbert   | 305.01 |
+------------+-----------+--------+

LEFT JOIN

当我们碰到一个情况，在customer中有一个客人是没有任何订单，但是我们在join的时候也想要显示出来，此时就可以使用Left Join，左侧资料表 (table_name1) 的所有记录都会加入到查询结果中，即使右侧资料表 (table_name2) 中的连接栏位没有符合的值也一样

SELECT table_column1, table_column2
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

SELECT * FROM customers left join orders on customers.id = orders.customer_id;
+----+------------+-----------+----------------+------+------------+--------+-------------+
| id | first_name | last_name | email          | id   | order_date | amount | customer_id |
+----+------------+-----------+----------------+------+------------+--------+-------------+
|  1 | Robin      | Jackman   | [email protected]  |    1 | 2001-10-12 |  99.12 |           1 |
|  1 | Robin      | Jackman   | [email protected]  |    3 | 2001-10-13 |  12.19 |           1 |
|  2 | Taylor     | Edward    | [email protected] |    2 | 2001-09-21 | 110.99 |           2 |
|  3 | Vivian     | Dickens   | [email protected] |    4 | 2001-11-29 |  88.09 |           3 |
|  4 | Harley     | Gilbert   | [email protected]  |    5 | 2001-11-11 | 205.01 |           4 |
|  4 | Harley     | Gilbert   | [email protected]  |    8 | 2001-12-11 | 100.00 |           4 |
|  5 | jo         | jo        | [email protected] | NULL | NULL       |   NULL |        NULL |
+----+------------+-----------+----------------+------+------------+--------+-------------+

如果不想显示NULL值，可以利用CASE判断式。

SELECT
  first_name,
  last_name,
  case
    when sum(amount) is NULL THEN 0
    else sum(amount)
  END AS total
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
group by
  customers.id;

或是利用函数IFNULL，判断第一个参数是否为NULL，是的话替换成第二个参数的值

SELECT
  first_name,
  last_name,
  IFNULL(SUM(amount), "87jojo") AS total
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
group by
  customers.id;

RIGHT JOIN

与LEFT JOIN差别在於，LEFT JOIN是以customers table为基础，而RIGHT JOIN是以order table为基础。

mysql> SELECT * FROM customers right join orders on customers.id = orders.customer_id;
+------+------------+-----------+----------------+----+------------+--------+-------------+
| id   | first_name | last_name | email          | id | order_date | amount | customer_id |
+------+------------+-----------+----------------+----+------------+--------+-------------+
|    1 | Robin      | Jackman   | [email protected]  |  1 | 2001-10-12 |  99.12 |           1 |
|    1 | Robin      | Jackman   | [email protected]  |  3 | 2001-10-13 |  12.19 |           1 |
|    2 | Taylor     | Edward    | [email protected] |  2 | 2001-09-21 | 110.99 |           2 |
|    3 | Vivian     | Dickens   | [email protected] |  4 | 2001-11-29 |  88.09 |           3 |
|    4 | Harley     | Gilbert   | [email protected]  |  5 | 2001-11-11 | 205.01 |           4 |
|    4 | Harley     | Gilbert   | [email protected]  |  8 | 2001-12-11 | 100.00 |           4 |
+------+------------+-----------+----------------+----+------------+--------+-------------+

JOIN有点类似交集的概念。
如下图所示

ON DELETE

当我们想要删除有被其他table的外键参照的column时，是无法删除的，比如此处我们想删除customers中的资料，但由於orders中有外键参照她，所以无法删除。

CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100) 
);

CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id)
        REFERENCES customers(id)
);

mysql> DELETE FROM customers where id="1";
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test7`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

此时，我们只需在创建orders时，加上ON DELETE CASCADE即可。
删除customers资料的同时，也会将其删除orders对应的资料。

CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id)
        REFERENCES customers(id)
        ON DELETE CASCADE
);

mysql> DELETE FROM customers where id="1";
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM customers;
+----+------------+-----------+----------------+
| id | first_name | last_name | email          |
+----+------------+-----------+----------------+
|  2 | Taylor     | Edward    | [email protected] |
|  3 | Vivian     | Dickens   | [email protected] |
|  4 | Harley     | Gilbert   | [email protected]  |
+----+------------+-----------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
|  2 | 2001-09-21 | 110.99 |           2 |
|  4 | 2001-11-29 |  88.09 |           3 |
|  5 | 2001-11-11 | 205.01 |           4 |
+----+------------+--------+-------------+
3 rows in set (0.00 sec)