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 |
+---------+-------------+-------------+

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏小白安全

小白博客 MySQL日期时间函数大全

DAYOFWEEK(date) 返回日期date是星期几(1=星期天,2=星期一,……7=星期六,ODBC标准) mysql> select DAYOFWEE...

308100
来自专栏数据之美

图文并茂详解 SQL JOIN

Join是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另...

29480
来自专栏乐沙弥的世界

批量 SQL 之 FORALL 语句

    对PL/SQL而言,任何的PL/SQL块或者子程序都是PL/SQL引擎来处理,而其中包含的SQL语句则由PL/SQL引擎发送SQL语句转交到SQL引擎...

9520
来自专栏深度学习之tensorflow实战篇

ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)/ ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY CO

ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)用法      今天在使用多字段去重时,由于某些字段有多种可...

26730
来自专栏数据之美

Hive 在多维统计分析中的应用 & 技巧总结

多维统计一般分两种,我们看看 Hive 中如何解决: 1、同属性的多维组合统计 (1)问题: 有如下数据,字段内容分别为:url, catePath...

53380
来自专栏章鱼的慢慢技术路

SQL数据查询之——嵌套查询

18930
来自专栏全栈工程师成长之路

深入浅出后端开发(SQL指令笔记)

35370
来自专栏机器学习算法与Python学习

SQL Server 学习笔记

之前学过一点数据库但由于一直没有使用忘得差不多了,最近重新复习一下相关的知识,把基本的语法YOU又看了一遍,为了强化记忆在写一遍~~~~~~ ? 基本的 se...

390150
来自专栏章鱼的慢慢技术路

SQL数据查询之——单表查询

25650
来自专栏跟着阿笨一起玩NET

SQL小技巧总结。

14910

扫码关注云+社区

领取腾讯云代金券