InnoDB SQL分析

-- 新增列
ALTER TABLE student ADD COLUMN `class_id` int(11) DEFAULT NULL COMMENT '班级id' after `student_name`;
-- 修改列
ALTER TABLE student MODIFY COLUMN `class_name` varchar(20) DEFAULT NULL COMMENT '班级名称';
-- 新建索引
ALTER TABLE student ADD INDEX idx_class_id(class_id);
-- 创建表时，带索引
CREATE TABLE `config_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '代理自增主键',
  `config_id` int(11) NOT NULL COMMENT '配置id',
  PRIMARY KEY (`id`),
  KEY `idx_config_id` (`config_id`),
  KEY `idx_other_id` (`other_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配置表';
-- update 错误写法
update student set age = 1 and student_name = 'lilei' where id = 1;
-- 上述sql意思是：age值是bool表达式，如果name是lilei，则age=1，否则age=0
update student set age = (1 and student_name = 'lilei') where id = 1;
-- update正确写法
update student set age = 1, student_name = 'lilei' where id = 1;
-- select错误写法(sql5.6不报错class_name随机，sql5.7报错，sql8报错)
select class_id, count(id), class_name from student group by class_id;
-- select正确写法
select class_id, count(id), class_name from student group by class_id, class_name;

explain select * from student where id = 10