带你走进MySQL数据库（MySQL入门详细总结三）

待我称王封你为后i 2023-05-21 08:56 9阅读 0赞

> 导读：关于MySQL，用三篇文章带你进入MySQL的世界。文章较长，建议收藏再看！

[带你走进MySQL数据库（MySQL入门详细总结二）][MySQL_MySQL]

[带你走进MySQL数据库（MySQL入门详细总结一）][MySQL_MySQL 1]

### 文章目录 ###

*  1.存储引擎
 *  2.事务（Transaction)
 *  3.索引
 *  4.视图(view)
 *  5.DBA命令
 *  6.数据库设计三范式

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWh1YW5f_size_16_color_FFFFFF_t_70]

# 1.存储引擎 #

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

**1.什么是存储引擎**  
每一个存储引擎都对应一种不同的存储方式。

**2.查看当前mysql支持的存储引擎。**  
show engines \\G

**3.常见的存储引擎：**  
**MySIAM这种存储引擎不支持事务。**  
比较常用。  
他管理表具有以下特征：

*  1.使用三个文件表示每个表：  
    格式文件----存储表结构的定义（mytable.frm) 格式  
    数据文件----存储表行的内容（mytable.MYD) 数据  
    索引文件—存储表上的索引（mytable.MYI) 索引  
    2.灵活的AUTD\_INCREMENT  
    3.可被压缩、可转换为只读表，节省空间

**缺点：不支持事务。**

**InnoDB引擎**  
优点：支持事务，行级锁，外键，级联删除和级联更新等。这种引擎数据最安全。  
数据存储在tabalespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。  
在mysql服务器崩溃后提供自动恢复。

**MEMORY引擎**  
缺点：不支持事务，数据容易丢失，因为表数据和索引都是存储在内存当中。  
优点：查询数据最快。

# 2.事务（Transaction) #

**1.事务的概述**  
**什么是事务？**  
一个事务是一个完整的业务逻辑单元，不可再分。  
比如：银行账户转账，从A账户向B账户转账10000，需要执行两条update语句。  
update t\_act set balance = balance -10000 where actno = ‘adfa1’;  
**update** t\_act **set** balance = balance+10000 **where** actno = ‘adfa2’;  
以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。  
要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

**2.和事务相关的语句只有：DML语句。（insert delete update)**  
因为他们这三个语句都是和数据库表当中的数据相关的。  
事物的存在就是为了保证数据的安全性，和完整性。

**3.假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？**  
答：不需要事务。  
但通常一个事务（业务），需要多条DML语句共同联合完成。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWh1YW5f_size_16_color_FFFFFF_t_70 1]

提交事务（会写到文件）commit  
回滚事务（不会写到文件）rollback

**4.事务的控制语句（TCL)**  
事务控制语句：  
BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。  
**5.事务的特性**  
事务包括四大特性：ACID  
A:原子性：事务是最小的工作单元，不可再分。一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

B:一致性：事务必须保证多条DML语句同时成功或者同时失败。在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

C:隔离性：事务A与事务B之间具有隔离。数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

D:持久性：最终数据必须持久化到硬盘文件中，事务才算成功的结束。事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

**6.事务之间的隔离性**  
事务隔离的隔离级别：  
第一级别：读未提交（read uncommitted)  
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。存在脏读（Dirty Read)现象:表示读到了脏的数据。

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

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

第四级别：串行化/序列化  
解决所有的问题。需要事务排队。  
mysql默认的级别是可重复读。  
oracle默认级别是：读已提交。

**演示：**  
mysql事务默认情况是自动提交。即只要执行一条DML语句则提交一次。  
怎么关闭自动提交？  
用：**start transaction;**

**使用两个以上的事务演示以上的隔离区别:**  
查询当前隔离级别：select @@global.tx\_isolation;  
设置事务的全局隔离级别:**set global transaction isolation level** read uncommitted;

# 3.索引 #

