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

  创建表:

  1. CREATE TABLE `tbtest` (      
  2.   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'student id',      
  3.   `nameVARCHAR(32) NOT NULL COMMENT 'student name',      
  4.   `score` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'student score'    
  5. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='student tb'  
        看下:
  1. mysql> show create table tbtest;  
  2. +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
  3. Table  | Create Table                                                                                                                                                                                                                                                                                                |  
  4. +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
  5. | tbtest | CREATE TABLE `tbtest` (  
  6.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'student id',  
  7.   `namevarchar(32) NOT NULL COMMENT 'student name',  
  8.   `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'student score',  
  9.   PRIMARY KEY (`id`)  
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='student tb' |  
  11. +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
  12. 1 row in set (0.00 sec)  
       显然:mysql主键是唯一索引。


       我们来为name创建索引, 如下:

  1. mysql> ALTER TABLE `tbtest` ADD INDEX idx_name (`name`) ;  
  2. Query OK, 0 rows affected (0.16 sec)  
  3. Records: 0  Duplicates: 0  Warnings: 0  
  4.   
  5. mysql> show create table tbtest;                           
  6. +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
  7. Table  | Create Table                                                                                                                                                                                                                                                                                                                           |  
  8. +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
  9. | tbtest | CREATE TABLE `tbtest` (  
  10.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'student id',  
  11.   `namevarchar(32) NOT NULL COMMENT 'student name',  
  12.   `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'student score',  
  13.   PRIMARY KEY (`id`),  
  14.   KEY `idx_name` (`name`)  
  15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='student tb' |  
  16. +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
  17. 1 row in set (0.00 sec)  

        顺便来小结一下各类索引的创建:

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

  1. ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );   
       2. 添加UNIQUE(唯一索引) 
  1. ALTER TABLE `table_name` ADD UNIQUE ( `column` ) ;  
       3. 添加INDEX(普通索引) 

  1. ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) ;  
       4. 添加FULLTEXT(全文索引) 

  1. ALTER TABLE `table_name` ADD FULLTEXT ( `column`) ;  
       5. 添加多列索引 (实际上就是捆绑索引)

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


       简单, 不多说。
阅读更多
换一批

没有更多推荐了,返回首页