Mysql基础:字段约束条件:无符号、零填充、非空、默认值、唯一值、主键、自增、外键前戏、关系的判断、一对多关系、外键字段的建立、多对多关系、一对一关系、

目录

• 主题:字段约束条件
  • 一、无符号、零填充
    • 无正负符号
    • 字段数据长度不够用0填充
  • 二、非空
  • 三、默认值
  • 四、唯一值
    • 单列唯一
    • 多列唯一
  • 五、主键
    • 代码验证主键作用
    • InnoDB存储引擎规定一张表必须有且只有一个主键
  • 六、自增
    • 功能简介
    • 特殊情况1
    • 特殊情况2
    • 特殊情况3
    • 特性描述
  • 七、外键前戏
    • 1、外键的定义
    • 2、外键的作用
      • 阻止执行
      • 级联执行
    • 3、外键创建限制
    • 4、例子引入
      • 出现的问题：
      • 解决方案
  • 八 、表关系的判断
  • 九、一对多关系
  • 十、外键字段的建立
  • 十一、多对多关系
  • 十二、一对一关系
  • 十三、作业
    • 1、服务器表与应用程序表
    • 2、课程表与班级表
    • 3、学生表与班级表
    • 4、老师表与课程表
    • 5、书籍表与出版社表

主题:字段约束条件

• 什么是字段约束

简而言之，字段约束就是将字段的内容定一个规则，我们要按照规则办事，常见的字段约束有下面几个。

• 字段约束的作用

1、保证数据的完整性

描述：我们有时候填表会发现有些是必填项，这里就是not null的作用，他要求这个表格不能为空，获取我们完整的信息。

2、保证数据的有效性

描述：在这里我们假设一个个场景，要是张三的电话号码是123×××××××45，那么李四的电话号码绝对不会和张三一摸一样，这里就体现出unique的作用了

• 常用的约束条件作用概述

约束条件	作用
unsigned	去掉正负号
zerofill	字段数据长度不够用0填充
not null	让字段数据不能为空
default	设置字段默认值
unique	设置字段数据唯一
primary key	主键，不能为空且唯一

一、无符号、零填充

无正负符号

存储记录的时候取消正负号，这时候就只能存储0~max的数据值。

关键字：unsigned
	id int unsigned

字段数据长度不够用0填充

昨天学习后我们了解到，int字段括号内数字的作用是控制输出的长度，这里用上zerofill之后，如果输入333就会把前面空着的两个位置用0填充。

关键字：zerofill
	id int(5) zerofill

二、非空

关键字：not null

作用描述：当我们默认情况下创建字段的时候，是可以输入null让值为空的，但是加上not null之后就不能空着不输入该字段的值了，但是我们输入''，这样的空信息却是可以在not null条件下输入进去的，并且在查看的时候该字段在该条记录对应的值就是空的。

create table t1(
	id int,
 	name varchar(16)
);
insert into t1(id) values(1);
insert into t1(name) values('jason');
insert into t1(name,id) values('kevin',2);

我们发现默认情况下，也就是不设置字段约束的情况下，字段值是可以为空的，结果如下：

ps:所有字段类型不加约束条件的情况下默认都可以为空

create table t2(
	id int,
 	name varchar(16) not null
);
insert into t2(id) values(1);
insert into t2(name) values('jason');
insert into t2 values(1,'');
insert into t2 values(2,null);

这里我们发现添加了字段约束后我们添加记录的时候如果出现空的值就会报错。

三、默认值

关键字：default 默认值

作用描述：当我们设置了默认值字段约束的时候，可以在后面设置默认值，在添加记录的时候如果输入了值，就会使用输入的值，否则就会用默认值填充(可以跟python中的关键字参数类比记忆)。

create table t3(
	id int default 666,
	name varchar(16) default '匿名'
);
insert into t3(id) values(1);
insert into t3(name) values('jason');
insert into t3 values(2,'kevin');

四、唯一值

关键字：unique

