MySQL表的约束

目录

前言：

????????空属性：

????????默认值：

????????列描述 ：

????????zerofill：

????????主键：

????????自增长 ：

????????唯一键：

????????外键：

---

前言：

真正约束字段的是数据类型，但是数据类型约束很单一，有时候需要一些额外的约束，更好的保证数据的合法性，表的约束很多，这里重点介绍以下几个

• null/not null
• default
• comment
• zerofill
• primary key
• auto_increment
• unique key

---

?

空属性：

• 值：null和not null
• 数据库默认基本字段都为空，但是实际开发要尽可能保证字段不为空，因为空数据没法参加运算

mysql> select null;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> select 1+null;
+--------+
| 1+null |
+--------+
| ?NULL |
+--------+
1 row in set (0.00 sec)

例如：创建一个班级表，包含班级名和班级所在教室

按正常业务逻辑来看：

• 如果班级没有名字，你就不知道你在哪个班级
• 如果教室名字为空，你就不知道在哪里上课
• 所以在设计数据库表的时候，一定要在表中限制，这就是约束?

mysql> create table myclass(
?-> class_name varchar(20) not null,
?-> class_room varchar(10) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field ??　　| Type ???　　|Null 　　| Key 　| Default | Extra|
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO ?    | ??　　| NULL ?| ??　　　|
| class_room | varchar(10) | NO ?    | ??　　| NULL ?| ??　　　|
+------------+-------------+------+-----+---------+-------+


//插入数据时,没有给教室数据插入失败：
mysql> insert into myclass(class_name) values('class1');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

---

?

默认值：

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。?

mysql> create table tt10 (
?-> name varchar(20) not null,
?-> age tinyint unsigned default 0,
?-> sex char(2) default '男'
?-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc tt10;
+-------+---------------------+------+-----+---------+-------+
| Field | Type ???????　　　　| Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name | varchar(20) ????　　| NO ?   | ?　?| NULL ?| ???　　|
| age ?| tinyint(3) unsigned| YES    | ??　| 0 ???　| ???　|
| sex ?| char(2) ??????　　　| YES    | ?　?| 男 ??　| ???　　|
+-------+---------------------+------+-----+---------+-------+

?　默认值的作用就是在插入时如果没有给该字段负值，就使用默认值

mysql> insert into tt10(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt10;
+----------+------+------+
| name ??　| age | sex |
+----------+------+------+
| zhangsan | ??0 | 男 ?|
+----------+------+------+
--注意：只有设置了default的列，才可以在插入值的时候，对列进行省略

---

?

列描述 ：

?列描述：comment，没有实际含义，专门用来描述字段，会根据表的创建语句保存，当注释用就好了

desc看不到注释信息，但是show看得到

mysql> create table tt12 (
?-> name varchar(20) not null comment '姓名',
?-> age tinyint unsigned default 0 comment '年龄',
?-> sex char(2) default '男' comment '性别'
?-> );
?
--注意：not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空

--desc看不到comment注释信息
mysql> desc tt12;
+-------+---------------------+------+-----+---------+-------+
| Field | Type ???????       | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name | varchar(20) ????    | NO ?| ??   | NULL ?  | ???   |
| age ?| tinyint(3) unsigned | YES | ??   | 0 ???   | ???   |
| sex ?| char(2) ?????       | YES | ??   | 男 ??   | ???   | 
+-------+---------------------+------+-----+---------+-------+

--show可以看到
mysql> show create table tt12\G
*************************** 1. row ***************************
???Table: tt12
Create Table: CREATE TABLE `tt12` (
`name` varchar(20) NOT NULL COMMENT '姓名',
`age` tinyint(3) unsigned DEFAULT '0' COMMENT '年龄',
`sex` char(2) DEFAULT '男' COMMENT '性别'
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

---

zerofill：

?刚开始学习数据库时候，很多人都会对数字类型后面的长度很迷茫(是数字类型)

通过show查看tt3表的建表语句：

mysql> show create table tt3\G
    ***************** 1. row *****************
??    ?Table: tt3
    Create Table: CREATE TABLE `tt3` (
    `a` int(10) unsigned DEFAULT NULL,
    `b` int(10) unsigned DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=gbk
    1 row in set (0.00 sec)

可以看到int(10)，这个是什么意思呢？整形不应该是4字节码吗？那这个10又是什么意思？其实咩有zerofill属性，那这个括号内的数字是毫无意义的。

mysql> insert into tt3 values(1,2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt3;
+------+------+
| a ?  |   b ?|
+------+------+
| ?1   | ? 2  |
+------+------+

这时候添加zerofill属性后：

mysql> alter table tt3 change a a int(5) unsigned zerofill;
mysql> show create table tt3\G
*************************** 1. row ***************************
???Table: tt3
Create Table: CREATE TABLE `tt3` (
`a` int(5) unsigned zerofill DEFAULT NULL, ?--具有了zerofill
`b` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

这时候再进行查找就得到如下结果：

mysql> select * from tt3;
+-------+------+
| a ??  | b ?  |
+-------+------+
| 00001 | 2    |
+-------+------+

可以看到a的值由原来的1变成00001，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里是5），就会用0来填充，这是显示的结果，实际上MySQL中存储的还是1.

这只是设置了zerofill属性后一种格式化输出而已

---

?

主键：

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表最多只能有一个主键；主键所在的列通常是整数类型

例如：

• 创建表的时候直接在字段上指定主键

mysql> create table tt13 (
-> id int unsigned primary key comment '学号不能为空',
-> name varchar(20) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> desc tt13;
+-------+------------------+------+-----+---------+-------+
| Field | Type ??????    | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id ?| int(10) unsigned | NO ? | PRI | NULL ?  | ???| <= key 中 pri表示该字段是主键
| name | varchar(20) ?  ?| NO ? | ??  | NULL ?  | ???|
+-------+------------------+------+-----+---------+-------+

• 主键约束：主键对应的字段中不能重复，一旦重复，操作失败?

mysql> insert into tt13 values(1, 'aaa');
Query OK, 1 row affected (0.00 sec)


mysql> insert into tt13 values(1, 'aaa');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

• 当表创建好后没有主键的时候，可以再追加主键?

alter table 表名 add primary key(字段列表)

• ?删除主键

alter table 表名 drop primary key;

• 复合主键

在创建表的时候，在所有字段之后，使用primary key（主键字段列表）来创建主键，如果有多核字段作为主键，可以使用复合主键

mysql> create table tt14(
-> id int unsigned,
-> course char(10) comment '课程代码',
-> score tinyint unsigned default 60 comment '成绩',
-> primary key(id, course) -- id和course为复合主键
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc tt14;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type ???????        | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id ?  ?| int(10) unsigned ?  | NO  ?| PRI | 0 ??   ?| ??  ? | <= 这两列合成主键
| course | char(10) ?????      | NO ? | PRI | ?  ??  ?|  ???  | <=--
| score  | tinyint(3) unsigned | YES  |   ??| 60 ??   |  ???  | 
+--------+---------------------+------+-----+---------+-------+

---

?

自增长 ：

auto_increment：当对应的字段不给值，会自动的被系统触发，系统会从当前字段中已有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键

自增长特点：

• ?任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
• 自增长字段必须是整数
• 一张表最多只能有一个自增长

mysql> create table tt21(
?-> id int unsigned primary key auto_increment,
?-> name varchar(10) not null default ''
?-> );

mysql> insert into tt21(name) values('a');
mysql> insert into tt21(name) values('b');

mysql> select * from tt21;
+----+------+
| id | name |
+----+------+
| ?1 | a ?  |
| ?2 | b   ?|
+----+------+

?索引：

?在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。
数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息

?

---

唯一键：

?一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

?关于唯一键和主键的区别：我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。

举个例子：

假如在一个公司表中，有两个信息，一个是身份证号码一个是员工工号，我们可以选择员工工号作为主键，身份证号作为唯一键。

mysql> create table student (
?-> id char(10) unique comment '学号，不能重复，但可以为空',
?-> name varchar(10)
?-> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(id, name) values('01', 'aaa');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(id, name) values('01', 'bbb'); --唯一约束不能重复
ERROR 1062 (23000): Duplicate entry '01' for key 'id'

mysql> insert into student(id, name) values(null, 'bbb'); -- 但可以为空
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+------+
| id ? | name |
+------+------+
| 01  ?| aaa  |
| NULL | bbb  |
+------+------+

---

外键：

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。

foreign key (字段名) references 主表(列)

例子：

?

如果将班级表中的数据都设计在每个学生表的后面?，那么就会出现数据冗余，所以我们只要设计成让Stu->class_id和Class->id形成关联 ---- 外键约束

• ?先创建主表

create table Class (
    id int primary key,
    name varchar(30) not null comment'班级名'
);

• 再创建从表

create table Stu (
    id int primary key,
    name varchar(30) not null comment '学生名',
    class_id int,
    foreign key (class_id) references myclass(id)
);

• 正常插入数据

mysql> insert into Class values(10, 'C++'),(20, 'java');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into Stu values(100, '张三', 10),(101, '李四',20);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

• 插入一个班级号为30的学生，因为没有这个班级，所以插入不成功

mysql> insert into Stu values(102, 'wangwu',30);
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (mytest.Stu, CONSTRAINT Stu_ibfk_1
FOREIGN KEY (class_id) REFERENCES Class (id))

• 插入班级id为null，比如来了个学生还没有分配班级

mysql> insert into stu values(102, 'wangwu', null);

如何理解外键约束：

举个例子：在上面创建表的时候是可以把所有的属性都塞到一块表，但是这样会有一个问题

比如现在计算机最多3个班，但是由于没有能够约束班级id的属性，所以在插入的时候可能会出现插入班级id的数据不在1~3的范围内，一共就三个班，出现了一个5班的同学正在上课。

解决方法就是通过外键来完成，通过外键约束让数据库来判断是否符合业务逻辑（副表没有的属性不让你插）