MySql（二十）常见约束和标识列

分手后的思念是犯贱 2021-12-15 15:27 336阅读 0赞

# 文章目录 #

常见约束  
        约束含义  
        约束分类  
        约束的添加分类  
        添加约束的时机  
    标识列

# 常见约束 #

## 约束含义 ##

> 用于显示表中的数据，从而保证表中数据的准确性和可靠性

## 约束分类 ##

> not null、default、primary key、unique、check、foreign key

1. not null

> 含义：非空；用于保证该字段的值不能为空。比如：姓名、学号等

2. defalut

> 含义：默认；用于保证该字段有默认值。比如：性别

3. primary key

> 含义：主键；用于保证该字段的具有唯一性，并且非空。比如：学号、员工编号等

4. unique

> 含义：唯一；用于保证该字段的值具有唯一性，但是可以为空(只能有一个为null)。比如：座位号

5. check

> 含义：检查约束(mysql中不支持，即使用语法虽然不报错但是没有任何效果)；
> 
> 作用：比如sex列一般定义为char类型；‘男’和’女’都可以添加；但事实上’中’或’1’也都可以添加进去，即只要是符合char类型范围内的都可以添加进去。但是要求性别只可能是’男’和’女’，这个时候就可以用检查约束：即只有满足sex='男’或sex='女’才可以将数据添加进去

6. foreign key

> 含义：外键约束；用于限制两个表的关系，用于保证该字段的值必须来自于某主表的关联列的值。
> 
> （在从表中添加外键约束，用于引入主表中某列的值）
> 
> 比如：员工表的部门编号、学生表的专业编号、员工表的工种编号…

## 约束的添加分类 ##

1.  列级约束:六大约束语法上都支持，但是“外键约束”没有效果
2.  表级约束:除了not null 和 default其他都支持

CREATE TABLE 表名(
        字段名1 字段类型 列级约束,
        ...
        字段名n 字段类型 列级约束,
        表级约束
    );

## 添加约束的时机 ##

> 1.  创建表时
> 2.  修改表时

①：创建表时添加列级约束

> 列级约束:六大约束语法上都支持，但是”外键约束“没有效果

语法：

> 直接在字段名和字段类型的后面添加：约束类型
> 
> 列级约束相当于只支持：默认、非空、唯一、主键

表student

CREATE TABLE student(
        id INT PRIMARY KEY, /**主键**/
        name VARCHAR(4) NOT NULL, /**非空**/
        gender CHAR(1) CHECK(gender='男'OR gender='女'), /**检查**/
        age INT DEFAULT 20, /**默认**/
        seatNumber INT UNIQUE, /**唯一**/
        majorid INT FOREIGN KEY REFERENCES major(id) /**外键**/
    );

表major

CREATE TABLE major(
        id INT PRIMARY KEY, /**主键**/
        majorName VARCHAR(10)
    );

> desc 表名;    //查看表结构
> 
> show index from 表名;    //查看表中所有索引

②：创建表时添加表级约束

> 表级约束:除了not null 和 default其他都支持

语法：

> 在各个字段的下面添加：
> 
> constraint 约束名 约束类型(字段名);或者
> 
> 约束类型(字段名); // 此时约束名默认为对应字段的名字

表student

CREATE TABLE student(
        id INT, 
        name VARCHAR(4) NOT NULL,
        gender CHAR(1), 
        age INT DEFAULT 20, 
        seatNumber INT, 
        majorid INT,
        
        CONSTRAINT pk PRIMARY KEY(id), /**主键**/
        CONSTRAINT uq UNIQUE(seat), /**唯一**/
        CONSTRAINT ck CHECK(gender='男'OR gender='女'), /**检查**/
        CONSTRAINT fk_student_major FOREIGN KEY (majorid) REFERENCES major(id) /**外键**/
    );

③：修改表时添加约束

表student

CREATE TABLE student(
        id INT, 
        name VARCHAR(4), 
        gender CHAR(1), 
        age INT, 
        seatNumber INT, 
        majorid INT 
    );

1.  列级约束:六大约束语法上都支持，但是“外键约束”没有效果
2.  表级约束:除了not null 和 default其他都支持

/**添加非空约束**/
    ALTER TABLE student MODIFY COLUMN name VARCHAR(4) IS NOT NULL;
    /**删除非空约束**/
    ALTER TABLE student MODIFY COLUMN name VARCHAR(4) IS NULL;
    Alter TABLE student MODIFY COLUMN name  VARCHAR(4);
    /**添加默认约束**/
    ALTER TABLE student MODIFY COLUMN age INT DEFAULT 18；
    /**添加主键约束(列级约束)**/
    ALTER TABLE student MODIFY COLUMN id INT PRIMARY KEY；
    /**添加主键约束(表级约束)**/
    ALTER TABLE student ADD PRIMARY KEY(id)；
    /**添加唯一键约束(列级约束)**/
    ALTER TABLE student MODIFY COLUMN seat INT UNIQUE；
    /**添加唯一键约束(表级约束)**/
    ALTER TABLE student ADD UNIQUE(seat)；
    ALTER TABLE student ADD CONSTRAINT seat1 UNIQUE(seat)；
    /**添加外键约束(由于列级对外键反映，这里指写表级约束)**/
    ALTER TABLE student ADD FOREIGN KEY(majorid) REFERENCES major(id);
    ALTER TABLE student ADD CONSTRAINT fk_student_major FOREIGN KEY(majorid) REFERENCES major(id);

