完整性约束
完整性约束：用于保证数据的完整性和一致性
为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，
DBMS自动按照一定的约束条件对数据进行监测，
使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
约束可以在创建表时指定 ( 通过 CREATE TABLE 语句 )，或者在表创建之后设置 ( 通过 ALTER TABLE 语句 )
SQL CREATE TABLE + CONSTRAINT 语法
CREATE TABLE table_name (
column_name1 data_type ( size ) constraint_name ,
column_name2 data_type ( size ) constraint_name ,
column_name3 data_type ( size ) constraint_name ,
....
);
null和not null
# 1. null、not null
null，1. 表示可以为空（默认不用传值，使用default的值，default默认值为null）
2. 表示可以为空值（也就是传null值，并非空字符串）
not null，1. 表示不能为空（也就是必须传值，当然也可以使用default的值，
但是default的值不能是null值）
2. 表示不能为空值（也就是不能传null值）
# =========================================================
CREATE TABLE 时的 SQL NOT NULL 约束：
在创建表结构时，可以给字段添加 NOT NULL 关键字来添加 NOT NULL 约束
CREATE TABLE lesson (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(32) default '',
views int(11) NOT NULL default '0',
created_at DATETIME
);
ALTER TABLE 时的 SQL not null 约束：
如果表已经被创建，而又想添加 NOT NULL 约束，可以使用 ALTER TABLE 命令
ALTER TABLE lesson MODIFY COLUMN views int(11) NOT NULL default '0';
删除 NOT NULL 约束
如果想要删除 NOT NULL 约束，可以使用 ALTER TABLE 命令，
也就是不指定 NOT NULL 关键字即可。
ALTER TABLE lesson MODIFY COLUMN views int(11) default '0';
# ===========================================================
default
# ===============================================================
default，
默认值，创建列时可以指定默认值，缺省的默认值是NULL，
当插入数据时如果未主动设置，则自动添加默认值
注意：当设置为not null时，default不能为null
DEFAULT 约束中指定的默认值可以是文字常量或表达式。
如果使用表达式作为默认值，则需要表达式默认值括在括号内 () ，以将它们与文字常量默认值区分开来。
例如:
CREATE TABLE t1 (
-- 常量默认值
i INT DEFAULT 0,
c VARCHAR(10) DEFAULT '',
-- 表达式默认值
f FLOAT DEFAULT (RAND() * RAND()),
b BINARY(16) DEFAULT (UUID_TO_BIN(UUID())),
d DATE DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR),
p POINT DEFAULT (Point(0,0)),
j JSON DEFAULT (JSON_ARRAY())
);
但是，这有一个例外。这个例外就是： TIMESTAMP 和 DATETIME 列。
对于 TIMESTAMP 和 DATETIME 列，我们可以将 CURRENT_TIMESTAMP 函数指定为默认值，而需要添加括号。
表达式默认值必须遵守以下规则。如果表达式包含不允许的构造，则会发生错误
1. 允许使用文字，内置函数（确定性和非确定性）和运算符
2. 不允许使用子查询，参数，变量，存储函数和用户定义的函数
3. 表达式默认值不能依赖于具有 AUTO_INCREMENT 属性的列。
4. 某一列的表达式默认值可以引用另外一张表中的列，但是对生成的列或具有表达式默认值的列的引用必须是对于在表定义中较早出现的列。
也就是说，表达式默认值不能包含对生成的列或具有表达式默认值的列的前向引用。翻译成白话文就是，引用的列必须已经存在。
5. 排序 ( ordering ) 约束也适用于使用 ALTER TABLE 重新排序表列。
如果结果表的表达式默认值包含对具有表达式默认值的生成列或列的前向引用，则该语句将失败
对于语句 CREATE TABLE ... LIKE 和 CREATE TABLE ... SELECT ，目标表保留原始表中的表达式默认值。
插入新行时，可以通过省略列名或将列指定为 DEFAULT 来插入具有表达式 default 的列的默认值（就像具有文字默认值的列一样)
mysql> CREATE TABLE t4 (uid BINARY(16) DEFAULT (UUID_TO_BIN(UUID())));
mysql> INSERT INTO t4 () VALUES();
mysql> INSERT INTO t4 () VALUES(DEFAULT);
mysql> SELECT BIN_TO_UUID(uid) AS uid FROM t4;
+--------------------------------------+
| uid |
+--------------------------------------+
| f1109174-94c9-11e8-971d-3bf1095aa633 |
| f110cf9a-94c9-11e8-971d-3bf1095aa633 |
+--------------------------------------+
# ================================================================
创建表时指定 DEFAULT 约束:
CREATE TABLE `lesson` (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(32) default '',
views int(11) NOT NULL default '0',
created_at DATETIME
);
ALTER TABLE 时的 SQL DEFAULT 约束:
当然可以使用modify来修改, 只不过用modify比较麻烦
ALTER TABLE lesson ALTER [column] views SET DEFAULT '1';
删除 DEFAULT 约束:
ALTER TABLE lesson ALTER views DROP DEFAULT;
# ================================================================
unqiue key
PRIMARY KEY 约束会自动定义一个 UNIQUE 约束，
或者说 PRIMARY KEY 是一种特殊的 UNIQUE 约束。
但二者是有明显区别的：
1. 每个表可以有多个 UNIQUE 约束，但只能有一个 PRIMARY KEY 约束
2. unique可以为空，primary不能为空
# =============================================================
CREATE TABLE 时的 SQL UNIQUE 约束 ：
在创建表结构时，可以使用 UNIQUE 关键字给表添加 UNIQUE 约束
CREATE TABLE lesson (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(32) default '',
views int(11) NOT NULL default '0',
created_at DATETIME,
UNIQUE [KEY](name)
);
or
create table t1(id int unique [key], depart char(5) unique [key]);
UNI # 默认的名字就是字段的名字
如果想要多加多列，可以在括号内添加列，并使用逗号 (,) 分隔
CREATE TABLE lesson (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(32) default '',
views int(11) NOT NULL default '0',
created_at DATETIME,
unique (name,id)
);
MUL # 多列的默认名字，貌似是左边第一个字段，或者通过show index from lesson \G查看
如果还想给 UNIQUE 约束命名
1. create table t1(
id int, depart char(5),
unique uq_id (id),
unique uq_depart (depart)
);
2. create table t1(
id int,
depart char(5),
-- constraint uq_name unique(id, depart)
constraint unique uq_name (id, depart)
);
3. create table t1(
id int,
depart char(5),
unique uq_name (id, depart)
); MUL
ALTER TABLE 时的 SQL UNIQUE 约束:
如果表已经被创建，而又想添加 UNIQUE 约束，可以使用 ALTER TABLE ADD 命令
ALTER TABLE lesson ADD UNIQUE [KEY](name);
alter table lesson modify name char unique key;
当然了，我们的 UNIQUE 可以包含多列，添加方法就和建表时添加多列是同样的
ALTER TABLE lesson ADD UNIQUE (id,name);
如果还想给 UNIQUE 约束命名，可以使用 CONSTRAINT 关键字
alter table lesson add unique names (name);
ALTER TABLE lesson ADD [CONSTRAINT [uniq_lesson_name]] UNIQUE (id,name);
删除 UNIQUE 约束:
如果想要删除 UNIQUE 约束，可以使用 ALTER TABLE DROP 命令
ALTER TABLE lesson DROP {INDEX | key} uniq_lesson_name;
# =============================================================
# 跟primary key 的区别就是unique 可以为空
# not null + unique的化学反应
create table t3(id int not null unique, name char(5));
结果id为PRI，也就是主键
# =============================================================
primary key
primary key = not null + unique
主键primary key是innodb存储引擎组织数据的依据，
innodb称之为索引组织表，一张表中必须有且只有一个主键。
如果创建表的时候没有明确指定primary key，那么innodb会搜索一个not null + unique的字段作为主键；
如果没有符合条件的，则innodb会自动创建一个6 字节的 ROWID隐藏的字段作为主键，当然对我们没有什么意义。
1. 主键必须包含唯一的值
2. 主键列不能包含 NULL 值
3. 每个表都应该有一个主键，并且每个表只能有一个主键
# ================================================================================
CREATE TABLE 时的 SQL PRIMARY KEY 约束：
创建表时可以使用 PRIMARY KEY 关键字给表添加 PRIMARY KEY 约束，
但要注意，添加的列必须设置为 NOT NULL;（v8版本，其实不用设置not null，因为primary key = not null + unique）
CREATE TABLE lesson (
id int(11) NOT NULL [PRIMARY] KEY AUTO_INCREMENT, -- primary可以不用写
name varchar(32) default '',
views int(11) NOT NULL default '0',
created_at DATETIME
);
or 特别注意：primary key的key关键字，不能少；这也是跟unique不同的地方
create table pri_tb (id int, primary key(id));
虽然一个表只能有一个 PRIMARY KEY，但一个 PRIMARY KEY 可以包含多个列，
添加多个列可以使用 PRIMARY KEY，关键字，括号内添加多个列，多列之间用逗号分隔。
CREATE TABLE lesson (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(32) default '',
views int(11) NOT NULL default '0',
created_at DATETIME,
PRIMARY KEY (id,name)
);
如果需要给 PRIMARY KEY 约束命名，可以使用 CONSTRAINT 关键字：
1. create table t1(id int, name char(5), primary key pk_name (id));
2. CREATE TABLE lesson (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(32) default '',
views int(11) NOT NULL default '0',
created_at DATETIME,
[CONSTRAINT [pk_lesson_id]] PRIMARY KEY (id,name)
);
ALTER TABLE 时的 SQL PRIMARY KEY 约束:
如果一个表已经创建，而又想给表添加 PRIMARY KEY 约束，可以使用 ALTER TABLE 命令
alter table t1 modify id int primary key;
ALTER TABLE lesson ADD PRIMARY KEY (id);
ALTER TABLE lesson ADD CONSTRAINT pk_lesson PRIMARY KEY (id,name);
给 PRIMARY KEY 约束命名：
如果想要给 PRIMARY KEY 约束命名，可以使用 ALTER TABLE CONSTRAINT 命令
ALTER TABLE lesson ADD CONSTRAINT pk_lesson_id PRIMARY KEY (id);
删除 PRIMARY KEY 约束：
ALTER TABLE lesson DROP PRIMARY KEY
# ==================================================================================
auto_increment
# 前提：要想为该字段设置自增，就必须先设置一个键, 并且default不能和auto_increment同时出现
# 建议：建立一张表设置一个id字段并且为自增
create table t1(id int primary key auto_increment);
# 注意:
1. 将 0 存储到 AUTO_INCREMENT 列与存储 NULL 具有相同的效果，
除非启用了 NO_AUTO_VALUE_ON_ZERO SQL 模式。
2. AUTO_INCREMENT 序列以 1 开始而不是 0
3. 在 MySQL 8.0 及以上的版本， AUTO_INCREMENT 列会自动添加一个隐式的 UNSIGNED 属性来保证插入的值非负。
4. 整数或浮点数据类型可以添加附加属性 AUTO_INCREMENT。
如果某个列添加了 AUTO_INCREMENT 属性，那么在插入数据的时候，
如果不指定该列或者指定该列的值为 NULL or 0，
那么 MySQL 会自动将该列的值设置为下一个序列值
5. 在有自增和主键的情况下，想删除主键，得先删除自增再删除主键
删除自增：alter table tb4 modify id int not null;
# 更改表的自增值：
# alter table t11 auto_increment=10;
# 创建表的时指定：
# create table t11(id int auto_increment, primary key(id) auto_increment=10;
# 查看自增列的步长和起始值:
show variables like 'auto_inc%' 默认看的是会话级别的
# 设置自增列的步长和起始值：
# 1. 基于会话级别：
# MySQL是基于会话的，每一次登录就是一个会话，自增值也不一样，本次操作只针对当前会话生效
# show session variables like 'auto_inc%';
# set session auto_increment_increment=10; # 步长
# set session auto_increment_offset=2; # 起始值/起始偏移量
# PS：起始值 <= 步长
# 2. 基于全局级别：（需要重新登录）
# show global variables like 'auto_inc%';
# set global auto_increment_increment=5;
# set global auto_increment_offset=3;
# 最好不要设置全局的自增
##########################################################
小坑：如果有自增列，insert插入数据的时候，values(xx),(xx),(xx)，前两个都符合规则，最后一个不符合，
# 虽然数据不会插入成功，但是自增值已经改变，此时再插入数据values(xx)，自增值变成了 4
################################################################
foreign key
# 就是在一张表中存储另一张表的唯一字段的值
# 前提：存储引擎为innodb，且被关联表的那个字段必须唯一（unique / primary key)
# 建议：在实际开发中，尽量不要在数据库中的表与表之间建议硬性的关系，造成了强耦合的问题，不方便日后的扩展
# 应在自己的应用程序级别建立逻辑上的联系, 所以开发团队要有共识。
# 作用:
# 1. FOREIGN KEY 约束用于预防破坏表之间连接的行为
# 2. FOREIGN KEY 约束也能防止非法数据插入外键列，因为插入的数据必须是外键所指向的那个表字段中的值之一
# 两个作用简单明了
1. 当删除一个 FOREIGN KEY 指向的主表 (lesson) 记录时，如果 FOREIGN KEY 所在的表 (lesson_views) 存在记录，那么会删除失败
2. 当在 FOREIGN KEY 表 ( lesson_views ) 插入或更新一条记录，如果 FOREIGN KEY 指向的主表 ( lesson ) 不存在该记录，那么插入或者更新失败
# ================================================================================
CREATE TABLE 时的 SQL FOREIGN KEY 约束:
给一个表添加 FOREIGN KEY 约束可以使用 FOREIGN KEY 关键字
CREATE TABLE lesson_views (
uniq bigint(20) primary key NOT NULL default '0' ,
lesson_name varchar(32) default '',
lesson_id int(11) default '0',
date_at int(11) NOT NULL default '0',
views int(11) NOT NULL default '0',
FOREIGN KEY (lesson_id) REFERENCES lesson(id)
);
给 FOREIGN KEY 命名
如果想要给 FOREIGN KEY 约束命名，可以使用 CONSTRAINT 关键字
create table fk_tb(
fid int auto_increment not null primary key,
name char (5),
main_id int,
foreign key fk_name (main_id) references main_tb(id)
);
注意: 外键的类型 必须 跟关联的表的字段类型要一致, 最好后面的宽度也一样.
CREATE TABLE lesson_views (
uniq bigint(20) primary key NOT NULL default '0' ,
lesson_name varchar(32) default '',
lesson_id int(11) default '0',
date_at int(11) NOT NULL default '0',
views int(11) NOT NULL default '0',
CONSTRAINT fk_lesson_id FOREIGN KEY (lesson_id) REFERENCES lesson(id)
);
ALTER TABLE 时的 SQL FOREIGN KEY 约束:
如果一个表已经被创建，我们仍然可以使用 ALTER TABLE FOREIGN KEY 来添加外键约束
ALTER TABLE lesson_views ADD FOREIGN KEY (lesson_id) REFERENCES lesson(id);
如果还想给 FOREIGN KEY 约束命名，则可以像下面这样使用
ALTER TABLE lesson_views ADD CONSTRAINT fk_lesson_id FOREIGN KEY (lesson_id) REFERENCES lesson(id);
# 默认的名字: 表名 + _ + ibfk_1
# 表名, 小写
# ib, innodb
# fk, foreign key
# _1, 表中的第一个外键
删除 FOREIGN KEY 约束:
如果想要删除一个已经命名的 FOREIGN KEY 约束，可以使用 DROP 关键字
ALTER TABLE lesson_views DROP FOREIGN KEY fk_lesson_id;
# ================================================================================
# 创建步骤：
# 1. 首先要创建被关联的表：
create table department(
id int auto_increment primary key,
name char(10),
comment char(50)
) engine = innodb auto_increment = 1 default charset = utf8;
# 2. 再创建主动关联的表：
create table employee(
id int auto_increment,
name char(5),
dep_id int,
primary key(id),
constraint fk_name foreign key(dep_id) references department(id)
);
# 插入数据步骤：
# 1. 首先往被关联的表插入数据
insert into department(name, comment) values('技术', '技术能力有限部门'),
('销售', '销售能力不足部门'),
('财务', '花钱特别多部门');
# 2. 再往主动关联的表插入数据
insert into employee(name, dep_id) values('alex', 1),
('egon', 1),
('taiba', 1),
('peiqi', 2),
('eva-j', 3);
# 删除步骤：（不作特殊处理）（说明：这是在fk所在的表存在该记录时，才要先删主动关联表，再删被关联表）
# 1. 首先删除主动关联的表的数据
delete from employee where id = 1; (id为1的都要删干净)
# 2. 再删除被关联表的数据
delete from department where id = 1;
# 如果说我想更改被关联表的数据，同时，主动关联的表的数据也随之发生变化：
# （更新的时候，同步更新；删除的时候，同步删除）
# 创建主动关联表的时候需要加额外的参数：
create table employee(
id int auto_increment,
name char(10),
dep_id int,
primary key(id),
constraint fk_name foreign key(dep_id) references department(id)
on delete cascade
on update cascade
);
# 例如下拉框中的信息，就可以使用外键
# 可以将多个列组合成一个外键，前提是此表中要设置自增还有主键(在v8中, 我试了一下, 并不用)，
并且与另一个表的类型要一致，要跟另一个表的主键挂钩
# 除此之外另一个表的主键是多列组合的,
# 外键的名字要唯一，不能与同一个数据库中的其他表的外键名字重复
# ========================================================================================
check
# CHECK 约束用于限制列中的值的范围
CHECK 约束既可以用于某一列也可以用于某张表：
1. 如果对单个列定义 CHECK 约束，那么该列只允许特定的值
2. 如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制
# ===========================================================================================
CREATE TABLE 添加 CHECK 约束:
创建表结构时可以使用 CHECK 关键字给表或者字段添加 CHECK 约束
例如我们在创建 lesson 表时可以给 id 字段加上一个大于 0 的 约束
CREATE TABLE lesson (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(32) default '',
views int(11) NOT NULL default '0',
created_at DATETIME,
CHECK ( id>0 )
);
多个字段添加约束:
如果想给一个表中多个字段添加约束，直接在 CHECK 关键字后的括号内添加，每个约束使用 AND 连接
CREATE TABLE lesson (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(32) default '',
views int(11) NOT NULL default '0',
created_at DATETIME,
CHECK ( id>0 AND views >= 0 );
);
给 CHECK 约束命名:
CREATE TABLE lesson (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(32) default '',
views int(11) NOT NULL default '0',
created_at DATETIME,
CONSTRAINT chk_lesson_id CHECK ( id>0 )
);
默认的名字: 表名 + _ + chk_1
ALTER TABLE 时的 SQL CHECK 约束:
如果表已经被创建，我们可以使用 ALTER TABLE ADD CHECK 添加约束
ALTER TABLE lesson ADD CHECK (id>0);
并不能alter table lesson add check 名字 (id > 0);
如果还想要命名 CHECK 约束，并定义多个列的 CHECK 约束
ALTER TABLE lesson ADD CONSTRAINT chk_lesson CHECK (id>0 AND views >= 0);
删除 CHECK 约束:
ALTER TABLE lesson DROP CHECK chk_lesson_id
最佳实战:
虽然各个数据库系统都可以添加 CHECK 约束，但不推荐这么做，因为这会影响数据插入和更新的速度
# ============================================================================================
179
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
