[MySQL][表的约束][二][主键][自增长][唯一键][外键]详细讲解

目录

• 1.主键
• 2.自增长
• • 1.是什么？
  • 2.索引
• 3.唯一键
• 4.外键
• • 1.为什么？
  • 2.是什么？
  • 3.如何理解外键约束？
• 5.综合案例 -- 阅读

1.主键

• 主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个

  • 但不意味着一个表中的主键，只能添加给一列
    • 一个主键可以被添加到一列，或者多列上 – 复合主键

• 示例：创建表的时候直接在字段上指定主键

  mysql> create table t4 ( -> id int unsigned primary key comment '学号不能为空', -> name varchar(20) not null);  mysql> desc t4; +-------+------------------+------+-----+---------+-------+ | Field | Type             | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id    | int(10) unsigned | NO   | PRI | NULL    |       | # key中pri表示该字段是主键 | name  | varchar(20)      | NO   |     | NULL    |       | +-------+------------------+------+-----+---------+-------+ 

• 主键约束：主键对应的字段中不能重复，一旦重复，操作失败

  mysql> insert into t4 values(1, 'aaa'); mysql> insert into t4 values(1, 'aaa'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 

• 当表创建好以后但是没有主键的时候，可以再次追加主键

  alter table 表名 add primary key(字段列表) 

• 删除主键

  alter table 表名 drop primary key; 

  mysql> alter table t4 drop primary key; mysql> desc t4; +-------+------------------+------+-----+---------+-------+ | Field | Type             | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id    | int(10) unsigned | NO   |     | NULL    |       | | name  | varchar(20)      | NO   |     | NULL    |       | +-------+------------------+------+-----+---------+-------+ 

• 复合主键：在创建表的时候，在所有字段之后，使用primary key来创建主键，如果有多个字段作为主键，可以使用复合主键

  mysql> create table t5( -> id int unsigned, -> course char(10) comment '课程代码', -> score tinyint unsigned default 60 comment '成绩', -> primary key(id, course) <= id和course为复合主键 -> );  mysql> desc t5; +--------+---------------------+------+-----+---------+-------+ | Field  | Type                | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+-------+ | id     | int(10) unsigned    | NO   | PRI | 0       |       | <= 这两列合成主键 | course | char(10)            | NO   | PRI |         |       | | score  | tinyint(3) unsigned | YES  |     | 60      |       | +--------+---------------------+------+-----+---------+-------+  mysql> insert into t5 (id,course)values(1, '123');  mysql> insert into t5 (id,course)values(1, '123'); ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY' <= 主键冲突 

2.自增长

1.是什么？

• auto_increment：
  • 当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值 +1操作，得到一个新的不同的值
  • 通常和主键搭配使用，作为逻辑主键
• 自增长的特点：
  • 任何一个字段要做自增长，前提是本身是一个索引(key一栏有值)
  • 自增长字段必须是整数
  • 一张表最多只能有一个自增长
• 示例：

  mysql> create table t6( -> id int unsigned primary key auto_increment, -> name varchar(10) not null default '' -> );  mysql> insert into tt21(name) values('a'); mysql> insert into tt21(name) values('b');  mysql> select * from tt21; +----+------+ | id | name | +----+------+ | 1  | a    | | 2  | b    | +----+------+ 

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

    mysql > select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 1                | +------------------+ 

2.索引

• 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单
• 索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容
• 索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。 数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息

3.唯一键

• 一张表中往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题
• 唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较
• 关于唯一键和主键的区别，可以简单理解成：
  • 主键更多的是标识唯一性的
  • 而唯一键更多的是保证在业务上，不要和别的信息出现重复
• 乍一听好像没啥区别，举一个例子
  • 假设一个场景(仅仅为了助理解)
  • 比如在公司，需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个身份证号码，一个是员工工号，可以选择身份号码作为主键
  • 而设计员工工号的时候，需要一种约束：所有的员工工号都不能重复
  • 具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯一键
• 一般而言，建议将主键设计成和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整
• 示例：

  mysql> create table student ( -> id char(10) unique comment '学号，不能重复，但可以为空', -> name varchar(10) -> );  mysql> insert into student(id, name) values('01', 'aaa'); mysql> insert into student(id, name) values('01', 'bbb'); <=唯一约束不能重复 ERROR 1062 (23000): Duplicate entry '01' for key 'id'  mysql> insert into student(id, name) values(null, 'bbb'); -- 但可以为空  mysql> select * from student; +------+------+ | id   | name | +------+------+ | 01   | aaa  | | NULL | bbb  | +------+------+ 

4.外键

1.为什么？

• 如下面示例，即便学生表通过class_id设置了和班级表的联系，但是不具备任何的约束力

  • 可能错误地插入一个不存在班级的学生
  • 可能删除了一个还有学生的班级

• class_id只有外键之名(关联关系)，没有外键之实(约束关系)

  

  

• 外键：

  1. 从表和主表的关联关系
  2. 产生外键约束

2.是什么？

• 外键用于定义主表和从表之间的关系：

  • 外键约束主要定义在从表上
  • 主表则必须是有主键约束或unique约束
  • 当定义外键后，要求外键列数据必须在主表的主键列存在或为NULL

• 语法：

  foreign key (字段名) references 主表(列) 

• 示例：
  

• 如果将班级表中的数据都设计在每个学生表的后面，那就会出现数据冗余

  • 所以只要设计成让stu->class_id和myclass_id形成关联关系 --> 外键约束

• 对上面的示意图进行设计：

1. 先创建主键表

   create table myclass ( id int primary key, name varchar(30) not null comment'班级名' ); 

2. 再创建从表

   create table stu ( id int primary key, name varchar(30) not null comment '学生名', class_id int, foreign key (class_id) references myclass(id) ); 

3. 正常插入数据

   mysql> insert into myclass values(10, '高三(9)班'),(20, '高三(19)班'); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0  mysql> insert into stu values(100, '张三', 10),(101, '李四',20); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 

4. 插入一个班级号为30的学生，因为没有这个班级，所以插入不成功

   mysql> insert into myclass values(10, '高三(9)班'),(20, '高三(19)班'); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0  mysql> insert into stu values(100, '张三', 10),(101, '李四',20); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 

5. 插入班级id为null，比如来了一个学生，目前还没有分配班级

   mysql> insert into stu values(102, 'wangwu', null); 

3.如何理解外键约束？

• 首先我们承认，这个世界是数据很多且大多都是相关性的
• 理论上，上面的例子，不创建外键约束，就正常建立学生表，以及班级表，该有的字段我们都有
• 此时，在实际使用的时候，可能会出现什么问题？
  • 有没有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中？
• 因为此时两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题
  • 解决方案就是通过外键完成的
• 建立外键的本质其实就是把相关性交给MYSQL去审核了，提前告诉MYSQL表之间的约束关系
  • 那么当用户插入不符合业务逻辑的数据的时候，MYSQL不允许你插入

5.综合案例 – 阅读

• 有一个商店的数据，记录客户及购物情况，有以下三个表组成：
  • 商品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)
• 要求：
  • 每个表的主外键
  • 客户的姓名不能为空值
  • 邮箱不能重复
  • 客户的性别(男，女)

  -- 创建数据库 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) );