MySQL篇【5】——约束

最新推荐文章于 2024-09-11 23:47:40 发布
最新推荐文章于 2024-09-11 23:47:40 发布
阅读量571 收藏 2
点赞数 2
分类专栏： MySQL 文章标签： mysql 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/JayceSun/article/details/127278064
版权

目录


在数据库的实际操作中难免会有一些操作不规范的地方，比方说在一张学生成绩表中，成绩的最高值为100，那么如果在这张表中如果出现了超过100分的成绩那显然是不合理的，因次需要添加约束来避免这些错误

表的约束

真正约束字段的是数据类型（比方说int类型只能存储整数），但是数据类型约束很单一，需要一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是tel，要求是唯一的。
表的约束很多，本篇主要介绍：null/ not null, default, comment, zerofill, primary key, auto_increment, unique key

空属性

  • 两个值：null(默认的)和not null
  • 数据库默认字段基本都是字段可以为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算
mysql> select null;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> select 1+null;
+--------+
| 1+null |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

案例：
创建一个班级表，包含班级名和班级所在的教室，班级和教室名字不能为空。

mysql> create table myclass(
    -> class_name varchar(20) not null,
    -> class_room varchar(10) not null
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |     | NULL    |       |
| class_room | varchar(10) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into myclass(class_name,class_room) values('class1', 'a101');
Query OK, 1 row affected (0.00 sec)

mysql> insert into myclass(class_name,class_room) values('class2');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

默认值

mysql> create table tt13(
    -> name varchar(20) not null,
    -> age tinyint unsigned default 0,
    -> sex char(2) default '男'
    -> );

mysql> desc tt13;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | 0       |       |
| sex   | char(2)             | YES  |     | 男      |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into tt13(name) values('jack');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tt13;
+------+------+------+
| name | age  | sex  |
+------+------+------+
| jack |    0 | 男   |
+------+------+------+
1 row in set (0.00 sec)

列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来提醒程序员或DBA

mysql> create table tt14( name varchar(20) not null comment'姓名', age tinyint unsigned default 0 comment '年龄', sex char(2) default '男' comment '姓别' );
Query OK, 0 rows affected (0.07 sec)

通过desc看不到注释信息，但可以通过show看到：

mysql> show create table tt14\G
*************************** 1. row ***************************
       Table: tt14
Create Table: CREATE TABLE `tt14` (
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned DEFAULT '0' COMMENT '年龄',
  `sex` char(2) DEFAULT '男' COMMENT '姓别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

zerofill

在创建表时，有时会看见数字类型后面会加一个数字比方说下面的int(10),这代表什么意思？

mysql> create table tt15(
    -> a int(10) unsigned,
    -> b int(10) unsigned
    -> );
Query OK, 0 rows affected (0.06 sec)

如果没有zerofill这个属性，括号内的数字时毫无意义的

// 没有zerofill属性
mysql> insert into tt15(a,b) values(1,2);
Query OK, 1 row affected (0.04 sec)

mysql> select * from tt15;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)
//添加zerofill属性
mysql> alter table tt15 change a a int(5) unsigned zerofill;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tt15\G
*************************** 1. row ***************************
       Table: tt15
Create Table: CREATE TABLE `tt15` (
  `a` int(5) unsigned zerofill DEFAULT NULL,
  `b` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from tt15;
+-------+------+
| a     | b    |
+-------+------+
| 00001 |    2 |
+-------+------+
1 row in set (0.00 sec)

这次可以看到a的值由1变成00001，这就是zerofill属性的作用，如果宽度小于设定的宽度，自动填充0，不过这只是显示的结果，在MySQL中实际存储的还是1，可以用hex()（十六进制转换）函数来证明

mysql> select a, hex(a) from tt15;
+-------+--------+
| a     | hex(a) |
+-------+--------+
| 00001 | 1      |
+-------+--------+
1 row in set (0.00 sec)

主键

主键primary key用来约束该字段里的数据不能重复，不能为空，一张表最多只能由一个主键；主键所在的列通常是整数类型。
案例：

  • 创建表的时候直接在字段上指定主键
mysql> create table tt16( id int unsigned primary key, name char(20) not null );
Query OK, 0 rows affected (0.04 sec)

mysql> desc tt16;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | char(20)         | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
  • 主键约束：主键对应的字段中不能重复，一旦重复，操作失败
mysql> insert into tt16(id,name) values(1,'aaa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tt16(id,name) values(1,'aaa');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
  • 当表创建好以后但是没有主键的时候，可以再次追加主键
alter table tablename add primary key(coloum);
  • 删除主键
mysql> alter table tt16 drop primary key;
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc tt16;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   |     | NULL    |       |
| name  | char(20)         | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
  • 复合主键
    在创建表的时候，在所有字段之后，使用primary key（主键字段列表）来创建主键，如果有多个字段作为主键，可以使用复合主键
mysql> create table tt17(
    -> id int unsigned,
    -> course char(10),
    -> score tinyint unsigned default 60,
    -> primary key(id,course)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc tt17;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | int(10) unsigned    | NO   | PRI | NULL    |       |
| course | char(10)            | NO   | PRI | NULL    |       |
| score  | tinyint(3) unsigned | YES  |     | 60      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

#插入数据
mysql> insert into tt17(id,course,score) values(1,'123',60);
Query OK, 1 row affected (0.01 sec)

mysql> insert into tt17(id,course,score) values(1,'123',60);
ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY'
mysql> insert into tt17(id,course,score) values(2,'123',60);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt17(id,course,score) values(1,'124',60);
Query OK, 1 row affected (0.01 sec)

从上面的例子可以看出复合主键插入时，只有复合主键中的每一个数据都与另一条数据完全匹配才算主键冲突。

自增长

auto_increment：当对应的字段不给值，会自动被系统触发，系统会用当先字段中已有的最大值做+1操作，得到一个新的值，通常和主键搭配使用，作为逻辑主键。

  • 一个字段要做自增长，前提是本身是一个索引（key一栏有值）
  • 自增长字段必须是整数
  • 一张表最多只能有一个自增长
    案例：
mysql> create table tt18( id int unsigned primary key auto_increment, name varchar(10)not null default '' );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tt18(name) values('a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tt18(name) values('b');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tt18;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

在插入后获取上次插入的AUTO_INCREMENT的值（批量插入获取的是第一个值）

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

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

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

关于索引，会在后面的文章更加详细地介绍。

唯一键

一张表中往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，不过唯一键就可以解决表中有多个字段需要唯一性约束的问题。
唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

唯一键和主键的区别：
主键更多标识唯一性，而唯一键更多在保证业务，不要和别的信息出现重复，下面来举一个例子:
某学校要做一个学生管理系统，需要保存的数据包括学生的手机号，同时需要一个字段来作为学生的主键。那么我们可以用学生的学号来作为主键吗？学生的手机号可能是会发生变化的，那么当学生的手机号发生变化时，系统的主键也会发生变化，在业务调整时，尽量不要对主键做过多调整。在上面的例子中，不同学生的手机号其实只需要保证唯一性就行了，所以我们可以选择手机号作为唯一键。
那么我们应该选择什么值来作为主键呢？尽量选择与业务无关的数据！也就是说，我们可以在存储学生信息时，新建一列id，只用来作为数据的主键
案例：

mysql> create table stu( id char(10) unique, name varchar(10) );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into stu(id,name) values('01','aaa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu(id,name) values('01','bbb'); //不可以插入相同的值
ERROR 1062 (23000): Duplicate entry '01' for key 'id'
mysql> insert into stu(id,name) values(null,'bbb'); //可以为空
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu;
+------+------+
| id   | name |
+------+------+
| 01   | aaa  |
| NULL | bbb  |
+------+------+
2 rows in set (0.00 sec)

外键

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

foreign key coloum references 主表(coloum);

在这里插入图片描述

  • 创建主键表
mysql> create table class(
    -> id int primary key,
    -> name varchar(30) not null
    -> );
  • 创建从表
mysql> create table stu(
    -> id int primary key,
    -> name varchar(30) not null,
    -> class_id int,
    -> foreign key(class_id) references class(id)
    -> );
  • 正常插入数据
mysql> insert into class value(10, '向日葵班'),(20, '玫瑰班');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into stu values(1, '野原新之助',10),(2, '阿豹', 20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
  • 插入一个班级号为30 的学生，因为没有这个班级，所以插入不成功
mysql> insert into stu values(3, 'ssj', 30);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`restrain`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
  • 插入班级id为null，比如来了一个学生，目前还没有分配班级
mysql> insert into stu values(3, '风间彻', null);
Query OK, 1 row affected (0.01 sec)

如何理解外键约束

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

JayceSun449
关注
  • 2
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL数据库笔记6——约束Constraint
weixin_44384631的博客
05-26 1392
1.什么是约束（Constraint）       在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。 2.常见的约束 非空约束（not null）：约束的字段不能为NULL。 唯一约束（unique）：约束的字段不能重复。 主键约束（primary key，PK）：约束的字段既不能为NULL，也不能重复。 外键约束（foreign key，FK） 检查约束（check）:Oracle数据库有c
MySQL基础介绍2——约束.md
06-15
1. DQL:查询语句 1. 排序查询 2. 聚合函数 3. 分组查询 4. 分页查询 2. 约束 3. 多表之间的关系 4. 范式 5. 数据库的备份和还原
MYSQL数据库基础篇——约束
最新发布
qq_74177011的博客
09-11 15
全网最全：Mysql六种约束详解
MySQL的约束——外键约束
热门推荐
m0_63217468的博客
08-22 1万+
MySQL的约束——外键约束
数据库（MySQL）—— 约束
qq_67693066的博客
05-02 1554
数据库（MySQL）—— 约束
Mysql基础篇（5）—— 约束
树叶的一生，只是为了归根么？
01-17 770
为了保证数据的完整性，防止数据库中存在不符合语义的数据存在，是表级的强制规定。
MySQL基础篇之约束——外键约束
kuaixiao0217的博客
11-27 695
主要介绍了MySQL的约束，其中主要就是外键约束
MySQL —— 约束
熵减玩家
08-07 1245
介绍MySQL的约束
MYSQL————数据库的约束
2301_80251684的博客
08-24 981
1.：指示某列不能存储null值2.：保证某列的每行必须有唯一值3.：规定没有给列赋值时的默认值4.：not null和unique的结合。确保某列（或两个或多个列的结合）有唯一标识，有助于更容易更快捷的找到表中的一个特定记录。5.：保证一个表中的数据匹配另一个表中的值的参照完整性。6.：保证列中的值符合指定的条件。对于MYSQL数据库，对check子句进行分析，但是忽略check子句。
MySQL基础——约束
霖hero
06-15 539
上篇文章我们学习了MySQL基础——函数，这篇文章我们学习MySQL基础——约束。约束是作用于表中字段上的规则，用于限制存储在表中的数据，其目的是保证数据库中数据的正确、有效性和完整性。常用的约束如下表：注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。接下来我们通过案例来演示在创建数据表时设置约束。案例需求如下：示例代码如下： 如下图所示：这样就成功在创建表的时候为字段创建约束。外键是用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。添加外键语法格式如下： 示例代码如
数据库MySQL —— 约束
hdakj22的博客
03-22 1091
约束概述、约束分类：非空约束、唯一约束、主键约束、默认约束、检查约束以及外键约束
mysql学习———表的约束
12-14
在本篇文章中，我们将深入探讨五种主要的MySQL表约束：主键约束、非空约束、唯一约束、默认约束以及自动增加约束。 **主键约束**： 主键是用于唯一标识表中每一行记录的字段。在MySQL中，主键约束可以通过`PRIMATY ...
（2）MySQL——————DML，DDL，数据类型，约束以及事务
12-14
1，数据操纵语言 1.1，从其它表中拷贝数据 ...5，事务 5.1，什么是约束 5.2，事务的概念 5.3，事务的使用步骤 5.4，事务的并发问题 5.5，数据库提供的4 种事务隔离级别 5.6，SAVEPOINT的使用 数据操纵语言 DML(D
MySQL——表的约束
11-13
表的约束，主键 候选键 非空约束 默认值，创建主键（表级方式和列级方式 单列主键，复合主键）修改主键 删除主键；创建候选键 修改候选键 删除候选键；创建非空约束 删除非空约束；添加默认值 修改默认值；
mysql用时间戳还是时间存储比较好
qq_42572322的博客
09-05 520
TIMESTAMP 通常占用的空间比 DATETIME 少，如果你对存储空间有限制，或者你需要存储大量的时间戳数据，TIMESTAMP 可能是更好的选择。- 如果你的应用需要在不同的时区中使用时间数据，或者需要自动处理夏令时的变化，使用 TIMESTAMP 可能更方便。- 考虑到不同的数据库系统和应用程序可能对时间类型的支持不同，选择一个能够满足你当前和未来需求的时间类型是很重要的。- 对于需要频繁进行日期和时间范围查询的场景，DATETIME 类型可能更适合，因为它允许更灵活的日期和时间计算。
MySQL表操作及约束
2301_77479435的博客
09-06 1057
MySQL表操作及约束
【大数据】Canal实现MySQL数据增量同步至Kafka：原理与配置解析
一个不断前行的程序者
09-04 1301
Canal是一款开源的数据库增量日志解析组件，主要用于监控数据库数据变更，并将变更数据同步到其他存储介质。Canal通过模拟MySQL Slave的交互协议，实时获取数据库的增量更新，从而实现数据同步。本文将介绍如何使用Canal将MySQL的数据通过监听Binlog，增量发送到Kafka。Binlog（Binary Log）是MySQL数据库的二进制日志，记录了所有对数据库数据的修改操作。开启Binlog后，MySQL会实时将数据变更记录到Binlog文件中。
深入解析Go语言中的条件控制与数据处理
一个被知识诅咒的人
09-08 1089
本文详细介绍了Go语言中的switch语句及其在正则表达式匹配中的应用，探讨了如何通过math/big包进行高精度圆周率计算。此外，文章还展示了如何实现一个简易键值对存储系统，并通过encoding/json和encoding/xml包处理常见的JSON和XML数据格式。通过这些示例和知识点，读者将能掌握Go语言中条件控制和数据处理的高级应用，提升编写高效代码的能力。
MySQL的 where 1=1会不会影响性能
ning的博客
09-07 585
通过分析和实验，我们可以得出结论：在现代的MySQL版本中，使用where 1=1并不会对查询性能产生显著影响，因为MySQL的优化器会对其进行常量折叠优化。然而，选择最合适的方法还需要考虑你的具体使用场景和ORM框架。在任何情况下，编写清晰、可维护的代码始终是最重要的。版权声明：本博客内容为原创，转载请保留原文链接及作者信息。参考文章MySQL中where 1=1真的会影响性能么？MySQL的 where 1=1会不会影响性能？看完官方文档就悟了！
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

JayceSun449

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值