首页 / MYSQL / MySQL SELECT 联合查询
MySQL SELECT 联合查询
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL SELECT 联合查询,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5396字,纯文字阅读大概需要8分钟。
内容图文
![MySQL SELECT 联合查询](/upload/InfoBanner/zyjiaocheng/499/25d84eb6e9684058b0de1d122e6eed2a.jpg)
mysql> select * from staff_2;
+----+-------+----------+
| id | slary | name |
+----+-------+----------+
| 1 | 3200 | guoding |
| 2 | 2700 | liding |
| 3 | 3500 | haofugui |
| 4 | 3600 | xiaoli |
| 5 | 3200 | yazhi |
| 6 | 3200 | yuanfei |
| 7 | 3500 | guoting |
+----+-------+----------+
7 rows in set (0.00 sec)
mysql> select * from staff_3;
+----------+-------+
| name | slary |
+----------+-------+
| guoding | 3200 |
| liding | 2700 |
| haofugui | 3500 |
| xiaoli | 3600 |
+----------+-------+
4 rows in set (0.00 sec)
1. UNION
语句:SELECT
column_name
FROM
table1
UNION
SELECT
column_name
FROM
table2
说明:1)用于合并两个或多个 SELECT 语句的结果集,并消去表中任何重复行;
2)UNION 内部的 SELECT 语句必须拥有相同数量的列,列也必须拥有相似的数据类型;
3)同时,每条 SELECT 语句中的列的顺序必须相同;
4)如果子句中有order by,limit等,需用括号()包起来,推荐放到所有子句之后,即对最终合并的结果来排序或筛选。
示例:
mysql> select * from staff union select * from staff_1;
+----+----------+-------+
| id | name | slary |
+----+----------+-------+
| 1 | guoding | 3200 |
| 2 | dingtao | 2800 |
| 3 | haofugui | 3500 |
| 4 | guoming | 4000 |
| 5 | haotian | 2900 |
| 6 | fengfei | 3200 |
| 7 | guoting | 2600 |
| 2 | liding | 2700 |
| 4 | xiaoli | 3600 |
| 5 | yazhi | 3200 |
| 6 | yuanfei | 3200 |
| 7 | guoting | 3500 |
+----+----------+-------+
12 rows in set (0.00 sec)
mysql> select * from staff union select * from staff_2;
+----+----------------------+----------------------+
| id | name | slary |
+----+----------------------+----------------------+
| 1 | guoding | 3200 |
| 2 | dingtao | 2800 |
| 3 | haofugui | 3500 |
| 4 | guoming | 4000 |
| 5 | haotian | 2900 |
| 6 | fengfei | 3200 |
| 7 | guoting | 2600 |
| 1 | 3200 | guoding |
| 2 | 2700 | liding |
| 3 | 3500 | haofugui |
| 4 | 3600 | xiaoli |
| 5 | 3200 | yazhi |
| 6 | 3200 | yuanfei |
| 7 | 3500 | guoting |
+----+----------------------+----------------------+
14 rows in set (0.00 sec)
mysql> select * from staff union select name from staff_1;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> select * from staff union select * from staff_3;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> (select * from staff order by id asc) union (select * from staff_1 order by slary desc);
+----+----------+-------+
| id | name | slary |
+----+----------+-------+
| 1 | guoding | 3200 |
| 2 | dingtao | 2800 |
| 3 | haofugui | 3500 |
| 4 | guoming | 4000 |
| 5 | haotian | 2900 |
| 6 | fengfei | 3200 |
| 7 | guoting | 2600 |
| 2 | liding | 2700 |
| 4 | xiaoli | 3600 |
| 5 | yazhi | 3200 |
| 6 | yuanfei | 3200 |
| 7 | guoting | 3500 |
+----+----------+-------+
12 rows in set (0.00 sec)
2. UNION ALL
作用及规则与UNION相同,区别是,UNION ALL不消除重复行
示例:
mysql> select * from staff union select * from staff_1; +----+----------+-------+ | id | name | slary | +----+----------+-------+ | 1 | guoding | 3200 | | 2 | dingtao | 2800 | | 3 | haofugui | 3500 | | 4 | guoming | 4000 | | 5 | haotian | 2900 | | 6 | fengfei | 3200 | | 7 | guoting | 2600 | | 2 | liding | 2700 | | 4 | xiaoli | 3600 | | 5 | yazhi | 3200 | | 6 | yuanfei | 3200 | | 7 | guoting | 3500 | +----+----------+-------+ 12 rows in set (0.00 sec) mysql> select * from staff union all select * from staff_1; +----+----------+-------+ | id | name | slary | +----+----------+-------+ | 1 | guoding | 3200 | | 2 | dingtao | 2800 | | 3 | haofugui | 3500 | | 4 | guoming | 4000 | | 5 | haotian | 2900 | | 6 | fengfei | 3200 | | 7 | guoting | 2600 | | 1 | guoding | 3200 | | 2 | liding | 2700 | | 3 | haofugui | 3500 | | 4 | xiaoli | 3600 | | 5 | yazhi | 3200 | | 6 | yuanfei | 3200 | | 7 | guoting | 3500 | +----+----------+-------+ 14 rows in set (0.00 sec)
MySQL SELECT 联合查询
标签:合并 color ble ber sql 实现 desc 查询 union
本文系统来源:https://www.cnblogs.com/yy20141204bb/p/8393864.html
内容总结
以上是互联网集市为您收集整理的MySQL SELECT 联合查询全部内容,希望文章能够帮你解决MySQL SELECT 联合查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。