MySQL字段约束条件

目录

-字段约束条件-

约束条件 与 数据长度一样都是可选参数

作用：用于保证数据的完整性和一致性

约束条件属于表结构，可以通过desc 表名;来进行查看

类型 作用
PRIMARY KEY (PK) 标识该字段为该表的主键
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充

1.无符号UNSIGNED、0填充ZEROFILL

unsigned 表示无符号
    id int unsigned
  
zerofill 仅用于显示，当不满足5位时，按照左边补0，例如：00002；满足时，正常显示。
    id int(5) zerofill

2.非空 not null

not null代表该字段不能为空，必须为该字段插入值，如果不插入值则会使用默认值，如果默认值为null则报错

（1）所有的字段类型，在不加约束条件的情况下默认都可以为空

 
# 建立数据库 db3
mysql> create database db3;
Query OK, 1 row affected (0.02 sec)
 
# 切换库db3
mysql> use db3
Database changed
# 建表t1
mysql> create table t1(id int,name varchar(16));
Query OK, 0 rows affected (0.06 sec)
# 查看表结构，默认值都为null可以为空
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
 
# 插入语句 insert into 表名(字段名) values(记录)；
mysql> insert into t1(id) values(1);
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into t1(name) values('jason');
Query OK, 1 row affected (0.02 sec)
 
mysql>   insert into t1(name,id) values('kevin',2);
Query OK, 1 row affected (0.03 sec)
 
 
mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | NULL  |  --  可以为null
| NULL | jason |
|    2 | kevin |
+------+-------+
3 rows in set (0.00 sec)

（2）not null非空

# sql语句创建表
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);

例：--只要写了东西就默认为非空

# 建立表t2
mysql> create table t2(
    -> id int,
    -> name varchar(16) not null
    -> );
Query OK, 0 rows affected (0.06 sec)
 
