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事务尽可能简短

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

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

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

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

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

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

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

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

三、索引

3.1什么是索引

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

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

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

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

3.2索引创建注意事项

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

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

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

1. 不等于操作不能用于索引

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

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

4. 索引列为空，或包含空值

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

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

3.3索引的缺点

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

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

MySQL索引按存储类型分类

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

2. 哈希索引

常用索引类型：

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按照下列标准选择建立索引的列

1. 频繁搜索的列

2. 经常用作查询选择的列

3. 经常排序、分组的列

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

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

1. 仅包含几个不同值的列

2. 表中仅包含几行

4.3使用索引注意事项：

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

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

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

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

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

五、查看、删除索引

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视图的用途：

1. 筛选表中的行

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

3. 降低数据库的复杂程度

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

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

#创建：一般以view_xxx或v_xxx格式命名
CREATE VIEW view_name  
   AS
    <SELECT 语句>;
    
#删除：删除前先判断视图是否存在
DROP VIEW [IF EXISTS] view_name;
​
#查看：
SELECT 字段1, 字段2, …… FROM view_name;

6.4查看所有视图

USE information_schema;
SELECT * FROM views\G;

6.5使用经验

• 使用视图修改数据会有许多限制，一般在实际开发中视图仅用作查询

6.6注意事项

1. 视图中可以使用多个表

2. 一个视图可以嵌套另一个视图

3. 对视图数据进行添加、更新和删除操作直接影响所引用表中的数据

4. 当视图数据来自多个表时，不允许添加和删除数据

七、数据库备份

为什么要进行数据备份？

可能导致数据丢失的意外状况

1. 数据库故障

2. 突然断电

3. 病毒入侵

4. 人为误操作

5. 程序错误

6. 运算错误

7. 磁盘故障

8. 灾难（如火灾、地震）和盗窃

9. ……

如何避免意外状况导致的数据丢失？

• 数据备份与恢复

数据恢复

• 是指通过技术手段，将保存在硬盘等存储介质上的丢失的数据进行抢救和恢复的技术

八、mysqldump命令备份数据库

8.1语法简介

mysqldump命令——MySQL的客户端常用逻辑备份工具

• 将CREATE和INSERT INTO语句保存到文本文件

• 属于DOS命令

备份语法：

mysqldump [options] database [table1,[table2]…] > [path]/filename.sql

• options的选项参数

  1. –u username: 表示用户名

  2. –h host: 表示登录的主机名称，如本机为主机可省略

  3. –ppassword: 表示登录密码

• mysqldump是DOS系统下的命令在使用时，无须进入mysql命令行；否则，将无法执行

导出的SQL脚本中两种注释

• 以“–”开头：关于SQL语句的注释信息

• 以“/!”开头, “/”结尾：是关于MySQL服务器相关的注释

注意事项：

• 为保证账户密码安全，命令中可不写密码

• 但是，参数“-p”必须有，回车后根据提示写密码

8.2常用参数选项

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

1. 备份后文件包含信息MySQL服务器及mysqldump工具的版本号

2. 备份账户的名称

3. 主机信息

4. 备份的数据库名称

5. SQL语句注释和服务器相关注释

6. CREATE和INSERT语句

九、使用Navicat备份数据库

Navicat也可以用于导出数据库的备份脚本

操作

• 右键点选数据库->转储SQL文件->结构和数据…

• 选择导出文件的保存路径和文件名后，便可导出数据库脚本

十、恢复数据库

在需要恢复数据库数据时，对导出的SQL备份脚本执行导入操作

导入方法

• 使用mysql命令

• 使用source命令

• 使用Navicat导入数据

用mysql命令恢复数据库

mysql为DOS命令

mysql –u username –p [dbname] < filename.sql

注意事项：

• 在执行该语句之前，必须在MySQL服务器中创建新数据库

• 因为导出的备份文件中只包含表的备份，而不包含创建的库的语句，因此执行导入操作时必须指定数据库名，且该数据库必须存在

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

除了在命令行中导入数据以外，还可以在数据库已连接状态下导入数据

语法：

source filename;

示例：

CREATE DATABASE hospitalDB;  #创建数据库
USE hospitalDB; #选择要导入数据库的数据库
source E:\DB\MySQL8.0\backup\t147hospital.sql#导入数据,后面不要加字符“;”

注意：

登录MySQL服务后使用
执行该命令前，先创建并选择恢复后的目标数据库
SQL脚本文件后面不要加字符“; ”

十二、使用Navicat导入数据

Navicat中导入数据的操作步骤

• 右键单击要导入数据的数据库

• 右键单击“运行SQL文件…”快捷菜单项

• 在导入窗口，选择要运行的SQL文件

• 点击“开始”按钮开始导入数据

十三、本章总结