mysql union和union all(含实例)

union和union all

1.语法

  union:联合的意思，即把两次或多次查询结果合并起来。
  要求：两次查询的列数必须一致
  可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。
  如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合并，最终只保留一行。也可以这样理解，union会去掉重复的行。
  如果不想去掉重复的行，可以使用union all。

2.例子

select name FROM student union select name from teacher

  如果两次查询的列数不一致，就会报错：

select name,id  FROM student union select name from teacher

  union all :

select name  FROM student union all  select name from teacher

  所需的表和数据：

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` int(40) NULL DEFAULT NULL,
  `course` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `student` VALUES ('12', 'tbb', 80, '语文');
INSERT INTO `student` VALUES ('12', 'tbb', 70, '数学');
INSERT INTO `student` VALUES ('12', 'tbb', 60, '英语');
INSERT INTO `student` VALUES ('13', 'xb', 98, '语文');
INSERT INTO `student` VALUES ('13', 'xb', 96, '英语');
INSERT INTO `student` VALUES ('13', 'xb', 97, '数学');
INSERT INTO `student` VALUES ('14', 'xz', 18, '语文');
INSERT INTO `student` VALUES ('14', 'xz', 28, '数学');
INSERT INTO `student` VALUES ('14', 'xz', 8, '英语');
INSERT INTO `student` VALUES ('15', 'xm', 78, '物理');

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(40) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `teacher` VALUES ('tbb', 25);
INSERT INTO `teacher` VALUES ('xm', 24);