本章讲解数据库表的列的属性和约束

列的属性

默认值 default

举例：比如我们把first_table的second_column列的默认值指定为'abc'，创建一下这个表

 CREATE TABLE first_table (
        first_column INT,
         second_column VARCHAR(100) DEFAULT 'abc'
     );

然后插入一条数据后看看默认值是不是起了作用：

 INSERT INTO first_table(first_column) VALUES(1);

图片.png

我们插入数据时候并没有插入second_column的数据。所以它直接使用了默认值。

如果我们不设置默认值，其实就相当于指定的默认值为NULL，比如first_table表并没有设置first_column列的默认值，那它的默认值就是NULL，也就是说上边的表定义语句和下边这个是等价的：

CREATE TABLE first_table (
    first_column INT DEFAULT NULL,
    second_column VARCHAR(100) DEFAULT 'abc'
);

这个从SHOW CREATE TABLE语句中也可以看出来：

图片.png

创建表first_table时候 first_column列默认值是NULL

NOT NULL属性

有时候我们需要要求表中的某些列中必须有值，不能存放NULL，那么可以用这样的语法来定义这个列：

列名 列的类型 NOT NULL

比如我们把first_table表的first_column列定义一个NOT NULL属性。

alter table first_table modify first_column  int  NOT NULL;

图片.png

这样的话，我们就不能再往这个字段里插入NULL值了，比如这样：

insert into first_table(first_column,second_column)values(NULL,'aaa');

图片.png

这时候报错，提示first_column不能为空

另外，一旦对某个列定义了NOT NULL属性，那这个列的默认值就不为NULL了。上边first_column并没有指定默认值，意味着我们在使用INSERT插入行时必须显式的指定这个列的值，而不能省略它，比如这样就会报错的：

 INSERT INTO first_table(second_column) VALUES('aaa');

插入值时候不能忽视 NOT NULL的列，必须显式的指定列的值

主码 primary key

直接在列后面声明primary key;

比如我们把学生信息表student_info的学号列声明为主键可以这么写:

CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
);

主键声明单独提取出来

PRIMARY KEY (列名1, 列名2, ...)

然后把这个主键声明放到列定义的后边就行了。

举例: 比如student_info的学号列声明为主键也可以这么写：

CREATE TABLE student_info (
    number INT,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    
    PRIMARY KEY (number)
);

值得注意的是，对于多个列的组合作为主键的情况，必须使用这种单独声明的形式，比如student_score表里的学号,科目的列组合作为主键，可以这么写：

CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject)
);

另外，主键列默认是有NOT NULL属性，也就是必填的

图片.png

如果填入NULL值会报错:

INSERT INTO student_info(number) VALUES(NULL);

图片.png

报错提醒学号列不能为空

所以大家在插入数据的时候至少别忘了给主键列赋值！

外键 foreign key

CONSTRAINT [外键名称] FOREIGN KEY(列1, 列2, ...) REFERENCES 父表名(父列1, 父列2, ...);

如果A表中的某个列或者某些列依赖与B表中的某个列或者某些列，那么就称A表为子表，B表为父表。子表和父表可以使用外键来关联起来。

举例：新建一个学生成绩表里面的number学号列依赖于 student_info表中的number学号列，
所以student_info就是一个父表，student_score就是子表。我们可以在student_score的建表语句中来定义一个外键：

CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject),
    FOREIGN KEY(number) REFERENCES student_info(number)
);

这样，在对student_score表插入数据的时候，MySQL都会为我们检查一下插入的学号是否能在student_info表中找到，如果找不到则会报错。

父表中被子表依赖的列或者列组合必须建立索引，这是因为在查询子表时，MySQL需要通过父表的索引来快速定位到子表中的数据。如果没有建立索引，MySQL就需要扫描整个父表，这样会导致查询效率非常低下。你可以使用以下语句来创建索引：
CREATE INDEX index_name ON parent_table(column_name);