作用描述：分成单列唯一和多列唯一两种使用方式，单列唯一就很好理解，一个字段内的所有数据值不能重复。多列唯一的意思是设置多列唯一的几个字段中的数据组合起来不能相同。

单列唯一

'''单列唯一'''
create table t4(
	id int unique,
	name varchar(32) unique
);
insert into t4 values(1,'jason'),(2,'jason');

这里我们就是用两个相同的数据值测试唯一性。结果如下：

多列唯一

'''联合唯一'''
create table t5(
	id int,
 	ip varchar(32),
 	port int,
 	unique(ip,port)
);
insert into t5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);
insert into t5 values(4,'127.0.0.1',8080);

这里我们在添加第四条记录的时候会报错，原因就是我们的第四条记录跟第一条记录的ip和port的值组合起来之后重复了。

五、主键

关键字：primary key

作用描述：单从约束层面上而言主键相当于not null + unique(非空且唯一)

ps：主键分成单列主键和联合主键，联合主键跟多列唯一用法一样，但是用的较少。

代码验证主键作用

	create table t6(
    	id int primary key,
    	name varchar(32)
    );
	insert into t6(name) values('jason');
 	insert into t6 values(1,'kevin');
 	insert into t6 values(1,'jerry');

结果如下：

InnoDB存储引擎规定一张表必须有且只有一个主键

InnoDB存储引擎规定了所有的表都必须有且只有一个主键(主键是组织数据的重要条件并且主键可以加快数据的查询速度)，因此当我们没有添加主键的时候InnoDB存储引擎会自动设置一个字段当作主键，有以下两种设置方式：

方式一：

当表中没有主键也没有其他非空切唯一的字段的情况下。
​ InnoDB会采用一个隐藏的字段作为表的主键，隐藏意味着无法使用，基于该表的数据查询只能一行行查找，速度很慢。

方式二：

当表中没有主键但是有其他非空且唯一的字段，那么会从上往下将第一个该字段自动升级为主键。

    	create table t7(
        	id int,
         	age int not null unique,
          	phone bigint not null unique,
          	birth int not null unique,
         	height int not null unique
        );

ps：我们在创建表的时候应该有一个字段用来标识数据的唯一性，并且该字段通常情况下就是'id'(编号)字段。
id nid sid pid gid uid

0create 0table userinfo(
uid int primary key,
);

六、自增

功能简介

关键字：auto_increment

作用描述：在设置了自增的字段下，我们如果不输入对应的数据值，他会自动给值，同时也会在下一次记录数据值的时候自动把上一次的值加一当作结果绑定给下一次的记录，前提是这一次记录数据值的时候也没有给定值。

特殊情况1

把表的id字段设置成自增，然后在添加第一条记录，我们把id的值设置成200，后续添加记录的时候如果没有给定id 的值，就会从200开始自增。

特殊情况2

把表的id字段设置成自增，然后在添加第一条记录，我们把id的值设置成200，如果再次添加一条记录id的值也是200的话，就会报错。

特殊情况3

把表的id字段设置成自增，然后在添加第一条记录，我们把id的值设置成200，接着再添加几条数据值，让他自增，接着我们删除id为200的记录，我们就可以在添加记录的时候重新添加一条id字段值为200的记录。

ps：该约束条件不能单独出现，并且一张表中只能出现一次，主要就是配合主键一起用。

create table t8(
	id int primary key,
 	name varchar(32)
);

create table t9(
	id int primary key auto_increment,
 	name varchar(32)
);
	insert into t8 values(200,'zzh');
	insert into t8(name) values('jason');

结果如下：

特性描述

自增特性
自增不会因为数据的删除而回退 永远自增往前
如果自己设置了更大的数 则之后按照更大的往前自增

如果想重置某张表的主键值 可以使用

truncate t9; 

清空表数据并重置主键。

七、外键前戏

1、外键的定义

外键是某个表中的一列，它包含在另一个表的主键中。

外键也是索引的一种，是通过一张表中的一列指向另一张表中的主键，来对两张表进行关联。

一张表可以有一个外键，也可以存在多个外键，与多张表进行关联。

