MySQL 查詢並集、交集、差集方式

2023-02-08 22:02:54

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 <表名>

注意事項

1.首先：任何執行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;

結果如下：

2.Union vs Union All 的區別

Union 操作，自動去重複，即兩個或多個資料表中相同的行只吃出現一次；

若想要所有表中的對應的資料都顯示的話，則需要用到Union all

3.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'),('神崎','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');