# 插入值
mysql> insert into t2(id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> insert into t2(name) values('jason');
Query OK, 1 row affected (0.03 sec)
# 只要有插值的动作，就算非空
mysql> insert into t2 values(1,'');
Query OK, 1 row affected (0.02 sec)
 
mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 插入null 由于约束条件为not null则报错
mysql> insert into t2 values(2,null);
ERROR 1048 (23000): Column 'name' cannot be null

3.默认值Default

默认值用于在创建表时为字段设置默认值，当插入时可以省略该字段的数据插入而去使用设定的默认值，如不设定默认值则为null

#  建表
create table t3(
	id int default 333,  --  不填数据的情况下默认为 333
  name varchar(16) default '铎铎'  --  不填数据的情况下默认为'铎铎'
);
insert into t3 values(2,null)
mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | 333     |       |
| name  | varchar(16) | YES  |     | 铎铎    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

4.唯一值Unique

（1）单列唯一

单列唯一是指该字段下的值不能重复

# 建立表
create table t4(
	id int,
  name varchar(32) unique  -- name的值唯一不能重复
);
insert into t4 values(1,'duoduo'),(2,'lulu');
# 插入 1,'duoduo'
mysql> insert into t4 values(1,'duoduo');
Query OK, 1 row affected (0.03 sec)
# 插入 1,'duoduo' 2,'lulu'
mysql> insert into t4 values(1,'duoduo'),(2,'lulu');
ERROR 1062 (23000): Duplicate entry 'duoduo' for key 'name'
# 报错duoduo已经存在了，不能重复
 
# 插入  1,'lulu'
mysql> insert into t4 values(1,'lulu');
Query OK, 1 row affected (0.02 sec)
# 插入  ,'kdka'
mysql> insert into t4 values(1,'kdka');
Query OK, 1 row affected (0.02 sec)
 
mysql> select * from t4;
+------+--------+
| id   | name   |
+------+--------+
|    1 | duoduo |
|    1 | lulu   |
|    1 | kdka   |
+------+--------+
3 rows in set (0.00 sec)
# id字段可以不唯一可以重复，name字段有约束条件unique所以不能重复

（2）联合唯一

unique(field1,field2,field3...)

联合唯一：field字段对应的值之间的组合不能重复

# 建表
create table t5(
  id int,
  ip varchar(32),  -- 字符括号内填写的数字最好为16的倍数，并不要超过255
  port varchar(32),
  unique(ip,port)  -- 指定ip和port的组合不能重复
);
# 插入值 两两组合，只要不重复则可以插入成功
insert into t5 values(1,'127.0.0.1','8080'),(2,'127.0.0.1','8081'),(3,'127.0.0.2','8080');
 
# 将id=1 中的两组颠倒过来，也可以插入成功，说明顺序对应的字段名也是组合的一部分
insert into t5 values(4,'8080','127.0.0.1');
 
# 查看t5
mysql> select * from t5;
+------+-----------+-----------+
| id   | ip        | port      |
+------+-----------+-----------+
|    1 | 127.0.0.1 | 8080      |
|    2 | 127.0.0.1 | 8081      |
|    3 | 127.0.0.2 | 8080      |
|    4 | 8080      | 127.0.0.1 |
+------+-----------+-----------+
4 rows in set (0.00 sec)

5.主键Primary key

（1）主键的特点

1）主键单从约束条件层面上而言主键相当于not null + unque ==>> 非空且唯一

2）InnoDB存储引擎规定了所有的表都必须有且只有一个主键，InnoDB会根据主键锁约束字段的值来建立索引，建立表的整体结构，加速查询

# 建表
create table t6(
	id int primary key,  -- id为主键，此时id不可为空并且值唯一
  name varchar(32)
);
 
insert into t6(name) values('duoduo');
insert into t6 values(1,'亚索');
insert into t6 values(2,'亚索');
 
# 不输入id报错 由于id约束条件为主键，则id不可以为空
mysql> insert into t6(name) values('duoduo');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
 
# 输入 1,'亚索'
mysql> insert into t6 values(1,'亚索');
Query OK, 1 row affected (0.02 sec)
# 输入 2,'亚索' name字段可以重复
mysql> insert into t6 values(2,'亚索');
Query OK, 1 row affected (0.02 sec)
# 输入 1,'duoduo' id字段为主键不可以重复
mysql>  insert into t6 values(1,'duoduo');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
 
mysql> select * from t6;
+----+--------+
| id | name   |
+----+--------+
|  1 | 亚索   |
|  2 | 亚索   |
+----+--------+
2 rows in set (0.00 sec)

（2）主键的作用：

1）主键是组织数据的重要条件

2）主键可以加快数据的查找速度（索引）

（3）InnoDB引擎中主键的特性

1）当表中没有主键也没有其他非空且唯一的字段的情况下，也就是表中没有显式的设置主键，InnoDB会采用一个隐藏的字段作为表的主键，否则对于该表的数据查询只能一行行查找，速度很慢

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

# 建表
create table t7(
	id int,
  age int not null unique,
  level int not null unique,
  phonenum bigint not null unique  
);
mysql> desc t7;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| id       | int(11)    | YES  |     | NULL    |       |
| age      | int(11)    | NO   | PRI | NULL    |       |
| level    | int(11)    | NO   | UNI | NULL    |       |
| phonenum | bigint(20) | NO   | UNI | NULL    |       |
+----------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

则会发现从上往往下，3个非空且唯一的字段中，第一个age自动升级为主键

​ 在创建表的时候应该有一个字段应该来标识字段的唯一性，通常情况下该字段就是编号字段，我们可以将该编号字段设置为主键

该字段可以叫 id uid zid gid...

6.自增auto_increment

自增特点：

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

2）自增字段的记录不会因为数据的删除而回退，永远自增往前

如果自己设置了自增字段的记录为更大的数，则之后就在该数字的基础上继续自增

# 建表
create table t8(
  id int primary key auto_increment,
  name varchar(32)
);
 
# 插入值
mysql> insert into t8 values(1,'aa');
...
# 设定id=55
mysql> insert into t8 values(55,'dd');
 
# 再插入值
mysql> insert into t8(name) values('ff');
 
mysql> select * from t8;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
| 55 | dd   |
| 56 | ff   |
+----+------+
 
# 则发现此时 id自增列从55开始自增
 
# 设定id=2000
mysql> insert into t8 values(2000,'mm');
mysql> insert into t8(name) values('ff');
 
 
mysql> select * from t8;
+------+------+
| id   | name |
+------+------+
|    1 | aa   |
|    2 | bb   |
|    3 | cc   |
|    4 | dd   |
|   55 | dd   |
|   56 | ff   |
| 2000 | mm   |
| 2001 | ff   |
| 2002 | ff   |
+------+------+
# 则发现此时 id自增列从2000开始自增
 
 
# 删除前面的值之后再添加新的值
mysql> delete from t8 where id<2003;
Query OK, 2 rows affected (0.02 sec)
 
mysql> insert into t8(name) values('ff');
Query OK, 1 row affected (0.02 sec)
 
mysql> select * from t8;
+------+------+
| id   | name |
+------+------+
| 2003 | ff   |
+------+------+
#id自增列还是从2003开始自增

7.外键

1.使用外键的目的

比如我们需要一张员工表，写入数据库中

image-20221124192117322

这张表在表达上有以下缺点

​ （1）语义不明确，描述不清晰，员工表中包含了部门的信息

​ （2）数据过于冗余，部门的信息重复浪费了存储空间

​ （3）数据的扩展性较差，修改部门的信息相关员工的信息都要修改

那么我们可以将员工表和部门表独立开，这样表达简洁明了，扩展起来也方便，但是部门和与员工之间就没有联系了

这时候建立彼此之间的联系，就需要外键Foreign Key来建立表与表之间数据的联系

image-20221124191810506

综上，外键字段：用于标识数据与数据之间关系的

2.表与表之间的关系

（1）外键的关系判断：

​ 表与表的关系或者说是表与表之间数据的关系

（2）关系总共有四种：

​ 一对多、多对多、 一对一、 没有关系

（3）关系的判断法则可以采用 换位思考：

​ 也就是站在表A的视角，思考是否A的多个数据可以属于表B中的1个数据

​ 比如 ：

​ 员工角度：多个员工 属于 一个部门 √

​ 部门角度：多个部门 拥有 一个员工×

​ ==>得出 彼此之间的关系是 一对多

针对一对多关系，外键应该建在多的一方

3.外键字段的建立

可以先定义出表的普通字段，之后在考虑外键字段的添加

（1）一对多

一对多应该满足

1.创建表的时候一定要先创建被关联表

2.录入表数据的时候一定先录入被关联表（数据库层面的约束非常强烈）

3.修改数据的时候，外键字段无法修改和删除，也就是需要先解除关系

# 建立员工表
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(32)
)；

