『 MySQL数据库 』数据库之表的约束

前言 💻

数据库是一个用来管理、组织、存储数据的一个工具，一个数据库可以存放大量的数据,而为了存放这些数据并将这些数据进行组织,那么为了便于组织和管理,所存储的数据必然要使用分区以特定的格式进行存储;

而对于各个用户来说用户不一定会将所存储的数据以特定的形式表标准准的进行存放,为了能使用户以更加正确的方式进行存放,数据库中有了一个为表的约束的概念;

表的约束实际上约束的是用户,使用户必须以规定的格式或者类型才能将数据存放进数据库;

空属性约束(非空约束) 🔖

NULL与NOT NULL;

• NULL

  默认可以为空(非必填);

  语法(例如):

  CREATE TABLE IF NOT EXISTS Students(
      ->	name varchar(20) null comment'姓名',
      ->	gender enum('男','女') null comment'性别',
      ->	numbers char(11) null comment'电话号码'
      ->	);
  

  其中关键字为null且若是有comment时这个注释将加在最后;

  

• NOT NULL

  默认不能为空(必填);

  语法(例如):

  CREATE TABLE IF NOT EXISTS Students(
      ->	name varchar(20) not null comment'姓名',
      ->	gender enum('男','女') not null comment'性别',
      ->	numbers char(11) not null comment'电话号码'
      ->	);
  

  其中关键字为not null且若是有comment时这个注释将加在最后;

  

  当约束为not null时插入数据不插入当前字段时将会报错;

  

default约束(默认值约束,缺省) 🔖

• default

  该约束为默认值,意思也为缺省值;

  语法(例如):

  CREATE TABLE IF NOT EXISTS Students(
      ->	name varchar(20) default'xxx' comment'姓名',
      ->	gender enum('男','女') default'男' comment'性别',
      ->	numbers char(11) default'123456' comment'电话号码'
      ->	);
  

在MySQL的语法中支持default约束与not null约束同时定义(不冲突),

若是这两个约束同时出现,在插入数据时若是没有指定需要插入的数据则使用default约束所指定的缺省值,

若是指定插入数据则使用插入的数据,当然这个数据必须符合该类型,同时该数据不能为NULL,这就是default约束与not null约束共同定义的条件;

CREATE TABLE IF NOT EXISTS Students(
    ->	name varchar(20) not null default'xxx' comment'姓名',
    ->	gender enum('男','女') not null default'男' comment'性别',
    ->	numbers char(11) not null default'123456' comment'电话号码'
    ->	);

insert into Students values(NULL,NULL,NULL) #error 可以缺省不传,但是传入的参数要符合类型且不能为NULL

列描述comment 🔖

列描述通常是在创建一个列(字段)时对该字段的描述(注释);

CREATE TABLE IF NOT EXISTS Students(
    ->	name varchar(20) comment'姓名',
    ->	gender enum('男','女') comment'性别',
    ->	numbers char(11) comment'电话号码'
    ->	);

其中comment即为列描述;

使用desc查看表结构的时候不能看见comment列描述,使用show create table可以;

数字类型长度zerofill 🔖

假设存在一个表,且表内数据类型为int,在使用desc查看表结构时将会看到实际上int类型后跟了一个(11);

当给这个字段加上zerofill属性后将会发现其实就是0填充;

主键primary key 🔖

主键一般是用来约束唯一的字段,它约束了用户在插入该字段的数据不能重复,不能为空,且主键只能有一个;

一般主键字段用来当作一个数据的唯一标识符;

主键一般为整数类型;

📍 追加主键 📍

当一个字段一开始没有设定主键也可以在创建之后设置主键；

但是使用这种方式设定主键时应该注意在此之前应确保该表中的要设为主键的字段不存在重复数据；

alter table table_name modify 字段属性 primary key comment'描述';

如果已经需要成为主键的字段内存在重复的数据时,再将该字段设置为主键时将会报错;

📍 删除主键 📍

alter table table_name drop primary key;

使用该命令可以删除primary key主键,由于表中只能拥有一个主键,所以在删除主键时指定主键即可;

📍 复合主键 📍

在一个表中只能拥有一个主键但不代表一个主键内只能拥有一个字段;

