Mysql的联合查询命令UNION和UNION ALL
SQL UNION 语法:
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2
注释:默认UNION 操作符选取不同的值.如果允许重复的值,请使用 UNION ALL.
当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行.
SQL UNION ALL 语法
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2
注释:UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名.
注意:UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名.
#两张表的数据如下:
mysql> select hid,bname from boy where hid=1;
+-----+-------+
| hid | bname |
+-----+-------+
| 1 | lisi |
+-----+-------+
1 row in set (0.00 sec)
mysql> select hid,bname from boy where hid=3;
+-----+--------+
| hid | bname |
+-----+--------+
| 3 | 赵六 |
+-----+--------+
1 row in set (0.00 sec)
mysql> select hid,bname from boy where hid=1 union select hid,bname from boy where hid=3;
+-----+--------+
| hid | bname |
+-----+--------+
| 1 | lisi |
| 3 | 赵六 |
+-----+--------+
2 rows in set (0.00 sec)
#union语句必须满足1个条件;各语句取出的列数相同.
mysql> select hid,bname from boy where hid=1
-> union
-> select bname from boy where hid=3;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
#union列名称未必要一致,列名称会使用第一条sql的列名称为准.
mysql> select hid,bname from boy where hid=1;
+-----+-------+
| hid | bname |
+-----+-------+
| 1 | lisi |
+-----+-------+
1 row in set (0.00 sec)
mysql> select cno,score from sc where cno="k1";
+-----+-------+
| cno | score |
+-----+-------+
| K1 | 83 |
| K1 | 85 |
| K1 | 92 |
+-----+-------+
3 rows in set (0.00 sec)
mysql> select hid,bname from boy where hid=1
-> union
-> select cno,score from sc where cno="k1";
+-----+-------+
| hid | bname |
+-----+-------+
| 1 | lisi |
| K1 | 83 |
| K1 | 85 |
| K1 | 92 |
+-----+-------+
4 rows in set (0.00 sec)
#union查询支持跨表查询.
mysql> select hid,bname from boy where hid=1;
+-----+-------+
| hid | bname |
+-----+-------+
| 1 | lisi |
+-----+-------+
1 row in set (0.01 sec)
mysql> select hid,bname from girl where hid=3;
+-----+--------+
| hid | bname |
+-----+--------+
| 3 | 默默 |
+-----+--------+
1 row in set (0.00 sec)
mysql> select hid,bname from boy where hid=1 union select hid,bname from girl where hid=3;
+-----+--------+
| hid | bname |
+-----+--------+
| 1 | lisi |
| 3 | 默默 |
+-----+--------+
2 rows in set (0.00 sec)
#girl表3条数据.
mysql> select * from girl;
+-----+--------+
| hid | bname |
+-----+--------+
| 3 | 默默 |
| 2 | 羞羞 |
| 5 | 海燕 |
+-----+--------+
3 rows in set (0.00 sec)
#boy表4条数据.
mysql> select * from boy;
+-----+--------+
| hid | bname |
+-----+--------+
| 1 | lisi |
| 2 | 王五 |
| 3 | 赵六 |
| 5 | 海燕 |
+-----+--------+
4 rows in set (0.00 sec)
#查询结果只有7条数据?
mysql> select * from girl
-> union
-> select * from boy;
+-----+--------+
| hid | bname |
+-----+--------+
| 3 | 默默 |
| 2 | 羞羞 |
| 5 | 海燕 |
| 1 | lisi |
| 2 | 王五 |
| 3 | 赵六 |
+-----+--------+
6 rows in set (0.00 sec)
注意:使用union时,完全相等的行将会被合并,合并是比较耗时的操作,一般不让union进行合并,使用"union all"避免合并.
#使用"union all"相同的行将不会被合并.
mysql> select * from girl union all select * from boy;
+-----+--------+
| hid | bname |
+-----+--------+
| 3 | 默默 |
| 2 | 羞羞 |
| 5 | 海燕 |
| 1 | lisi |
| 2 | 王五 |
| 3 | 赵六 |
| 5 | 海燕 |
+-----+--------+
7 rows in set (0.00 sec)
#union的子句中不写order by,如下面sql,结果并未体现出order by排序的结果.
mysql> (select * from girl order by hid desc) union all (select * from boy order by hid desc);
+-----+--------+
| hid | bname |
+-----+--------+
| 3 | 默默 |
| 2 | 羞羞 |
| 5 | 海燕 |
| 1 | lisi |
| 2 | 王五 |
| 3 | 赵六 |
| 5 | 海燕 |
+-----+--------+
7 rows in set (0.00 sec)
#union sql合并后得到的总的结果,可以使用order by,子句order by失去意义.
mysql> select * from girl union all select * from boy order by hid desc;
+-----+--------+
| hid | bname |
+-----+--------+
| 5 | 海燕 |
| 5 | 海燕 |
| 3 | 默默 |
| 3 | 赵六 |
| 2 | 王五 |
| 2 | 羞羞 |
| 1 | lisi |
+-----+--------+
7 rows in set (0.00 sec)
#下面是两张表合并的数据,将其中bname相同的行的hid求和.
mysql> select * from boy union all select * from girl as tmp;
+-----+--------+
| hid | bname |
+-----+--------+
| 1 | lisi |
| 2 | 王五 |
| 3 | 赵六 |
| 5 | 海燕 |
| 3 | 默默 |
| 2 | 羞羞 |
| 5 | 海燕 |
+-----+--------+
7 rows in set (0.00 sec)
#将两张表的查询结果看成一张表,使用group by分组求和.
mysql> select sum(hid),bname from (select * from boy union all select * from girl) as tmp group by bname;
+----------+--------+
| sum(hid) | bname |
+----------+--------+
| 1 | lisi |
| 10 | 海燕 |
| 2 | 王五 |
| 2 | 羞羞 |
| 3 | 赵六 |
| 3 | 默默 |
+----------+--------+
6 rows in set (0.00 sec)
转载于:https://blog.51cto.com/215687833/2342495