多表设计

• 外键约束
• 单表约束: 主键约束:     primary key 唯一非空,如:学号
  ​
                  唯一约束:   unique      如:用户名
  ​
                  非空约束:   not null        不能为空
  每个表都有一个主键约束,没有业务用途,只用来标记.
  多表约束:   用来保证多表数据的完整性.
  删除部门之前,需要先保证该部门下没有关联的员工.
  创建dept部门表
  创建employee员工表
  添加外键约束:
      在员工表上给字段dno添加外键.
  alter table employee add foreign key (dno) references key dept(did);添加外键指向dno字段,关联于表中的did字段.
      设置外键非空
  alter table employee modify dno int not null;
• 表与表关系
  一对多的关系  例子:部门,员工
  多对多的关系  例子:学生,课程
  一对一的关系  例子:公司,地址
  
  

• 一对多▲
  建表原则:
  在多的一方创建外键指向一的一方的主键 !
  员工表中的部门编号指向部门表中的部门编号;
  实际开发不会写上外键foreign key这句话,仅表示两个表之间的两个部门编号相关联.
  
  

• 多对多▲
  建表原则:
  创建第三张表(中间表)在中间表中至少两个字段,分别作为外键指向双方的主键.
  
  

• 一对一
  建表原则:
  1.建两个表---假设是一对多,在多的一方创建外键指向一的一方的主键,将主键设置为唯一unique.
  2.建一个表---公司表和地址表写在一起,也可以保证公司和地址的一对一.
  
  

• 多表分析,创建
  ​
  
  

• 多表查询
  连接查询:
      交叉连接:
          1.使用cross join关键字
          2.不使用
      内连接:inner join(inner可省)
          ---返回(返回匹配上的数据,匹配不上的就不要了)两个表的交集
          显式内连接:
              select*from 表1 inner join 表2 on 关联条件
              select*from classes c join student s on c.cid=s.cno;
          ▲隐式内连接:
              select*from表1,表2 where 关联条件                   ---业务需求
              select*from classes c,student s where c.cid=s.cno and s.sex='男';
      外连接:outer join(outer可省)
          ---以左表为基准,根据关联条件去右表查询,匹配上的则返回匹配数据,匹配不上返回null
          ▲左外连接:(outer可省)
              select*from 表1 left outer join 表2 on 关联条件
              select*from classes c left join student s on c.cid=s.cno;
          右外连接:
              select*from 表1 right outer join 表2 on 关联条件
              select*from classes c right join student s on c.cid=s.cno;
  子查询:
      带in的子查询:    ---在条件范围内
      select*from classes where cid in (select cno from student where birthday>'1991-01-01');
      带exists的子查询:---if条件判断,子句为true,执行前边
      select*from classes where exists (select cno from student where birthday>'1991-01-01');
      带any的子查询:   ---大于任意的就是大于最小值
      select * from classes where cid >any (select cno from student);
      带all的子查询:   ---大于所有的就是大于最大值
      select*from classes where cid > all (select cno from student);
     
  ​
• 数据准备
  多表查询的数据准备:
  ​
  ​
  
  
  

• 事务的概述
  事务:逻辑上的一组操作,组成这组操作的各个逻辑单元,要么全都成功,要么全都失败.
  转账案例:           ---转账双方同时完成,才是成功.
      开始事务:Start transaction
      提交事务:commit ---commit之后需要重新开启.
      回滚事务:rollback
  事务的特性:ACID
      原子性:事务的不可分割,组成事务的各个逻辑单元不可分割.
      一致性:事务执行的前后数据完整性保持一致.
      隔离性:事务执行不应该受到其他事务的干扰.
      持久性:事务一旦结束,数据就持久化到数据中.
• 事务的隔离级别
  如果不考虑隔离性,引发一些安全问题:
      脏读:一个事务读到了另一个事务未提交的数据,导致查询的结果不一致.
      不可重复读:
      虚读/幻读:
  解决这些安全性问题:
      read uncommitted---(效率最高,安全性最低)
      read committed  提交的数据
      repeatable read
      serializable    ---(效率最低,安全性最高)
  ​
  
  
  

