【ONE·MySQL || 数据类型 && 表的约束】

总言

  主要内容：介绍MySQL中的常见数据类型（数值类型、文本二进制类型、时间日期、字符串类型），以及对表的约束（非空约束、默认约束、列描述、零填充约束、自增长约束、主键约束、唯一键约束、外键约束）。
  
  

  
  
  
  
  
  

1、数据类型

  当前博文中MySQL版本号：

[root@VM-4-3-centos ~]# mysql --version
mysql  Ver 14.14 Distrib 5.7.44, for Linux (x86_64) using  EditLine wrapper

  
  
  

1.1、数值类型

1.1.1、整数类型

1.1.1.1、总览

类型	大小（字节）	范围（有符号）	范围（无符号）	用途
TINYINTT	1 Bytes	（-128，127）	（0，255）	小整数值
SMALLINT	2 Bytes	（-32 768，32 767）	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	（-8 388 608，8 388 607）	（0，16 777 215）	大整数值
INT或INTEGER	4 Bytes	（-2 147 483 648，2 147 483 647）	（0，4 294 967 295）	大整数值
BIGINT	8 Bytes	（-9,223,372,036,854,775,808，9 223 372 036 854 775 80）	（0，18 446 744 073 709 551 615）	极大整数值

  在MySQL中，整型分有符号和无符号两类，默认整型是有符号的，可以通过UNSIGNED来说明某个字段是无符号类型。
  
  

1.1.1.2、tinyint（细节演示）

  1）、说明
  1、和C、C++语言不同，mysql中，如果插入数据不符合要求，直接终止操作，而不是截断。原因：维护数据可信度，倒逼程序员必须遵守规则。
  2、这也体现了mysql中，数据类型的作用：①满足各种场景的应用。②数据类型的本质是一种(对程序员的)约束。
  
  
  2）、演示

  1、准备工作：

  2、插入数据演示：

  
  
  
  
  
  

1.1.2、浮点类型

1.1.2.1、总览

  M：表示有效数字的总个数
  D：表示小数点后的个数
  

类型	大小（字节）	范围（有符号）	范围（无符号）	用途
FLOAT ( M, D )	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE ( M, D )	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2。否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

  
  说明：不同的MySQL版本，在对浮点数的处理会有些区别。 而实践是检验真理的唯一标准，无论书籍还是博文都有其时效，有时不如上手实际验证一通。
  
  
  

1.1.2.2、float

  1）、基本介绍
  float[(m, d)] [unsigned] : m指定显示长度，d指定小数位数，占用空间4个字节。
  说明：单精度浮点数，不能表示精准的小数值。
  
  
  2）、演示

  下图演示验证了MySQL在保存值时会进行四舍五入。（例如：99.991~99.994可以插入，但最后一位会被舍弃；同理，-99.991~-99.994可以插入，最后一位仍旧被舍弃。）

  
  
  

1.1.2.3、decimal

  1）、基本介绍
  decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数。与浮点数数据类型 FLOAT 和 DOUBLE 不同，DECIMAL 类型的值总是精确地存储，并且支持固定的小数位数。
  

  补充说明：DECIMAL(M,D)的存储方式和其他数字类型都完全不同，它是以字符串形式进行存储的。
  例如，对于无符号的整数100，它是将十进制的100转化为二进制为01100100，使用1字节存储。但DECIMAL类型存储时，比如定义DECIMAL(3,0)，存入100时，实际存入的是由字符“1"、“0”、“0”拼接而成的字符串“100”的二进制值，存入时占用3个字节，分别是31, 30, 30 (注意这是十六进制)。

  
  
  2）、演示
  

  

  decimal整数最大位数m为65，支持小数最大位数d是30。如果d被省略，默认为0；如果m被省略，默认是10。

  
  
  
  

1.1.2.4、关于取整方式说明

  1）、零向取整
  说明：对浮点数进行的取整操作，使其变为最接近的整数，但取整的方向是朝着零的方向。这意味着正数会向下取整，而负数会向上取整。
  是C语言默认的取整方式，如下，在C语言中可以使用强制类型转换（type casting）将浮点数转换为整数。也可以使用trunc函数向0取整。

#include <stdio.h>
int main()
{
   
	float a = 5.6;
	float b = -5.6;
	printf("a:%d, b:%d\n", (int)a, (int)b);
	printf("a:%f, b:%f\n", a, b);

	return 0;
}

  
  
  2）、向下取整
  说明：不论输入的是正数还是负数，找到一个小于等于输入数的最大整数，也就意味着向负无穷方向取整。
  在C语言中，可以使用floor函数向下取整。

