ToB企服应用市场:ToB评测及商务社交产业平台

标题: [MySQL][表的束缚][二][主键][自增长][唯一键][外键]详细讲解 [打印本页]

---

作者: 万万哇    时间: 2024-7-19 02:59
标题: [MySQL][表的束缚][二][主键][自增长][唯一键][外键]详细讲解

---

1.主键

• 主键：primary key用来唯一的束缚该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个
  
  
  
  • 但不意味着一个表中的主键，只能添加给一列
    
    
    
    • 一个主键可以被添加到一列，或者多列上 – 复合主键
      
    
    
  
  
• 示例：创建表的时候直接在字段上指定主键
  
  1. mysql> create table t4 (
  2. -> id int unsigned primary key comment '学号不能为空',
  3. -> name varchar(20) not null);
  5. mysql> desc t4;
  6. +-------+------------------+------+-----+---------+-------+
  7. | Field | Type             | Null | Key | Default | Extra |
  8. +-------+------------------+------+-----+---------+-------+
  9. | id    | int(10) unsigned | NO   | PRI | NULL    |       | # key中pri表示该字段是主键
  10. | name  | varchar(20)      | NO   |     | NULL    |       |
  11. +-------+------------------+------+-----+---------+-------+

  复制代码
• 主键束缚：主键对应的字段中不能重复，一旦重复，操作失败
  
  1. mysql> insert into t4 values(1, 'aaa');
  2. mysql> insert into t4 values(1, 'aaa');
  3. ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

  复制代码
• 当表创建好以后但是没有主键的时候，可以再次追加主键
  
  1. alter table 表名 add primary key(字段列表)

  复制代码
• 删除主键
  
  1. alter table 表名 drop primary key;

  复制代码
  1. mysql> alter table t4 drop primary key;
  2. mysql> desc t4;
  3. +-------+------------------+------+-----+---------+-------+
  4. | Field | Type             | Null | Key | Default | Extra |
  5. +-------+------------------+------+-----+---------+-------+
  6. | id    | int(10) unsigned | NO   |     | NULL    |       |
  7. | name  | varchar(20)      | NO   |     | NULL    |       |
  8. +-------+------------------+------+-----+---------+-------+

  复制代码
• 复合主键：在创建表的时候，在全部字段之后，使用primary key来创建主键，如果有多个字段作为主键，可以使用复合主键
  
  1. mysql> create table t5(
  2. -> id int unsigned,
  3. -> course char(10) comment '课程代码',
  4. -> score tinyint unsigned default 60 comment '成绩',
  5. -> primary key(id, course) <= id和course为复合主键
  6. -> );
  8. mysql> desc t5;
  9. +--------+---------------------+------+-----+---------+-------+
  10. | Field  | Type                | Null | Key | Default | Extra |
  11. +--------+---------------------+------+-----+---------+-------+
  12. | id     | int(10) unsigned    | NO   | PRI | 0       |       | <= 这两列合成主键
  13. | course | char(10)            | NO   | PRI |         |       |
  14. | score  | tinyint(3) unsigned | YES  |     | 60      |       |
  15. +--------+---------------------+------+-----+---------+-------+
  17. mysql> insert into t5 (id,course)values(1, '123');
  19. mysql> insert into t5 (id,course)values(1, '123');
  20. ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY' <= 主键冲突

  复制代码

---

2.自增长

1.是什么？

• auto_increment：
  
  
  
  • 当对应的字段，不给值，会自动的被体系触发，体系会从当前字段中已经有的最大值 +1操作，得到一个新的差别的值
    
  • 通常和主键搭配使用，作为逻辑主键
    
  
  
• 自增长的特点：
  
  
  
  • 任何一个字段要做自增长，前提是自己是一个索引(key一栏有值)
    
  • 自增长字段必须是整数
    
  • 一张表最多只能有一个自增长
    
  
  
• 示例：
  1. mysql> create table t6(
  2. -> id int unsigned primary key auto_increment,
  3. -> name varchar(10) not null default ''
  4. -> );
  6. mysql> insert into tt21(name) values('a');
  7. mysql> insert into tt21(name) values('b');
  9. mysql> select * from tt21;
  10. +----+------+
  11. | id | name |
  12. +----+------+
  13. | 1  | a    |
  14. | 2  | b    |
  15. +----+------+

  复制代码
  
  
  • 在插入后获取前次插入的 AUTO_INCREMENT 的值(批量插入获取的是第一个值)
    1. mysql > select last_insert_id();
    2. +------------------+
    3. | last_insert_id() |
    4. +------------------+
    5. | 1                |
    6. +------------------+

    复制代码
  
  

2.索引

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

---

3.唯一键

• 一张表中每每有很多字段必要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以办理表中有多个字段必要唯一性束缚的问题
  
• 唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较
  
• 关于唯一键和主键的区别，可以简朴理解成：
  
  
  
  • 主键更多的是标识唯一性的
    
  • 而唯一键更多的是保证在业务上，不要和别的信息出现重复
    
  
  
• 乍一听似乎没啥区别，举一个例子
  
  
  
  • 假设一个场景(仅仅为了助理解)
    
  • 比如在公司，必要一个员工管理体系，体系中有一个员工表，员工表中有两列信息，一个身份证号码，一个是员工工号，可以选择身份号码作为主键
    
  • 而设计员工工号的时候，必要一种束缚：全部的员工工号都不能重复
    
  • 具体指的是在公司的业务上不能重复，我们设计表的时候，必要这个束缚，那么就可以将员工工号设计成为唯一键
    
  
  
