列的属性

列的属性简介

列的部分属性又叫做“列的约束”，比如默认值属性叫做“默认值约束”，而非空属性也叫做“非空约束”。

create table 表名
(
    列名1 数据类型 列属性,
    列名2 数据类型 列属性,
    ……
    列名n 数据类型 列属性
);

CREATE TABLE product (
    id INT NOT NULL AUTO_INCREMENT COMMENT '商品编号',
    name VARCHAR(10) COMMENT '商品名称',
    type VARCHAR(10) COMMENT '商品类型',
    city VARCHAR(10) COMMENT '来源城市',
    price DECIMAL(5, 1) COMMENT '出售价格',
    date DATE COMMENT '入库时间',
    PRIMARY KEY (id)
);

默认值

对于没有被插入值的列，它的值就会被设置为NULL。换一种说法来说就是：默认情况下，列的默认值其实是NULL。

在MySQL中，如果希望列的默认值不是NULL，而是其他的值，此时可以使用default这个属性来实现。

列名 类型 default 默认值

非空

在实际开发中，有时要求表中的某些列必须有值而不能是NULL，此时我们可以使用not null这个属性来实现。

列名 类型 not null

自动递增

在实际开发中，很多时候我们希望某一列（如主键列）的值是自动递增的，此时可以使用auto_increment属性来实现。

列名 类型 auto_increment

默认情况下，设置了auto_increment属性的列，开始值是1，然后每次递增1。如果想要改变初始值，我们可以使用下面这种方式。

列名 类型 auto_increment = 1000;

提示

此外对于MySQL中的auto_increment属性来说，我们需要特别清楚以下7点:

1. auto_increment只能用于整数列，而不能用于其他类型的列。
2. 一个表中最多只能有一个具有auto_increment属性的列。
3. auto_increment属性只能给已经建立了索引的列设置。其中，主键列和唯一键列会自动建立索引。
4. 如果某一列设置了auto_increment，那么该列不能再使用default属性来指定默认值。
5. 如果某一列设置了auto_increment，那么MySQL就会自动帮该列生成一个唯一值。该值从1开始，然后每次递增1。
6. 如果某一列设置了auto_increment，那么它的值就是自动递增的，所以我们在插入数据的时候，可以不指定该列的值。
7. MySQL使用的是auto_increment来实现自动递增，而SQL Server使用identity来实现自动递增。

条件检查

在实际开发中，可能会遇到这样一个场景：有一个age列，我们需要限制它的值在0~200之间，这样是为了防止输入的年龄值超过正常的范围。

在MySQL中，我们可以使用check属性来为某一列添加条件检查。

列名 类型 check(表达式)

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age INT CHECK (age BETWEEN 0 AND 200)
);

唯一键

在MySQL中，如果我们希望某一列中存储的值都是唯一的，此时可以使用unique属性来实现。其中使用了unique属性的列，也叫做“唯一键”或“唯一键列”。

列名 类型 unique

主键

如果某一列设置为主键，那么这一列的的值具有两个特点：1.具有唯一性；2.不允许为空（NULL）。

在MySQL中，我们可以使用primary key属性来设置某一列为“主键”。其中，该列也叫做“主键”或“主键列”。

列名 类型 primary key

提示

主键和唯一键其实是非常相似的，它们的值都是不允许重复的，区别在于以下3个方面：

1. 主键的值不能为NULL，而唯一键的值可以为NULL。
2. 一个表只能有一个主键，但可以有多个唯一键。
3. 主键可以作为外键，但是唯一键不可以。

外键

在MySQL中，我们可以使用foreign key属性来设置一个外键。所谓的外键，指的是子表中的某一列受限于（或依赖于）父表中的某一列。

constraint 外键名 foreign key(子表的列名) references 父表名(父表的列名)

示例：

假设我们有两个表 orders 和 customers，其中 orders 表中的 customer_id 列是外键，引用 customers 表中的 id 列。

-- 创建 customers 表
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 创建 orders 表，并设置外键约束
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) 
        REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

• ON DELETE CASCADE 表示如果 customers 表中的一条记录被删除，则所有引用该记录的 orders 表中的记录也会被自动删除。
• ON UPDATE CASCADE 表示如果 customers 表中的一条记录的 id 值被更新，则 orders 表中所有引用该记录的 customer_id 值也会被自动更新为新的值。

提示

对于外键来说，我们还需要清楚以下3点：

1. 一般是使用父表的主键作为子表的外键。
2. 插入数据时，必须先插入父表，然后才能插入子表。
3. 删除表时，只能先删除子表，然后才能删除父表。

注释

在MySQL中，我们可以使用comment关键字来给列添加一个注释。

列名 类型 comment 注释内容

操作已有表

在前面内容中，都是在创建表的同时来添加列的属性。实际上，还可以操作已经创建好的表的列属性，我们需要分为以下两种情况来考虑。

1. 约束型属性。
2. 其他属性。

约束型属性

如果想要添加约束型属性，我们可以使用alter table...add constraint...语句来实现。如果想要删除约束型属性，我们可以使用alter table...drop constraint...语句来实现。

在MySQL中，约束型属性主要有以下4种：

1. 条件检查。
2. 唯一键。
3. 主键。
4. 外键。

-- 添加属性
alter table 表名
add constraint 标识名
属性;

**示例：**指定了一个约束名称 unique_fullname ，其约束了first_name 和 last_name 的组合是唯一的。

LTER TABLE employees ADD CONSTRAINT unique_fullname UNIQUE (first_name, last_name);

其他属性

在MySQL中，对于其他属性，我们可以使用alter table...modify...语句来添加和删除。这些属性主要包括以下4种。

1. 默认值。
2. 为空。
3. 自动递增。
4. 注释。

alter table 表名
modify 列名 类型 属性;

示例： 指定name 列设置为 NOT NULL

ALTER TABLE fruits MODIFY COLUMN name VARCHAR(100) NOT NULL;

注意

以下是错误语法：

ALTER TABLE fruits MODIFY COLUMN name NOT NULL;

使用 modify 需要提供该列的数据类型（例如 VARCHAR）以及其他任何相关的属性。

提示

特别说明：对于列的属性，我们更推荐在建表的同时就设置好。如果在建好表之后再去设置属性，这样是非常麻烦的。实在迫不得已了，再考虑使用这一节介绍的方式去“补救”。

实践

使用SQL语句创建一个学生表student，该表包含5列，其中列名、类型、注释如下表所示。其中，这些列的约束（即列属性）包括以下4个方面。

（1）学号作为主键，并且是自动递增的，从1开始，增量为1。 （2）名字不允许为空。 （3）性别的值只能是：'男'和'女'。 （4）年龄的值在0~100之间。

列名	说明
sno	int
name	varchar(10)
sex	char(5)
age	int
major	varchar(20)

CREATE TABLE student (
    sno INT AUTO_INCREMENT PRIMARY KEY COMMENT '学号',
    name VARCHAR(10) NOT NULL COMMENT '名字',
    sex CHAR(5) CHECK (sex IN ('男', '女')) COMMENT '性别',
    age INT CHECK (age BETWEEN 0 AND 100) COMMENT '年龄',
    major VARCHAR(20) COMMENT '专业'
);