day21 多表设计、多表查询、事务、索引

一、多表设计

概述:

项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:一对多、一对一、多对多

在数据库层面,并未建立关联,所以是无法保证数据的一致性和完整性的,所以我们需要使用约束:外键,在字表中设定一个自字段为外键和父表建立起联系

创建外键的方式:①在创建表的时候添加②在创建完表之后图形化界面修改表

一对多

一对多关系实现:在数据库表中多的一方,添加字段,来关联一的一方的主键

一对一

关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他字段放在另一张表中,以提升操作效率

实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)

可以理解为把一张数据较多的表,分成几个表

多对多

关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择

实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键

二、多表查询

内连接

隐式内连接:select  字段列表   from   表1 , 表2   where   条件 ... ;

显式内连接:select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;

外连接

左外连接:select  字段列表   from   表1  left  [ outer ]  join 表2  on 连接条件 ... ;

右外连接:select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;

子查询

介绍:SQL语句中嵌套select语句,称为嵌套查询,又称子查询。

形式:select  *  from   t1   where  column1 =  ( select  column1  from  t2  … );

子查询外部的语句可以是insert / update / delete / select 的任何一个,最常见的是 select。

标量子查询:

子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式

常用的操作符:=   <>    >     >=     <    <=      

-- ========================= 子查询 ================================
-- 标量子查询
-- A.查询 "教研部" 的所有员工信息
-- 先查看教研部所属id
select id
from tb_dept
where name = '教研部';
-- 再根据教研部ID去到员工表找外键[dept_id]
select *
from tb_emp
where dept_id = 2;

select *
from tb_emp
where dept_id = (select id from tb_dept where name = '教研部');

-- B.查询在 "方东白" 入职之后的员工信息
-- 先找出方东白入职日期
select entrydate
from tb_emp
where name = '方东白';
-- 根据入职日期去 emp 找对应的员工信息
select *
from tb_emp
where entrydate >= (select entrydate from tb_emp where name = '方东白');
select *
from tb_emp
where entrydate >= '2012-11-01';

列子查询:

子查询返回的结果是一列(可以是多行)

常用的操作符:in  、not in等

行子查询:

子查询返回的结果是一行(可以是多列)。

常用的操作符:=  、<> 、in 、not  in

-- 列子查询  只有一列,有多行数据
-- A.查询 "教研部" 和 "咨询部" 的所有员工信息
-- 我们应该先要查询出 教研部 和 咨询部 所属id
select id
from tb_dept
where name in ('教研部', '咨询部');

select *
from tb_emp
where dept_id in (select id from tb_dept where name in ('教研部', '咨询部'));
select *
from tb_emp
where dept_id in (2, 3);

表子查询:

子查询返回的结果是多行多列,常作为临时表

常用的操作符:in

-- 表子查询  多行多列
-- A.查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
-- 先根据日期去找员工信息
select *
from tb_emp
where entrydate > '2006-01-01';
-- 再根据员工信息去找部门信息

--   select * from 表 inner join 表 on 外键 = 主键;
select *
from (select * from tb_emp where entrydate > '2006-01-01') temp
         inner join tb_dept on temp.dept_id = tb_dept.id;

三、事务

使用场景:如果学工部解散了,那么这个部门下的员工应该都删除,执行以下sql语句

-- 删除学工部 delete from tb_dept where id = 1;

-- 删除学工部的员工 delete from tb_emp where dept_id = 1;

如果删除部门成功了,而删除该部门的员工时失败了,就造成了数据的不一致。

事务 是一组操作的集合,它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作 要么同时成功,要么同时失败。

注意事项:默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。

开启事务:start transaction;  /  begin ; 提交事务:commit; 回滚事务:rollback;

事务的四大特性:

原子性:事务是不可分割的最小单元,要么全部成功,要么全部失败(操作要么成功,要么失败)

一致性:事务完成时,必须使所有的数据都保持一致状态(数据要么被修改,要么不变)

隔离性:数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行(事务之间不影响)

持久性:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的(对数据的操作是永久性的)

Demo

四、索引

概述:索引是帮助数据库高效获取数据数据结构。

优点:提高数据查询的效率,降低数据库的IO成本。

通过索引列对数据进行排序,降低数据排序的成本,降低CPU消耗。

缺点:索引会占用存储空间。

索引大大提高了查询效率,同时却也降低了insert、update、delete的效率。

MySQL数据库支持的索引结构有很多,如:Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引,如果没有特别指明,都是指默认的 B+Tree 结构组织的索引。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值