数据库——完整性约束与索引，存储引擎，字符集和校对集

完整性约束

为了防止向数据表插入错误数据，mysql定义了一些维护数据库完整性的规则，即表的约束，常见约束包括：默认约束，非空约束，主键约束，外键约束和唯一约束。
（实体完整性约束：主键约束；参照完整性约束：外键约束；用户自定义完整性：针对某一具体应用的数据必须满足的语义要求 ）


1.默认约束

\quad 默认约束用于为数据表中的字段设置默认值，当插入记录时没有这个字段的值时，系统会自动为这个字段赋予设置的默认值，也可以在插入数据时使用default关键词作为有默认值约束字段要插入的值。

-- 创建数据表时添加默认约束
字段名 字段类型 default 默认值;
-- 修改字段属性的方式添加默认约束
alter table 表名 modify 字段名 字段类型 default 默认值;
-- 修改字段属性的方式删除默认约束
alter table 表名 modify 字段名 字段类型;

2.非空约束

\quad 非空约束指的是字段的值不能为NULL

-- 创建表示添加非空约束
字段名 字段类型 not null;
-- 修改字段属性的方式添加非空约束
alter table 表名 modify 字段名 字段类型 not null;
-- 修改字段属性的方式删除非空约束
alter table 表名 modify 字段名 字段类型;

注意：

• 通常表结构中的default为null表示该字段没有设置默认值，而不能理解为默认值为null，且设置 default null（默认值为null） 在字段允许为null时也没有多大意义。
• 非空约束 not null 与设置default null 不能同时存在，否则出现错误invalid default value
• 字段not null 且没有默认值约束时，插入记录时不能插入null或者省略该字段
• 字段not null 但是有默认值约束（自然default不能是null），插入记录时可以省略该字段，但是不能插入null
• 字段not null约束自然不能插入null，但是没有null约束，是可以插入null值，甚至有默认值插入null时，不会使用默认值，而是使用null值

3.唯一约束

\quad 唯一约束用于保证数据表中字段的唯一性，即该字段的值不能够重复出现，但允许为null，且允许重复的null。

-- 创建表时添加唯一约束，列级约束
字段名 字段类型 unique;
-- 创建表时添加唯一约束，表级约束
unique(字段名1,字段名2,...);
/**
表级约束的完整语法，即unique（字段名）的完整形式：
unique key 索引名（字段列表） 
-- 上述语法表示在添加唯一约束时创建索引，用于加快查询速度。其中索引名可以自己指定，也可以省略，mysql自动使用字段作为索引名。当需要对索引删除时，需要指定这个索引名
-- 创建复合唯一约束:在表级唯一约束创建时，字段列表为多个字段时，组成复合唯一键，只有这多个字段的值同时相同时才会被视为重复记录。
*/

注意：对于一个现有的表，添加和删除唯一性约束无法通过修改表字段属性的方式实现，而是要按照索引的方式实现（索引的概念和使用后面文章会介绍）

-- 添加唯一约束
alter table 表名 add unique(字段名);
-- 删除唯一约束
alter table 表名 drop index 索引名; -- 需要加index，否则可能就是删除字段

4.主键约束

\quad 主键可以唯一标识记录，相当于非空约束和唯一约束的组合，要求被约束的字段不允许重复，也不允许出现null值，即当插入null或插入重复值或没有默认值时省略字段都会插入失败。每个表只有一个主键。

-- create创建表示添加主键，列级约束
字段名 字段类型 primary key;
-- create创建表示添加主键，表约束
primary key(字段列表);

注意：对于一个现有的表，添加和删除唯一性约束无法通过修改表字段属性的方式实现

-- 添加主键约束
alter table 表名 add primary key(字段列表);
-- 删除主键约束
alter table 表名 drop primary key;
-- 删除主键约束并不能删除添加主键约束时产生的非空约束，所以对应非空约束需要另外删除
-- 删除字段的非空约束采用的是修改字段属性的方式，即使得字段属性中不存在 not null即可
alter table 表名 modify 字段名 字段类型;

