集合运算 union/except/intersect/inner join/right join

最新推荐文章于 2024-05-13 01:15:39 发布
上进的菜鸟
最新推荐文章于 2024-05-13 01:15:39 发布
阅读量533
点赞数
分类专栏： mysql
mysql 专栏收录该内容
36 篇文章 0 订阅
订阅专栏
##################
##集合运算
##################

#####################1.表的加法 union(增加的是记录 竖直方向） 
use xscj;

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

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'); 

select product_id,product_name from product
union
select product_id,product_name from product2;


-- 集合运算的注意事项 
# 1.作为运算对象的记录的列数必须相同 
# 2.作为运算对象的记录的列的类型必须一致 （一定要不同类型的列是，可以使用类型转换函数cast)
# 3.可以使用任何select语句，但order by子句只能在最后使用一次 (where/group by /having)
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;

-- 集合运算符会除去重复的记录 （也可与包含重复的记录all）

select product_id,product_name from product
union all
select product_id,product_name from product2;

#########################选取交集  intersect/intersect all
-- mysql可能不支持
select product_id,product_name from product
intersect all
select product_id,product_name from product2;



-- mysql不支持
#########################2.记录的减法 except
select product_id,product_name from product
except
select product_id,product_name from product2
order by product_id;


#########################3.联结（以列为单位对表进行联结，水平方向） 

########3.1内联结 Inner join
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.from子句 
-- （from shopproduct as sp inner join product as p）这个才是来源 

-- 要点2：on 子句指定联结Key,必须在from和where之间 
-- on sp.product_id = p.product_id。联结条件可以用=/</>/<=/between等谓词

-- 要点3：在select子句中指定的列带别名 

## 内联结和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';

#################3.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;


-- 外联结要点1.选取单张表中全部的信息 
--           在实际的业务中，想要生成固定行数的单据是，就需要使用外联结 

-- 外联结要点2.每张表都是主表吗 right右主表 left左主表


###################3.3 三张以上的表的联结 
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));

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

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';


################4.交叉联结 cross join

select sp.shop_id,sp.shop_name,sp.product_id,p.product_name
from shopproduct as sp 
cross join product as p;

-- 联结的过时语法 
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price  
FROM ShopProduct SP, Product P 
WHERE SP.product_id = P.product_id   
AND SP.shop_id = '000A';

-- Join新增知识点
SELECT companies.permalink AS companies_permalink,
       companies.name AS companies_name,
       acquisitions.company_permalink AS acquisitions_permalink,
       acquisitions.acquired_at AS acquired_date
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
    ON companies.permalink = acquisitions.company_permalink
   AND acquisitions.company_permalink != '/company/1000memories'
 ORDER BY 1
-- 注意on后面跟了and，你可以想象成where 但是和where不同的是：on...and是在连接之前发生的
-- 如果后面使用where则是在连接之后发生的

#union（并） 
#except（差） 
#intersect（交） 
#cross join（笛卡尔积） 
#关系除法 
CREATE TABLE Skills (
skill VARCHAR(32), 
PRIMARY KEY(skill)); 
CREATE TABLE EmpSkills (
emp   VARCHAR(32),  
skill VARCHAR(32), 
PRIMARY KEY(emp, skill));

INSERT INTO Skills VALUES('Oracle'); 
INSERT INTO Skills VALUES('UNIX'); 
INSERT INTO Skills VALUES('Java'); 

INSERT INTO EmpSkills VALUES('相田', 'Oracle'); 
INSERT INTO EmpSkills VALUES('相田', 'UNIX'); 
INSERT INTO EmpSkills VALUES('相田', 'Java'); 
INSERT INTO EmpSkills VALUES('相田', 'C#'); 
INSERT INTO EmpSkills VALUES('神崎', 'Oracle'); 
INSERT INTO EmpSkills VALUES('神崎', 'UNIX'); 
INSERT INTO EmpSkills VALUES('神崎', 'Java'); 
INSERT INTO EmpSkills VALUES('平井', 'UNIX'); 
INSERT INTO EmpSkills VALUES('平井', 'Oracle'); 
INSERT INTO EmpSkills VALUES('平井', 'PHP'); 
INSERT INTO EmpSkills VALUES('平井', 'Perl'); 
INSERT INTO EmpSkills VALUES('平井', 'C++'); 
INSERT INTO EmpSkills VALUES('若田部', 'Perl'); 
INSERT INTO EmpSkills VALUES('渡来', 'Oracle'); 

-- skills表是关系除法中的除数 Empskills表示关系除法中的被除数 
#选取掌握了skills表中所有三个领域的emp员工
select distinct emp from empskills esl
where not exists (select skill from skills 
                 except select skill from empskills es2
                 where es1.emp = es2.emp); -- es1.emp=es2.emp代表就对这一个特定的人选取他的所有技能 
-- 三个skill减去每个人的skill，如果大于零（即存在啥东西）就代表没有拥有全部的技能 
-- 如果没有东西就代表拥有全部技能 
-- 注意差集，前面的集合作为标准！！！！