多表设计

• 外键约束
• 单表约束: 主键约束:     primary key 唯一非空,如:学号
  ​
                  唯一约束:   unique      如:用户名
  ​
                  非空约束:   not null        不能为空
  每个表都有一个主键约束,没有业务用途,只用来标记.
  多表约束:   用来保证多表数据的完整性.
  删除部门之前,需要先保证该部门下没有关联的员工.
  创建dept部门表
  创建employee员工表
  添加外键约束:
      在员工表上给字段dno添加外键.
  alter table employee add foreign key (dno) references key dept(did);添加外键指向dno字段,关联于表中的did字段.
      设置外键非空
  alter table employee modify dno int not null;
• 表与表关系
  一对多的关系  例子:部门,员工
  多对多的关系  例子:学生,课程
  一对一的关系  例子:公司,地址
  
  

• 一对多▲
  建表原则:
  在多的一方创建外键指向一的一方的主键 !
  员工表中的部门编号指向部门表中的部门编号;
  实际开发不会写上外键foreign key这句话,仅表示两个表之间的两个部门编号相关联.
  
  

• 多对多▲
  建表原则:
  创建第三张表(中间表)在中间表中至少两个字段,分别作为外键指向双方的主键.
  
  

• 一对一
  建表原则:
  1.建两个表---假设是一对多,在多的一方创建外键指向一的一方的主键,将主键设置为唯一unique.
  2.建一个表---公司表和地址表写在一起,也可以保证公司和地址的一对一.
  
  

• 多表分析,创建
  ​
  
  

• 多表查询
  连接查询:
      交叉连接:
          1.使用cross join关键字
          2.不使用
      内连接:inner join(inner可省)
          ---返回(返回匹配上的数据,匹配不上的就不要了)两个表的交集
          显式内连接:
              select*from 表1 inner join 表2 on 关联条件
              select*from classes c join student s on c.cid=s.cno;
          ▲隐式内连接:
              select*from表1,表2 where 关联条件                   ---业务需求
              select*from classes c,student s where c.cid=s.cno and s.sex='男';
      外连接:outer join(outer可省)
          ---以左表为基准,根据关联条件去右表查询,匹配上的则返回匹配数据,匹配不上返回null
          ▲左外连接:(outer可省)
              select*from 表1 left outer join 表2 on 关联条件
              select*from classes c left join student s on c.cid=s.cno;
          右外连接:
              select*from 表1 right outer join 表2 on 关联条件
              select*from classes c right join student s on c.cid=s.cno;
  子查询:
      带in的子查询:    ---在条件范围内
      select*from classes where cid in (select cno from student where birthday>'1991-01-01');
      带exists的子查询:---if条件判断,子句为true,执行前边
      select*from classes where exists (select cno from student where birthday>'1991-01-01');
      带any的子查询:   ---大于任意的就是大于最小值
      select * from classes where cid >any (select cno from student);
      带all的子查询:   ---大于所有的就是大于最大值
      select*from classes where cid > all (select cno from student);
     
  ​
• 数据准备
  多表查询的数据准备:
  ​
  ​
  
  
  

• 事务的概述
  事务:逻辑上的一组操作,组成这组操作的各个逻辑单元,要么全都成功,要么全都失败.
  转账案例:           ---转账双方同时完成,才是成功.
      开始事务:Start transaction
      提交事务:commit ---commit之后需要重新开启.
      回滚事务:rollback
  事务的特性:ACID
      原子性:事务的不可分割,组成事务的各个逻辑单元不可分割.
      一致性:事务执行的前后数据完整性保持一致.
      隔离性:事务执行不应该受到其他事务的干扰.
      持久性:事务一旦结束,数据就持久化到数据中.
• 事务的隔离级别
  如果不考虑隔离性,引发一些安全问题:
      脏读:一个事务读到了另一个事务未提交的数据,导致查询的结果不一致.
      不可重复读:
      虚读/幻读:
  解决这些安全性问题:
      read uncommitted---(效率最高,安全性最低)
      read committed  提交的数据
      repeatable read
      serializable    ---(效率最低,安全性最高)
  ​