union的用法
union用来连接两个查询语句，把两个查询语句的查询结果合并起来，两个查询语句的查询字段个数必须一样，否则会出错，查询的字段可以不一样，类型也可以不一样，但是这样查询的意义不大，如果查询的字段不一样，最终的结果集以前者查询的字段为准。如果用union进行连接，碰到所有字段值一样的列，就会合并，去掉重复的行，如果用union all进行连接，则不会去掉重复的内容，所有的内容都被取出。
mysql> (select goods_id,goods_name,cat_id from goods where cat_id=2 order by goo
ds_id asc)
-> union (select goods_id,goods_name,cat_id from goods where cat_id=4 order
by goods_id desc);
+----------+--------------+--------+
| goods_id | goods_name | cat_id |
+----------+--------------+--------+
| 16 | 恒基伟业G101 | 2 |
| 1 | KD876 | 4 |
| 14 | 诺基亚5800XM | 4 |
| 18 | 夏新T5 | 4 |
+----------+--------------+--------+
4 rows in set (0.00 sec)
#在每个查询语句中进行排序，再进行连接，其中的排序就没有意义，所以mysql回把排序的语句进行优化掉
mysql> select goods_id,cat_id,goods_name from goods where cat_id=2
-> union
-> select goods_id,cat_id,goods_name from goods where cat_id=4
-> order by goods_id;
+----------+--------+--------------+
| goods_id | cat_id | goods_name |
+----------+--------+--------------+
| 1 | 4 | KD876 |
| 14 | 4 | 诺基亚5800XM |
| 16 | 2 | 恒基伟业G101 |
| 18 | 4 | 夏新T5 |
+----------+--------+--------------+
4 rows in set (0.00 sec)
mysql> #排序放在最后是针对最后的结果集进行排序，有意义，
mysql> (select goods_id,cat_id,goods_name from goods where cat_id=3 order by goo
ds_id limit 3)
-> union
-> (select goods_id,cat_id,goods_name from goods where cat_id=4 order by goo
ds_id limit 2)
-> ;
+----------+--------+--------------+
| goods_id | cat_id | goods_name |
+----------+--------+--------------+
| 8 | 3 | 飞利浦9@9v |
| 9 | 3 | 诺基亚E66 |
| 10 | 3 | 索爱C702c |
| 1 | 4 | KD876 |
| 14 | 4 | 诺基亚5800XM |
+----------+--------+--------------+
5 rows in set (0.05 sec)
#这次排序影响的最终的显示结果有意义，所以order by 起了作用
一道面试题
mysql> create table a (
-> id char(1),
-> num int
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.12 sec)
mysql> insert into a values ('a',5),('b',10),('c',15),('d',10);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> create table b (
-> id char(1),
-> num int
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> insert into b values ('b',5),('c',15),('d',20),('e',99);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
#将两张表合并，相同id的num相加
mysql> select id,sum(num) from (select * from a union all select * from b) as temp group by id;
+------+----------+
| id | sum(num) |
+------+----------+
| a | 5 |
| b | 15 |
| c | 30 |
| d | 30 |
| e | 99 |
+------+----------+
5 rows in set (0.05 sec)
#将两张表用union all合并，然后再按照id分组，求和，达到最终的目的