表之间的关系

旧城等待， 2021-10-30 08:06 527阅读 0赞

# 表之间的关系 #

# 定义一张部门员工表
    emp             
    id  name    gander  dep_name    dep_desc
    1   ming    male    教学部     教书
    2   lilei   male    教学部     教书
    3   hammm   female  销售部     销售工作
    4   hong    female  技术部     技术能力
    '''
    把所有数据存放到一张表中的弊端:
    1.组织结构不清晰(重点是人还是部门?)
    2.浪费银盘空间(重复的内容多)
    3.扩展性极差(改动部门名要改动多行数据)
    '''
    # 上述代码的弊端类似把代码全部写到一个py文件中,所以应该>>>解耦合 # 1.将表中拆成两张表,一张员工表,一张部门表 # 2.分析两张表中的数据之间关系:(一对多,多对多,一对一) # 1.站在员工表的角度:多个员工属于一个部门 # 2.站在部门表的角度:多个部门不能对应一个员工 # 3.然后将拆分后的两张表建立联系

拆分后的两张表:
    emp         
    id  name    gander  dep_id
    1   ming    male    1
    2   lilei   male    1
    3   hammm   female  2
    4   hong    female  3
    
    dep     
    id  dep_name    dep_desc
    1   教学部     教书
    2   销售部     销售工作
    3   技术部     技术能力

# 外键 foreign key #

# 上述建立关系后的两张表中,员工表中的dep_id我可以随意改动,所以应该有一种限制,限制dep_id字段必须只能是部门表已有的id字段才合理
    
    ''' foreign key 1.外键是绑定两张表关系的关键字,外键要建在主关联表中 2.先创建被关联表,才能创建主关联表 3.在插入记录时,必须先插被关联的记录,才能插主关联表 4.删除记录时,先删除主关联表中的记录,当主关联表中的记录与被关联表中的记录没有联系时,被关联表中的该条记录才能删除 '''

## 级联更新级联删除 on update cascade ,on delete cascade ##

# 受限于外键约束,导致操作数据变得非常复杂,能否有一种简单的方式,让我们不需要考虑在操作目标表的时候还需要考虑关联表的情况,比如直接删除被关联表中的部门,对应在主关联表中的该部门的员工都被删除.
    ''' 级联更新:on update cascade 更新部门后,对应部门里的员工表中的标识部门字段同步更新 级联删除:on delete cascade 删除部门后,对应该部门的员工表记录对应删除 ''''

# 一对多 #

# 在一对多的表关联中,'多'为主关联表,'一'为被关联表
    '''
    站在两张表的角度:
    1.站在员工表:一名员工是否可以属于多个部门 不可以
    2.站在部门表:一个部门是否可以有多名员工 可以
    双方只有一方是多对一的关系就是一对多
    '''

# 代码实现创建关联表:
    create table dep(
        id int primary key auto_increment,
        dep_name varchar(64),
        dep_desc varchar(64)
    );
    
    create table emp(
        id int primary key auto_increment,
        name varchar(64),
        gender enum('male','female','other') default 'male',
        dep_id int,
        foreign key(dep_id) references dep(id)
    );
    
    
    # 插入记录
    insert into dep(dep_name,dep_desc) values
    ('教学部','教书育人'),
    ('销售部','招学生'),
    ('技术部','技术攻关');
    insert into emp(name,gender,dep_id) values
    ('小明','male',1),
    ('李雷','male',1),
    ('韩梅梅','female',2),
    ('小红','female',3);

# ![1726514-20190820184121450-142895219.png][] #

# 多对多 #

# 在多对多关联表中,外键建在第三张表中
    # 图书表与作者表之间的关系
    ''' 站在两张表的角度: 1.站在图书表:一本书是否可以有多个作者 可以 2.站在作者表:一个作者是否可以写多本书 可以 双方都能一条数据对应多条数据买这种关系就是多对多 '''
    # 创建多对多关系表的方法
    ''' 1.设计第三张表,作为主关联表,此表中要有一个字段是图书id的外键,和一个字段是作者id的外键 2.先创建图书和作者这两种被关联表 3.再创建主关联表 '''

# 创建表
    create table book(
        id int primary key auto_increment,
        name varchar(64),
        price int
    );
    create table author(
        id int primary key auto_increment,
        name varchar(64),
        gender enum('male','female','other')
    );
    create table book2author(
        id int primary key auto_increment,
        book_id int,
        foreign key(book_id) references book(id)
        on update cascade
        on delete cascade
        author_id int,
        foreign key(author) references author(id)
        on update cascade
        on delete cascade
    );
    # 插入数据
    insert into book(name,price) values
    ('斗破苍穹','90'),
    ('python入门','178'),
    ('MySQL基础','169'),
    ('前端速成','99');
    insert into author(name,gender) values
    ('土豆','male'),
    ('韩梅梅','female'),
    ('李雷','male');
    insert into book2author(book_id,author_id) values
    (1,1),
    (2,2),
    (2,3),
    (3,3),
    (4,2);

# 一对一 #

客户表和学生表(报名之前数客户,报名之后是学生)

# 客户表记录唯一对应学生表中的一条记录,反之也一样
    ''' 站在两张表的角度 1.站在客户表:客户的一条记录对应学生的一条记录 2.站在学生表:学生的一条记录对应客户的一条记录 是一对一关联表 '''

# 创建一对一关联表 外键放在被频繁查询的表中并且外键为单列唯一
    create table customer(
        id int primary key auto_increment,
        name varchar(64) not null,
        qq int not null,
        phone int not null
    );
    create into student(
        id int primary key auto_increment,
        name varchar(64) not null,
        customer_id int unique,  # 在一对一关联表中 此字段为唯一的
        foreign key(customer_id) references customer(id)
        on update cascade
        on delete cascade
    );
    # 插入记录
    insert into customer(name,qq,phone) values
    ('李雷','123456789','15555555555'),
    ('韩梅梅','987654321','16666666666'),
    ('小明','111222333','11111111111');
    insert into student(name,customer_id) values
    ('李雷',1),
    ('韩梅梅',2);

# 修改表 #

# MySQL 对大小写不敏感
    语法:
    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 旧字段名 新字段名 数据类型[约束条件];

# 复制表 #

# 查询语句执行结果也是一张表,可以看成虚拟表
    # 复制表结构+记录 (主键,外键,索引不会被复制)
    create table 新表名 select * from 被复制的表名;
    
    # 只复制结构
    select * from 表名 where 1=2;
    create table 新表名 select * from 被复制的表名 where 1=2;
    create table 表名 like employees;

转载于:https://www.cnblogs.com/waller/p/11384836.html

[1726514-20190820184121450-142895219.png]: /images/20211029/17ba3d5e3bfb46209af13390e06e9783.png