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;
级联置空在删除时,对主表进行操作,会将对应主表需要删除的位置删除,从表所在位置置空而非删除
约束的创建
约束的创建分为两种:列级约束和表级约束。
约束创建的时机:创建表时创建的约束、修改表时添加、删除的约束。
- 创建表时创建的约束
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 | √ | √ | √ |