mysql中表的约束及索引-笔记四

一、表的约束

约束条件	说明
PRIMARY KEY	主键约束，用于唯一标识对应的记录
FOREIGN KEY	外键约束
NOT NULL	非空约束
UNIQUE	唯一性约束
DEFAULT	默认值约束，用于设置字段的默认值

1、主键约束

1.1、单字段主键

字段名 数据类型 PRIMARY KEY
列如：
CREATE TABLE student(
id int PRIMARY KEY,
name varchar(20),
age int
);

1.2、多字段主键

PRIMARY KEY(字段名1，字段名2，..)
列如：
create table student(
id int,
name varchar(20),
age int,
PRIMARY KEY(id,name,age)
);

2、非空约束

字段名 数据类型 NOT NULL
列如：
create table student(
id int PRIMARY KEY,
name varchar(20) NOT NULL,
age int
);

3、唯一约束

字段名 数据类型 UNIQUE
列如：
create table student(
id int PRIMARY KEY,
name varchar(20) NOT NULL,
age int UNIQUE
)；

4、默认约束

字段名 数据类型 DEFAULT 默认值
列如：
create table student(
id int PRIMARY KEY AUTO_INCREMENT,
name int NOT NULL,
grade float DEFAULT 0
);

二、设置表的字段值自动增加

在数据表中，若想为表中插入的新记录自动生成唯一的ID，可以使用“AUTO_INCREMENT”约束来实现，AUTO_INCREMENT约束的字段可以是任何整数类型。默认情况下，该字段的值是从1开始自增的。

语法：字段名 数据类型 AUTO_INCREMENT
列如：
create table student(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(20),
grade float
);

三、索引

1、索引的概念

数据库的索引好比新华字典的音序表，它是对数据库表中一列或多列的值进行排序后的一种结构，其作用就是提高表中数据的查询速度。

2、索引分为：

索引	说明
普通索引	key或index
唯一性索引	unique
全文索引	fulltext
单列索引	无
多列索引	无
空间索引	spatial

2.1创建索引

2.1.1创建表的时候创建索引

语法：
create table 表名(
字段名 数据类型[完整性约束条件]，
字段名 数据类型[完整性约束条件],
....
[UNIQUE | FULLTEXT | SOATIAL] INDEX | KEY [别名](字段名1[(长度)] [ASC | DESC])
)

关于上述语法的相关解释具体如下：

（1）UNIQUE：可选参数，表唯一索引。

（2）FULLTEXT：可选参数，表示全文索引。

（3）SPATIAL：可选参数，表示空间索引。

（4）INDEX和KEY：用来表示字段的索引，二者选一即可。

（5）别名：可选参数，表示创建的索引的名称。

（6）字段名1：指定索引对应字段的名称。

（7）长度：可选参数，用于表示索引的长度。

（8）ASC和DESC：可选参数，用于表示索引的长度。

2.1.1.1创建普通索引

create table student(
id int,
name varchar(20),
INDEX(id)
)
使用show create table student\G;查看表的结构
EXPLAIN SELECT * FROM STUDENT WHERE ID=1\G;查看索引是否被使用

2.1.1.2创建唯一性索引

create table student(
id int not null,
name varchar(20) not null,
grade float,
unique index 别名(id ASC)
)

2.1.1.3创建全文索引

create table student(
id int not null,
name varchar(20) not null,
grade float,
fulltext index 别名(id)
)engine=myisam;

由于目前只有MyISAM存储引擎支持全文索引，InnoDB存储引擎还不支持全文索引。

2.1.1.4创建单列索引

create table student(
id int not null,
name varchar(20) not null,
index 别名(name(20))
)

2.1.1.5创建多列索引

create table student(
id int not null,
name varchar(20) not null,
grade float,
index 别名(id,name(20))
)

2.1.1.6创建空间索引

create table student(
id int not null,
space GEOMETRY not null,
name varchar(20) not null,
grade float,
spatial index 别名(space)
)ENGINE=MyISAM;

3、通过CREATE INDEX语句在已经存在的表上创建索引

语法：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名 (字段名 |(长度)|[ASC | DESC])

创建表：
create table student(
bookid int not null,
bookname varchar(255) not null,
authors varchar(255) null,
comment varchar(255) null,
publicyear year not null
)

3.1创建普通索引

create index 别名 on 表名(字段名)；
列如：
create index bieming on student(bookid);

3.2创建唯一性索引

语法：create unique index 别名 on 表名(字段名)；

3.3创建单列索引

语法：create index 别名 on 表名(字段名)；

3.4创建多列索引

语法：create index 别名 on 表名(字段名，字段名...)；

3.5创建全文索引

重新创建表:
create table teacher(
bookid int not null,
bookname varchar(255) not null,
authors varchar(255) not null,
info varchar(255) null,
comment varchar(255) null,
publicyear year not null
)ENGINE=MyISAM;

语法：create fulltext index 别名 on 表名(字段名)

3.6创建空间索引

重新创建表：
create table opp(
g GEOMETRY NOT NULL
)ENGINE=MyISAM;

create spatial index 别名 on 表名(字段名)；

4、使用ALTER TABLE语句在已经存在的表上创建索引

语法：
alter table 表名 add [unique | fultext | spatial] index 索引名(字段名[(长度)][ASC | DESC])

创建表：
create table book(
bookid int not null,
bookname varchar(255) not null,
authors varchar(255) not null,
info varchar(255) null,
comment varchar(255) null,
publicyear year not null
)

4.1创建普通索引

alter table 表名 add index 别名(字段名);
列如：
alter table book add index bieming(bookid);

4.2创建唯一性索引

alter table 表名 add unique 别名(字段名);
列如：
alter table book add unique bieming(bookid);

4.3创建单列索引

alter table 表名 add index 别名(字段名);
列如：
alter table book add index bieming(bookid);

4.4创建多列索引

alter table 表名 add index 别名(字段名,字段名,....);
列如：
alter table book add index bieming(bookid,bookname);

4.5创建全文索引

重新创建表:
create table book1(
bookid int not null,
bookname varchar(255) not null,
authors varchar(255) not null,
info varchar(255) null,
comment varchar(255) null,
publicyear year not null
)ENGINE=MyISAM;

alter table 表名 add fulltext index 别名(字段名);
列如：
alter table book add fulltext index bieming(bookid);

4.6创建空间索引

重新创建表：
create table opp(
g GEOMETRY NOT NULL
)ENGINE=MyISAM;

alter table 表名 add spatial index 别名(字段名);

四、删除索引

1、使用alter table 删除索引

语法：alter table 表名 drop index 索引名;

2、使用drop index 删除索引

drop index 索引名 on 表名;