mysql-常见约束及标识列

什么是约束

含义:一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性。

常见约束

1. NOT NULL:非空约束，用于保证该字段的值不能为空。比如姓名、学号
2. DEFAULT：用于保证该字段有默认值。比如性别
3. PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空。比如学号、员工号
4. UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。比如座位号
5. CHECK：检查约束【mysql不支持】比如年龄、性别
6. FOREIGN KEY：外键，用于限制两个表的关系，，用于保证该字段的值必须来自于主表的关联列的值。在从表中添加外键约束，用于引用主表中某列的值。
   比如：学生表的专业编号，员工表的部门编号，员工表的工种编号。

添加约束的时机：
①创建表时

cerate table 表名（
    字段名 字段类型  约束
）

②修改表时

约束添加的分类：
列级约束：
六大约束语法上都支持，但外键约束没有效果
表级约束：
除了非空、默认，其他都支持。

create table 表名（
   字段名 字段类型 列级约束
   字段名  字段类型，
   表级约束
）

创建表时添加约束

1、添加列级约束
直接在字段名和类型后面追加约束类型，只支持默认、非空、唯一
eg:
创建数据库

CREATE DATABASE students;

使用当前数据库

USE students;

在当前数据库中创建表

CREATE TABLE student(
   id INT PRIMARY KEY,#主键
   stuName VARCHAR(20) NOT NULL,#外键
   gender CHAR(1),CHECK(gender='男' OR gender='女'),#检查约束
   seat INT UNIQUE,#唯一
   age INT DEFAULT 18,#默认约束
   majorId INT  REFERENCES major(id) #外键 FOREIGN KEY
);

创建major表：

CREATE TABLE major(
  id INT PRIMARY KEY,
  majorName VARCHAR(20)
);

#查看student表中所有的索引，包含主键、外键

SHOW INDEX FROM student;

添加表级约束

在各个字段的最下面
【CONSTRAINT 约束名】约束类型（字段名）

CREATE TABLE stuinfo(
     id INT,
     stuName VARCHAR(20),
     gender CHAR(1),
     seat INT,
     age INT,
     majorId INT,
     
     CONSTRAINT pk PRIMARY KEY(id),#主题
     CONSTRAINT uq UNIQUE(seat),#唯一键
     CONSTRAINT ck CHECK (gender='男' OR gender='女'),#检查
     CONSTRAINT fx_stuinfo_major FOREIGN KEY(majorId)REFERENCES major(id)

);

通用写法：

CREATE TABLE IF NOT EXISTS stuInfo(
  id INT PRIMARY KEY,
  stuName VARCHAR(20) NOT NULL,
  sex CHAR(1),
  age INT DEFAULT 18,
  seat INT UNIQUE,
  majorId INT,
  CONSTRAINT fx_stuinfo_major FOREIGN KEY(majorId)REFERENCES major(id)
);

主键和唯一的区别

1、区别：
①、一个表至多有一个主键，但可以有多个唯一
②、主键不允许为空，唯一可以为空
2、相同点
都具有唯一性
都支持组合键，但不推荐

	保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键	√	×	至少有1个	√，但不推荐
唯一	√	√	可以有多个	√，但不推荐

外键的特点

①要求在从表设置外键关系
②从表的外键列的类型和主表的关联列类型要求一致或兼容
③主表中的关联列必须是一个key（一般是主键或唯一）
④插入数据时，先插入主表，在插入从表
删除数据时，先删除从表，再删除主表。

修改表时添加约束

1. 添加列级约束

alter table 表名 modify column 字段名 字段类型 新的约束；

2. 添加表级约束

alter table 表名 add 【constraint 约束名】约束类型（字段名） 【外键的引用】;

创建一张表

CREATE TABLE stuinfo(
     id INT,
     stuName VARCHAR(20),
     gender CHAR(1),
     seat INT,
     age INT,
     majorId INT
)

1、添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL; 

2、添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

3、添加主键
①列级约束

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

②表级约束

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

4、添加唯一键
①列级约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

②表级约束

ALTER TABLE stuinfo ADD UNIQUE(seat);

5、添加外键
表级约束

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id);

修改表时删除约束

1、删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20)  NULL; 

2、删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

3、删除主键

ALTER TABLE stuinfo MODIFY COLUMN id INT ;
ALTER TABLE stuinfo drop PRIMARY KEY;

4、删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

查看唯一健

SHOW INDEX FROM stuinfo;

5、删除外键约束

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

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

	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	语法都支持，但外键没有效果	不可以
表级约束	所有列的下面	默认和非空不支持，其他支持	可以（主键没有效果）

语法上的区别

列级约束写法：
alter table 表名 modify column 字段名 字段类型 新的约束；

表级约束写法：
alter table 表名 add 【constraint 约束名】约束类型（字段名） 【外键的引用】;

标识列

什么是标识列
又称为自增长列：可以不用手动输入值，系统提供的默认的序列值。关键字：AUTO_INCREMENT

###创建表时设置标识列

CREATE TABLE tab_increment(
   id INT PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR(20)
);

插入数据:

insert into tab_increment(name),values('lucy');

SHOW VARIABLES LIKE '%auto_increment%';

设置步长

set auto_increment_increment=3;

特点：

• 标识列必须和主键搭配使用吗？不一定，但要求是一个key
• 一个表中可以几个标识列？至多一个
• 标识列的类型只能是数值型（int,float,double）
• 标识列可以通过set auto_increment_increment=3;设置步长也可以通过手动插入值设置起始值

修改表时设置标识列

alter table 表名 modify column 字段名  字段类型 约束 标识列

修改表时删除标识列

alter table 表名  modify colum 字段名 字段类型；