您当前所在位置：首页软件教程数据库MySQL 查询并集、交集、差集方式详解

MySQL 查询并集、交集、差集方式详解

归类：数据库更新时间：2023-12-07 22:59:41作者：互联网人气：146

MySQL如何查询交集、并集、差集

MySQL WorkBench 如何对数据进行我们常见的集合运算呢？

Union、 INTERSECT（遗憾的是MySQL 8.0 不支持该运算用where /in 实现）、EXCEPT（遗憾的是MySQL 8.0 不支持该运算，但巧妙的用where …Not in 实现），本博客日记主要记录一些注意事项。

那么MySQL是如何处理交集和差集呢？

基本语法

1
2
3
Select <列名1>,<列名2>,<列名3>... from <表名>
union -- 或者其他 intersect、except、union all等）
Select <列名1>,<列名2>,<列名3>... from <表名>

注意事项

1.首先：任何执行Union、 INTERSECT、EXCEPT的语句

都要注意，该关键字前后的Select 语句中选择的列的数量要一致，不一致会提示错误：

1
2
3
4
5
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 中的选择的可以不一定是表格中原来就有的列表，此时能够正常查询数据表，具体如下：

1
2
3
4
5
select product_id, product_name
from product
union
select product_id, '1'
from product2;

结果如下：

2.Union vs Union All 的区别

Union 操作，自动去重复，即两个或多个数据表中相同的行只吃出现一次；

若想要所有表中的对应的数据都显示的话，则需要用到Union all

3.select sysdate() ; 查询返回系统当前的时间；

1
2
3
4
5
select product_name, sale_price, purchase_price
from product
union
select sysdate(), sysdate() , sysdate()
from product;

查询结果如下：

**4.MySQL 8.0 不支持交运算INTERSECT、except **

5. 查询差集：

1
2
3
4
5
-- 使用 IN 子句的实现方法
SELECT *
  FROM product
 WHERE product_id NOT IN (SELECT product_id
                            FROM product2)

结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
/* 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'),('神崎','UNIX'),('若田部','Perl');
 
/*Table structure for table `inventoryproduct` */
 
DROP TABLE IF EXISTS `inventoryproduct`;
 
