关于union,union all
union相当于对多个检索结果的组合,并去除重复数据,union all则不去除重复数据数据库版本
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.17-log |
+------------+
表结构如下:
mysql> select * from student;
+-----------+-------------+------+-------+
| studentId | studentName | sex | class |
+-----------+-------------+------+-------+
| 1 | 李明 | 1 | 1 |
| 2 | 小红 | 2 | 1 |
| 3 | 王强 | 1 | 1 |
| 4 | 李磊 | 1 | 1 |
| 5 | 韩梅梅 | 2 | 2 |
| 6 | 黄虹 | 2 | 2 |
| 7 | 都天 | 1 | 2 |
| 8 | 黄虹 | 1 | 2 |
+-----------+-------------+------+-------+
其中“黄虹” 是同名的一个男生和一个女生
只查男生的姓名:
mysql> select studentName from student where sex = 1;
+-------------+
| studentName |
+-------------+
| 李明 |
| 王强 |
| 李磊 |
| 都天 |
| 黄虹 |
+-------------+
只查女生的姓名:
mysql> select studentName from student where sex = 2;
+-------------+
| studentName |
+-------------+
| 小红 |
| 韩梅梅 |
| 黄虹 |
+-------------+
如果将上述两个检索结构用union合并的话,结果如下:
mysql> select studentName from student where sex = 1
-> union
-> select studentName from student where sex = 2;
+-------------+
| studentName |
+-------------+
| 李明 |
| 王强 |
| 李磊 |
| 都天 |
| 黄虹 |
| 小红 |
| 韩梅梅 |
+-------------+
可以看出“黄虹”由于重复,被去除掉了。
下面再用union all 做合并:
mysql> select studentName from student where sex = 1
-> union all
-> select studentName from student where sex = 2;
+-------------+
| studentName |
+-------------+
| 李明 |
| 王强 |
| 李磊 |
| 都天 |
| 黄虹 |
| 小红 |
| 韩梅梅 |
| 黄虹 |
+-------------+
可以看出重复的“黄虹”并没有被去除掉。
如果我们的检索结果再加上“studentId”而使每一个数据都是独特的话,使用union合并,就会显示所有的结果
mysql> select studentId,studentName from student where sex = 1
-> union
-> select studentId,studentName from student where sex = 2;
+-----------+-------------+
| studentId | studentName |
+-----------+-------------+
| 1 | 李明 |
| 3 | 王强 |
| 4 | 李磊 |
| 7 | 都天 |
| 8 | 黄虹 |
| 2 | 小红 |
| 5 | 韩梅梅 |
| 6 | 黄虹 |
+-----------+-------------+
关于union的排序功能,阅读很多博客,都说union是会自动排序的,但是我并没有实验出来,上面的sql语句没有按照studentId来排序。
下面我又在实验楼上用5.5的mysql实验了一下,依然没有排序。
mysql> select version();
+-------------------------+
| version() |
+-------------------------+
| 5.5.50-0ubuntu0.14.04.1 |
+-------------------------+
mysql> select * from student;
+-----------+-------------+------+-------+
| studentId | studentName | sex | class |
+-----------+-------------+------+-------+
| 1 | liming | 1 | 1 |
| 2 | xiaohong | 2 | 1 |
| 3 | wangqiang | 1 | 1 |
| 4 | lilei | 1 | 1 |
| 5 | hanmeimei | 2 | 2 |
| 6 | huanghong | 2 | 2 |
| 7 | dutian | 1 | 2 |
| 8 | huanghong | 1 | 2 |
+-----------+-------------+------+-------+
mysql> select studentId ,studentName from student where sex = 1
-> union
-> select studentId ,studentName from student where sex = 2;
+-----------+-------------+
| studentId | studentName |
+-----------+-------------+
| 1 | liming |
| 3 | wangqiang |
| 4 | lilei |
| 7 | dutian |
| 8 | huanghong |
| 2 | xiaohong |
| 5 | hanmeimei |
| 6 | huanghong |
+-----------+-------------+
没有按studentId排序
mysql> select * from student where studentId >2 and studentId < 6
-> union
-> select * from student where studentId < 4;
+-----------+-------------+------+-------+
| studentId | studentName | sex | class |
+-----------+-------------+------+-------+
| 3 | wangqiang | 1 | 1 |
| 4 | lilei | 1 | 1 |
| 5 | hanmeimei | 2 | 2 |
| 1 | liming | 1 | 1 |
| 2 | xiaohong | 2 | 1 |
+-----------+-------------+------+-------+
没有按studentId排序
如果需要排序的话,可以使用order by
sql语句:
mysql> select studentId,studentName from student where sex = 2
-> union
-> select studentId,studentName from student where sex = 1
-> order by studentId;
+-----------+-------------+
| studentId | studentName |
+-----------+-------------+
| 1 | 李明 |
| 2 | 小红 |
| 3 | 王强 |
| 4 | 李磊 |
| 5 | 韩梅梅 |
| 6 | 黄虹 |
| 7 | 都天 |
| 8 | 黄虹 |
+-----------+-------------+