「MySQLDay3 」约束、视图、事务

MySQLDay3 约束与视图

（1）约束

（2）视图

（3）事务

1-约束

约束内容：

（1）非空 NOT NULL （不为空）

（2）唯一 UNIQUE （不可重复）

（3）默认 DEFAULT （不指定时有值自动填充）

（4）主键 PRIMARY KEY （UNIQUE+NOT NULL）

（5）外键 FOREIGN KEY

主键与唯一：

• 一个表至多有一个主键，但可以有多个唯一；
• 主键不能为空，唯一可以为空
• 主键与唯一都具有唯一性
• 都支持组合，主键可以由两个字段共同表示

外键：

• 外键用于造就两表之间的关系

• 外键列与主表中的被引列要求数据类型一致，名称无要求

• 从表的外键一般是主表的主键

• 插入数据时，先删除外键所在的从表，再删除主键所在的主表

级联删除与级联置空

描述：一般对于有主键、外键约束的两张表，删除时应该从外键对应的表开始删除，从表删除完毕再删除主表内容，采用级联（删除、置空）的方式设置外键后，从主表删除内容后，会自动删除从表存在的对应内容

-- 级联删除做法：追加 ON DELETE CASCADE
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

级联删除会直接根据删除条件删除主从两表中的内容

-- 级联置空做法：追加 ON DELETE SET NULL
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

级联置空在删除时，对主表进行操作，会将对应主表需要删除的位置删除，从表所在位置置空而非删除

约束的创建

约束的创建分为两种：列级约束和表级约束。

约束创建的时机：创建表时创建的约束、修改表时添加、删除的约束。

1. 创建表时创建的约束

create table 表名(
	字段名 字段类型 not null,#非空
	字段名 字段类型 primary key,#主键
	字段名 字段类型 unique,#唯一
	字段名 字段类型 default 值,#默认
	constraint 约束名 foreign key(字段名) references 主表（被引用列）# 表级约束

)

	支持类型	约束名称
列级约束	unique、default、not null、primary key	无法起名
表级约束	primary key、unique、	可以起名，但对主键无效

约束的修改

1. 非空
添加非空
alter table 表 modify column 字段 字段类型 not null;

删除非空
alter table 表 modify column 字段 字段类型;

2. 默认
添加默认
alter table 表 modify column 字段 字段类型 default 值;

删除默认
alter table 表 modify column 字段 字段类型;

3. 主键
添加主键
alter table 表 add [constraint 约束名称] primary key(字段名称);

删除主键
alter table drop primary key;

4. 唯一
添加唯一
alter table 表名 add [constraint 约束名] unique(字段名称);

删除唯一
alter table 表 drop index 索引名;

5. 外键
添加外键
alter table 表 add [constraint 约束名称] foreign key(字段名称) references 主表(被引用列);

删除外键
alter table 表 drop foreign key 约束名称;

自增长列：

特点：

• 可以自动的为插入值提供序列值，默认1开始，步长为1，更改步长设置：set auto_increment_increment= x;
• 一个表最多只有一个自增列
• 自增列只能支持数值型
• 自增列必须为一个key

自增列创建
create table 表(字段名称 字段类型 约束 auto_increment);

自增列修改
alter table 表 modify column 字段名称 字段类型 约束 auto_increment;

删除自增长列
alter table 表 modify column 字段名称 字段类型 约束;

2-视图

视图特点：动态生成的虚拟表，不占用内存。

• 简化sql语句
• 提高sql的重用性
• 保护基表的数据，提高安全性

视图的创建：

create view 视图名称
as
查询语句;

视图操作

1.修改：
方式1：
create or replace view 视图名称;
as 
查询语句;

方式2：
alter view 视图名称
as
查询语句;

2.删除
drop view 视图1,视图2,...

3.查看
desc 视图名称;
show create view 视图名称;

4.使用
insert|update|delete|select 视图名称;

注意：视图最常见的应用在于查询，如果视图具备了以下特点，则不允许更新（插入、修改、删除）

• 包含分组函数、group by、distinct、having、union
• join
• 常量视图
• where后的子查询用到了from中的表
• 用到了不可更新的视图

3-事务

**定义：**多条sql语句组成的一个执行单元，这个单元要么全都执行完毕，要么全都不会执行

特点：

• 原子性：事务中的语句不可分割执行
• 一致性：事务使数据冲一个状态切换到另外一个状态
• 隔离性：需要被执行的事务不受其它事务的影响
• 持久性：一个事务一旦提交，则永久保存在本地

隐式与显示事务：隐式事务没有明显的开启与结束，自身就是一条事务自动提交（insert update delete），显示事务具有明显的开启与结束；

事务的一般流程：（开启事务、编写逻辑、结束事务）

• 开启事务

set autocommit=0;

start transaction;# 可省略

• 编写逻辑sql、设置回滚点savepoint
• 结束事务

提交：commit;

回滚：rollback;

指定回滚：rollback to 回滚点名称；

并发事务：

事务并发，多个事务对同一个数据库进行操作时出现的问题：

• 脏读：在一个事务提交之前，另一个事务读取到了这个事务还未提交的数据
• 不可重复读：一个事务对数据进行多次读取，得到的数据内容不同，与脏读的区别在于，读取了两次，这两次的中间有一次提交。
• 幻读：与脏读类似，读取了还未提交的数据，但是幻读更强调数据的插入，脏读强调数据的修改

如何解决？

通过设置隔离级别

隔离级别	脏读	不可重复读	幻读
read uncommitted	×	×	×
read committed	√	×	√
repeatable read	√	√	×
serializable	√	√	√