UPDATE eroad t2

JOIN (

  select idroad,count(*) as pnum  from ecarnum where idroad>0 and stopcar>0 GROUP BY idroad

) t1 ON t1.idroad = t2.id

SET t2.ikong = t1.pnum;

SELECT COALESCE(null_column, 0) AS null_column FROM whatever;

COALESCE遍历您给出的值列表,并返回第一个非空值.

两个字段唯一

菜单权限，一个用户只能一个路由，一条记录

alter table emenu add constraint  `luyou_iduser` unique (`luyou`, `iduser`)

一个表，交换2条记录的值

update egoodtype a, egoodtype b set a.psort= b.psort, b.psort= a.psort where a.id = ? and b.id=? 

时间戳格式化

select FROM_UNIXTIME(time,'%Y-%m-%d %H:%i:%s')as addtime 

date 日期格式化

SELECT  date_format(a.pbirdate,'%Y年%m月') as pbirdate

增加唯一健

ALTER TABLE

  `jcdgroup`

ADD

  UNIQUE `igroup` USING btree (`igroup`)

增加索引

ALTER TABLE

  `exunjian`

ADD

  INDEX `padmin` USING btree (`padmin`)