MySQL 05 约束、事务和分页查询

1、约束

概念

约束一般在创建表时添加，也可以在表创建后添加。其用于限制插入的数据（约束是表级的强制规定），可以保证数据的一致性和完整性。

 常见的约束：
    NOT NULL       非空约束
    DEFAULT        默认约束
    UNIQUE         唯一性约束
    PRIMARY KEY    主键约束
    FOREIGN KEY    外键约束

    CHECK          检查约束【MySQL不支持】

设置自增长列

只需在创建表时，在列名的数据类型的后面加上：AUTO_INCREMENT 即可

自增长列的特点：系统会提供一个序列号的值，不用我们管

常见错误

注意：一个表只能有一个字段使用AUTO_INCREMENT，且该字段必须为主键【PRIMARY KEY】的一部分
否则报错：ERROR 1075 (42000):Incorrect table definition; there can be only one auto column and it must be defined as a key

示例：创建一个employees表，id设置为自增列
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10),
    age INT(2)
);

非空约束

字段 数据类型 NOT NULL
用于确保当前列的值不为NULL值

注意：
⑴ 所有类型的值都可以为NULL值，包括INT、FLOAT等数据类型
⑵ 空字符串（”“）不等于NULL，0也不等于NULL

常见错误

错误代码： 1048
Column ‘?非空列?’ cannot be null

默认约束

字段 数据类型 DEFAULT 默认值
用于给字段设置默认值

示例：【创建一个student表，默认的class为1、默认的school为’希望小学’】

CREATE TABLE student (
    class INT DEFAULT 1,
    school VARCHAR(10) DEFAULT ‘希望小学’
);

常见错误

注意：添加默认值的字段时，如果想设置默认值，只能通过
INSERT INTO 表名(有默认值字段, 有默认值字段)
VALUES(DEFAULT, DEFAULT);
的方式来添加

示例：【给上面创建的student表，添加一条记录，要求值为默认值】

INSERT INTO student(class, school)
VALUES(DEFAULT, DEFAULT);

唯一性约束

字段 数据类型 UNIQUE

特点：
⑴ 可以为某个列添加唯一键，代表此列的值不能重复
⑵ 可以添加组合唯一键，代表多个列的组合不能重复
⑶ 添加唯一性约束可以为NULL值 （ 前提是没有设置NOT NULL，所以最好把UNIQUE和NOT NULL一起搭配使用【即类似于PRIMARY KEY的作用】）

常见错误

⑴ 当唯一性约束列中，添加了重复的值，就会报错：
错误代码： 1062
Duplicate entry ‘?已存在的值?’ for key ‘唯一约束的键名’

⑵ 设置了非空的唯一性约束的列，如果添加NULL值,就会报错
错误代码： 1048
Column ‘?非空列?’ cannot be null

⑴ 示例：【创建一个student表，其中name字段不能重复】
CREATE TABLE student (
    name VARCHAR(10) UNIQUE
);

注：这个表没有设置非空（NOT NULL）约束，所以可以添加多个NULL值
INSERT INTO student
VALUES(NULL);
多次执行这条语句，不会报错，但是最好不要这样

所以最好添加NOT NULL的约束：

CREATE TABLE student (
    NAME VARCHAR(10) UNIQUE NOT NULL
);
这样再次添加NULL值就会报错
错误代码： 1048
Column ‘name’ cannot be null

⑵ 示例：【创建一个student表，其中id和name字段不能重复（这里使用了组合唯一键）】
CREATE TABLE student (
    id INT,
    name VARCHAR(10),
    CONSTRAINT uk UNIQUE(id, name)
);

给上面这个表插入数据：
INSERT INTO student
SELECT 1, ‘张三’ UNION
SELECT 2, ‘张三’ UNION
SELECT 1, ‘张’;

这是不报错的，但是再次添加就会报错：
INSERT INTO student
VALUES(1, ‘张三’);

错误代码： 1062
Duplicate entry ‘1-张三’ for key ‘uk’

主键约束

PRIMARY KEY
代表该列不能重复，相当于表的主要标识列

特点：
⑴ 经常和AUTO_INCREMENT一起使用
⑵ 主键列不能为空（前提是没有设置自增或默认约束）
⑶ 可以添加组合主键，其代表多个列的组合不能重复
⑷ 相当于NOT NULL 和 UNIQUE的组合
⑸ 一个表最多有一个主键