#include <stdio.h>
#include <math.h>

int main()
{
   
	printf("%lf\n", floor(5.8));
	printf("%lf\n", floor(-5.8));

	printf("%lf\n", floor(2.1));
	printf("%lf\n", floor(-2.1));

	return 0;
}

  
  
  
  3）、向上取整
  说明：与向下取整相反，不论输入的是正数还是负数，找到一个大于等于输入数的最小整数，也就意味着向正无穷方向取整。
  在C语言中，可以使用ceil函数向上取整。

#include <stdio.h>
#include <math.h>

int main()
{
   
	printf("%lf\n", ceil(5.8));
	printf("%lf\n", ceil(-5.8));

	printf("%lf\n", ceil(2.3));
	printf("%lf\n", ceil(-2.3));

	return 0;
}

  
  
  
  4）、四舍五入
  即我们数学中最常用的取整方式。在C语言中，可以使用round函数实现。
  round() 的取整有两种情况：
  1、小数部分大于 0.5 和小于 0.5 的数，在运算上都是完全符合“四舍五入”规则的。
  2、对于小数部分刚好等于 0.5 的数比较特殊：如果整数部分是偶数，那么采用“舍”的操作，往绝对值小的方向走；如果整数部分是奇数，那么采用“入”的操作，往绝对值大的方向走。

#include <stdio.h>
#include <math.h>

int main()
{
   
	printf("%lf, %lf\n\n", round(5.8), round(-5.8));

	printf("%lf, %lf\n\n", round(2.3), round(-2.3));

	printf("%lf, %lf\n\n", round(0.5), round(-0.5));

	printf("%lf, %lf\n\n", round(1.5), round(-1.5));


	return 0;
}

  
  
  

1.1.3、位类型

  1）、基本介绍

  bit[(M)] : 位字段类型，可以存储0和1两个值。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。

  说明： bit字段在显示时，是按照ASCII码对应的值显示。
  
  
  2）、演示
  在MySQL中，BIT类型常用于表示布尔类型数据，例如存储用户是否已经登录、是否有管理员权限等状态。实际应用中，若有只存放0或1的值，或者只有两种状态的值（在线/离线、男/女等），这时可以定义其类型为bit(1)，以节省空间。

  
  
  
  

1.1.4、布尔类型

  BOOLEAN、BOOL：MySQL中布尔类型(BOOLEAN)是一种特殊的数据类型，它是一种用于表示逻辑值，只有逻辑值“真”或“假”两种可能性。它可以用于存储布尔值，如true和false
  
  bool类型在MySQL中通常用于存储二进制数据，如开关状态（开或关）、布尔值（true或false）和权限控制等。
  
  
  
  

1.2、文本、二进制类型

1.2.1、总览

  1）、基本介绍

类型	大小（字符）	用途
CHAR	（0，255）	定长字符串
VARCHAR	（0，65535）	变长字符串
TINYBLOB	（0，255）	不超过 255 个字符的二进制字符串
TINYTEXT	（0，255）	短文本字符串
BLOB	（0，65 535）	二进制形式的长文本数据
TEXT	（0，65 535）	长文本数据
MEDIUMBLOB	（0，16 777 215）	二进制形式的中等长度文本数据
MEDIUMTEXT	（0，16 777 215）	中等长度文本数据
LONGBLOB	（0，4 294 967 295）	二进制形式的极大文本数据
LONGTEXT	（0，4 294 967 295）	极大文本数据

  
  
  2）、关于字节和字符的补充说明

  字节： 计算机信息技术用于计量存储容量的一种计量单位。通常有1个字节=8个比特位（1byte = 8 bit)。每个bit位可以表示0、1两种状态，那么一个字节可以表示 $2^8=256$ 个状态。
  
  
  字符： 在计算机和电信技术中，字符指类字形单位或符号，包括字母、数字、运算符号、标点符号和其他符号，以及一些功能性符号。例如：1、2、3、A、B、C、~！、#、￥、%、、*等。

