MySQL（二）——约束

一、约束分类

（1）PRIMARY KEY：主键约束是使用最频繁的约束，用于保证该字段的值具有唯一性并且非空。在设计数据表时，一般情况下，都会要求表中设置一个主键。
（2）NOT NULL ：非空约束，用来约束表中的字段不能为空。。
（3）FOREIGN KEY：外键约束经常和主键约束一起使用，用来确保数据的一致性。
（4）DEFAULT：默认值约束，用来约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。
（5）UNIQUE：唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的，虽然只能有一个空值。
（6）CHECK：检查约束是用来检查数据表中，字段值是否有效的一个手段。

二、主键约束

（1）MySQL 主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行，这样的一列或多列称为表的主键，通过它可以强制表的实体完整性，同时可以方便根据主键查询该行数据。
（2）选取设置主键约束的字段，主键约束即在表中定义一个主键来唯一确定表中每一行数据的标识符，主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键。

主键应该遵守下面的规则：
（1）唯一性原则。每个表只能定义一个主键，主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值。
（2）在创建表时设置主键约束，在 CREATE TABLE 语句中，主键是通过 PRIMARY KEY 关键字来指定的。
（3）一个字段名只能在联合主键字段表中出现一次。
（4）联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。
（5）当创建主键的约束时，系统会默认所在的列和列组合建立对应的索引，方便提高查询效率。

1、单列主键

（1）定义字段时，指定单列主键
语法格式：

CREATE TABLE 表名称 ( 
	字段名 数据类型 PRIMARY KEY 
)
1
2
3

eg：

CREATE TABLE `user_test` (
  `id` int  PRIMARY KEY ,
  `name` varchar(255) ,
  `age` int 
);
1
2
3
4
5

（2）定义完字段之后，指定主键列

CREATE TABLE 表的名称 (
	....
	CONSTRAINT 主键名称 PRIMARY key (字段名);
)
1
2
3
4

eg：

CREATE TABLE `user_test` (
  `id` int ,
  `name` varchar(255) ,
  `age` int,
  CONSTRAINT pk1 PRIMARY key (id)
);
1
2
3
4
5
6

2、联合主键

联合主键（复合主键），由多个列（字段组成）。
注意事项：
（1）当主键是有一个列组成时，不能够直接在字段名称后面声明主键约束；
（2）一张表只能够允许一个主键。

定义字段时，指定复合主键：
eg：

CREATE TABLE `user_test` (
  `id` int,
  `name` varchar(255),
  `age` int ,
  `phone` varchar(11) ,
  CONSTRAINT pk1 PRIMARY KEY (id,phone)
);
1
2
3
4
5
6
7

3、删除主键约束

ALTER TABLE 数据表名 DROP PRIMARY KEY;
1

三、非空约束

not null 非空约束用于确保当前列的值不为空；在创建表时，如果指定 not null 该字段在插入数据时不允许为空;

语法格式：

1、创建表的时候添加非空约束

<字段名> <数据类型> NOT NULL;
1

eg：

