首页 > 数据库 >MySQL之约束条件及表间关系

MySQL之约束条件及表间关系

时间：2022-11-24 20:56:32浏览次数：73  
标签：约束条件 insert int into values 及表间 key MySQL id

MySQL之约束条件

MySQL创建表的完整语法中，每个字段名后必须跟随字段类型，还可以选择跟随约束条件。

本文将介绍常见的约束条件，首先先创建一个测试约束条件的库：

create database constraint_test;
use constraint_test;

无符号和零填充

在MySQL的字段类型我们已经介绍了数字类型的字段可以加装unsigned和zerofill这两个约束条件。

  • unsigned，将数字的符号位取消，变成多一个比特位的无符号数

  • zerofill，当填入数字字段的数据没有到最大存储位数，会以0来填充

    如id int(5) zerofill，虽然5实际上并不是int的最大存储位数，但是会作为0填充的依据，会用0填充到5位。

非空 not null

create table t1(
    id int,
	name varchar(16) not null
     );
desc t1;

image

  • 被约束条件not null约束的字段，插入的数据不能为null

    insert into t1(id) values(1);  # 插入一行数据，没有指定的数据就默认插入null
ERROR 1364 (HY000): Field 'name' doesn't have a default value

  • null是空数据，并不等同于''空字符

    insert into t1 values(1, '');
select * from t1;

    image

默认值 default

create table t2(
id int,
name varchar(16) default '匿名',
gender enum('male','female') default 'male'
    );
desc t2;

image

  • 在没有加约束条件default时，其实默认值为null
  • 当插入一行数据时，没有被指定的数据会被自动填入默认值
  • 加约束条件default 默认值时，自动填入的默认值就变了。
insert into t2(id,name) values(1, 'leethon');
insert into t2(name, gender) values('maria','female');
insert into t2(id, gender) values(2 ,'male');
select * from t2;

image

唯一值 unique

单列唯一

create table t3(
id int unique,
name varchar(32) unique
);
desc t3;

image

  • 单列唯一的字段，每一插入的值都不能重复
mysql> insert into t3 values(1, 'leethon');  # 插入一行值
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(1, 'maria');  # 插入一个id一样的值
ERROR 1062 (23000): Duplicate entry '1' for key 'id'  # 报错，说键id下重复进入了1
mysql> insert into t3 values(2, 'leethon');  # 插入一个名字一样的值
ERROR 1062 (23000): Duplicate entry 'leethon' for key 'name'  # 报错

联合唯一

create table t4(
id int,
ip char(15),
port int,
unique(ip, port)
);
desc t4;

image

  • 联合统一的几个字段，其插入的值组合起来必须是唯一的，但是单字段下的值可以重复