mysql> create table if not exists t4(
    -> id1 int not null,
    -> id2 int(5) unsigned zerofill not null,
    -> name varchar(20) not null default'xxx',
    -> primary key(id1,id2)
    -> ); # 将id1与id2设为复合主键


mysql> desc t4;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id1   | int(11)                  | NO   | PRI | NULL    |       |
| id2   | int(5) unsigned zerofill | NO   | PRI | NULL    |       |
| name  | varchar(20)              | NO   |     | xxx     |       |
+-------+--------------------------+------+-----+---------+-------+

从上面可以看出成功的创建出了一个复合主键;

mysql> desc t4;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id1   | int(11)                  | NO   | PRI | NULL    |       |
| id2   | int(5) unsigned zerofill | NO   | PRI | NULL    |       |
| name  | varchar(20)              | NO   |     | xxx     |       |
+-------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ^C
mysql> insert into t4 values(1,1,'xxx');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values(1,2,'xxx');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values(2,1,'xxx');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values(2,2,'xxx');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;
+-----+-------+------+
| id1 | id2   | name |
+-----+-------+------+
|   1 | 00001 | xxx  |
|   1 | 00002 | xxx  |
|   2 | 00001 | xxx  |
|   2 | 00002 | xxx  |
+-----+-------+------+
4 rows in set (0.00 sec)

mysql> insert into t4 values(2,1,'xxx'); #报错
ERROR 1062 (23000): Duplicate entry '2-00001' for key 'PRIMARY'

自增长 🔖

当一个字段需要进行自增长时(例如id主键),则需要一个约束来帮助其进行自增;

在MySQL中auto_increment可以使一个字段内的数据在一次插入时进行自增长+1(不插入该值的情况下);

但是在使用自增长的时候也有一定的条件:

• 任何一个字段要做自增长,前提是本身是一个索引(key栏有值)
• 自增长的字段必须是整数
• 一张表中最多只能有一个自增长

