今日内容概要
-
外键(表与表之间的关系)
-
一对多
-
多对多
-
一对一
-
多表查询相关(核心)
-
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;
连表可以连很多表,不只是两张,大多数是两张
文章来源:https://www.toymoban.com/news/detail-720443.html
Navicat可视化软件
这个软件基本上是不用写SQL语句
官网:Navicat | 产品查看完整的 Navicat 产品列表,包括最佳数据库管理工具 Navicat、Navicat Monitor、Navicat Charts Creator、Navicat Collaboration 和 Navicat Data Modeler。https://www.navicat.com.cn/products/文章来源地址https://www.toymoban.com/news/detail-720443.html
到了这里,关于day42的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!