mysql完整性约束实例详解
本文实例讲述了mysql完整性约束。分享给大家供大家参考,具体如下:
主要内容
- notnull与default
- unique
- primary
- auto_increment
- foreignkey
约束条件作用:用于保证数据的完整性和一致性
主要分为
PRIMARYKEY(PK) #标识该字段为该表的主键,可以唯一的标识记录
FOREIGNKEY(FK) #标识该字段为该表的外键
NOTNULL #标识该字段不能为空
UNIQUEKEY(UK) #标识该字段的值是唯一的,
AUTO_INCREMENT #标识该字段的值自动增长(整数类型,而且为主键)
DEFAULT #为该字段设置默认值
UNSIGNED#无符号
ZEROFILL#使用0填充
unique
在mysql中称为单列唯一
#例子1: createtabledepartment( idint, namechar(10)unique ); mysql>insertintodepartmentvalues(1,'it'),(2,'it'); ERROR1062(23000):Duplicateentry'it'forkey'name' #例子2: createtabledepartment( idintunique, namechar(10)unique ); insertintodepartmentvalues(1,'it'),(2,'sale'); #第二种创建unique的方式 createtabledepartment( idint, namechar(10), unique(id), unique(name) ); insertintodepartmentvalues(1,'it'),(2,'sale');
联合唯一:只要两列记录,有一列不同,既符合联合唯一的约束
#创建services表 mysql>createtableservices( ->idint, ->ipchar(15), ->portint, ->unique(id), ->unique(ip,port) ->); QueryOK,0rowsaffected(0.05sec) mysql>descservices; +-------+----------+------+-----+---------+-------+ |Field|Type|Null|Key|Default|Extra| +-------+----------+------+-----+---------+-------+ |id|int(11)|YES|UNI|NULL|| |ip|char(15)|YES|MUL|NULL|| |port|int(11)|YES||NULL|| +-------+----------+------+-----+---------+-------+ 3rowsinset(0.01sec) #联合唯一,只要两列记录,有一列不同,既符合联合唯一的约束 mysql>insertintoservicesvalues ->(1,'192,168,11,23',80), ->(2,'192,168,11,23',81), ->(3,'192,168,11,25',80); QueryOK,3rowsaffected(0.01sec) Records:3Duplicates:0Warnings:0 mysql>select*fromservices; +------+---------------+------+ |id|ip|port| +------+---------------+------+ |1|192,168,11,23|80| |2|192,168,11,23|81| |3|192,168,11,25|80| +------+---------------+------+ 3rowsinset(0.00sec) mysql>insertintoservicesvalues(4,'192,168,11,23',80); ERROR1062(23000):Duplicateentry'192,168,11,23-80'forkey'ip'
auto_increment
约束:约束的字段为自动增长,约束的字段必须同时被key约束
不指定id,则自动增长
#创建student createtablestudent( idintprimarykeyauto_increment, namevarchar(20), sexenum('male','female')default'male' ); mysql>descstudent; +-------+-----------------------+------+-----+---------+----------------+ |Field|Type|Null|Key|Default|Extra| +-------+-----------------------+------+-----+---------+----------------+ |id|int(11)|NO|PRI|NULL|auto_increment| |name|varchar(20)|YES||NULL|| |sex|enum('male','female')|YES||male|| +-------+-----------------------+------+-----+---------+----------------+ rowsinset(0.17sec) #插入记录 mysql>insertintostudent(name)values('老白'),('小白'); QueryOK,2rowsaffected(0.01sec) Records:2Duplicates:0Warnings:0 mysql>select*fromstudent; +----+--------+------+ |id|name|sex| +----+--------+------+ |1|老白|male| |2|小白|male| +----+--------+------+ rowsinset(0.00sec)
指定id的情况
mysql>insertintostudentvalues(4,'asb','female'); QueryOK,1rowaffected(0.00sec) mysql>insertintostudentvalues(7,'wsb','female'); QueryOK,1rowaffected(0.01sec) mysql>select*fromstudent; +----+--------+--------+ |id|name|sex| +----+--------+--------+ |1|老白|male| |2|小白|male| |4|asb|female| |7|wsb|female| +----+--------+--------+ rowsinset(0.00sec) #再次插入一条不指定id的记录,会在之前的最后一条记录继续增长 mysql>insertintostudent(name)values('大白'); QueryOK,1rowaffected(0.00sec) mysql>select*fromstudent; +----+--------+--------+ |id|name|sex| +----+--------+--------+ |1|老白|male| |2|小白|male| |4|asb|female| |7|wsb|female| |8|大白|male| +----+--------+--------+ rowsinset(0.00sec)
对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长
mysql>deletefromstudent; QueryOK,5rowsaffected(0.00sec) mysql>select*fromstudent; Emptyset(0.00sec) mysql>select*fromstudent; Emptyset(0.00sec) mysql>insertintostudent(name)values('ysb'); QueryOK,1rowaffected(0.01sec) mysql>select*fromstudent; +----+------+------+ |id|name|sex| +----+------+------+ |9|ysb|male| +----+------+------+ rowinset(0.00sec) #应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它 mysql>truncatestudent; QueryOK,0rowsaffected(0.03sec) mysql>insertintostudent(name)values('xiaobai'); QueryOK,1rowaffected(0.00sec) mysql>select*fromstudent; +----+---------+------+ |id|name|sex| +----+---------+------+ |1|xiaobai|male| +----+---------+------+ rowinset(0.00sec) mysql> auto_increment_increment和auto_increment_offset
查看可用的开头auto_inc的词
mysql>showvariableslike'auto_inc%'; +--------------------------+-------+ |Variable_name|Value| +--------------------------+-------+ |auto_increment_increment|1| |auto_increment_offset|1| +--------------------------+-------+ rowsinset(0.02sec)
#步长auto_increment_increment,默认为1 #起始的偏移量auto_increment_offset,默认是1 #设置步长为会话设置,只在本次连接中有效 setsessionauto_increment_increment=5; #全局设置步长都有效。 setglobalauto_increment_increment=5; #设置起始偏移量 setglobalauto_increment_offset=3;
强调:Ifthevalueofauto_increment_offsetisgreaterthanthatofauto_increment_increment,thevalueofauto_increment_offsetisignored.
翻译:如果auto_increment_offset的值大于auto_increment_increment的值,则auto_increment_offset的值会被忽略
设置完起始偏移量和步长之后,再次执行showvariableslike'auto_inc%';
发现跟之前一样,必须先exit,再登录才有效。
mysql>showvariableslike'auto_inc%'; +--------------------------+-------+ |Variable_name|Value| +--------------------------+-------+ |auto_increment_increment|5| |auto_increment_offset|3| +--------------------------+-------+ rowsinset(0.00sec) #因为之前有一条记录id=1 mysql>select*fromstudent; +----+---------+------+ |id|name|sex| +----+---------+------+ |1|xiaobai|male| +----+---------+------+ rowinset(0.00sec) #下次插入的时候,从起始位置3开始,每次插入记录id+5 mysql>insertintostudent(name)values('ma1'),('ma2'),('ma3'); QueryOK,3rowsaffected(0.00sec) Records:3Duplicates:0Warnings:0 mysql>select*fromstudent; +----+---------+------+ |id|name|sex| +----+---------+------+ |1|xiaobai|male| |3|ma1|male| |8|ma2|male| |13|ma3|male| +----+---------+------+
清空表区分delete和truncate的区别:
deletefromt1;#如果有自增id,新增的数据,仍然是以删除前的最后一样作为起始。
truncatetablet1;数据量大,删除速度比上一条快,且直接从零开始。
foreignkey
理解foreignkey
如上图如果一个公司有很多员工,每个员工都对应一个部门,在填表的时候就会重复写这些部门,太冗余了
我们可以将它们分离
此时有两张表,一张是employee表,简称emp表(关联表,也就从表)。一张是department表,简称dep表(被关联表,也叫主表)。
#1.创建表时先创建被关联表,再创建关联表 #先创建被关联表(dep表) createtabledep( idintprimarykey, namevarchar(20)notnull, descripevarchar(20)notnull ); #再创建关联表(emp表) createtableemp( idintprimarykey, namevarchar(20)notnull, ageintnotnull, dep_idint, constraintfk_depforeignkey(dep_id)referencesdep(id)//创建约束 ); #2.插入记录时,先往被关联表中插入记录,再往关联表中插入记录 insertintodepvalues (1,'IT','IT技术有限部门'), (2,'销售部','销售部门'), (3,'财务部','花钱太多部门'); insertintoempvalues (1,'zhangsan',18,1), (2,'lisi',19,1), (3,'egon',20,2), (4,'yuanhao',40,3), (5,'alex',18,2);
3.删除表
#按道理来说,删除了部门表中的某个部门,员工表的有关联的记录相继删除。 mysql>deletefromdepwhereid=3; ERROR1451(23000):Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfails(`db5`.`emp`,CONSTRAINT`fk_name`FOREIGNKEY(`dep_id`)REFERENCES`dep`(`id`)) #但是先删除员工表的记录之后,再删除当前部门就没有任何问题 mysql>deletefromempwheredep_id=3; QueryOK,1rowaffected(0.00sec) mysql>select*fromemp; +----+----------+-----+--------+ |id|name|age|dep_id| +----+----------+-----+--------+ |1|zhangsan|18|1| |2|lisi|18|1| |3|egon|20|2| |5|alex|18|2| +----+----------+-----+--------+ 4rowsinset(0.00sec) mysql>deletefromdepwhereid=3; QueryOK,1rowaffected(0.00sec) mysql>select*fromdep; +----+-----------+----------------------+ |id|name|descripe| +----+-----------+----------------------+ |1|IT|IT技术有限部门| |2|销售部|销售部门| +----+-----------+----------------------+ 2rowsinset(0.00sec)
上面的删除表记录的操作比较繁琐,按道理讲,裁掉一个部门,该部门的员工也会被裁掉。其实呢,在建表的时候还有个很重要的内容,叫同步删除,同步更新
ondeletecascade#同步删除
onupdatecascade#同步更新
createtableemp( idintprimarykey, namevarchar(20)notnull, ageintnotnull, dep_idint, constraintfk_depforeignkey(dep_id)referencesdep(id) ondeletecascade#同步删除 onupdatecascade#同步更新 );
#再去删被关联表(dep)的记录,关联表(emp)中的记录也跟着删除 mysql>deletefromdepwhereid=3; QueryOK,1rowaffected(0.00sec) mysql>select*fromdep; +----+-----------+----------------------+ |id|name|descripe| +----+-----------+----------------------+ |1|IT|IT技术有限部门| |2|销售部|销售部门| +----+-----------+----------------------+ 2rowsinset(0.00sec) mysql>select*fromemp; +----+----------+-----+--------+ |id|name|age|dep_id| +----+----------+-----+--------+ |1|zhangsan|18|1| |2|lisi|19|1| |3|egon|20|2| |5|alex|18|2| +----+----------+-----+--------+ 4rowsinset(0.00sec) #再去更改被关联表(dep)的记录,关联表(emp)中的记录也跟着更改 mysql>updatedepsetid=222whereid=2; QueryOK,1rowaffected(0.02sec) Rowsmatched:1Changed:1Warnings:0 #赶紧去查看一下两张表是否都被删除了,是否都被更改了 mysql>select*fromdep; +-----+-----------+----------------------+ |id|name|descripe| +-----+-----------+----------------------+ |1|IT|IT技术有限部门| |222|销售部|销售部门| +-----+-----------+----------------------+ 2rowsinset(0.00sec) mysql>select*fromemp; +----+----------+-----+--------+ |id|name|age|dep_id| +----+----------+-----+--------+ |1|zhangsan|18|1| |2|lisi|19|1| |3|egon|20|222| |5|alex|18|222| +----+----------+-----+--------+ 4rowsinset(0.00sec)
更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
声明:本文内容来源于网络,版权归原作者所有,内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:czq8825#qq.com(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。