union

1.union 联合查询，将多条select语句的结果合并到一起

现在有两张表

mytable

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` tinyint(3) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

mytable1

CREATE TABLE `mytable1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

现在想查出两个表当中 每个表年龄最大的人

select name,age from mytable order by age desc limit 2;
select name,age from mytable1 order by age desc limit 2;

在这两个语句之间加入 union 关键字即可

(select name,age from mytable order by age desc limit 2) union
(select name,age from mytable1 order by age desc limit 2);

2.union的使用场景

获得数据的逻辑比较复杂或者冲突时，将每个查询条件分开，使用union 联合起来查询

3.union 默认会去掉重复的记录

如果想显示重复的记录，可以在 union 后加上all关键字

(select name,age from mytable order by age desc limit 2) union all
(select name,age from mytable1 order by age desc limit 2);

4.子语句的order by 只有配合limit 使用才能生效

若要对全部的记录进行排序，在最后一个select 语句后增加相应的排序规则

(select name,age from mytable order by age desc limit 2) union all
(select name,age from mytable1 order by age desc limit 2) order by age;