MySQL字段完整性约束(重要)

Wesley13
• 阅读 712

[TOC]

完整性约束(重要)

  • primary key:主键,唯一标识,表都会拥有,不设置为默认找第一个 不空,唯一 字段,未标识则创建隐藏字段

  • foreign key:外键,外键要通过foreign key 语法建立表与表之间的关联

  • unique key:唯一性数据, 该条字段的值需要保证唯一,不能重复

  • auto_increment:自增,只能加给key的int类型字段,作为辅助修饰,一个表中只能设置一个自增字段

  • not null:不为空 - 针对一些字段,如注册时的用户名,出生人的性别等,这些需求下的字段,只不能设置为Null,必须要对其赋值

  • default:默认值 - 对有默认值意外的字段进行赋值时,有默认值的字段会被赋默认值

  • unsigned:无符号 - 存储的数字从0开始

  • zerofill: 0填充 - 存整数时数据长度小于取值范围长度,会在数字左方用0填充

    not null 与 default 限制

    不能为空,没有默认值的x,必须赋值

    y、z在没有赋值情况下,才有默认值,设置值后,采用默认值

    mysql>: create table td1 (x int not null, y int default 0, z int default 100);

    报错,auto_increment必须设置给 键字段

    mysql>: create table td2 (x int auto_increment);

    报错,auto_increment必须设置给 int字段

    mysql>: create table td2 (x char(4) auto_increment);

    报错,auto_increment字段最多出现 1次

    mysql>: create table td2 (x int unique auto_increment, y int unique auto_increment);

    正确,主键和唯一键分析

    x为主键:没有设置primary key时,第一个 唯一自增键,会自动提升为主键

    mysql>: create table td21 (x int unique auto_increment, y int unique);

    y为主键:没有设置primary key时,第一个 唯一自增键,会自动提升为主键

    mysql>: create table td22 (x int unique, y int unique auto_increment);

    x为主键:设置了主键就是设置的,主键没设置自增,那自增是可以设置在唯一键上的

    mysql>: create table td23 (x int primary key, y int unique auto_increment);

    x为主键:设置了主键就是设置的,主键设置了自增,自增字段只能有一个,所以唯一键不能再设置自增了

    mysql>: create table td24 (x int primary key auto_increment, y int unique);

    默认主键:没有设置主键,也没有 唯一自增键,那系统会默认添加一个 隐式主键(不可见)

    mysql>: create table td25 (x int unique, y int unique);

    唯一键:确保一个字段,数据不能重复

    主键:是一条记录的唯一标识(可以理解为数据的编号)

    联合唯一

    ip在port不同时,可以相同,ip不同时port也可以相同,均合法

    ip和port都相同时,就是重复数据,不合法

    mysql>: create table tu1 (ip char(16), port int, unique(ip, port));

    也可以设置成 联合主键,道理同 联合唯一

    mysql>: create table tu2 (ip char(16), port int, primary key(ip, port));

    sql可以多行书写

    mysql>: create table t22( ip char(16), port int, primary key(ip,port) );

    通常自增字段的 自增索引 会被永久记录,想清空表并清空自增索引:

    mysql>: truncate 数据库名.表名

外键

外键是 建立表与表关联 的字段,通常 一个表的外键 是 另一个表的主键(唯一键也可以)

1、外键的 字段名 可以自定义(名字随意),通常命名规范(关联表_关联字段)

2、外键要通过 foreign key 语法建立表与表之间的关联

mysql>: foreign key(所在表的外键字段) references 关联表(关联表已有字段)
eg:foreign key(detail_id) references author_detail(id)

3、级联关系(更新一起更新,删一起删)
    级联更新 on update cascade
    级联删除 on delete cascade
    
# 重点:外键字段本身可以唯一或不唯一,但是外键字段必须唯一

多表关系

一对一

例如:丈夫-妻子,用户-身份证,作者-作者详情

外键在任何一方都可以,此时外键要设置 唯一键

