mysql select符合查询_MySQL SELECT 联合查询

这里主要分享一下联合查询的用法。

MySQL使用UNION和UNION ALL实现数据的联合查询。

假设有以下两个表：

mysql>select * from staff;

+----+----------+-------+

| 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_nameFROM table1UNIONSELECT column_nameFROM 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)