Mysql常见语句/问题总结

一，mysql 数据相关。

   1. 查询数据

       select * from 表名 where 字段名1=? and 字段名2=?

       查询时间最近的数据  select * from 表名 where 时间字段名=(select MAX(时间字段名) from 表名 where 字段名=?)

   2.删除数据

      delete from 表名 where 字段名=?

      删除多条数据  delete from 表名 where 字段名=? or 字段名=? 或者 delete from 表名 where 字段名 in(?,?)

   3. 增加数据

       insert into 表名(字段名1,字段名2) values (?,?)

   4 .更新数据

      update 表名set 字段名1=? where 字段名2=?

      更新时间字段  update 表名set 时间字段名=cast('2020-08-21' as datetime) where 字段名=?

二，mysql 表相关

   1. 创建表

DROP TABLE IF EXISTS `table_name`;
CREATE TABLE `table_name`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `amount` decimal (16,2) DEFAULT NULL COMMENT '金额',
  `date` date  DEFAULT NULL  COMMENT '日期',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '测试表' ROW_FORMAT = Dynamic;

      2. 修改字段默认值

      alter table 表名 alter column 字段名 drop default;       (若本身存在默认值，则先删除)
      alter table 表名 alter column 字段名 set default 默认值;  (若本身不存在则可以直接设定)

   3. 修改表名，表的注释

      修改表名：alter table 旧表名 rename to 新表名;

      修改注释：alter table 表名 comment '修改后的表的注释'

   4. 修改字段的注释
      alter table 表名 modify column 字段名 int comment '修改后的字段注释'
      alter table 表名 modify column 字段名 varchar(128) comment '修改后的字段注释' （注意：字段名和字段类型照写就行）

  5. 修改字段类型
      alter table 表名 modify column 字段名 varchar(50);

   6. 修改字段的名称
      alter table 表名 change 旧字段名 新字段名 新数据类型  (如果数据类型不修改  新的数据类型和原来保持一致即可)

   7.  删除一个字段：
    alter table 表名 drop 字段名;

   8.  清空表
    truncate table 表名;
    delete * from 表名;
        (1) truncate 是整体删除 (速度较快)，delete是逐条删除 (速度较慢)
        (2) truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete高的原因
        (3) truncate 不激活trigger (触发器)，但是会重置Identity (标识列、自增字段)，相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的 ID数。而 delete 删除以后，identity 依旧是接着被删除的最近的那一条记录ID加1后进行记录。如果只需删除表中的部分记录，只能使用 DELETE语句配合 where条件

   9. 添加索引
      (1) 添加PRIMARY KEY（主键索引）：
        ALTER TABLE 表名 ADD PRIMARY KEY ( 字段名)

      (2) 添加UNIQUE(唯一索引) ：
        ALTER TABLE 表名 ADD UNIQUE ( 字段名)

      (3) 添加INDEX(普通索引) ：
         ALTER TABLE 表名 ADD INDEX 索引名( 字段名 )

      (4) 添加FULLTEXT(全文索引) ：
         ALTER TABLE 表名 ADD FULLTEXT ( 字段名)

      (5) 添加多列索引：
        ALTER TABLE 表名 ADD INDEX 索引名( 字段名1, 字段名2, 字段名3)

   10. 删除索引
      PRIMARY  KEY（主键索引）       ALTER  TABLE 表名  DROP PRIMARY KEY 
      INDEX(普通索引)或UNIQUE(唯一索引)     ALTER  TABLE  表名  DROP INDEX 索引名    或者    DROP INDEX 索引名 ON 表名

11. 添加 删除主键

        删除主键     alter table 表名  drop primary key;
        增加主键     alter table 表名  add primary key(字段名);
   注:在增加主键之前,必须先把重复的字段名删除掉。

三. 常见问题

   1. 需求，查询出来表中一个或者多个字段 都不重复的数据。

      例如：user表  查询出来 name  age  sex三个字段不完全一样的数据。

id	name	age	sex
1	张三	10	男
2	李四	11	男
3	王五	12	男
4	张三	10	男
5	张三	12	男
6	王五	12	男

 

select * from user where id not in (select id from user group by  name,age,sex)；

这样查询出来为空，正确查询

(1)，利用中间表   select * from user where id not in (select id form  (select id from user group by  name,age,sex) na)；

(2), 修改查询语句   select id,count(id) as countid from user group by  name,age,sex having countid >1;

2. order by 排序问题，

 当一般排序无法满足需求时，需要我们自定义排序。利用field(字段名称,数据1,数据2,数据3)

 value与数据1、数据2、数据3比较，返回1、2、3，如遇到null或者不在列表中的数据则返回0.

select * from 表名 order by field(字段名称,数据1,数据2,数据3)；

正序排序时，如果有字段为空，默认空的字段排在前面，如果需要空的数据排在后面，需要加上：字段 is NULL,字段（select * from 表名 order by 字段 is NULL,字段）

如果想按照A1,A2......A10,A11....A21这样的顺序排序

   

ORDER BY 字段名 REGEXP '^[A-Z]{2}' ASC, 
      IF(字段名 REGEXP '^[A-Z]{2}', LEFT(字段名, 2), LEFT(字段名, 1)), 
      CAST(IF(字段名 REGEXP '^[A-Z]{2}', RIGHT(字段名, LENGTH(字段名) - 2), RIGHT(字段名, LENGTH(字段名) - 1)) AS SIGNED) 

 

或者

ORDER BY   
     CASE WHEN 字段名 REGEXP '^[A-Z]{2}' 
      THEN 1 
      ELSE 0 
     END ASC, 
     CASE WHEN 字段名 REGEXP '^[A-Z]{2}' 
      THEN LEFT(字段名, 2) 
      ELSE LEFT(字段名, 1) 
     END ASC, 
     CASE WHEN 字段名 REGEXP '^[A-Z]{2}' 
      THEN CAST(RIGHT(字段名, LENGTH(字段名) - 2) AS SIGNED) 
      ELSE CAST(RIGHT(字段名, LENGTH(字段名) - 1) AS SIGNED) 
     END ASC 

3. datetime精确到天时间查询问题

   (1) select * from table where company_id=? and to_days(create_time)>=to_days(?) and to_days(create_time)<=to_days(?)

当create_time字段有索引时会导致索引失效

优化：select * from table where company_id=? and create_time>=? and create_time<=DATE_FORMAT(DATE_ADD(?,INTERVAL 1 DAY),'%Y-%m-%d')

 (2)如果有统计的需求，精确到日时,可以使用cast（） 函数或者 date_format（）

SELECT cast(`create_time` AS date) as date ,COUNT(*) FROM  table GROUP BY cast(`create_time` AS date) ORDER BY date DESC 

或者：SELECT  date_format(`create_time`,'%Y-%m-%d') as date ,COUNT(*) FROM table GROUP BY date_format(`create_time`,'%Y-%m-%d') ORDER BY date DESC