5.外键约束
\quad 在数据库设计时，为了保证不同表中相同含义数据的一致性和完整性，可为数据表添加外键约束。
\quad 外键指的是在一个表中引用另一个表中的一列或多列，被引用的列具有主键约束或唯一性约束，被引用的表称为主表，引用外键的表称为从表。

-- create创建表时添加外键约束，自然主表已经存在
[constraint 外键约束名称] foreign key [索引名称] (从表中外键字段列表)
references 主表名(主表主键名称列表)
[on delete 处理参数选项]
[on update 处理参数选项];
/*
	constraint 用于定义外键约束的名称，省略mysql会自动生成一个名称
	添加外键约束时，系统会为没有索引的外键字段创建非唯一性（mul）索引，省略索引名称则与外键约束同名
	on delete与on update用于设置主表中的数据被删除或者修改时，从表对应数据的处理方式
*/
-- alter table 添加外键约束
alter table 从表名 add
[constraint 外键约束名称] foreign key [外键索引名称] (从表中外键字段列表)
references 主表名(主表主键名称列表)
[on delete 处理参数选项]
[on update 处理参数选项];

-- 删除外键约束
alter table 从表名 drop foreign key 外键约束名称;
-- 删除外键约束时并不会自动删除系统创建的索引，需要手动删除
alter table 从表名 drop index 索引名;

参数名称	描述
restrict/no action	默认值。要删除主表的数据，要先删除从表的相应数据
cascade	主表删除/更新记录时，同时自动更新从表对应的记录
set null	主表删除/更新记录时，使用null值替换从表中对应的记录（不适用于not null字段）
set default	设默认值，但是InnoDB目前不支持

注意：

• 目前只有InnoDB存储引擎支持外键约束
• 实体间具有一对一，一对多，多对多的联系，而具有关联的表的数据可以通过连接查询方式获取，并且对于没有添加外键约束时，有关联的表的插入，删除和更新操作互不影响，但是对于添加了外键约束的有关联的表的操作会受到一定的约束。
• 从表插入/更新数据时，外键字段的值要符合参照完整性约束，值要么为null要么为主表对应字段的取值
• 主表删除/更新数据时，从表按照添加外键约束时设置的参数自动执行相应的操作

自动增长
mysql提供了自动增长的功能，基本语法格式如下：

字段名 字段类型 auto_increment;

注意：

• 一个表中只能有一个自动增长字段，该字段的数据类型是整数类型，而且必须定义为键，unique key或者primary key；
• 若为自动增长字段插入null，0，default（在允许插入这些值的情况下），则该字段会使用自动增长值；若插入的是一个具体的值，则不会使用自动增长值；
• 自动增长值从1开始自增，每次加1。若插入的值大于自动增长的值，则下次插入的自动增长值会自动使用已有数据中的最大值＋1,；若插入的值小于自动增长值，则不会影响自动增长值（当前数据中的最大值+1不发生改变）
• 使用delete删除记录，自动增长值不会减小

6.check

