sql create table index

drop table if exists `users`;

CREATE TABLE IF NOT EXISTS `users` (

`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

`username` VARCHAR(50) NOT NULL,

`userpass` CHAR(32) NOT NULL,

`telno` VARCHAR(20) NOT NULL,

`sex` ENUM('男','女') NOT NULL DEFAULT '男',

`birthday` DATE NOT NULL DEFAULT '0000-00-00',

PRIMARY KEY(`id`),

INDEX username_index(`username`),

INDEX userpass_index(`userpass`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `users` CHANGE `username` `uname` VARCHAR(32) NOT NULL DEFAULT '';

ALTER TABLE `users` MODIFY `userpass` VARCHAR(16) NOT NULL DEFAULT '';

ALTER TABLE `users` ADD `tname` VARCHAR(40) NOT NULL DEFAULT '';

alter table users add tpass char(38) not null default '' after userpass;

alter table users drop tname;

alter table users drop tpass;

drop table if exists user;

alter table users rename as user;

insert into user(uname, userpass, telno) values('admin', '123456', '123456789');

insert into user(uname, userpass, telno) values('root', 'sendto2018', '56789');

alter table user auto_increment=1;

-- \G 显示结果立起来

show indexes from user\G;

ALTER TABLE `user` DROP INDEX `userpass_index`;

ALTER TABLE `user` ADD INDEX `userpass_index`(userpass);