MariaDB连接表UOIN 与 JOIN (八)

作者: simuty | 来源:发表于2016-12-15 12:03 被阅读110次

第一部分 UNION

UNION 其实就是合并两个SELECT结果集

union是产生的两个记录集(字段要一样的)并在一起，成为一个新的记录集。

MariaDB [study_db]> SELECT * FROM new_books WHERE classify < 6
    -> UNION 
    -> SELECT * FROM new_books WHERE classify = 18;
+---------+------------------+-------------------+------------------+-------------------------------------------------------+----------+------------+
| book_id | book_name        | book_author_name  | publication_date | description                                           | classify | book_price |
+---------+------------------+-------------------+------------------+-------------------------------------------------------+----------+------------+
|      11 | 失控             | 凯文·凯利         | 2010-12-01       | 科技的发展让人迷失在知识的海洋                        | 2        |     123.00 |
|      12 | 苏菲的世界       | 乔斯坦·贾德       | 1999-04-01       | NULL                                                  | 2        |      30.00 |
|      13 | 道德经           | 老子              | 1999-10-01       | 道.德.经                                              | 2        |       NULL |
|      14 | 社会契约论       | 卢梭              | 2003-02-01       | NULL                                                  | 2        |       NULL |
|       1 | 跟我学MariaDB    | hhw               | 2016-12-03       | 开启数据库的大门                                      | 18       |      44.00 |
|       2 | 跟我学NodeJS     | hhw               | 2016-12-02       | 学习基础,领略框架,迈向后台这条不归路                  | 18       |      44.44 |
|       3 | HTTP权威指南     | O'Reilly          | 2002-01-01       | 网络基础解疑                                          | 18       |      79.00 |
+---------+------------------+-------------------+------------------+-------------------------------------------------------+----------+------------+
7 rows in set (0.01 sec)

前后两个表字段需要数量一致, 反面实例:

MariaDB [study_db]> SELECT * FROM new_books WHERE classify < 6 UNION  SELECT * FROM book_classify  WHERE classify_id = 18;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

1.1 首先对于别名做个最简单的实例:

MariaDB [study_db]> SELECT 
    ->      '文学' AS '📚  类', COUNT(*) AS '📚 数量'
    ->     FROM book_classify;
+-----------+-------------+
| 📚  类      | 📚 数量       |
+-----------+-------------+
| 文学      |          22 |
+-----------+-------------+
1 row in set (0.01 sec)

1.2 汇总表

MariaDB [study_db]> SELECT book_id , book_name, book_classify.classify_name, book_classify.classify_id, book_classify.description AS '描述'
    ->     FROM book_classify , new_books 
    ->     WHERE new_books.classify = book_classify.classify_id;
+---------+------------------+-----------------+-------------+--------------------+
| book_id | book_name        | classify_name   | classify_id | 描述               |
+---------+------------------+-----------------+-------------+--------------------+
|       1 | 跟我学MariaDB    | 工业技术        |          18 | 工业技术           |
|       2 | 跟我学NodeJS     | 工业技术        |          18 | 工业技术           |
|       3 | HTTP权威指南     | 工业技术        |          18 | 工业技术           |
|       4 | 追风筝的人       | 文学            |           9 | 文学               |
|       5 | 小王子           | 文学            |           9 | 文学               |
|       6 | 围城             | 文学            |           9 | 文学               |
|       7 | 活着             | 文学            |           9 | 文学               |
|       8 | 解忧杂货店       | 文学            |           9 | 文学               |
|       9 | 简爱             | 文学            |           9 | 文学               |
|      10 | 平凡的世界       | 文学            |           9 | 文学               |
|      11 | 失控             | 哲学、宗教      |           2 | 哲学、宗教类       |
|      12 | 苏菲的世界       | 哲学、宗教      |           2 | 哲学、宗教类       |
|      13 | 道德经           | 哲学、宗教      |           2 | 哲学、宗教类       |
|      14 | 社会契约论       | 哲学、宗教      |           2 | 哲学、宗教类       |
|      15 | 经济学原理       | 经济            |           6 | 经济               |
|      16 | 国富论           | 经济            |           6 | 经济               |
|      17 | 伟大的博弈       | 经济            |           6 | 经济               |
|      18 | 经济学常识       | 经济            |           6 | 经济               |
|      19 | 人类简史         | 历史、地理      |          11 | 历史、地理         |
|      20 | 国史大纲         | 历史、地理      |          11 | 历史、地理         |
|      21 | 史记             | 历史、地理      |          11 | 历史、地理         |
|      22 | 巨人的陨落       | 历史、地理      |          11 | 历史、地理         |
|      23 | 偷影子的人       | 文学            |           9 | 文学               |
|      24 | 1984             | 文学            |           9 | 文学               |
+---------+------------------+-----------------+-------------+--------------------+
24 rows in set (0.00 sec)