⑴ 示例：【创建一个student表，将id设置为主键，并添加自增】
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10)
);

给上面的student表添加数据：
INSERT INTO student(name)
VALUES(‘张三’);
解释：因为设置了自增，所以可以不用设置id，同时每次系统添加的id值都不一样，所以满足非空和唯一两个约束条件【因为PRIMARY KEY相当于NOT NULL 和 UNIQUE的组合】

⑵ 示例：【创建一个student表，将id和name设置为组合主键】
CREATE TABLE student (
    id INT,
    name VARCHAR(10),
    CONSTRAINT pk PRIMARY KEY(id, name)
);

可以插入的数据：
INSERT INTO student
SELECT 1, ‘张三’ UNION
SELECT 2, ‘张三’ UNION
SELECT 1, ‘张’;

外键约束

FOREIGN KEY
从表的外键列的值，必须来自主表的关联列的值

注意：只能在表级约束处添加

语法：CONSTRAINT 约束名 FOREIGN KEY(外键列) REFERENCES 主表名(关联列)
或
FOREIGN KEY(外键列) REFERENCES 主表名(关联列)

哪个表中的值被另一个表所依赖，即另一个表需要从这个表中取值，这个表就是主表
哪个表需要从其他表中的相关联的列中取数据，这个表就是从表

特点：
⑴ 在从表中设置外间关系
⑵ 插入数据时，先插入主表【因为从表需要依赖主表数据】
⑶ 清空表数据或删除表时，先清空从表数据或先删除从表【因为主表中的值被从表依赖，所以主表无法早于从表被删除】
⑷ 主表的关联列必须是主键列（PRIMARY KEY）
⑸ 从表的外键列必须和主表的关联列的数据类型一致

常见错误

⑴ 从表中的外键列的数据必须来自主表的关联列的数据，如果添加了主表关联列没有的数据；或从表添加数据时，主表的关联列没有数据时，则会报错
错误代码： 1452
Cannot add or update a child row: a foreign key constraint fails

⑵ 如果先清空主表的数据，则会报错
错误代码： 1451
Cannot delete or update a parent row: a foreign key constraint fails

⑶ 如果先删除主表，则会报错
错误代码： 1217
Cannot delete or update a parent row

⑷ 如果主表的关联列不是主键，或从表的外键列设置了NOT NULL，而又想添加【级联置空】则会报错
错误代码： 1005
Can’t create table ‘数据库名.从表名’ (errno: 150)

示例：【创建一个student表，其majorid和majors表中的majorid相互关联】

① 先创建主表【majors】
CREATE TABLE majors (
    majorid INT PRIMARY KEY
);

② 再创建从表【student】
CREATE TABLE student (
    name VARCHAR(10),
    majorid INT,
    CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES majors(majorid)
);

③ 先给majors【主表】添加数据
INSERT INTO majors
SELECT 1 UNION
SELECT 2 UNION
SELECT 3;

④ 再给student【从表】添加数据
INSERT INTO student
SELECT ‘张三’, 1 UNION
SELECT ‘李四’, 2 UNION
SELECT ‘王五’, 3;

⑤ 先清空表【student】的数据
TRUNCATE TABLE student;

⑥ 再清空主表【majors】的数据
DELETE FROM majors;

⑦ 先删除从表【student】
DROP TABLE student

⑧ 再删除主表【majors】
DROP TABLE majors

级联删除

语法
CONSTRAINT fk FOREIGN KEY(外键列) REFERENCES 主表(关联列) ON DELETE CASCADE

Tips：外键列可以设置非空约束（NOT NULL）

示例：【创建一个student表，其majorid和majors表中的majorid相互关联】
【主表majors】
CREATE TABLE majors(
    majorname VARCHAR(10) PRIMARY KEY
);

INSERT INTO majors
SELECT ‘Java’ UNION
SELECT ‘C#’ UNION
SELECT ‘H5’;

【从表student】
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL,
    majorname VARCHAR(10),
    CONSTRAINT fk FOREIGN KEY(majorname) REFERENCES majors(majorname) ON DELETE CASCADE
);

INSERT INTO student
SELECT NULL, ‘张三’, ‘Java’ UNION
SELECT NULL, ‘李四’, ‘C#’ UNION
SELECT NULL, ‘王五’, ‘H5’;