• ①ASCII码，一个英文字母（不分大小写）占一个字节的空间，一个中文汉字占两个字节的空间。一个二进制数字序列，在计算机中作为一个数字单元，一般为8位二进制数，换算为十进制。最小值0，最大值255。
• ②UTF-8编码，一种从Unicode码转换成可变长度的字符编码方式。一个英文字符等于一个字节，一个中文（含繁体）等于三个字节。
• ③Unicode编码，一个英文等于两个字节，一个中文（含繁体）等于两个字节。 中文标点占两个字节，英文标点占两个字节。
• ④UTF-16编码，一个英文字母字符或一个汉字字符存储都需要2个字节（Unicode扩展区的一些汉字存储需要4个字节）。
• ⑤GBK编码，国家标准编码，通常用于中文字符集。一个英文字母（不分大小写）占一个字节的空间，一个中文汉字占两个字节的空间。 对于符号来说，英文标点占一个字节，中文标点占两个字节。
    
    
    
    
    

1.2.2、字符类型

1.2.2.1、char

  1）、基本介绍
  char(L)：固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255，如果省略L，则长度为 1。
  
  注意：
  1、MySQL中这里长度L是字符个数(长度)，不是字节个数（长度）。例如：char(2)，可以插入2字符的数字、英文、汉字（但实际汉字的编码格式下不止一个字节，要有所区别于以前学的语言）。
  2、这里char的最大长度值是255个字符。也就是说，如果该列使用utf8编码，则该列所占用的字节数=字符数×3。如果是gbk编码，该列所占用的字节数=字符数×2。
  3、对于char，MySQL总是根据定义的字符串长度分配足够的空间。存储CHAR值时，会根据需要采用空格进行剩余空间填充。当检索CHAR值时，末尾的空格将被删除，除非启用了PAD_CHAR_TO_FULL_LENGTH SQL 模式（具体实现看mysql的版本）。
  
  
  
  2）、演示
  基本演示：

  
  
  
  
  

1.2.2.2、varchar

  1）、基本介绍

  varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节。
  
  注意：
  1、varchar长度可以指定为0到65535(字节)之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。
  2、当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532÷3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532÷2=32766（因为gbk中，一个字符占用2字节）。
  3、注意理解这里的长度的含义。下表通过显示将各种字符串值存储到CHAR(4)和VARCHAR(4)列（假设该列使用一个单字节字符集，如latin1）的结果，说明了CHAR和VARCHAR之间的区别。

  
  扩展：varchar和char的区别说明
  
  
  
  2）、演示
  验证VARCHAR的有效最大长度取决于最大的行大小（65,535字节，在一行中的所有列之间共享）和所使用的字符集。

  相比于char固定长度存储，varchar更节省空间，因为它仅使用必要的空间(根据实际字符串的长度改变存储空间)。有一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储。

  
  
  
  

1.3、时间日期

1.3.1、总览

类型	大小（字节）	范围	格式	用途
DATE	3	（1000-01-01，9999-12-31）	YYYY-MM-DD	日期值
TIME	3	（-838:59:59，838:59:59）	HH:MM:SS	时间值或持续时间
YEAR	1	（1901，2155）	YYYY	年份值
DATETIME	8	（1000-01-01 00:00:00，9999-12-31 23:59:59）	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”，UTC表示世界统一时间	YYYY-MM-DD hh:mm:ss	时间戳

  
  
  

1.3.2、基本介绍与演示

  1）、介绍
  
  DATE：表示日期，没有时间部分，格式为YYYY-MM-DD。其中，YYYY表示年份，MM表示月份，DD表示日期，需要3个字节的存储空间。
  ①在向DATE类型的字段插入数据时，同样需要满足一定的格式条件：YYYY-MM-DD或者YYYYMMDD，后者会被转化为YYYY-MM-DD格式的字符串。表示的日期最小取值为1000-01-01，最大取值为9999-12-03。
  
  
  
  DATETIME：在所有的日期时间类型中占用的存储空间最大，总共需要8个字节的存储空间。在格式上为DATE类型和TIME类型的组合，可以表示为YYYY-MM-DD HH:MM:SS，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。
  ①在向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件：YYYY-MM-DD HH:MM:SS或者YYYYMMDDHHMMSS格式的字符串，同上述，后者会被转换为前者格式。表示的日期最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
  
  
  
  TIMESTAMP：可以表示日期时间，其显示格式与DATETIME类型相同，都是YYYY-MM-DD HH:MM:SS，需要4个字节的存储空间。
  ①TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间，超出了类型范围则MySQL会抛出错误信息。
  ②存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。
  
  
  
  2）、相关演示
  注意，在插入日期时，填入的是字符串，带''。

  应用简单举例：历史订单时间、历史充值时间、发表某评论，过段时间修改后，时间也随之变化（如我们的博客最近修改日期）、等等。
  
  
  
  

