获取表创建语句

  1. SHOW CREATE TABLE table

7天后

  1. DATE_ADD(NOW(), INTERVAL 7 DAY);//2020-10-16 14:56:42

7天前

  1. select DATE_SUB(NOW(), INTERVAL 7 DAY);//2020-09-23
  2. select DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY),'%Y-%m-%d %H:%i:%S');//2020-09-23 09:21:19

删除多个字段

  1. alter table table_name 
  2.     drop column `field_name`,
  3.     drop column `field_name`;

新增多个字段

  1. alter table table_name 
  2.     add column `field_name` VARCHAR(50) NULL DEFAULT NULL COMMENT '备注',
  3.     add column `field_name` VARCHAR(50) NULL DEFAULT NULL COMMENT '备注' after field_name;

获取某列数据为字符串

  1. //某字段10行数据转字符串
  2. GROUP_CONCAT(id) //1,2,3
  3. //连接一个或者多个字符串
  4. concat('11','22','33') // 112233

时间操作

  1. //获取系统时间戳
  2. unix_timestamp()
  3. //获取当前时间
  4. now()
  5. //时间转时间戳
  6. unix_timestamp('2018-01-15 09:45:16')
  7. //时间戳转时间
  8. from_unixtime(1515980716, '%Y-%m-%d %H:%i:%S')

获取要删除表的语句

  1. SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
  2. FROM information_schema.tables
  3. WHERE table_schema = 'db_name' AND Table_name LIKE '%table_name%';

某库的表总数

  1. SELECT count(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA='db_name';

获取gourp by 后的总条数

  1. select count(*) as total from (select count(column_name) from table_name group by column_name) as t

不重复字段的条数

  1.  ... count(distinct column_name) ...

获取分组字段的所有信息

  1.  ... GROUP_CONCAT(column_name) ... GROUP BY column_name