一、存储引擎（了解）

1.完整的建表语句

CREATE TABLE dept(
    DEPTNO int(2) NOT NULL,
    DNAME varchar(14) DEFAULT NULL,
    LOC varchar(13) DEFAULT NULL,
    PRIMARY KEY (DEPTNO)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

建表的时候可以指定存储引擎，也可以指定字符集。Mysql默认使用的存储引擎是InnoDB方式，默认采用的字符集是UTF8.

2.什么是存储引擎？

存储引擎这名字只有在mysql中存在，存储引擎其实就是指表的存储方式。（oracle中有相应的机制，但是不叫做存储引擎，Oracle中没有特殊的名字，就是”表的存储方式”）
Mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

3.常见存储引擎

3.1 MyISAM存储引擎

MyISAM是mysql最常用的存储引擎，但是这种存储引擎不是默认的，MyISAM 这种存储引擎不支持事务。
它管理的表使用三个文件表示每个表，具有以下特征：

• 格式文件-存储表结构的定义（mytable.frm）
• 数据文件-存储表中的数据（mytable.MYD）
• 索引文件-存储表上索引（mytable.MYI）

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

3.2 InnoDB存储引擎

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

3.3 MEMORY存储引擎

使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎查询速度非常快。MEMORY存储引擎以前被称为HEAP引擎。
缺点：不支持事务，数据容易丢失，因为数据存储在内存中。
优点：查询速度最快。

3.4 总结

• MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适合情形是使用压缩的只读表。
• 如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。

• 可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于键盘的表中重新生成的数据。

  二、事务（Transaction）

  1.事务概述

  一个事务是一个完整的业务逻辑单元，不可再分。
  比如：银行账户转账，从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），因为它们这三个语句都适合数据库表当中的“数据”相关的。事务的存在是为了保证数据的完整性，安全性。假设所有的业务的都能使用一条DML语句搞定则不需要事务机制。
  但实际情况不是这样的，通常一个“事儿（事务、业务）”需要多条DML语句共同联合完成。

  2.事务原理

  假设一个事儿，需要先执行一条insert，再执行一条update，最后执行一条delete。
  开启事务机制：

• 执行insert语句—>insert…（这个执行成功之后，把这个执行记录到数据库的操作历史当中，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据。）

• 执行update语句—->update…（这个执行也是记录一下历史操作，不会真正的修改硬盘上的数据）
• 执行delete语句—->delete…（这个执行也是记录一下历史操作【记录到缓存】，不会真正的修改硬盘上的数据）

提交事务或者回滚事务（事务结束）
当提交事务时，把以上这些操作全部真正执行，数据发生更改；当回滚事务时，相当于取消这些操作，操作不会被执行，数据不会被更改。所以，提交或者回滚事务时，一个事务才真正的结束。

3.事务四大特性

对于数据库来说事务保证批量的DML要么全成功，要么全失败。事务具有四个特征ACID

• 原子性（Atomicity）

事务是最小的工作单元，不可再分，整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。

• 一致性（Consistency）

事务必须保证多条DML语句同时成功或者同时失败。

• 隔离性（Isolation）

事务A和事务B之间隔离，一个事务不会影响其他事务的运行。

• 持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

4.事务的隔离性

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

• 第一级别：读未提交（read uncommitted）

对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读现象（dirty read）：表示读到了脏数据，这个数据不稳定，根本不在硬盘文件上，只存在于缓存中。

• 第二级别：读已提交（read committed）

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

• 第三级别：可重复读（repeatable read）

这种隔离级别解决了：不可重复读问题。

• 第四级别：序列化读/串行化读（serializable）

解决了所有问题。效率第，需要事务排队。
Oracle数据库默认的隔离级别是：读已提交。
Mysql 数据库默认的隔离级别是：可重复读。

5.演示事务

演示事务
mysql事务默认情况下是自动提交的。什么是自动提交？只要执行任意一条DML语句则提交一次。怎么关闭自动提交？ start transaction；
提交事务：commit，回滚事务：rollback

三、索引

1.什么是索引？有什么用？

索引就相当于一本书的目录，通过目录可以快速地找到对应的资源。在数据库方面，查询一张表的时候有两种检索方式：

• 第一种方式：全表扫描
• 第二种方式：根据索引检索（效率很高）

索引为什么可以提高检索效率呢？
其实最根本的原理是缩小了扫描的范围。索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断地维护，是有维护成本的。比如，表中的数据也经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
添加索引是给某一个字段，或者说某些字段添加索引。

Select ename ,sal from emp where ename=’smith’;

当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描enamel字段中的所有的值。
当enamel字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

2.创建、删除索引对象

创建索引对象：

Create index 索引名称 on 表名（字段名）;

删除索引对象：

Drop index 索引名称 on 表名;

3.添加索引的情形

• 数据量庞大。
• 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
• 该字段经常出现在where字句中。（经常根据某个字段查询，则这个字段可用考虑设置索引）

注意：主键和具有unique约束的字段自动会添加索引，所以，优先选择主键或unique字段查询。

4.索引原理（重点，面试经常问）

知乎-快速理解索引原理
CSDN-MySQL索引原理
通过B tree 缩小扫描范围，底层索引进行了排序，分析，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
索引什么时候失效？

Select ename from emp where ename like '%A%';

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

四、视图（View）

1.视图概述

视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。视图有时也被称为“虚拟表”，视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。
相对于从基表中直接获取数据，视图有以下好处：

• 访问数据变得简单
• 可被用来对不同用户显示不同的表的内容

• 用来协助适配表的结构以适应前端现有的应用程序

  2.视图作用

• 视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。

• 视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）。
• n从而加强了安全性，使用户只能看到视图所显示的数据。
• 视图还可以被嵌套，一个视图中可以嵌套另一个视图。

视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

3.视图创建、删除

Create view myview as select empno,ename from emp;
Drop view myview;

注意：只有DQL语句才能以视图对象的方式创建出来。
对视图进行怎删改查，会影响原表数据。（通过视图影响原表数据的，不是直接操作的原表）

4.面向视图操作

Select * from myview;
Create table emp_bak as select * from emp;
Create view myview1 as select empno,ename,sal from emp_bak;
Update myview1 set ename=’hehe’,sal=1 where empno=7369;//通过视图修改原表数据。
Delete from myview1 where empno=7369;//通过视图删除原表数据。

五、DBA命令（了解）

1.新建用户

2.授权

3.回收权限

4.数据导入导出（掌握）

4.1 将数据库当中的数据导出（备份）

导出整个数据库，在windows的dos命令窗口中执行：

Mysqldump 数据库名>文件路径名 -u用户 -p密码
Mysqldump sun>D:\sun.sql -uroot -p123456

导出数据库中的某张表，在windows的dos命令窗口中执行：

Mysqldump 数据库名 表名>文件路径名 -u用户 -p密码
Mysqldump sun emp>D:\emp.sql -uroot -p123456

4.2 导入数据

Create database sun;
Use sun;
Source D:\sun.sql

六、数据库设计三范式（重点，面试经常问）

1.设计范式概述

设计范式是设计表的依据，按照这个三范式设计的表不会出现数据冗余。

2.三范式

• 第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
• 第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。
• 

设计案例1：多对多关系？三张表，关系表两外键。
t_student学生表

t_teacher讲师表

t_student_teacher_relation 学生讲师关系表

• 第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。

设计案例2：一对多关系？两张表，多的表加外键。
班级t_class

学生 t_student

提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿空间换时间，即允许冗余数据来提高执行速度。
设计案例3：一对一关系。

• 方式一：主键共享

t_user_login 用户登录表

t_user_detail 用户详细信息表

• 方式二：外键唯一

t_user_login 用户登录表

t_user_detail 用户详细信息表

总结：三范式理解，重点掌握三种设计案例。