在MySQL中，主要有四类索引： 主键索引（PRIMARY KEY） 唯一索引（UNIQUE） 常规索引（INDEX） 全文索引（FULLTEXT）

 

主键索引是关系数据库中最常见的索引类型，主要作用是确定数据表里一条特定的数据记录的位置。我们可以在字段后添加PRIMARY KEY来对字段设置为主键索引。

注意：

1.最好为每张表指定一个主键，但不是必须指定。

2.一个表只能指定一个主键，而且主键的值不能为空 3.主键可以有多个候选索引（例如NOT NULL，AUTO_INCREMENT）

 

唯一索引与主键索引一样，都可以防止创建重复的值。但是，不同之处在于，每个数据表中只能有一个主键索引，但可以有多个唯一索引。我们使用关键字UNIQUE对字段定义为唯一索引。

 

常规索引技术是关系数据查询中最重要的技术，如果要提升数据库的性能，索引优化是首先应该考虑的，因为它能使我们的数据库得到最大性能方面的提升。

常规索引也存在缺点：

1.多占用磁盘空间

2.会减慢插入，删除和修改操作

3.需要按照索引列上排序格式执行 创建索引我们可以使用INDEX和KEY关键字随表一同创建。

 

全文索引在MySQL中是一个FULLTEXT类型索引，但FULLTEXT索引只能用于MyISAM表，并且只可以在CHAR、VARCHAR或TEXT类型的列上创建，也允许创建在一个或多个数据列上。 但是FULLTEXT是不支持中文全文索引的，所以我们将来会使用效率更高的全文索引引擎Sphinx。

 

添加和删除索引

添加索引： 我们使用add关键字 ALTER TABLE `uses` ADD INDEX/UNIQUE/PRIMARY KEY(字段名称)

这样会在该字段上建立索引（普通索引，唯一索引，主键索引）。

删除索引： ALTER TABLE `users` DROP 索引名称;

这样我们会删除这个索引，我们可以使用show indexes from 表名查看当前表索引。

 

数据表的类型和储存位置

MySQL支持MyISAM、InnoDB、HEAP、BOB、ARCHIVE、CSV等多种数据表类型，在创建一个新MySQL数据表时，可以为它设置一个类型。

MyISAM和InnoDB两种表类型最为重要：

1.MyISAM数据表类型的特点是成熟、稳定和易于管理。

2.MyISAM表类型会产生碎片空间，要经常使用OPTIMIZE TABLE命令去清理表空间

3.MyISAM不支持事务处理，InnoDB支持

4.MyISAM不支持外键，InnoDB支持

5.MyISAM表类型的数据表效率更高 6.MyISAM表类型的数据表会产生三个文件，InnoDB表类型表默认只会产生一个文件。

 

创建表的时候，如果没有明确地指定任何字符集，则新创建数据表的字符集将由MySQL配置文件里charcter-set-server选项的设置决定。

在创建数据表时如果需要指定默认的字符集与之相同，但MySQL客户程序在与服务器通信时使用的字符集，我们需要使用default-character-set选项或通过SQL命令SET NAMES utf8来指定一个字符集为utf8.

 

创建数据表之前，我们应该注意：

1.创建数据库（如已存在则不需要创建）

2.选择数据库

3.在该数据库当中创建数据表

 

创建数据表需要注意：

1.指定数据表的名称（数据表不能重名）

2.指定该表的字段名称、字段数据类型、字段索引

3.指定表类型和表默认字符集（可省略）

 

下面我们正式建一个表：

建成的表如下：

中文名	字段名	数据类型	属性	索引
用户编号	id	INT	UNSIGNED NOT NULL AUTO_INCREMENT	主键
用户名称	username	VARCHAR(50)	NOT NULL	普通
口令	userpass	VARCHAR(50)	NOT NULL	普通
联系电话	telno	VARCHAR(20)	NOT NULL	唯一
性别	sex	ENUM(‘男’,’女’)	NOT NULL DEFAULT ‘男’
出生日期	birthday	DATE	NOT NULL DEFAULT ‘0000-00-00’

 

下面是建表语句

首先

set character_set_server=utf8;      #将Mysql服务的字符集改为utf8,这样待会建的库和表就都是utf8的字符

create database test;                    #建库

use test                                         #使用库才能建表

 

CREATE TABLE IF NOT EXISTS `users`(   

     `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

     `username` VARCHAR(50) NOT NULL,   

     `userpass` VARCHAR(50) NOT NULL,     

    `telno` VARCHAR(20) NOT NULL UNIQUE,     # unique表示建立唯一键

    `sex` ENUM(‘男’，’女’) NOT NULL DEFAULT ‘男’，   

    `birthday` DATE NOT NULL DEFAULT ‘0000-00-00’,     

    PRIMARY KEY(`id`),       #建立唯一键

    INDEX username_index(`username`),    #建立普通索引

    INDEX userpass_index(`userpass`)        #建立普通索引

)ENGINE=MyISAM;

 

注意事项：     

1.表的字段之间要使用逗号隔开。   

 2.建表的最后一句一定不能有逗号。   

 3.表名称和字段名称尽量不要使用MySQL系统的关键字     

4.如果一定要使用关键字，我们可以使用反引号将表名称和字段名称包含起来来进行过滤屏蔽。   

 5.使用反引号会使建表效率增高。     

6.数据表名称和字段名称不能重名     

7.AUTO_INCREMENT属性必须依附于主键索引或唯一索引

 

修改表的语法：

ALTER TABLE 表名 ACTION;

我们可以对表进行修改字段，添加字段，删除字段，添加索引，删除索引，更改表名称，更改字段名称，更改auto_increment属性的初始值等。

 

修改字段：

我们使用change或者是modify关键字

ALTER TABLE `uses` CHANGE `username` `uname` VARCHAR(32) NOT NULL; ALTER TABLE `users` MODIFY `username` VARCHAR(32) NOT NULL;

由上例可以发现： change可以改变字段名称，而modify不可以

 

添加字段：

我们使用add关键字

ALTER TABLE `uses` ADD `tname` VARCHAR(32) NOT NULL;

这样我们就会新增一个tname字段。

 

删除字段：

我们使用drop关键字

ALTER TABLE `users` DROP `tname`; 这样我们会删除tname字段

 

添加和删除索引

添加索引： 我们使用add关键字 ALTER TABLE `uses` ADD INDEX/UNIQUE/PRIMARY KEY(字段名称)

这样会在该字段上建立索引（普通索引，唯一索引，主键索引）。

删除索引： ALTER TABLE `users` DROP 索引名称;

这样我们会删除这个索引，我们可以使用show indexes from 表名查看当前表索引。

 

更改表名称：

我们使用rename关键字

ALTER TABLE 旧表名 RENAME AS 新表名

将旧表名更改为新表名

 

更改AUTO_INCREMENT初始值:

ALTER TABLE 表名称 AUTO_INCREMENT=1

将AUTO_INCREMENT的初始值设置为1

 

删除表：

DROP TABLE [IF EXISTS] 表名称；