SQL多表设计

多表设计

一对多

• 一对多关系实现：在数据库中多的一方，添加字段，来关联一的一方主键

• 建立关系连接（外键）

  -- 创建表时指定
  create table 表名(字段名 数据类型,...[constraint] [外键名称] foreign key(外键字段名) references 主键(字段名)
  );
  -- 建完表后，添加外键
  alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);
  

• 注意事项：

  • 物理外键
    • 概念：使用foreign key定义外键关联另外一张表。
    • 缺点：
      • 影响增、删、改的效率（需要检查外键关系）。
      • 仅用于单节点数据库，不适用于分布式、集群场景。
      • 容易引发数据库的死锁问题，消耗性能。
  • 逻辑外键（推荐）
    • 概念：在业务层逻辑中，解决外键关联。
    • 通过逻辑外键，就可以方便的解决物理外键存在的问题。

一对一

• 关系：一对一关系，用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。
• 实现：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的（UNIQUE）

多对多

• 关系：（类似于）一个学生可以选修多门课程，一门课程也可由供多个学生选择
• 实现：建立第三张表中间表，中间表至少包含两个外键，分别关联两方主键

多表查询

• 概述：
  • 多表查询：指从多张表中查询数据
  • 语法：
    • select * from tb1,tb2;，这样显示的结果是两个关系的笛卡尔积
    • 笛卡尔积：笛卡尔乘积是指在数学中，两个集合（A集合和B集合）的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）
  • 所谓消除无效的笛卡尔积，就在业务逻辑层实现自然连接，如：sekect * from tb1,tb2 where tb1.id=tb2.id;
• 分类
  • 连接查询
    • 内连接：相当于查询A、B交集部分数据
      • 隐式内连接：select 字段列表 from 表1,表2 where 条件... ;(可以在from后给表起别名，其他地方使用就都用别名)
      • 显示内连接：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 cloumn1 from t2);
      • 子查询外部的语句可以是insert / update / delete / select的任何一个，最常见的是select。
      • 分类：
        • 标量子查询：子查询返回的结果为单个值
          • 子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，可以用作where语句中
          • 常用的操作符：=、<>、>、>=、<、<=
        • 列子查询：子查询返回的结果为一列
          • 子查询返回的结果是一列（可以是多行）
          • 常用的操作符：in、not in等
        • 行子查询：子查询返回的结果为一行
          • 子查询返回的结果是一行（可以是多列）
          • 常用的操作符： =、<>、in、not in
        • 表子查询：子查询返回的结果为多行多列
          • 子查询返回的结果是多行多列，常作为临时表
          • 常用的操作符：in

事务

• 概念
  • 事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作 要么同时成功，要么同时失败。
• 注意事项
  • 默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。（也就是一条一条执行）
• 事务控制
  • 开启事务：start transaction; / begin;
  • 提交事务（所有事务执行成功后运行）：commit;
  • 回滚事务（其中有一条语句失败，需要回滚）：rollback;
• 四大特性
  • 原子性
    • 事务是不可分割的最小单元，要么全部成功，要么全部失败。
  • 一致性
    • 事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性
    • 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性
    • 事务一单提交或回滚，它对数据库中的数据的改变就是永久的。

索引

• 概念

  • 索引是帮助数据库 高效获取数据 的 数据结构。
  • 优点
    • 提高数据查询的效率，降低数据库的IO成本。
    • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。
  • 缺点
    • 索引会占用存储空间。
    • 索引大大提高了查询效率，同时缺也降低了insert、update、delete的效率。因为进行DML语句的同时还要维护索引结构。

• 结构

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

• 语法

  • 创建索引
    • create [unique] index 索引名 on 表名(字段名, ...);
  • 查看索引
    • show index from 表名;
  • 删除索引
    • drop index 索引名 on 表名;
  • 注意事项
    • 主键字段，在建表时，会自动创建主键索引。
    • 添加唯一约束时，数据库实际上会添加唯一索引。