day42

今日内容概要

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

• 一对多

• 多对多

• 一对一

• 多表查询相关(核心)

• Navicat可视化软件

• 多表查询练习题(可能有些复杂，当堂不一定能消化)

• python操作MySQL

• 事务

外键

表与表之间建立关系

表与表之间只有三种关系：一对一，多对一，一对多
在MySQL的关系中没有多对一的说法

定义一张表
该表的组织结构不清晰
浪费硬盘空间

数据的扩展性极差，修改起来难度极差

如何优化？

使用外键
什么是外键？
外键是关系数据库中的一个概念，用于建立两个关系表之间的关联关联
它是一个列或一组列，用来指向另一个表的主键
外键在建立数据表与数据表之间的关系时起到了重要的作用

一对多关系

1.一对多关系，外键建在字段多的地方
2.在创建表的时候一定要先创建被关联表
3.在录入数据的时候必须要先录入被关联表
在确定表与表关系之前，首先要换位思考
例如：
在员工表：要考虑到员工表里面的一个员工是否能对应部门表里面的多个部门
在部门表：要考虑到一个部门能否对应员工表里面的多个员工
总结：员工表与部门表只是单向的一对多成立，那么员工表与部门表就是一对多的表关系

create table dep(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);
 
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 # 级联删除
);
 
录入数据
mysql> insert into dep(dep_name,dep_desc) values('人事部','管理人才');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into emp(name,age,dep_id) values('kevin',20,1);\
Query OK, 1 row affected (0.00 sec)

多对多

以图书表和作者表为例
我们站在图书表的角度：一本图书能不能有多个作者？可以
我们站在作者表的角度：一个作者能不能写多本书？可以
结论：两个都可以，那么表关系就是’多对多‘
针对多对多的表关系，外键字段建在第三张表中

mysql> create table book(
    -> id int primary key auto_increment,
    -> title varchar(32),
    -> price decimal(8,2));
Query OK, 0 rows affected (0.04 sec)
 
mysql> create table author(
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> addr varchar(32));
Query OK, 0 rows affected (0.04 sec)
 
mysql> 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);
Query OK, 0 rows affected (0.03 sec)
 
mysql> insert into book(title,price) values('斗破苍穹',1000);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into book(title,price) values('完美世界',2000);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into author(name,addr) values('zhangsan','beijing');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into author(name,addr) values('lisi','shanghai');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into book2author(book_id,author_id) values(1,1);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into book2author(book_id,author_id) values(1,2);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into book2author(book_id,author_id) values(2,1);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into book2author(book_id,author_id) values(2,2);
Query OK, 1 row affected (0.00 sec)

一对一

以作者和作者详情表为例
外键关系建在哪里？两张表都可以，但是，推荐建在查询率较高的一张表

mysql> create table author_detail(
    -> id int primary key auto_increment,
    -> qq varchar(32),
    -> email varchar(32));
Query OK, 0 rows affected (0.03 sec)
 
mysql> 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
    -> );
Query OK, 0 rows affected (0.03 sec)
 
 
mysql> insert into author_detail(qq,email) values(1234,4321);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into author1(name,gender,author_detail_id) values('wzc',20,1);
Query OK, 1 row affected (0.00 sec)

注意事项

1.在创建表的时候 需要先创建被关联表(没有外键字段的表)
2.在插入新数据的时候 应该先确保被关联表中有数据
3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据
4.在修改和删除被关联表中的数据的时候 无法直接操作
    如果想要数据之间自动修改和删除需要添加额外的配置

多表查询

在此之前，都是单表下的查寻
多表查询的思路是：
1.子查询
查询kevin的部门名称
应该先查询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的执行条件，其实就是分布操作

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

专业的连表查询：

inner join 内连接，查询的是两张表中都有的数据
left join 左连接，以左表为基准，查询左表中所有的数据，右表没有的数据，使用NULL填充

right join：右连接，以右表为基准，查询右表中所有的数据，左表中没有的数据，使用NULL填充

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

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

union

select * from emp right join dep on.dep_id=dep.id;

连表可以连很多表，不只是两张，大多数是两张
 

Navicat可视化软件

这个软件基本上是不用写SQL语句
官网：Navicat | 产品查看完整的 Navicat 产品列表，包括最佳数据库管理工具 Navicat、Navicat Monitor、Navicat Charts Creator、Navicat Collaboration 和 Navicat Data Modeler。https://www.navicat.com.cn/products/