【MySQL】表的约束（2）

【MySQL】表的约束（2）

目录

• 【MySQL】表的约束（2）
• • 自增长
  • 唯一键
  • 外键
  • 综合案例-阅读

    作者：爱写代码的刚子

    时间：2024.3.13

    前言：本篇博客主要是介绍表的约束下部分内容(自增长，唯一键，外键)

    自增长

    auto_increment:当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常（必须）和主键搭配使用，作为逻辑主键。

    自增长的特点:

    • 任何一个字段要做自增长，前提是本身是一个索引(key一栏有值)

    • 自增长字段必须是整数

    • 一张表最多只能有一个自增长

    • 创建一个自增长的表

      • 向表中插入数据并查看：

        默认从1开始自增

        • 但是如果插入了id值较大的数据，那将会从原始id的最大值开始增长:
          • 如何做到？

            除了表内有AUTO_INCREMENT，表外也有AUTO_INCREMENT（用来表示下一次数据插入时的AUTO_INCREMENT起始值）

            • 注意设为自增长的那一列必须为主键

            • **select last_insert_id();**在插入后获取上次插入的 AUTO_INCREMENT 的值(批量插入获取的是第一个值）

              索引（本质就是牺牲空间换时间）：

              在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

              索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。 数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得 更快，可快速访问数据库表中的特定信息。

              唯一键

              一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键:唯一键就可以解决表中有多个字段需要唯一性约束的问题。

              唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

              • 创建含有唯一键的表：
                • 插入数据：

                  唯一键可以为空，但是主键不能为空

                  关于唯一键和主键的区别:

                  我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。（并不冲突，反而互相补充）

                  • 让唯一键也不为空：

                    外键

                    外键用于定义主表和从表之间的关系:外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。

                    • 外键：1. 从表和主表的关联关系2. 产生外键约束
                    • 创建两张表：
                      • 往student表中插入一些学生：
                        • 这时我们插入了一个班级之外的学生（理论上这是不被允许的）：

                          所以mysql要规避这种不符合要求的插入。

                          • 同时删除班级也是不合理的
                            • 再次查看这两张表结构：

                              class_id有外键之名（关联关系），但是没有外键之实（没有约束）

                              • 创建一个带有外键的从表student：
                                • 当前的class表：
                                  • 从表内依旧有student关联，所以不能删除class里面的id：
                                    • 当关联的class_id没有数据时可以删除主表相关的id字段：

                                      外键保证完整性：有学生对应班级，有班级对应学生，不会将学生插入一个不存在的班级，也不会删除一个还有学生的班级，保证了表与表之间的完整性

                                      首先我们承认，这个世界是数据很多都是相关性的。

                                      理论上，上面的例子，我们不创建外键约束，就正常建立学生表，以及班级表，该有的字段我们都有。

                                      此时，在实际使用的时候，可能会出现什么问题? 有没有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中? 比如比特只开了比特100班，比特101班，但是在上课的学生里面竟然有比特102班的学生(这个班目前并 不存在)，这很明显是有问题的。

                                      因为此时两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。

                                      解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉mysql 表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql不允许你插入。

                                      • 可以对外键起别名：

                                        综合案例-阅读

                                        有一个商店的数据，记录客户及购物情况，有以下三个表组成:

                                        • 商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供应商 provider)
                                        • 客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证card_id)
                                        • 购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)

                                          要求:

                                          • 每个表的主外键
                                          • 客户的姓名不能为空值
                                          • 邮箱不能重复
                                          • 客户的性别(男，女)

                                            SQL:

                                            -- 创建数据库
                                            create database if not exists bit32mall default character set utf8 ;
                                            -- 选择数据库 use bit32mall;
                                            -- 创建数据库表
                                            -- 商品
                                            create table if not exists goods (
                                              goods_id int primary key auto_increment comment '商品编号', 					goods_name varchar(32) not null comment '商品名称', 
                                              unitprice int not null default 0 comment '单价，单位分', 
                                              category varchar(12) comment '商品分类',
                                              provider varchar(64) not null comment '供应商名称' 
                                            );
                                            -- 客户
                                            create table if not exists customer (
                                              customer_id int primary key auto_increment comment '客户编号', name 	varchar(32) not null comment '客户姓名',
                                              address varchar(256) comment '客户地址',
                                              email varchar(64) unique key comment '电子邮箱',
                                              sex enum('男','女') not null comment '性别',
                                              card_id char(18) unique key comment '身份证' 
                                            );
                                            -- 购买
                                            create table if not exists purchase (
                                                order_id int primary key auto_increment comment '订单号', 							customer_id int comment '客户编号',
                                                goods_id int comment '商品编号',
                                                nums int default 0 comment '购买数量',
                                                foreign key (customer_id) references customer(customer_id),
                                                foreign key (goods_id) references goods(goods_id)
                                            );