CREATE TABLE `inventoryproduct` (
  `inventory_id` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `product_id` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `inventory_quantity` int NOT NULL,
  PRIMARY KEY (`inventory_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 
/*Data for the table `inventoryproduct` */
 
insert  into `inventoryproduct`(`inventory_id`,`product_id`,`inventory_quantity`) values ('P001','0001',0),('P001','0002',120),('P001','0003',200),('P001','0004',3),('P001','0005',0),('P001','0006',99),('P001','0007',999),('P001','0008',200),('P002','0001',10),('P002','0002',25),('P002','0003',34),('P002','0004',19),('P002','0005',99),('P002','0006',0),('P002','0007',0),('P002','0008',18);
 
/*Table structure for table `product` */
 
DROP TABLE IF EXISTS `product`;
 
CREATE TABLE `product` (
  `product_id` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `product_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `product_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `sale_price` int DEFAULT NULL,
  `purchase_price` int DEFAULT NULL,
  `regist_date` date DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 
/*Data for the table `product` */
 
insert  into `product`(`product_id`,`product_name`,`product_type`,`sale_price`,`purchase_price`,`regist_date`) values ('0001','T恤','衣服',1000,500,'2009-09-20'),('0002','打孔器','办公用品',500,320,'2009-09-11'),('0003','运动T恤','衣服',4000,2800,NULL),('0004','菜刀','厨房用具',3000,2800,'2009-09-20'),('0005','高压锅','厨房用具',6800,5000,'2009-01-15'),('0006','叉子','厨房用具',500,NULL,'2009-09-20'),('0007','擦菜板','厨房用具',880,790,'2008-04-28'),('0008','圆珠笔','办公用品',100,NULL,'2009-11-11');
 
/*Table structure for table `product2` */
 
DROP TABLE IF EXISTS `product2`;
 
CREATE TABLE `product2` (
  `product_id` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `product_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `product_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `sale_price` int DEFAULT NULL,
  `purchase_price` int DEFAULT NULL,
  `regist_date` date DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 
/*Data for the table `product2` */
 
insert  into `product2`(`product_id`,`product_name`,`product_type`,`sale_price`,`purchase_price`,`regist_date`) values ('0001','T恤','衣服',1000,500,'2009-09-20'),('0002','打孔器','办公用品',500,320,'2009-09-11'),('0003','运动T恤','衣服',4000,2800,NULL),('0009','手套','衣服',800,500,NULL),('0010','水壶','厨房用具',2000,1700,'2009-09-20');
 
/*Table structure for table `productcopy` */
 
DROP TABLE IF EXISTS `productcopy`;
 
CREATE TABLE `productcopy` (
  `product_id` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `product_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `product_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `sale_price` int DEFAULT NULL,
  `purchase_price` int DEFAULT NULL,
  `regist_date` date DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
/*Data for the table `productcopy` */
 
/*Table structure for table `productins` */
 
DROP TABLE IF EXISTS `productins`;
 
CREATE TABLE `productins` (
  `product_id` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `product_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `product_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `sale_price` int DEFAULT '0',
  `purchase_date` int DEFAULT NULL,
  `regist_date` date DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
/*Data for the table `productins` */
 
/*Table structure for table `producttype` */
 
DROP TABLE IF EXISTS `producttype`;
 
CREATE TABLE `producttype` (
  `product_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `sum_sale_price` int DEFAULT NULL,
  `sum_purchase_price` int DEFAULT NULL,
  PRIMARY KEY (`product_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
/*Data for the table `producttype` */
 
/*Table structure for table `samplelike` */
 
DROP TABLE IF EXISTS `samplelike`;
 
CREATE TABLE `samplelike` (
  `strcol` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`strcol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
/*Data for the table `samplelike` */
 
insert  into `samplelike`(`strcol`) values ('abcdd'),('abcddd'),('abddc'),('abdddc'),('ddabc'),('dddabc');
 
/*Table structure for table `samplemath` */
 
DROP TABLE IF EXISTS `samplemath`;
 
CREATE TABLE `samplemath` (
  `m` decimal(10,3) DEFAULT NULL,
  `n` int DEFAULT NULL,
  `p` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
/*Data for the table `samplemath` */
 
insert  into `samplemath`(`m`,`n`,`p`) values ('500.000',0,NULL),('-180.000',0,NULL),(NULL,NULL,NULL),(NULL,7,3),(NULL,5,2),(NULL,4,NULL),('8.000',NULL,3),('2.270',1,NULL),('5.555',2,NULL),(NULL,1,NULL),('8.760',NULL,NULL);
 
/*Table structure for table `samplestr` */
 
DROP TABLE IF EXISTS `samplestr`;
 
CREATE TABLE `samplestr` (
  `str1` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `str2` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `str3` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
/*Data for the table `samplestr` */
 
insert  into `samplestr`(`str1`,`str2`,`str3`) values ('opx','rt',NULL),('abc','def',NULL),('山田','太郎','是我'),('aaa',NULL,NULL),(NULL,'xyz',NULL),('@!#$%',NULL,NULL),('ABC',NULL,NULL),('aBC',NULL,NULL),('abc太郎','abc','ABC'),('abcdefabc','abc','ABC'),('micmic','i','I');
 
/*Table structure for table `shopproduct` */
 
DROP TABLE IF EXISTS `shopproduct`;
 
CREATE TABLE `shopproduct` (
  `shop_id` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `shop_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `product_id` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `quantity` int NOT NULL,
  PRIMARY KEY (`shop_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 
/*Data for the table `shopproduct` */
 
insert  into `shopproduct`(`shop_id`,`shop_name`,`product_id`,`quantity`) values ('000A','东京','0001',30),('000A','东京','0002',50),('000A','东京','0003',15),('000B','名古屋','0002',30),('000B','名古屋','0003',120),('000B','名古屋','0004',20),('000B','名古屋','0006',10),('000B','名古屋','0007',40),('000C','大阪','0003',20),('000C','大阪','0004',50),('000C','大阪','0006',90),('000C','大阪','0007',70),('000D','福冈','0001',100);
 
/*Table structure for table `skills` */
 
DROP TABLE IF EXISTS `skills`;
 
CREATE TABLE `skills` (
  `skill` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`skill`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
/*Data for the table `skills` */
 
insert  into `skills`(`skill`) values ('Java'),('Oracle'),('UNIX');
本文地址：http://9zoku.com/database/279.html
版权声明：本文内容来自互联网，该文版权归原作者所有。本站仅提供信息存储空间服务，不拥有文章所有权，不承担相关法律责任。若对本内容有异议或投诉请与管理员联系 5604882#qq.com(请将#换成@)。

相关内容