mysql--union
来源:互联网 发布:网络用语立场什么意思 编辑:程序博客网 时间:2024/06/05 08:00
关于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 | 黄虹 |
+-----------+-------------+
0 0
- mysql UNION UNION All
- mysql union ,UNION RESULT
- mysql union
- mysql union
- MySQL Union
- mysql union
- mysql union
- MySQL Union
- mysql--union
- mysql --- union
- mysql UNION 和 UNION ALL
- Mysql中UNION & UNION ALL
- mysql union all和union
- MYSQL UNION 用法
- MySQL中UNION
- MySQL中UNION集合
- mysql中的union
- mysql union一些语句
- 正向代理与反向代理的区别【Nginx读书笔记】
- MergeCursor引起的CursorAdapter更新无效问题
- Problem I: n皇后问题
- 循环 & 递归 & 递推 之间的联系和区别
- Python读写中文注意事项
- mysql--union
- APP保活
- 控制台出现 jQuery 1.9 .live() is not a function 解决方法
- Pandas中ix,loc,iloc有什么区别?
- 题目1440:Goldbach's Conjecture
- SpringMVC json 转换中错误的解决: Unrecognized field , not marked as ignorable
- 认识docker(一)
- network 多线程聊天
- 欢迎使用CSDN-markdown编辑器