没有级联关系:

  • 增加:先增加被关联表记录,再增加关联表记录

  • 删除:先删除关联表记录,再删除被关联表记录

  • 更新:关联与被关联表都无法完成 关联的外键和主键 数据更新 - (如果被关联表记录没有被绑定,可以修改)

    """ 作者(author):id,name,sex,age,mobile 作者详情(author_detail): id,info,address,author_id """

    建表

    作者详情(author_detail): id,info,address

    create table author_detail( id int primary key auto_increment, info char(50), address char(50) );

    作者表(author): id,name,sex,age,mobile, detail_id

    create table author( id int primary key auto_increment, name char(10) not null, sex enum("男","女") default "男", age int not null, mobile char(13) unique key not null, detail_id int unique not null, # 一对一的表必须添加唯一键,并且类型和被关联的字段类型一致 foreign key(detail_id) references author_detail(id) # 设置外键detail_id,是author_detail表中的字段id );

    插入

    必须先创建被关联表数据,有关联表外键关联的记录后,关联表才可以创建数据

    mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address'); mysql>: insert into author(name,age,mobile,detail_id) values('Tom',18,'13344556677', 1);

    修改(在被关联表中已被关联的记录不可以直接修改,关联表修改的前提是被关联表必须有至少一条记录没有被其他表关联)先修改关联表重新指向被关联表中新的记录。在修改被关联表的原记录

    修改关联表

    mysql>: update author set detail_id=2 where detail_id=1; # 当被关联表有id为2的既可以

    修改被关联表

    mysql>: update author_detail set id=10 where id=1;

    删除(先删除关联表记录,再删除被关联表记录)

    删除关联表记录

    mysql>: delete from author where detail_id=1; # 直接删除

    删除被关联表记录

    mysql>: delete from author_detail where id=2; # 当被关联表中数据没有被关联是可以修改

有级联关系:

  • 更新级联关系:on update cascade,要改一起改

  • 删除级联关系:on delete cascade,要删一起删

    """ 作者(author):id,name,sex,age,mobile 作者详情(author_detail): id,info,address,author_id """

    建表

    作者详情(author_detail): id,info,address

    create table author_detail( id int primary key auto_increment, info char(50), address char(50) );

    作者表(author): id,name,sex,age,mobile, detail_id

    create table author( id int primary key auto_increment, name char(10) not null, sex enum("男","女") default "男", age int not null, mobile char(13) unique key not null, detail_id int unique not null, # 一对一的表必须添加唯一键,并且类型和被关联的字段类型一致 foreign key(detail_id) references author_detail(id) # 设置外键detail_id,是author_detail表中的字段id on update cascade # 创建更新级联关系 on delete cascade # 创建删除级联关系 );

    插入

    必须先创建被关联表数据,有关联表外键关联的记录后,关联表才可以创建数据

    mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address'); mysql>: insert into author(name,age,mobile,detail_id) values('Tom',18,'13344556677', 1);

    修改(一起改)

    修改关联表

    mysql>: update author set detail_id=2 where detail_id=1; # 当被关联表有id为2的既可以

    修改被关联表

    mysql>: update author_detail set id=10 where id=1;

    删除(一起删)

    删除关联表记录

    mysql>: delete from author where detail_id=1; # 直接删除

    删除被关联表记录

    mysql>: delete from author_detail where id=2; # 当被关联表中数据没有被关联是可以修改

一对多

例如:部门-员工,班级-学生,书-出版社

外键必须放在多的一方,此时外键值不唯一

## 建表
# 出版社(publish): id,name,address,phone
create table publish(
    id int primary key auto_increment,
    name varchar(64),
    address varchar(256),
    phone char(20)
);

# 书(book):id,name,price,publish_id, author_id
create table book(
    id int primary key auto_increment,
    name varchar(64) not null,
    price decimal(5, 2) default 0,
    publish_id int,  # 一对多的外键不能设置唯一
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade
);

# 增:先增加被关联表(publish)的数据,再增加关联表(book)的数据
mysql>: insert into publish(name, address, phone) values
('人民出版社', '北京', '010-110'),
('西交大出版社', '西安', '010-119'),
('淮南师范出版社', '上海', '010-120');

mysql>: insert into book(name, price, publish_id) values
('西游记', 6.66, 1),
('东游记', 8.66, 1),
('python从入门到入土', 2.66, 2),
('轮程序员修养之道', 3.66, 3),
('好好活着', 88.88, 3);
# 没有被关联的字段,插入依旧错误
mysql>: insert into book(name, price, publish_id) values ('打脸之道', 0.3, 4);  # 失败


# 更新:直接更新被关联表的(publish) 主键,关联表(book) 外键 会级联更新
mysql>: update publish set id=10 where id=1;
# 更新:直接更新关联表的(book) 外键,修改的值对应被关联表(publish) 主键 如果存在,可以更新成功,反之失败
mysql>: update book set publish_id=2 where id=4;  # 成功
mysql>: update book set publish_id=1 where id=4;  # 失败


# 删:
#    删被关联表,关联表会被级联删除
mysql>: delete from publish where id = 2;

#    删关联表,被关联表不会发生变化
mysql>: delete from book where publish_id = 3;

多对多

例如:老师-班级,课程-学生,出版社-作者

一定要创建第三张表(关系表),每一个外键值不唯一,看可以多个外键建立联合唯一

# 多对多:一定要创建第三张表(关系表),每一个外键值不唯一,看可以多个外键建立联合唯一

# 作者(author):id, name, age
create table author(
    id int primary key auto_increment,
    name varchar(64),
    age int unsigned default 0
);

# 出版社(publish):id, name, address
create table publish(
    id int primary key auto_increment,
    name varchar(64),
    address varchar(256)
);

# 作者与出版社关系表:id, author_id, publish_id
create table author_publish(
    id int primary key auto_increment,
    # 关系表一定有多个外键,关联着多张表
    # 关联作者表
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    # 关联出版社表
    publish_id int,
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade,
    # 建立两个字段的联合唯一
    unique(author_id, publish_id)
);

# 注:关系表 关联着 作者 和 出版社 两张表,在表结构上 作者 与 出版社 两表键没有任何关系


# 增:两张被关联表,没有前后关系,但关系表必须在两个表都提供数据后才能进行 关系匹配
mysql>: insert into author(name, age) values('ruakei', 67),('engo', 76),('Lxx', 3);
mysql>: insert into publish(name, address) values('老男孩出版社', '上海'),('小女孩出版社', '北京');

# 操作关系表:
mysql>: insert into author_publish(author_id, publish_id) values(1,1),(1,2),(2,1),(2,2),(3,1);

# 关系表操作:增、删、改,只要两张被关系表有提供对应的操作数据,都可以操作成功,且对两张被关系表没有影响


# 操作两张被关系表:
#        增:不会影响关系表
mysql>: insert into publish(name, address) values('西交大出版社', '西安');
#        改:关系表都会级联更新
mysql>: update publish set id=10 where id=1;
#        删:关系表都会级联删除
mysql>: delete from author where name='ruakei';
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
MySQL创建表时加入的约束以及外键约束的的意义
1,创建表时加入的约束a) 非空约束,notnullb) 唯一约束,uniquec) 主键约束,primarykeyd) 外键约束,foreignkey1,非空约束,针对某个字段设置其值不为空,如:学生的姓名不能为空droptableifexistst_studen
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL(三) 完整性约束
一.介绍  约束条件与数据类型的宽度意义,都是可选参数.  作用:用于保证数据的完整性和一致性.  主要分为:PRIMARYKEY(PK)标识该字段为该表的主键,可以唯一的标识记录FOREIGNKEY(FK)标识该字段为该表的外键NOTNULL标识该字段不能为空
Wesley13 Wesley13
3年前
MySQL中添加、删除约束
MySQL中6种常见的约束:主键约束(primarykey)、外键约束(foreignkey)、非空约束(notnull)、唯一性约束(unique)、默认值约束(defualt)、自增约束(aoto\_increment),下面是添加、删除这几种约束的一些方法。\我已经建了数据库;1\添加约束21、建表时添加约
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这