MySQL学习笔记汇总（四）——表的约束、存储引擎、事务

冷不防 2023-10-05 08:04 106阅读 0赞

## 一、表的约束 ##

*  **在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。**
 *  常见的约束有哪些：

> **非空约束(not null)：约束的字段不能为NULL  
> 唯一约束(unique)：约束的字段不能重复  
> 主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）  
> 外键约束(foreign key)：…（简称FK）  
> 检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。**

### 非空约束：not null ###

**给字段添加非空约束：**

drop table if exists t_user;
    	create table t_user(
    		id int,
    		username varchar(255) not null,
    		password varchar(255)
    	);
    	insert into t_user(id,password) values(1,'123');
    	ERROR 1364 (HY000): Field 'username' doesn't have a default value
    	insert into t_user(id,username,password) values(1,'lisi','123');

insert into t_user(id,password) values(1,'123');
    此语句出现了错误，因为在创建表时给username字段添加了非空约束，所以此字段值不能为空！！

not null约束只有列级约束。没有表级约束。

### 唯一性约束：unique ###

**给某一列添加unique**

drop table if exists t_user;
    		create table t_user(
    			id int,
    			username varchar(255) unique  // 列级约束
    		);
    		insert into t_user values(1,'zhangsan');
    		insert into t_user values(2,'zhangsan');
    		ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'
    
    		insert into t_user(id) values(2);
    		insert into t_user(id) values(3);
    		insert into t_user(id) values(4);

insert into t_user values(1,'zhangsan');
    insert into t_user values(2,'zhangsan');
    这两条语句出现了错误，因为在建表时给username这个字段添加了唯一性约束，此字段值不能重复，“zhangsan”值重复！

**给两个列或者多个列添加unique**

drop table if exists t_user;
    		create table t_user(
    			id int, 
    			usercode varchar(255),
    			username varchar(255),
    			unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】
    		);
    		insert into t_user values(1,'111','zs');
    		insert into t_user values(2,'111','ls');
    		insert into t_user values(3,'222','zs');
    		
    		insert into t_user values(4,'111','zs');
    		ERROR 1062 (23000): Duplicate entry '111-zs' for key 'usercode'

insert into t_user values(4,'111','zs');
    此语句出现错误，在建表时给usercode和username这两个字段联合添加了唯一约束，表示这两个字段联合起来不能重复，usercode，username
    与	insert into t_user values(1,'111','zs');这个语句的usercode,username,重复！

### 主键约束：primary key ###

**添加主键约束：**

drop table if exists t_user;
    		create table t_user(
    			id int primary key,  // 列级约束
    			username varchar(255),
    			email varchar(255)
    		);
    		insert into t_user(id,username,email) values(1,'zs','zs@123.com');
    		insert into t_user(id,username,email) values(2,'ls','ls@123.com');
    		insert into t_user(id,username,email) values(3,'ww','ww@123.com');

insert into t_user(id,username,email) values(1,'jack','jack@123.com');
    		ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
    
    		insert into t_user(username,email) values('jack','jack@123.com');
    		ERROR 1364 (HY000): Field 'id' doesn't have a default value
    		id是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复

*  主键相关的术语

> 主键约束 : primary key  
> 主键字段 : id字段添加primary key之后，id叫做主键字段  
> 主键值 : id字段中的每一个值都是主键值。

*  主键作用：**主键值是这行记录在这张表当中的唯一标识。**
 *  主键的分类

> 根据主键字段的字段数量来划分： 单一主键 复合主键  
> 根据主键性质来划分：自然主键 业务主键

一张表的主键约束只能有1个

**使用表级约束方式定义主键：**

drop table if exists t_user;
    		create table t_user(
    			id int,
    			username varchar(255),
    			primary key(id)
    		);

mysql提供主键值自增：

drop table if exists t_user;
    		create table t_user(
    			id int primary key *auto_increment*, // id字段自动维护一个自增的数字，从1开始，以1递增。
    			username varchar(255)
    		);

### 外键约束：foreign key ###

**外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键 字段，那么该字段的值必须来源于参照的表的主键**

*  **外键可以为NULL**
 *  **被外键引用的字段不一定是主键，但至少具有unique约束。**

建立学生和班级表之间的连接  
![在这里插入图片描述][20200811112312471.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70]

t\_student中的classno字段引用t\_class表中的cno字段，此时t\_student表叫做子表。t\_class表叫做父表。

顺序要求：