/*
-- 在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制
-- 同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件 

当学生的性别是男时，其名字不能以Ms.打头。
*/
    CREATE TABLE Student
         (Sno    CHAR(9)， 
          Sname  CHAR(8) NOT NULL，
          Ssex    CHAR(2)，
          Sage   SMALLINT，
          Sdept  CHAR(20)，
          PRIMARY KEY (Sno)，
          CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')
          /*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/
        );

索引

\quad 索引是一种特殊的数据结构，可以看作是利用mysql提供的语法对数据表中的某个/些字段与记录的位置建立一个对应关系，按照一定的顺序排序好，根据语法不同，索引大致分为五种：

• 普通索引，使用key/index定义，不需要加如何限制条件，作用是加快对数据的访问速度
• 唯一性索引：由unique key/index定义，创建唯一性索引时需要 添加唯一性约束，用于防止添加重复的值
• 主键索引：由primary key定义的一种特殊的唯一性索引，用于根据主键自身的唯一性标识每一条记录，防止主键字段为null或重复
• 全文索引：由fulltext key/index定义，用于根据查询字符提高数据量较大的字段的查询速度。
• 空间索引：由spatial key/index定义在空间数据类型字段上的索引，提高系统获取空间数据的效率。

\quad 创建索引的字段不能为空，根据字段个数可分为：单列索引和复合索引。另外，若创建的索引是从左边开始截取数据表中字段值的一部分内容，可以统称为前缀索引。

1.创建索引

-- 创建表时创建索引
primary key (字段列表)[索引选项],
key/index [索引名称][索引类型](字段列表)[索引选项],
unique [key/index] [索引名称][索引类型](字段列表)[索引选项],
fulltext |spatial [key/index] [索引名称][索引类型](字段列表)[索引选项],

-- alter table向已创建的表添加索引
alter table 表名 add
primary key (字段列表)[索引选项]
|key/index [索引名称][索引类型](字段列表)[索引选项]
|unique [key/index] [索引名称][索引类型](字段列表)[索引选项]
|fulltext [key/index] [索引名称][索引类型](字段列表)[索引选项]
|spatial [key/index] [索引名称][索引类型](字段列表)[索引选项];

-- create index 向已创建的数据表添加所有
create [unique|fulltext|spatial] index [索引名称] [索引类型]
on 表名(字段列表) [索引选项];

查看索引

show index/keys from 表名;

删除索引
1）删除主键索引
删除主键索引之前，需要考虑主键字段是否有auto_increment字段属性，有则需要先删除该字段属性，再删除主键索引，否则会报错

-- 删除主键的auto_increment字段属性
alter table 表名 modify 字段名 字段类型; 
-- 删除主键索引
alter table 表名 drop primary key ;
drop index `primary` on 表名;

2）删除非主键索引

alter table 表名 drop index 索引名 ;
drop index 索引名 on 表名;

索引的使用原则：

• 查询条件中频繁使用的字段适合建立索引
• 数值型字段适合
• 存储空间较小的适合
• 取值很少或者重复值较高的字段不适合
• 更新频繁的字段不适合，即当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）

注意：创建主键约束，唯一性约束时会自动创建主键索引和唯一性索引，创建外键时，若没有索引会自动创建一个普通索引。

存储引擎

存储引擎可以看作是数据表存储数据的一种格式，不同的格式具有的特性各不相同。

字符集和校对集

字符集
\quad 字符是计算机中保存的各种文字和符号。由于计算机采用二进制存储数据，用户输入的字符会按照一定的规则转换为二进制后保存，这个过程称为字符编码，将一系列字符的编码规则组合起来就形成了字符集。
\quad mysql提供了各种字符集，通过show character set;或show charset;可以查看可以字符集。

校对集
\quad mysql提供了许多校对集，用于为不同字符集指定比较和排序规则。如 字符集utf8的默认校对集为utf8_general_ci。校对集的名称由"_"分隔的三部分组成，开头是对应的字符集，中间是国家名或者general，结尾是ci、cs或bin，其中ci表示不区分大小写，cs表示区分大小写，bin表示以二进制的方式比较。show collation;可以查看mysql可用的校对集。

字符集和校对集的设置
\quad 根据需求不同，字符集和校对集的设置分为4个方面：mysql环境，数据库，数据表和字段。其中通过相关的变量设置mysql环境的字符集和校对集，决定了数据库，数据表，字段的默认字符集和校对集。

数据库

-- 创建数据库时在库选项（多个之间空格隔开即可）中设置字符集和校对集
[default] character set | charset [=] 字符集名称
[default] collate [=] 校对集名称

数据表

-- 创建数据表时在表选项（多个之间空格隔开即可）中设置字符集和校对集
[default] character set | charset [=] 字符集名称
[default] collate [=] 校对集名称

字段

-- 在字段属性里设置
character set | charset 字符集名称 
collate 校对集名称

注意：

• 若仅指定字符集，未指定校对集，则使用该字符集的默认校对集
• 若仅指定校对集，则使用该校对集对应的字符集
• 若没有为数据表指定字符集与校对集，则会自动使用数据库的
• 若没有为字段指定，则会自动使用数据表的