PHP+MySQL专家编程——MySQL联接
原创PHP+MySQL专家编程——MySQL联接
原创
MySQL联接
我们通常会在SELECT语句中使用联接，MySQL查询的联接使我们能够利用一个SQL语句查询或操作多个表的数据。
我们使用书中的SQL实例来进行说明和实践，实例中有两个表，如下
msyql >SELECT *
>FROM flags;
+-----------+--------+
| country | color |
+-----------+--------+
| Australia | Blue |
| Sweden | Blue |
| USA | Blue |
| Australia | Red |
| Canada | Red |
| Japan | Red |
| USA | Red |
| Australia | White |
| Canada | White |
| Japan | White |
| USA | White |
| Sweden | Yellow |
+-----------+--------+
msyql >SELECT *
>FROM colors;
+-------+------------+---------+------------+
| color | is_primary | is_dark | is_rainbow |
+-------+------------+---------+------------+
| Black | no | yes | no |
| Blue | yes | yes | yes |
| Green | yes | yes | yes |
| Red | yes | no | yes |
| White | yes | no | no |
+-------+------------+---------+------------+colors表是用来存放颜色和其他属性，flags是用来存储国旗和对应的颜色
如果我们想知道美国国旗上的颜色，可以使用下面的语句。
msyql >SELECT color
>FROM flags
>WHERE country='USA';
+-------+
| color |
+-------+
| Blue |
| Red |
| White |
+-------+1 INNER JOIN
如果要了解有关国旗颜色的更多属性，我们需要用到内联查询。
mysql >SELECT flags.color, colors.is_primary, colors.is_dark, colors.is_rainbow
>FROM flags
>INNER JOIN colors ON flags.color = colors.color
>WHERE flags.country='USA';
+-------+------------+---------+------------+
| color | is_primary | is_dark | is_rainbow |
+-------+------------+---------+------------+
| Blue | yes | yes | yes |
| Red | yes | no | yes |
| White | yes | no | no |
+-------+------------+---------+------------+其中有些需要提到的点：
1.1表别名(Alias)
第一点就是我们并没有使用原本的表名字，'flags' 和 'colors'，而是用了'f' 和 'c' 作为表名称，这个其实就是别名，在MySQL中，并不限制表名称格式，但是尽量用规范和恰当的命名标准，MySQL中表的别名最长度是256个字符，表名最大长度是64个字符。
1.2 ON 子句和USING 子句
联接命令中，ON 子句的语法是 table1.column_name = table2.column_name。而当联接的列拥有相同的列名称，那么就可以使用USING 语法来简化ON语法，格式为 USING(column_name)。
1.3 另一种可供选择的INNER JOIN语法
mysql> SELECT f.color, c.is_primary, c.is_dark, c.is_rainbow
-> FROM flags f
-> INNER JOIN colors c ON f.color = c.color
-> WHERE f.country='USA';
+-------+------------+---------+------------+
| color | is_primary | is_dark | is_rainbow |
+-------+------------+---------+------------+
| Blue | yes | yes | yes |
| Red | yes | no | yes |
| White | yes | no | no |
+-------+------------+---------+------------+逗号语法是比较常见的同时也是比较好用的一种方法，然而它没有为软件开发人员提供最好的可读性。
使用逗号语法时候，需要在WHERE子句中指定相联接的列以及检索数据的约束条件。而不像INNER JOIN语法是在表格指定时，使用ON子句或者USING子句中定义相关联的联接关系，，在WHERE子句中明确基于联接表的数据选择条件，这样就可以提高代码的可读性，并大大减少较为复杂的夺标语句中漏写某个联接列的可能。
2 OUTER JOIN （外部联接）
mysql> SELECT f.country, f.color
->FROM flags f
->LEFT OUTER JOIN colors c USING (color)
->WHERE c.color is NULL;
+------------+ -----------+
| country | color |
+------------+ -----------+
| Sweden | Yellow |
+------------+------------+
mysql> SELECT c.color, c.is_primary
->FROM colors c
->LEFT OUTER JOIN flags f USING (color)
->WHERE f.country is NULL;
+--------+ ---------+
| color | is_primary |
+--------+ ---------+
| Black | no |
+--------+ ---------+
| Green | yes |
+--------+ ---------+我们发现上面的代码不仅包括了OUTER JION 的语法，还包括了关键字LEFT。OUTER其实是可选的关键字，通常我们仅仅使用LEFT JOIN简化SQL语法
使用OUTER JION有两个原因， 一是当数据值集合未知时，仍要检索所有能匹配部分约束条件的数据集合 另一种情况是当规范化数据库没有强制参照完整性时，需要用OUTER JOIN
一般我们外联分为左联和右联，推荐应用程序用左联，并且在应用程序的所有SQL语句中保持一致的写法
3 MySQL的合并查询(UNION)
UNION语句主要用来为某SQL查询合并多个SELECT 语句的结果。对于合法的UNION语句，每个SELECT语句的列数必须是相同 ，
UNION语句通常返回SELECT语句集合生成的数据集合，并去除了重复的数据。而ALL语法可以返回所有SELECT的数据行，DISTINCT语法返回所有数据行是唯一的（默认）
4 MySQL查询中的GROUP BY子句
GROUP BY 语法用于支持对数据行的聚合，并可以使用标量函数（scalar function）。
mysql>SET SESSION sql_mode=ONLY_FULL_GROUP_BY;
# 错误用法
mysql>SELECT country, COUNT(*)
->FROM flags;
+-----------+----------+
| country | COUNT(*) |
+-----------+----------+
| Australia | 12 |
+-----------+----------+
# 使用标量函数
mysql> SELECT country, COUNT(*) AS color_count
->FROM flags
->GROUP BY country;
+-----------+-------------+
| country | color_count |
+-----------+-------------+
| Australia | 3 |
| Canada | 2 |
| Japan | 2 |
| Sweden | 2 |
| USA | 3 |
+-----------+-------------+
# 不返回数字型的标量函数 用 GROUP_CONCAT
mysql>SELECT country, GROUP_CONCAT(color) AS colors
->FROM flags
->GROUP BY country;
+-----------+----------------+
| country | colors |
+-----------+----------------+
| Australia | Blue,Red,White |
| Canada | Red,White |
| Japan | Red,White |
| Sweden | Blue,Yellow |
| USA | Blue,Red,White |
+-----------+----------------+
mysql>SELECT country, GROUP_CONCAT(color) AS colors, COUNT(*) AS color_count
->FROM flags
->GROUP BY country;
+-----------+----------------+-------------+
| country | colors | color_count |
+-----------+----------------+-------------+
| Australia | Blue,Red,White | 3 |
| Canada | Red,White | 2 |
| Japan | Red,White | 2 |
| Sweden | Blue,Yellow | 2 |
| USA | Blue,Red,White | 3 |
+-----------+----------------+-------------+####4.1 WITH ROLLUP
使用这个额外关键字，使用此语法，查询结果的数据行将包含每个GROUP BY列的聚合行。(就相当于多一行直接用select 的查询条件而不用GROUP BY语句直接查询的数量)，用聚合的属性行用 NULL 表示。
mysql>SELECT country, COUNT(*) AS color_count
->FROM flags
->GROUP BY country WITH ROLLUP;
+-----------+-------------+
| country | color_count |
+-----------+-------------+
| Australia | 3 |
| Canada | 2 |
| Japan | 2 |
| Sweden | 2 |
| USA | 3 |
| NULL | 12 |
+-----------+-------------+
mysql>SELECT c.color, c.is_dark, COUNT(*)
->FROM colors c, flags f
->WHERE c.color = f.color
->GROUP BY c.color, c.is_dark WITH ROLLUP;
+-------+---------+----------+
| color | is_dark | COUNT(*) |
+-------+---------+----------+
| Blue | yes | 3 |
| Blue | NULL | 3 |
| Red | no | 4 |
| Red | NULL | 4 |
| White | no | 4 |
| White | NULL | 4 |
| NULL | NULL | 11 |
+-------+---------+----------+####4.2 HAVING
使用GROUP BY 子句时候，可以使用HAVING ，而不是 WHERE 来针对标量函数进行限制
mysql>SELECT country, GROUP_CONCAT(color) AS colors
->FROM flags
->GROUP BY country
->HAVING COUNT(*) = 2;
+---------+-------------+
| country | colors |
+---------+-------------+
| Canada | Red,White |
| Japan | Red,White |
| Sweden | Blue,Yellow |
+---------+-------------+
# 我们可以输出标量函数，更直观
mysql>SELECT country, GROUP_CONCAT(color) AS colors， COUNT(*) as color_count
->FROM flags
->GROUP BY country
->HAVING COUNT(*) = 2;
+---------+-------------+-------------+
| country | colors | color_count |
+---------+-------------+-------------+
| Canada | Red,White | 2 |
| Japan | Red,White | 2 |
| Sweden | Blue,Yellow | 2 |
+---------+-------------+-------------+原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。