1.4、string类型

1.4.1、枚举与集合

1.4.1.1、基本介绍

  enum：枚举类型，基本语法格式如下：

ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

  是一种可以在事先定义好（枚举出来）的各个可取值中选择一个的数据类型。定义此类型后，将对有关字段（变量）的取值范围进行限定，只能取各枚举值中的某一个，它可以实现类似单选按钮的功能。该类型的实际存储的是“数字”，一个枚举类型数据最多可以有65535个枚举项，占用空间2个字节。
  
  
  set：集合类型，基本语法格式如下：

SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

  是一种可以在事先定义好的各个可取值中选择若干个进行组合的数据类型。 定义此类型后，有关字段（变量）的取值应是所列的若干个元素值的组合，它可以实现类似复选按钮的功能。一个集合类型数据最多包含64个元素值，占用空间8个字节。

  
  
  

1.4.1.2、相关演示

  1）、相关演示一

  实际上，SET数据类型底层使用的数据结构是位图（bit map）。在位图中，每个元素被表示为一个比特位，通过将多个比特位组合在一起，可以表示一个集合中的多个值。当向SET字段插入一个值时，MySQL会检查该值是否在预定义的值列表中，如果值存在于列表中，相应的比特位会被设置为1；如果值不存在，则比特位会被设置为0。通过这种方式，可以使用位图来表示和存储集合中的值。

  
  
  2）、相关演示二

  对集合、枚举筛选：以下为演示表。

  
  
  集合查询使用find_ in_ set函数：find_in_set(sub,str_list)。（查询相关内容后续讲述）

  
  
  
  
  
  
  
  
  
  

2、表的约束

2.1、总言

  主要内容：理解约束。是什么、为什么需要、有哪些约束（约束谁？）。
  
  1）、约束是什么
  在MySQL中，约束（Constraint）是一种规则，用于确保数据库表中的数据的准确性和完整性。表的约束很多，这里主要介绍如下几个： null/not null、default、comment、zerofill、primary key、auto_increment、unique key 等。
  
  
  2）、为什么需要约束
  真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。
  
  
  
  

2.2、空属性（非空约束）

2.1.1、基本介绍

  1）、是什么（概念介绍）

  在MySQL中，空属性（NULL）和非空约束（NOT NULL） 是用于确保数据完整性的两种常见约束。
  
  1、空属性（NULL）：表示列中的值为未知或缺失 。NULL与具体的值不同，其不等于0或空字符串，而 是一个特殊的占位符 （PS：MySQL中默认情况下列可以包含NULL值）
  ①当插入或更新数据时，如果列允许NULL值，并且没有为该列提供值，则该列将被设置为NULL。
  ②相反，如果在非空列上尝试插入NULL值，MySQL会拒绝操作并返回错误。

  
  2、非空约束（NOT NULL）：是一种约束，用于确保列中的值不为NULL。这意味着在该列中必须有一个不为NULL的值，如果尝试插入或更新数据时没有为该列提供值，将会引发错误。
  
  
  
  2）、为什么需要非空约束

• 数据完整性： 设置列为NOT NULL约束可以保证数据的完整性，避免在插入或更新数据时该列被留空或存储空值。通过限制该列的取值必须为非空值，可以有效防止数据的不完整和不符合业务规则的情况。
• 空值检查： MySQL在执行插入或更新操作时会进行空值检查。如果插入或更新操作中涉及到被设置为NOT NULL的列，并且该列的值为空或为NULL，MySQL会拒绝该操作并抛出错误。
• 存储空间优化： 设置列为NOT NULL约束还可以在存储空间上进行优化。对于可为空的列，MySQL需要为每个行分配额外的空间来存储是否为空的标识，而对于被设置为NOT NULL的列，不需要额外的空间来表示空值的存在。

  通过设置NOT NULL约束，MySQL可以在插入和更新数据时对列的非空性进行强制检查，避免数据的不完整和不符合业务规则的情况。底层原理涉及对约束条件的验证和空值检查，以确保被设置为NOT NULL的列始终包含有效的非空值。
  
  
  
  
  3）、如何为列添加非空约束？

  方式有二：
  ①在创建表时使用NOT NULL关键字。
  ②在修改表时使用ALTER TABLE语句。（需要注意，如果表中已经存在NULL值，添加非空约束将会失败。需要先更新这些NULL值或删除包含NULL值的行，然后再添加非空约束。）

