四、事务、范式

四、事务、范式

1、存储引擎(了解)

1. 完整的建表语句：

   create table tx(
   	id int default null
   )engine = InnoDB default charset=utf-8;
   -- 建表的时候，可以指定存储引擎，也可以指定字符集
   

2. 什么是存储引擎?

   • 只有mysql中存在这个名称，就是表的存储方式，mysql支持很多存储引擎(存储方式)，每个存储引擎都有各自的优缺点，要在合适的时候选择合适的存储引擎
   • 查看mysql支持的存储引擎：show engines \G

3. 常见的存储引擎

   1. MyISAM

      不支持事务，mysql最常用的引擎，但不是默认的，采用三个文件存储一张表

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

      优点：可被压缩、节省存储空间，可以转换为只读表，提高检索效率

      缺点：不支持事务

   2. InnoDB

      支持事务，mysql默认引擎，数据存储在tablespace这样的表空间中，无法被压缩，无法转换为只读

      优点：支持事务、行级锁、外键等，安全性高，提供mysql数据库崩溃后提供自动恢复机制

   3. MEMORY

      缺点：不支持事务，数据容易丢失(数据和索引都是存储在内存中的)

      优点：查询速度快

2、事务

1. 概念：一个事务是一个完整的业务逻辑单元，不可再分

2. 和事务相关的语句只有：DML语句(insert delete update)

   因为只有他们这三个语句都是和数据库表中的"数据"相关的，事务的存在是为了保证数据的完整性，安全性

3. 事务的特性ACID

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

4. 事务的隔离级别：

   1. 读未提交(read uncommitted):对方事务还没有提交，我们当前事务可以读取到对象未提交的数据
      • 对方事务还没有提交，我们当前事务可以读取到对象未提交的数据，这种现象被称为赃读(Dirty Read):表示读到了脏的数据
   2. 读已提交(read committed):对方事务提交之后的数据我方可以读取到，解决了脏读，但是会出现不可重复读:由于对方事务的更改，我方读到的数据不一致
   3. 可重复读(repeatable read):解决了不可重复读的问题，但是出现了幻读：读到的数据可能不是真实的
   4. 序列化读(serializable):解决了所有问题

5. Mysql默认是自动提交事务的，需要关闭自动提交事务：start transaction;

6. 设置当前隔离级别：set global transaction isolation level 隔离级别;

7. 查看当前的隔离级别：select @@global.tx_isolation;

8. Mysql默认隔离级别是可重复读(repeatable),oracle默认隔离级别是读已提交(read commintted)

3、索引

1. 索引的概念：相当于一本书的目录，通过目录可以找到对应的资源

2. 数据库查询一张表数据检索方式：

   1. 全表索引
   2. 根据索引检索(效率高)

3. 什么时候考虑给字段添加索引(满足什么条件?)

   1. 数据量庞大
   2. 该字段很少进行DML操作。(因为字段进行修改操作，索引也需要维护)
   3. 该字段经常出现在where子句中(经常根据那个字段查询)

4. 创建索引对象、删除索引对象

   • 创建索引对象：create index 索引名称 on 表名(字段名);
   • 删除索引对象：drop index 索引名称 on 表名;

5. 注意点：

   • 主键和具有unique约束的字段会自动添加索引
   • 根据主键查询效率较高，尽量根据主键检索

6. 索引的分类：

   1. 单一索引：给单个字段添加索引
   2. 复合索引：给多个字段联合起来添加1个索引
   3. 主键索引：主键上会自动添加索引
   4. 唯一索引：有unique约束的字段会自动添加索引

7. 索引什么时候会失效

   • select ename from emp where ename like ‘%A%’;
   • 模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的

8. 查询sql语句的执行计划：explain

   explain select ename,sal from emp where sal = 5000;
   

9. 索引的执行原理：底层使用 B + tree

4、视图

1. 什么是视图
   • 视图可以隐藏表的实现细节，保留级别较高的系统，数据库只对外提供相关的视图
2. 创建视图/删除视图
   • create view myview as select empno,ename from emp;
   • drop view myview;

5、DBA命令

1. 将数据库导出：在windows的dos命名窗口
   1. 导出整个库：mysqldump mnyuansql > D:\mnyuan.sql -uroot -p1234
   2. 导出指定数据库中的表：mysqldump mnyuansql emp > D:\mnyuan.sql -uroot -p1234
2. 导入数据：source D:\mnyuan.sql

6、数据库设计三范式

1. 设计范式：设计表的依据，按照三范式设计的表能很大程度上减少数据冗余
2. 三范式是那些：
   1. 第一范式：任何一张表都应该有主键、并且每一个字段原子性不可再分
   2. 第二范式：建立自第一范式的基础上，所有非主键完全依赖主键，不能产生部分依赖(多对多?三张表，关系两个外键)
   3. 第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖(一对多?两张表，多的表加外键)
3. 一对一怎么设计：
   1. 主键共享
   2. 外键唯一