2、外键的作用

外键的主要作用是保证数据的一致性和完整性，并且减少数据冗余。
主要体现在以下两个方面：

阻止执行

从表插入新行，其外键值不是主表的主键值便阻止插入。
从表修改外键值，新值不是主表的主键值便阻止修改。
主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)。
主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。

级联执行

这里需要提前设置

on update cascade
on delete cascade

关联主表和从表

主表删除行，连带从表的相关行一起删除。
主表修改主键值，连带从表相关行的外键值一起修改。

3、外键创建限制

父表必须已经存在于数据库中，或者是当前正在创建的表。

如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

必须为父表定义主键。

外键中列的数目必须和父表的主键中列的数目相同。

两个表必须是 InnoDB 表，MyISAM 表暂时不支持外键。

外键列必须建立了索引，MySQL 4.1.2 以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立。

外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如 int 和tinyint 可以，而 int 和 char 则不可以；

4、例子引入

我们需要一张员工表
id name age dep_name（部门名称） dep_desc（部门功能）

出现的问题：

1.表语义不明确(到底是员工表还是部门表)

2.存取数据过于冗余(浪费存储空间)，如部门名称和功能重复出现。

3.数据的扩展性极差（修改的时候会遇到很的问题，比较繁琐）

解决方案

将上述表一分为二
员工表：id name age
部门表：id dep_name dep_desc

上述的三个问题全部解决，但是员工跟部门之间没有了关系，接着我们引出外键来解决这个问题：

外键字段:用于标识数据与数据之间关系的字段。

八 、表关系的判断

表关系、数据关系其实意思是一样的，只是知识说法上有区分。

关系总共有四种：

• 一对多
• 多对多
• 一对一
• 没有关系

关系的判断可以采用'换位思考'原则

九、一对多关系

以员工表和部门表为例
1.先站在员工表的角度
问:一名员工能否对应多个部门
答:不可以
2.再站在部门表的角度
问:一个部门能否对应多名员工
答:可以
结论:一个可以一个不可以 那么关系就是'一对多'
针对'一对多'关系 外键字段建在'多'的一方

十、外键字段的建立

因为外键字段需要引入其他表中的字段，所以我们应该先创建没有外键的表，再创建含有外键的表。

小技巧:先定义出含有普通字段的表 之后再考虑外键字段的添加

python

create table emp(
	id int primary key auto_increment,
 	name varchar(32),
 	age int,
 	dep_id int,
	foreign key(dep_id) references dep(id)
);

create table dep(
	id int primary key auto_increment,
 	dep_name varchar(32),
  	dep_desc varchar(64)
);

1.创建表的时候一定要先创建被关联表
2.录入表数据的时候一定要先录入被关联表
3.修改数据的时候外键字段无法修改和删除

针对3有简化措施>>>:级联更新级联删除

python

create table emp1(
	 id int primary key auto_increment,
 	 name varchar(32),
 	 age int,
 	 dep_id int,
	 foreign key(dep_id) references dep1(id) 
    on update cascade 
    on delete cascade
);

create table dep1(
	id int primary key auto_increment,
 	dep_name varchar(32),
  	dep_desc varchar(64)
);

这里我们可以看到外键的key值是MUL，意为复合约束条件

• 外键其实是强耦合，不符合解耦合的特性，会导致维护难度和成本变高。
• 所以很多时候 实际项目中当表较多的情况 我们可能不会使用外键 而是使用代码建立逻辑层面的关系

十一、多对多关系

以书籍表与作者表为例
1.先站在书籍表的角度
问:一本书能否对应多个作者
答:可以
2.再站在作者表的角度
问:一个作者能否对应多本书
答:可以

结论:两个都可以，关系就是'多对多'

针对'多对多'关系不能在表中直接创建，需要新建第三张关系表。

python

