淮南集团网站建设,假冒网站能通过备案登记吗,中国林业工程建设网站,网站建设仿站总言 主要内容#xff1a;介绍MySQL中的常见数据类型#xff08;数值类型、文本二进制类型、时间日期、字符串类型#xff09;#xff0c;以及对表的约束#xff08;非空约束、默认约束、列描述、零填充约束、自增长约束、主键约束、唯一键约束、外键约束#xff09;。 …总言 主要内容介绍MySQL中的常见数据类型数值类型、文本二进制类型、时间日期、字符串类型以及对表的约束非空约束、默认约束、列描述、零填充约束、自增长约束、主键约束、唯一键约束、外键约束。 文章目录 总言1、数据类型1.1、数值类型1.1.1、整数类型1.1.1.1、总览1.1.1.2、tinyint细节演示 1.1.2、浮点类型1.1.2.1、总览1.1.2.2、float1.1.2.3、decimal1.1.2.4、关于取整方式说明 1.1.3、位类型1.1.4、布尔类型 1.2、文本、二进制类型1.2.1、总览1.2.2、字符类型1.2.2.1、char1.2.2.2、varchar 1.3、时间日期1.3.1、总览1.3.2、基本介绍与演示 1.4、string类型1.4.1、枚举与集合1.4.1.1、基本介绍1.4.1.2、相关演示 2、表的约束2.1、总言2.2、空属性非空约束2.1.1、基本介绍2.1.2、使用演示 2.3、默认值默认约束2.3.1、基本介绍2.3.2、使用演示 2.4、列描述comment2.4.1、基本介绍 2.5、zerofill零填充约束2.5.1、基本介绍2.5.2、使用演示 2.6、主键主键约束2.6.1、基本介绍2.6.2、使用演示2.6.3、复合主键/联合主键引入 2.7、自增长自增长约束2.7.1、基本介绍2.7.2、使用演示2.7.3、索引·概念引入篇 2.8、唯一键唯一约束2.8.1、基本介绍2.8.2、使用演示 2.9、外键2.9.1、基本介绍2.9.1、使用演示 1、数据类型 当前博文中MySQL版本号
[rootVM-4-3-centos ~]# mysql --version
mysql Ver 14.14 Distrib 5.7.44, for Linux (x86_64) using EditLine wrapper1.1、数值类型
1.1.1、整数类型
1.1.1.1、总览
类型大小字节范围有符号范围无符号用途TINYINTT1 Bytes-1281270255小整数值SMALLINT2 Bytes-32 76832 767(065 535)大整数值MEDIUMINT3 Bytes-8 388 6088 388 607016 777 215大整数值INT或INTEGER4 Bytes-2 147 483 6482 147 483 64704 294 967 295大整数值BIGINT8 Bytes-9,223,372,036,854,775,8089 223 372 036 854 775 80018 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 E38-1.175 494 351 E-38)0(1.175 494 351 E-383.402 823 466 351 E38)0(1.175 494 351 E-383.402 823 466 E38)单精度浮点数值DOUBLE ( M, D )8 Bytes(-1.797 693 134 862 315 7 E308-2.225 073 858 507 201 4 E-308)0(2.225 073 858 507 201 4 E-3081.797 693 134 862 315 7 E308)0(2.225 073 858 507 201 4 E-3081.797 693 134 862 315 7 E308)双精度 浮点数值DECIMAL对DECIMAL(M,D) 如果MD为M2。否则为D2依赖于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.hint 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.hint 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.hint 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、BOOLMySQL中布尔类型(BOOLEAN)是一种特殊的数据类型它是一种用于表示逻辑值只有逻辑值“真”或“假”两种可能性。它可以用于存储布尔值如true和false bool类型在MySQL中通常用于存储二进制数据如开关状态开或关、布尔值true或false和权限控制等。
1.2、文本、二进制类型
1.2.1、总览 1、基本介绍
类型大小字符用途CHAR0255定长字符串VARCHAR065535变长字符串TINYBLOB0255不超过 255 个字符的二进制字符串TINYTEXT0255短文本字符串BLOB065 535二进制形式的长文本数据TEXT065 535长文本数据MEDIUMBLOB016 777 215二进制形式的中等长度文本数据MEDIUMTEXT016 777 215中等长度文本数据LONGBLOB04 294 967 295二进制形式的极大文本数据LONGTEXT04 294 967 295极大文本数据 2、关于字节和字符的补充说明 字节 计算机信息技术用于计量存储容量的一种计量单位。通常有1个字节8个比特位1byte 8 bit)。每个bit位可以表示0、1两种状态那么一个字节可以表示 2 8 256 2^8256 28256 个状态。 字符 在计算机和电信技术中字符指类字形单位或符号包括字母、数字、运算符号、标点符号和其他符号以及一些功能性符号。例如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、对于charMySQL总是根据定义的字符串长度分配足够的空间。存储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÷321844[因为utf中一个字符占用3个字节]如果编码是gbkvarchar(n)的参数n最大是65532÷232766因为gbk中一个字符占用2字节。 3、注意理解这里的长度的含义。下表通过显示将各种字符串值存储到CHAR(4)和VARCHAR(4)列假设该列使用一个单字节字符集如latin1的结果说明了CHAR和VARCHAR之间的区别。 扩展varchar和char的区别说明 2、演示 验证VARCHAR的有效最大长度取决于最大的行大小65,535字节在一行中的所有列之间共享和所使用的字符集。 相比于char固定长度存储varchar更节省空间因为它仅使用必要的空间(根据实际字符串的长度改变存储空间)。有一种情况例外如果MySQL表使用ROW_FORMATFIXED创建的话每一行都会使用定长存储。 1.3、时间日期
1.3.1、总览
类型大小字节范围格式用途DATE31000-01-019999-12-31YYYY-MM-DD日期值TIME3-838:59:59838:59:59HH:MM:SS时间值或持续时间YEAR119012155YYYY年份值DATETIME81000-01-01 00:00:009999-12-31 23:59:59YYYY-MM-DD hh:mm:ss混合日期和时间值TIMESTAMP41970-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或空字符串而 是一个特殊的占位符 PSMySQL中默认情况下列可以包含NULL值 ①当插入或更新数据时如果列允许NULL值并且没有为该列提供值则该列将被设置为NULL。 ②相反如果在非空列上尝试插入NULL值MySQL会拒绝操作并返回错误。 2、非空约束NOT NULL是一种约束用于确保列中的值不为NULL。这意味着在该列中必须有一个不为NULL的值如果尝试插入或更新数据时没有为该列提供值将会引发错误。 2、为什么需要非空约束
数据完整性 设置列为NOT NULL约束可以保证数据的完整性避免在插入或更新数据时该列被留空或存储空值。通过限制该列的取值必须为非空值可以有效防止数据的不完整和不符合业务规则的情况。空值检查 MySQL在执行插入或更新操作时会进行空值检查。如果插入或更新操作中涉及到被设置为NOT NULL的列并且该列的值为空或为NULLMySQL会拒绝该操作并抛出错误。存储空间优化 设置列为NOT NULL约束还可以在存储空间上进行优化。对于可为空的列MySQL需要为每个行分配额外的空间来存储是否为空的标识而对于被设置为NOT NULL的列不需要额外的空间来表示空值的存在。 通过设置NOT NULL约束MySQL可以在插入和更新数据时对列的非空性进行强制检查避免数据的不完整和不符合业务规则的情况。底层原理涉及对约束条件的验证和空值检查以确保被设置为NOT NULL的列始终包含有效的非空值。 3、如何为列添加非空约束 方式有二 ①在创建表时使用NOT NULL关键字。 ②在修改表时使用ALTER TABLE语句。需要注意如果表中已经存在NULL值添加非空约束将会失败。需要先更新这些NULL值或删除包含NULL值的行然后再添加非空约束。
方式1字段名数据类型 not null;
方式2alter 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当我们显示的向一列插入如果插入的是正常值就正常工作否则插入NULLNOT NULL就起约束作用不让插入数值。 2.4、列描述comment
2.4.1、基本介绍 列描述comment没有实际含义用于为表中的列添加注释会根据表创建语句保存用来给程序员或DBA进行信息了解。 实际comment在之前建表时已经使用过 2.5、zerofill零填充约束
2.5.1、基本介绍 1、问题引入 在之前对数据类型的学习中我们知道TINYINT的大小是1 BytesINT的大小是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×832位。因此有符号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) unsigned2.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、使用演示 先创建主键表 再创建从表演示正常插入数据的情况。 一些外键约束作用体现
