这些都是我自学时手打到文本文档,在复制粘贴到博客的,有一些命令格式不对,但全部百分百原创,如果有疑问或者不对的地方,欢迎评论区指正,也可以加q群592383030来探讨(我就是自学的普通人,不卖课,不涉及补习机构)
我会出一整套mysql的学习过程,从基础的sql语句,约束,函数,事务。到进阶的存储引擎,sql优化,索引,存储过程,锁,触发器,视图甚至lnnoDB引擎的详解和一些运维方面的比如,mha主从,读写分离;mmm双主,pxc MySQL高可用,zabbix,elk监控MySQL。如果有需要来我主页,有帮助到您就关注我点个赞。
一,约束概述
1,概念:约束是作用于表中字段的规则,用于限制存储在表中的数据。
2,目的:保证表中数据的正确、有效性和完整性
认识常见的约束
非空约束 限制该字段的数据不能为null(空) 关键字是not null
唯一约束 保证该字段的所有数据都是唯一、不重复的 关键字是unique (比如写入身份证号信息,用这个查重)
主键约束 主键是一行数据(一个表有很多字段,一个人每个字段都有一条自己的数据,那么这个人的数据就称为一条数据)的 唯一标识,要求它非空且唯一 关键字是primary key(从查的角度看,以成绩单为例名字,成绩,性别,年龄都有重复的概率,这些如果作为主键,一旦重复了就无法保存,因为主键必须唯一。所以一般只有id做主键,因为id有唯一性比较合理,录入时,别人和我id一样就无法保存,只能不同id才能存储成功。查的时候id准确就你精准定位到成绩,这些你把名字,成绩,性别,年龄作为主键都能实现,但是名字,成绩,性别,年龄有唯一性不合理。
默认约束 保存数据时,如果未指定该字段的值,则采用默认 关键字是default
检测约束 保证字段值满足某一个条件 关键字是check
外键约束 用来让两张表的数据之间建立连接,保证数据的一致和完整 关键字是foreign key
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束
二,约束使用演示
根据要求创建出表
要求:五个字段分别是id(字段类型int,并实现自动增长),姓名(字段类型是varchar(10),不为空并且唯一),年龄(字段类型是int,大于0且小于等于120),状态(字段类型是char(1),如果没有指定默认为一)性别(字段类型是char(1)无约束条件)
注意:id要实现自动增长涉及到新的关键字auto——increment
这一步就是创建表,结尾加表的注释
create table user( 这一步就是创建表,
id int primary key auto_increment comment‘主键’, id字段采用int字段类型,并且调用 primary key 确认此字段是主键并且它非空值且唯一 ,调用auto—increment实现自动增长,comment加注释
name varchar(10) not null unique comment‘姓名’ name字段采用varchr字段类型,调用not null实现此字段不为空,在调用unique实现唯一不重复(名字有唯一性并不合理,但是按要求办事),comment加注释
age int check(age > 0 && age <= 120)comment‘年龄’ age字段采用int字段类型,并且调用check来检测约束,check后面的括号里就写检查约束的条件,符合这个条件才能成功存储到字段内,comment加注释
status char(1) default‘1’comment‘状态’ status字段采用char字段类型,调用 default实现如果不写入数据则采用默认值后面的‘’里写的就是自定义的默认值。comment加注释
gender char(1)comment‘性别’ gender采用char字段类型,comment加注释
)comment‘用户表’; comment表注释
注意:id配置了主动自增加,写入时就不用配置了,直接配置id,name,age,status,gender就可以了,即使没有指定id也没有给id插入值,新的数据行依旧有id并且递增
但是有时候执行错误sql语句,违反约束之类的,虽然报错不存储,但是会申请id号,虽然没存进去,但是id已经被他占了,再存下一条时会发现id跳了一位,比如1,2,3,5四不见了,就是这个原因。
三,外键约束
概念: 外键连接用来让两张表的数据之间建立连接,保证数据的一致和完整
比如员工表里有姓名,年龄,入职时间,部门id。但是部id字段这里只是数字id号,无法判断到底是那个部门,就需要把此表部门id字段关联到另外一张部门表的id字段,部门表里阐述每个部门id对应那个具体部门。
员工表有外键约束的称为子表,外键所关联的部门表也被称为父表
外键连接语法有两种,下面分别演示
1,这种语法用于创建表时添加外键约束
create table 表名(
字段名 数据类型,【常规约束】【comment注释】
....
【constraint】【外键名称】foreing key(外键字段名)references 父表(需要被连接的父列表名))
2,这种语法用于对已存在的表,在添加外键约束
alter table 表名 add constraint 外键名称(名字随便起,要记住删除时要用) foreign key(外键字段名)references 父表名(父表字段名);
alter table 表名 修改指定表
add constraint:添加约束。 后面的外键名称随便起,但是要记住,删除时要用
foreign key (外键字段名) 括号前面创建外键约束关键字,括号里面是子表中具体需要做外键的字段名。
references 父表 (需要被连接的父列表名)) references是固定关键字,后面跟父表名和需要被连接的父表中字段。
在查表时,字段上红色小钥匙是主键;发起外键表的对应字段上有蓝色小钥匙
父表里,如果有被别的子表连接,是不能修改,删除的,会报错,只有去掉所有子连接,才能改父表对应字段内容
3,删除外键语法
alter table 表名 drop foreign key 外键名称;
四,外键删除更新行为
no action 当在父表中删除/更新对应记录,首先检测该记录是否有对应外键,如果有则不允许删除/更新(与下一条restrict一致)
restrict 与上一条no action一模一样
cascade 当父表中删除/更新对应记录时,首先检测该记录是否有对应外键,如果有,则也同步删除/更新到子表对应字段中
set null 当在父表中删除对应记录时,首先检测该记录是否有对应外键,如果有则设置子表中对应值为null
set default 父表变更时,子表将外键设置成一个默认值(lnnodb不支持)
操作很简单把创建外键命令搬过来
alter table 表名 add constraint 外键名称 foreign key(外键字段名)references 父表名(父表字段名);
在创建外键命令最后加入,on update:意思是父表更新时怎么操作和on delete:意思是父表删除时怎么操作
只需要在这两个后面加上本章学习的对应选项
注意!!看清楚本章前面三个选项,放在on delete和on delete是含义不同的,放在前面代表删除操作,放在后面代表对应更新操作。根据需求在这两个地方填入适合的选项。。