第一部分UNION
UNION 其實(shí)就是合并兩個(gè)SELECT結(jié)果集
union是產(chǎn)生的兩個(gè)記錄集(字段要一樣的)并在一起览效，成為一個(gè)新的記錄集 。
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 ??????|科技的發(fā)展讓人迷失在知識(shí)的海洋| 2 ???????| ????123.00 |
| ?????12 |蘇菲的世界|喬斯坦·賈德| 1999-04-01 ??????| NULL ?????????????????????????????????????????????????| 2 ???????| ?????30.00 |
| ?????13 |道德經(jīng)|老子| 1999-10-01 ??????|道.德.經(jīng)| 2 ???????| ??????NULL |
| ?????14 |社會(huì)契約論|盧梭| 2003-02-01 ??????| NULL ?????????????????????????????????????????????????| 2 ???????| ??????NULL |
| ??????1 |跟我學(xué)MariaDB ???| hhw ??????????????| 2016-12-03 ??????|開啟數(shù)據(jù)庫的大門| 18 ??????| ?????44.00 |
| ??????2 |跟我學(xué)NodeJS ????| hhw ??????????????| 2016-12-02 ??????|學(xué)習(xí)基礎(chǔ),領(lǐng)略框架,邁向后臺(tái)這條不歸路| 18 ??????| ?????44.44 |
| ??????3 | HTTP權(quán)威指南| O'Reilly ?????????| 2002-01-01 ??????|網(wǎng)絡(luò)基礎(chǔ)解疑| 18 ??????| ?????79.00 |
+---------+------------------+-------------------+------------------+-------------------------------------------------------+----------+------------+
7 rows in set (0.01 sec)2.6 AND OR 結(jié)合使用
篩選book_id等于3 或 10,并且在一月出版的書;默認(rèn)從前往后執(zhí)行, 當(dāng)然可以用(..)
其中用到日期函數(shù), 后續(xù)完善;
>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權(quán)威指南|NULL|2002-01-01|NULL|18|NULL|
|10|平凡的世界|NULL|2005-01-01|NULL|9|NULL|
+---------+------------------+------------------+------------------+-------------+----------+------+
2rows inset(0.00sec)
前后兩個(gè)表字段需要數(shù)量一致, 反面實(shí)例
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首先對(duì)于別名做個(gè)最簡單的實(shí)例:
MariaDB [study_db]> SELECT
-> ?????'文學(xué)' AS '? ?類', COUNT(*) AS '? 數(shù)量'
-> ????FROM book_classify;
+-----------+-------------+
|? ?類|? 數(shù)量|
+-----------+-------------+
|文學(xué)| ?????????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 |跟我學(xué)MariaDB ???|工業(yè)技術(shù)| ?????????18 |工業(yè)技術(shù)|
| ??????2 |跟我學(xué)NodeJS ????|工業(yè)技術(shù)| ?????????18 |工業(yè)技術(shù)|
| ??????3 | HTTP權(quán)威指南|工業(yè)技術(shù)| ?????????18 |工業(yè)技術(shù)|
| ??????4 |追風(fēng)箏的人|文學(xué)| ??????????9 |文學(xué)|
| ??????5 |小王子|文學(xué)| ??????????9 |文學(xué)|
| ??????6 |圍城|文學(xué)| ??????????9 |文學(xué)|
| ??????7 |活著|文學(xué)| ??????????9 |文學(xué)|
| ??????8 |解憂雜貨店|文學(xué)| ??????????9 |文學(xué)|
| ??????9 |簡愛|文學(xué)| ??????????9 |文學(xué)|
| ?????10 |平凡的世界|文學(xué)| ??????????9 |文學(xué)|
| ?????11 |失控|哲學(xué)、宗教| ??????????2 |哲學(xué)惑艇、宗教類|
| ?????12 |蘇菲的世界|哲學(xué)心剥、宗教| ??????????2 |哲學(xué)自阱、宗教類|
| ?????13 |道德經(jīng)|哲學(xué)管毙、宗教| ??????????2 |哲學(xué)腿椎、宗教類|
| ?????14 |社會(huì)契約論|哲學(xué)、宗教| ??????????2 |哲學(xué)锅风、宗教類|
| ?????15 |經(jīng)濟(jì)學(xué)原理|經(jīng)濟(jì)| ??????????6 |經(jīng)濟(jì)|
| ?????16 |國富論|經(jīng)濟(jì)| ??????????6 |經(jīng)濟(jì)|
| ?????17 |偉大的博弈|經(jīng)濟(jì)| ??????????6 |經(jīng)濟(jì)|
| ?????18 |經(jīng)濟(jì)學(xué)常識(shí)|經(jīng)濟(jì)| ??????????6 |經(jīng)濟(jì)|
| ?????19 |人類簡史|歷史、地理| ?????????11 |歷史鞍泉、地理|
| ?????20 |國史大綱|歷史皱埠、地理| ?????????11 |歷史、地理|
| ?????21 |史記|歷史咖驮、地理| ?????????11 |歷史边器、地理|
| ?????22 |巨人的隕落|歷史、地理| ?????????11 |歷史托修、地理|
| ?????23 |偷影子的人|文學(xué)| ??????????9 |文學(xué)|
| ?????24 | 1984 ????????????|文學(xué)| ??????????9 |文學(xué)|
+---------+------------------+-----------------+-------------+--------------------+
24 rows in set (0.00 sec)
