第一部分 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
网友评论