方式1：<字段名><数据类型> not null;
方式2：alter table 表名 modify 字段 类型 not null;

  
  
  
  

2.1.2、使用演示

  1、创建表时添加非空约束： 相关情况说明见图中文字描述（解释了desc table_name显示结构中的NULL信息栏、解释了DEFAULT NULL）。

  
  2、验证上述非空约束：

  
  
  
  
  
  

2.3、默认值（默认约束）

2.3.1、基本介绍

  1）、是什么（概念介绍）
  在MySQL中，默认值和默认约束是两个相关的概念，它们都用于确保数据的完整性。
  说明： 默认约束用于为数据表中的字段指定默认值，即当在表中插入一条新纪录时，如果没有给这个字段赋值，那么，数据库系统会自动为这个字段插入默认值。
  注意：BLOB、TEXT数据类型不支持默认约束。
  
  
  2）、如何添加默认约束？
  默认值通过default关键字定义：
  ①可以在创建表时使用DEFAULT关键字+默认值，为表的列设置默认值。
  ②还可以在创建表后使用ALTER TABLE语句，为现有列添加默认值。

方式1： <字段名> <数据类型> default <默认值>;
方式2: alter table 表名 modify 列名 类型 default 默认值; 

  
  
  
  

2.3.2、使用演示

  演示建表时设置default默认值，常规插入的情况：

  
  问题：如何正确看待空属性NULL和默认值default之间的关系？
  1. default：不显示的向指定列中插入，default自动起效。
  2. NULL：当我们显示的向一列插入，如果插入的是正常值，就正常工作，否则，插入NULL，NOT NULL就起约束作用不让插入数值。

  
  
  
  
  
  
  

2.4、列描述（comment）

2.4.1、基本介绍

  列描述comment：没有实际含义，用于为表中的列添加注释，会根据表创建语句保存，用来给程序员或DBA进行信息了解。
  
  
  实际comment在之前建表时已经使用过：

  
  
  
  
  
  

2.5、zerofill（零填充约束）

2.5.1、基本介绍

  1）、问题引入
  在之前对数据类型的学习中，我们知道TINYINT的大小是1 Bytes，INT的大小是4 Bytes，使用desc或者show查看表结构是，会发现整型后面带有一个数值：int(11)、int(10)等，那么，这里的数值具体表示什么含义？
  这就是接下来要介绍的ZEROFILL约束。

  
  2）、是什么

  Zerofill：是MySQL中一种填充类型(Fill Type)，用于在插入或更新数据时，自动将列值填充为零，使字段具有固定的位数。Zerofill填充类型仅适用于整数类型，其主要作用是方便对齐和数据处理。
  使用ZEROFILL约束的语法如下： 其中，width是显示宽度，指定了该列的显示长度。例如，如果将width设置为5，则该列的值将自动填充前导零，以确保总共有5个字符的长度。

CREATE TABLE table_name (  
    column_name INT(width) ZEROFILL  
);

  
  
  

2.5.2、使用演示

  说明：实则上述整型的这种宽度限制，在没有使用zerofill约束时，看不出什么区别。此外，需要注意设置了zerofill属性，只是影响格式化输出，对实际存储并无影响。

  

  其它问题说明：为什么默认无符号int为10，有符号int为11？
  回答：int类型在MySQL中占4个字节（byte），即4×8=32位。因此，有符号int类型取值在 - 2147483648 ~ 2147483647之间，无符号int类型取值在 0 ~ 4294967295之间。

有符号数最小值:
- 2 1 4 7 4 8 3 6 4  8
1 2 3 4 5 6 7 8 9 10 11    --共11位，即 int(11)
 
无符号数最大值:
4 2 9 4 9 6 7 2 9 5
1 2 3 4 5 6 7 8 9 10       --共10位,即 int(10) unsigned

  
  
  
  
  

2.6、主键（主键约束）