1.3 UNION

MariaDB [study_db]>  SELECT 
    ->          '文学' AS '📚  类', COUNT(*) AS '📚 数量'
    ->          FROM book_classify AS classify, new_books AS books
    ->          WHERE books.book_id = classify.classify_id
    ->          AND books.classify = 9
    ->         
    ->          UNION 
    ->           
    ->          SELECT 
    ->          '工业技术', COUNT(*)
    ->          FROM book_classify AS classify, new_books AS books
    ->          WHERE books.book_id = classify.classify_id
    ->          AND books.classify = 18;
+--------------+-------------+
| 📚  类         | 📚 数量       |
+--------------+-------------+
| 文学         |           7 |
| 工业技术     |           3 |
+--------------+-------------+
2 rows in set (0.00 sec)

SQL最强大的功能之一就是能在数据查询的执行中联结（join）表。联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语法是学习SQL的极为重要的部分。在能够有效地使用联结前，必须了解关系表以及关系数据库设计的一些基础知识。

需求: 假如有一家书店, 里边有各种类型的书籍,此时我们需要一个数据库来记录,以方便用户查询与管理书籍, 那么, 我们最容易想到的一种解决方法就是下图中第一种方案

屏幕快照 2016-12-07 下午1.36.52.png

如前所述，将数据分解为多个表能更有效地存储，更方便地处理，并且可伸缩性更好。
但这些好处是有代价的。
如果数据存储在多个表中，怎样用一条SELECT 语句就检索出数据呢？

可伸缩（scale）:

能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好（scale well).

第二部分 JOIN

准备数据:

屏幕快照 2016-12-14 上午11.49.21.png

屏幕快照 2016-12-14 上午11.49.11.png

join 是两张表做交连后里面条件相同的部分记录产生一个记录集

JOIN 按照功能大致分为如下三类：

1. 内连接分为三种：自然连接(natural join)、等值连接、非等值连接;
2. 外连接分为三类：左外连接、右外连接;
3. 交叉连接.

JOIN语法

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

2.1 内连接之--自然连接(NATURAL JOIN)

MariaDB [user_db]> SELECT *
    -> FROM customers NATURAL JOIN orders
    -> ;
+-------------+-----------+---------+------+----------+---------------------+--------------+
| customer_id | name      | address | age  | order_id | order_date          | order_amount |
+-------------+-----------+---------+------+----------+---------------------+--------------+
|           1 | 紫荆城    | 帝都    |   24 |        1 | 2016-12-14 12:02:35 |       111.00 |
|           1 | 紫荆城    | 帝都    |   24 |        3 | 2016-11-11 23:33:33 |         1.00 |
|           2 | 明珠      | 魔都    |   20 |        2 | 2016-11-11 22:22:22 |    222222.00 |
|           3 | 西湖      | 杭州    |   20 |        4 | 2016-12-12 11:11:11 |    123456.00 |
+-------------+-----------+---------+------+----------+---------------------+--------------+
4 rows in set (0.00 sec)

2.2 内连接之--等值连接

MariaDB [user_db]> SELECT * 
    -> FROM customers 
    -> INNER JOIN orders
    -> ON customers.customer_id = orders.customer_id;

