mysql数据库表格之间的关系
2024-08-23 09:16:42
外键
昨日内容回顾:
字段类型
约束条件
创建表的完整语法
create table 表名(
字段名 字段类型[(宽度) 约束条件],
字段名 字段类型[(宽度) 约束条件],
字段名 字段类型[(宽度) 约束条件],
字段名 字段类型[(宽度) 约束条件]
);
# 需要注意的三点:
1.同一张表内，字段名不能重复
2.字段名和字段类型是必须的，宽度和约束条件是可选的
3.最后一个字段后不能加逗号！
宽度:对存储数据的限制
字段类型与约束条件
字段类型:约束改字段存储的数据的类型
约束条件:基于字段类型之上附加的额外的限制条件
整型:
tinyint smallint int bigint
默认都是有正负号的
int(8)
唯独整型字段宽度限制的不是存储宽度，而是显示宽度
id int(8) zerofill
浮点型
float
double
decimal
能够存储的范围
float(255,30)
double(255,30)
decimal(65,30)
相同点都能存小数
但是精度从上往下一次增加
float
double
decimal
补充:可以用字符串来存整型，浮点型相关的数据，保证精确
字符类型
char：定长
varchar：变长
都是存字符串
char(4)：如果超出了4位，直接报错，如果不超过4位，也按4位存储，默认空格填充
varchar(4)：如果超出了4位，直接报错，如果不超过4位，按实际长度存储
char Vs varchar
char
优点:存取速度都快，存取简单暴力，都是固定存取
缺点:浪费硬盘空间
varchar
报头是必须的，用来描述后面存储数据的真实长度
优点：节省空间
缺点:存取速度都慢
sfhajshafjkssfsajfkljsldafjlsajdfjkasklfjlksjafkjsakldfj
日期类型
date 2019-05-14
datetime 2019-05-14 11:11:11
time 11:11:11
year 2019
枚举与集合
枚举:多选一
例如获取用户的性别
gender enum('male','female','others')
集合:多选多但是也可以就选一个
hobby set('haige','ftbd','xcsmt','DJ')
约束条件
not null
default
primary key 主键
限制条件上等价于 not null + unique
auto_increment 只能加载被设置为key的字段上
# 注意以后创建主键的时候。固定写法
id int primary key auto_increment
insert into t1(name,password) values('egon','123');
primary key(name,password)
unique
单列唯一
联合唯一:服务器的ip+port
host int,
port int,
unique(host,port)
delete from
truncate
表与表之间建关系
前戏之一对多关系
# 定义一张部门员工表
id name gender dep_name dep_desc
1 jason male 教学部 教书育人
2 egon male 外交部 漂泊游荡
3 tank male 教学部 教书育人
4 kevin male 教学部 教书育人
5 owen female 技术部 技术能力有限部门
"""
把所有数据都存放于一张表的弊端
1.组织结构不清晰
2.浪费硬盘空间
3.扩展性极差
"""
# 上述的弊端产生原因类似于把代码全部写在一个py文件中，你应该怎么做？>>>解耦合！将上述一张表拆成员工和部门两张表！
# 类似的表关系学生与班级，也是如此，一张学生表和一张班级表
# 分析表数据之间的关系：多个用户对应一个部门，一个部门对应多个用户。禁止一个用户对应多个部门这种情况是另外一张表关系
# 如何查找表与表之间的关系
"""
老师与课程表
1.站在老师表的角度：一名老师能否教授多门课程(限制死，不能，一名老师只能教python，不能同时教python和linux)
2.站在课程表的角度：一门课程能否可以被多个老师教,完全可以！
那就是课程表多对一老师表，如何表示这种关系？在课程表中创建一个字段(tea_id)指向老师表的id字段
学生与班级表
1.站在学生表的角度：？？？
2.站在班级表的角度：？？？
那就是学生表多对一班级表，如何表示这种关系？在学生表中创建一个字段(class_id)指向班级表的id字段
"""
# 再回过头来看员工与部门表，我员工表里面的dep_id我可以随意更改，但是应该有一个强制限制，限制dep_id字段必须只是部门表已有的id字段才合理
一对多(Foreign Key)
# foreign key会带来什么样的效果？
# 1、在创建表时，先建被关联的表dep，才能建关联表emp
create table dep(
id int primary key auto_increment,
dep_name char(10),
dep_comment char(60)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female') not null default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
);
# 2、在插入记录时，必须先插被关联的表dep，才能插关联表emp
insert into dep(dep_name,dep_comment) values
('sb教学部','sb辅导学生学习，教授python课程'),
('外交部','老男孩上海校区驻张江形象大使'),
('nb技术部','nb技术能力有限部门');
insert into emp(name,gender,dep_id) values
('alex','male',1),
('egon','male',2),
('lxx','male',1),
('wxx','male',1),
('wenzhou','female',3);
# 当我想修改emp里的dep_id或dep里面的id时返现都无法成功
# 当我想删除dep表的教学部的时候，也无法删除
# 方式1:先删除教学部对应的所有的员工，再删除教学部
# 方式2:受限于外键约束，导致操作数据变得非常复杂，能否有一张简单的方式，让我不需要考虑在操作目标表的时候还需要考虑关联表的情况，比如我删除部门，那么这个部门对应的员工就应该跟着立即清空
# 先把之前创建的表删除，先删员工表，再删部门表，最后按章下面的方式重新创建表关系
# 3.更新于删除都需要考虑到关联与被关联的关系>>>同步更新与同步删除
create table dep(
id int primary key auto_increment,
dep_name char(10),
dep_comment char(60)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female') not null default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade
on delete cascade
);
insert into dep(dep_name,dep_comment) values
('sb教学部','sb辅导学生学习，教授python课程'),
('外交部','老男孩上海校区驻张江形象大使'),
('nb技术部','nb技术能力有限部门');
insert into emp(name,gender,dep_id) values
('alex','male',1),
('egon','male',2),
('lxx','male',1),
('wxx','male',1),
('wenzhou','female',3);
# 删除部门后，对应的部门里面的员工表数据对应删除
# 更新部门后，对应员工表中的标示部门的字段同步更新
多对多
# 图书表与作者表之间的关系
"""
仍然站在两张表的角度：
1.站在图书表：一本书可不可以有多个作者，可以！那就是书多对一作者
2.站在作者表：一个作者可不可以写多本书，可以！那就是作者多对一书
双方都能一条数据对应对方多条记录，这种关系就是多对多！
"""
# 先来想如何创建表？图书表需要有一个外键关联作者，作者也需要有一个外键字段关联图书。问题来了，先创建谁都不合适！如何解决？
# 建立第三张表，该表中有一个字段fk左表的id，还有一个字段是fk右表的id
create table author(
id int primary key auto_increment,
name char(16)
);
create table book(
id int primary key auto_increment,
bname char(16),
price int
);
insert into author(name) values
('egon'),
('alex'),
('wxx')
;
insert into book(bname,price) values
('python从入门到入土',200),
('葵花宝典切割到精通',800),
('九阴真经',500),
('九阳神功',100)
;
create table author2book(
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
);
insert into author2book(author_id,book_id) values
(1,3),
(1,4),
(2,2),
(2,4),
(3,1),
(3,2),
(3,3),
(3,4);
一对一
客户表和学生表(老男孩的客户与学生之间,报名之前都是客户，只有报了名的才能是学生)
# 左表的一条记录唯一对应右表的一条记录，反之也一样
create table customer(
id int primary key auto_increment,
name char(20) not null,
qq char(10) not null,
phone char(16) not null
);
create table student(
id int primary key auto_increment,
class_name char(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);
# 三种外键关系都是用foreign key，区别在于如何使用以及其他条件限制即可做出三种关系
修改表
# 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 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
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;
作业布置
练习：账号信息表，用户组，主机表，主机组
#用户表
create table user(
id int not null unique auto_increment,
username varchar(20) not null,
password varchar(50) not null,
primary key(username,password)
);
#用户组表
create table usergroup(
id int primary key auto_increment,
groupname varchar(20) not null unique
);
#主机表
create table host(
id int primary key auto_increment,
ip char(15) not null unique default '127.0.0.1'
);
#业务线表
create table business(
id int primary key auto_increment,
business varchar(20) not null unique
);
#建关系：user与usergroup
create table user2usergroup(
id int not null unique auto_increment,
user_id int not null,
group_id int not null,
primary key(user_id,group_id),
foreign key(user_id) references user(id),
foreign key(group_id) references usergroup(id)
);
#建关系：host与business
create table host2business(
id int not null unique auto_increment,
host_id int not null,
business_id int not null,
primary key(host_id,business_id),
foreign key(host_id) references host(id),
foreign key(business_id) references business(id)
);
#建关系：user与host
create table user2host(
id int not null unique auto_increment,
user_id int not null,
host_id int not null,
primary key(user_id,host_id),
foreign key(user_id) references user(id),
foreign key(host_id) references host(id)
);
练习:
# 班级表
cid caption
# 学生表
sid sname gender class_id
# 老师表
tid tname
# 课程表
cid cname teacher_id
# 成绩表
sid student_id course_id number
补充：
将所有的数据都放在一张表内产生的弊端:
1.表的组织结构不清晰
2.浪费存储空间
3.可扩展性极差(修改某一个部门的信息的时候~)
类似于你把所有的代码都写到一个py文件中，你是怎么优化的？
>>>解耦拆分
如何查找表与表之间的关系
以员工和部门表为例。查找表关系需要做到换位思考(站在两边去找表关系)
先站在员工表:
找员工表的多条数据能否对应部门表的一条数据
翻译:
多个员工能否属于一个部门
可以！之后不能直接下结论，还需要站在部门表的角度再确认关系
再站在部门表:
找部门表的多条数据能否对应员工表的一条数据
翻译:
多个部门能否有同一个员工
不可以！
只有站在两边表的角度都分析过了，才能够下结论
员工表单向多对一部门表
外键(foreign key)
1.必选要先建被关联表
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(64)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female','others') not null default 'male', # default后面的默认值空格直接书写即可
dep_id int,
foreign key(dep_id) references dep(id)
);
2.插入数据
2.新增数据的时候，要先增被关联表中的数据
insert into dep(dep_name,dep_desc) values
('外交部','形象代言人'),
('教学部','教书育人'),
('技术部','技术能力有限部门');
insert into emp(name,gender,dep_id) values
('jason','male',1),
('egon','male',2),
('kevin','male',2),
('tank','male',2),
('jerry','female',3);
3.修改emp表中的dep_id字段
update emp set dep_id=100 where id=1;
update dep set id=100 where id=1;
delete from dep where id=2;
delete from emp where id>1 and id<5;
delete from dep where id=2;
# 给外键字段新增功能 同步更新同步删除(级联删除级联更新)
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(64)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female','others') not null default 'male', # default后面的默认值空格直接书写即可
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
insert into dep(dep_name,dep_desc) values
('外交部','形象代言人'),
('教学部','教书育人'),
('技术部','技术能力有限部门');
insert into emp(name,gender,dep_id) values
('jason','male',1),
('egon','male',2),
('kevin','male',2),
('tank','male',2),
('jerry','female',3);
update dep set id=100 where id=2;
delete from dep where id=100;
多对多
图书与作者
create table book(
id int primary key auto_increment,
title char(16),
price int,
author_id int,
foreign key(author_id) references author(id)
);
create table author(
id int primary key auto_increment,
name char(16),
gender char(16),
book_id int,
foreign key(book_id) references book(id)
);
create table book(
id int primary key auto_increment,
title char(16),
price int
);
create table author(
id int primary key auto_increment,
name char(16),
gender char(16)
);
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(title,price) values
('小王子','69.96'),
('围城','99.99'),
('python全栈开发','21000');
insert into author(name,gender) values
('jason','male'),
('egon','female'),
('kevin','male');
insert into book2author(book_id,author_id) values
(1,1),
(1,2),
(1,3),
(2,1),
(2,3),
(3,1),
(3,2);
一对一
user
name password
userdetail
gender phone addr hobby ...
create table customer(
id int primary key auto_increment,
name char(20) not null,
qq char(10) not null,
phone char(16) not null
);
create table student(
id int primary key auto_increment,
class_name char(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);
# 三种外键关系都是用foreign key，区别在于如何使用以及其他条件限制即可做出三种关系
书籍和作者
一本书可不可以被多个作者写 可以
一个作者可不可以写多本书 可以
多对多关系
书籍和出版社
一本书可不可以被多个出版社出版 不可以
一个出版社可不可以出版多本书 可以
一对多关系
作者与作者详情表
两个不可以就是
一对一
在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 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
最新文章
- IOS 网络请求
- idea 使用
- [游戏模版8] Win32 透明贴图
- IMP-00038:无法转换为环境字符集句柄
- [设计模式]<<设计模式之禅>>模板方法模式
- oracle10G之前介质下载地址【珍藏版】
- 【转】 教你如何创建类似QQ的android弹出菜单
- POJ 2728 Desert King 最优比率生成树
- HDU-1754-I Hate It（线段树，简单，不过好像有点问题）
- PILLOW图片中加入中文 曲线救国Opencv
- [Swift]LeetCode732. 我的日程安排表 III | My Calendar III
- 【原创】Linux基础之用户和组
- 作业-haproxy配置文件的增删查（有一个bug不知道咋改）
- Win10连接远程桌面的时候提示您的凭证不工作该怎么办？
- Java占位符
- 【转】Web前端性能优化——如何提高页面加载速度
- C#中关于@的用法
- C#微信小程序服务端获取用户解密信息
- navicate连接Linux下mysql慢，卡，以及mysql相关查询，授权
- Kafka学习整理五(Consumer配置)