目錄

• MySQL查詢(xún)交集、并集、差集
• 背景和使用的數(shù)據(jù)樣本
• 基本語(yǔ)法
• 注意事項(xiàng)
• 總結(jié)

MySQL查詢(xún)交集、并集、差集

背景和使用的數(shù)據(jù)樣本

該章節(jié)學(xué)些主要涉及到Datawhale SQL 組隊(duì)學(xué)習(xí)任務(wù)；

本次使用的數(shù)據(jù)，由Datawhale 開(kāi)源提供，具體代碼見(jiàn)文章尾部：各位看官可以直接復(fù)制到MySQL Workbench 上運(yùn)行生成數(shù)據(jù)庫(kù)和表格。

MySQL WorkBench 如何對(duì)數(shù)據(jù)進(jìn)行我們常見(jiàn)的集合運(yùn)算呢？

Union、 INTERSECT（遺憾的是MySQL 8.0 不支持該運(yùn)算用where /in 實(shí)現(xiàn)）、EXCEPT（遺憾的是MySQL 8.0 不支持該運(yùn)算，但巧妙的用where …Not in 實(shí)現(xiàn)），本博客日記主要記錄一些注意事項(xiàng)。

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

基本語(yǔ)法

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

注意事項(xiàng)

1.首先：任何執(zhí)行Union、 INTERSECT、EXCEPT的語(yǔ)句

都要注意，該關(guān)鍵字前后的Select 語(yǔ)句中選擇的列的數(shù)量要一致，不一致會(huì)提示錯(cuò)誤：

select product_id, product_name   -- 注意這里是2選擇查詢(xún)兩個(gè)列from productunion select product_id, product_name  -- 這里的列的數(shù)量要與前面的列的數(shù)量一致from product2;

正確的代碼輸出結(jié)果如下：

其次，select 語(yǔ)句中列數(shù)量不一致時(shí)，提示：

Error Code: 1222. The used SELECT statements have a different number of columns 0.000 sec

但是，對(duì)于select 中的選擇的可以不一定是表格中原來(lái)就有的列表，此時(shí)能夠正常查詢(xún)數(shù)據(jù)表，具體如下：

select product_id, product_namefrom productunion select product_id, "1"from product2;

結(jié)果如下：

2.Union vs Union All 的區(qū)別

Union 操作，自動(dòng)去重復(fù)，即兩個(gè)或多個(gè)數(shù)據(jù)表中相同的行只吃出現(xiàn)一次；

若想要所有表中的對(duì)應(yīng)的數(shù)據(jù)都顯示的話，則需要用到Union all

3.select sysdate() ; 查詢(xún)返回系統(tǒng)當(dāng)前的時(shí)間；

select product_name, sale_price, purchase_pricefrom productunion select sysdate(), sysdate() , sysdate()from product;

查詢(xún)結(jié)果如下：

**4.MySQL 8.0 不支持交運(yùn)算INTERSECT、except **

5. 查詢(xún)差集：

-- 使用 IN 子句的實(shí)現(xiàn)方法SELECT *   FROM product WHERE product_id NOT IN (SELECT product_id     FROM product2)

結(jié)果：

/* v 9.08SQL腳本的一些要點(diǎn):0.存儲(chǔ)引擎使用 InnoDB, 字符集改為 utf8mb4 以更好地支持中文.1.所有表名所使用的英文字母都改為小寫(xiě)(后續(xù)章節(jié)中,SQL查詢(xún)中的表名也需要相應(yīng)修改為小寫(xiě))2.所有列名所使用的英文字母確認(rèn)為小寫(xiě)(后續(xù)章節(jié)中,SQL查詢(xún)中的列名也需要相應(yīng)修改為小寫(xiě))3.存在問(wèn)題的數(shù)據(jù), 例如 inventoryproduct 表的inventory_id列應(yīng)為P開(kāi)頭的, 已修正為正確的數(shù)據(jù).4.需測(cè)試SQL腳本在命令行及各個(gè)客戶(hù)端中是否能被正確執(zhí)行.  * MySQL Workbench 已測(cè)試通過(guò)  * DBeaver 已測(cè)試通過(guò)(使用"執(zhí)行SQL腳本(CTR+x)")  * HeidiSQL 已測(cè)試通過(guò)  * navicat 已測(cè)試通過(guò)  * sqlyog 已測(cè)試通過(guò)  * 命令行 測(cè)試未通過(guò). 插入中文數(shù)據(jù)時(shí)提示" 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"),("渡來(lái)","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","運(yùn)動(dòng)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","運(yùn)動(dòng)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");

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持。