MySQL SELECT 联合查询

+----+----------+-------+ | id | name | slary | +----+----------+-------+ | 1 | guoding | 3200 | | 2 | dingtao | 2800 | | 3 | haofugui | 3500 | | 4 | guoming | 4000 | | 5 | haotian | 2900 | | 6 | fengfei | 3200 | | 7 | guoting | 2600 | +----+----------+-------+ 7 rows in set (0.00 sec) mysql> select * from staff_1; +----+----------+-------+ | id | name | slary | +----+----------+-------+ | 1 | guoding | 3200 | | 2 | liding | 2700 | | 3 | haofugui | 3500 | | 4 | xiaoli | 3600 | | 5 | yazhi | 3200 | | 6 | yuanfei | 3200 | | 7 | guoting | 3500 | +----+----------+-------+ 7 rows in set (0.00 sec)

　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