+-------------+-----------+---------+------+----------+---------------------+--------------+-------------+
| customer_id | name      | address | age  | order_id | order_date          | order_amount | customer_id |
+-------------+-----------+---------+------+----------+---------------------+--------------+-------------+
|           1 | 紫荆城    | 帝都    |   24 |        1 | 2016-12-14 12:02:35 |       111.00 |           1 |
|           1 | 紫荆城    | 帝都    |   24 |        3 | 2016-11-11 23:33:33 |         1.00 |           1 |
|           2 | 明珠      | 魔都    |   20 |        2 | 2016-11-11 22:22:22 |    222222.00 |           2 |
|           3 | 西湖      | 杭州    |   20 |        4 | 2016-12-12 11:11:11 |    123456.00 |           3 |
+-------------+-----------+---------+------+----------+---------------------+--------------+-------------+
4 rows in set (0.00 sec)

2.3 内连接之--非等值连接

MariaDB [user_db]> SELECT name, order_amount, order_date 
    -> FROM customers NATURAL JOIN orders 
    -> WHERE order_amount > 1234;
+--------+--------------+---------------------+
| name   | order_amount | order_date          |
+--------+--------------+---------------------+
| 明珠   |    222222.00 | 2016-11-11 22:22:22 |
| 西湖   |    123456.00 | 2016-12-12 11:11:11 |
+--------+--------------+---------------------+
2 rows in set (0.00 sec)

3.1 交叉连接

cross join：交叉连接，得到的结果是两个表的乘积，即笛卡尔积

cefc1e178a82b9014e9d010b7b8da9773912efd2-1.png


MariaDB [user_db]> SELECT * FROM customers cross JOIN orders;
+-------------+-----------+---------+------+----------+---------------------+--------------+-------------+
| customer_id | name      | address | age  | order_id | order_date          | order_amount | customer_id |
+-------------+-----------+---------+------+----------+---------------------+--------------+-------------+
|           1 | 紫荆城    | 帝都    |   24 |        1 | 2016-12-14 12:02:35 |       111.00 |           1 |
|           2 | 明珠      | 魔都    |   20 |        1 | 2016-12-14 12:02:35 |       111.00 |           1 |
|           3 | 西湖      | 杭州    |   20 |        1 | 2016-12-14 12:02:35 |       111.00 |           1 |
|           4 | 宝岛      | 琉球    |   24 |        1 | 2016-12-14 12:02:35 |       111.00 |           1 |
|           1 | 紫荆城    | 帝都    |   24 |        2 | 2016-11-11 22:22:22 |    222222.00 |           2 |
|           2 | 明珠      | 魔都    |   20 |        2 | 2016-11-11 22:22:22 |    222222.00 |           2 |
|           3 | 西湖      | 杭州    |   20 |        2 | 2016-11-11 22:22:22 |    222222.00 |           2 |
|           4 | 宝岛      | 琉球    |   24 |        2 | 2016-11-11 22:22:22 |    222222.00 |           2 |
|           1 | 紫荆城    | 帝都    |   24 |        3 | 2016-11-11 23:33:33 |         1.00 |           1 |
|           2 | 明珠      | 魔都    |   20 |        3 | 2016-11-11 23:33:33 |         1.00 |           1 |
|           3 | 西湖      | 杭州    |   20 |        3 | 2016-11-11 23:33:33 |         1.00 |           1 |
|           4 | 宝岛      | 琉球    |   24 |        3 | 2016-11-11 23:33:33 |         1.00 |           1 |
|           1 | 紫荆城    | 帝都    |   24 |        4 | 2016-12-12 11:11:11 |    123456.00 |           3 |
|           2 | 明珠      | 魔都    |   20 |        4 | 2016-12-12 11:11:11 |    123456.00 |           3 |
|           3 | 西湖      | 杭州    |   20 |        4 | 2016-12-12 11:11:11 |    123456.00 |           3 |
|           4 | 宝岛      | 琉球    |   24 |        4 | 2016-12-12 11:11:11 |    123456.00 |           3 |
|           1 | 紫荆城    | 帝都    |   24 |        5 | 2016-12-14 15:18:17 |      1234.00 |           5 |
|           2 | 明珠      | 魔都    |   20 |        5 | 2016-12-14 15:18:17 |      1234.00 |           5 |
|           3 | 西湖      | 杭州    |   20 |        5 | 2016-12-14 15:18:17 |      1234.00 |           5 |
|           4 | 宝岛      | 琉球    |   24 |        5 | 2016-12-14 15:18:17 |      1234.00 |           5 |
+-------------+-----------+---------+------+----------+---------------------+--------------+-------------+
20 rows in set (0.01 sec)

