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)

已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页