第一部分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)2.6 AND OR 结合使用
筛选book_id等于3 或 10,并且在一月出版的书;默认从前往后执行, 当然可以用(..)
其中用到日期函数, 后续完善;
>SELECT * FROM booksWHERE(book_id =3OR book_id =10)ANDmonth(publication_date)=1;
+---------+------------------+------------------+------------------+-------------+----------+------+
| book_id | book_name | book_author_name | publication_date | description | classify | c |
+---------+------------------+------------------+------------------+-------------+----------+------+
|3| HTTP权威指南|NULL|2002-01-01|NULL|18|NULL|
|10|平凡的世界|NULL|2005-01-01|NULL|9|NULL|
+---------+------------------+------------------+------------------+-------------+----------+------+
2rows inset(0.00sec)
前后两个表字段需要数量一致, 反面实例
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 汇总表
1.更清楚且更直观;
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)
