MySQL之外键以及操作表的SQL语句补充

外键

　　其实就是用来标识表与表之间的数据关系，简单的理解为该字段可以让你去到其他表中查找数据

表与表之间建关系

　　1、表关系分类

　　　　一对一

　　　　多对多

　　　　一对多

　　　　没有关系

　　　　判断表关系的方式:换位思考

　　2、一对多

　　　　 针对一对多的表关系 外键字段建在多的一方

　　　　foreign key(dep_id) references dep(id)

create table emp(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id)
);

create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(254)
);

　　小技巧：使用SQL语句建立真正意义上的表关系 可以先创建不含外键字段的基本表，之后再添加外键字段

　　3、多对多

　　　　多对多表关系 需要单独开设第三张表存储(并且第三张表可以不绑定)

　　　　关键字

foreign key(author_id) references author(id)
on update cascade  # 级联更新
on delete cascade,  # 级联删除

　　　　例子

create table book(
    id int primary key auto_increment,
    title varchar(32),
    price float(6,2)
);

create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int
);

create table book2author(
    id int primary key auto_increment,
    author_id int,
    book_id int,
    foreign key(author_id) references author(id)
    on update cascade  # 级联更新
    on delete cascade,  # 级联删除
    foreign key(book_id) references book(id)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);

　　4、一对一

　　　　外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中

create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    author_id int unique,
    foreign key(author_id) references author_detail(id)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);

create table author_detail(
    id int primary key auto_increment,
    phone varchar(32),
    address varchar(32)
);

外键约束

　　1、在创建表的时候 需要先创建被关联表(没有外键字段的表)

　　2、在插入新数据的时候 应该先确保被关联表中有数据

　　3、在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据

　　4、在修改和删除被关联表中的数据的时候 无法直接操作

　　　　如果想要数据之间自动修改和删除需要添加额外的配置

    create table emp1(
        id int primary key auto_increment,
        name varchar(32),
        age int,
        dep_id int,
        foreign key(dep_id) references dep1(id) 
         on update cascade  # 级联更新
         on delete cascade  # 级联删除
    );
    create table dep1(
        id int primary key auto_increment,
        dep_name varchar(32),
        dep_desc varchar(254)
    );

操作表的SQL语句补充

　　1、修改表名  

ALTER TABLE 表名 
                          RENAME 新表名;

　　2、增加字段

ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名; 

　　3、删除字段

ALTER TABLE 表名 
                    DROP 字段名;

　　4、修改字段  # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！

      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];