2.6.1、基本介绍

  1）、主键是什么？
  主键 PRIMARY KEY： 表中经常有一个列或多列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可强制表的实体完整性。
  ①一个表只能有一个主键，并且主键字段的值必须是唯一的，不能有重复值。
  ②主键字段的值也不能为NULL。
  ③在有些数据库中，虽然主键不是必需的，但最好为每个表都设置一个主键，不管是单主键还是复合主键。它存在代表着表结构的完整性，表的记录必须得有唯一区分的字段，主键主要是用于其他表的外键关联，以及本记录的修改与删除。
  
  
  2）、为什么需要主键？
  此部分文字不必死记硬背，建议实践时加以理解。
  
  唯一标识记录： 主键的最主要用途是唯一标识数据库表中的每条记录。在数据库中，可能会有成千上万条记录，如果没有一种方法来唯一地标识每条记录，那么查询、更新或删除特定的记录将会变得非常困难。
  提高查询速度： 在MySQL中，主键会自动被索引。这意味着当你在查询中使用主键时，数据库可以快速地定位到特定的记录，从而提高查询速度。如果没有主键，数据库将需要进行全表扫描，这在大型数据表中是非常低效的。
  避免记录重复： 由于主键要求唯一，这就确保了数据库表中的每条记录都具有唯一的标识符，避免了记录重复的问题。
  数据完整性： 通过确保记录的唯一性，主键有助于维护数据的完整性。这有助于确保数据库中的数据准确无误，并防止因重复数据而引起的各种潜在问题。
  外键约束： 在关系型数据库中，主键也用于创建外键约束。外键是一个表中的字段，其值必须是另一个表的主键的值。这有助于维护数据库中不同表之间的关系，确保数据的准确性和一致性。
  
  
  
  
  

  3）、主键的设置方式

  1、可以在创建表时，①直接在字段之后指定主键primary key；②在所有字段之后增加primary key选项

-- 在 create table 语句中，通过 PRIMARY KEY 关键字来指定主键。
--在定义字段的同时指定主键，语法格式如下：
create table 表名(
   ...
   <字段名> <数据类型> primary key 
   ...
)

--在定义字段之后再指定主键，语法格式如下：
create table 表名(
   ...
   [constraint <约束名>] primary key [字段名]
);

  
  
  2、当表创建好以后，可以使用alter table语句追加或修改主键。

create table 表名(
   ...
);
alter table <表名> add primary key（字段列表);

  
  
  3、表中不需要主键约束时，可以从表中将其删除。

alter table <数据表名> drop primary key;

  
  
  

2.6.2、使用演示

  创建表时设置主键（附加后续插入数据操作）：主键约束性体现在，被设置为主键的列不能出现重复，但非主键的列可以重复。

  
  演示已经有表，删除、追加主键：

  一个问题：主键具有唯一性，但具有唯一性的一定是主键吗？
  
  
  

2.6.3、复合主键/联合主键（引入）

  1）、基本介绍
  介绍： 之前提到过，主键可以由一列或者多列组成。当主键是由多个字段组成时，不能直接在字段名后面声明主键约束，而是要在最后来指定主键。

create table 表名(
   ...
   primary key （字段1，字段2，…,字段n)
);

  需要注意的是： 即使是复合主键，也只能有一个复合主键。（这小节的内容会在后续复合索引时详细介绍，这里先演示操作）
  意义： 复合主键允许创建一个唯一标识符，该标识符由多个字段的组合组成。使用复合主键可以帮助解决单字段主键的一些限制，例如在某些情况下，两个记录可能在单个字段上是唯一的，但在组合时是重复的。
  
  这里我们以一个例子来说明上述意义，选课表。对于某一课程与学生，存在以下关系：
  ①允许一名学生同时选择多个不同的课程。
  ②允许多名不同的学生同时选择某一项课程。
  ③同一课程不允许同一名学生重复选择。
  
  由此，我们能创建下表：

  
  
  
  
  2）、如何设计主键？（一个逻辑简述）
  1、结合业务，可以选择一个具有唯一性的列属性作为主键
  2、选择与业务无关的唯一值(特意设计的，这样设计主键值和业务无关，业务调整时不影响整体主键的表结构—>解耦)：例如QQ号
  
  
  
  

2.7、自增长（自增长约束）

2.7.1、基本介绍

  1）、自增长是什么？
  在MySQL中，自增长（AUTO_INCREMENT）是一种属性，用于自动为新记录生成一个唯一的数字。通常和主键搭配使用，作为逻辑主键，以确保每个记录都有一个唯一的标识符。

  语法如下：

字段名 数据类型 auto_increment

  相关特点说明：
  1、一个表中只能有一个自动增长字段，该字段的数据类型只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等，且必须定义为键，如unique key、primary key。

  2、默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。如果插入的值大于自动增长的值，则下次插入的自动增长值会自动使用最大值加1；如果插入的值小于自动增长值，则不会对自动增长值产生影响。

  3、一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。

  4、auto_increment约束的字段必须具备 NOT NULL 属性。

  5、auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。
  
  
  
  

