句式:SELECT field1,field2... FROM table1 UNION SELECT field3,field4... FROM table2;
注意:
前后所选字段数目需相同,否则报错,例:
mysql> select `submission_date` from `runoob_tbl` union all select `runoob_author`,`submission_date` from `runoob_tbl2`;
1222 - The used SELECT statements have a different number of columns
所选出的内容显示时,会以前面的字段名称命名:
mysql> select `runoob_id`,`runoob_title` from `runoob_tbl` union select `runoob_author`,`submission_date` from `runoob_tbl2`;
+------------------+--------------+
| runoob_id | runoob_title |
+------------------+--------------+
| 1 | 学习 PHP |
| 2 | 学习 MySQL |
| 3 | 学习 C++ |
| 4 | JAVA 教程 |
| 5 | 连接 |
| 菜鸟教程 | 2018-08-15 |
| 菜鸟教程 | 2018-08-15 |
| RUNOOB.COM | 2016-05-06 |
| RUNOOB.COM | 2016-03-06 |
| 随便 | 2018-08-18 |
+------------------+--------------+
UNION ALL 表示将包括重的内容一起显示出来 ,UNION 或UNION DSTINCT表示去重显示。
mysql> select `runoob_author`,`submission_date` from `runoob_tbl` union select `runoob_author`,`submission_date` from `runoob_tbl2`;
+------------------+-----------------+
| runoob_author | submission_date |
+------------------+-----------------+
| 爆炸菜鸟教程爆炸 | 2018-08-15 |
| 菜鸟教程 | 2018-08-15 |
| RUNOOB.COM | 2016-05-06 |
| RUNOOB.COM | 2016-03-06 |
| 随便写 | 2018-08-18 |
+------------------+-----------------+
5 rows in set
mysql> select `runoob_author`,`submission_date` from `runoob_tbl` union all
select `runoob_author`,`submission_date` from `runoob_tbl2`;
+------------------+-----------------+
| runoob_author | submission_date |
+------------------+-----------------+
| 爆炸菜鸟教程爆炸 | 2018-08-15 |
| 菜鸟教程 | 2018-08-15 |
| RUNOOB.COM | 2016-05-06 |
| RUNOOB.COM | 2016-03-06 |
| 随便写 | 2018-08-18 |
| 爆炸菜鸟教程爆炸 | 2018-08-15 |
| 菜鸟教程 | 2018-08-15 |
| RUNOOB.COM | 2016-05-06 |
| RUNOOB.COM | 2016-03-06 |
| 随便写 | 2018-08-18 |
+------------------+-----------------+
其他可参考:https://blog.csdn/limingchuan123456789/article/details/8754273
待深究问题:UNION选出后的结果数据类型是怎么判定的?了解数据类型优先级:https://blog.csdn/limingchuan123456789/article/details/8754273
UNION语句后可以加ORDER BY来针对选出后的内容排序
更多推荐
mysql+union+不存在时,mysql union用法以及注意事项
发布评论