MySQL数据库企业级开发技术

一、事务

1.1什么是事务

1.2事务的特性

二、使用事务

2.1自动关闭和开启事务

2.2使用事务时要遵循的原则

2.2.1事务尽可能简短

2.2.2事务中访问的数据量尽量最少

2.2.3查询数据时尽量不要使用事务

2.2.4在事务处理过程中尽量不要出现等待用户输入的操作

三、索引

3.1什么是索引

3.2索引创建注意事项

3.3索引的缺点

四、创建、删除索引

4.1创建、删除索引

4.2创建索引的指导原则

4.2.1按照下列标准选择建立索引的列

4.2.2请不要使用下面的列创建索引

4.3使用索引注意事项：

五、查看、删除索引

5.1查看索引

5.2删除索引

六、视图

6.1什么是视图？

6.2视图的用途：

6.3创建视图、删除视图、查看视图

6.4查看所有视图

6.5使用经验

6.6注意事项

七、数据库备份

八、mysqldump命令备份数据库

8.1语法简介

8.2常用参数选项

8.3备份文件包含的主要信息

九、使用Navicat备份数据库

十、恢复数据库

十一、使用source命令恢复数据库

十二、使用Navicat导入数据

十三、本章总结

一、事务

1.1什么是事务

TRANSACTION

是将一系列数据操作捆绑成为一个整体进行统一管理机制

多个操作作为一个整体向系统提交，要么都执行、要么都不执行

是一个不可分割的工作逻辑单元

1.2事务的特性

事务必须具备以下四个属性，简称ACID 属性,MySql中支持事务的存储引擎：InnoDB、BDB

1.原子性（Atomicity）

事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行

2.一致性（Consistency）

当事务完成时，数据必须处于一致状态

3.隔离性（Isolation）

并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务

4.持久性（Durability）

事务完成后,它对数据库的修改被永久保持

二、使用事务

开始事务语法
begin;
#或者
start transaction;
提交事务语法：
commit;
回滚事务语法：
rollback;
关闭自动事务提交语法
set autocommit=0|1; #0:关闭自动提交，1:开启
自动关闭和开启事务

默认情况下，每条单独的SQL语句视为一个事务

关闭默认提交状态后，可手动开启、关闭事务

关闭/开启自动提交状态
SET autocommit = 0|1;
值为0：关闭自动提交

值为1：开启自动提交

注意事项：

关闭自动提交后，从下一条SQL语句开始将会开启新事务，需使用COMMIT或ROLLBACK语句结束该事务
2.1自动关闭和开启事务

默认情况下，每条单独的SQL语句视为一个事务

关闭默认提交状态后，可手动开启、关闭事务

关闭/开启自动提交状态
SET autocommit = 0|1;
值为0：关闭自动提交

值为1：开启自动提交

注意事项：

关闭自动提交后，从下一条SQL语句开始将会开启新事务，需使用COMMIT或ROLLBACK语句结束该事务
2.2使用事务时要遵循的原则
2.2.1事务尽可能简短

事务启动至结束后在数据库管理系统中保留大量资源，以保证事务的原子性、一致性、隔离性和持久性

如果在多用户系统中，较大的事务将会占用系统的大量资源，使得系统不堪重负，会影响软件的运行性能，甚至导致系统崩溃

2.2.2事务中访问的数据量尽量最少

当并发执行事务处理时，事务操作的数据量越少，事务之间对操作数据的争夺就越少

2.2.3查询数据时尽量不要使用事务

对数据进行浏览查询操作并不会更新数据库的数据时，尽量不使用事务查询数据，避免占用过量的系统资源

2.2.4在事务处理过程中尽量不要出现等待用户输入的操作

处理事务的过程中，如果需要等待用户输入数据，那么事务会长时间占用资源，有可能造成系统阻塞

三、索引

3.1什么是索引
索引是一种有效组合数据的方式，为快速查找到指定记录。

