SQL sever 基础学习7(集合运算)

1.表的加法

特征：以行为单位进行操作

表的加法——union:并集

select product_id, product_name
    from product
UNION
select product_id, product_name
    from product2;
--可以select两个表中的不同名的行，但要求对应行数据类型一致如下例
select product_id, product_name
    from product
UNION
select index_id, chr
    from addition;
/*
表中的product_id与index_id数据类型均为char(10),product_name与char的数据类型均为varchar(100);
*/

第二例一样可以查询出相应的结果

注：作为运算对象的记录的列数必须一样

注2：作为运算对象的记录中的列的类型必须一致

注3：可以使用任何select语句，但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; 

包含重复键的集合运算——ALL运算

--例1
select product_id, product_name
    from product
union all
select product_id, product_name
    from product2;
--例2
select product_id, product_name
    from product
UNION all
select index_id, chr
    from addition
    order by product_id;

选取表中的公共部分——INTERSECT:交集

select product_id, product_name
    from product
intersect
select product_id, prrduct_name
    from product2
order by product_id;
--INTERSECT应用与两张表，选取他们当中的公共记录

2.表的减法——EXCEPT(差集)

select product_id,product_name
    from product
except
select product_id, product_name
    from product2
order by product_id;

注：注意被减数与减数的位置，被减数在except前，减数在后

二.联结（以列为单位对表进行联结）

特征：以列为单位进行操作

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;
--也可以不使用别名，直接用<表名>.<列名的形式>选取
--例2
select addition.index_id, addition.product_account,
       product.product_id,product.product_name,         
       product.sale_price,product.sale_price*addition.product_account as really_price
	from product inner join addition 
	on product.product_id = addition.index_id;

注：ON子句

on之后指定两张表联结所使用的列（联结键），其实on起到了与where一样的作用，指定多个连接件时可以用and，or。同时，联结条件也可以用between 与>,<等逻辑运算键关联起来

On与where同时使用

--例1
select addition.index_id, addition.product_account, 
product.product_id,product.product_name, 
product.sale_price,product.sale_price*addition.product_account as really_price
	from product inner join addition 
	on product.product_id = addition.index_id
	where addition.product_account <= 0.85;
--例2
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';

2.外联结——OUTER JOIN

--例1
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;
--例2
select addition.index_id, addition.product_account, product.product_name, 
product.sale_price,product.sale_price*addition.product_account as really_price
	from product left outer join addition 
	on product.product_id = addition.index_id;
--例3
select addition.index_id, addition.product_account, product.product_name, 
product.sale_price,product.sale_price*addition.product_account as really_price
	from product right outer join addition 
	on product.product_id = addition.index_id;

在外联结前必须注明谁是主表，决定谁是主表额关键字就是 例2 与例3 的right 与left。

例2中确定了主表为product，所以结果中的行数与product表中的行数一致，同样的，在例3中确定了主表为addition，所以结果中二点行数与addition表中的行数一致

注：内联结与外联结的特点与区别

联结形式	作用
inner join	内联结只取两张表共同有的键值进行运算
right outer join	右外联结取右主表中所有的信息与辅表进行运算，无法与辅表匹配的信息也即多余的信息也会显示出来，由于主表多余的信息无法与辅表匹配，导致计算结果中多余的信息以null值填充
left outer jzuo	同上

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

4.交叉联结——cross join

--例1
select sp.shop_id, sp.shop_name, sp.product_id, p.product_name
    from shopProduct as sp cross join product as p;
--例2
select product.product_id, product.product_name, product.sale_price, 
addition.product_account, product.sale_price*addition.product_account as final_price
	from product cross join addition;

注：使用交叉联结是不能使用on子句

注2：交叉连接就是将两表中的所有记录做交叉组合，结果的个数就是两张表中的行数乘积