该章节学些主要涉及到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");
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。