表的设计（约束、外键）

1.表的设计

1.1 约束

约束：保证数据的完整性与一致性

1. not null 与 default

create table student(
  id int not null,
  name varchar(20) not null,  # 不能为空
  age int(3) unsigned not null default 18  # 不能为空，当为空时，默认为18
);

2. unique
   2.1 单列唯一

create table department(
    id int,
    name char(10) unique
);

2.2 多列唯一

create table department(
    id int,
    name char(10) ,
    unique(id),
    unique(name)
);

2.3 组合唯一
联合唯一，只要两列记录，有一列不同，既符合联合唯一的约束

create table services(
  id int,
  ip char(15),
  port int,
  unique(ip, port)
);

3. primary key
   primart key 有 not null and unique的性质
   没有设置

create t1(
  id int unsigned auto_increment primary key,
  name char(10)
);

3.1 单列主键

在MySQL的一个表中只有唯一的一个主键，不能有多列主键，但可以有复合主键
一个表中可以：
单列做主键
多列做主键（复合主键）
约束：等价于 not null unique,字段的值不为空且唯一
存储引擎默认是（innodb）:对于innodb存储引擎来说，一张表必须有一个主键。

3.2 复合主键

4. auto_increment

约束:约束的字段为自动增长，约束的字段必须同时被key约束
对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长

对于自增字段，用delete删除：
delete from t1;如果有自增id，新增的数据仍然是以删除前的记录作为起始。
对于自增字段，用truncate清楚表：
truncata table t1;数据量大，删除的速度比delete快。且id从0开始

5. foreign key
   建立两张表之间的联系

1.2 外键

1. 先创建被关联表
2. 再创建关联表

• 注意：在关联表中加入
  on delete cascade #同步删除
  on update cascade #同步更新

1.3 外键的变种

• 多对一or一对多
• 多对多

• 一对一
  条件：
  1.先站在左表的角度，左表的多条记录对应右表的一条记录
  2.先站在右表的角度，右表的多条记录对应左表的一条记录
  多对一or一对多：条件1 and 条件2 有一个成立
  多对多：条件1 and 条件2 都成立。通过建立第三张表，来建立多对多的关系
  一对一： 条件1 and 条件2 都不成立，给一个表的foreign key字段设置约束unique
  eg:
  请创建如下表，并创建相关约束

  
  eg.png

1. 创建班级表

create table class(
    cid int primary key auto_increment,
    caption varchar(20) not null
);

# 插入值
insert into class(caption) values('三年二班'),('一年三班'),('三年一班');

2. 创建老师表

create table teacher(
    tid int primary key auto_increment,
    tname varchar(20) not null
);

# 插入值
insert into teacher(tname) values('李sir'),('王sir'),('吴sir');

3. 创建学生表  （关联班级表）

create table student(
    sid int primary key auto_increment,
    sname varchar(20) not null,
    gender enum('男', '女'),
    class_cid int,

    #  关联班级表
    constraint fk_student_class foreign key(class_cid) references class(cid)
    on delete cascade
    on update cascade
);

# 插入值
insert into student(sname, gender, class_cid) values ('张三', '女', 1),('李四', '女', 1),('王五', '男', 2);

4. 创建课程表 (关联老师表)
create table course(
    cid int primary key auto_increment,
    cname varchar(20) not null,
    teacher_id int,

    # 关联老师表
    constraint fk_course_teacher foreign key(teacher_id) references teacher(tid)
    on delete cascade
    on update cascade
);

# 插入值
insert into course(cname, teacher_id) values ('生物', 1),('体育', 1),('物理', 2);

5. 创建成绩表（关联学生表 and 课程表）
create table score(
    sid int primary key auto_increment,
    student_id int,
    course_id int,
    number int not null,
    
    # 关联学生表 and 老师表
    constraint fk_score_student foreign key(student_id) references student(sid)
    on delete cascade
    on update cascade,
    constraint fk_score_course foreign key(course_id) references course(cid)
    on delete cascade
    on update cascade
);

# 插入值
insert into score(student_id, course_id, number) values (1, 1, 60), (1, 2, 59), (2, 2, 100);