【MySQL】 表的约束(下)

欢迎拜访：雾里看山-CSDN博客
本篇主题：【MySQL】 表的约束(下)
发布时间：2025.2.12
隶属专栏：MySQL
  

  
自增长

基本介绍

auto_increment：当对应的字段，不给值，会主动的被体系触发，体系会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配利用，作为逻辑主键。
自增长的特点:

• 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
  
• 自增长字段必须是整数
  
• 一张表最多只能有一个自增长
  

案例利用

1. mysql> create table if not exists t16(
2.     -> id int unsigned primary key auto_increment,
3.     -> name varchar(20) not null
4.     -> );
5. Query OK, 0 rows affected (0.02 sec)
7. mysql> desc t16;
8. +-------+------------------+------+-----+---------+----------------+
9. | Field | Type             | Null | Key | Default | Extra          |
10. +-------+------------------+------+-----+---------+----------------+
11. | id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
12. | name  | varchar(20)      | NO   |     | NULL    |                |
13. +-------+------------------+------+-----+---------+----------------+
14. 2 rows in set (0.01 sec)

复制代码

自增长的值默认从 1 开始

1. mysql> insert into t16 (name) values ('a');
2. Query OK, 1 row affected (0.00 sec)
4. mysql> insert into t16 (name) values ('b');
5. Query OK, 1 row affected (0.00 sec)
7. mysql> insert into t16 (name) values ('c');
8. Query OK, 1 row affected (0.01 sec)
10. mysql> select * from t16;
11. +----+------+
12. | id | name |
13. +----+------+
14. |  1 | a    |
15. |  2 | b    |
16. |  3 | c    |
17. +----+------+
18. 3 rows in set (0.00 sec)

复制代码

当自增长的值被更改以后，会从最大值开始自增长

1. mysql> insert into t16 (id, name) values (1000, 'd');
2. Query OK, 1 row affected (0.01 sec)
4. mysql> select * from t16;
5. +------+------+
6. | id   | name |
7. +------+------+
8. |    1 | a    |
9. |    2 | b    |
10. |    3 | c    |
11. | 1000 | d    |
12. +------+------+
13. 4 rows in set (0.00 sec)
15. mysql> insert into t16 (id, name) values (1000, 'd');
16. ERROR 1062 (23000): Duplicate entry '1000' for key 'PRIMARY'
17. mysql> insert into t16 (name) values ('d');
18. Query OK, 1 row affected (0.00 sec)
20. mysql> select * from t16;
21. +------+------+
22. | id   | name |
23. +------+------+
24. |    1 | a    |
25. |    2 | b    |
26. |    3 | c    |
27. | 1000 | d    |
28. | 1001 | d    |
29. +------+------+
30. 5 rows in set (0.00 sec)

复制代码

auto_increment值的查找

查找下一次插入时的值

1. mysql> show create table t16 \G
2. *************************** 1. row ***************************
3.        Table: t16
4. Create Table: CREATE TABLE `t16` (
5.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
6.   `name` varchar(20) NOT NULL,
7.   PRIMARY KEY (`id`)
8. ) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8
9. 1 row in set (0.00 sec)

复制代码

查找末了一次插入时的值

1. mysql> select last_insert_id();
2. +------------------+
3. | last_insert_id() |
4. +------------------+
5. |              502 |
6. +------------------+
7. 1 row in set (0.02 sec)

复制代码

创建表时设置 auto_increment值

1. mysql> create table t17(
2.     -> id int unsigned primary key auto_increment,
3.     -> name varchar(20) not null
4.     -> )auto_increment=500;
5. Query OK, 0 rows affected (0.02 sec)
7. mysql> insert into t17 (name) values ('a');
8. Query OK, 1 row affected (0.00 sec)
10. mysql> insert into t17 (name) values ('b');
11. Query OK, 1 row affected (0.01 sec)
13. mysql> insert into t17 (name) values ('c');
14. Query OK, 1 row affected (0.00 sec)
16. mysql> select * from t17;
17. +-----+------+
18. | id  | name |
19. +-----+------+
20. | 500 | a    |
21. | 501 | b    |
22. | 502 | c    |
23. +-----+------+
24. 3 rows in set (0.00 sec)

复制代码

索引

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

介绍

一张表中有通常有很多字段必要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段必要唯一性约束的题目。
唯一键的本质和主键差不多，唯一键答应为空，而且可以多个为空，空字段不做唯一性比较。
利用

