数据库之外键

一：外键的基础知识

【1】

（1）表的对应关系：

　　（1）一对多

　　（2）多对多

　　（3）一对一

 

二：外键

（1）产生背景：

（1）在上述表中 首先表结构不清晰 不能明确指出该表是描述员工信息 还是部门信息

（2）其次上述有多个重复部门 在数据库中有多个重复数据 浪费空间

（3）上述表扩展性比较差

 

（2）解决思路:

 

如图所示：

（1）我将上述表格进行拆分出来 每个表干什么工作很清晰

（2）员工表关联上述部门的id 通过id既可以知道每个员工所属的部门

 

（3）表格之间的关系确立：

　　（1）站在员工表：多个员工能否属于同一个部门

　　　　   翻译：一个部门能否包含多个员工----->可以的

　　（2）站在部门表：多个部门属于一个员工

　　　　 翻译：一个员工能否属于多个部门------->不可以

结论：像这种单方面的多对一 我们称为一对多关系

PS：该方法只是我们认为意淫的方法 我们此时并没有真正建立关联

 

（3）foreign key外键

作用：将两个表之间建立管理

例如:

# 部门表
create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(128)
);



# 员工表
create table emp(
    id int primary key auto_increment,
    emp_name varchar(32),
    emp_gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);


insert into dep(dep_name,dep_desc) values('技术部','软件开发'),('外联部','外联社交');

insert into emp(emp_name,emp_gender,dep_id) values('小红','female',2),('小李','male',2

一对多关系

PS:

（1）在创建表关系的时候 必须先创建被关联的表

（2）写入数据也是必须先写入被关联的表

（3）因为如果没有被关联表 其关联主键的时候根本找不到该表在哪里

（5）虽然表关系建立了 但是同时给表带来了新的约束条件

（6）被关联表不能修改表的id 也不能删除表的id

 

（4）cascade ：

作用：表之间自动更新关系 例如级联更新 级联删除

例如：

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

create table emp(
    id int primary key auto_increment,
    emp_name varchar(32),
    emp_gender enum('male','female','others') default 'male', 
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade   # 动态更新
    on delete cascade   # 动态删除
);


insert into dep(dep_name,dep_desc,dep_id) values('技术部','软件开发'),('外联部','外联社交');

insert into emp(emp_name,emp_gender,dep_id) values('小红','female',2),('小李','male',1);

动态更新 动态删除

 

多对多关系：

应用场景:

 

（1）表格关系的确立：

　　（1）在书本的层面:多本书能否有一个作者

　　　　　　翻译：一个作者能否写多本书-----> 可以

　　（2）在作者层面:多个作者能不能写一本书

　　　　　　翻译:一本书能否有多个作者-----> 可以

 总结：

（1）在上述两个层面都属于可以的情况 则表格与表格之间属于多对多的情况

（2）在多对多的情况下 必须引用第三方表

（3）因为两者都需要关联对方的id 那么先创建那个表呢 需要引入第三方表 完成表格的关联

 例如:

create table book(
    id int primary key auto_increment,
    book_name varchar(16),
    book_price int
);

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

create table book2author(
    id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);


insert into book(book_name,book_price) values('西记',199),('python',199),('金梅',199);

insert into author(name,age) values('河马',73),('SR',18);

insert into book2author(book_id,author_id) values(1,1),(2,2),(1,1);

多对多关系

PS:

（1）在上述表格的创建中 如果书籍表/或者作者表删除更新不会影响对方

（2）其只会影响第三方表 也就是和其建立级联关系的表

 

 一对一关系：

应用场景:

（2）表格关系确立:
　　（1）一个人能不能有多个详细信息 ----> 不可以

　　（2）详细信息能不能对应多个人 ----> 不可以

PS：当表格之间的关系都是不可以的情况

（1）要么两者之间没有一点关系

（2）要么就是一对一的关系

例如：

create table information(
    id int primary key auto_increment,
    age int,
    addr varchar(32)
);


create table person(
    id int primary key auto_increment,
    name varchar(32),
    gender varchar(32),
    information_id int unique,
    foreign key(information_id) references information(id)
    on update cascade
    on delete cascade
);


insert into information(age,addr) values(18,'安徽'),(73,'动物园'),(84,'上海');


insert into person(name,gender,information_id) values('SR','男',1),('河马','其它',2),('憨子','女',3);

一对一关系

 

三：修改查询表方式

1.修改表的完整语句
            1. 修改表名  
                  ALTER TABLE 表名 
                                      RENAME 新表名;
            2. 增加字段
                  ALTER TABLE 表名
                                      ADD 字段名  数据类型 [完整性约束条件…],
                                      ADD 字段名  数据类型 [完整性约束条件…];
                  ALTER TABLE 表名
                                      ADD 字段名  数据类型 [完整性约束条件…]  FIRST;  # 直接移到最前面
                  ALTER TABLE 表名 
                                      ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;  # 寻找插哪个字段的后面                    
            3. 删除字段
                  ALTER TABLE 表名 
                                      DROP 字段名;
            4. 修改字段  # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
                  ALTER TABLE 表名 
                                      MODIFY  字段名 数据类型 [完整性约束条件…];
                  ALTER TABLE 表名 
                                      CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
    
    
    
    # 查询语句执行的结果也是一张表，可以看成虚拟表

    # 复制表结构＋记录 （key不会复制: 主键、外键和索引）
    create table new_service select * from service;

    # 只复制表结构
    select * from service where 1=2;        //条件为假，查不到任何记录

    create table new1_service select * from service where 1=2;  

    create table t4 like employees;

修改查询方式

 

转载于:https://www.cnblogs.com/SR-Program/p/11384227.html