MySQL多表

多表设计多表设计之外键约束

• 约束的作用
  
  • 用来保证数据的完整性.
    
• 单表约束
  
  • 主键约束
  • 非空约束
  • 唯一约束
    
• 多表约束
  
  • 外键约束
  • 在多表之间保证数据的完整性.
  • 在多个表之间创建一个关系,或者说叫指向.
  • 添加外键的关键字:foreign key
  • alter table 表名 add forergn key (字段名) references 表名(字段名).
  • 在开发中一般不会建成一个真正的外键,只是在程序中,添加一个条件.
    
  
  

多表设计之表关系介绍

• 表与表之间的关系
  
  • 一对多的关系
    
    • 一个部门下有多个员工,一个员工只能属于一个部门
      
  • 多对多的关系
    
    • 一个订单有多件商品,一件商品也可以存在于多个订单里边.
      
  • 一对一的关系
    
    • 一个公司可以有一个注册地址,一个注册地址也只能对应一个公司
    • 一般情况下这样一对一的关系可以合并成一个表,
      
    
    
  
  

多表设计之一对多关系

• 建表原则:
  
  • 在多的一方创建外键指向一的一方的主键.
    
  
  

多表设计之多对多关系

• 建表原则:
  
  • 需要借助第三方(第三张表[中间表]),第三方表至少放入这两个表的主键作为他的字段.分别把这两个字段做为外键指向对应的那两个表的主键.
    
  
  

多表设计之一对一关系

• 建表原则:
  
  • 唯一外键对应方式(一般使用这个方式)
    
    • 对外键加一个约束 unique
    • 假设一对一是一个一对多关系,需要在多的一方创建外键指向一的一方的主键,将外键设置为唯一约束(unique)
      
  • 主键对应方式
    
    • 将其中一张表的主键作为外键对应另一张表的主键.
    • 这样的完全可以写在一张表上,顺序位置都是一样一样的.一般很少用,碰到一对一关系的,一般使用唯一外键来对应方式来使用.
      
    
    
  
  

总之所有的关系都是在一对多的关系上进行变化的主键最好不要带有业务关系.多表之间的查询

• 概述
  
  • 
    
• 多表查询的分类
  
  
  • 连接查询
    
    
    • 交叉连接:查询到的是两个表的笛卡尔积.
    • 语法
      
      • select * from 表1 cross join 表2.
      • select * from 表1,表2.
      • 把两个表中的字段,分别一一对应,并列出所有结果.
        
    • 内连接
    • 内连接查询返回的是两个表的并集
    • 根据关联条件,去两个表中匹配,如果匹配上,就返回匹配上的记录,如果匹配不上则不返回
      
      • 显示内连接
        
        • 语法: select * from 表1 inner join表2 on 关联条件
        • inner join 是可以省略的
          
      • 隐式内连接
        
        • 语法
        • select I from 表1,表2 where 关联条件.
        • 关联条件可以有多个,中间用,and or not来连接,
          
        
        
    • 外连接 outer join.(outer可以省略)
    • 以谁为基准,则谁的所有数据都返回.
      
      
      • 左外连接
      • 以左表为基准,根据关联条件去右表查询,如果匹配上了则返回匹配上的数据,如果匹配不上则返回null.
        
        • 语法:select * from 表1 left outer join 表2 on 关联条件.
          
      • 右外连接
      • 以右表为基准,根据关联条件去左表查询,如果匹配上了则返回匹配上的数据,如果匹配不上则返回null.
        
        • 语法:select * from 表1 right outer join 表2 on 关联条件
          
        
        
    • 内连接和外连接的区别:
      
      • 一个是并集一个指定连接表的所有加上并集.
        
      
      
  • 子查询
    
    • 一个查询语句条件需要依赖另一个查询语句的结果.
    • 带In的子查询
    • 带exists的子查询
      
      • exists 是否存在
      • 如果子句返回true,则执行前面的SQL语句
      • 有点像java中的if语句
        
    • 带any的子查询
      
      • 大于任意的一个
      • 我的理解
        
        • any > (有返回表中记录SQL语句):在结果中有比他小的就返回
          
        
        
    • 带all的子查询
      
      • 大于所有的,
      • 我的理解
        
        • all > (有返回表中记录SQL语句):在结果中没有比他大的就返回.
          
        
        
      
      
    
    
  
  