1. mysql> create table stu(
2.     -> id char(20) unique comment '这是一个学生的唯一键',
3.     -> name varchar(32) not null
4.     -> );
5. Query OK, 0 rows affected (0.01 sec)
7. mysql> desc stu;
8. +-------+-------------+------+-----+---------+-------+
9. | Field | Type        | Null | Key | Default | Extra |
10. +-------+-------------+------+-----+---------+-------+
11. | id    | char(20)    | YES  | UNI | NULL    |       |
12. | name  | varchar(32) | NO   |     | NULL    |       |
13. +-------+-------------+------+-----+---------+-------+
14. 2 rows in set (0.00 sec)
16. mysql> insert into stu (id, name) values ('12345', '张三');
17. Query OK, 1 row affected (0.00 sec)
19. mysql> insert into stu (id, name) values ('12345', '李四');
20. ERROR 1062 (23000): Duplicate entry '12345' for key 'id'
21. mysql> insert into stu (id, name) values (NULL, '李四');
22. Query OK, 1 row affected (0.01 sec)
24. mysql> insert into stu (id, name) values (NULL, '王五');
25. Query OK, 1 row affected (0.01 sec)
27. mysql> select * from stu;
28. +-------+--------+
29. | id    | name   |
30. +-------+--------+
31. | 12345 | 张三   |
32. | NULL  | 李四   |
33. | NULL  | 王五   |
34. +-------+--------+
35. 3 rows in set (0.00 sec)

复制代码

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

我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。

1. mysql> create table student(
2.     -> id char(20) primary key,
3.     -> name varchar(32) not null,
4.     -> telphone char(20) unique key,
5.     -> qq varchar(64) unique key
6.     -> );
7. Query OK, 0 rows affected (0.02 sec)
9. mysql> desc student;
10. +----------+-------------+------+-----+---------+-------+
11. | Field    | Type        | Null | Key | Default | Extra |
12. +----------+-------------+------+-----+---------+-------+
13. | id       | char(20)    | NO   | PRI | NULL    |       |
14. | name     | varchar(32) | NO   |     | NULL    |       |
15. | telphone | char(20)    | YES  | UNI | NULL    |       |
16. | qq       | varchar(64) | YES  | UNI | NULL    |       |
17. +----------+-------------+------+-----+---------+-------+
18. 4 rows in set (0.00 sec)
19. mysql> insert into student values ('12345', '张三', '1341234', '1234');
20. Query OK, 1 row affected (0.00 sec)
22. mysql> insert into student values ('12345', '李四', '1341234', '1234');
23. ERROR 1062 (23000): Duplicate entry '12345' for key 'PRIMARY'
24. mysql> insert into student values ('12346', '李四', '1341234', '1234');
25. ERROR 1062 (23000): Duplicate entry '1341234' for key 'telphone'
26. mysql> insert into student values ('12346', '李四', '1341235', '1234');
27. ERROR 1062 (23000): Duplicate entry '1234' for key 'qq'
28. mysql> insert into student values ('12346', '李四', '1341235', '1235');
29. Query OK, 1 row affected (0.01 sec)
31. mysql> select * from student;
32. +-------+--------+----------+------+
33. | id    | name   | telphone | qq   |
34. +-------+--------+----------+------+
35. | 12345 | 张三   | 1341234  | 1234 |
36. | 12346 | 李四   | 1341235  | 1235 |
37. +-------+--------+----------+------+
38. 2 rows in set (0.00 sec)

复制代码

非空唯一键

唯一键也可以设置为非空，在功能上类似于主键

1. mysql> alter table student modify telphone char(20) unique not null;
2. Query OK, 0 rows affected, 1 warning (0.06 sec)
3. Records: 0  Duplicates: 0  Warnings: 1
5. mysql> alter table student modify qq char(20) unique not null;
6. Query OK, 2 rows affected, 1 warning (0.03 sec)
7. Records: 2  Duplicates: 0  Warnings: 1
9. mysql> desc student;
10. +----------+-------------+------+-----+---------+-------+
11. | Field    | Type        | Null | Key | Default | Extra |
12. +----------+-------------+------+-----+---------+-------+
13. | id       | char(20)    | NO   | PRI | NULL    |       |
14. | name     | varchar(32) | NO   |     | NULL    |       |
15. | telphone | char(20)    | NO   | UNI | NULL    |       |
16. | qq       | char(20)    | NO   | UNI | NULL    |       |
17. +----------+-------------+------+-----+---------+-------+
18. 4 rows in set (0.00 sec)
20. mysql> insert into student values ('54321', '王五', '1234235', '12123');
21. Query OK, 1 row affected (0.00 sec)
23. mysql> insert into student values ('54322', '赵六', NULL, '12124');
24. ERROR 1048 (23000): Column 'telphone' cannot be null
25. mysql> insert into student values ('54322', '赵六', '1234236', NULL);
26. ERROR 1048 (23000): Column 'qq' cannot be null

