数据库教程-mariadb教程-MariaDB Intersect运算符

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

INTERSECT运算符用于返回2个或更多表的交集。 如果两个表中都存在记录,它将被包含在INTERSECT结果中。 否则,它将从INTERSECT结果中被省略。

语法

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

说明图如下所示 –

MariaDB Intersect运算符

1. INTERSECT运算符返回单个字段

以下是针对INTERSECT运算符的一般查询,但不会在MariaDB中超混杂。

假设有两张表: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) 

假设现在要查询学生和老师的姓名有哪几个?参考以下查询语句 –

-- 理想语句,但是MariaDB中不支持  SELECT student_name   FROM Students INTERSECT   SELECT name   FROM teachers; 

上面语句的代替语句是 –

SELECT s.student_name AS name FROM Students s WHERE s.student_name IN (SELECT t.name FROM teachers t); 

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

MariaDB [testdb]> SELECT s.student_name AS name     -> FROM Students s     -> WHERE s.student_name IN (SELECT t.name FROM teachers t); +-------+ | name  | +-------+ | Maxsu | +-------+ 1 row in set (0.10 sec) 

它显示了两个表中name列都有存在的值 – 'Maxsu'

2. INTERSECT运算符指定WHERE条件

以下是针对INTERSECT运算符的一般查询,但不能在MariaDB中正常使用。参考以下语句 –

-- 理想语句,但是MariaDB中不支持  SELECT s.student_name   FROM Students s WHERE student_id<100 INTERSECT   SELECT t.name   FROM teachers t WHERE t.address = 'Haikou'; 

上面语句的代替语句是 –

SELECT s.student_name AS name FROM Students s WHERE s.student_id<100 AND s.student_name IN (SELECT t.name FROM teachers t  WHERE t.address = 'Haikou'); 

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

MariaDB [testdb]> SELECT s.student_name AS name     -> FROM Students s     -> WHERE s.student_id<100 AND s.student_name IN (SELECT t.name FROM teachers t  WHERE t.address = 'Haikou'); +-------+ | name  | +-------+ | Maxsu | +-------+ 1 row in set (0.00 sec) 

  

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

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

为您推荐

发表评论

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