2.7.2、使用演示

  1、为什么能做到自增长？
  内部计数器： MySQL为每个自增长字段维护了一个内部计数器。当插入新的记录时，MySQL会检查计数器的值，并将其递增为下一个值。计数器的初始值可以由用户指定，并在后续的插入操作中自动递增。
  锁定机制： 为了避免在并发插入操作中产生重复的自增长值，MySQL使用了一种锁定机制。在插入新记录时，MySQL会尝试获取一个排他锁（也叫写锁）来确保计数器操作的原子性。这样可以确保在同一时间只有一个插入操作可以更新计数器。

  
  
  2、若就不按照它的计数器规规矩矩来，我们自行插入id乱值。①是否能插入？②之后再使用自增长会如何？

  
  3、对自增长的修改操作：alter table 表名 auto_increment=数值;

  
  
  
  
  
  

2.7.3、索引·概念引入篇

  PS：会在后续博文中讲解。
  
   是什么： 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
  
  意义： （本质是空间换时间，虽然耗费了一点成本，但可以提高查找速率。）索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。
  
  
  
  
  
  

2.8、唯一键（唯一约束）

2.8.1、基本介绍

  1）、唯一键是什么？
  在MySQL中，唯一键（Unique Key）是一种约束，用于确保表中的数据具有唯一性。唯一键的作用是确保在表中的一列或多列上，每行的值都是唯一的，不会出现重复的情况。
  例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。
  
  
  
  2）、为什么需要唯一键？
  一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，因此唯一键就可以解决表中有多个字段需要唯一性约束的问题。
  
   特别说明：对唯一键的理解不能仅仅站在技术角度，需要上升到业务逻辑角度。 现实生活中，很多事物信息具有唯一性属性，一般而言，主键只是在众多具有唯一性的属性列中被选择成为主键而已，也有其它字段需要保证其唯一性。因主键只能有一个，所以mysq|提供了其它保证数据列信息唯一性的方案，如，此处介绍的唯一键。
  
  
  
  
  3）、如何设置唯一键？
  要创建唯一键，可以在创建表时使用UNIQUE关键字，或者在表创建后使用ALTER TABLE语句添加唯一键约束：

方式1：<字段名> <数据类型> unique
方式2： alter table 表名 add constraint 约束名 unique(列);

  删除唯一约束：

alter table <表名> drop index <唯一约束名>;

  
  
  
  
  

2.8.2、使用演示

  
  相关演示：

  
  关于唯一键和主键区别说明：（简述） 唯一键和主健并不冲突，反而是互相补充的！共同维护表的完整性。 在设计数据库时，根据具体需求和表之间的关系来选择使用哪种约束是重要的。

  
  🌂定义与功能：

• 主键（Primary Key）：定义为主键的字段或字段组合是用来唯一标识数据库表中的每条记录的。一个表中只能有一个主键。主要功能是保证数据的唯一性，并作为记录的唯一标识符。 在数据库操作中，主键可以快速查找到特定的记录。
• 唯一键（Unique Key）：唯一键的作用也是保证数据的唯一性，但它不要求必须是记录的唯一标识符。也就是说，一个表可以有多个唯一键。唯一键允许多个字段具有相同的值，只要这些值在各自的数据集中是唯一的。

  

  🌂数据完整性和空值接受度：

• 主键：主键字段的值必须是唯一的，并且不能为NULL。这意味着主键字段始终包含有效的数据。
• 唯一键：虽然唯一键也要求数据唯一，但它允许为NULL值。这意味着可以有多个记录的唯一键字段值为NULL。

  
  🌂自动增长：

• 主键：通常可以设置为自动增长，这意味着每当有新记录插入时，数据库会自动为主键字段分配一个新的、唯一的值。
• 唯一键：虽然也可以设置为自动增长，但这不是必须的。唯一键的值可以手动指定或通过其他方式生成。

  
  🌂外键关系：

• 主键：通常用作外键，与其他表建立关系。 在具有外键约束的数据库设计中，主键是其他表的外键。
• 唯一键：虽然也可以用作外键，但它的主要作用不是建立表之间的关系。唯一键通常用于确保数据的唯一性，而不是作为记录的标识符。

  
  🌂索引与查询性能：

