Mysql notes

Pick up the right row in group by.

group by中选出的行并不会按照order by的顺序排列，要让它按照顺序选择出正确的，可以套一个嵌套select，让它在里面排好序再交给group by。

select * from (select problem_id, author_id, time from oj_submission order by time desc) submission 
where problem_id in (1,2) 
group by problem_id;

经过group的字段不可以使用where来过滤了，应该使用having：

select count(*) as uv from weblog 
where date = '2012-10-03'
group by user_id
having uv > 3

alter table `questions` change `title` `title` varchar(32) not null;

INSERT … ON DUPLICATE KEY UPDATE

LOAD DATA INFILE '/tmp/data.txt'
REPLACE INTO TABLE your_table
(column1, column2, ...)

date()方法可以将datetime转换为date