[Mysql] 集合操作

原创已于 2022-06-05 12:02:39 修改 · 766 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #sql

于 2022-05-31 21:35:28 首次发布

Mysql 专栏收录该内容

64 篇文章

订阅专栏

本文通过一系列SQL查询示例，详细讲解了如何进行表的交集、并集、差集操作，以及如何查找不在另一表中的数据。通过创建和操作两个示例表`table_aid`和`table_bid`，展示了如何使用INNER JOIN、UNION和LEFT/RIGHT JOIN等方法来实现这些操作。此外，还给出了一个实际的业务场景，即在顾客和订单表中找出从未下单的客户，进一步巩固了这些概念。

数据导入

DROP TABLE IF EXISTS `table_aid`;
CREATE TABLE `table_aid` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `table_aid` VALUES ('1');
INSERT INTO `table_aid` VALUES ('2');
INSERT INTO `table_aid` VALUES ('3');
INSERT INTO `table_aid` VALUES ('4');

DROP TABLE IF EXISTS `table_bid`;
CREATE TABLE `table_bid` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `table_bid` VALUES ('2');
INSERT INTO `table_bid` VALUES ('3');
INSERT INTO `table_bid` VALUES ('5');
INSERT INTO `table_bid` VALUES ('6');

table_aid表

table_bid表

上述表a和表b都只有一个字段，即相同字段名id

问题1:求表a与表b的交集

SELECT a.id
FROM table_aid AS a
INNER JOIN table_bid AS b
ON a.id = b.id;

结果展示:

问题2:求表a与表b的并集

SELECT *
FROM table_aid
UNION 
SELECT *
FROM table_bid;

结果展示:

问题3:存在表a但不存在表b的元素

SELECT a.id
FROM table_aid AS a
LEFT JOIN table_bid AS b
ON a.id = b.id
WHERE b.id IS NULL;

结果展示:

问题3:存在表b但不存在表a的元素

SELECT b.id
FROM table_aid AS a
RIGHT JOIN table_bid AS b
ON a.id = b.id
WHERE a.id IS NULL;

结果展示:

问题4:存在表a但不存在表b的元素，同时存在表b但不同时存在表a的元素

-- 解法1
SELECT a.id
FROM table_aid AS a
LEFT JOIN table_bid AS b
ON a.id = b.id
WHERE b.id IS NULL
UNION
SELECT b.id
FROM table_aid AS a
RIGHT JOIN table_bid AS b
ON a.id = b.id
WHERE a.id IS NULL;

-- 解法2
SELECT *
FROM table_aid
WHERE id NOT IN(SELECT id FROM table_bid)
UNION
SELECT *
FROM table_bid
WHERE id NOT IN(SELECT id FROM table_aid);

结果展示:

知识扩展:查找不在表里的数据

遇到要查找“不在表里的数据，也就是在表A里的数据，但是不在表B里的数据”，可以使用如下SQL语句:

SELECT...

FROM 表1 AS a

LEFT JOIN 表2 AS b

ON a.列名 = b.列名

WHERE b.列名 IS NULL;

SELECT...

FROM 表1 AS a

RIGHT JOIN 表2 AS b

ON a.列名 = b.列名

WHERE a.列名 IS NULL;

案例练习

数据导入

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `customers` VALUES (1, 'Joe');
INSERT INTO `customers` VALUES (2, 'Henry');
INSERT INTO `customers` VALUES (3, 'Sam');
INSERT INTO `customers` VALUES (4, 'Max');

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `id` int(11) NOT NULL,
  `customerid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `orders` VALUES (1, 3);
INSERT INTO `orders` VALUES (2, 1);

某网站包含两个表，顾客姓名表(表名Customers)和购买记录表(表名Orders)

“顾客姓名表”中的ID与“购买记录”表中的学生学号CustomerId一一对应

customers表(顾客姓名表)

orders表(购买记录表)

问题:找出所有从不订购任何东西的客户

SELECT a.name AS Customers
FROM customers AS a
LEFT JOIN orders AS b
ON a.id = b.customerid
WHERE b.customerid IS null;

结果展示: