Mysql普通索引创建实例

1、前面讲到了Mysql主键索引的创建,下面将介绍Mysql普通索引创建的实例

2、Mysql普通索引创建的实例

      2.1 在创建表的时候,指定普通索引

 create table table_index
(   id int primary key auto_increment  ,
    name varchar(20) ,
	index index_name (name)
);

      利用 下面的sql语句,来查看表的创建语句

show  create  table   table_index; 

+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                              |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_index | CREATE TABLE `table_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+--------------------------------------------

     利用下面的sql语句,来查看表的索引信息

show  index from table_index;

     +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table_index |          0 | PRIMARY    |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| table_index |          1 | index_name |            1 | name        | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+      

       2.2 先创建表的时候没有普通索引,然后利用alter table  或者 create index 添加索引   

create table table_index
(   id int primary key auto_increment  ,
    name varchar(20) 
);

alter table table_index
add index index_name (name);

或者

create table table_index
(   id int primary key auto_increment  ,
    name varchar(20) 
);
create index index_name On  table_index( name );

       利用sql语句,来查看表的索引信息

show  index from table_index;

+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table_index |          0 | PRIMARY    |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| table_index |          1 | index_name |            1 | name        | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+---