例子.修改数据的时候，外键字段无法修改和删除，也就是需要先解除关系

针对3有简化措施>>可以采用 级联更新 和 级联删除 的约束条件

及联更新on update cascade ：只会更改ID

及联删除on delete cascade：被关联的表ID删除后，关联表的相关记录也会被删除

# 创建外键时候添加及联更新和及联删除的约束条件
create table emp(
	id int primary key auto_increment,
  name varchar(32),
	age int
  dep_id int,
  foreign key(dep_id) references dep(id) 
  on update cascade
  on delete cascade
);

注意：外键的特性是强耦合，并非解耦合

所以实际项目中当表较多的情况，我们可能不会使用外键，而是使用代码建立表与表之间的逻辑关系

（2）多对多

以电影和电影导演来说

​ 电影角度：多个电影 属于 一个导演 √`

​ 导演角度：多个导演 执导 一部电影 √

​ ==>得出 彼此之间的关系是 多对多

image-20221124200723549 
# 创建电影表
create table film(
 id int primary key auto_increment,
 name varchar(64)
);
# 创建导演表
create table director(
 id int primary key auto_increment,
  name varchar(64)
);
# 将外键创立成表FilmTODirect
create table filmtodirect(
  id int primary key auto_increment,
  file_id int,
  foreign key(file_id) references film(id)
  on update cascade
  on delete cascade,
    director_id int,
  foreign key(director_id) references director(id)
  on update cascade
  on delete cascade
);
 
# 插入数据
insert into director(id,name) values
(1,'徐克'),
(2,'陈凯歌'),
(3,'林超贤'),
(4,'周星驰'),
(5,'吴宇森');
 
 
insert into film(id,name) values
(1,'长津湖1'),
(2,'长津湖2'),
(3,'西游伏妖篇'),
(4,'义胆群英'),
(5,'英雄本色3');
 
 
insert into filmtodirect(id,file_id,director_id) values
(1,1,1),
(2,1,2),
(3,1,3),
(4,2,1),
(5,3,1),
(6,3,4),
(7,4,1),
(8,4,4),
(9,5,1),
(10,5,5);
mysql> select * from film;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | 长津湖1         |
|  2 | 长津湖2         |
|  3 | 西游伏妖篇      |
|  4 | 义胆群英        |
|  5 | 英雄本色3       |
+----+-----------------+
5 rows in set (0.00 sec)
 
mysql> select * from director;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 徐克      |
|  2 | 陈凯歌    |
|  3 | 林超贤    |
|  4 | 周星驰    |
|  5 | 吴宇森    |
+----+-----------+
5 rows in set (0.00 sec)
 
mysql> select * from filmtodirect;
+----+---------+-------------+
| id | file_id | director_id |
+----+---------+-------------+
|  1 |       1 |           1 |
|  2 |       1 |           2 |
|  3 |       1 |           3 |
|  4 |       2 |           1 |
|  5 |       3 |           1 |
|  6 |       3 |           4 |
|  7 |       4 |           1 |
|  8 |       4 |           4 |
|  9 |       5 |           1 |
| 10 |       5 |           5 |
+----+---------+-------------+
10 rows in set (0.00 sec)

（3）一对一

以用户和用户详情表来说

​ 用户角度：多个用户 属于 一个详情 x

​ 用户详情角度：多个详情 属于 一个用户 x

​ ==>得出 不可以 彼此之间的关系是 没有关系 或者是 一对一 ，明显是一对一

这时候外键在哪个表添加都没有关系，而最好添加在存储热数据的表中，便于查找

# 创建用户表表
create table customer(
    id int primary key auto_increment,
    name varchar(20)
);
 
# 创建用户详情表
create table detail(
    id int primary key auto_increment,
    gender varchar(10),
    phone varchar(11),
   c_id int,
    foreign key(c_id) references customer(id) 
  on delete cascade 
  on update cascade
);
mysql> desc customer;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
 
mysql> desc detail;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| gender | varchar(10) | YES  |     | NULL    |                |
| phone  | varchar(11) | YES  |     | NULL    |                |
| c_id   | int(11)     | YES  | MUL | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

image-20221124101332166

posted @   Duosg  阅读(163)  评论(0)  编辑  收藏  举报
相关博文：
· 11.27MySQL周末总结
· MySQL创建数据表语法、字段类型
· mysql数据库(字段约束条件)
· 字段之约束条件
· MySQL字段约束条件、自增特性、约束条件之外键
阅读排行：
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
点击右上角即可分享
微信分享提示