**1.什么是索引**  
索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。  
在数据库方面，查询一张表的时候有两种检索方式：  
第一种方式：全表扫描  
第二种方式：根据索引检索(效率很高）  
索引虽然可以提高检索效率，但是不能随意添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护，是由维护成本的，比如，表中的数据经常被修改这样就不适合添加索引，因为数据一旦修改，索引就需要重新维护。

**2.怎么创建索引对象？怎么删除索引对象？**  
添加索引是给某些字段添加索引。  
如：select ename ,sal from emp where ename = ‘sfsa’;  
当ename字段没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。  
当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

**给字段添加索引：**  
**create index** emp\_sal\_index **on** emp(sal);  
create index 索引名称 on 表名 （字段名）

**删除索引对象：**  
drop index 索引名称 on 表名；

**3.什么时候考虑给字段添加索引？（满足什么条件）**  
\*数据量庞大。(根据客户的需求）  
\*该字段很少修改。（很少DML操作）  
\*该字段经常出现在where子句中。（经常根据哪个字段查询）

**4.注意：主键和具有unique约束字段自动会添加索引。**  
根据主键查询效率较高，尽量根据主键检索。

**5.查看sql语句的执行计划**  
**explain** \+ sql语句。

**6.索引底层采用的数据结构是：B+Tree**  
**7.索引的实现原理：**  
在内存或硬盘创建一个索引。索引自动排序。  
索引分区。  
通过b-Tree缩小了扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

**8.索引的分类**  
单一索引：给单个字段添加索引  
复合索引：给多个字段联合起来添加1个索引  
主键索引：主键上自动添加索引。  
唯一索引：有unique约束的字段上会自动添加索引。

**9.索引什么时候失效？**  
比如模糊查询的时候，第一个字符使用的是%，这时候索引是失效的。

# 4.视图(view) #

**1.什么是视图？**  
站在不同的角度去看数据。（同一张表的数据，通过不同的角度去看待）  
2.怎么创建视图？怎么删除视图？  
**create view** myview as select empno,ename from emp;

**drop** view myview;  
3.对视图进行增删改查，会影响到原表的数据。（通过视图影响到表数据的原表）可以对视图进行CRUD操作。

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

# 5.DBA命令 #

**1.将数据库当中的数据导出。**  
在window窗口中：**mysqldump** xingkong>D:\\ -uroot -p+加密码（导出整个 库）  
在window窗口中：**mysqldump** xingkong t\_student>D:\\ -uroot -p+加密码（导出指定表）

**2.导入数据。**  
create database +数据库  
使用数据库  
source 文件路径

**3.创建用户**  
**create user** username **IDENTIFIED BY** ‘password’;  
4.授权  
5.撤销权限。

[菜鸟教程][Link 1]

# 6.数据库设计三范式 #

1.什么是设计范式？  
设计表的依据，按照这个三范式设计的表**不会出现数据冗余。**

**2.三范式：**  
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。

第二范式:在第一范式的基础上，所有非主键字段完全依赖主键，**不能产生部分依赖。**  
**多对多？三张表，关系表里有两个外键**。  
t\_student学生表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWh1YW5f_size_16_color_FFFFFF_t_70 2]

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

班级t\_class  
学生t\_student  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWh1YW5f_size_16_color_FFFFFF_t_70 3]  
一对多？两张表，**多**的表加外键。

注意:实际开发中以满足客户的需求为主，有的时候拿冗余换执行速度。

3.一对一怎么设计？  
一对一设计及有两种方案：  
1.主键共享（**主键加外键**）  
2.外键唯一（某字段外键加+**unique**)  
用户登录表。  
用户详细信息表。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWh1YW5f_size_16_color_FFFFFF_t_70_pic_center]

[MySQL_MySQL]: https://blog.csdn.net/jiahuan_/article/details/105642948
[MySQL_MySQL 1]: https://blog.csdn.net/jiahuan_/article/details/105589563
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWh1YW5f_size_16_color_FFFFFF_t_70]: /images/20230521/27ac8b3fc1dd493ca123eaa1aa233f8a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWh1YW5f_size_16_color_FFFFFF_t_70 1]: /images/20230521/e237ab8ded16422f9746232ec2433af2.png
[Link 1]: https://www.runoob.com/mysql/mysql-database-import.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWh1YW5f_size_16_color_FFFFFF_t_70 2]: /images/20230521/70a04e6c89984b6996450a0ffd6f31e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWh1YW5f_size_16_color_FFFFFF_t_70 3]: /images/20230521/102d618c7ded47b08568a18c3242296c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWh1YW5f_size_16_color_FFFFFF_t_70_pic_center]: /images/20230521/0823b7be62eb4a4fba1cf0d41f27094b.png