create table book(
	id int primary key auto_increment,
 	title varchar(32),
  	price float(5,2)
);
create table author(
	id int primary key auto_increment,
 	name varchar(32),
  	phone bigint
);
create table book2author(
	id int primary key auto_increment,
   author_id int,
 	foreign key(author_id) references author(id)
  	on update cascade
  	on delete cascade,
   book_id int,
  	foreign key(book_id) references book(id)
  	on update cascade
  	on delete cascade
);

十二、一对一关系

以用户表与用户详情表为例

1.先站在用户表的角度

问:一个用户能否对应多个用户详情
​ 答:不可以

2.再站在用户详情表的角度

问:一个用户详情能否对应多个用户
​ 答:不可以

结论:两个都可以 关系就是'一对一'或者没有关系

针对'一对一'外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中

create table user(
	id int primary key auto_increment,
  	name varchar(32),
 	detail_id int unique,
  	foreign key(detail_id) references userdetail(id)
  	on update cascade
  	on delete cascade
);

create table userdetail(
	id int primary key auto_increment,
  	phone bigint
);

十三、作业

判断下列表数据关系 并自定义创建出表
有些表数据关系不是确定 根据具体业务可能有变化

1、服务器表与应用程序表

表关系：多对多

服务器表：server_id/server_name/

create table server(server_id int primary key auto_increment,server_name varchar(16));

insert into server(server_name) values('server1');
insert into server(server_name) values('server2');
insert into server(server_name) values('server3');
创建三台服务器

应用程序表：app_id/app_name/

create table app(app_id int primary key auto_increment,app_name varchar(16));

insert into app(app_name) values('app1');
insert into app(app_name) values('app2');
insert into app(app_name) values('app3');
创建三个应用程序

第三个存放关系的表：id/server_id/app_id/

create table server_app(id int primary key auto_increment,serverid int,foreign key(serverid) references server(server_id) on update cascade on delete cascade,appid int,foreign key(appid) references app(app_id) on update cascade on delete cascade);

添加server和app直接的关系
insert into server_app(serverid,appid) values(1,1);
insert into server_app(serverid,appid) values(1,2);
insert into server_app(serverid,appid) values(2,3);
insert into server_app(serverid,appid) values(3,1);
insert into server_app(serverid,appid) values(3,2);
insert into server_app(serverid,appid) values(3,3);

讲解：
服务器1，运行了应用1和应用2
服务器2，运行了应用3
服务器3，运行了应用123
换到应用的角度
应用1，在服务器1和服务器3上运行了
应用2，也在服务器1和服务器3上运行了
应用3，在服务器2和服务器3上运行了

2、课程表与班级表

表关系：多对多

课程表：course_id/course_name/

create table course(course_id int primary key auto_increment,course_name varchar(16));

insert into course(course_name) values('course1');
insert into course(course_name) values('course2');
insert into course(course_name) values('course3');
创建三个课程

班级表：class_id/class_name

create table class(class_id int primary key auto_increment,class_name varchar(16));

insert into class(class_name) values('class1');
insert into class(class_name) values('class2');
insert into class(class_name) values('class3');
创建三个班级

第三个存放关系的表：id/course_id/class_id

create table course_class(id int primary key auto_increment,courseid int,foreign key(courseid) references course(course_id) on update cascade on delete cascade,classid int,foreign key(appid) references class(class_id) on update cascade on delete cascade);

添加course和class直接的关系
insert into course_class(courseid,classid) values(1,1);
insert into course_class(courseid,classid) values(1,2);
insert into course_class(courseid,classid) values(2,3);
insert into course_class(courseid,classid) values(3,1);
insert into course_class(courseid,classid) values(3,2);
insert into course_class(courseid,classid) values(3,3);

讲解：
课程1，被班级1和班级2选了
课程2，被班级3选了
课程3，被班级123选了
换到班级的角度
班级1，选了课程1和课程3
班级2，也选了课程1和课程3
班级3，选了课程2和课程3

3、学生表与班级表

表关系：一对多

学生表：student_id/student_name

create table student(student_id int primary key auto_increment,student_name varchar(16),classid int,foreign key(classid) references class(class_id) on update cascade on delete cascade);

