东莞有哪些做推广的网站,动画设计策划案,国家企业信用信息公示系统(官网),食品包装设计ppt模板一、基本概念
1、什么是约束 约束是表级的强制规定
2、为什么使用约束 是为了保证表中数据的完整性#xff0c;完整性又可以拆分为精确性和可靠性
3、怎么去保证数据完整性呢#xff0c;从以下四个角度进行考虑
实体完整性#xff1a;一张表中#xff0c;不能存在两条完…一、基本概念
1、什么是约束 约束是表级的强制规定
2、为什么使用约束 是为了保证表中数据的完整性完整性又可以拆分为精确性和可靠性
3、怎么去保证数据完整性呢从以下四个角度进行考虑
实体完整性一张表中不能存在两条完全相同无法区分的记录域完整性限制某一个字段例如性别只能是男/女但是他不会约束年龄引用完整性例如员工所在的部门要在部门表中能找到这个部门的信息用户自定义完整性例如用户名唯一、密码不能为空等
4、约束的分类
从约束字段的个数来看分为单列约束、多列约束从约束的作用范围来看分为列级约束声明在对应字段的后面、表级约束表中所有字段都声明完后在所有字段后单独声明的约束从约束的功能上来看 not null非空约束unique唯一性约束primary key主键约束foreign key外键约束check检查约束default默认值约束
5、如何添加约束
在CREATE TABLE时添加约束使用ALTER TABLE添加约束也可以使用这个删除约束
6、如何查看约束 需要到information_schema这个库下查询TABLE_CONSTRAINTS这个表 例如我需要查询t_decade_test表中存在哪些约束就可以使用
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name t_decade_test;二、约束类型介绍
1、not null非空约束
作用限制某个字段/某列的值不允许为空关键字NOT NULL注意 空字符串不等于null0也不等于null如果没有使用not null约束那么默认情况下所有的类型都是可以为null的非空约束只能设置成列级约束
1如何在建表时创建非空约束
CREATE TABLE t_decade_test_not_null(
id INT NOT NULL,
last_name VARVHAR(20) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)
);2创建完这个表后如下几种情况会报错
使用insert插入数据id或者last_name的值为null使用insert插入数据没有给id或者last_name字段赋值且没有指定它的默认值只用update更新数据给id或者last_name字段传递的值为null
3如何使用ALTER TABLE添加约束我们还是以之前的t_decade_test_not_null为例 对email字段添加一个非空约束但是如果email字段已经存在null值那么下方的语句是无法执行的
ALTER TABLE t_decade_test_not_null
MODIFY email VARCHAR(25) NOT NULL;4如何使用ALTER TABLE删除约束呢其实只要把NOT NULL改成NULL即可
ALTER TABLE t_decade_test_not_null
MODIFY email VARCHAR(25) NULL;2、unique唯一性约束
作用确保某一列/某一个字段的值不会重复关键字UNIQUE注意点 MySQL会给唯一约束的列上默认建一个唯一索引一个表中可以设置多个唯一性约束唯一性约束可以针对某一个字段也可以针对某几个字段组合起来的值唯一性约束允许字段为空。如果两条数据该字段都赋值null不会受到唯一性约束的限制创建唯一性约束的时候如果不给约束命名那就默认唯一性约束的名字和字段名相同
1如何在建表时给字段添加唯一性约束
CREATE TABLE t_decade_test_unique(
id INT UNIQUE #这就是列级约束,
last_name VARVHAR(20),
email VARCHAR(25),
salary DECIMAL(10,2),
# 添加表级约束一般约束的命名格式为作用_表名_字段
CONSTRAINT uk_t_decade_test_unique_email UNIQUE(email)
);2如何使用ALTER TABLE来添加约束 方式一alter table 表名 add UNIQUE(字段名称)
alter table t_decade_test_unique add UNIQUE(lastname);# 如果要给约束取名还是使用constraint
alter table t_decade_test_unique add constraint uk_t_decade_test_unique_lastname UNIQUE(lastname,email);方式二alter table 表名 modify 字段名 字段类型 UNIQUE
alter table t_decade_test_unique MODIFY last_name VARVHAR(20) UNIQUE;3什么是复合的唯一性约束 可以理解为我们对多个字段进行组合 要求两条数据中这几个字段的值不能完全相同必须有一个或者几个字段的值不一样
CREATE TABLE t_unique_user(
id INT,
user_name VARCHAR(20),
user_pwd VARCHAR(15),
constraint uk_t_unique_user_nameAndPwd UNIQUE(user_name,user_pwd);
);这样一来这个表中的任意两条数据name和pwd字段就不能完全相同
4删除唯一性约束
创建唯一性约束的字段会自动创建一个唯一索引所以我们只能通过删除唯一索引的方式来删除该约束而唯一索引的名和唯一性约束的名称是一样的如果是单列约束那默认就和字段名称相同如果是复合约束那默认就和()中排在第一位的字段名相同。当然我们也可以使用constraint来自定义约束名
alter table t_unique_user
DROP index 索引名;我们也可以通过show index from 表名;查看表的索引
3、primary key主键约束
作用用来标识表中的一行记录也就是说可以通过这个来确定某条数据是独一无二的关键字primary key特点主键约束相当于唯一性约束非空约束注意点 一个表中只能有一个主键约束它可以在列级别创建也可以在表级别创建主键约束对应着表中的一列或者多列复合主键如果是多列组合的复合主键约束那么任何一列都不允许为空而且组合的值不允许重复主键约束的名总是PRIMARY它不能像前面的唯一性约束一样自定义名称创建主键约束后系统会自动在对应的列或者组合字段上创建主键索引根据主键进行查询的效率更高如果删除了主键约束对应的主键索引也会被删除不要修改主键字段的值因为他是数据记录的唯一标识如果修改了主键的值就有可能破坏数据的完整性
1创建表时如何添加主键约束 列级约束
CREATE TABLE t_test_primary_key(
id INT PRIMARY KEY,
last_name VARCHAR(20),
salary DECIMAL(10,2),
email VARCHAR(15)
);或者表级约束
CREATE TABLE t_test_primary_key(
id INT,
last_name VARCHAR(20),
salary DECIMAL(10,2),
email VARCHAR(15),
constraint prk_t_test_primary_key_id PRIMARY KEY(id) #这里的名称是无效的
);或者复合主键
CREATE TABLE t_test_table(
id INT,
user_name VARCHAR(20),
user_pwd VARCHAR(15),
PRIMARY KEY(user_name,user_pwd)
);2使用ALTER TABLE创建主键约束
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);3删除主键约束
ALTER TABLE table_name
DROP PRIMARY KEY;4、auto_increment自增列
作用让某个字段的值自增特点 一个表只能有一个自增字段当需要产生唯一标识符或者顺序值的时候可以设置自增长自增长约束的列必须是键列必须要有主键约束或者唯一性约束而且自增长约束的列必须是整数类型在没有给第一条数据的该字段显式赋值情况下默认从1开始如果我们在插入某条数据时自增列对应的value填的是0或者null实际上入表的值会是该字段当前最大值加1并不会展示为0或者null
1在建表时声明自增列
CREATE TABLE t_auto_increment(
id INT PRIMARY KEY AUTO_INCREMENT
last_name VARCHAR(20)
);2使用alter table声明自增列
ALTER TABLE t_auto_increment
MODIFY id INT AUTO_INCREMENT;3删除自增
ALTER TABLE t_auto_increment
MODIFY id INT;4MySQL5.7与MySQL8.0中计数器的差别 假设现在有一个场景我们连续执行三次insert语句id字段value都是0 使用select语句后我们可以发现三条数据的id依次为123
现在我们同时在两个版本的MySQL中删除id为3的数据 然后重复执行insert id为0这个操作 然后可以发现两个版本的MySQL中该表中的第三条数据并不是从3开始了而是从4开始
然后我们删除id为4的这条数据再对两个版本的MySQL服务都进行重启操作继续重新insert id0这个操作我们可以发现
MySQL5.7中该表第三条数据的id重新从3开始MySQL8.0中该表第三条数据的id从5开始
MySQL5.7中自增主键的计数器是在内存中维护的数据库重启后计数器会被初始化 MySQL8.0中自增主键的计数器会被持久化到重做日志中每次计数器发生改变都会记录到该日志中当数据库重启InnoDB会根据重做日志来初始化计数器的内存值
5、FOREIGN KEY外键约束
作用限制某个表的某个字段的引用完整性比如说员工表中某个员工的部门id必须要在部门表中存在关键字FOREIGN KEY了解什么是主表/从表或者说什么是父表/子表 主表父表被引用的表比如上面的部门表从表子表引用别人的表比如上面的员工表 特点 从表的外键引用/参考的必须是主表的主键列或者是唯一约束的那个字段这是因为被依赖/被参考的值必须是唯一的创建外键约束时如果没有自定义名称那么会自动生成一个外键名不会像之前介绍的约束一样和列名保持一致了如果在创建表的时候就指定外键约束那么必须先创建主表再创建从表删表时先删从表或者先删外键约束再删主表当主表的数据被从表参照时主表的数据将不被允许删除如果要删除数据需要先去从表中删除依赖该记录的数据然后再删除主表数据在从表中指定外键约束一个表可以建立多个外键约束从表的外键列和主表中被引用的列不一定要名称相同但是数据类型必须保持一致创建外键约束时系统会默认在所在的列上建立普通索引索引名列名列名≠外键约束名删除外键约束后必须手动删除对应的索引
1在create table时指定外键约束
CREATE TABLE t_dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);CREATE TABLE t_emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
department_id INT,
# 使用表级约束的方式指定外键约束
constraint fk_emp_department_id FOREIGN KEY (department_id) REFERENCES t_dept (dept_id)
);2演示外键效果 在创建了t_dept和t_emp表之后 如果两张表都是空表当我们直接执行insert into t_emp values(1,decade,10); SQL执行会报错因为我们从表t_emp依赖的主表t_dept中并没有id为10的记录
所以我们要先执行 insert into t_dept values(10,技术部); 再执行insert into t_emp values(1,decade,10);
执行上述插入语句之后 假设我们执行delete from t_dept where id 10;语句还是会报错因为这条数据被从表依赖 同样的当我们执行update t_dept set id 20 where dept_name 技术部;时语句也会报错
3建表后如何使用alter table添加外键约束
CREATE TABLE t_dept2(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);CREATE TABLE t_emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
department_id INT
);ALTER TABLE t_emp2
ADD CONSTRAINT fk_emp2_department_id FOREIGN KEY (department_id) REFERENCES t_dept2 (dept_id);4外键约束的约束等级
Cascade方式当父表的数据发生update/delete时同步update/delete子表中引用此记录的数据Set null方式当父表的数据发生update/delete时将子表中引用此记录的外键列设置为null注意子表的外键列不允许有非空约束No action方式如果子表中有引用父表记录的数据那么不允许对父表对应候选键进行update/delete操作Restrict方式同No action方式Set default方式父表有变更时将子表的外键列设置成一个默认值但InnoDB不能识别
对于外键约束最好使用ON UPDATE CASCADE ON DELETE RESTRICT的方式 即更新会同步但是不允许删除使用方式如下
CREATE TABLE t_dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);CREATE TABLE t_emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
department_id INT,
# 使用表级约束的方式指定外键约束
constraint fk_emp_department_id FOREIGN KEY (department_id) REFERENCES t_dept (dept_id) ON UPDATE CASCADE ON DELETE RESTRICT
);5删除外键约束
# 第一步先查看表中的约束名删除约束
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name 表名称;ALTER TABLE 从表名
DROP FOREIGN KEY 外键约束名称;# 第二步查看索引名和删除索引
SHOW INDEX FROM 表名称;ALTER TABLE 从表名 DROP INDEX 索引名;6开发场景 阿里开发规范要求不得使用外键与级联可以理解为主表数据更新触发从表相应记录更新外键与级联适合单机低并发场景不适合分布式、高并发场景级联可能导致数据库更新风暴外键影响数据库插入速度所以一切外键概念都应该在应用层即Java代码层面进行解决经典白学~
6、check检查约束
作用检查某个字段的值是否复合要求关键字CHECK说明MySQL5.7不支持可以使用但对数据验证没有任何作用就算你添加了不符合要求的数据也不会触发告警MySQL8.0支持
1建表时添加
CREATE TABLE t_emp(
id iNT PRIMARY KEY AUTO_INCREMENT
last_name VARCHAR(20),
salary DECIMAL(10,2) CHECK(salary5000)
);如果你执行insert into t_emp values(1, 彭于晏, 1500);语句就会报错
7、default默认值约束
作用给某个字段设置默认值如果在插入数据时没有给该字段显式赋值那么就赋值为默认值关键字DEFAULT
1在创建表时添加约束
CREATE TABLE t_emp(
id iNT PRIMARY KEY AUTO_INCREMENT
last_name VARCHAR(20),
salary DECIMAL(10,2) DEFAULT 3000
);如果你执行insert into t_emp values(1, 彭于晏);那么此条数据对应salary那一列的值就是3000
2使用ALTER TABLE添加约束
CREATE TABLE t_emp(
id iNT PRIMARY KEY AUTO_INCREMENT
last_name VARCHAR(20),
salary DECIMAL(10,2)
);ALTER TABLE t_emp
MODIFY salary DECIMAL(10,2) DEFAULT 3000;3删除约束
ALTER TABLE t_emp
MODIFY salary DECIMAL(10,2);三、写在最后
很长时间没有更新博客了最近也不知道在瞎忙什么后面会慢慢恢复之前的学习状态人还是要不断学习才不会被时代抛弃希望自己这次坚持的久一点hh~
如有错误欢迎指正
