SQL集合与链接草稿

一、集合

1、UNION 存在的价值；

2、通过上述 bag 与 set 定义之间的差别我们就发现, 使用 bag 模型来描述数据库中的表在很多时候更加合适：用UNION 实现并、用 IN 实现交、用NOT IN实现差（补集）；

3、用视图实现：

 

二、连接

1、

4.2.1.3结合 GROUP BY 子句使用内连结

结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待.

最简单的情形, 是在内连结之前就使用 GROUP BY 子句.

但是如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合.

 

使用自然连结还可以求出两张表或子查询的公共部分, 例如教材中 7-1 选取表中公共部分–INTERSECT 一节中的问题: 求表 product 和表 product2 中的公共部分, 也可以用自然连结来实现:

SELECT * FROM product NATURAL JOIN product2
2、

这个结果和书上给的结果并不一致, 少了运动 T 恤, 这是由于运动 T 恤的 regist_date 字段为空, 在进行自然连结时, 来自于 product 和 product2 的运动 T 恤这一行数据在进行比较时, 实际上是在逐字段进行等值连结, 回忆我们在 6.2ISNULL,IS NOT NULL 这一节学到的缺失值的比较方法就可得知, 两个缺失值用等号进行比较, 结果不为真. 而连结只会返回对连结条件返回为真的那些行.

3、

INNER JOIN 也可以实现交集运算

4、

在实际的业务中,例如想要生成固定行数的单据时,就需要使用外连结.如果使用内连结的话,根据 SELECT 语句执行时商店库存状况的不同,结果的行数也会发生改变,生成的单据的版式也会受到影响,而使用外连结能够得到固定行数的结果

5、

（需指定主表）外连结名称的由来也跟 NULL 有关,即“结果中包含原表中不存在（在原表之外）的信息”.相反,只包含表内信息的连结也就被称为内连结了

6、

（问题在于，让能容纳表外内容的OUTER JOIN(因为主表含高压锅和圆珠笔) 在无法容纳NULL 的WHERE子句之后执行）少了在所有商店都无货的高压锅和圆珠笔. 聪明的你可能很容易想到,在WHERE子句中增加 quantity IS NOT NULL 的条件, 然而在真实的查询环境中, 由于数据量大且数据质量并非如系统说明和我们设想的那样"干净", 我们并不能很容易地意识到缺失值等问题数据的存在, 因此,还是让我们想一下如何改写我们的查询以使得它能够适应更复杂的真实数据的情形吧

/*CREATE table `product2` */
CREATE TABLE product2 (
  `product_id` char(4) NOT NULL,
  `product_name` varchar(100) NOT NULL,
  `product_type` varchar(32) NOT NULL,
  `sale_price` int DEFAULT NULL,
  `purchase_price` int DEFAULT NULL,
  `regist_date` date DEFAULT NULL,
  PRIMARY KEY (`product_id`)
);
 
/*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');

SELECT * FROM product2;

-- CREATE TABLE shopproduct
CREATE TABLE shopproduct (
  `shop_id` char(4)  NOT NULL,
  `shop_name` varchar(200)  NOT NULL,
  `product_id` char(4)  NOT NULL,
  `quantity` int NOT NULL,
  PRIMARY KEY (`shop_id`,`product_id`));

/*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);

SELECT * FROM shopproduct;


/*Note for 004*/

SELECT product_id, product_name
  FROM product
 UNION 
SELECT product_id, product_name
  FROM product2;
	
-- 练习题:
-- 假设连锁店想要增加毛利率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集.
SELECT *
FROM product
WHERE sale_price <800
UNION
SELECT *
FROM product
WHERE sale_price > 1.5* purchase_price;

-- 练习题:
-- 找出 product 和 product2 中售价高于 500 的商品的基本信息.
SELECT *
FROM product
WHERE sale_price > 500
UNION
SELECT *
FROM product2
WHERE sale_price > 500;

-- 保留重复行，0002，0003是重复行
SELECT product_id, product_name
  FROM product
 UNION ALL
SELECT product_id, product_name
  FROM product2;
	
-- 练习题:
-- 商店决定对product表中利润低于50%和售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集. 
SELECT *
FROM product
WHERE sale_price / purchase_price >1.5
UNION ALL
SELECT *
FROM product
WHERE sale_price < 1000;

-- 练习题:
-- 使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性.
-- 用IN：
SELECT *
FROM product
WHERE product_id NOT IN (SELECT product_id
										FROM product2);
										
