MariaDB中UNION运算符用来做什么 详细怎么用
|
这篇文章主要介绍“MariaDB中UNION运算符用来做什么，具体怎么用”的相关知识，下面会通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“MariaDB中UNION运算符用来做什么，具体怎么用”文章能帮助大家解决问题。 在MariaDB数据库中，UNION运算符用于组合两个或更多SELECT语句的结果集。它删除各种SELECT语句之间的重复行。 语法 SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]; 注意：UNION运算符中的每个SELECT语句在具有相似数据类型的结果集中必须具有相同数量的字段。 1. 使用UNION操作符返回单个字段 下面来看一个从多个SELECT语句返回一个字段的UNION运算符的例子。(两个表具有相同的公共字段)。 假设有两张表:students和teachers。对应的表结构和数据如下 - 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; 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,'陈小祥','上海','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 | 陈小祥 | 上海 | 2013-06-07 | | 2 | 张天经 | 广州 | 2013-08-08 | | 3 | 李四光 | 三亚 | 2014-09-07 | +------------+--------+---------+----------------+ 3 rows in set (0.08 sec) 假设现在要查询所有学生和老师的姓名，以及他们的地址，可参考以下查询语句 - SELECT student_name AS name, student_address as address FROM Students UNION SELECT name,address FROM teachers; 执行上面查询语句，得到以下结果 - MariaDB [testdb]> SELECT student_name AS name, student_address as address -> FROM Students -> UNION -> SELECT name,address -> FROM teachers; +---------+-----------+ | name | address | +---------+-----------+ | Maxsu | Haikou | | JMaster | Beijing | | Mahesh | Guangzhou | | Kobe | Shanghai | | Blaba | Shenzhen | | 陈小祥 | 上海 | | 张天经 | 广州 | | 李四光 | 三亚 | +---------+-----------+ 8 rows in set (0.05 sec) 它显示了两个表中并删除重复的name列的值。 2. 具有ORDER BY子句的UNION运算符 使用ORDER BY子句的UNION运算符从两个表中检索多个列。参考以下语句 - SELECT student_id, student_name FROM Students WHERE student_name = 'Maxsu' UNION SELECT teacher_id, address FROM teachers WHERE teacher_id > 1 ORDER BY 1; 执行上面示例代码，得到以下结果 - MariaDB [testdb]> SELECT student_id, student_name -> FROM Students -> WHERE student_name = 'Maxsu' -> UNION -> SELECT teacher_id, address -> FROM teachers -> WHERE teacher_id > 1 -> ORDER BY 1; +------------+--------------+ | student_id | student_name | +------------+--------------+ | 1 | Maxsu | | 2 | 广州 | | 3 | 三亚 | +------------+--------------+ 3 rows in set (0.00 sec) 3. 一些其它的用法 具有不同列名的表之间的UNION： (SELECT e_name AS name, email FROM employees) UNION (SELECT c_name AS name, email FROM customers); 指定UNION的全局顺序并限制总行数： (SELECT name, email FROM employees) UNION (SELECT name, email FROM customers) ORDER BY name LIMIT 10; 添加一个常数行： (SELECT 'John Doe' AS name, 'john.doe@example.net' AS email) UNION (SELECT name, email FROM customers); 不同的类型： SELECT CAST('x' AS CHAR(1)) UNION 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 (SELECT 2, c_name AS name, email FROM customers) ORDER BY sort_column; 现在大家对于MariaDB中UNION运算符用来做什么，具体怎么用的内容应该都有一定的认识了吧，希望这篇能对大家有所帮助。 （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