UNIQUE 限制列取值不能重复

表明该列或者列组合的值是不允许重复的。

直接写在定义列属性后面

比如在学生信息表student_info中，我们不允许两条学生基本信息记录中的身份证号是一样的，那我们可以为id_number列添加UNIQUE属性：

CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    
    id_number CHAR(18) UNIQUE,
    
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
);

也可以单独提取出来

UNIQUE [约束名称] (列名1, 列名2, ...)

比如我们为student_info表的id_number（身份证号）列添加UNIQUE属性也可以这么写：

CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    
    UNIQUE  uk_id_number (id_number)
);

图片.png

如果表中为某个列或者列组合定义了UNIQUE属性的话，MySQL会对我们插入的记录做校验，如果新插入记录在该列或者列组合的值已经在表中存在了，那就会报错！

可以使用alter修改表添加约束

ALTER TABLE student_info ADD UNIQUE (id_number);

这里省略了约束名，系统会自动定义的

check 限制列的取值范围

表明该列或者列组合的值域

直接写在创建列后面属性

比如创建学生分数表时候 score列的值域限制在0-100

CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    
    score TINYINT check(score>=0 and score<=100),
    
    PRIMARY KEY (number, subject),
    FOREIGN KEY(number) REFERENCES student_info(number)
);

或者在最后定义约束

check 约束名 (约束值域)

举例：创建学生分数表时候 score列的值域限制在0-100

CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject),
    FOREIGN KEY(number) REFERENCES student_info(number)，
    
    CHECK score_check(score>=0 and score<=100)
);

约束名字可以省略，比如这里的score_check，系统会自动定义

可以后面alter修改表添加约束

 alter table student_score add check(score>=0 and score<=100);

这里省略了约束名字。

下面是一些其他的属性

AUTO_INCREMENT 自动增长整形&浮点型

如果一个表中的某个列的数据类型是整数类型或者浮点数类型，那么这个列可以设置AUTO_INCREMENT属性。

当我们把某个列设置了AUTO_INCREMENT属性之后，如果我们在插入新记录的时候不指定该列的值，或者将该列的值显式地指定为NULL或者0，那么新插入的记录在该列上的值就是当前该列的最大值加1后的值（有一点点绕，稍后一举例子大家就明白了）。

我们可以用这样的语法来定义这个列：

列名 列的类型 AUTO_INCREMENT

比如我们想在first_table表里设置一个名为id的列，把这个列设置为主键，来唯一标记一条记录，然后让其拥有AUTO_INCREMENT属性，我们可以这么写：

alter table first_table add column id int unsigned auto_increment primary key comment 'id列 无符号整数 auto_increment自动增长';

图片.png

那我们在插入新记录时可以忽略掉这个列，或者将列值显式地指定为NULL或0，但是它的值将会递增，看：

INSERT INTO first_table(first_column, second_column) VALUES(1, 'aaa');

INSERT INTO first_table(id, first_column, second_column) VALUES(NULL, 1, 'aaa');

INSERT INTO first_table(id, first_column, second_column) VALUES(0, 1, 'aaa');

图片.png

可以看到，列id是从1开始递增的。

在为列定义AUTO_INCREMENT属性的时候需要注意这几点：

一个表中最多有一个具有AUTO_INCREMENT属性的列。

具有AUTO_INCREMENT属性的列必须建立索引。主键和具有UNIQUE属性的列会自动建立索引。不过至于什么是索引，在学习MySQL进阶的时候才会介绍。

拥有AUTO_INCREMENT属性的列就不能再通过指定DEFAULT属性来指定默认值

一般拥有AUTO_INCREMENT属性的列都是作为主键的属性，来自动生成唯一标识一条记录的主键值。

影响展示外观的ZEROFILL属性

下边是正整数3的三种写法：

写法一：3
写法二：003
写法三：000003