-- 用exists:
SELECT *
FROM product
WHERE NOT EXISTS (SELECT *
									FROM product2
									WHERE product2.product_id = product.product_id);

-- 练习题:
-- 使用NOT谓词进行集合的减法运算, 求出product表中, 售价高于2000,但利润低于30%的商品, 结果应该如下表所示.
SELECT *
FROM product
WHERE sale_price > 2000 
AND product_id NOT IN (SELECT product_id
									FROM product
									WHERE sale_price/purchase_price >1.3);
						
-- 练习题:
-- 使用product表和product2表的对称差来查询哪些商品只在其中一张表, 结果类似于:
-- 1.用“并”-“交”
CREATE VIEW union_product(product_id, product_name,product_type,sale_price,purchase_price,regist_date)
AS
SELECT *
  FROM product
 UNION 
SELECT *
  FROM product2;
	
CREATE VIEW Intersect_product(product_id, product_name,product_type,sale_price,purchase_price,regist_date)
AS
SELECT *
  FROM product
WHERE product_id IN (SELECT product_id
										FROM product2);
										
SELECT * 
FROM union_product
WHERE product_id NOT IN (SELECT product_id
										FROM Intersect_product);
-- 2.用A-B并B-A
CREATE VIEW Left_product(product_id, product_name,product_type,sale_price,purchase_price,regist_date)
AS
SELECT * 
  FROM product
 WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION
SELECT * 
  FROM product2
 WHERE product_id NOT IN (SELECT product_id FROM product);
 
--  练习题:
-- 借助上述对称差的实现方式, 求product和product2的交.
SELECT *
FROM union_product
WHERE product_id NOT IN  (SELECT product_id
										FROM left_product);
										
-- 练习题:
-- 找出每个商店里的衣服类商品的名称及价格等信息. 希望得到如下结果:
SELECT s.shop_id, s.shop_name, p.product_id, p.product_name, p.product_type, p.purchase_price
FROM product AS p INNER JOIN shopproduct AS s
ON s.product_id = p.product_id
WHERE p.product_type = '衣服';

-- 练习题:
-- 分别使用连结两个子查询和不使用子查询的方式, 找出东京商店里, 售价低于 2000 的商品信息,希望得到如下结果.
-- 方法1：使用两个子查询
SELECT s.*, p.*
FROM (SELECT *
		FROM product 
		WHERE sale_price <2000) AS p

INNER JOIN

 (SELECT *
		FROM shopproduct 
		WHERE shop_name = '东京') AS s
		
ON p.product_id = s.product_id;

-- 方法2：不使用子查询
SELECT s.*, p.*
FROM product AS p 
INNER JOIN shopproduct AS s
ON s.product_id = p.product_id
WHERE p.sale_price < 2000 AND s.shop_name = '东京';

-- 练习题:
-- 每个商店中, 售价最高的商品的售价分别是多少?
SELECT s.*, p.*,MAX(p.sale_price) AS max_price
FROM product AS p 
INNER JOIN shopproduct AS s
ON s.product_id = p.product_id
GROUP BY s.shop_id;

-- 练习题:
-- 分别使用内连结和关联子查询每一类商品中售价最高的商品.
-- 方法一：直接分组（关联子查询不会)
SELECT *, max(sale_price) AS max_price
FROM product
GROUP BY product_type;
-- 方法二：内连接
SELECT  P1.product_id
       ,P1.product_name
       ,P1.product_type
       ,P1.sale_price
       ,P2.max_price
  FROM product AS P1
	
 INNER JOIN 
   (SELECT *,max(sale_price) AS max_price 
      FROM product 
     GROUP BY product_type) AS P2 
ON P1.sale_price = P2.max_price 
WHERE P1.product_type =P2.product_type;

-- 练习题:
-- 试写出与上述自然连结等价的内连结.
SELECT p.*,s.*
FROM product AS p
INNER JOIN
shopproduct AS s
ON p.product_id = s.product_id;

-- 练习题: 统计每种商品分别在哪些商店有售, 需要包括那些在每个商店都没货的商品.
-- 使用左连结的代码如下(注意区别于书上的右连结):

SELECT s.*,p.*
FROM product AS p
RIGHT OUTER JOIN shopproduct AS s
ON s.product_id = p.product_id;

-- 练习题:
-- 使用外连结从shopproduct表和product表中找出那些在某个商店库存少于50的商品及对应的商店.希望得到如下结果.