复制代码

外键

介绍

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique 约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。
语法：
foreign key (字段名) references 主表(列)
案例：

创建主表并插入数据

1. mysql> create table if not exists class(
2.     -> id int primary key,
3.     -> name varchar(32) not null
4.     -> );
5. Query OK, 0 rows affected (0.02 sec)
7. mysql> desc class;
8. +-------+-------------+------+-----+---------+-------+
9. | Field | Type        | Null | Key | Default | Extra |
10. +-------+-------------+------+-----+---------+-------+
11. | id    | int(11)     | NO   | PRI | NULL    |       |
12. | name  | varchar(32) | NO   |     | NULL    |       |
13. +-------+-------------+------+-----+---------+-------+
14. 2 rows in set (0.00 sec)
16. ysql> insert into class values (1,'通信101');
17. Query OK, 1 row affected (0.01 sec)
19. mysql> insert into class values (2,'通信102');
20. Query OK, 1 row affected (0.00 sec)
22. mysql> select * from class;
23. +----+-----------+
24. | id | name      |
25. +----+-----------+
26. |  1 | 通信101   |
27. |  2 | 通信102   |
28. +----+-----------+
29. 2 rows in set (0.00 sec)

复制代码

创建从表并确认从属关系

1. mysql> create table student(
2.     -> id int unsigned primary key,
3.     -> name varchar(20) not null,
4.     -> telphone varchar(32) unique key,
5.     -> class_id int,
6.     -> foreign key(class_id) referemces class(id)
7.     -> );
8. Query OK, 0 rows affected (0.04 sec)
10. mysql> desc student;
11. +----------+------------------+------+-----+---------+-------+
12. | Field    | Type             | Null | Key | Default | Extra |
13. +----------+------------------+------+-----+---------+-------+
14. | id       | int(10) unsigned | NO   | PRI | NULL    |       |
15. | name     | varchar(20)      | NO   |     | NULL    |       |
16. | telphone | varchar(32)      | YES  | UNI | NULL    |       |
17. | class_id | int(11)          | YES  | MUL | NULL    |       |
18. +----------+------------------+------+-----+---------+-------+
19. 4 rows in set (0.00 sec)

复制代码

从表的插入受主表的约束

1. mysql> insert into student values (100, '张三', '12345', 1);
2. Query OK, 1 row affected (0.00 sec)
4. mysql> insert into student values (101, '李四', '12346', 2);
5. Query OK, 1 row affected (0.00 sec)
7. mysql> insert into student values (102, '王五', '12347', 3);
8. ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_db`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
9. mysql> insert into student values (102, '王五', '12347', NULL);
10. Query OK, 1 row affected (0.00 sec)
12. mysql> select * from student;
13. +-----+--------+----------+----------+
14. | id  | name   | telphone | class_id |
15. +-----+--------+----------+----------+
16. | 100 | 张三   | 12345    |        1 |
17. | 101 | 李四   | 12346    |        2 |
18. | 102 | 王五   | 12347    |     NULL |
19. +-----+--------+----------+----------+
20. 3 rows in set (0.00 sec)

复制代码

主表也受从表的约束

1. mysql> delete from class where id=1;
2. ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test_db`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`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)
  

要求：

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

1. -- 创建数据库
2. create database if not exists bit32mall
3. default character set utf8 ;
4. -- 选择数据库
5. use bit32mall;
6. -- 创建数据库表
7. -- 商品
8. create table if not exists goods
9. (
10. goods_id int primary key auto_increment comment '商品编号',
11. goods_name varchar(32) not null comment '商品名称',
12. unitprice int not null default 0 comment '单价，单位分',
13. category varchar(12) comment '商品分类',
14. provider varchar(64) not null comment '供应商名称'
15. );
16. -- 客户
17. create table if not exists customer
18. (
19. customer_id int primary key auto_increment comment '客户编号',
20. name varchar(32) not null comment '客户姓名',
21. address varchar(256) comment '客户地址',
22. email varchar(64) unique key comment '电子邮箱',
23. 比特就业课
24. sex enum('男','女') not null comment '性别',
25. card_id char(18) unique key comment '身份证'
26. );
27. -- 购买
28. create table if not exists purchase
29. (
30. order_id int primary key auto_increment comment '订单号',
31. customer_id int comment '客户编号',
32. goods_id int comment '商品编号',
33. nums int default 0 comment '购买数量',
34. foreign key (customer_id) references customer(customer_id),
35. foreign key (goods_id) references goods(goods_id)
36. );

复制代码

  ⚠️ 写在末了：以上内容是我在学习以后得一些总结和概括，如有错误或者必要补充的地方欢迎各位大佬评论或者私信我交流！！！

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