例子：
MariaDB [book]> alter table books drop key index_bName; Query OK, 43 rows affected (0.01 sec) Records: 43 Duplicates: 0 Warnings: 0 MariaDB [book]> desc books; +------------+------------------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------------------------------------+------+-----+---------+----------------+ | bId | int(4) | NO | PRI | NULL | auto_increment | | bName | varchar(255) | YES | | NULL | | | bTypeId | enum('1','2','3','4','5','6','7','8','9','10') | YES | | NULL | | | publishing | varchar(255) | YES | | NULL | | | price | int(4) | YES | | NULL | | | pubDate | date | YES | | NULL | | | author | varchar(30) | YES | | NULL | | | ISBN | varchar(255) | YES | | NULL | | +------------+------------------------------------------------+------+-----+---------+----------------+ 8 rows in set (0.01 sec) MariaDB [book]> desc demo3; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | uName | varchar(20) | YES | UNI | NULL | | | uPwd | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) MariaDB [book]> alter table demo3 drop key uName; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [book]> desc demo3; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | uName | varchar(20) | YES | | NULL | | | uPwd | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) 3. 主键索引查询数据库，按主键查询是最快的，每个表只能有一个主键列，可以有多个普通索引列。
主键列要求列的所有内容必须唯一，而索引列不要求内容必须唯一，但不允许为空
3.1 创建主键索引 3.1.1 方法一：创建表创建主键索引 MariaDB [book]> create table demo4 (id int(4) not null auto_increment,name varchar(4) default null,primary key(id)); Query OK, 0 rows affected (0.02 sec) MariaDB [book]> desc demo4; +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | varchar(4) | YES | | NULL | | +-------+------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec) MariaDB [book]> show index from demo4; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | demo4 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) 3.1.2 方法二：创建表后添加<不推荐> MariaDB [book]> drop table demo4; Query OK, 0 rows affected (0.01 sec) MariaDB [book]> create table demo4 (id int(4) not null auto_increment primary key,name varchar(4) not null); Query OK, 0 rows affected (0.02 sec) MariaDB [book]> alter table demo4 change id id int(4) not null; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [book]> desc demo4; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(4) | NO | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) MariaDB [book]> show index from demo4; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | demo4 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) 3.2 删除主键索引 MariaDB [book]> alter table demo4 drop primary key; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [book]> desc demo4; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int(4) | NO | | NULL | | | name | varchar(4) | NO | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) MariaDB [book]> show index from demo4; Empty set (0.00 sec)主键索引，唯一性索引区别：主键索引不能有NULL，唯一性索引可以有空值
4.复合索引索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引