联合查询是多表查询的一种方式，经常应用在分表操作中，在保证多个SELECT语句的查询字段数相同的情况下，合并多个查询的结果。
语法
1 SELECT … 2 UNION [ALL | DISTINCT] SELECT … 3 [UNION [ALL | DISTINCT] SELECT …];
UNION是实现联合查询的关键字。
ALL表示保存所有的查询结果。
DISTINCT是默认值，可以省略，表示去除完全重复的记录
1 CREATE DATABASE mahaiwuji; 2 3 USE mahaiwuji; 4 5 CREATE TABLE student1 ( 6 sid INT (4) PRIMARY KEY, 7 sname VARCHAR (36), 8 score INT 9 ) ENGINE = INNODB DEFAULT CHARSET = utf8; 10 11 INSERT INTO student1 VALUES (1,‘a1‘,60); 12 INSERT INTO student1 VALUES (2,‘a2‘,65); 13 INSERT INTO student1 VALUES (3,‘a3‘,70); 14 INSERT INTO student1 VALUES (4,‘a4‘,75); 15 INSERT INTO student1 VALUES (5,‘a5‘,80); 16 17 18 CREATE TABLE student2 ( 19 sid INT (4) PRIMARY KEY, 20 sname VARCHAR (36), 21 score INT 22 ) ENGINE = INNODB DEFAULT CHARSET = utf8; 23 24 INSERT INTO student2 VALUES (5,‘a5‘,80); 25 INSERT INTO student2 VALUES (6,‘a6‘,85); 26 INSERT INTO student2 VALUES (7,‘a7‘,90); 27 INSERT INTO student2 VALUES (8,‘a8‘,95); 28 INSERT INTO student2 VALUES (9,‘a9‘,100);
1 -- 自动去掉完全重复的数据 2 SELECT * FROM student1 3 UNION 4 SELECT * FROM student2;
1 -- 合并所有的数据 2 SELECT * FROM student1 3 UNION ALL 4 SELECT * FROM student2;
1 -- 合并部分数据 2 SELECT * FROM student1 WHERE sid=1 3 UNION ALL 4 SELECT * FROM student2;
1 -- 排序 2 SELECT * FROM student1 3 UNION ALL 4 SELECT * FROM student2 5 ORDER BY sid DESC;