MySQL05约束、事务和分页查询。

1、约束

概念

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

 常见的约束：    NOT NULL       非空约束    DEFAULT        默认约束    UNIQUE         唯一性约束    PRIMARY KEY    主键约束    FOREIGN KEY    外键约束    CHECK          检查约束【MySQL不支持】

设置自增长列

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

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

常见错误

否则报错：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值

常见错误

错误代码： 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，所以【即类似于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 主表名(关联列)

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

特点：
⑴ 在从表中设置外间关系
⑵ 插入数据时，先插入主表【因为从表需要依赖主表数据】
⑶ 清空表数据或删除表时，先清空从表数据或先删除从表【因为主表中的值被从表依赖，所以主表无法早于从表被删除】
⑷
⑸

常见错误

⑴ 从表中的外键列的数据必须来自主表的关联列的数据，如果；或从表添加数据时，，则会报错
错误代码： 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

示例：【创建一个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

检查约束【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 约束名 UNIQUE(列名1, 列名2, …)
或
UNIQUE(列名1, 列名2, …)

主键

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

创建表时添加约束

列级约束

列名 数据类型 约束

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

但是不能添加：

表级约束

列名 数据类型 [列级约束],
列名 数据类型 [列级约束],
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 起始记录索引, 记录数

 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;