mysql> desc t5;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | YES  |     |         |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into t5 (name)value('Zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 (name)value('Zhangshan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 values(100,'Zhangshan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 (name)values('Zhangshang');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+-----+------------+
| id  | name       |
+-----+------------+
|   1 | Zhangsan   |
|   2 | Zhangshan  |
| 100 | Zhangshan  |
| 101 | Zhangshang |
+-----+------------+
4 rows in set (0.00 sec)

📍 在InnoDB存储引擎中自增值的跳跃 📍

如果表的存储引擎是InnoDB的话当数据插入失败则会出现auto_increment自增值跳跃的情况;

唯一键unique 🔖

一张表中有一般有很多的字段所存储的数据都需要具有唯一性,如(身份证,银行卡号,邮箱)等等;

在MySQL中除了主键以外还存在一个约束为唯一键;

当然唯一键与主键也有很多的不同之处,最主要的一点就是:

• 对于主键来说,一般主键一般是来维护数据的唯一性,在绝大多数情况下主键一般是作每个数据的标识符,使得用户在对该数据进行操作的时候能够通过这个标识符达到精确搜索到该数据;
• 对于唯一键来说,唯一键更多的是在业务上来维护数据的唯一性,如(一个人的邮箱,身份证,银行卡等等);

除了这两点以外,唯一键在使用的性质等等也与主键不同;

• 主键不能为空,唯一键可以为空;
• 一张表中至多只能有1个主键,而一张表中可以有n个唯一键;

mysql> desc t6;
+-------+--------------------------+------+-----+---------+----------------+
| Field | Type                     | Null | Key | Default | Extra          |
+-------+--------------------------+------+-----+---------+----------------+
| id    | int(3) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)              | NO   |     | NULL    |                |
| tel   | char(11)                 | YES  | UNI | NULL    |                |
| email | char(20)                 | YES  | UNI | NULL    |                |
+-------+--------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into t6 (name,tel,email)values('Lihua','123456','12345@xx.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t6 (name,tel,email)values('Zhangsan','123456','12345@xx.com');
ERROR 1062 (23000): Duplicate entry '123456' for key 'tel'
mysql> insert into t6 (name,tel,email)values('Zhangsan','123455','12345@xx.com');
ERROR 1062 (23000): Duplicate entry '12345@xx.com' for key 'email'
mysql> insert into t6 (name,tel,email)values('Zhangsan','123455','12344@xx.com');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+-----+----------+--------+--------------+
| id  | name     | tel    | email        |
+-----+----------+--------+--------------+
| 001 | Lihua    | 123456 | 12345@xx.com |
| 004 | Zhangsan | 123455 | 12344@xx.com | #这里的自增值越过是因为默认存储引擎为InnoDB
+-----+----------+--------+--------------+

外键 🔖

外键是用来维护两个表之间的关系,有些有关联的表可以分为主表和从表;

一般外键约束是在从表上定义,同时当定义外键后,要求外键列数据必须在主表的主键列存在主表的主键列或为null;

forgen key (字段名) reference 主表(列)

mysql> create table if not exists t7_class(
    -> class_id int(2) unsigned zerofill primary key commentt'专业班级',
    -> class_name varchar(20) not null comment'班级名字'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t7_class
    -> ;
+------------+--------------------------+------+-----+---------+-------+
| Field      | Type                     | Null | Key | Default | Extra |
+------------+--------------------------+------+-----+---------+-------+
| class_id   | int(2) unsigned zerofill | NO   | PRI | NULL    |       |
| class_name | varchar(20)              | NO   |     | NULL    |       |
+------------+--------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into t7_class values(1,'计科一班');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t7_class values(2,'计科二班');
                                   
Query OK, 1 row affected (0.00 sec)

mysql> select * from t7_class;
+----------+--------------+
| class_id | class_name   |
+----------+--------------+
|       01 | 计科一班     |
|       02 | 计科二班     |
+----------+--------------+

mysql> create table if not exists t7_stu(
    -> id int(3) unsigned zerofill primary key auto_increment comment'设置主键',
    -> name varchar(20) not null comment'姓名',
    -> class int(2) unsigned zerofill comment'外键',
    -> foreign key (class) references t7_class(class_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> system clear;

此时主表与从表的外键关系已经创建完毕;

mysql> show create table t7_class\G #查看该表详细信息
*************************** 1. row ***************************
       Table: t7_class
Create Table: CREATE TABLE `t7_class` (
  `class_id` int(2) unsigned zerofill NOT NULL COMMENT '专业班级',
  `class_name` varchar(20) NOT NULL COMMENT '班级名字',
  PRIMARY KEY (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table t7_stu\G #查看该表详细信息
*************************** 1. row ***************************
       Table: t7_stu
Create Table: CREATE TABLE `t7_stu` (
  `id` int(3) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '设置主键',
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `class` int(2) unsigned zerofill DEFAULT NULL COMMENT '外键',
  PRIMARY KEY (`id`),
  KEY `class` (`class`),
  CONSTRAINT `t7_stu_ibfk_1` FOREIGN KEY (`class`) REFERENCES `t7_class` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

插入数据时从表中建立外键约束的字段插入数据时只能是主表内对应的数据或者为空,否则将报错;

mysql> desc t7_class;
+------------+--------------------------+------+-----+---------+-------+
| Field      | Type                     | Null | Key | Default | Extra |
+------------+--------------------------+------+-----+---------+-------+
| class_id   | int(2) unsigned zerofill | NO   | PRI | NULL    |       |
| class_name | varchar(20)              | NO   |     | NULL    |       |
+------------+--------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc t7_stu;
+-------+--------------------------+------+-----+---------+----------------+
| Field | Type                     | Null | Key | Default | Extra          |
+-------+--------------------------+------+-----+---------+----------------+
| id    | int(3) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)              | NO   |     | NULL    |                |
| class | int(2) unsigned zerofill | YES  | MUL | NULL    |                |
+-------+--------------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into t7_stu (name,class)value('Lihua',01);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t7_stu (name,class)value('Lihua',02);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t7_stu (name,class)value('Liming',03);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t7_stu`, CONSTRAINT `t7_stu_ibfk_1` FOREIGN KEY (`class`) REFERENCES `t7_class` (`class_id`))
mysql> select * from t7_stu;
+-----+-------+-------+
| id  | name  | class |
+-----+-------+-------+
| 001 | Lihua |    01 |
| 002 | Lihua |    02 |
+-----+-------+-------+
2 rows in set (0.00 sec)

mysql>