• 一样平常而言，建议将主键设计成和当前业务无关的字段，这样，当业务调解的时候，我们可以尽量不会对主键做过大的调解
  
• 示例：
  1. mysql> create table student (
  2. -> id char(10) unique comment '学号，不能重复，但可以为空',
  3. -> name varchar(10)
  4. -> );
  6. mysql> insert into student(id, name) values('01', 'aaa');
  7. mysql> insert into student(id, name) values('01', 'bbb'); <=唯一约束不能重复
  8. ERROR 1062 (23000): Duplicate entry '01' for key 'id'
  10. mysql> insert into student(id, name) values(null, 'bbb'); -- 但可以为空
  12. mysql> select * from student;
  13. +------+------+
  14. | id   | name |
  15. +------+------+
  16. | 01   | aaa  |
  17. | NULL | bbb  |
  18. +------+------+

  复制代码

---

4.外键

1.为什么？

• 如下面示例，即便学生表通过class_id设置了和班级表的接洽，但是不具备任何的束缚力
  
  
  
  • 大概错误地插入一个不存在班级的学生
    
  • 大概删除了一个还有学生的班级
    
  
  
• class_id只有外键之名(关联关系)，没有外键之实(束缚关系)
  
  
  
  
  
  
  
• 外键：
  
  
  
  • 从表和主表的关联关系
    
  • 产生外键束缚
    
  
  

2.是什么？

• 外键用于界说主表和从表之间的关系：
  
  
  
  • 外键束缚重要界说在从表上
    
  • 主表则必须是有主键束缚或unique束缚
    
  • 当界说外键后，要求外键列数据必须在主表的主键列存在或为NULL
    
  
  
• 语法：
  
  1. foreign key (字段名) references 主表(列)

  复制代码
• 示例：
  
  
  
  
• 如果将班级表中的数据都设计在每个学生表的后面，那就会出现数据冗余
  
  
  
  • 所以只要设计成让stu->class_id和myclass_id形成关联关系 --> 外键束缚
    
  
  
• 对上面的示意图举行设计：
  

• 先创建主键表
  1. create table myclass (
  2. id int primary key,
  3. name varchar(30) not null comment'班级名'
  4. );

  复制代码
• 再创建从表
  1. create table stu (
  2. id int primary key,
  3. name varchar(30) not null comment '学生名',
  4. class_id int,
  5. foreign key (class_id) references myclass(id)
  6. );

  复制代码
• 正常插入数据
  1. mysql> insert into myclass values(10, '高三(9)班'),(20, '高三(19)班');
  2. Query OK, 2 rows affected (0.03 sec)
  3. Records: 2 Duplicates: 0 Warnings: 0
  5. mysql> insert into stu values(100, '张三', 10),(101, '李四',20);
  6. Query OK, 2 rows affected (0.01 sec)
  7. Records: 2 Duplicates: 0 Warnings: 0

  复制代码
• 插入一个班级号为30的学生，由于没有这个班级，所以插入不乐成
  1. mysql> insert into myclass values(10, '高三(9)班'),(20, '高三(19)班');
  2. Query OK, 2 rows affected (0.03 sec)
  3. Records: 2 Duplicates: 0 Warnings: 0
  5. mysql> insert into stu values(100, '张三', 10),(101, '李四',20);
  6. Query OK, 2 rows affected (0.01 sec)
  7. Records: 2 Duplicates: 0 Warnings: 0

  复制代码
• 插入班级id为null，比如来了一个学生，现在还没有分配班级
  1. 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)
    
  
  
• 要求：
  
  
  
  • 每个表的主外键
    
  • 客户的姓名不能为空值
    
  • 邮箱不能重复
    
  • 客户的性别(男，女)
    
  1. -- 创建数据库
  2. create database if not exists bit32mall
  3. default character set utf8 ;
  5. -- 选择数据库
  6. use bit32mall;
  8. -- 创建数据库表
  9. -- 商品
  10. create table if not exists goods
  11. (
  12. goods_id int primary key auto_increment comment '商品编号',
  13. goods_name varchar(32) not null comment '商品名称',
  14. unitprice int not null default 0 comment '单价，单位分',
  15. category varchar(12) comment '商品分类',
  16. provider varchar(64) not null comment '供应商名称'
  17. );
  19. -- 客户
  20. create table if not exists customer
  21. (
  22. customer_id int primary key auto_increment comment '客户编号',
  23. name varchar(32) not null comment '客户姓名',
  24. address varchar(256) comment '客户地址',
  25. email varchar(64) unique key comment '电子邮箱',
  26. sex enum('男','女') not null comment '性别',
  27. card_id char(18) unique key comment '身份证'
  28. );
  30. -- 购买
  31. create table if not exists purchase
  32. (
  33. order_id int primary key auto_increment comment '订单号',
  34. customer_id int comment '客户编号',
  35. goods_id int comment '商品编号',
  36. nums int default 0 comment '购买数量',
  37. foreign key (customer_id) references customer(customer_id),
  38. foreign key (goods_id) references goods(goods_id)
  39. );

  复制代码
  
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4