Mysql 表关系实战

// 从表：关联外键的表
// 主表：外键指向的表

//班级表
drop table if exists millions_class;
create table millions_class (
    class_id int primary key auto_increment,
    class_name varchar(10) not null default 'node' comment '班级名称'
) character set utf8;

// 学生表、关联班级外键
$ drop table if exists millions_student;
$ create table millions_student (
    stu_id int primary key auto_increment,
    stu_name varchar (10) not null default 'millions' comment '班级名称',
class_id int,
    foreign key (class_id) references millions_class (class_id)
) character set utf8;

// 插入班级数据
$ insert into millions_class value (null, 'node_1');
// 插入学生数据
$ insert into millions_student value (null, 'bob');

// 查看详细表结构信息
$ show create table millions_student;
// 查看表结构基本信息
$ desc millions_student;

// 设置级联操作（操作一个表影响另一个表）
// 主表更新、主表删除 On update On delete

// Set null:主表被删除或更新，从表的该外键指向null
// 修改外键，先删除外键约束（外键字段并不会被删除），再新建
$ Alter table millions_student drop foreign key millions_student_ibfk_1;
$ Alter table millions_student add foreign key (class_id) references millions_class (class_id) on delete set null;
$ delete from millions_class where class_id = 1;
$ update millions_student set class_id = 1 where stu_id;

// Cascade:主表被删除或更新，从表执行相同操作
$ Alter table millions_student drop foreign key millions_student_ibfk_1;
$ Alter table millions_student add foreign key (class_id) references millions_class (class_id) on delete cascade;

// Restrict:拒绝主表操作 
$ Alter table millions_student add foreign key (class_id) references millions_class (class_id) on delete set null on update cascade;
$ update millions_class set class_id = 2 where class_name='php';