MySQL?查詢?并集、交集、差集方式
更新時間：2023年02月07日 10:03:01 作者：AI_SupplyChain
這篇文章主要介紹了MySQL?查詢?并集、交集、差集方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
MySQL查詢交集、并集、差集
背景和使用的數(shù)據(jù)樣本
該章節(jié)學些主要涉及到Datawhale SQL 組隊學習任務；
本次使用的數(shù)據(jù)，由Datawhale 開源提供，具體代碼見文章尾部：各位看官可以直接復制到MySQL Workbench 上運行生成數(shù)據(jù)庫和表格。
MySQL WorkBench 如何對數(shù)據(jù)進行我們常見的集合運算呢？
Union、 INTERSECT（遺憾的是MySQL 8.0 不支持該運算用where /in 實現(xiàn)）、EXCEPT（遺憾的是MySQL 8.0 不支持該運算，但巧妙的用where …Not in 實現(xiàn)），本博客日記主要記錄一些注意事項。
那么MySQL如何處理交集和差集呢？
基本語法
Select <列名1>,<列名2>,<列名3>... from <表名> union -- 或者其他 intersect、except、union all等） Select <列名1>,<列名2>,<列名3>... from <表名>
注意事項
1.首先：任何執(zhí)行Union、 INTERSECT、EXCEPT的語句
都要注意，該關鍵字前后的Select 語句中選擇的列的數(shù)量要一致，不一致會提示錯誤：
select product_id, product_name -- 注意這里是2選擇查詢兩個列 from product union select product_id, product_name -- 這里的列的數(shù)量要與前面的列的數(shù)量一致 from product2;
正確的代碼輸出結果如下：
其次，select 語句中列數(shù)量不一致時，提示：
Error Code: 1222. The used SELECT statements have a different number of columns 0.000 sec
但是，對于select 中的選擇的可以不一定是表格中原來就有的列表，此時能夠正常查詢數(shù)據(jù)表，具體如下：
select product_id, product_name from product union select product_id, '1' from product2;
結果如下：
2.Union vs Union All 的區(qū)別
Union 操作，自動去重復，即兩個或多個數(shù)據(jù)表中相同的行只吃出現(xiàn)一次；
若想要所有表中的對應的數(shù)據(jù)都顯示的話，則需要用到Union all
3.select sysdate() ; 查詢返回系統(tǒng)當前的時間；
select product_name, sale_price, purchase_price from product union select sysdate(), sysdate() , sysdate() from product;
查詢結果如下：
**4.MySQL 8.0 不支持交運算INTERSECT、except **
5. 查詢差集：
-- 使用 IN 子句的實現(xiàn)方法
SELECT *
FROM product
WHERE product_id NOT IN (SELECT product_id
FROM product2)
結果：
/* v 9.08
SQL腳本的一些要點:
0.存儲引擎使用 InnoDB, 字符集改為 utf8mb4 以更好地支持中文.
1.所有表名所使用的英文字母都改為小寫(后續(xù)章節(jié)中,SQL查詢中的表名也需要相應修改為小寫)
2.所有列名所使用的英文字母確認為小寫(后續(xù)章節(jié)中,SQL查詢中的列名也需要相應修改為小寫)
3.存在問題的數(shù)據(jù), 例如 inventoryproduct 表的inventory_id列應為P開頭的, 已修正為正確的數(shù)據(jù).
4.需測試SQL腳本在命令行及各個客戶端中是否能被正確執(zhí)行.
* MySQL Workbench 已測試通過
* DBeaver 已測試通過(使用"執(zhí)行SQL腳本(CTR+x)")
* HeidiSQL 已測試通過
* navicat 已測試通過
* sqlyog 已測試通過
* 命令行 測試未通過. 插入中文數(shù)據(jù)時提示" 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');
總結
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
這篇文章主要介紹了MySQL官方壓力測試工具 Mysqlslap的使用方法，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2020-12-12
對于大量數(shù)據(jù)的導入導出，是件挺麻煩的事，需要考慮很多的細節(jié)，這類對于需要大量數(shù)據(jù)導入導出的朋友可以參考下。2010-12-12
本文主要介紹了一文詳解MySQL存儲引擎,常用兩種存儲引擎是MyISAM和InnoDB,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-05-05
這篇文章主要介紹了一個關于MySQL指定編碼實現(xiàn)的小坑,文中大家需要注意如果有需要保存emoji符號的字段，記得一定要指定編碼為 utf8mb4，感興趣的朋友一起看看吧2021-10-10
這篇文章主要為大家詳細介紹了Centos7安裝mysql5.6.29的shell腳本，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-03-03
這篇文章主要為大家詳細介紹了CentOS 6.4下編譯安裝MySQL 5.6.14教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-01-01
這篇文章主要為大家詳細介紹了mac下mysql5.7.20 安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-11-11
一個項目中如果將所有的數(shù)據(jù)都存放在一張表中是不合理的，比如一個員工信息，公司只有2個部門，但是員工有1億人，就意味著員工信息這張表中的部門字段的值需要重復存儲，極大的浪費資源，因此可以定義一個部門表和員工信息表進行關聯(lián)，而關聯(lián)的方式就是外鍵。2021-06-06
MySQL中json_extract函數(shù)說明及使用方式
今天看mysql中的json數(shù)據(jù)類型,涉及到一些使用,使用到了函數(shù)json_extract來,下面這篇文章主要給大家介紹了關于MySQL中json_extract函數(shù)說明及使用方式的相關資料,需要的朋友可以參考下2022-08-08
mysql時間戳格式化函數(shù)from_unixtime使用的簡單說明
mysql中的FROM_UNIXTIME函數(shù)可以數(shù)據(jù)庫中整型類的時間戳格式化為字符串的日期時間格式,下面這篇文章主要給大家介紹了關于mysql時間戳格式化函數(shù)from_unixtime使用的簡單說明,需要的朋友可以參考下2022-08-08