索引是创建在数据库表对象上,由表中的一个字段或多个字段生成的键组成

是对数据库表中一列或多列值进行排列的一种结构

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

3.2索引创建注意事项

创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERe 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

以下几种情况索引不会被使用

不等于操作不能用于索引

经过普通或者函数运算的索引列不能使用索引

含前向模糊查询（通配符%在搜索词首出现），比如“like %王xx”【反向键索引：reverse】

索引列为空，或包含空值

数值比较时左右类型不同，相当于做了隐式类型转换

给索引查询的值是未知字段，而不是已知数

3.3索引的缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATe和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

MySQL索引按存储类型分类

B-树索引：InnoDB、MyISAM均支持

哈希索引

常用索引类型：

1、普通索引

基本索引类型

允许在定义索引的列中插入重复值和空值

2、唯一索引：

索引数据不能重复

允许有空值

3、主键索引：

主键中的每一个值是非空、唯一的

一个主键将自动创建主键索引

4、复合索引

将多个列组合作为索引

5、全文索引：(MyISAM)

支持值的全文查找

允许重复值与空值

6、空间索引

对空间数据的列建立索引

四、创建、删除索引

4.1创建、删除索引
创建索引语法
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
       ON table_name (column_name [length] …);
注意事项：

如果创建索引是未指定创建索引的类型，则创建的索引为普通索引

通过CREATE INDEX语句无法创建主键索引，主键索引的创建语句
alter table tableName add primary key(column)
删除索引语法
DROP INDEX index_name ON Table_Name;
注意事项：

注意：删除表时，该表的所有索引同时会被删除。
4.2创建索引的指导原则
4.2.1按照下列标准选择建立索引的列

频繁搜索的列

经常用作查询选择的列

经常排序、分组的列

经常用作连接的列（主键/外键）

4.2.2请不要使用下面的列创建索引

仅包含几个不同值的列

表中仅包含几行

4.3使用索引注意事项：

查询时减少使用*返回全部列，不要返回不需要的列

索引应该尽量小，在字节数小的列上建立索引

Where子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前

避免在Order By子句中使用表达式

根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理

五、查看、删除索引

5.1查看索引
查看已创建的索引信息
SHOW INDEX FROM table_name;
查看hospital数据库中patient表的索引信息
use hospital;
show index from patient;
5.2删除索引
语法：
DROp INDEX index_name ON table_name;
示例：
drop index index_patientName ON patient;
注意：

六、视图

6.1什么是视图？
1、视图是一张虚拟表

表示一张表的部分数据或多张表的综合数据

其结构和数据是建立在对表的查询基础上

2、视图中不存放数据

数据存放在视图所引用的原始表中

3、一个原始表，根据不同用户的不同需求，可以创建不同的视图

6.2视图的用途：

筛选表中的行

防止未经许可的用户访问敏感数据

降低数据库的复杂程度

将多个物理数据库抽象为一个逻辑数据库

6.3创建视图、删除视图、查看视图

#创建：一般以view_xxx或v_xxx格式命名
CREATE VIEW view_name  
   AS

MySQL数据库企业级开发技术

1.1什么是事务

TRANSACTION

是将一系列数据操作捆绑成为一个整体进行统一管理机制

多个操作作为一个整体向系统提交，要么都执行、要么都不执行

是一个不可分割的工作逻辑单元

2.2使用事务时要遵循的原则

2.2.2事务中访问的数据量尽量最少

当并发执行事务处理时，事务操作的数据量越少，事务之间对操作数据的争夺就越少

2.2.3查询数据时尽量不要使用事务

对数据进行浏览查询操作并不会更新数据库的数据时，尽量不使用事务查询数据，避免占用过量的系统资源

2.2.4在事务处理过程中尽量不要出现等待用户输入的操作

处理事务的过程中，如果需要等待用户输入数据，那么事务会长时间占用资源，有可能造成系统阻塞

4.2创建索引的指导原则