insert into student(student_name,classid) values('student1',3);
insert into student(student_name,classid) values('student2',2);
insert into student(student_name,classid) values('student3',1);
insert into student(student_name,classid) values('student4',1);
insert into student(student_name,classid) values('student5',1);
insert into student(student_name,classid) values('student6',2);
创建六个学生并添加对应的班级信息

讲解：
这里的关系是一个学生只能对应一个班级，但是一个班级可以有多个学生。
班级3有学生1
班级2有学生2和学生6
班级1有学生3、4、5

班级表：class_id/class_name

create table class(class_id int primary key auto_increment,class_name varchar(16));

insert into class(class_name) values('class1');
insert into class(class_name) values('class2');
insert into class(class_name) values('class3');
创建三个班级

4、老师表与课程表

表关系：多对多

老师表：teacher_id/teacher_name

create table teacher(teacher_id int primary key auto_increment,teacher_name varchar(16));

insert into teacher(teacher_name) values('teacher1');
insert into teacher(teacher_name) values('teacher2');
insert into teacher(teacher_name) values('teacher3');
创建三个老师

课程表：class_id/class_name

create table class(class_id int primary key auto_increment,class_name varchar(16));

insert into class(class_name) values('class1');
insert into class(class_name) values('class2');
insert into class(class_name) values('class3');
创建三个班级

第三个存放关系的表：id/teacher_id/class_id

create table teacher_class(id int primary key auto_increment,teacherid int,foreign key(teacherid) references teacher(teacher_id) on update cascade on delete cascade,classid int,foreign key(classid) references class(class_id) on update cascade on delete cascade);

添加teacher和class直接的关系
insert into teacher_class(teacherid,classid) values(1,1);
insert into teacher_class(teacherid,classid) values(1,2);
insert into teacher_class(teacherid,classid) values(2,3);
insert into teacher_class(teacherid,classid) values(3,1);
insert into teacher_class(teacherid,classid) values(3,2);
insert into teacher_class(teacherid,classid) values(3,3);

讲解：
老师1，选择上课程1和课程2
老师2，选择上课程3
老师3，选择上课程123
换到课程的角度
课程1，由老师1和老师3一起上
课程2，由老师1和老师3一起上
课程3，由老师2和老师3一起上

5、书籍表与出版社表

表关系：多对多

书籍表：book_id/book_name

create table book(book_id int primary key auto_increment,book_name varchar(16));

insert into book(book_name) values('book1');
insert into book(book_name) values('book2');
insert into book(book_name) values('book3');
创建三本书

出版社表：publishing_house_id/publishing_house_name

create table publishing_house(publishing_house_id int primary key auto_increment,publishing_house_name varchar(16));

insert into publishing_house(publishing_house_name) values('publishing_house1');
insert into publishing_house(publishing_house_name) values('publishing_house2');
insert into publishing_house(publishing_house_name) values('publishing_house3');
创建三个出版社

第三个存放关系的表：id/book_id/publishing_house_id

create table book_publishing_house(id int primary key auto_increment,bookid int,foreign key(bookid) references book(book_id) on update cascade on delete cascade,publishing_houseid int,foreign key(publishing_houseid) references publishing_house(publishing_house_id) on update cascade on delete cascade);

添加book和publishing_house直接的关系
insert into book_publishing_house(bookid,publishing_houseid) values(1,1);
insert into book_publishing_house(bookid,publishing_houseid) values(1,2);
insert into book_publishing_house(bookid,publishing_houseid) values(2,3);
insert into book_publishing_house(bookid,publishing_houseid) values(3,1);
insert into book_publishing_house(bookid,publishing_houseid) values(3,2);
insert into book_publishing_house(bookid,publishing_houseid) values(3,3);

讲解：
书本1，被出版社1出版了，也被出版社2出版了
书本2，被出版社3出版了
书本3，被出版社123各自出版了一次

换到出版社的角度
出版社1，出版了书本1和书本3
出版社2，出版了书本1和书本3
出版社3，出版了书本2和书本3