MySQL数据库#4

外键(表与表之间的关系)

什么是外键：

        外键是指关系数据库中一个表中的字段，它与另一个表的主键相关联，用于建立两个表之间的关联关系。外键可以用来确保数据的完整性，通过检查关联表的主键来限制对于某个表的操作。当对主表进行操作时，外键的限制也会自动作用到从表中。这可以保证数据在不同表之间的一致性，防止出现不合理的数据关系。在使用外键时，需要定义好关系表之间的关联规则，包括指定主表和从表，以及主表和从表之间的关联字段等。

举例：

我们可以建立一个表：

当我们需要修改部门表的名称的话，则需把工程部里的所有人都得改，牵一发而动全身，很麻烦，

第二个就是这个表的表达不清晰，不清楚是员工表还是部门表，表内的相关字段一直重复存储，

表的扩展性很差，所以我们要想解决，就需要分成两张表

emp 和dep 但是这样的话表与表之后就没有关联了。所以我们需要外键来建立关系。

表与表之间的关系

表与表之间的关系有：

一对多：

想要确认是否为一对多关系需要换位思考法

我们以员工表与部门表为例：

我们先看员工表，一个员工是否可以有多个部门？不能

部门是否可以有多个员工？可以

根据以上分析我们得知，员工表不可以，部门表可以，表之间的关系就是：一对多，在表关系中并没有多对一。我们针对一对多，外键字段就要在多的一方，也就是部门表

如何在mysql表中建立一对多的关系：先把基础的字段建立出来，然后在考虑外键字段

create table emp(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id)，on update cascade , on delete cascade # 让两张表建立了外键关系
   on update cascaed # 级联更新
    on delete cascade # 级联删除
);

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

多对多

        以图书表和作者表为例
我们站在图书表的角度

    问：一本图书能不能有多个作者?
    答：可以
我们再站在作者表的角度

    问：一个作者能不能写多本书
    答：可以
得出结论：如果两个都可以，那么表关系就是'多对多'

针对于多对多的表关系，外键字段建在第三张表中

create table book(
    id int primary key auto_increment,
    title varchar(32),
    price decimal(8,2)
);

create table author(
    id int primary key auto_increment,
    name varchar(32),
    addr varchar(32)
);

create table book2author(
    id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(book_id) references author(id) # 让两张表建立了外键关系
    on update cascade # 级联更新
    on delete cascade, # 级联删除
    foreign key(author_id) references book(id) # 让两张表建立了外键关系
    on update cascade # 级联更新
    on delete cascade
);

输入完之后查看

insert into book(title, price) values('朝花夕拾', 50);
insert into book(title, price) values('骆驼祥子', 2000);

insert into author(name, addr) values('luxun', 'beijing');
insert into author(name, addr) values('shuqingchun', 'liaoningi');

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

注意事项：

      1.   在创建表的时候 需要先创建被关联表(没有外键字段的表)

      2.在插入新数据的时候 应该先确保被关联表中有数据

3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据

4.在修改和删除被关联表中的数据的时候 无法直接操作

    如果想要数据之间自动修改和删除需要添加额外的配置

一对一的关系

create table author1(
    id int primary key auto_increment,
    name varchar(32),
    gender varchar(32),
    author_detail_id int unique,
    foreign key(author_detail_id) references author_detail(id)
    on update cascade
    on delete cascade
);

create table author_detail(
    id int primary key auto_increment,
    qq varchar(32),
    email varchar(32)
);
 

        

多表查询

        我们之前所查的都是单表查询，

多表查询的思路是

  子查询

        查询keivn的部门名称的话我们需要先查询kevin所在的部门id

select dep_id from emp where name='kevin'

       之后再用查出的id去和dep表中查询部门名称

select *from dep where id=(select dep_id from emp where name='kevin');

所以子查询实际就是一条sql的执行结果是另一条sql语句的执行条件。

多表查询

        多表查询：把多张有关系的表连接成一张大的虚拟表，连接出来的虚拟表不是实际存在的，它是在内存中存储，是按照单表查询。

我们先准备下数据；

create table dep(
    id int primary key auto_increment,
    name varchar(20) 
);

create table emp(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
);

如果两张表没有建立强制的约束关系，就使用逻辑意义上的关联
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'保洁')
;

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

创建完毕

        

多表查询

        select *from emp,dep where dep_id=dep.id;

专业的连表语法;

inner join:内连接，查询两张表都有的数据

left join ：左连接，以左表为基准，查询左表内所有的数据，右表没有的数据使用NULL代替

right ： 右连接，以右表为基准，查询右表内所有的数据，左表没有的数据使用NULL代替

union ：连接两个sql语句的结果

select * from emp left join dep on emo.dep_id=dep.id

union

select * from dep right jojn dep on dep.emo_id=dep,id

多表查询练习题

1、查询所有的课程的名称以及对应的任课老师姓名

2、查询平均成绩大于八十分的同学的姓名和平均成绩

3、查询没有报李平老师课的学生姓名

        
4、查询挂科超过两门(包括两门)的学生姓名和班级