跳转至

5. 约束索引

约束

作用: 保证数据打一致性,有效性

默认约束(default)

插入记录时, 不给该字段赋值则使用默认值

示例

sex enum('M', 'F', 'S') default 'S',

非空约束(not null)

不允许该字段的值为 null, 如 id int not null,

练习

create database db3
create table t1(
    id int not null,
    name varchar(15) not null,
    sex enum('M', 'F', 'S') default 'S',
    course varchar(20) not null default 'Python'
);

外键约束(foreign key)

定义: 让当前表的字段值在另一张表的范围内去选择

语法格式

foreign key(参考字段名)
references 主表(被参考字段名)
on delete 级联动作
on update 级联动作;

使用规则

  1. 主表、从表字段数据类型要一致
  2. 主表: 被参考字段是主键

示例

表1:缴费信息表(财务)
    id  姓名     班级      缴费金额
    1   唐伯虎   AID07     300
    2   点秋香   AID07     300

   create table bjtab(
-> stu_id int,
-> name varchar(20),
-> money smallint);

    insert into jftab values
    (1, '唐伯虎', 'AID07', 300),
    (2, '点秋香', 'AID07', 300);


表2:学生信息表(班主任)
   stu_id    姓名     缴费金额
   3        祝枝山     300

create table bjtab(
stu_id int,
name varchar(20),
money smallint,
foreign key(stu_id) references jftab(id)
on delete cascade
on update cascade
) character set utf8;

删除外键

alter table 表名 drop foreign key 外键名;

外键名的查看方式:

show create table 表名;

已有表添加外键

alter table 表名 add foreign key(stu_id) references jftab(id) on delete 级联动作 on update 级联动作;

级联动作
  1. cascade
    数据级联删除,级联更新(参考字段)

  2. restrict(默认)
    从表中有相关联记录,不允许主表操作

  3. set null
    主表删除\更新时,从表相关联记录字段值为NULL

索引

定义: 对数据库中表的一列或多列的值进行排序的一种结构(Btree)

优点: 加快数的检索速度

缺点:
1. 当你对表中数据更新时, 索引需要动态维护, 占用系统资源, 降低数据维护速度
2. 占用物理存储空间

索引示例

1. 开启运行时间检测, mysql -> set profiling = 1;

2. 执行查询语句: 
    select name from t1
    where name='lucy99999';

3. 查看执行时间
    show profiles;

4. 在name字段创建索引
    create index name on t1(name);

5. 再次执行查询命令
    select name from t1
    where name = 'lucy100000';

6. 再次查询时间
    show profiles;

普通索引(index)

使用规则

  • 可设置多个字段, 字段值无约束
  • 把经常用来查询打字段设置为索引字段
  • KEY标志: MUL

创建普通索引
1. 创建表时创建

create table 表名(
    字段名, 数据类型
    ...
    idenx(字段名1),
    idenx(字段名2),
    ...
);
示例
    create table t2(
    id int,
    name varchar(20),
    sex enum('M', 'F'),
    age tinyint,
    index(id),
    index(name));
2. 已有表中
create index 索引名称 on 表名(字段);  
3. 查看索引
1. desc 表名; ---> key 标志为MUL  
2. show index from 表名\G;  
4. 删除普通索引
drop index 索引名 on 表名  

唯一索引(unique)

使用规则

  1. 可设置多个字段
  2. 约束:字段的值不允许重复, 但可以为NULL
  3. KEY标志 UNI

创建唯一索引:

  1. 创建表时创建

    create table 表名(
        字段 l类型,
        ...
        unique(字段),
        ...
    );
    

  2. 已有表中创建

    create unique index 索引名 on 表名(字段名);
    

  3. 查看删除同普通索引

    drop index 索引名 on 表名;
    

主键索引(primary key)

使用规则

  1. 只能有1个字段为主键字段
  2. 约束:字段值不允许重复,也不能为NULL
  3. KEY标志: PRI
  4. 通常设置记录编号字段 id, 能够唯一锁定一条记录

创建主键

  1. 创建表时

id int, primary key auto_increment,
name varchar(20) not null,
)auto_increment=10000;  (默认从10000开始添加)

或(了解)
id int,auto_increment,
name varchar(20),
...
primary key(id, name)  # 复合主键
2. 在已有表中

alter table 表名 add primary key(id);
3. 删除主键

1. 先删除自增长属性(modify)
    alter table 表名 modify id int;

2. 删除主键
    alter table 表名 drop primary key;