【知识积累】MYSQL之索引

#查看索引
SHOW INDEX FROM tableName;
#创建复合索引
create UNIQUE INDEX indexName ON tableName(column1, column2, column3);
#创建复合唯一索引
ALTER TABLE tableName ADD UNIQUE INDEX indexName ON (column1, column2, column3);
#创建主键索引
ALTER TABLE tableName ADD PRIMARY KEY (id);
#创建唯一索引
ALTER TABLE tableName ADD UNIQUE INDEX indexName(id);
#创建普通索引
ALTER TABLE tableName ADD INDEX indexName(id);
#删除索引
DROP INDEX indexName ON tableName;

DROP TABLE IF EXISTS index_a;
CREATE TABLE index_a(
id BIGINT(10) NOT NULL auto_increment,
username VARCHAR(50) DEFAULT NULL COMMENT '用户名',
password VARCHAR(50) DEFAULT NULL COMMENT '密码',
age VARCHAR(2) DEFAULT NULL COMMENT '年龄',
mobile VARCHAR(20) DEFAULT NULL COMMENT '电话', 
create_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY key (id), #创建主键索引
KEY (username) USING BTREE #创建普通索引
) ENGINE=INNODB auto_increment=1 DEFAULT CHARSET=utf8 COMMENT '用户表';
INSERT INTO index_a(username, password, age, mobile) VALUES ('张三', '123456', '1', '13838380431');
INSERT INTO index_a(username, password, age, mobile) VALUES ('李四', '123456', '2', '13838380432');
INSERT INTO index_a(username, password, age, mobile) VALUES ('王五', '123456', '3', '13838380433');
INSERT INTO index_a(username, password, age, mobile) VALUES ('马六', '123456', '4', '13838380434');

SHOW INDEX FROM index_a; 

CREATE INDEX username_password ON index_a(username, password); 

ALTER TABLE index_a ADD UNIQUE INDEX mobile(mobile); 

ALTER TABLE index_a ADD INDEX create_time(create_time); 

DROP INDEX mobile ON index_a; 

SELECT * FROM index_a;
UPDATE index_a SET password = '' WHERE id = 1;
DELETE FROM index_a WHERE id = 1;
TRUNCATE index_a; #清空表
ALTER TABLE index_a auto_increment = 2; #修改自增下标