【删除主表中的’C#’专业】
DELETE FROM majors
WHERE majorname = ‘C#’;

【查看从表数据】
mysql> SELECT * FROM student;
+—-+——–+———–+
| id | name | majorname |
+—-+——–+———–+
| 1 | 张三 | Java |
| 3 | 王五 | H5 |
+—-+——–+———–+
可以看到专业为’C#’的李四的记录已经没有了

级联置空

语法
CONSTRAINT fk FOREIGN KEY(外键列) REFERENCES 主表(关联列) ON DELETE SET NULL

注意：外键列不能设置非空约束（NOT NULL），否则报错：错误代码： 1005
Can’t create table ‘数据库名.从表名’ (errno: 150)

【主表majors】
CREATE TABLE majors(
    majorname VARCHAR(10) PRIMARY KEY
);

INSERT INTO majors
SELECT ‘Java’ UNION
SELECT ‘C#’ UNION
SELECT ‘H5’;

【从表student】
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL,
    majorname VARCHAR(10),
    CONSTRAINT fk FOREIGN KEY(majorname) REFERENCES majors(majorname) ON DELETE SET NULL
);

INSERT INTO student
SELECT NULL, ‘张三’, ‘Java’ UNION
SELECT NULL, ‘李四’, ‘C#’ UNION
SELECT NULL, ‘王五’, ‘H5’;

【删除主表中的’C#’专业】
DELETE FROM majors
WHERE majorname = ‘C#’;

【查看从表数据】
mysql> SELECT * FROM student;
+—-+——–+———–+
| id | NAME | majorname |
+—-+——–+———–+
| 1 | 张三 | Java |
| 2 | 李四 | NULL |
| 3 | 王五 | H5 |
+—-+——–+———–+
可以看到专业为’C#’的李四的专业的列值变成了NULL

检查约束【MySQL不支持】

语法：
CHECK(约束条件)

示例：【创建一个person表，其中gender字段的值只能为’男’或’女’】

CREATE TABLE person (
    name VARCHAR(10),
    gender CHAR DEFAULT ‘男’ CHECK(gender = ‘男’ or gender = ‘女’) – 添加默认值，以防报错
);

总结

⑴ 自增字段（AUTO_INCREMENT），一个表中只能有一个，一般都设置到主键（PRIMARY KEY）上。所以主键一般都和自增同时设置
⑵ 主键（PRIMARY KEY）相当于非空（NOT NULL）和唯一性（UNIQUE）
⑶ 唯一性约束（UNIQUE）因为可以插入多条NULL值，所以，最好和非空（NOT NULL）一起使用
⑷ 外键（FOREIGN KEY）要注意：从表的外键和主表的关联键的数据类型和长度要一致

组合键

只能设置在表级约束，需要用到CONSTRAINT关键字
如果不想给约束起名，可以直接用约束加上列名

唯一性约束

CONSTRAINT 约束名 UNIQUE(列名1, 列名2, …)
或
UNIQUE(列名1, 列名2, …)

主键

CONSTRAINT 约束名 PRIMARY KEY(列名1, 列名2, …)
或
PRIMARY KEY(列名1, 列名2, …)

创建表时添加约束

列级约束

列名 数据类型 约束

在设计列时，直接在列的后面加约束
可以添加：NOT NULL（非空），UNIQUE（唯一），PRIMARY KEY（主键），DEFAULT（默认），CHECK（检查）

但是不能添加：FOREIGN KEY（外键在列级约束上无效）

表级约束

列名 数据类型 [列级约束],
列名 数据类型 [列级约束],
CONSTRAINT 表级约束名 [约束类型](列名1, 列名2, …)

在所有列的下面，统一添加约束
可以添加：UNIQUE（唯一），PRIMARY KEY（主键），CHECK(检查)，FOREIGN KEY（外键）

但不能添加：NOT NULL（非空），DEFAUTL（默认值）

总结

一般情况下：
非空、默认、唯一、主键都会放在列级约束上，除非想给唯一、主键起名或组合键时，可以放在表级约束
外键放在表级约束