修改表时添加约束语法总结：

1.修改表时添加列级约束：

> alter table 表名 modify column 字段名 字段类型 新约束类型;

2.修改表时添加表级约束：

> alter table 表名 add contraint 约束名 新约束类型(字段名);
> 
> alter table 表名 新约束类型(字段名);

④：修改表时删除约束

/**删除非空约束(列的默认约束为null)**/
    ALTER TABLE student MODIFY COLUMN name VARCHAR(4);
    /**删除默认约束**/
    ALTER TABLE student MODIFY COLUMN age INT;
    /**删除主键约束**/
    ALTER TABLE student MODIFY COLUMN id INT;
    ALTER TABLE student DROP PRIMARY KEY;
    /**删除唯一键约束**/
    ALTER TABLE student MODIFY COLUMN seat INT;
    ALTER TABLE student DROP INDEX seat;
    /**删除外键约束**/
    ALTER TABLE student DROP FOREIGN KEY fk_student_major;

表major

CREATE TABLE major(
        id INT PRIMARY KEY, /**主键**/
        majorName VARCHAR(10)
    );

1. 'primary key’和’unique’的区别

<table> 
 <thead> 
  <tr> 
   <th style="text-align:center;">&nbsp;</th> 
   <th style="text-align:center;">保证唯一性</th> 
   <th style="text-align:center;">是否允许为空</th> 
   <th style="text-align:center;">一个表是允许有多个</th> 
   <th style="text-align:center;">是否允许组合</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">primariy key</td> 
   <td style="text-align:center;">✔</td> 
   <td style="text-align:center;">✘</td> 
   <td style="text-align:center;">✘&nbsp;</td> 
   <td style="text-align:center;">&nbsp;✔（不推荐使用）</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">unique</td> 
   <td style="text-align:center;">✔</td> 
   <td style="text-align:center;">✔（只允许一个为空）</td> 
   <td style="text-align:center;">✔</td> 
   <td style="text-align:center;">✔（不推荐使用）</td> 
  </tr> 
 </tbody> 
</table>

2. 外键特点：

*  要求在从表设置外键关系
 *  从表的外键列的类型和主表的关联列的类型要求一致或兼容。（名称无要求）
 *  主表的关联列必须是一个key(一般是主键或唯一)
 *  插入数据时：先插入主表，再插入从表；删除数据时：先删除从表，再删除主表

列级约束和表级约束的区别：

<table> 
 <thead> 
  <tr> 
   <th style="text-align:center;">&nbsp;</th> 
   <th style="text-align:center;">位置</th> 
   <th style="text-align:center;">支持的约束类型</th> 
   <th style="text-align:center;">是否可以器约束名</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">列级约束</td> 
   <td style="text-align:center;">列的后面</td> 
   <td style="text-align:center;">语法上都支持，但是外键约束没有效果&nbsp;</td> 
   <td style="text-align:center;">不可以</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">表级约束</td> 
   <td style="text-align:center;">&nbsp;所有列的下面</td> 
   <td style="text-align:center;">“默认”和“非空”不支持，其他都支持&nbsp;&nbsp; &nbsp;</td> 
   <td style="text-align:center;">可以（主键没有效果）</td> 
  </tr> 
 </tbody> 
</table>

## 标识列 ##

定义：标识列又称为自增长列

含义：可以不用手动的插入数值，系统提供默认的序列值

1. 创建表时设置标识列

/**创建表并添加标识列**/
    CREATE TABLE student(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(4)
    );
    /**向有标识列的表中天健数据**/
    INSERT INTO student(id,name) 
    VALUES
    (null,'Tom'),
    (null,'Join');
    /**方法2**/
    INSERT INTO student(name)
    VALUES
    ('Tom'),
    ('Join');

> //查看表中有关于标识列的变量
> 
> show variables like ‘%auto\_increment%’;
> 
> 1.  有关于标识列的变量1：auto\_increment\_increment 默认值为：1 即默认“步长”
> 2.  有关于标识列的变量2：auto\_increment\_offset 默认值为：1 即"起始值
> 
> 因此我们可以通过设置标识列的变量来设置它的步长和起始值
> 
> （mysql中不支持设置“起始值”；但是支持设置"步长"）

①：设置步长：

>     set auto\_increment\_increment = 3;

②：设置“起始值”

/**1. 手动设置起始值**/
    
        insert into student (id,name) values (10,'Tom');
    
    /** 2. 其他列都设置为null,这样就达到设置起始值为10的目的了**/
    
        insert into student (id,name) values (null,'Join');

1. 标识列必须和“主键”进行搭配使用吗？

> 答案：不一定；但是标识列要求必须和一个key进行搭配使用（key:主键、唯一、外键…）

2. 一个表中可以有多个标识列吗？

> 答案：至多一个

3. 标识列的类型必须是数值型吗？

> 答案：是的

2. 修改表时设置标识列

ALTER TABLE student MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

3 修改表时删除标识列

ALTER TABLE student MODIFY COLUMN id INT;

本文原文地址：[https://blog.csdn.net/qq\_43952245/article/details/90612305][https_blog.csdn.net_qq_43952245_article_details_90612305]

[https_blog.csdn.net_qq_43952245_article_details_90612305]: https://blog.csdn.net/qq_43952245/article/details/90612305