对于无符号整数类型的列，我们可以在查询数据的时候让数字左边补0，如果想实现这个效果需要给该列加一个ZEROFILL属性（也可以理解为这是一个属于数据类型的属性），就像这样：

CREATE TABLE zerofill_table (
         i1 INT UNSIGNED ZEROFILL,
         i2 INT UNSIGNED
     );

我们在zerofill_table表中创建了两个无符号整数列，不同的是i1列具有ZEROFILL属性，下边我们为这个表插入一条记录：

 INSERT INTO zerofill_table(i1, i2) VALUES(1, 1);

然后我们使用查询语句来展示一下刚插入的数据：

图片.png

对于具有ZEROFILL属性的i1列，在显示的时候在数字前边补了一堆0，仔细数数发现是9个0，而没有ZEROFILL属性的i2列，在显示的时候并没有在数字前补0。

为什么i1列会补9个0呢？我们查看一下zerofill_table的表结构：

图片.png

可以看见加了一个(10)，这个10就是所谓的显示宽度。
如果声明了 ZEROFILL 属性的整数列的实际值的位数小于显示宽度时，会在实际值的左侧补0，使补0的位数和实际值的位数相加正好等于显示宽度。

不过在使用ZEROFILL属性时应该注意下边几点：

在展示查询结果时，某列数据自动补0的条件有这几个：
- 该列必须是整数类型的
- 该列必须有UNSIGNED ZEROFILL的属性
- 该列的实际值的位数必须小于显示宽度

在创建表的时候，如果声明了ZEROFILL属性的列没有声明UNSIGNED属性，那MySQL会为该列自动生成UNSIGNED属性。

也就是说如果我们创建表语句是这样的：

CREATE TABLE zerofill_table (
    i1 INT ZEROFILL,
    i2 INT UNSIGNED
);

可以省略 unsigned属性，后面属性定义了ZEROFILL会自动生成UNSIGNED属性。

整数的显示宽度 ()

每个整数类型都会有默认的显示宽度。

比如TINYINT的默认显示宽度是4，INT的默认显示宽度是(11)... 如果加了UNSIGNED属性，则该类型的显示宽度减1，比如TINYINT UNSIGNED的显示宽度是3，INT UNSIGNED的显示宽度是10。

显示宽度并不会影响实际类型的实际存储空间。

显示宽度仅仅是在展示查询结果时，如果整数的位数不够显示宽度的情况下起作用的，并不影响该数据类型要求的存储空间以及该类型能存储的数据范围，也就是说INT(1)和INT(10)仅仅在展示时可能有区别，在别的方面没有任何区别。

比方说如果zerofill_table表中i1列的显示宽度是5，而数字12345678的位数是8，它照样可以被填入i1列中：

 INSERT INTO zerofill_table(i1, i2) VALUES(12345678, 12345678);

图片.png

只有列的实际值的位数小于显示宽度时才会补0，实际值的位数大于显示宽度时照原样输出。

这里我们试试超过i1的显示宽度10位会原样输出：

图片.png

对于没有声明ZEROFILL属性的列，显示宽度没有用。

整数

只有在查询声明了ZEROFILL属性的列时，显示宽度才会起作用，否则忽略显示宽度这个东西的存在。

总结一下数据库完整性

完整性检查是围绕完整性约束条件进行的，因此完整性约束条件是完整性控制机制的核心。完整性约束条件的作用对象可以是表、元组和列。

检查数据的符合语义，完整性约束。

列级约束

图片.png

元祖约束

图片.png

关系约束

图片.png

数据库表的列的属性和约束

列的属性

默认值 default

NOT NULL属性

主码 primary key

外键 foreign key

UNIQUE 限制列取值不能重复

check 限制列的取值范围

下面是一些其他的属性

AUTO_INCREMENT 自动增长 整形&浮点型

影响展示外观的ZEROFILL属性

整数的显示宽度 ()

总结一下数据库完整性

列级约束

元祖约束

关系约束

举例

AUTO_INCREMENT 自动增长整形&浮点型