#夏日挑战赛# 聊聊MySql中的约束、存储引擎以及事务 原创

发布于 2022-6-14 17:01
浏览
4收藏

#夏日挑战赛# 聊聊MySql中的约束、存储引擎以及事务-开源基础软件社区
本文正在参加星光计划3.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**
添加主键约束:
```sql
		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约束。

顺序要求:

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

父表:

		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 语句

#夏日挑战赛# 聊聊MySql中的约束、存储引擎以及事务-开源基础软件社区

  • ==mysql默认使用的存储引擎是InnoDB方式。==

常见的存储引擎

  • MyISAM
    #夏日挑战赛# 聊聊MySql中的约束、存储引擎以及事务-开源基础软件社区
  • MyISAM这种存储引擎不支持事务。
  • MyISAM是mysql最常用的存储引擎,但是这种引擎不是默认的。
  • MyISAM采用三个文件组织一张表:

xxx.frm(存储格式的文件)
xxx.MYD(存储表中数据的文件)
xxx.MYI(存储表中索引的文件)

优点:可被压缩,节省存储空间。并且可以转换为只读表,提高检索效率。
缺点:不支持事务。

  • InnoDB
    #夏日挑战赛# 聊聊MySql中的约束、存储引擎以及事务-开源基础软件社区

  • 表的结构存储在xxx.frm文件中

  • 数据存储在tablespace这样的表空间中(逻辑概念),无法被压缩,无法转换成只读

  • 这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。

  • InnoDB支持级联删除和级联更新。
    优点:支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

  • MEMORY
    #夏日挑战赛# 聊聊MySql中的约束、存储引擎以及事务-开源基础软件社区
    缺点:不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
    优点:查询速度最快。

三、事务(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)
  • 事务中的概念:
  1. 事务(Transaction):一批操作(一组 DML)
  2. 开启事务(Start Transaction)
  3. 回滚事务(rollback)
  4. 提交事务(commit)
  5. SET AUTOCOMMIT:禁用或启用事务的自动提交模式

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

事务的特性

事务包括四大特性:ACID

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

关于事务之间的隔离性

事务隔离性存在隔离级别,理论上隔离级别包括4个:

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

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

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

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

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

©著作权归作者所有,如需转载,请注明出处,否则将追究法律责任
8
收藏 4
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