数据库教程-mariadb教程-MariaDB Union All运算符

跨零代码为大家提供高品质的解决方案,请大家多多来访,跨零不胜感激,在此谢过。

MariaDB UNION ALL操作符与UNION操作符相同,但不会删除重复的记录。它返回查询中的所有行,并且不删除各种SELECT语句之间的重复行。

语法

SELECT expression1, expression2, ... expression_n   FROM tables   [WHERE conditions]   UNION ALL   SELECT expression1, expression2, ... expression_n   FROM tables   [WHERE conditions]; 

1. 使用UNION ALL操作符返回单个字段

下面来看一个从多个SELECT语句返回一个字段的UNION运算符的例子。(两个表具有相同的公共字段)。

假设有两张表:studentsteachers。对应的表结构和数据如下 –

students表中的数据:

MariaDB [testdb]> select * from students; +------------+--------------+-----------------+----------------+ | student_id | student_name | student_address | admission_date | +------------+--------------+-----------------+----------------+ |          1 | Maxsu        | Haikou          | 2017-01-07     | |          2 | JMaster      | Beijing         | 2016-05-07     | |          3 | Mahesh       | Guangzhou       | 2016-06-07     | |          4 | Kobe         | Shanghai        | 2016-02-07     | |          5 | Blaba        | Shenzhen        | 2016-08-07     | +------------+--------------+-----------------+----------------+ 5 rows in set (0.00 sec) 

teachers表中的数据:

USE testdb; DROP TABLE teachers; CREATE TABLE teachers(       teacher_id INT NOT NULL AUTO_INCREMENT,       name VARCHAR(100) NOT NULL,       address VARCHAR(40) NOT NULL,       admission_date DATE,       PRIMARY KEY ( teacher_id ) ); -- 插入数据 INSERT INTO teachers   (teacher_id, name, address, admission_date)   VALUES(1,'Maxsu','Haikou','2013-06-07 00:00:00');  INSERT INTO teachers   (teacher_id, name, address, admission_date)   VALUES(2,'张天经','广州','2013-08-08 00:00:00');  INSERT INTO teachers   (teacher_id, name, address, admission_date)   VALUES(3,'李四光','三亚','2014-09-07 00:00:00'); 

经过上创建和插入数据,现在teachers表中拥有以下数据记录 –

MariaDB [testdb]> select * from teachers; +------------+--------+---------+----------------+ | teacher_id | name   | address | admission_date | +------------+--------+---------+----------------+ |          1 | Maxsu  | Haikou  | 2013-06-07     | |          2 | 张天经 | 广州    | 2013-08-08     | |          3 | 李四光 | 三亚    | 2014-09-07     | +------------+--------+---------+----------------+ 3 rows in set (0.00 sec) 

假设现在要查询所有学生和老师的姓名,以及他们的地址,可参考以下查询语句 –

SELECT student_name AS name, student_address as address FROM Students UNION ALL SELECT name,address FROM teachers; 

执行上面查询语句,得到以下结果 –

MariaDB [testdb]> SELECT student_name AS name, student_address as address     -> FROM Students     -> UNION ALL     -> SELECT name,address     -> FROM teachers; +---------+-----------+ | name    | address   | +---------+-----------+ | Maxsu   | Haikou    | | JMaster | Beijing   | | Mahesh  | Guangzhou | | Kobe    | Shanghai  | | Blaba   | Shenzhen  | | Maxsu   | Haikou    | | 张天经  | 广州      | | 李四光  | 三亚      | +---------+-----------+ 8 rows in set (0.00 sec) 

它显示了两个表中重复的name列的值 – 'Maxsu'

2. 具有ORDER BY子句的UNION运算符

使用ORDER BY子句的UNION运算符从两个表中检索多个列。参考以下语句 –

SELECT student_id, student_name   FROM Students   WHERE student_name = 'Maxsu'   UNION  ALL SELECT teacher_id, address FROM teachers   WHERE teacher_id < 3  ORDER BY 1; 

执行上面示例代码,得到以下结果 –

MariaDB [testdb]> SELECT student_id, student_name     -> FROM Students     -> WHERE student_name = 'Maxsu'     -> UNION  ALL     -> SELECT teacher_id, address     -> FROM teachers     -> WHERE teacher_id < 3     -> ORDER BY 1; +------------+--------------+ | student_id | student_name | +------------+--------------+ |          1 | Haikou       | |          1 | Maxsu        | |          2 | 广州         | +------------+--------------+ 3 rows in set (0.00 sec) 

3. 一些其它的用法

具有不同列名的表之间的UNION:

(SELECT e_name AS name, email FROM employees) UNION ALL (SELECT c_name AS name, email FROM customers); 

指定UNION的全局顺序并限制总行数:

(SELECT name, email FROM employees) UNION ALL (SELECT name, email FROM customers) ORDER BY name LIMIT 10; 

添加一个常数行:

(SELECT 'John Doe' AS name, 'john.doe@example.net' AS email) UNION ALL (SELECT name, email FROM customers); 

不同的类型:

SELECT CAST('x' AS CHAR(1)) UNION ALL SELECT REPEAT('y',4); +----------------------+ | CAST('x' AS CHAR(1)) | +----------------------+ | x                    | | yyyy                 | +----------------------+ 

按照每个SELECT使用排序列的顺序返回结果:

(SELECT 1 AS sort_column, e_name AS name, email FROM employees) UNION ALL (SELECT 2, c_name AS name, email FROM customers) ORDER BY sort_column; 

  

从零到一,创造未来!跨零综合IT问题解决服务站,欢迎你的到来。Oracle数据库教程 mariadb教程 只为你绽放。

本文固定链接: http://kua0.com/2019/01/31/数据库教程-mariadb教程-mariadb-union-all运算符/

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注