mysql主键必定是唯一索引------顺便用实例说说索引的创建

       创建表：

CREATE TABLE `tbtest` (    
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'student id',    
  `name` VARCHAR(32) NOT NULL COMMENT 'student name',    
  `score` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'student score'  
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='student tb'

mysql> show create table tbtest;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbtest | CREATE TABLE `tbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'student id',
  `name` varchar(32) NOT NULL COMMENT 'student name',
  `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'student score',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='student tb' |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

       我们来为name创建索引， 如下：

mysql> ALTER TABLE `tbtest` ADD INDEX idx_name (`name`) ;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show create table tbtest;                         
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                           |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbtest | CREATE TABLE `tbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'student id',
  `name` varchar(32) NOT NULL COMMENT 'student name',
  `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'student score',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='student tb' |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

       1. 添加PRIMARY KEY（主键索引， 一般来说， 如果设置为主键后， 它自动会成为索引， 不需要再设置） 

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ); 

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) ;

ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) ;

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) ;

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