事务

• 事务的概述
  
  • 指的是逻辑上的一组操作,组成这组操作的各个逻辑单元,要么全部成功,要么全部失败
    
• MySQL中的事务管理
  
  • 开启事务
    
    • start transaction
      
  • 提交事务
    
    • comnit
      
  • 回滚事务
    
    • rollback;
      
    
    
• 事务提交或者回滚后就会结束
  

事务的特性acid

• 原子性
  
  • 事务的不可分割,组成事务的各个逻辑单元不可分割
    
• 一致性
  
  • 事务执行的前后,数据完整性保持一致
    
• 隔离性
  
  • 事务执行不应该受到其他事务的干扰
    
• 持久性
  
  • 事务一旦结束(提交或者回滚),数据就持久化到数据库中
    
  
  

事务的隔离级别

• 如果不考虑隔离性,会引发一些安全问题
• 一个事务在执行的时候受到其他事务的干扰,就会引发一些安全问题 ,主要体现在读取数据上
  
  • 脏读:一个事务督导了另一个事务未提交的数据,导致查询结果不一致
  • 不可重复读:一个事务读到了另一个事务已经提交的update的数据,导致多次查询结果不一致
  • 虚读/幻读: 一个事务读到了另一个事务已经提交的insert的数据,导致多次查询结果不一致
    
  
  

如何解决这些安全性问题

• 设置事务的隔离级别
  
  • read uncommitted:脏读,不可重复读,虚读都有可能发生
  • read committed:
  • repeatable read:
  • serializable:
    
  
  

MySQL多表

多表设计多表设计之外键约束

• 约束的作用
  
  • 用来保证数据的完整性.
    
• 单表约束
  
  • 主键约束
  • 非空约束
  • 唯一约束
    
• 多表约束
  
  • 外键约束
  • 在多表之间保证数据的完整性.
  • 在多个表之间创建一个关系,或者说叫指向.
  • 添加外键的关键字:foreign key
  • alter table 表名 add forergn key (字段名) references 表名(字段名).
  • 在开发中一般不会建成一个真正的外键,只是在程序中,添加一个条件.
    
  
  

多表设计之表关系介绍

• 表与表之间的关系
  
  • 一对多的关系
    
    • 一个部门下有多个员工,一个员工只能属于一个部门
      
  • 多对多的关系
    
    • 一个订单有多件商品,一件商品也可以存在于多个订单里边.
      
  • 一对一的关系
    
    • 一个公司可以有一个注册地址,一个注册地址也只能对应一个公司
    • 一般情况下这样一对一的关系可以合并成一个表,
      
    
    
  
  

多表设计之一对多关系

• 建表原则:
  
  • 在多的一方创建外键指向一的一方的主键.
    
  
  

多表设计之多对多关系

• 建表原则:
  
  • 需要借助第三方(第三张表[中间表]),第三方表至少放入这两个表的主键作为他的字段.分别把这两个字段做为外键指向对应的那两个表的主键.
    
  
  

多表设计之一对一关系

• 建表原则:
  
  • 唯一外键对应方式(一般使用这个方式)
    
    • 对外键加一个约束 unique
    • 假设一对一是一个一对多关系,需要在多的一方创建外键指向一的一方的主键,将外键设置为唯一约束(unique)
      
  • 主键对应方式
    
    • 将其中一张表的主键作为外键对应另一张表的主键.
    • 这样的完全可以写在一张表上,顺序位置都是一样一样的.一般很少用,碰到一对一关系的,一般使用唯一外键来对应方式来使用.
      
    
    
  
  

