MySQL查询交集、并集、差集
背景和使用的数据样本:
该章节学些主要涉及到Datawhale SQL 组队学习任务;
本次使用的数据,由Datawhale 开源提供,具体代码见文章尾部:各位看官可以直接复制到MySQL Workbench 上运行生成数据库和表格。MySQL WorkBench 如何对数据进行我们常见的集合运算呢?Union、 INTERSECT(遗憾的是MySQL 8.0 不支持该运算用where /in 实现)、EXCEPT(遗憾的是MySQL 8.0 不支持该运算,但巧妙的用where …Not in 实现),本博客日记主要记录一些注意事项。那么MySQL如何处理交集和差集呢?
基本语法
Select <列名1>,<列名2>,<列名3>... from <表名>
union -- 或者其他 intersect、except、union all等)
Select <列名1>,<列名2>,<列名3>... from <表名>
注意事项
- 首先:任何执行Union、 INTERSECT、EXCEPT的语句,都要注意,该关键字前后的Select 语句中选择的列的数量要一致,不一致会提示错误:
select product_id, product_name -- 注意这里是2选择查询两个列
from product
union
select product_id, product_name -- 这里的列的数量要与前面的列的数量一致
from product2;
正确的代码输出结果如下:
其次,select 语句中列数量不一致时,提示:
Error Code: 1222. The used SELECT statements have a different number of columns 0.000 sec
但是,对于select 中的选择的可以不一定是表格中原来就有的列表,此时能够正常查询数据表,具体如下:
select product_id, product_name
from product
union
select product_id, '1'
from product2;
结果如下:
- Union vs Union All 的区别
Union 操作,自动去重复,即两个或多个数据表中相同的行只吃出现一次;
若想要所有表中的对应的数据都显示的话,则需要用到Union all - select sysdate() ; 查询返回系统当前的时间;
select product_name, sale_price, purchase_price
from product
union
select sysdate(), sysdate() , sysdate()
from product;
查询结果如下:
**4.MySQL 8.0 不支持交运算INTERSECT、except **
5. 查询差集:
-- 使用 IN 子句的实现方法
SELECT *
FROM product
WHERE product_id NOT IN (SELECT product_id
FROM product2)
结果:
/* v 9.08
SQL脚本的一些要点:
0.存储引擎使用 InnoDB, 字符集改为 utf8mb4 以更好地支持中文.
1.所有表名所使用的英文字母都改为小写(后续章节中,SQL查询中的表名也需要相应修改为小写)
2.所有列名所使用的英文字母确认为小写(后续章节中,SQL查询中的列名也需要相应修改为小写)
3.存在问题的数据, 例如 inventoryproduct 表的inventory_id列应为P开头的, 已修正为正确的数据.
4.需测试SQL脚本在命令行及各个客户端中是否能被正确执行.
* MySQL Workbench 已测试通过
* DBeaver 已测试通过(使用"执行SQL脚本(CTR+x)")
* HeidiSQL 已测试通过
* navicat 已测试通过
* sqlyog 已测试通过
* 命令行 测试未通过. 插入中文数据时提示" Data too long for column 'product_name' at row 1"
*/
CREATE DATABASE /*!32312 IF NOT EXISTS*/`shop` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `shop`;
/*Table structure for table `chars` */
DROP TABLE IF EXISTS `chars`;
CREATE TABLE `chars` (
`chr` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`chr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `chars` */
insert into `chars`(`chr`) values ('1'),('10'),('11'),('2'),('222'),('3');
/*Table structure for table `empskills` */
DROP TABLE IF EXISTS `empskills`;
CREATE TABLE `empskills` (
`emp` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`skill` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`emp`,`skill`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*Data for the table `empskills` */
insert into `empskills`(`emp`,`skill`) values ('平井','C++'),('平井','Oracle'),('平井','Perl'),('平井','PHP'),('平井','UNIX'),('渡来','Oracle'),('相田','C#'),('相田','Java'),('相田','Oracle'),('相田','UNIX'),('神崎','Java'),('神崎','Oracle'),(