外键(foreign key)
外键就是用来帮助我们建立表与表之间关系的
"""
定义一张员工表 表中有很多字段
id name gender dep_name dep_desc
"""
# 1 该表的组织结构不是很清晰(可忽视)
# 2 浪费硬盘空间(可忽视)
# 3 数据的扩展性极差(无法忽视的)
# 如何优化？
"""上述问题就类似于你将所有的代码都写在了一个py文件中"""
将员工表拆分 员工表和部门表
具体如下图所示：
表关系
表与表之间最多只有四种关系：
一对多关系
多对多关系
一对一关系
在确定表与表之间关系的时候，一定要换位思考，分别站在两张表的角度考虑
1、一对多关系
以员工表和部门表为例：
先站在员工表角度：
思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)，不能！
再站在部门表角度：
思考一个部门能否对应多个员工(一个部门数据对应多条员工数据)，能！
得出结论：
员工表与部门表表示单向的一对多
所以表关系就是一对多
建立外键字段注意：
1）一对多表关系时，外键字段建在多的一方
2）创建表的时候，一定要先建被关联表
3）在录入数据的时候，也必须先录入被关联表
SQL语句建立表关系：
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female','others') default 'male',
foreign key(dep_id) references dep(id)
);
insert into dep(dep_name, dep_desc) values('教学部','教书育人'),('外交部','外交'),('技术部','技术能力有限部门');
insert into emp(name, dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);
当我们需要修改或者删除表中数据时：
#1、修改dep表里面的id字段
update dep set id=200 where id=2; #报错
#2、删除dep表里面的数据
delete from dep; #报错
发现：修改和删除都不可以，原因是两张表有了关联，修改部门表会影响员工表，解决方法：
1、先删除教学部对应的员工数据，之后再删除部门(操作太过繁琐)
2、真正做到数据之间关联同步
更新就同步更新----->级联更新
删除就同步删除----->级联删除
create table dep(
id int primay key auto_increment,
dep_name char(16),
dep_desc char(32)
);
create table emp(
id int pirmary key auto_increment,
name char(16);
gender enum('male','female','others') default 'male',
dep_id int,
foregin key(dep_id) references dep(id),
on update cascade #级联更新
on delete cascade #级联删除
);
insert into dep(dep_name,dep_desc) values('教学部','教书育人'),('外交部','外交互动'),('技术部','技术有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);
2、多对多关系
生活中也会存在多对多的关系，如下图所示：
#图书表与作者表
create table book(
id int primary key auto_increment,
title varchar(32),
price int,
author_id int,
foreign key(author_id) references author(id)
on update cascade #级联更新
on delete cascade #级联删除
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
book_id int,
foreign key(book_id) references book(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
#这是错误的示范
针对多对多表关系，不能在原有的表中创建外键
需要单独开设一个关系表，专门用来存储两张表数据之间的关系
正确的表创建：
create table book(
id int primary key auto_increment,
title varchar(32),
price int
);
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,
foregin key(author_id) references author(id)
on update cascade #级联更新
on delete cascade, #级联删除
foregin key(book_id) references book(id)
on update cascade #级联更新
on delete cascade #级联删除
);
3、一对一关系
如果一个表字段特别的多的话，但是每次查询又不是所有的字段都用得到
如：id name age addr phone hobby email...
此时可以将表一分为二，如将用户表分成用户表和用户详情表
用户表：
用户表
id name age
用户详情表
id addr phone hooby email..
分析：
站在用户表角度：
一个用户能否对应多个用户详情 不能！
站在详情表角度：
一个详情能否对应多个个用户 不能！
得出结论：
单向的一对多都不成立，那么此时两者之间的表关系为一对一或者没有关系
一对一表关系，外键字段建在任意一方都可以，但是推荐建在查询频率比较高的一方
create table authordetail(
id int primary key auto_increment,
phone int,
addr varchar(64)
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
authordetail_id int unique, #一对一时，外键字段必须唯一
foregin key(authordetail_id) references authordetail(id)
on update cascade
on delete cascade
);
总结
表关系的建立需要用到foreign key
一对多
外键字段建在多的一方
多对多
自己开设第三张存储
一对一
建在任意一方都可以 但是推荐你建在查询频率较高的表中
修改表(了解)
#MySQL对大小写是不敏感的
1、修改表名
alter table 表名 rename 新表名;
2、增加字段
alter table 表名 add 字段名 字段类型(宽度) 约束条件;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名;
3、删除字段
alter table 表名 drop 字段名;
4、修改字段
alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
复制表(了解)
我们 sql 语句查询的结果其实也是一张虚拟的表
具体如下图所示：
复制表操作
create table 表名 select * from 旧表名; #此复制只能复制表结构和数据，并不能复制主键、外键...
#复制符合条件的记录生成新表
create table new_dep2 select * from dep where id >3;
练习一：账号信息表，用户组，主机表，主机组
#用户表
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
create table class(
cid int primary key auto_increment,
caption varchar(20) not null unique
);
# 学生表
sid sname gender class_id
create table student(
sid int primary key auto_increment,
sname char(11) not null,
gender enum('male','female','others') default 'male',
class_id int not null unique,
foreign key(class_id) references class(cid)
);
# 老师表
tid tname
create table teacher(
tid int primary key auto_increment,
tname char(20) not null
);
# 课程表
cid cname teacher_id
create table course(
cid int primary key auto_increment,
cname char(20) not null unique,
teacher_id int not null,
foreign key(teacher_id) references teacher(tid)
);
# 成绩表
sid student_id course_id number
create table score(
sid int not null unique auto_increment,
student_id int not null,
course_id int not null,
primary key(student_id,course_id), #联合主键
foreign key(student_id) references student(sid),
foreign key(course_id) references course(cid)
);