前面的介绍都是围绕表的数据进行的,本篇介绍一下表的加减法和联结。
1.表的加减法
1.1 UNION-并集去重
select * from sale UNION select * from sale1; select * from sale1 union select * from sale2; #sale1和sale2相差一列数据 >EXECUTE FAIL: The used SELECT statements have a different number of columns alter table sale1 modify column regist_date varchar(256) not null; #修改sale1的注册时间列的属性 select * from sale UNION select * from sale1; >EXECUTE SUCCESS
select * from sale order by sale_price UNION select * from sale1; EXECUTE FAIL: syntax error; select * from sale UNION select * from sale1 order by sale_price; EXECUTE SUCCESS
从上面的例证可以发现UNION就是求并集,UNION语法的三点要求
一是SELECT的列的数目必须一致,名称可以不一致。
二是选取的列对应的属性一致,上述虽然不同属性的列也并集成功了,但是这不是所有的DBMS都可以成功执行,尽量还是保持列的属性一致。
三是ORDER BY必须在最后使用一次,前面的SELECT不能用ORDER BY,其他的筛选字句或者分组子句WHERE/GROUP/HAVING没有使用限制。
【这里我的理解还是和前面介绍的一样,ORDER BY 不是SELECT语句的约束条件,它是SELECT语句执行结束后对结果的排序,UNION应该是对行记录的并集,ORDER BY处理后的输出不再是无序的行记录,有点玄乎哈!】
1.2 ALL-并集不去重
select product_type from sale1 union all select product_type from sale;
1.3 INTERSECT-交集
MySQL暂不支持,坑!后面JOIN可以实现,后面再说吧!
1.4 EXCEPT-差集
select product_name form sale except select product_name from sale1; select product_name from sale1 except select product_name from sale;
MySQL也不支持EXCEPT!不过上面的两个语句的注意要点还是要知道的,UNION求并集的时候2+4和4+2是没有区别的,但是EXCEPT求差集的时候会发现4-2和2-4是完全不一样的结果,要根据自己的需求来决定顺序。
2.JOIN-联结
上一小节的并集,交集和差集都是针对行数据的合并,列的数目和属性一致,JOIN联结是进行列处理形成新表,JONIN可以联结超过两张数目的表。
2.1 INNER JOIN-内连接
这里我用zabbix数据库作为示例数据库,zabbix数据库结构复杂,数据量多,方便演示
select h.hostid,i.itemid,i.name from hosts as h INNER JOIN items as i on h.hostid = i.hostid;
select h.hostid,i.itemid,i.name from hosts as h INNER JOIN items as i on h.hostid = i.hostid where i.name like '%CPU占用%';
INNER JOIN的语句中有新的子句关键词ON,ON后面跟着的是联结条件,也称为联结键,即hosts表中hostid和items表中的hostid,这两个是一样的,在多重联结的时候,联结键可以用AND和OR这些逻辑子句,在联结出的新表中可以再用WHERE进行筛选,ON要写在WHERE前面,先联结再筛选,不然会报错的,在这里我用三种颜色标示了三个子句,先是紫色部分,通过......INNER JOIN......ON形成新表,再SELECT选择新表中的元素,最后再WHERE设置限制新表的限制条件。
select g.groupid,h.hostid,i.itemid,i.name from hosts_groups as g INNER JOIN hosts as h INNER JOIN items as i on g.hostid=h.hostid and h.hostid=i.hostid;
g.hostid这样"表的别名.列名"的可以表明列来源于哪一张表,避免混乱。联结形成的新表示暂时存在的,SELECT执行结束之后,这个新表就会消失,这时候要是想保留新表,就要提一下我们之前介绍的视图了,视图是我认为很不错的功能。
2.2 OUTER JOIN
下面是sale和sale1的表的内容
select A.product_id,B.product_id, A.product_name,B.product_name, A.product_type,B.product_type from sale as A LEFT OUTER JOIN sale1 as B on A.product_name = B.product_name;
select A.product_id,B.product_id, A.product_name,B.product_name, A.product_type,B.product_type from sale as A RIGHT OUTER JOIN sale1 as B on A.product_name = B.product_name;
很明显的可以看到LEFT的时候数据行数和OUTER JOIN左边的表一致,外联结的过程也有一个判断过程,有左侧表的product_name挨个去右侧表中去比对,符合条件就输出SELECT的内容,不符合条件的时候,会输出NULL。
这里RIGHT应该就是以右表为主表,和上面的LEFT理解一样,但是结果和一些书上以及网页上看到的不一样,需要深究。
2.3 交叉联结-CROSS JOIN
没有太多的实际用途,就是对两张表的行数据进行交叉组合。