> **删除数据的时候，先删除子表，再删除父表。  
> 添加数据的时候，先添加父表，在添加子表。  
> 创建表的时候，先创建父表，再创建子表。  
> 删除表的时候，先删除子表，在删除父表。**

父表：

create table t_class(
    			cno int,
    			cname varchar(255),
    			primary key(cno)
    		);

子表：

create table t_student(
    			sno int,
    			sname varchar(255),
    			classno int,
    			primary key(sno),
    			foreign key(classno) references t_class(cno)
    		);

给父表加数据：

insert into t_class values(101,'xxx');
    		insert into t_class values(102,'yyy');

给子表加数据：

insert into t_student values(1,'zs1',101);
    		insert into t_student values(2,'zs2',101);
    		insert into t_student values(3,'zs3',102);
    		insert into t_student values(4,'zs4',102);
    		insert into t_student values(5,'zs5',102);
    		insert into t_student values(6,'zs6',102);

## 二、存储引擎 ##

*  数据库中的各表均被（在创建表时）指定的存储引擎来处理。
 *  为了解当前服务器中有哪些存储引擎可用，可使用 SHOW ENGINES 语句![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 1]
 *  mysql默认使用的存储引擎是InnoDB方式。

### 常见的存储引擎 ###

*  **MyISAM**  
    ![在这里插入图片描述][20200811113757323.png]

> MyISAM这种存储引擎不支持事务。  
> MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。  
> MyISAM采用三个文件组织一张表：  
> xxx.frm（存储格式的文件）  
> xxx.MYD（存储表中数据的文件）  
> xxx.MYI（存储表中索引的文件）  
> 优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。  
> 缺点：不支持事务。

*  **InnoDB**  
    ![在这里插入图片描述][20200811114331406.png]

> 优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。  
> 表的结构存储在xxx.frm文件中  
> 数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。  
> 这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。  
> InnoDB支持级联删除和级联更新。

*  **MEMORY**  
    ![在这里插入图片描述][2020081111451082.png]

> 缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。  
> 优点：查询速度最快。  
> 以前叫做HEPA引擎。

## 三、事务（Transaction） ##

*  **一个事务是一个完整的业务逻辑单元，不可再分。**

> 比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：  
> update t\_act set balance = balance - 10000 where actno = ‘act-001’;  
> update t\_act set balance = balance + 10000 where actno = ‘act-002’;  
> 以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。  
> 要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

*  和事务相关的语句只有：DML语句。（insert delete update）
 *  事务中的概念：

> a) 事务（Transaction）：一批操作（一组 DML）  
> b) 开启事务（Start Transaction）  
> c) 回滚事务（rollback）  
> d) 提交事务（commit）  
> e) SET AUTOCOMMIT：禁用或启用事务的自动提交模式

mysql事务默认情况下是自动提交的。

### 事务的特性 ###

**事务包括四大特性：ACID**

> A: 原子性：事务是最小的工作单元，不可再分。  
> C: 一致性：事务必须保证多条DML语句同时成功或者同时失败。  
> I：隔离性：事务A与事务B之间具有隔离。  
> D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

### 关于事务之间的隔离性 ###

**事务隔离性存在隔离级别，理论上隔离级别包括4个：**

> 第一级别：读未提交（read uncommitted）  
> 对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。  
> 读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。

> 第二级别：读已提交（read committed）  
> 对方事务提交之后的数据我方可以读取到。  
> 这种隔离级别解决了: 脏读现象没有了。  
> 读已提交存在的问题是：不可重复读。

> 第三级别：可重复读（repeatable read）  
> 这种隔离级别解决了：不可重复读问题。  
> 这种隔离级别存在的问题是：读取到的数据是幻象。

> 第四级别：序列化读/串行化读（serializable）  
> 解决了所有问题。  
> 效率低。需要事务排队。

mysql数据库默认的隔离级别是：可重复读。

**今天的分享就到这里啦！！~感谢大家的观看，希望对大家有帮助的话麻烦给个丝滑三连击。（点赞＋转发+关注）  
一起加油，一起努力，一起秃见成效**

[20200811112312471.png]: https://img-blog.csdnimg.cn/20200811112312471.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200811112319320.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200811113437446.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[20200811113757323.png]: https://img-blog.csdnimg.cn/20200811113757323.png
[20200811114331406.png]: https://img-blog.csdnimg.cn/20200811114331406.png
[2020081111451082.png]: https://img-blog.csdnimg.cn/2020081111451082.png