CREATE TABLE `users_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int NOT NULL,
  `phone` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
);
1
2
3
4
5
6
7

2、创建表后修改表时添加非空约束

ALTER TABLE 数据表名
CHANGE COLUMN 字段名 字段名 数据类型 NOT NULL;
1
2

eg：

ALTER TABLE users_test 
CHANGE COLUMN name VARCHAR(255) NOT NULL;
1
2

3、删除非空约束

ALTER TABLE 数据表名
CHANGE COLUMN 字段名 字段名 数据类型 NULL;
1
2

四、外键约束

外键约束是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

注意：

主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

必须为主表定义主键。 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。 外键中列的数目必须和主表的主键中列的数目相同。

外键中列的数据类型必须和主表主键中对应列的数据类型相同。

1、在创建表时设置外键约束

[CONSTRAINT 外键名] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES 主表名 主键列1 [，主键列2，…]
1
2

eg：

CREATE TABLE users_test (
	id INT(11) PRIMARY KEY,
	name VARCHAR(22) NOT NULL,
	phone VARCHAR(11)
	);

CREATE TABLE users_emp (
	id INT(11) PRIMARY KEY,
	name VARCHAR(25),
	userId INT(11),
	CONSTRAINT emp_user1
	FOREIGN KEY(userId) REFERENCES users_test(id)
	);
1
2
3
4
5
6
7
8
9
10
11
12
13

2、在修改表时添加外键约束

ALTER TABLE 数据表名> ADD CONSTRAINT 外键名
FOREIGN KEY(列名) REFERENCES 主表名 (列名);
1
2

eg：

ALTER TABLE users_emp
ADD CONSTRAINT emp_user1
FOREIGN KEY(userId)
REFERENCES users_test(id)
1
2
3
4

3、删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
1

eg：

ALTER TABLE users_emp
DROP FOREIGN KEY emp_user1;
1
2

五、默认约束

用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。
默认值约束通常用在已经设置了非空约束的列，这样能够防止数据表在录入数据时出现错误。

1、在创建表时设置默认值约束

字段名 数据类型 DEFAULT 默认值;
1

eg：

CREATE TABLE users_test (
	id INT(11) PRIMARY KEY,
	name VARCHAR(22),
	adress VARCHAR(50) DEFAULT 'HeFei'
);
1
2
3
4
5

2、在修改表时添加默认值约束

ALTER TABLE 数据表名
CHANGE COLUMN 字段名 数据类型 DEFAULT 默认值;
1
2

eg：

ALTER TABLE users_test
CHANGE COLUMN adress VARCHAR(50) DEFAULT 'HeFei';
1
2

六、唯一约束

指所有记录中字段的值不能重复出现。
唯一约束与主键约束相似的是它们都可以确保列的唯一性。不同的是，唯一约束在一个表中可有多个，并且设置唯一约束的列允许有空值，但是只能有一个空值。而主键约束在一个表中只能有一个，且不允许有空值。比如，在用户信息表中，为了避免表中用户名重名，可以把用户名设置为唯一约束。

1、在创建表时设置唯一约束

字段名 数据类型 UNIQUE
1

eg：

CREATE TABLE users_test
	id INT(11) PRIMARY KEY,
	name VARCHAR(22) UNIQUE,
	adress VARCHAR(50)
);
1
2
3
4
5

2、在修改表时添加唯一约束

ALTER TABLE 数据表名 ADD CONSTRAINT 唯一约束名 UNIQUE(列名);
1

eg：

ALTER TABLE users_test
ADD CONSTRAINT unique_name UNIQUE(name);
1
2

3、删除唯一约束

ALTER TABLE 表名 DROP INDEX 唯一约束名;
1

eg：

ALTER TABLE users_test
DROP INDEX unique_name;
1
2

七、检查约束

用来检查数据表中字段值有效性的一种手段，可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。设置检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。

CHECK 表达式
1

“表达式”指的就是 SQL 表达式，用于指定需要检查的限定条件。
若将 CHECK 约束子句置于表中某个列的定义之后，则这种约束也称为基于列的 CHECK 约束。
在更新表数据的时候，系统会检查更新后的数据行是否满足 CHECK 约束中的限定条件。MySQL 可以使用简单的表达式来实现 CHECK 约束，也允许使用复杂的表达式作为限定条件，例如在限定条件中加入子查询。
若将 CHECK 约束子句置于所有列的定义以及主键约束和外键定义之后，则这种约束也称为基于表的 CHECK 约束。该约束可以同时对表中多个列设置限定条件。

1、在创建表时设置检查约束

CHECK(检查约束)
1

eg：

CREATE TABLE users_test (
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    salary FLOAT,
    CHECK(salary>0 AND salary<100),
    );
1
2
3
4
5
6

2、在修改表时添加检查约束

ALTER TABLE 表名 ADD CONSTRAINT 检查约束名 CHECK(检查约束)
1

eg：

ALTER TABLE users_test
    ADD CONSTRAINT check_id
    CHECK(id>0);
1
2
3

3、删除检查约束

ALTER TABLE 数据表名 DROP CONSTRAINT 检查约束名;
1

eg：

ALTER TABLE users_test DROP CONSTRAINT check_id;
1

八、自定增长约束

1、自动增长概念

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值，每增加一条记录，主键自动增加。
通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长。
默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复(即为主键或主键的一部分)。
AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。
AUTO_INCREMENT 约束的字段只能是整数类型(TINYINT、SMALLINT、INT、BIGINT 等)。
AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

2、设置自动增长

语法格式如下：

字段名 数据类型 AUTO_INCREMENT
1

eg：

drop table users_test;
CREATE TABLE `users_test` (
  `id` int PRIMARY KEY AUTO_INCREMENT  ,
  `name` varchar(255),
  `age` int ,
  `phone` varchar(11) 
);
1
2
3
4
5
6
7

3、自动增长设置初始值

eg1：创建表的时候指定

//设置初始值为100
CREATE TABLE `users_test` (
  `id` int PRIMARY KEY AUTO_INCREMENT  ,
  `name` varchar(255),
  `age` int ,
  `phone` varchar(11) 
)AUTO_INCREMENT=100;
1
2
3
4
5
6
7

eg2：创建表之后修改自定增长开始值

//设置初始值为100
alter table users_test AUTO_INCREMENT 100; 
1
2

4、delete 与 truncate 删除数据区别

delete 删除数据之后，自动增长还是从最后一个删除数据的id基础上做自增；truncate 清空数据之后，自动增长是从初始值1开始。
delete from users_test （清空表数据）；truncate users_test （清空表数据）。
truncate 会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表；
delete 是逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据。