MySQL-约束与索引

约束

保证数据的完整性、一致性、有效性

默认约束

• 在插入记录时,如果不给该字段赋值,则使用默认值
• 字段名 数据类型 default 值

非空约束

• 不允许将该字段设置为NULL
• 字段名 数据类型 not null

索引

定义

对数据库中表的一列或者多列的值进行排序的一种结构(MySQL中用Btree方式)

优缺点

• 优点
  • 加快数据的检索速度
• 缺点
  • 当对表中的数据进行增加、删除或修改的时候,索引也得动态维护,降低了数据的维护速度
  • 索引需要占用物理空间

索引示例

# 开启运行时间检测
set profiling=1;
# 执行查询语句
select name from t1 where name="lucy99999";
# 查看执行时间
show profiles;
# 在name字段创建索引
create index name on t1(name);
# 再执行查询语句
select name from t1 where name="lucy88888";
# 查看执行时间
show profiles;

索引分类

普通索引 index

• 一个表中可以有多个index字段
• 字段的值可以有重复,且可以为null值
• 经常把做查询条件的字段设置为index字段
• index字段的key标志是MUL

# 创建index
# 创建表时
create table 表名(...,index(字段名),index(字段名),...);
# 已有表
create index 索引名 on 表名(字段名);

# 查看索引
desc 表名
show index from 表名\G;

# 删除索引
drop index 索引名 on 表名;

唯一索引 unique key

• 一个表中可以有多个unique字段
• unique字段的值不允许重复,但可以为null
• unique的key标志是UNI

# 创建唯一索引
# 创建表时创建
create table 表名(...,unique(字段名),unique(字段名),...);
# 在已有表中创建
create unique index 索引名 on 表名(字段名);
# 删除唯一索引
drop index 索引名 on 表名;
# index,unique在删除时只能一个一个删

主键索引 primary key

• 一个表中只能有一个主键(primary)字段
• 对应字段的值不允许重复,且不能为空
• 主键字段的key标志PRI
• 把表中能够唯一标识一条记录的字段设置为主键,通常把表中记录编号的字段设置为主键

# 创建主键索引
# 创建表时
create table 表名(字段名 数据类型 primary key [auto_increment],...)
auto_increment = 起始值;
# 已有表
alter table 表名 add primary key(字段名);
# 删除自增长属性(modify)
alter table 表名 modify 字段名 数据类型;
# 删除主键索引
alter table 表名 drop primary key;
# 已有表添加自增长属性
alter table 表名 modify 字段名 数据类型 auto_increment;
# 已有表重新指定起始值
alter table 表名 auto_increment = 起始值;

外键索引 foreign key

• 让当前表字段的值在另一个表的范围内选择
• 主表、从表字段数据类型要一致
• 主表被参考字段：主键

# 创建外键
foreign key(参考字段名)
references 主表(被参考字段名)
on delete 级联动作
on update 级联动作

# 在已有表中添加外键
# 在已有表中添加外键时,会受到表中原有数据的限制
alter table 表名 add
foreign key(参考字段名) references 被参考表名(被参考字段名)
on delete 级联动作
on undate 级联动作

# 查看外键名
show create table 表名;
# 删除外键
alter table 表名 drop foreign key 外键名;

级联动作

• cascade 数据级联更新
  • 当主表删除记录时,如果从表有相关联记录则级联删除
  • 当主表更新被参考字段的值时,从表级联更新参考字段的值
• restrict (默认)
  • 当主表删除记录时,如果从表中有相关记录则不允许主表删除
  • 当主表更新记录时,如果从表中有相关记录则不允许主表更新
• set null
  • 当主表删除记录时,从表中相关记录外键字段变为null
  • 当主表更新记录时,从表中相关记录外键字段变为null
• no action
  • 同restrict,都是立即检查外键限制