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.使用外键的目的
比如我们需要一张员工表，写入数据库中
这张表在表达上有以下缺点
（1）语义不明确，描述不清晰，员工表中包含了部门的信息
（2）数据过于冗余，部门的信息重复浪费了存储空间
（3）数据的扩展性较差，修改部门的信息相关员工的信息都要修改
那么我们可以将员工表和部门表独立开，这样表达简洁明了，扩展起来也方便，但是部门和与员工之间就没有联系了
这时候建立彼此之间的联系，就需要外键Foreign Key来建立表与表之间数据的联系
综上，外键字段：用于标识数据与数据之间关系的
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）多对多
以电影和电影导演来说
电影角度：多个电影 属于 一个导演 √`
导演角度：多个导演 执导 一部电影 √
==>得出 彼此之间的关系是 多对多
# 创建电影表
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)
