MySQL中UNION語句用法詳解與示例
一、數(shù)據(jù)準(zhǔn)備
-- 創(chuàng)建表
CREATE TABLE test_user (
ID int(11) NOT NULL AUTO_INCREMENT,
USER_ID int(11) DEFAULT NULL COMMENT '用戶賬號',
USER_NAME varchar(255) DEFAULT NULL COMMENT '用戶名',
AGE int(5) DEFAULT NULL COMMENT '年齡',
COMMENT varchar(255) DEFAULT NULL COMMENT '簡介',
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- 數(shù)據(jù)插入語句
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('1', '111', '開心菜鳥', '18', '今天很開心');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('2', '222', '悲傷菜鳥', '21', '今天很悲傷');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('3', '333', '認(rèn)真菜鳥', '30', '今天很認(rèn)真');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('4', '444', '高興菜鳥', '18', '今天很高興');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('5', '555', '嚴(yán)肅菜鳥', '21', '今天很嚴(yán)肅');SELECT * FROM test_user u;
一、UNION 和 UNION ALL
UNION
連接數(shù)據(jù)集關(guān)鍵字，可以將兩個(gè)查詢結(jié)果集拼接為一個(gè)，會過濾掉相同的記錄
UNION ALL
連接數(shù)據(jù)集關(guān)鍵字，可以將兩個(gè)查詢結(jié)果集拼接為一個(gè)，不會過濾掉相同的記錄
-- 使用UNION SELECT * FROM test_user u UNION SELECT * FROM test_user u;
使用 UNION ，可以看到查詢結(jié)果只有 5 條數(shù)據(jù)。
-- 使用UNION ALL SELECT * FROM test_user u UNION ALL SELECT * FROM test_user u;
使用 UNION ALL，可以看到查詢結(jié)果有 10 條數(shù)據(jù)。
二、UNION 的執(zhí)行順序（UNION 和其他語句一同出現(xiàn)）
from—>on—>join—>where—>group by—>having+(聚合函數(shù))—>select—>distinct—>UNION—>order by—>limit
UNION 的執(zhí)行順序在 ORDER BY 之前
請記住這個(gè)執(zhí)行順序，便可以知道 UNION 和其他語句一同出現(xiàn)的結(jié)果。
UNION 和 WHERE 語句
-- 1、第二個(gè)子句中的 where 語句不能同時(shí)作用于兩個(gè)select語句 -- 5 + 1，共計(jì) 6 條 SELECT *, 'table1' FROM test_user u UNION ALL SELECT *, 'table2' FROM test_user u WHERE AGE = 30;
UNION 在 where 之后，所以第二個(gè)表的WHERE先篩選后進(jìn)行數(shù)據(jù)集拼接；
如果想要把 where 作用所有結(jié)果集，可以通過再嵌套一個(gè) select 。
-- 1、第二個(gè)子句中的 where 語句不能同時(shí)作用于兩個(gè)select語句 -- 1 + 1，共計(jì) 2 條 -- 寫法 1 SELECT * FROM ( SELECT *, 'table1' FROM test_user u UNION ALL SELECT *, 'table2' FROM test_user u ) a WHERE AGE = 30; -- 或者使用寫法 2 SELECT *, 'table1' FROM test_user u WHERE AGE = 30 UNION ALL SELECT *, 'table2' FROM test_user u WHERE AGE = 30 ;
1.UNION 和 GROUP 語句
-- 2、第二個(gè)子句中的 group by 語句不能同時(shí)作用于兩個(gè)select語句 -- 5 + 3，共計(jì) 8 條 SELECT *, 'table1' FROM test_user u UNION ALL SELECT *, 'table2' FROM test_user u GROUP BY AGE;
UNION 在 GROUP BY 之后，所以 table2 的 GROUP BY 先分組后進(jìn)行數(shù)據(jù)集拼接；
2. UNION 和 HAVING 語句
-- 3、第二個(gè)子句中的 HAVING 語句不能同時(shí)作用于兩個(gè) select 語句 -- 5 + 1，共計(jì) 6 條 SELECT *, 'table1' FROM test_user u UNION ALL SELECT *, 'table2' FROM test_user u HAVING AGE = 30 ;
UNION 在 HAVING 之后，所以 table2 的 HAVING 先過濾后進(jìn)行數(shù)據(jù)集拼接；
3. UNION 和 ORDER BY 語句
-- 4、第二個(gè)子句中的 order by 語句可以同時(shí)作用于兩個(gè)select語句 -- 查詢結(jié)果整體按照 age 進(jìn)行了排序 SELECT *, 'table1' FROM test_user u UNION ALL SELECT *, 'table2' FROM test_user u ORDER BY AGE;
因?yàn)楫?dāng) UNION（ALL）語句和 ORDER BY語句同時(shí)出現(xiàn)，UNION（ALL）語句先執(zhí)行。
4. UNION 和 LIMIT 語句
-- 只有1條數(shù)據(jù)，因?yàn)長IMIT在UNION之后執(zhí)行 SELECT *, 'table1' FROM test_user u UNION ALL SELECT *, 'table2' FROM test_user u limit 0,1;
只有1條數(shù)據(jù)，因?yàn)?LIMIT 在 UNION 之后執(zhí)行。
UNION 、 ORDER BY 和 LIMIT 語句
-- 5、第二個(gè)子句中的 order by ,LIMIT 語句同時(shí)作用于兩個(gè) select 語句 ********* -- 只有1條數(shù)據(jù)，age=30 UNION--->ORDER BY--->LIMIT SELECT *, 'table1' FROM test_user u UNION ALL SELECT *, 'table2' FROM test_user u order by age desc limit 0,1;
先拼接數(shù)據(jù)集，在按照 age 排序，最后使用 LIMIT 。
三、MySQL 使用 UNION（ALL） + ORDER 導(dǎo)致排序失效
通過以下兩種方式解決：
- 添加 LIMIT 字段
- 額外增加排序字段
1.SQL 1 如下
SELECT * FROM test_user u ORDER BY AGE;
2. SQL 2 如下
SELECT * FROM test_user u ORDER BY AGE DESC;
3. 查詢結(jié)果集
(SELECT *, 'table1' FROM test_user u ORDER BY AGE) UNION ALL (SELECT *, 'table2' FROM test_user u ORDER BY AGE DESC);
可以看到此時(shí) ORDER BY 語句失效了。
原因：UNION（ALL） + 會使 ORDER 失效
解決辦法（1）： 添加 LIMIT
-- 都加上 LIMIT ( SELECT *, 'table1' FROM test_user u ORDER BY AGE limit 10) UNION ALL ( SELECT *, 'table2' FROM test_user u ORDER BY AGE DESC limit 10)
最好的解決方案就是先查詢后排序，避免上述情況發(fā)生。
解決辦法（2） ：添加額外的排序字段
select * from ( ( SELECT *, 'table1' AS name, row_number() over(ORDER BY AGE ) AS rn FROM test_user u ) UNION ALL ( SELECT *, 'table2' AS name, row_number() over(ORDER BY AGE DESC) AS rn FROM test_user u ) ) a order by name, rn;
額外需要兩個(gè)字段，通過 row_number() over(order by column)進(jìn)行表內(nèi)排序，再通過 name 字段進(jìn)行表排序。
四、UNION 報(bào)錯(cuò)語法
1. ORDER BY 語法報(bào)錯(cuò)
-- 語法錯(cuò)誤 SELECT *, 'table1' FROM test_user u ORDER BY AGE UNION ALL SELECT *, 'table2' FROM test_user u ORDER BY AGE DESC;
第一個(gè) SELECT 語句也使用了 ORDER BY ，導(dǎo)致報(bào)錯(cuò)。
解決方案：第一個(gè) SELECT 語句加上括號。
-- 語法正確 (SELECT *, 'table1' FROM test_user u ORDER BY AGE) UNION ALL SELECT *, 'table2' FROM test_user u ORDER BY AGE DESC;
加上括號后，雖然不再報(bào)錯(cuò)，但是第一個(gè) SELECT 語句的排序失效。
那要是上下兩個(gè)都加上括號呢？
-- 語法正確 (SELECT *, 'table1' FROM test_user u ORDER BY AGE) UNION ALL (SELECT *, 'table2' FROM test_user u ORDER BY AGE DESC);
語法不報(bào)錯(cuò)，但是兩個(gè)排序都失效了。
原因大家也清楚，前面第三小節(jié)已經(jīng)講過了，UNION 在 ORDER BY 語句之前。
2. LIMIT 語法報(bào)錯(cuò)
同樣對于 LIMIT 語句也是一樣的。
-- 語法錯(cuò)誤 SELECT *, 'table1' FROM test_user u limit 0,1 UNION ALL SELECT *, 'table2' FROM test_user u limit 0,1;
解決方案：同樣也是第一個(gè) SELECT 語句加上括號。
-- 語法正確，1 條記錄 (SELECT *, 'table1' FROM test_user u limit 0,1) UNION ALL SELECT *, 'table2' FROM test_user u limit 0,1;
此時(shí)語法正確，但只返回一行記錄。
如果想要返回兩條記錄，就給第二個(gè) SELECT 語句也加上括號。
-- 語法正確，2 條記錄 (SELECT *, 'table1' FROM test_user u limit 0,1) UNION ALL (SELECT *, 'table2' FROM test_user u limit 0,1);
總結(jié)： UNION 后面執(zhí)行的 ORDER BY，LIMIT 語句注意使用時(shí)要加括號，否則報(bào)錯(cuò)。
總結(jié)
到此這篇關(guān)于MySQL中UNION語句用法詳解的文章就介紹到這了,更多相關(guān)MySQL UNION語句內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
在日常的網(wǎng)站維護(hù)和MYSQL數(shù)據(jù)庫管理中，會用到非常多的MYSQL命令，下面給大家介紹18個(gè)管理MYSQL數(shù)據(jù)庫時(shí)最常使用的命令2018-03-03
MySQL 5.7.18 release版安裝指南（含有bin文件版本）
這篇文章主要介紹了MySQL 5.7.18 release版安裝指南，本文只談?wù)搑elease版，即含有bin文件版本,需要的朋友可以參考下2017-04-04
關(guān)于Mysql-connector-java驅(qū)動版本問題總結(jié)
這篇文章主要介紹了Mysql-connector-java驅(qū)動版本問題,本文給大家介紹的很詳細(xì)，通過原因說明問題小結(jié)個(gè)人建議給大家展示的很好，需要的朋友可以參考下2021-06-06
InnoDB 類型MySql恢復(fù)表結(jié)構(gòu)與數(shù)據(jù)
MySQL中.frm文件：保存了每個(gè)表的元數(shù)據(jù)，包括表結(jié)構(gòu)的定義等，該文件與數(shù)據(jù)庫引擎無關(guān)。MySQL中.ibd文件：InnoDB引擎開啟了獨(dú)立表空間(my.ini中配置innodb_file_per_table = 1)產(chǎn)生的存放該表的數(shù)據(jù)和索引的文件。2018-01-01
在本篇文章中我們給大家分享了關(guān)于Mysql常用基準(zhǔn)測試命令的總結(jié)內(nèi)容，有需要的讀者們可以學(xué)習(xí)下。2018-10-10
linux mysql忘記密碼的多種解決或Access denied for user ''root''@''local
linux mysql忘記密碼的多種解決方法。2009-07-07
Express連接MySQL及數(shù)據(jù)庫連接池技術(shù)實(shí)例
數(shù)據(jù)庫連接池是程序啟動時(shí)建立足夠數(shù)量的數(shù)據(jù)庫連接對象，并將這些連接對象組成一個(gè)池，由程序動態(tài)地對池中的連接對象進(jìn)行申請、使用和釋放，本文重點(diǎn)給大家介紹Express連接MySQL及數(shù)據(jù)庫連接池技術(shù)，感興趣的朋友一起看看吧2022-02-02
Mysql添加用戶和設(shè)置權(quán)限的操作方法
這篇文章主要介紹了Mysql添加用戶和設(shè)置權(quán)限的操作方法,主要包括管理用戶，權(quán)限控制的相關(guān)知識，本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-07-07
在本篇文章里小編給大家分享的是關(guān)于navicat 8 for mysql建庫的方法以及相關(guān)知識點(diǎn)，需要的朋友們參考學(xué)習(xí)下。2019-08-08
網(wǎng)站訪問量越來越大，MySQL自然成為瓶頸，因此最近我一直在研究 MySQL 的優(yōu)化，第一步自然想到的是 MySQL 系統(tǒng)參數(shù)的優(yōu)化2006-12-12