MySQL?查詢?并集、交集、差集方式
更新時(shí)間：2023年02月07日 10:03:01 作者：AI_SupplyChain
這篇文章主要介紹了MySQL?查詢?并集、交集、差集方式，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教
MySQL查詢交集、并集、差集
背景和使用的數(shù)據(jù)樣本
該章節(jié)學(xué)些主要涉及到Datawhale SQL 組隊(duì)學(xué)習(xí)任務(wù)；
本次使用的數(shù)據(jù)，由Datawhale 開源提供，具體代碼見文章尾部：各位看官可以直接復(fù)制到MySQL Workbench 上運(yùn)行生成數(shù)據(jù)庫和表格。
MySQL WorkBench 如何對數(shù)據(jù)進(jì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如何處理交集和差集呢？
基本語法
Select <列名1>,<列名2>,<列名3>... from <表名> union -- 或者其他 intersect、except、union all等） Select <列名1>,<列名2>,<列名3>... from <表名>
注意事項(xiàng)
1.首先：任何執(zhí)行Union、 INTERSECT、EXCEPT的語句
都要注意，該關(guān)鍵字前后的Select 語句中選擇的列的數(shù)量要一致，不一致會提示錯(cuò)誤：
select product_id, product_name -- 注意這里是2選擇查詢兩個(gè)列 from product union select product_id, product_name -- 這里的列的數(shù)量要與前面的列的數(shù)量一致 from product2;
正確的代碼輸出結(jié)果如下：
其次，select 語句中列數(shù)量不一致時(shí)，提示：
Error Code: 1222. The used SELECT statements have a different number of columns 0.000 sec
但是，對于select 中的選擇的可以不一定是表格中原來就有的列表，此時(shí)能夠正常查詢數(shù)據(jù)表，具體如下：
select product_id, product_name from product union select product_id, '1' from product2;
結(jié)果如下：
2.Union vs Union All 的區(qū)別
Union 操作，自動(dòng)去重復(fù)，即兩個(gè)或多個(gè)數(shù)據(jù)表中相同的行只吃出現(xiàn)一次；
若想要所有表中的對應(yīng)的數(shù)據(jù)都顯示的話，則需要用到Union all
3.select sysdate() ; 查詢返回系統(tǒng)當(dāng)前的時(shí)間；
select product_name, sale_price, purchase_price from product union select sysdate(), sysdate() , sysdate() from product;
查詢結(jié)果如下：
**4.MySQL 8.0 不支持交運(yùn)算INTERSECT、except **
5. 查詢差集：
-- 使用 IN 子句的實(shí)現(xiàn)方法
SELECT *
FROM product
WHERE product_id NOT IN (SELECT product_id
FROM product2)
結(jié)果：
/* v 9.08
SQL腳本的一些要點(diǎn):
0.存儲引擎使用 InnoDB, 字符集改為 utf8mb4 以更好地支持中文.
1.所有表名所使用的英文字母都改為小寫(后續(xù)章節(jié)中,SQL查詢中的表名也需要相應(yīng)修改為小寫)
2.所有列名所使用的英文字母確認(rèn)為小寫(后續(xù)章節(jié)中,SQL查詢中的列名也需要相應(yīng)修改為小寫)
3.存在問題的數(shù)據(jù), 例如 inventoryproduct 表的inventory_id列應(yīng)為P開頭的, 已修正為正確的數(shù)據(jù).
4.需測試SQL腳本在命令行及各個(gè)客戶端中是否能被正確執(zhí)行.
* MySQL Workbench 已測試通過
* DBeaver 已測試通過(使用"執(zhí)行SQL腳本(CTR+x)")
* HeidiSQL 已測試通過
* navicat 已測試通過
* sqlyog 已測試通過
* 命令行 測試未通過. 插入中文數(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'),('渡來','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è)參考，也希望大家多多支持腳本之家。
相關(guān)文章
MySQL刪除數(shù)據(jù)Delete與Truncate語句使用比較
在MySQL數(shù)據(jù)庫中，DELETE語句和TRUNCATE TABLE語句都可以用來刪除數(shù)據(jù)，但是這兩種語句還是有著其區(qū)別的，下文就為您介紹這二者的差別所在2012-09-09
這篇文章主要為大家詳細(xì)介紹了MySQL為例講解JDBC數(shù)據(jù)庫連接步驟，感興趣的小伙伴們可以參考一下2016-08-08
這篇文章主要給大家介紹了關(guān)于Mysql升級到5.7后遇到的group by查詢問題的解決方法，文中通過示例代碼介紹的非常詳細(xì)，對同樣遇到這個(gè)問題的朋友們具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧。2017-09-09
MySQL中l(wèi)ength()、char_length()的區(qū)別
在MySQL中l(wèi)ength(str)、char_length(str)都屬于判斷長度的內(nèi)置函數(shù)，本文主要介紹了MySQL中l(wèi)ength()、char_length()的區(qū)別，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05
今天小編就為大家分享一篇關(guān)于Mysql Update批量更新的幾種方式，小編覺得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來看看吧2019-02-02
批量替換 MySQL 指定字段中的字符串是數(shù)據(jù)庫應(yīng)用中很常見的需求，但是有很多初學(xué)者在遇到這種需求時(shí)，通常都是用腳本來實(shí)現(xiàn)；其實(shí)，MySQL 內(nèi)置的有批量替換語法，效率也會高很多；想了解具體方法，繼續(xù)閱讀本文吧 :)2009-09-09
這篇文章主要介紹了mysql的啟動(dòng)過程詳解,本文用生動(dòng)風(fēng)趣的文風(fēng)講解了mysql的啟動(dòng)過程,絕對讓你難忘哦,需要的朋友可以參考下2014-08-08
這篇文章主要介紹了Mysql觸發(fā)器字段雙向更新方式,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-08-08
mysql啟動(dòng)的error 2003和1067錯(cuò)誤問題解決方法
mysql安裝好經(jīng)常發(fā)現(xiàn)無法正常啟動(dòng)碰到最多的是error 2003的錯(cuò)誤，經(jīng)研究在此附上解決方法，希望可以幫助有類似問題的朋友2013-09-09
Win10系統(tǒng)下MySQL8.0.16 壓縮版下載與安裝教程圖解
這篇文章主要介紹了Win10系統(tǒng)下MySQL8.0.16 壓縮版下載與安裝教程圖解,本文圖文并茂給大家介紹的非常詳細(xì)，具有一定的參考解決價(jià)值，需要的朋友可以參考下2019-06-06