一、表的加减法
1.集合运算
集合,记录的集合,表、视图和查询的结果都是记录的集合。
集合运算,是对满足同一规则的记录进行的四则运算,从而得到两张表中记录的集合或公开记录的集合,或其中某张表中的记录的集合。
集合运算符,用来进行集合运算的运算符。
集合运算,是以行方向为单位进行操作的。
2.表的加法UNION
UNION,并集,加法运算。
-- 创建表Product2
CREATE TABLE Product2(
product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id)
);
-- 插入数据
START TRANSACTION;
INSERT INTO Product2 VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, '2008-09-20');
INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);
INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');
COMMIT;
-- 确认表内容
SELECT * FROM Product2;
-- 使用UNION对表进行加法运算
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;
从运算结果来看,UNION并集运算剔除了重复的记录。
集合运算注意事项:
1.作为运算对象的记录的列数必须相同
-- 列数不一致时会发生错误
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name, sale_price
FROM Product2;
2.作为运算对象的记录中列的类型必须一致
从左侧开始,相同位置上的列必须是同一数据类型。
-- 数据类型不一致时会发生错误
-- MySQL不报错
SELECT product_id, sale_price
FROM Product
UNION
SELECT product_id, regist_date
FROM Product2;
3.可以使用任何SELECT语句,但ORDER BY子句只能在最后使用一次
-- ORDER BY子句只在最后使用一次
SELECT product_id, product_name
FROM Product
WHERE product_type = '厨房用具'
UNION
SELECT product_id, product_name
FROM Product2
WHERE product_type = '厨房用具'
ORDER BY product_id;
3.包含重复行的集合运算ALL
-- 在UNION后面添加ALL关键字,保留重复行
SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;
INTERSECT,交集,选取两个记录集合中的公共部分。MySQL不支持。
EXCEPT,差集,减法运算。MySQL不支持
二、以列为单位对表进行联结
联结运算,将其他表中的列添加到当前表中,适用于无法从一张表中获取期望数据列的情况。
1.内联结INNER JOIN
两张表及其包含的列:
Product | ShopProduct | |
---|---|---|
product_id | √ | √ |
product_name | √ | |
product_type | √ | |
sale_price | √ | |
purchaset_price | √ | |
regist_date | √ | |
shop_id | √ | |
shop_name | √ | |
quantity | √ |
两张表中的列可分为:
1.两张表中都包含的列:商品编号。
2.只存在于一张表内的列:商品编号之外的列。
-- 将两张表进行内联结
-- 从Product表中取出product_name和sale_price,并与ShopProduct表中的内容进行关联查询
SELECT SP.shop_id,
SP.shop_name,
SP.product_id,
P.product_name,
P.sale_price
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id;
注意:
1.使用关键字INNER JOIN可以将多张表联结在一起。
2.ON用来指定联结条件,类似于WHERE 。需要指定多个键时,可以使用AND、OR。在进行内联结时ON子句必不可少,且ON必须写在FROM和WHERE之间。
3.为了避免混乱,进行联结操作时,按照< 表的别名>.< 列名>
的格式来写SELECT子句中的列。
内联结 + WHERE子句
-- 内联结和WHERE子句结合使用
-- 只选出北京店的数据
SELECT SP.shop_id,
SP.shop_name,
SP.product_id,
P.product_name,
P.sale_price
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id
WHERE SP.shop_id = '000A';
INNER JOIN可以用逗号或JOIN代替。
2.外联结OUTER JOIN
-- 将两张表进行外联结
SELECT SP.shop_id,
SP.shop_name,
SP.product_id,
P.product_name,
P.sale_price
FROM ShopProduct AS SP
RIGHT OUTER JOIN Product AS P
ON SP.product_id = P.product_id;
从结果来看,最下面的两条记录代表的商品不再任何商店售卖,外联结选出了ShopProduct表中不存在的数据。
外联结名称的跟NULL有关,即“结果中包含原表中不存在(在原表之外)的信息”。
外联结的查询结果会包含主表内的所有数据,指定主表的关键字是LEFT和RIGHT。
1.使用LEFT时FROM子句中写在左侧的表是主表,返回包括左表中的所有记录和右表中连接字段相等的记录。
2.使用RIGHT时右侧的表是主表,返回包括右表中的所有记录和右表中连接字段相等的记录。
SELECT SP.shop_id,
SP.shop_name,
SP.product_id,
P.product_name,
P.sale_price
FROM Product AS P
LEFT OUTER JOIN ShopProduct AS SP
ON SP.product_id = P.product_id;
从执行结果来看,使用LEFT或RIGHT指定主表,没有任何区别。
三张以上的表的联结
联结表的数量并没有限制。
-- 创建一张用来管理库存商品的表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 * FROM InventoryProduct;
-- 对3张表进行内联结
SELECT SP.shop_id,
SP.shop_name,
SP.product_id,
P.product_name,
P.sale_price,
IP.inventory_quantity
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id
INNER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id
WHERE IP.inventory_id = 'P001';
3.交叉联结CROSS JOIN
交叉联结是所有联结运算的基础,但实际上用得较少。
-- 交叉联结Product表和ShopProduct表
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP CROSS JOIN Product AS P;
+---------+-----------+------------+--------------+
| shop_id | shop_name | product_id | product_name |
+---------+-----------+------------+--------------+
| 000A | 北京 | 0001 | T恤 |
| 000A | 北京 | 0001 | 打孔器 |
| 000A | 北京 | 0001 | 运动T恤 |
| 000A | 北京 | 0001 | 菜刀 |
| 000A | 北京 | 0001 | 高压锅 |
| 000A | 北京 | 0001 | 叉子 |
| 000A | 北京 | 0001 | 擦菜板 |
| 000A | 北京 | 0001 | 圆珠笔 |
| 000A | 北京 | 0002 | T恤 |
| 000A | 北京 | 0002 | 打孔器 |
| 000A | 北京 | 0002 | 运动T恤 |
| 000A | 北京 | 0002 | 菜刀 |
| 000A | 北京 | 0002 | 高压锅 |
| 000A | 北京 | 0002 | 叉子 |
| 000A | 北京 | 0002 | 擦菜板 |
| 000A | 北京 | 0002 | 圆珠笔 |
| 000A | 北京 | 0003 | T恤 |
| 000A | 北京 | 0003 | 打孔器 |
| 000A | 北京 | 0003 | 运动T恤 |
| 000A | 北京 | 0003 | 菜刀 |
| 000A | 北京 | 0003 | 高压锅 |
| 000A | 北京 | 0003 | 叉子 |
| 000A | 北京 | 0003 | 擦菜板 |
| 000A | 北京 | 0003 | 圆珠笔 |
| 000B | 上海 | 0002 | T恤 |
| 000B | 上海 | 0002 | 打孔器 |
| 000B | 上海 | 0002 | 运动T恤 |
| 000B | 上海 | 0002 | 菜刀 |
| 000B | 上海 | 0002 | 高压锅 |
| 000B | 上海 | 0002 | 叉子 |
| 000B | 上海 | 0002 | 擦菜板 |
| 000B | 上海 | 0002 | 圆珠笔 |
| 000B | 上海 | 0003 | T恤 |
| 000B | 上海 | 0003 | 打孔器 |
| 000B | 上海 | 0003 | 运动T恤 |
| 000B | 上海 | 0003 | 菜刀 |
| 000B | 上海 | 0003 | 高压锅 |
| 000B | 上海 | 0003 | 叉子 |
| 000B | 上海 | 0003 | 擦菜板 |
| 000B | 上海 | 0003 | 圆珠笔 |
| 000B | 上海 | 0004 | T恤 |
| 000B | 上海 | 0004 | 打孔器 |
| 000B | 上海 | 0004 | 运动T恤 |
| 000B | 上海 | 0004 | 菜刀 |
| 000B | 上海 | 0004 | 高压锅 |
| 000B | 上海 | 0004 | 叉子 |
| 000B | 上海 | 0004 | 擦菜板 |
| 000B | 上海 | 0004 | 圆珠笔 |
| 000B | 上海 | 0006 | T恤 |
| 000B | 上海 | 0006 | 打孔器 |
| 000B | 上海 | 0006 | 运动T恤 |
| 000B | 上海 | 0006 | 菜刀 |
| 000B | 上海 | 0006 | 高压锅 |
| 000B | 上海 | 0006 | 叉子 |
| 000B | 上海 | 0006 | 擦菜板 |
| 000B | 上海 | 0006 | 圆珠笔 |
| 000B | 上海 | 0007 | T恤 |
| 000B | 上海 | 0007 | 打孔器 |
| 000B | 上海 | 0007 | 运动T恤 |
| 000B | 上海 | 0007 | 菜刀 |
| 000B | 上海 | 0007 | 高压锅 |
| 000B | 上海 | 0007 | 叉子 |
| 000B | 上海 | 0007 | 擦菜板 |
| 000B | 上海 | 0007 | 圆珠笔 |
| 000C | 广州 | 0003 | T恤 |
| 000C | 广州 | 0003 | 打孔器 |
| 000C | 广州 | 0003 | 运动T恤 |
| 000C | 广州 | 0003 | 菜刀 |
| 000C | 广州 | 0003 | 高压锅 |
| 000C | 广州 | 0003 | 叉子 |
| 000C | 广州 | 0003 | 擦菜板 |
| 000C | 广州 | 0003 | 圆珠笔 |
| 000C | 广州 | 0004 | T恤 |
| 000C | 广州 | 0004 | 打孔器 |
| 000C | 广州 | 0004 | 运动T恤 |
| 000C | 广州 | 0004 | 菜刀 |
| 000C | 广州 | 0004 | 高压锅 |
| 000C | 广州 | 0004 | 叉子 |
| 000C | 广州 | 0004 | 擦菜板 |
| 000C | 广州 | 0004 | 圆珠笔 |
| 000C | 广州 | 0006 | T恤 |
| 000C | 广州 | 0006 | 打孔器 |
| 000C | 广州 | 0006 | 运动T恤 |
| 000C | 广州 | 0006 | 菜刀 |
| 000C | 广州 | 0006 | 高压锅 |
| 000C | 广州 | 0006 | 叉子 |
| 000C | 广州 | 0006 | 擦菜板 |
| 000C | 广州 | 0006 | 圆珠笔 |
| 000C | 广州 | 0007 | T恤 |
| 000C | 广州 | 0007 | 打孔器 |
| 000C | 广州 | 0007 | 运动T恤 |
| 000C | 广州 | 0007 | 菜刀 |
| 000C | 广州 | 0007 | 高压锅 |
| 000C | 广州 | 0007 | 叉子 |
| 000C | 广州 | 0007 | 擦菜板 |
| 000C | 广州 | 0007 | 圆珠笔 |
| 000D | 福建 | 0001 | T恤 |
| 000D | 福建 | 0001 | 打孔器 |
| 000D | 福建 | 0001 | 运动T恤 |
| 000D | 福建 | 0001 | 菜刀 |
| 000D | 福建 | 0001 | 高压锅 |
| 000D | 福建 | 0001 | 叉子 |
| 000D | 福建 | 0001 | 擦菜板 |
| 000D | 福建 | 0001 | 圆珠笔 |
+---------+-----------+------------+--------------+
进行交叉联结时无法使用内联结和外联结中所使用的ON 子句,因为交叉联结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积。
上述交叉联结结果为104条记录,因为ShopProduct表存在13 条记录,Product 表存在8 条记录,所以结果中就包含了13 × 8 = 104 条记录。
内联结是交叉联结的一部分,“内”为“包含在交叉联结结果中的部分”。外联结的“外”为“交叉联结结果
之外的部分”。