SQL笔记(2)-约束与范式

1. 约束

1. 概念：对数据进行限定，保证数据的正确性，完整性
2. MySQL中的约束：
    a. 主键约束：primary key
        1. 主键约束：表示修饰的列非空且值唯一
                    一张表只能有一个主键
        2. 在创建表时：
            在列名 数据类型后添加primary key约束
        3. 修改表的主键约束
            * alter table 表名 drop primary key; -- 删除表的主键，由于只有一个主键，所以不需要指定列名
            * alter table 表名 modify 列名 新数据类型 primary key;
        4. 自动增长：关键字auto_increment
            一般配合主键使用，用于自动增长编号
            创建表时：
                列名 数据类型 primary key auto_increment
            修改表的属性：
                alter table 表名 modify 列名 新数据类型; -- 删除自动增长
                alter table 表名 modify 列名 新数据类型 auto_increment; -- 添加自动增长
            自动增长指与上一条数据有关，在上一个编号上加一
    b. 非空约束：not null
        1. 在创建表时：
            在列名 数据类型后添加not null约束
        2. 修改表的列属性：
            * alter table 表名 modify 列名 新数据类型 not null; -- 添加非空约束
            * alter table 表名 modify 列名 新数据类型; -- 删除非空约束
    c. 外键约束：foreign key
        1. 外键约束：让表与表产生关联，保证数据逻辑的正确性
        2. 创建表时：
            在最后添加
            constraint 外键名称 foreign key (此表关联的列名) reference 主表(主表被关联的列名<通常为主键>)
            -- constraint 外键名称 foreign key (列名) reference 主表(列名)
        3. 修改表的属性：
            * alter table 表名 drop foreign key 外键名称; -- 删除表的外键
            * alter table 表名 add constraint 外键名称 foreign key (列名) reference 主表(列名); -- 添加外键
        4. 注意：
            主表需要先创建
            外键值可以为NULL，但是不可以为不存在的值
    d. 唯一约束：unique
        1. 在创建表时：
            在列名 数据类型后添加unique约束
        2. 修改表的列属性：
            * alter table 表名 modify 列名 新数据类型 unique; -- 添加唯一约束，如果已经有重复数据会添加约束失败
            * alter table 表名 drop index 列名; -- 删除唯一约束
        3. 注意：
            MySQL唯一约束限定的列的值可以有多个NULL
3. 级联操作：
    a. 如果两个表之间存在外键约束，则级联操作可以方便修改两个表的外键列的值
        使得修改主表的外键列的值，副表外键列的值同时修改
    b. 格式：
        1. 级联更新：在定义外键时添加关键字on update cascade
            -- constraint 外键名称 foreign key (列名) reference 主表(列名) on update cascade
        2. 级联删除：在定义外键时添加关键字on delete cascade
            -- constraint 外键名称 foreign key (列名) reference 主表(列名) on delete cascade
        3. 同时添加级联更新与级联删除操作：
            -- constraint 外键名称 foreign key (列名) reference 主表(列名) on update cascade on delete cascade
    c. 级联操作在过多表关联时，更新数据的效率会降低，因此慎重使用

2. 数据库设计

1. 多表之间的关系：
    a. 1 : 1关系
        例如：人 - 身份证号
        设计实现：可以在任意一方添加一列作为外键列，且该外键列添加唯一属性，指向另一方的主键列
                  也可以合两张表为一张
    b. 1 : n关系
        例如：部门 - 员工
        设计实现：在n一方的表中添加一列作为外键列，指向1一方中的主键列
    c. n : m关系
        例如：学生 - 课程
        设计实现：创建一张新表，该表至少包含两个字段(分别为两张表的主键)，
                  该新表的两个字段都设置为外键，分别指向两张主表的主键

3. 数据库设计范式

1. 分类：六种范式
2. 第一范式(1NF)：每一列都是不可分割的原子数据项
3. 第二范式(2NF)：在1NF的基础上，非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
    a. 函数依赖：
        A --> B
        如果通过A属性(属性组)的值，可以确定唯一B属性的值，则称B依赖于A。
        例如：学号 --> 姓名，(学号，课程名) --> 课程得分
              姓名依赖学号属性，课程得分依赖学号和课程名组成的属性组
    b. 完全函数依赖：
        A --> B
        如果A是一个属性组，如果B的唯一确定依赖与属性组中所有属性，则称完全函数依赖
        例如：(学号，课程名) --> 课程得分
              课程得分必须依赖属性组中所有属性才能确定唯一
    c. 部分函数依赖：
        A --> B
        如果A是一个属性组，如果B的唯一确定依赖与属性组中部分属性，则称部分函数依赖
        例如：(学号，课程名) --> 姓名
              姓名仅依赖属性组中学号属性就可以确定唯一
    d. 传递函数依赖：
        A --> B, B --> C
        如果通过A属性(属性组)的值，可以确定唯一B属性的值，通过B属性(属性组)的值可以确定唯一C，则称C传递依赖于A
        例如：学号 --> 系名, 系名 --> 系主任
              学号确定唯一系名，一个系确定唯一的系主任
    e. 码：
        如果在一张表中，一个属性(属性组)被其他所有属性所完全依赖，则称该属性(属性组)为该表的码
        例如：(学号，姓名，系名，系主任，课程名称，课程得分)中码为属性组(学号，课程名称)
        主属性：码属性组中的所有属性
        非主属性：除码中属性外的其他属性
4. 第三范式(3NF)：在2NF的基础上，任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)

4. 数据库的备份与还原

1. 命令行：
    a. MySQL语法：
        1. 备份：mysqldump -u用户名 -p密码 备份的数据库名称 > 备份文件的保存路径
        2. 还原：
                1. 登陆数据库
                2. 创建一个新的数据库
                3. use新创建的数据库
                4. 还原备份的文件语法：source 备份文件的保存路径
2. 图形化界面：
    a. 备份：
        1. 对需要备份的数据库右键选择备份
        2. 选择备份文件的保存路径
    b. 还原：
        1. 执行SQL脚本
        2. 选择备份的文件执行