mysql完整性约束实例详解

2023-08-03 14:02:04 83

本文实例讲述了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（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

mysql完整性约束实例详解

热门推荐

随机推荐