查询时间，友好提示

• timestamp 日期类型

  $sql = "select date_format(create_time, '%Y-%m-%d') as day from table_name";

• int 时间戳类型

  $sql = "select from_unixtime(create_time, '%Y-%m-%d') as day from table_name";

  一个sql 返回多个总数

  $sql = "select count(*) all, " ;
  $sql .= " count(case when status = 1 then status end) status_1_num, ";
  $sql .= " count(case when status = 2 then status end) status_2_num ";
  $sql .= " from table_name";

  Update Join / Delete Join

  $sql = "update table_name_1 ";
  $sql .= " inner join table_name_2 on table_name_1.id = table_name_2.uid ";
  $sql .= " inner join table_name_3 on table_name_3.id = table_name_1.tid ";
  $sql .= " set *** = *** ";
  $sql .= " where *** ";
  //delete join 同上。

  替换某字段的内容的语句

  $sql = "update table_name set content = REPLACE(content, 'aaa', 'bbb') ";
  $sql .= " where (content like '%aaa%')";

  获取表中某字段包含某字符串的数据

  $sql = "SELECT * FROM `表名` WHERE LOCATE('关键字', 字段名) ";

  获取字段中的前4位

  $sql = "SELECT SUBSTRING(字段名,1,4) FROM 表名 ";

  查找表中多余的重复记录

• 单个字段

  $sql = "select * from 表名 where 字段名 in ";
  $sql .= "(select 字段名 from 表名 group by 字段名 having count(字段名) > 1 )";

• 多个字段

  $sql = "select * from 表名 别名 where (别名.字段1,别名.字段2) in ";
  $sql .= "(select 字段1,字段2 from 表名 group by 字段1,字段2 having count(*) > 1 )";

  删除表中多余的重复记录(留id最小)

• 单个字段

  $sql = "delete from 表名 where 字段名 in ";
  $sql .= "(select 字段名 from 表名 group by 字段名 having count(字段名) > 1)  ";
  $sql .= "and 主键ID not in ";
  $sql .= "(select min(主键ID) from 表名 group by 字段名 having count(字段名 )>1) ";

• 多个字段

  $sql = "delete from 表名 别名 where (别名.字段1,别名.字段2) in ";
  $sql .= "(select 字段1,字段2 from 表名 group by 字段1,字段2 having count(*) > 1) ";
  $sql .= "and 主键ID not in ";
  $sql .= "(select min(主键ID) from 表名 group by 字段1,字段2 having count(*)>1) ";

  insert不插入重复数据

  replace原理是当发现唯一索引或主键列数据已存在时，先删除旧数据再新增新数据，会导致即使看起来是更新了，但是主键仍然发生变化，如果是业务无关的自增主键，则不应该使用replace。

insert … on duplicate key update…可以比较理想地实现存在即更新，不存在则删除的效果，前提是表中要有主键或唯一索引。

insert ignore, 发现冲突可以不插入, 忽略[

](https://blog.csdn.net/fanst_/article/details/90179325)

遇到一个sql插入的场景，就是在sql插入新数据的时候，直接在sql中判断条件是否满足，如果条件不满足则不插入新数据，某则插入新数据。
insert into tablename(属性1，属性2) select ‘值1’,’值2’ from dual where exists (select 1 from tablename where 子句);

exists和not exists