mysql命令简单使用

简单使用

更新字段值

UPDATE table_name SET column1=value1,column2=value2 WHERE some_column=some_value;

增加表字段

alter table table_name add field_01 int(10) NOT NULL default 0 comment 'xxx';

更改表字段名和类型

alter table table_name change field_old field_new int(10) NOT NULL DEFAULT 0 COMMENT 'xxxx'

查看建表语句

show create table tabel_name

更改表字段类型

alter table table_name modify field_xx INT(10) NOT NULL DEFAULT 0 comment 'xxx'

查看sql执行时索引

explain select * from table_name where column_a = xxx

索引相关

ALTER TABLE table_name ADD PRIMARY KEY ( `column` ) 

ALTER TABLE table_name ADD UNIQUE ( `column` )

ALTER TABLE table_name ADD FULLTEXT ( `column`)

ALTER TABLE table_name ADD INDEX idx_xx_xx_xx ( `column1`, `column2`, `column3` )

//删除索引
ALTER TABLE table_name DROP INDEX index_name

建表

CREATE TABLE `student` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` VARCHAR(64) NOT NULL DEFAULT "" COMMENT '姓名',
  `age` INT(10) NOT NULL DEFAULT 0 COMMENT '年龄',
  `stu_card` VARCHAR(64) NOT NULL COMMENT '学生卡',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '是否逻辑删除',
  PRIMARY KEY (`id`),
  UNIQUE `uk_card` (`stu_card`),
  KEY `idx_name_age_card` (`name`,`age`,`stu_card`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_updated_at` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='xxx';

 根据日期查询

select * from table_name where updated_at = '20xx-xx-xx 15:00:00'

 表连接

#left join t1.c1集合 减 t2.c2集合
select t1.c1,t1.c2,t2.c1 from table_1 as t1 left join table_2 as t2 on t1.c1 = t2.c2
#inner join t1.c1集合 交 t2.c2集合
select t1.c1,t1.c2,t2.c1 from table_1 as t1 inner join table_2 as t2 on t1.c1 = t2.c2
#left join t2.c2集合 减 t1.c1集合
select t1.c1,t1.c2,t2.c1 from table_1 as t1 right join table_2 as t2 on t1.c1 = t2.c2

 正则表达式匹配

select *  from table_name where column_x1 REGEXP 'xxx'

GROUP_CONCAT

select GROUP_CONCAT(column) from table