insert into t4 values(1,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.1',8081);  # ip字段一样，但不报错
insert into t4 values(3,'192.168.1.0',8080);  # port一样，但不报错
insert into t4 values(4,'127.0.0.1',8080);  # 报错，Duplicate entry '127.0.0.1-8080' for key 'ip'
select * from t4;

image

主键 primary key

  1. 单从约束来看，primary key就等于unique+not null，非空且唯一。
  2. 在innodb存储引擎规定了所有的表都必须有且只有一个主键，因为主键是组织数据的重要条件并且主键可以加快数据的查询速度。
    • 当表中没有主键也没有其他非空切唯一的字段的情况下
      InnoDB会采用一个隐藏的字段作为表的主键，隐藏意味着无法使用，基于该表的数据查询只能一行行查找，速度很慢。
    • 当表中没有主键但是有其他非空且唯一的字段
      那么会从上往下将第一个该字段自动升级为主键。
create table t5(
id int primary key,
name varchar(16)
);
desc t5;

image

添加主键的字段的null属性和key属性都发生了标识变化

insert into t5 values(1, 'leethon');
insert into t5 values(null, 'maria');  # 报错：Column 'id' cannot be null
insert into t5 values(1, 'cxy');  # 报错： Duplicate entry '1' for key 'PRIMARY'
select * from t5;

主键除了作为约束条件，还承担着组织数据的重任，所以primary key十分的常用，而又由于这个功能比较固定，所以这个主键也一般绑定在id上，所以我们创建表的语法开始会固定的绑定id字段为主键：

create table a_table(
id int primary key,
    ...
)；

自增 auto_increment

该约束条件就是配合主键一起使用的，因为主键是用于组织我们的表格的，所以交给数据库帮我们自动生成这个值就行了，不必我们自己再费心输入id的值。而MySQL提出的解决方案就是自增。

而创建表的固定方式就又变成：

create table a_table(
id int primary key  auto_increment,
    ...
)；

而在实际操作这个自增属性时，需要注意它的一些特性，首先我们先创建一个带自增主键的表。

create table t6(
id int primary key auto_increment,
name varchar(16)
);
desc t6;

image

  • 自动生成id值

    insert into t6(name) values('leethon'),('maria'),('jerry'),('tom');  # 插入四行数据
select * from t6;

    image

  • 删除记录时，自增的数字并不会回退

    delete from t6 where id > 2;  # 将id字段大于2的记录全部删除
select * from t6;
insert into t6(name) values('lion');
select * from t6;

    image

  • 当自己手动插入了一个更大的id主键值，自增会从这个更大的值继续自增

    insert into t6 values(100, 'lucy');
insert into t6(name) values('jack');
select * from t6;

    image

约束条件之外键

外键约束条件的关键字是：foreign key，还可能配合on update cascade on delete cascade使用。

外键是关系型数据库建立表与表之间的方式，所以在说外键的用法之前还需要简要的介绍一下表间关系。从各种关系入手来介绍外键的用法。

表的解耦合

我们先通过Excel模拟我们数据库存储数据。假设有这么一张员工表：

image

如表中所演示的，左侧是每个员工的不同信息，但是右侧都是部门的详细信息，如果将这部分信息也塞到员工表里，显然是冗余的。而且当一个员工要更换部门时，要操作的数据更多，这种组织的形式是不便拓展的。

所以我们就要有一个解耦合的思想，将个人的信息存到一个表里，部门的信息存到另外一个表里：

image

这样处理就得到了解耦合的两个表，我们不必重复冗余的存储数据，两个表的信息也能随意去修改。

不过产生了一个新的问题就是两个表之间的关系断了，所以数据库提供了外键来联系表之间的关系。

表间关系之一对多

刚才所说的例子，其实就是一对多的关系，一个员工只能选择一个部门，而一个部门可以有多个员工。部门对员工的联系呈辐射状。

image

这时我们就可以让员工表再增加一个字段来记录与部门表主键的对应关系。直接加这个字段只有逻辑的对应，但是数据库提供的外键就让我们对这个部门表有实际的联系。

添加外键的字段就是关联字段，其所在的表是关联表，被关联字段所在表是被关联表。

# 我们要先建立被关联的表 -- 部门表
create table dep(
id int primary key auto_increment,
name varchar(16),
info varchar(50),
leader char(16)
);  # 被关联表创建方式很正常

# 再建立关联表 -- 员工表
create table emp(
id int primary key auto_increment,
name varchar(16),
age tinyint,
gender enum('male','female'),
dep_id int,  # 先建立部门id字段
foreign key(dep_id) references dep(id)  # 再将dep_id字段与建立过的dep表的id字段关联起来
);

image

插入一些数据：

# 先建立一些部门数据（因为不先建立被关联的内容，关联表就没有内容可以关联）
insert into dep(name, info, leader)
values('IT部门','技术岗','boss1'),
('安防部',	'负责安防',	'boss2'),
('财务部',	'发工资的',	'boss3');

# 再建立一些员工数据
insert into emp(name,age,gender,dep_id) 
values('leethon',18,'male',1),
('Maria',	18	,'female',1),
('anthony',	20,	'male',2),
('patton',	40,	'male',2),
('gary',	26,'male',3),
('peter',	37,'male',3);
# 查看结果
select * from dep;
select * from emp;

image

可以看到这两张表，但是看起来又没有这么直观，当时我们把它拆开是为了节省空间和方便拓展，而当用的时候我们应该也能把它们拼起来。这个会在后续进行补充。

表的同步修改

在被关联的字段修改时，关联表的外键字段就失效了，所以MySQL会不让被关联的字段被修改。

但是我们还可以加两个约束条件来，让被关联字段修改和删除时，关联的字段同步的修改和删除。

create table emp1(
id int primary key auto_increment,
name varchar(16),
age tinyint,
gender enum('male','female'),
dep_id int,  # 先建立部门id字段
foreign key(dep_id) references dep(id)  
on update cascade  # 同步更新
on delete cascade  # 同步删除
);

表间关系之多对多

多对多的关系又可以称之为相互关联，如作者和书籍，一个作者可以写多本书，一本书籍可以有多个作者。所以作者表和书籍表应该互相可以关联外键，但是从一对多的关系中得知，被关联的表应该提前建立，两个都为被关联表，所以两者都应该先建立，然后再用一个第三方的表关联两者。

create table author(
id int primary key auto_increment,
    name varchar(16)
);
create table book(
id int primary key auto_increment,
    name varchar(16)
);

create table author2book(  # 第三方关联表应该见名知义
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 
);
# 注意：代码比较长时半天找不出的语法错误可能是因为符号的中英文问题。

多对多关系中，我们提出的解决方案是两个表都是被关联表，而第三方表格记录两个表之间的对应关系，第三方表中有两个字段分别外键关联我们多对多关系的两个表。

image

表间关系之一对一

一对一关系即两个表之间的关系是一一对应的，这种看似没什么用，因为有人会认为，既然一一对应，为什么不直接做成一张表存储呢。

我们可以提出这么一个场景，当一个人的信息有很多很多，相当于表需要有很多很多字段来对应这些信息，而一个人的信息是有常用和不常用之分的，所以可以用一个表存常用信息，另一个表存不常用的信息，然后将两者关联起来。这样在取常用信息时很方便，也不会加载多余的信息，而且取用不常用信息时也能取到。

这种应用场景叫做冷热数据，将冷热数据解耦分开存放就是我们的存储策略。

不过一对一的确是比较少用的表关系。

# 先建立不常用信息表
create table userdetail(
	id int primary key auto_increment,
  	phone bigint
);

# 再建立常用信息表
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
);