4.2.1按照下列标准选择建立索引的列

频繁搜索的列

经常用作查询选择的列

经常排序、分组的列

经常用作连接的列（主键/外键）

4.2.2请不要使用下面的列创建索引

仅包含几个不同值的列

表中仅包含几行

5.1查看索引
查看已创建的索引信息

SHOW INDEX FROM table_name;

查看hospital数据库中patient表的索引信息

use hospital; show index from patient;

5.2删除索引
语法：

DROp INDEX index_name ON table_name;

示例：

drop index index_patientName ON patient;

注意：

6.2视图的用途：

筛选表中的行

防止未经许可的用户访问敏感数据

降低数据库的复杂程度

将多个物理数据库抽象为一个逻辑数据库

6.3创建视图、删除视图、查看视图
#创建：一般以view_xxx或v_xxx格式命名 CREATE VIEW view_name AS

MySQL数据库企业级开发技术

1.1什么是事务 TRANSACTION 是将一系列数据操作捆绑成为一个整体进行统一管理机制 多个操作作为一个整体向系统提交，要么都执行、要么都不执行 是一个不可分割的工作逻辑单元

2.2使用事务时要遵循的原则

2.2.2事务中访问的数据量尽量最少 当并发执行事务处理时，事务操作的数据量越少，事务之间对操作数据的争夺就越少

2.2.3查询数据时尽量不要使用事务 对数据进行浏览查询操作并不会更新数据库的数据时，尽量不使用事务查询数据，避免占用过量的系统资源

2.2.4在事务处理过程中尽量不要出现等待用户输入的操作 处理事务的过程中，如果需要等待用户输入数据，那么事务会长时间占用资源，有可能造成系统阻塞

4.2创建索引的指导原则

4.2.1按照下列标准选择建立索引的列 频繁搜索的列 经常用作查询选择的列 经常排序、分组的列 经常用作连接的列（主键/外键）

4.2.2请不要使用下面的列创建索引 仅包含几个不同值的列 表中仅包含几行

5.1查看索引 查看已创建的索引信息 SHOW INDEX FROM table_name; 查看hospital数据库中patient表的索引信息 use hospital; show index from patient;

5.2删除索引 语法： DROp INDEX index_name ON table_name; 示例： drop index index_patientName ON patient; 注意：

6.2视图的用途： 筛选表中的行 防止未经许可的用户访问敏感数据 降低数据库的复杂程度 将多个物理数据库抽象为一个逻辑数据库

1.1什么是事务

TRANSACTION

是将一系列数据操作捆绑成为一个整体进行统一管理机制

多个操作作为一个整体向系统提交，要么都执行、要么都不执行

是一个不可分割的工作逻辑单元

2.2.2事务中访问的数据量尽量最少

当并发执行事务处理时，事务操作的数据量越少，事务之间对操作数据的争夺就越少

2.2.3查询数据时尽量不要使用事务

对数据进行浏览查询操作并不会更新数据库的数据时，尽量不使用事务查询数据，避免占用过量的系统资源

2.2.4在事务处理过程中尽量不要出现等待用户输入的操作

处理事务的过程中，如果需要等待用户输入数据，那么事务会长时间占用资源，有可能造成系统阻塞

4.2.1按照下列标准选择建立索引的列

频繁搜索的列

经常用作查询选择的列

经常排序、分组的列

经常用作连接的列（主键/外键）

4.2.2请不要使用下面的列创建索引

仅包含几个不同值的列

表中仅包含几行

5.1查看索引
查看已创建的索引信息

SHOW INDEX FROM table_name;

查看hospital数据库中patient表的索引信息

use hospital; show index from patient;

5.2删除索引
语法：

DROp INDEX index_name ON table_name;

示例：

drop index index_patientName ON patient;

注意：

6.2视图的用途：

筛选表中的行

防止未经许可的用户访问敏感数据

降低数据库的复杂程度

将多个物理数据库抽象为一个逻辑数据库