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会自动判断,数据量少的是驱动表,数据量多的是被驱动表

相关推荐