mysql union查询_24.MySQL中的联合查询（UNION）

1.简介

联合查询是多表查询的一种方式，经常应用在分表操作中，在保证多个SELECT语句的查询字段数相同的情况下，合并多个查询的结果。

语法

SELECT …UNION [ALL | DISTINCT] SELECT …[UNION [ALL | DISTINCT] SELECT …];

UNION是实现联合查询的关键字。

ALL表示保存所有的查询结果。

DISTINCT是默认值，可以省略，表示去除完全重复的记录

2.准备

CREATE DATABASE mahaiwuji;USE mahaiwuji;CREATE TABLE student1 (    sid INT (4) PRIMARY KEY,    sname VARCHAR (36),   score INT ) ENGINE = INNODB DEFAULT CHARSET = utf8;INSERT INTO student1 VALUES (1,'a1',60);INSERT INTO student1 VALUES (2,'a2',65);INSERT INTO student1 VALUES (3,'a3',70);INSERT INTO student1 VALUES (4,'a4',75);INSERT INTO student1 VALUES (5,'a5',80);CREATE TABLE student2 (    sid INT (4) PRIMARY KEY,    sname VARCHAR (36),    score INT ) ENGINE = INNODB DEFAULT CHARSET = utf8;INSERT INTO student2 VALUES (5,'a5',80);INSERT INTO student2 VALUES (6,'a6',85);INSERT INTO student2 VALUES (7,'a7',90);INSERT INTO student2 VALUES (8,'a8',95);INSERT INTO student2 VALUES (9,'a9',100);

3.案例

-- 自动去掉完全重复的数据SELECT * FROM student1UNIONSELECT * FROM student2;

-- 合并所有的数据SELECT * FROM student1UNION ALLSELECT * FROM student2;

-- 合并部分数据SELECT * FROM student1 WHERE sid=1UNION ALLSELECT * FROM student2;

-- 排序SELECT * FROM student1UNION ALLSELECT * FROM student2ORDER BY sid DESC;