MySQL必知必会 -- 联结表

• 外键（foreign key） 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

使用联结表的优点：

• 信息不重复，从而不浪费时间和空间；
• 如果信息变动，可以只更新表中的单个记录，相关表中的数据不用改动；
• 由于数据无重复，显然数据是一致的，这使得处理数据更简单。

联结表的使用

MariaDB [test]> select * from users;
+-------+--------+------+------+-------+------------+-------+
| user  | passwd | sex  | age  | price | math_price | class |
+-------+--------+------+------+-------+------------+-------+
| user2 | 222    | girl | 23   | 155   | 36         |     1 |
| user3 | 333    | boy  | 12   | 78    | 66         |     1 |
| user4 | 444    | boy  | 22   | NULL  | NULL       |     2 |
| user5 | 555    | boy  | 30   | NULL  | 43         |     2 |
| user1 | 111    | boy  | 18   | 35    | 88         |     1 |
| user6 | 666    | girl | 16   | 35    | 43         |     2 |
+-------+--------+------+------+-------+------------+-------+
6 rows in set (0.01 sec)

MariaDB [test]> select * from hour;
+-------+-------+
| hours | user  |
+-------+-------+
|    12 | user2 |
|     8 | user6 |
|     9 | user1 |
|    11 | user3 |
|    10 | user4 |
|    10 | user5 |
+-------+-------+
6 rows in set (0.00 sec)

MariaDB [test]> select class,sex,hours from users,hour where hour.user = users.user order by class;
+-------+------+-------+
| class | sex  | hours |
+-------+------+-------+
|     1 | boy  |    11 |
|     1 | girl |    12 |
|     1 | boy  |     9 |
|     2 | boy  |    10 |
|     2 | boy  |    10 |
|     2 | girl |     8 |
+-------+------+-------+
6 rows in set (0.00 sec)

这里所指定的两个列（class,sex）在users表中，而另一个列（hours）在 hour表中。这两个表用WHERE子句正确联结，WHERE 就是他们的联结条件.

没有联结条件的表关系返回的结果为笛卡儿积。

检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

内联结

对于上面的查询语句，我们可以用内联结的方式表示：

这里使用innor join 的方式联结，用 ON 进行筛选。

多表联结

SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。
首先列出所有表，然后定义表之间的关系。

创建高级联结

使用表别名

MariaDB [test]> select  u.class,u.sex,h.hours from users as u,hour as h where u.user = h.user;
+-------+------+-------+
| class | sex  | hours |
+-------+------+-------+
|     1 | girl |    12 |
|     2 | girl |     8 |
|     1 | boy  |     9 |
|     1 | boy  |    11 |
|     2 | boy  |    10 |
|     2 | boy  |    10 |
+-------+------+-------+
6 rows in set (0.00 sec)

这种方式可以缩短SQL语句；并允许在单条SELECT语句中多次使用相同的表

使用不同类型的联结

自联结

一直有一位同学请假，找出同一班中其他的同学是否请假，我们首先要找出这个班的其他同学;

ariaDB [test]> select user,sex,math_price from users where class = (select class from users where user = 'user1');
+-------+------+------------+
| user  | sex  | math_price |
+-------+------+------------+
| user2 | girl | 36         |
| user3 | boy  | 66         |
| user1 | boy  | 88         |
+-------+------+------------+
3 rows in set (0.00 sec)

这是第一种解决方案，它使用了子查询,那么我们是用自联结如何解决。

MariaDB [test]> select u1.user,u1.sex,u1.math_price 
				from users as u1,users as u2 
				where u1.class = u2.class and u2.user =  'user1';

-------+------+------------+
| user  | sex  | math_price |
+-------+------+------------+
| user2 | girl | 36         |
| user3 | boy  | 66         |
| user1 | boy  | 88         |
+-------+------+------------+
3 rows in set (0.01 sec)

通常情况下使用自联结的查询速率比使用子查询的速率要快。

自然连接

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。
这项工作，由自己完成。自然联结是这样一种联结，其中你只能选择那些唯一的列。
这一般是通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的。

外联结

MariaDB [test]> select * from hour;
+-------+-------+
| hours | user  |
+-------+-------+
|    12 | user2 |
|     8 | user6 |
|     9 | user1 |
|    11 | user3 |
|    10 | user4 |
|    10 | user5 |
|    11 | NULL  |
+-------+-------+
7 rows in set (0.00 sec)

MariaDB [test]> select * from users;
+-------+--------+------+------+-------+------------+-------+
| user  | passwd | sex  | age  | price | math_price | class |
+-------+--------+------+------+-------+------------+-------+
| user2 | 222    | girl | 23   | 155   | 36         |     1 |
| user3 | 333    | boy  | 12   | 78    | 66         |     1 |
| user4 | 444    | boy  | 22   | NULL  | NULL       |     2 |
| user5 | 555    | boy  | 30   | NULL  | 43         |     2 |
| user1 | 111    | boy  | 18   | 35    | 88         |     1 |
| user6 | 666    | girl | 16   | 35    | 43         |     2 |
| user7 | 777    | boy  | NULL | NULL  | NULL       |  NULL |
+-------+--------+------+------+-------+------------+-------+
7 rows in set (0.00 sec)

MariaDB [test]> select users.user,hour.hours from users left join hour on users.user = hour.user;
+-------+-------+
| user  | hours |
+-------+-------+
| user2 |    12 |
| user6 |     8 |
| user1 |     9 |
| user3 |    11 |
| user4 |    10 |
| user5 |    10 |
| user7 |  NULL |
+-------+-------+
7 rows in set (0.00 sec)

MariaDB [test]> select users.user,hour.hours from users right join hour on users.user = hour.user;
+-------+-------+
| user  | hours |
+-------+-------+
| user2 |    12 |
| user3 |    11 |
| user4 |    10 |
| user5 |    10 |
| user1 |     9 |
| user6 |     8 |
| NULL  |    11 |
+-------+-------+
7 rows in set (0.00 sec)

使用了关键字OUTER JOIN来指定联结的类型，outer 可以省略，并且必须加上 LEFT 或RIGHT 指明方向。
左联结会打印出左边表的所有行，二右联结会打印出右边表的所有行。

使用带聚集函数的联结

如果要检索所有客户及每个客户所下的订单数，下面使用了COUNT()函数的代码可完成此工作：

使用联结和联结条件

• 注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
• 保证使用正确的联结条件，否则将返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡儿积。
• 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。