mysql

关键字/函数

exists, not exists

desc select * from ......

substring(xx,1,5)

word like concat ('%', #{word}, '%')

order by rand() limit 10

ROUND

having

sql

批量插入

  insert into Table2(field1,field2,...) select value1,value2,... from Table1

日期

  DATE_SUB(CURDATE(), INTERVAL 1 DAY);

  DATE_ADD(CURDATE(), INTERVAL 1 DAY); 

  MONTH(date_field) YEAR(date_field)

条件判断case then else

  SELECT id,(CASE date WHEN '0000-00-00' THEN '' ELSE date END) AS date FROM table

重复的插入会被忽略
  INSERT IGNORE INTO tags (tag) VALUES ('good');

使用全文索引并且匹配搜索
你需要首先添加全文搜索索引到指定的列(content_column)。注意如果你表里已经有数据的话，不会创建索引，所以你需要使用一个空的表来执行这个语句
  select * from articles where MATCH(content_column) AGAINST ('music')

重设置指定table里的自动增加数值
  ALTER TABLE your_table AUTO_INCREMENT = 2

随机

select * from Table order by rand() limit 10

SELECT * FROM Table AS t1 
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM Table)) AS id) AS t2 
WHERE t1.id >= t2.id 
ORDER BY t1.id ASC LIMIT 1