示例：
CREATE TABLE majors (
    majorid INT PRIMARY KEY, – 主键
    majorname VARCHAR(20) NOT NULL, – 非空
    CONSTRAINT uq UNIQUE(majorid, majorname) – 组合唯一键
);

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT, – 主键，自增
    name VARCHAR(10) NOT NULL UNIQUE, – 非空，唯一
    gender CHAR DEFAULT ‘男’, – 默认
    majorid INT,
    CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES majors(majorid) – 外键
);

CREATE TABLE mytab (
    col1 INT,
    col2 VARCHAR(20),
    CONSTRAINT pk PRIMARY KEY(col1, col2) – 组合主键
);

修改表时添加约束

添加默认约束

ALTER TABLE 表名
MODIFY 列名 数据类型 DEFAULT 值;

添加非空约束

ALTER TABLE 表名
MODIFY 列名 数据类型 NOT NULL;

添加唯一性约束

ALTER TABLE 表名
MODIFY 列名 数据类型 UNIQUE;
或
ALTER TABLE 表名
ADD UNIQUE(列名);
或
ALTER TABLE 表名
ADD CONSTRAINT 约束名 UNIQUE(列名);

添加主键约束

ALTER TABLE 表名
MODIFT 列名 数据类型 PRIMARY KEY;
或
ALTER TABLE 表名
ADD PRIMARY KEY(列名);
或
ALTER TABLE 表名
ADD CONSTRAINT 约束名 PRIMARY KEY(列名);

添加外键约束

ALTER TABLE 表名
ADD CONSTRAINT 约束名 FOREIGN KEY(外键列名) REFERENCES 主表名(关联列);

删除约束

删除默认约束

ALTER TABLE 表名
MODIFY 有默认值的列名 数据类型;

删除非空

ALTER TABLE 表名
MODIFY 有非空约束的列名 数据类型;

删除唯一性约束

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

删除主键

ALTER TABLE 表名
DROP PRIMARY KEY;

删除外键

ALTER TABLE 表名
DROP FOREIGN KEY 外键名;

2、事务

概念

    事务是数据库区分其他文件系统的一个特征，其保证了数据从一种状态到另一种状态的一致性。
    例如多条更新语句中一旦有一条出错，则出错上面的会被执行，而下面的不会执行。这样就出现了数据的错误。这时就需要用到事务。

特点【ACID】

⑴ A：原子性。一个事务是一个整体不能再分割
⑵ C：一致性。保证数据是准确可靠的
⑶ I：隔离性。一个事务和另外的事务之间是互相隔离的，互不干扰
⑷ D：持久性。一个事务只要执行成功了，则永久地保存到数据库中

具体操作

一般来说，需要将多条更新语句合并称为一个事务，必须有明显的开启事务和结束事务的标志。
这时就需要用：

   ⑴ SET AUTOCOMMIT = 0;
     自此句执行以后，每个SQL语句或者语句块所在的事务都需要显示"COMMIT"才能提交事务

   ⑵ STATR TRANSACTION;
     开启新事务

   ⑶ COMMIT;
      提交事务

   ⑷ ROLLBACK;
      回滚事务

示例：
SET AUTOCOMMIT = 0; – 取消默认提交
STATR TRANSACTION; – 开启新事务
UPDATE account SET balance = balance - 500 WHERE id = 1;
UPDATE account SET balance = balance + 500 WHERE id = 2;
COMMIT; – 提交事务 | ROLLBACK; – 回滚事务

3、分页查询

概念

有时我们一次无法查看所有的记录，这时就需要将所有的数据分页，每页显示固定的个数。

语法：
LIMIT 起始记录索引, 记录数

注意：
⑴ 索引从0开始
⑵ LIMIT语句可以搭配所有的查询使用，但一定要放在最后

 SELECT 查询的字段，分组函数
 FROM 表1 INNER JOIN 表2
 ON 连接条件
 WHERE 分组前条件1, 分组前条件2
 GROUP BY 分组的字段1, 分组的字段2
 HAVING 分组后条件1, 分组后条件2
 ORDER BY 排序的字段或表达式
 LIMIT 起始记录索引, 记录数

公式

  指定页数:a ，每页条目数:b
  LIMIT (a - 1) * b, b

使用示例

【查询工资大于10000的前10名的员工信息】
SELECT *
FROM employees
WHERE salary > 10000
GROUP BY salary DESC
LIMIT 0, 10;

【查询工资最高的前3名的员工信息】
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 0, 3;