标签：约束条件,insert,int,into,values,及表间,key,MySQL,id
From： https://www.cnblogs.com/Leethon-lizhilog/p/16923268.html

相关文章

  • mysql数据库(字段约束条件)
    什么是字段约束字段约束就是将字段的内容定一个规则，我们要按照规则办事约束描述关键字非空约束限制该字段的数据不能为nullnotnull唯一约束保证该字......
  • python入门基础之主键、外键、约束条件
    python入门基础之主键、外键、约束条件目录python入门基础之主键、外键、约束条件字段约束条件主键自增外键前戏关系的判断外键字段的建立多对多关系一对一关系字段约束......
  • 字段约束条件
    字段约束条件无符号、零填充unsigned#无符号idintunsignedzerofill#零填充idint(3)zerofill#存入数字不足三位时以0填充至三位非空createt......
  • Mysql:字段约束条件:无符号、零填充、非空、默认值、唯一值、主键、自增、外键前戏、
    目录无符号、零填充非空默认值唯一值主键自增外键前戏关系的判断一对多关系外键字段的建立多对多关系一对一关系无符号、零填充'''关键字unsigned无需正负号'''crea......
  • mysql打开表报错err 1030 - got error 1877 'Unknown error 1877' from storage engin
    参考解决链接https://www.cnblogs.com/caijh/p/7244915.html查看日志文件位置showvariableslike'%error%';根据错误日志分析，删除log_file文件cd/server/my......
  • MySQL之字段约束条件
    无符号、零填充非空默认值唯一值主键自增外键今日内容详细无符号、零填充unsigned idintunsignedzerofill idint(5)zerofill非空(notnull)createta......
  • 字段约束条件
    目录字段约束条件1.无符号、零填充2.非空3.默认值4.唯一值5.主键6.自增7.外键1).外键前戏2).外键字段的创建表关系之一对多表关系之多对多表关系之一对一字段约束条件1.......
  • MySQL数据库——字符编码、配置文件、存储引擎、数据类型、约束条件
    MySQL数据库——字符编码、配置文件、存储引擎、数据类型、约束条件一、字符编码与配置文件1、查看MySQL相关信息\s查看相关信息 当前用户、版本、编码、端口号 MySQ......
  • MySQL字段约束条件
    今日内容概要主题:字段约束条件无符号、零填充非空默认值唯一值主键自增外键今日内容详细无符号、零填充unsigned#取消正负号 idintunsignedzerof......
  • MySQL安装与配置
    一、下载，官网http://www.mysql.com1.  往下划找到： 2. 3.     4.   二、安装1.   2.   3.   4.在上一步的4处点击......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99