How to create and delete indexes in MySQL?

mysql index, mysql creates an index, mysql deletes an index

1. In a relational database, an index is a database structure related to a table, which enables the execution of SQL statements corresponding to the table Got faster. Today I will briefly demonstrate the creation, query and deletion of indexes in mysql.

2. First create a table casually, the SQL statement is as follows:

CREATE TABLE IF NOT EXISTS `student` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` varchar(64) NOT NULL DEFAULT '' COMMENT '姓名',
 `sex` tinyint(1) NOT NULL COMMENT '性别',
 `age` tinyint(2) NOT NULL COMMENT '年龄',
 `class` varchar(64) NOT NULL DEFAULT '' COMMENT '班级',
 PRIMARY KEY (`id`)
 ) ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='学生表';

3. You can see that in the SQL statement to create the table, a primary key index has been established. At this time, view the index in the table: SHOW INDEX FROM `student`, the result is as shown in the figure:

##4. Of course, we can also add other indexes based on , such as a unique index. Assuming that each student's name cannot be repeated, then you can add a unique index on the name field:

ALTER TABLE `student` ADD UNIQUE `stu_name` (`name`);

ALTER TABLE `student` ADD INDEX `stu_class` (`class`);

ALTER TABLE `student` DROP INDEX `stu_name`;
ALTER TABLE `student` DROP INDEX `stu_class`;

ALTER TABLE `student` DROP PRIMARY KEY;

ALTER TABLE `student`  MODIFY `id`  int(10) NOT NULL COMMENT '学号'

 ALTER TABLE `student` DROP PRIMARY KEY;

View the index in the table, SHOW INDEX FROM `student`, in the table There is no index anymore

The above is the detailed content of How to create and delete indexes in MySQL?. For more information, please follow other related articles on the PHP Chinese website!