简介

  • 关系模型仅仅是一种逻辑关系，不是必要的设定。如逻辑上你是我的外键，但是我不一定需要强制修改表结构添加外键。即便不配置，逻辑上来说你也是我的外键

  • 关系模型不能用于查询，可以看成是建立表结构用的。但是对于增加和删除数据时，可以对变动数据进行限制

    主键-单表

  • 主键约束：唯一且不为NULL

  • 主键即是一条数据的唯一标识，插入相同主键的数据是不允许的
  • 聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引

主键要求最为关键的一点:记录插入后就不要再变更主键了，否则导致一系列问题
选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。
因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。(身份证还好些，虽然可能会变，但是变更身份证的几率非常小)
主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有：

  1. 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
  2. 全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

对于大部分应用来说，通常自增类型的主键就能满足需求。我们在students表中定义的主键也是BIGINT NOT NULL AUTO_INCREMENT类型。

主键语法详见MySQL语法基础

联合主键

  • 即多个字段共同组成唯一标识记录，作为主键

  • 对于联合主键，只要有至少一列不重复即可

    • 没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升

      外键-多表(一对多/多对一)

  • 外键简单来说就是一个表的唯一索引在另外一个表中不是唯一性的字段。如班级表中班级id是唯一的，但是在学生表中班级id不是唯一的，一个班级id可以对应很多学生数据

    • 只是逻辑上是一种东西，不要误以为外键与被关联字段必须同名称！
  • 具有唯一性字段的表作为外键表
  • 只有多个表之间才有外键关系
  • 外键一般是针对另一个表的主键来说的，但是实际上不是主键也可以，唯一性索引即可。唯一性字段是建立表之间 的联系的必须的前提。唯一性索引即unique约束
  • 一般我们也是不建议在数据库层面使用外键的，应用层面可以解决。不过，这样会对数据的一致性造成威胁。具体要不要使用外键还是要根据你的项目来决定。

具体作用:使得两张表关联，保证数据的一致性和实现一些级联操作。比如被关联的表插入数据时，如果关联字段在外键中不存在，就无法插入

班级表classes和学生表students
students表：学号 学生姓名
classes表：班级号 班级名称
我们在学生表里增加一个class_id表示学生所属班级
把学生表的class_id和班级表的id关联起来即形成了外键 (class_id是外键)
（哪列是外键看逻辑关系选择或者一对多的是主，多对一的是从，从的那列即外键）

创建外键约束

  • 以下命令翻译：修改**被关联表**结构，为**被关联字段**添加一个叫做**xx**的外键约束，把**外键表**的**外键**作为参考

    1. alter table  被关联表
    2. add constraint 外键约束名称
    3. foreign key (被关联字段)  
    4. references 外键表 (外键);

    通过定义外键约束，关系数据库可以保证无法插入无效的数据。
    即如果**classes**表不存在**id=99**的记录，**students**表就无法插入**class_id=99**的记录。

    删除外键约束

    1. alter table 被关联表    
    2. drop foreign key 外键约束名;

    多对多

  • 多对多就是我的唯一索引在你那不唯一，你的唯一索引在我这不唯一。我和你都互有对方的一个外键。如班级与老师的关系，一个老师可以对应多个班级，一个班级也可以对应多个老师

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系

一对一

  • 简单说就是这个字段在我这是唯一的，在你那也是唯一的
  • 如students表的每个学生可以有自己的联系方式，如果把联系方式存入另一个表contacts，我们就可以得到一个“一对一”关系。但是，有些时候，如果某个学生没有手机号，那么，contacts表就不存在对应的记录。实际上，一对一关系准确地说，是contacts表一对一对应students表。而学生表不一一对应联系人表

当字段量太大时，把经常读取和不经常读取的字段分开，拆分为一对一关系可以提高查询速度；另外一对一可以更好的进行事务隔离

数据库三范式

  1. 数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值
    1. 如一个地址字段是江西省赣州市… 如果需要经常省份，那么应该新增一个省份字段，而不是拿地址字段进行结果拆分
  2. 一个表应该只保存一类信息，即实体中每一行的所有非主属性都必须完全依赖于主键
    1. 如学生分数，应该拆分为学生信息表和分数表。而不应该存一个表(即不可以多个相同的学生id的数据）

      主从数据库

  • 主从即从库是主库的备份，同时主库发生变化，从库也跟着变化。一般主从分布在不同的地理位置，以防止一个地方外界因素影响备份从库

    视图

    概念

  • MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。

    • 行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。
      • 数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。
  • 视图作用主要2个：
    • 保护某些安全数据；如可以让部分人只看自己对应的数据
    • 使复杂的查询易于理解与使用（如多个表的连接，抽取出关联信息组成一张表，就能直接从这张表里查，而不是每次进行连接查询）
  • 相关的真实表被删除，视图在使用时会报错。可以提前使用check table 表名检查，下面左边是存在时的结果，右边是删除后的结果
    • 视图表被删除不影响相关真实表。
    • 如果视图表来源是一个表的全部数据，则视图表增删改影响真实表。如果来源一个表部分数据或者多个表，那么无法对该视图进行增删改

image.png image.png

创建

  • 视图名必须唯一，同时不能与表重名。
  • 视图可以使用select语句查询到的列名，也可以自己指定相应的列名。
  • 可以指定视图执行的算法，通过ALGORITHM指定。
  • column_list如果存在，则数目必须等于SELECT语句检索的列数
  • 创建视图：**create** [or replace] [algorithm = {undefined | merge | temptable}] **view view_name** [(column_list)] **as select语句** select可以用()包裹
    • SELECT 语句不能引用系统或用户变量。
    • SELECT 语句不能包含 FROM 子句中的子查询。
    • SELECT 语句不能引用预处理语句参数。
  • 如果a视图来源是b视图，b一个视图中定义了排序，那么a视图的排序将覆盖b视图排序

  • 视图来源不能是临时表

    常用视图命令

  • 查看结构

    • desc/describe 视图：查看视图结构，同普通表一样
    • show create view 视图查看视图详细信息，如定义语句
      • show create view s;\G可以格式化输出

  • 删除视图：

    • drop view 视图
      • drop view if exists 视图如果存在则删除

        保持视图一致性

  • 即视图创建时如果限制了条件，那么往视图插入数据但是可能看不到该数据。在创建视图时的select语句后加上with check option，就限制用户对视图修改时，只能对视图可见的数据进行修改和显示。但是不影响直接修改源表

    • 如create view sa as select * from score where number >=60 WITH CHECK OPTION;