mysql where join
where 匹配时默认不区分大小写
= <> != < <= > >= in is null is not null
between 3 and 7 包含3和7
and or not ()
null=null 返回false,null<=>null返回true
select a+ifnull(b, 0) from t; 当b为null时,ifnull(b,0)返回0
where Date(order_date) between ‘2005-09-01‘ and ‘2005-09-30‘;
where Year(order_date) = 2005 and Month(order_date) = 9;
where wk = date ‘1982-05-20‘
where date ‘1976-05-20‘ between wk - interval ‘7‘ day and wk
where a.id = b.id
join
自联结
select a.id, a.name
from table as a, table as b
where a.id = b.id and b.pid=3
内部联结
from a inner join b
on a.id = b.id
外部联结
select a.id, b.name
from a left outer join b left表示从a里选择所有行,right...
on a.id=b.cid
可配合group by和聚集函数
驱动表和被驱动表:
left join:左侧的是驱动表,右侧的是被驱动表
right join:左侧的是被驱动表,右侧的是驱动表
join: mysql会自动判断,数据量少的是驱动表,数据量多的是被驱动表