连接（JOIN）运算
内连接——INNER JOIN
此处用商品表(product)和商店商品表(ShopProduct)测试，外键：product_id
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;
外连接——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;
结果，数据比内连接是多了几条
内连接和外连接的区别：
- 内连接：只能选出同时存在与两张表的数据
- 外连接：最终结果会包含主表的所有数据
- 指定主表使用关键字：LEFT和RIGHT，指定左侧/右侧的表为主表(两者功能没有差别)。
多表连接(3张及以上,INNER JOIN)
新创一张库存商品表(InventoryProduct)
连接键：product_id
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 = 'S001';
交叉连接——CROSS JOIN
-- 将商店商品表和商品表关联
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP
CROSS JOIN Product AS P;
基础表product只有9条数据，shopproduct有13条数据，但是运行结果却结构包含117条数据
原因：交叉连接是对两站表中的全部记录进行交叉组合那结果通常是两表函数的乘积,所以此连接使用意义不大
建表语句
CREATE TABLE Product
(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));
INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;
-- DDL：创建表
CREATE TABLE ShopProduct
(shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id));
-- DML：插入数据
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT;
-- 建表语句(mysql)
-- DDL：创建表
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));
-- DML：插入数据
START TRANSACTION;
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S001', '0001', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('S001', '0002', 120);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S001', '0003', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S001', '0004', 3);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S001', '0005', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S001', '0006', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S001', '0007', 999);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S001', '0008', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('S002', '0001', 10);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S002', '0002', 25);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S002', '0003', 34);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S002', '0004', 19);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S002', '0005', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S002', '0006', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S002', '0007', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('S002', '0008', 18);
COMMIT;
select * from InventoryProduct;
