MySQ-表关系-外键-修改表结构-复制表-03

目录

前言

不合理的表结构(案例)

先来看下面这张表

emp_info 员工信息表

id

name

gender

dep_name

dep_desc

1

jason

male

教学部

教书育人

2

egon

male

外交部

漂泊流浪

3

tank

male

教学部

教书育人

4

kevin

male

技术部

技术开发

所有的信息都记录在一张表中了

带来的问题

  • 表的结构不清晰
  • 浪费硬盘空间,数据冗余较多
  • 表的扩展性、可维护性极差(无法忽视的缺点)

如何解决问题?

拆表,确定表关系,建立表关联

确定表与表之间的关系,一定要换位思考(必须两方都考虑周全之后才能得出结论)

以员工表和部门表为例:
    先站在员工表看能否有多个员工对应一个部门
    翻译过来:
        一个部门能付否有多个员工
        可以!!!(暂时只能确定员工单向多对一部门)
        再站在部门表看能否有多个员工一起对应一个部门
    翻译过来
    ....乱了

如何确定表关系?

查找表关系,一定要分别站在两张表的角度全部考虑完毕才能下结论,否则无法得出正确答案

表关系

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

或者两张表没有关系

一对多

单向的多对一就是“一对多”的外键关系

无论是一对多还是多对一都是一对多的关系,都叫一对多,没有多对一的关系

多对多

如果两边都是单向的多对一关系,那么两者就是多对多的关系

分析

多对多的关系,必须额外创建第三张表,用来专门记录两种表之间的关联关系

  • 如果按一对一的思路来,两张表都必须关联对方,都必须让对方先建立,那就建不了表了,外键存的是关联关系,那就单独开一张表,存关联关系

案例建立

同步更新删除是在外键上的

一对一

应用场景

  • 表特别庞大时拆表优化性能(用户详细信息与展示信息)
  • 客户与学生(客户可能成为学生,学生一定是客户)

如果双向的一对多都不成立,那么两张表之间只有两种情况了

  1. 一对一的关系
  2. 根本没有关系

外键字段必须用foreign key + unique 来限制,唯一且必须出现

判断表关系最简单的语法

三种关系常见案例

一对多

有一个可以就是一对多的关系(有且只有一个

图书与出版社

一本书可不可以有多个出版社?不可以

一个出版社可不可以初版多本书,可以!!!

一对多的关系

多对多

有两个可以就是多对多的关系

图书与作者表

一本书可不可以有多个作者 可以!!

一个作者可不可以写多本书 可以!!

多对多的关系

一对一 或 没有关系

两个都不可以,要么是一对一的关系,要么没有关系

作者与作者详情

一个作者可不可以有多个详情 不可以!!

一个作者详情可不可以有多个作者 不可以!!!

一对一的关系

如何建立表关系?

外键 foreign key

在MySQL中通过外键来建立表与表之间的硬性关系

通常将关系字段称之为外键字段

确定外键字段归属方

  • 一对多的外键字段,应该建在“多”的那一方
  • 多对多的外键字段建在额外的第三张表
  • 一对一的外键字段建在任意一方都行,但推荐建在查询频率较高的一方(外键字段必须保证唯一性)

有外键关系的注意点

  • 创建表的时候,必须先创建被关联表
  • 插入数据的时候也应该先插入被关联数据
  • 级联更新,级联删除

注意外键的逗号,(逗号代表一个字段的结束)(还是不要忘了建表的字段定义那块最后一个语句不要加 , 逗号)

外键虽然能够帮你强制建立表关系,但也会给两行表之间增加数据相关的约束

修改表

mysql 对大小写不敏感 一定要注意是英文符号

修改表名

ALTER TABLE 表名 RENAME 新表名;
alter table 表名 rename 新表名;

增加字段

ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名  数据类型 [完整性约束条件…];
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST;  # 直接移到最前面
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;  # 选择要插在哪个字段后面   

删除字段

ALTER TABLE 表名 DROP 字段名;

修改字段

# modify只能改字段数据类型完整约束,不能改字段名,但是change可以!
ALTER TABLE 表名 MODIFY  字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

复制表

复制表结构+记录

key不会复制:主键、外键和索引

# 查询语句执行的结果也是一张表,可以看成虚拟表

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

like 可以吗? 试试

利用条件实现仅复制表结构

条件为假,查不出数据

select * from service where 1=2;        //条件为假,查不到任何记录

# 只复制表结构
create table new1_service select * from service where 1=2;  
create table t4 like employees;

今日数据库操作语句

创建数据库

mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

mysql>
mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| just_test          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

拆分员工与部门信息成两张表

一个员工属于一个部门

一个部门有多个员工

---> 一对多

在员工那边保留部门编号

创建表(员工与部门信息表)

create table dep(
    id int primary key auto_increment,
    dep_name varchar(30),
    dep_comment varchar(60)
);

create table emp(
    id int primary key auto_increment,
    name varchar(50),
    gender enum('male', 'famale', 'others') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);

insert into dep (dep_name, dep_comment) values('研发部', '敲代码的那一群人'), ('教育部', '成天这啊那啊找麻烦的那群人'), ('后勤保障部', '搬水的关门的收拾卫生的');

insert into emp (name, dep_id) values('jason', 1), ('egon', 2), ('agong', '3');


alter table emp modify foreign key(dep_id) refrences dep(id)
on update cacade
on delete cacade;

select emp.id,name,gender,dep.dep_name,dep.dep_comment from emp left join dep on dep.id=emp.id;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券