MySQL 几个比较重要的用法总结

• 给MySQL已经存在的表添加索引

ALTER TABLE `t_areadivision` ADD INDEX code (`code`);

• 带join的update语句

update t_taobao_region t left join t_areadivision a on t.region_code = a.code set t.parent = a.id;

• 插入时间戳

unix_timestamp(now())

• 将重复列的数据设置成指定值

update t_areadivision set parent = -10 where id in (select id from (select id,count(*) sum from t_areadivision group by parent,name having sum > 1) id

• 查找多个字段组合后重复记录

select id,count(*) sum from t_areadivision group by parent,name having sum > 1

• where 后面跟小括号(多个字段) + in,=等运算符

SELECT studentNO FROM student WHERE (studentNO,name) = (SELECT studentNO,name FROM student WHERE name='李四')