一、多表设计
概述:
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:一对多、一对一、多对多
在数据库层面,并未建立关联,所以是无法保证数据的一致性和完整性的,所以我们需要使用约束:外键,在字表中设定一个自字段为外键和父表建立起联系
创建外键的方式:①在创建表的时候添加②在创建完表之后图形化界面修改表
一对多
一对多关系实现:在数据库表中多的一方,添加字段,来关联一的一方的主键
一对一
关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他字段放在另一张表中,以提升操作效率
实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(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 结构组织的索引。