总之所有的关系都是在一对多的关系上进行变化的主键最好不要带有业务关系.多表之间的查询

• 概述
  
  • 
    
• 多表查询的分类
  
  
  • 连接查询
    
    
    • 交叉连接:查询到的是两个表的笛卡尔积.
    • 语法
      
      • select * from 表1 cross join 表2.
      • select * from 表1,表2.
      • 把两个表中的字段,分别一一对应,并列出所有结果.
        
    • 内连接
    • 内连接查询返回的是两个表的并集
    • 根据关联条件,去两个表中匹配,如果匹配上,就返回匹配上的记录,如果匹配不上则不返回
      
      • 显示内连接
        
        • 语法: select * from 表1 inner join表2 on 关联条件
        • inner join 是可以省略的
          
      • 隐式内连接
        
        • 语法
        • select I from 表1,表2 where 关联条件.
        • 关联条件可以有多个,中间用,and or not来连接,
          
        
        
    • 外连接 outer join.(outer可以省略)
    • 以谁为基准,则谁的所有数据都返回.
      
      
      • 左外连接
      • 以左表为基准,根据关联条件去右表查询,如果匹配上了则返回匹配上的数据,如果匹配不上则返回null.
        
        • 语法:select * from 表1 left outer join 表2 on 关联条件.
          
      • 右外连接
      • 以右表为基准,根据关联条件去左表查询,如果匹配上了则返回匹配上的数据,如果匹配不上则返回null.
        
        • 语法:select * from 表1 right outer join 表2 on 关联条件
          
        
        
    • 内连接和外连接的区别:
      
      • 一个是并集一个指定连接表的所有加上并集.
        
      
      
  • 子查询
    
    • 一个查询语句条件需要依赖另一个查询语句的结果.
    • 带In的子查询
    • 带exists的子查询
      
      • exists 是否存在
      • 如果子句返回true,则执行前面的SQL语句
      • 有点像java中的if语句
        
    • 带any的子查询
      
      • 大于任意的一个
      • 我的理解
        
        • any > (有返回表中记录SQL语句):在结果中有比他小的就返回
          
        
        
    • 带all的子查询
      
      • 大于所有的,
      • 我的理解
        
        • all > (有返回表中记录SQL语句):在结果中没有比他大的就返回.
          
        
        
      
      
    
    
  
  

事务

• 事务的概述
  
  • 指的是逻辑上的一组操作,组成这组操作的各个逻辑单元,要么全部成功,要么全部失败
    
• MySQL中的事务管理
  
  • 开启事务
    
    • start transaction
      
  • 提交事务
    
    • comnit
      
  • 回滚事务
    
    • rollback;
      
    
    
• 事务提交或者回滚后就会结束
  

事务的特性acid

• 原子性
  
  • 事务的不可分割,组成事务的各个逻辑单元不可分割
    
• 一致性
  
  • 事务执行的前后,数据完整性保持一致
    
• 隔离性
  
  • 事务执行不应该受到其他事务的干扰
    
• 持久性
  
  • 事务一旦结束(提交或者回滚),数据就持久化到数据库中
    
  
  

事务的隔离级别

• 如果不考虑隔离性,会引发一些安全问题
• 一个事务在执行的时候受到其他事务的干扰,就会引发一些安全问题 ,主要体现在读取数据上
  
  • 脏读:一个事务督导了另一个事务未提交的数据,导致查询结果不一致
  • 不可重复读:一个事务读到了另一个事务已经提交的update的数据,导致多次查询结果不一致
  • 虚读/幻读: 一个事务读到了另一个事务已经提交的insert的数据,导致多次查询结果不一致
    
  
  

如何解决这些安全性问题

• 设置事务的隔离级别
  
  • read uncommitted:脏读,不可重复读,虚读都有可能发生
  • read committed:
  • repeatable read:
  • serializable: