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   | roj@gmail.com  ||  2 | Taylor     | Edward    | taed@gmail.com ||  3 | Vivian     | Dickens   | vidi@gmail.com ||  4 | Harley     | Gilbert   | hgi@gmail.com  ||  5 | jo         | jo        | jojo@gmail.com |+----+------------+-----------+----------------+

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_name1INNER JOIN table_name2ON 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_column2FROM table_name1LEFT JOIN table_name2ON 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   | roj@gmail.com  |    1 | 2001-10-12 |  99.12 |           1 ||  1 | Robin      | Jackman   | roj@gmail.com  |    3 | 2001-10-13 |  12.19 |           1 ||  2 | Taylor     | Edward    | taed@gmail.com |    2 | 2001-09-21 | 110.99 |           2 ||  3 | Vivian     | Dickens   | vidi@gmail.com |    4 | 2001-11-29 |  88.09 |           3 ||  4 | Harley     | Gilbert   | hgi@gmail.com  |    5 | 2001-11-11 | 205.01 |           4 ||  4 | Harley     | Gilbert   | hgi@gmail.com  |    8 | 2001-12-11 | 100.00 |           4 ||  5 | jo         | jo        | jojo@gmail.com | NULL | NULL       |   NULL |        NULL |+----+------------+-----------+----------------+------+------------+--------+-------------+

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

SELECT  first_name,  last_name,  case    when sum(amount) is NULL THEN 0    else sum(amount)  END AS totalFROM customersLEFT JOIN orders ON orders.customer_id = customers.idgroup by  customers.id;

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

SELECT  first_name,  last_name,  IFNULL(SUM(amount), "87jojo") AS totalFROM customersLEFT JOIN orders ON orders.customer_id = customers.idgroup 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   | roj@gmail.com  |  1 | 2001-10-12 |  99.12 |           1 ||    1 | Robin      | Jackman   | roj@gmail.com  |  3 | 2001-10-13 |  12.19 |           1 ||    2 | Taylor     | Edward    | taed@gmail.com |  2 | 2001-09-21 | 110.99 |           2 ||    3 | Vivian     | Dickens   | vidi@gmail.com |  4 | 2001-11-29 |  88.09 |           3 ||    4 | Harley     | Gilbert   | hgi@gmail.com  |  5 | 2001-11-11 | 205.01 |           4 ||    4 | Harley     | Gilbert   | hgi@gmail.com  |  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    | taed@gmail.com ||  3 | Vivian     | Dickens   | vidi@gmail.com ||  4 | Harley     | Gilbert   | hgi@gmail.com  |+----+------------+-----------+----------------+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)