事务

事务：一条或多条 SQL 语句组成的一个执行单位，这一组 SQL 语句要么执行，要么都不执行。这样一种操作称为事务

事务的特点

A：原子性，事务中的语句之间不可再分，要么都执行，要么都不执行

C：一致性，数据的一致性在事务前后不会改变，只会发生一致性状态的切换

I：隔离性，一个事务不受其它事务的干扰 (根据隔离级别而定)

D：持久性，一个事务一旦提交，则持久化到本地

事务分为隐式事务和显式事务

隐式事务没有明显的开启和结束，而显式事务相反

显式事务的使用步骤

  1. set autocommit = false;
  2. START TRANSACTION; #可省
  3. 一组SQL语句，不支持表级语句(如：drop，create，alter等)
  4. [savepoint 还原点名;]
  5. commit;
  6. [rollback; / rollback 还原点名;]

事务并发问题

多个事务同时操作同一个数据库的相同数据时，可能会导致并发问题，事务并发的常见问题如下

  • 脏读：一个事务读取了其它事务更新但还没提交的数据
  • 不可重复读：一个事务多次读取同一条数据，但是结果不一样
  • 幻读：一个事务读取了其他事务插入但还没有提交的数据

为了解决这些不同的并发问题，数据库设置了隔离级别来解决，MySQL 中的有四个隔离级别如下

  • read uncommitted：读未提交。不能解决任何并发问题
  • read committed：读已提交。只能解决脏读问题
  • repeatable read：可重复读。只能解决脏读和不可重复读问题
  • serializable：串行化 ( 序列化 )。都能解决

MySQL 默认的隔离级别是 repeatable read

视图

MySQL 5.1 出现的特性，本身是一个虚拟表，本身的数据来自于表，通过执行时其本身保存的 SQL 逻辑来动态生成

优点

  1. 简化 SQL 语句
  2. 提高了 SQL 的重用性
  3. 保护基表数据，提高安全性

创建

create view 视图名
as
查询语句;

修改

create or replace 视图名
as
查询语句;

or

alter view 视图名
as
查询语句;

删除

drop view 视图名1,视图名2 ...;

查看

desc 视图名;

or

show create view 视图名;

使用和限制

可以对视图进行，crud 等操作

insert，delete，update 都可以使用，但是大部分情况下视图依然只用于查询，因为具备以下特点的视图都不允许被更新 ( 这里的更新可能是增删改中的一个或者多个 )

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

视图和表的区别

关键字 是否占用物理空间 使用
视图 view 占用较少，只保存 SQL 逻辑 一般用于查询
表 table 保存实际的数据 crud

约束

六大常用约束

  • primary key：主键，指定字段的值唯一且非空
  • foreign key：外键，指定字段的值引用了另外的表的字段
  • unique：唯一，指定字段的值不可重复
  • not null：非空，指定字段的值不能为空
  • default：默认，指定字段的值没有指定时会有默认值
  • check：检查，MySQL 不支持

主键和唯一的区别

  1. 主键在一张表中最多只能有一个；但是唯一可以有多个
  2. 主键不允许为空；唯一可以为空

主键和唯一的相同点

  1. 都具有唯一性
  2. 都支持多字段来组合键

外键

  1. 用于限制两张表的关系，从表的字段值引用了主表的某字段值
  2. 从表的外键字段和主表的被引用字段要求类型相同或兼容
  3. 主表的被引用字段要求是一个 key (一般就是用主键)
  4. 插入数据时，先插入主表 ( 保证从表有可引用的主表字段 )；删除数据时，先删除从表 ( 保证主表数据没有被引用 )。删除表时同理

级联删除和级联置空

级联删除：添加外键约束时追加关键字 on delete cascade

级联置空：添加外键约束时追加关键字 on delete set null

约束的添加

创建表时添加

create table 表名(
    字段名 字段类型 not null,
    字段名    字段类型 default 值,
    字段名 字段类型 unique,
    字段名 字段类型 primary key, #列级约束
    [constraint 约束名] foreign key(字段名) references 主表(被引用字段) #表级约束
);

列级约束和表级约束

  • 列级约束不能使用外键；表级约束不能使用非空和默认
  • 列级约束不能起约束名；表级约束可以起约束名，但是对主键无效
  • 列级约束可以在一个字段上追加多个，相互之间使用空格隔开，无顺序要求

修改表时添加和删除

unique，not null，default

#添加
alter table 表名 modify column 字段名 字段类型 列级约束语法;
#删除
alter table 表名 modify column 字段名 字段类型;

主键

#添加
alter table 表名 add primary key(字段名);
#删除
alter table 表名 drop primary key;

外键

#添加
alter table 表名 add [constraint 约束名] foreign key(字段名) references 主表名(被引用字段);
#删除
alter table 表名 drop foreign key(约束名);

唯一

#添加
alter table 表名 add unique(字段名);
#删除
alter table 表名 drop index 约束名;

自增长列

关键字：AUTO_INCREMENT

不用手动插入值，自动提供序列之，默认初始值为 1，步长为 1

查看步长和初始值

show variables like "%auto_increment%";

如果要更改起始值：手动插入第一个数据并指定自增长列的值

如果要更改步长：

set [@@]auto_increment_increment = x;

一个表只能有一个自增长列，自增长列只能为数值型，并且自增长列必须为一个 key(一般就是主键了，但是现在自增也用的不多了，UUID，雪花算法，一堆生成主键的方法)

修改表时也可以设置自增长列 ( 不能 add 这样作为表级约束添加，只能 modify 这样作为列级约束修改 )

ALTER TABLE stuinfo MODIFY COLUMN id INT(11) AUTO_INCREMENT;

删除自增长列

ALTER TABLE stuinfo MODIFY COLUMN id INT(11);