实际上，在 MySQL 中（仅限于 MySQL） CROSS JOIN 与 INNER JOIN 的表现是一样的，在不指定 ON 条件得到的结果都是笛卡尔积，反之取得两个表完全匹配的结果。
INNER JOIN 与 CROSS JOIN 可以省略 INNER 或 CROSS 关键字，因此下面的 SQL 效果是一样的：

... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2

4.1 左连接与右连接

先看两张图
包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行

static1.squarespace-1.png

右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行

static1.squarespace-2.png

然后看具体的实例SQL语句

MariaDB [user_db]> SELECT * FROM 
    -> customers LEFT JOIN orders
    -> ON customers.customer_id = orders.customer_id;

+-------------+-----------+---------+------+----------+---------------------+--------------+-------------+
| customer_id | name      | address | age  | order_id | order_date          | order_amount | customer_id |
+-------------+-----------+---------+------+----------+---------------------+--------------+-------------+
|           1 | 紫荆城    | 帝都    |   24 |        1 | 2016-12-14 12:02:35 |       111.00 |           1 |
|           2 | 明珠      | 魔都    |   20 |        2 | 2016-11-11 22:22:22 |    222222.00 |           2 |
|           1 | 紫荆城    | 帝都    |   24 |        3 | 2016-11-11 23:33:33 |         1.00 |           1 |
|           3 | 西湖      | 杭州    |   20 |        4 | 2016-12-12 11:11:11 |    123456.00 |           3 |
|           4 | 宝岛      | 琉球    |   24 |     NULL | NULL                |         NULL |        NULL |
+-------------+-----------+---------+------+----------+---------------------+--------------+-------------+
5 rows in set (0.00 sec)

右连接

MariaDB [user_db]> SELECT * FROM  customers RIGHT JOIN orders  ON customers.customer_id = orders.customer_id; 
+-------------+-----------+---------+------+----------+---------------------+--------------+-------------+
| customer_id | name      | address | age  | order_id | order_date          | order_amount | customer_id |
+-------------+-----------+---------+------+----------+---------------------+--------------+-------------+
|           1 | 紫荆城    | 帝都    |   24 |        1 | 2016-12-14 12:02:35 |       111.00 |           1 |
|           2 | 明珠      | 魔都    |   20 |        2 | 2016-11-11 22:22:22 |    222222.00 |           2 |
|           1 | 紫荆城    | 帝都    |   24 |        3 | 2016-11-11 23:33:33 |         1.00 |           1 |
|           3 | 西湖      | 杭州    |   20 |        4 | 2016-12-12 11:11:11 |    123456.00 |           3 |
|        NULL | NULL      | NULL    | NULL |        5 | 2016-12-14 15:18:17 |      1234.00 |           5 |
+-------------+-----------+---------+------+----------+---------------------+--------------+-------------+
5 rows in set (0.00 sec)

实例中先左连接右连接:

6BADB0A1-32CC-4324-88E8-72D019D25C4D.png

MySQL不支持全连接, 但解决方法前往stackoverflow

一图胜千言

hMKKt.jpg

参考
Mysql Join语法解析与性能分析
 Stackoverflow
mysql的exists与inner join 和 not exists与 left join 性能差别惊人
 五种提高 SQL 性能的方法

更多精彩内容请关注“IT实战联盟”哦~~~

IT实战联盟.jpg

MariaDB连接表UOIN 与 JOIN (八)

第一部分 UNION

第二部分 JOIN

2.1 内连接之--自然连接(NATURAL JOIN)

2.2 内连接之--等值连接

2.3 内连接之--非等值连接

3.1 交叉连接

4.1 左连接与右连接

然后看具体的实例SQL语句

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读