MySQL表的约束与内置函数

目录

MySQL表的约束

空属性

默认值 

列描述comment

zerofill

主键

复合主键

自增长

唯一键

外键

MySQL的内置函数

日期函数

字符串函数

​编辑数学函数

 其他函数

---

MySQL表的约束

        MySQL表的约束是用于规定表中数据的完整性和一致性的规则。

        约束可以在创建表时定义，也可以在表已创建后通过ALTER TABLE语句进行添加、修改或删除。之前的文章提到如果插入的数据超出了对应数据类型的取值范围，那么数据将会插入失败，这就是一种数据类型的约束。

        为了更好的保证数据的合法性，从业务逻辑角度保证数据的正确性，MySQL中出现了表的约束，主要包括：空属性约束、默认约束、comment、zerofill、主键约束、外键约束、唯一键约束、自增长约束等。

空属性

空属性用来表示字段是否允许为空值。

通过select可以查看null的值，空值无法参与运算，因此null值加一后得到的还是null。

 如果要让某个字段不允许为空，在创建表的时候就可以给对应字段设置not null属性。

向表中插入记录时只有设置字段的不能为空的字段都不为空时才能插入成功，否则将会插入失败。

默认值 

• 如果某一个字段会经常性地出现某个值，可将这个值设置成该字段的默认值。
• 向表中插入数据时如果不给带有默认值的字段赋值，那么就会使用默认值进行插入。

 例如：

 倘若同时为字段设置not null和default，那么插入该字段的时候没有指明字段的值就会使用该字段的默认值进行填充，可见not null意义就不是很大了。

列描述comment

列描述是在创建表的时候用来对各个字段进行描述的，列描述会根据表创建语句保存，一般是用来给程序员或DBA了解表的相关信息的，相当于一种注释。通过show create table 表名SQL可以看到创建表时的相关细节，包括列描述。

例如：

zerofill

• 数值类型后面的圆括号中的数字，代表的是显示宽度，对应数值类型设置zerofill属性后，如果数据的宽度小于设定的宽度则自动填充0。

例如：为b填上zerofill属性，可见自动填充了0到5位数。

 注意zerofill只是让数据以特定方式显示，数据底层的存储方式没有变化。

主键

将一个字段设置为主键，意味着将以它创建索引结构，MySQL使用B+树索引结构来存储和管理主键，因此使用主键查找数据的效率很高（对数级）。

表中每条记录的主键不能重复也不能为空，一张表只能有一个主键，主键常为整数类型。

例如：

使用alter table 表名 drop primary keySQL即可删除指定表的主键；

使用alter table 表名 add primary key(列名)SQL可以给指定列设置成主键；

复合主键

• 一张表里面只能有一个主键，但一个主键可以由多个字段来承担，这种主键叫做复合主键。
• 复合主键用来唯一约束多个字段里面的数据，表当中每条记录的这多个字段不能同时重复也不能为空。

 

删除复合主键和增加复合主键的方式和主键相同。 

自增长

1.设置了自增长属性的字段，插入数据时如果不给该字段值，那么系统会自动找出当前字段当中已有的最大值，将最大值进行加一后的值插入该字段。
2.任何一个字段要做自增长，前提是其本身必须是一个索引（Key一栏有值），并且自增长字段必须是数值类型，一张表最多只能有一个自增长字段。
3.自增长通常和主键搭配使用，作为逻辑主键。一般而言，建议将主键设计成与当前业务无关的字段，避免因为业务逻辑的调整而需要修改主键。

使用auto_increment设置自增长主键：

唯一键

1.主键具有唯一性，但是一张表只能有一个主键，实际可以需要多个字段具有唯一性，那些需要唯一性特性的字段可以设置成唯一键保证唯一性，一张表可以有多个唯一键。

2.唯一键允许字段为空，并且可以多个字段为空，空字段不做唯一性比较。

 

 插入相同值报错，可以插入null：

外键

• 外键用来定义主表和从表之间的关系，外键约束主要定义在从表上，主表必须有主键约束或唯一键约束。
• 外键定义后，要求在从表插入外键列的数据必须在主表对应的列存在该数据或为null值。

 例如：

创建班级表，包含班级id和班级名两列：

再创建一个学生表，包含学生id、学生姓名、学生所在班级id三列，其中班级id设置为外键（需要关联到班级表的班级id列：

 

在班级表中插入两个班级，之后在学生表中插入的学生信息中，学生所在的班级id必须在班级表出现过，或者插入null，这就是外键约束：

 如果像班级表再插入班级id为3的班级，学生表才能插入班级id为3的学生。

外键约束的好处

• 准确性：外键约束能保证只有班级id在班级表中存在的记录才能插入学生表，否则就会插入失败。
• 简化工作：实际建立外键的本质就是把相关性交给MySQL去审核了，提前告诉MySQL表之间的约束关系，当用户插入不符合业务逻辑的数据时，MySQL就不允许你进行插入。

MySQL的内置函数

日期函数

 例如：

datediff函数用于获取两个日期的差，单位是天，运算时为左边日期减右边日期：

字符串函数

使用实例： 

charset

 

concat

 

 instr

 

ucase和lcase

 

left

length

 replace

 strcmp

 substring

 

 ltrim、rtrim、trim

数学函数

绝对值函数abs和进制转换函数bin、hex、conv 

 取整函数ceiling和floor

格式化函数(四舍五入)format，随机函数rand、求余函数mod 

 

MySQL中取余运算符的结果的符号与被除数的符号一致： 

 

 其他函数

user函数：获取MySQL连接的当前用户名和主机名

 

 md5函数：对一个字符串进行md5摘要，摘要后得到一个32位字符串

 一般情况下存储用户的明文密码会将用户密码形成摘要后存储对应的摘要，当用户登录账号时，将用户输入的的密码形成摘要后与数据库中存储的摘要做对比，如果对比成功则允许登录。
这么做可以避免用户信息泄露，摘要是定长的，有利于数据库表结构的设计。

 database函数：显示当前正在使用的数据库

 

sha2函数：SHA2函数用于生成给定字符串的SHA-2哈希，用于对用户数据进行加密（或者使用旧版本加密算法的password）

 使用方法:

SELECT PASSWORD('password');

SELECT SHA2(string, bit_length)

ifnull函数：判断参数是否为null，接受两个参数，如果第一个参数不为null则返回第一个参数值，否则返回第二个参数值