PHP+MySQL专家编程——MySQL联接

原创

Hi胡瀚

发布于 2018-12-03 23:35:48

1.6K0

发布于 2018-12-03 23:35:48

文章被收录于专栏：开发 & 运维分享

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 删除。

其他

登录后参与评论

0 条评论

热度