SELECT p.*,s.*
FROM product AS p LEFT JOIN (SELECT *
      FROM shopproduct
     WHERE quantity < 50 ) AS s
ON s.product_id = p.product_id;

-- 创建Inventoryproduct表
CREATE TABLE Inventoryproduct
( inventory_id       CHAR(4) NOT NULL,
product_id         CHAR(4) NOT NULL,
inventory_quantity INTEGER NOT NULL,
PRIMARY KEY (inventory_id, product_id));

START TRANSACTION;
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0001', 0);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0002', 120);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0003', 200);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0004', 3);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0005', 0);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0006', 99);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0007', 999);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0008', 200);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0001', 10);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0002', 25);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0003', 34);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0004', 19);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0005', 99);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0006', 0);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0007', 0 );
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0008', 18);
COMMIT;

SELECT s.shop_id, s.shop_name,s.product_id,p.sale_price,i.inventory_quantity 
FROM product AS p
INNER JOIN shopproduct AS s
ON p.product_id =s.product_id
INNER JOIN Inventoryproduct AS i
ON s.product_id = i.product_id
WHERE i.inventory_id = 'P001';



SELECT P.product_id
       ,P.product_name
       ,P.sale_price
       ,SP.shop_id
       ,SP.shop_name
       ,IP.*
  FROM product AS P
  LEFT OUTER JOIN shopproduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id;


 SELECT P1.product_id
               ,P1.product_name
               ,P1.sale_price
               ,P2.product_id AS P2_id
               ,P2.product_name AS P2_name
               ,P2.sale_price AS P2_price 
          FROM product AS P1 
          LEFT OUTER JOIN product AS P2 
            ON P1.sale_price <= P2.sale_price;
/*0001	T恤衫	1000	0001	T恤衫	1000
0002	打孔器	500	0001	T恤衫	1000
0006	叉子	500	0001	T恤衫	1000
0007	擦菜板	880	0001	T恤衫	1000
0008	圆珠笔	100	0001	T恤衫	1000
0002	打孔器	500	0002	打孔器	500
0006	叉子	500	0002	打孔器	500
0008	圆珠笔	100	0002	打孔器	500
0001	T恤衫	1000	0003	运动T恤	4000
0002	打孔器	500	0003	运动T恤	4000
0003	运动T恤	4000	0003	运动T恤	4000
0004	菜刀	3000	0003	运动T恤	4000
0006	叉子	500	0003	运动T恤	4000
0007	擦菜板	880	0003	运动T恤	4000
0008	圆珠笔	100	0003	运动T恤	4000
0001	T恤衫	1000	0004	菜刀	3000
0002	打孔器	500	0004	菜刀	3000
0004	菜刀	3000	0004	菜刀	3000
0006	叉子	500	0004	菜刀	3000
0007	擦菜板	880	0004	菜刀	3000
0008	圆珠笔	100	0004	菜刀	3000
0001	T恤衫	1000	0005	高压锅	6800
0002	打孔器	500	0005	高压锅	6800
0003	运动T恤	4000	0005	高压锅	6800
0004	菜刀	3000	0005	高压锅	6800
0005	高压锅	6800	0005	高压锅	6800
0006	叉子	500	0005	高压锅	6800
0007	擦菜板	880	0005	高压锅	6800
0008	圆珠笔	100	0005	高压锅	6800
0002	打孔器	500	0006	叉子	500
0006	叉子	500	0006	叉子	500
0008	圆珠笔	100	0006	叉子	500
0002	打孔器	500	0007	擦菜板	880
0006	叉子	500	0007	擦菜板	880
0007	擦菜板	880	0007	擦菜板	880
0008	圆珠笔	100	0007	擦菜板	880
0008	圆珠笔	100	0008	圆珠笔	100*/

SELECT *
-- 使得尽管使用了GROUP BY，但还能利用构造出的上表信息：p2_id
       ,COUNT(p2_id)+1 AS rank
  FROM (
        SELECT P1.product_id
               ,P1.product_name
               ,P1.sale_price
               ,P2.product_id AS P2_id
               ,P2.product_name AS P2_name
               ,P2.sale_price AS P2_price 
          FROM product AS P1 
          LEFT OUTER JOIN product AS P2 
            ON P1.sale_price < P2.sale_price 
        ) AS X
 GROUP BY product_id
 ORDER BY rank;