• 主键：通常会为主键字段自动创建索引（通常是聚集索引），这有助于提高基于主键的查询性能。
• 唯一键：可以为唯一键创建索引 （通常是聚集索引或非聚集索引），但这不是必须的 。创建索引可以提高查询性能，但也可能增加数据插入、更新或删除的时间成本。

  
  
  
  
  

2.9、外键

2.9.1、基本介绍

  1）、问题引入：表内约束和表间约束（回答为什么需要外键）
  表内约束： 通常是在单个表的内部定义的约束条件，用于确保表中数据的准确性和完整性。常见的表内约束包括主键约束、唯一约束、检查约束、默认约束和NOT NULL约束等。这些约束可以用来限制字段的值、确保数据的唯一性、防止非法数据的插入等。
  
  表与表之间的约束： 通常通过外键来定义，用于维护两个表之间的关系和参照完整性。 外键是一个字段或字段组合，其值依赖于另一个表的主键或唯一键的值。通过外键约束，可以确保引用数据的准确性，并防止因删除或更新操作导致引用数据不一致的问题。

  同唯一键，要理解外键也是要站在现实生活中的业务逻辑角度考虑。 在关系型数据库中，通常会有一个“订单”表和一个“客户”表。每个订单都对应一个客户，客户的ID可以是订单表的外键。这样，当从“客户”表中删除一个客户时，如果该客户有相关的订单记录在“订单”表中，这些订单记录将被阻止删除，以保持数据的一致性。

  
  
  
  2）、什么是外键？

  MySQL 外键约束（FOREIGN KEY）：是表的一个特殊字段，通常与另一个表中的主键或唯一键关联使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
  外键约束关键字: foieign key。
  

理解主表和从表

  主表和从表： 外键定义在一个表中，这个表称为从表（或子表），而外键所引用的数据则定义在另一个表中，这个表称为主表（或父表）。
  理解外键的从表和主表的关键，在于理解它们之间的关系。从表中包含外键列，这些列的值必须是主表中相应主键列的有效值。 这意味着，如果从表中有一个记录引用了主表中的某个值，那么这个记录就不能被删除或更新，除非在主表中存在相应的记录。

  一个举例（说明外键的从表和主表的概念）： 假设有两个表，一个是学生表，另一个是课程表。学生表包含学生的信息，而课程表包含课程的信息。每个学生可以选修多门课程，每门课程也可以被多个学生选修。为了表示这种关系，可以在学生表中添加一个外键列course_id，该列引用课程表的id列。这样，学生表成为从表，而课程表成为主表。通过外键约束，可以确保每个学生选修的课程在课程表中确实存在，从而维护数据的完整性。

  
  

再次理解外键

  1、补充说明： 外键不仅仅是产生表和表之间的关联，还有一个重要属性往往被人忽略，外键在MySQL中还具有特定的约束规则，保证表和表的数据完整性和一致性。存在约束的关联字段，才叫做外键。

  
  
  2、外键约束的作用:

    限制从表插入:如果从表插入的外键值,在主表中不存在,就插入失败

    限制主表删除:如果主表的主键值已经被从表引用,在主表删除数据的时候,就删除失败

  注意：如果想要使用外键约束，存储引擎需要是InnoDB

  
  
  3、定义外键时，需要遵守下列规则：

• 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
• 必须为主表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。 也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表的主键中列的数目相同。
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同
    
    
    
    
    3）、外键相关操作

  1）、在创建数据表时创建外键约束： 只需要在创建数据表的create语句后面，使用foreign key关键字指定本表的外键字段，使用reference关键字指定关联表的关联字段，并且明确约束行为即可。

CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

--举例：
create table stu (
id int primary key,
name varchar(30) not null comment '学生名',
class_id int,
foreign key (class_id) references myclass(id) --这里就是创建外键时的设置格式
);

  
  
  2）、外键约束也可以在修改表时添加，但是添加外键约束的前提是从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。 创建表时设置外键约束的语法格式如下：

ALTER TABLE <数据表名>  ADD CONSTRAINT <外键名> FOREIGN KEY (<外键字段名>) REFERENCES <主表 (主表列名)> ;
# ADD CONSTRAINT 意为添加一个约束
#REFERENCES 主表 意为用子表的外键去关联父表

  
  3）、当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系：（PS：需要先删除从表，后删除主表，否则无法删除。）

alter table <表名> drop foreign key <外键约束名>;

  
  
  
  

2.9.1、使用演示

  先创建主键表：

  
  
  再创建从表：演示正常插入数据的情况。

  
  
  一些外键约束作用体现：