BCSP-玄子JAVA开发之JAVA数据库编程CH-06_MySQL数据库企业开发技术
BCSP-玄子JAVA开发之JAVA数据库编程CH-06_MySQL数据库企业开发技术
6.1 事务
6.1.1 什么是事务
TRANSACTION
- 是将一系列数据操作捆绑成为一个整体进行统一管理机制
- 多个操作作为一个整体向系统提交，要么都执行、要么都不执行
- 是一个不可分割的工作逻辑单元
6.1.2 事务的特性
事务必须具备的属性，简称 ACID 属性
| ACID | 属性描述 |
|---|---|
| 原子性：Atomicity | 事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行 |
| 一致性：Consistency | 当事务完成时，数据必须处于一致状态 |
| 隔离性：Isolation | 并发事务之间彼此隔离、独立，不应以任何方式依赖于或影响其他事务 |
| 持久性：Durability | 事务完成后，它对数据库的修改被永久保持 |
6.1.3 如何创建事务
MySQL中支持事务的存储引擎
-
InnoDB支持事务操作
-
通过UNDO日志和REDO日志实现对事务的支持
-
UNDO日志
- 复制事务执行前的数据，用于在事务发生异常时回滚数据
-
REDO日志
- 记录在事务执行中，每条对数据进行更新的操作
- 当事务提交时，该内容将被刷新到磁盘
-
-
MyISAM不支持事务操作
实现事务的方式
- SQL语句
- 设置自动提交关闭或开启
在执行命令SET autocommit=0，禁止当前会话的自动提交后，后面的SQL语句将作为事务中的语句一同提交
6.1.4 使用 SQL 语句实现事务处理
开始事务
BEGIN ;
或
START TRANSACTION;
提交事务
COMMIT ;
回滚（撤销）事务
ROLLBACK ;
使用事务实现小王和小张之间的转账操作
USE paycorp;
BEGIN;
UPDATE account SET balance=balance-2000 WHERE accountName='小王';
UPDATE account SET balance=balance+2000 WHERE accountName='小张';
COMMIT;
小王和小张的总账户余额和转账前保持一致，数据库中数据从一个一致性状态更新到另一个一致性状态
6.1.5 自动关闭和开启事务
-
默认情况下，每条单独的SQL语句视为一个事务
-
关闭默认提交状态后，可手动开启、关闭事务
关闭/开启自动提交
SET autocommit = 0|1;
- 状态值为0：关闭自动提交
- 值为1：开启自动提交
关闭自动提交后，从下一条SQL语句开始将会开启新事务，需使用COMMIT或ROLLBACK语句结束该事务
6.1.6 使用事务时要遵循的原则
事务尽可能简短
- 事务启动至结束后在数据库管理系统中保留大量资源，以保证事务的原子性、一致性、隔离性和持久性
- 如果在多用户系统中，较大的事务将会占用系统的大量资源，使得系统不堪重负，会影响软件的运行性能，甚至导致系统崩溃
事务中访问的数据量尽量最少
- 当并发执行事务处理时，事务操作的数据量越少，事务之间对操作数据的争夺就越少
查询数据时尽量不要使用事务
- 对数据进行浏览查询操作并不会更新数据库的数据时，尽量不使用事务查询数据，避免占用过量的系统资源
在事务处理过程中尽量不要出现等待用户输入的操作
- 处理事务的过程中，如果需要等待用户输入数据，那么事务会长时间占用资源，有可能造成系统阻塞
6.2 索引
6.2.1 什么是索引
是对数据库表中一列或多列值进行排列的一种结构
-
作用
-
大大提高数据库的检索速度
-
改善数据库性能
-
-
MySQL索引按存储类型分类
-
B-树索引（BTREE）
- InnoDB、MyISAM均支持
-
哈希索引（HASH）
-
-
数据存储在数据表中，而索引是创建在数据库表对象上
-
由表中的一个字段或多个字段生成的键组成
- 通过索引，可以极大的提升数据查询效率，改善数据库的性能
Indexes Use Key Values to Locate Data
（根据索引键查找定位数据行）
6.2.2 常用索引类型
普通索引
- 基本索引类型
- 允许在定义索引的列中插入重复值和空值
唯一索引
- 索引列数据不重复
- 允许有空值
主键索引
- 主键列中的每个值是非空、唯一的
- 一个主键将自动创建主键索引
复合索引
- 将多个列组合作为索引
全文索引
- 支持值的全文查找
- 允许重复值和空值
6.2.3 创建/删除索引
创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column_name [length] …);
-- 唯一索引、全文索引或空间索引，可选
如果创建索引是未指定创建索引的类型，则创建的索引为普通索引通过CREATE INDEX语句无法创建主键索引，主键索引的创建语句
删除索引
ALTER TABLE tablename ADD PRIMARY KEY(column)
删除表时，该表的所有索引同时会被删除
6.2.4 创建索引的指导原则
按照下列标准选择建立索引的列
- 频繁搜索的列
- 经常用作查询选择的列
- 经常排序、分组的列经常用作连接的列（主键/外键）
不要使用下面的列创建索引
- 仅包含几个不同值的列
- 表中仅包含几行
6.2.5 使用索引时注意事项
-
查询时减少使用
*返回表的全部列，不要返回不需要的列 -
索引应该尽量小，在字节数小的列上建立索引
-
WHERE 子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
-
避免在 ORDER BY 子句中使用表达式
-
根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理
6.2.6 查看索引 / 删除索引
查看已创建的索引信息
SHOW INDEX FROM table_name;
删除索引
DROP INDEX index_name ON table_name;
删除表时，该表的所有索引将同时被删
除删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除
如果组成索引的所有列都被删除，则整个索引将被删除
6.3 视图
6.3.1 为什么需要视图
不同的人员关注不同的数据
保证信息的安全性
6.3.2 什么是视图
视图是一张虚拟表
- 表示一张表的部分数据或多张表的综合数据
- 其结构和数据是建立在对表的查询基础上
视图中不存放数据
- 数据存放在视图所引用的原始表中
一个原始表，根据不同用户的不同需求，可以创建不同的视图
- 筛选表中的行
- 防止未经许可的用户访问敏感数据
- 降低数据库的复杂程度
- 将多个物理数据表抽象为一个逻辑数据表
6.3.3 视图的好处
开发人员
- 限制数据检索更容易
- 维护应用程序更方便
最终用户
- 结果更容易理解
- 获得数据更容易
6.3.4 创建 / 查看视图
使用SQL语句创建视图
CREATE VIEW view_name
AS
<SELECT 语句>;
-- 一般以view_xxx或v_xxx格式命名
使用SQL语句删除视图
DROP VIEW [IF EXISTS] view_name;
-- 删除前先判断 视图是否存在
使用SQL语句查看视图
SELECT 字段1, 字段2, …… FROM view_name;
6.3.5 使用视图注意事项
-
视图中可以使用多个表
-
一个视图可以嵌套另一个视图，但最好不要超过3层
-
对视图数据进行添加、更新和删除操作会直接影响所引用表中的数据
-
当视图数据来自多个表时，不允许添加和删除数据
查看所有视图
USE information_schema;
SELECT * FROM views;
使用视图修改数据会有许多限制，一般在实际开发中视图仅用作查询
6.4 数据库备份
6.4.1 为什么进行数据库备份
可能导致数据丢失的意外状况
- 数据库故障
- 突然断电
- 病毒入侵
- 人为误操作
- 程序错误
- 运算错误
- 磁盘故障
- 灾难（如火灾、地震）和盗窃
6.4.2 数据的备份与恢复
数据备份
- 是容灾的基础
- 指为防止系统出现操作失误或系统故障导致数据丢失，而将全部或部分数据集合从应用主机的硬盘或阵列复制到其他的存储介质的过程
- MySQL数据备份的常用方法
- mysqldump备份数据库
- Navicat备份数据库
数据恢复
- 是指通过技术手段，将保存在硬盘等存储介质上的丢失的数据进行抢救和恢复的技术
6.4.3 使用 mysqldump 命令备份数据库
mysqldump命令——MySQL的客户端常用逻辑备份工具
- 将CREATE和INSERT INTO语句保存到文本文件
- 属于DOS命令
mysqldump [options] database [table1,[table2]…] > [path]/filename.sql
- [options] 的选项参数
- –u username: 表示用户名
- –h host: 表示登录的主机名称，如本机为主机可省略
- –p password: 表示登录密码
- database
- 需备份的数据库名
- [table1,[table2]…]
- 需备份的表名若省略，备份所有表
- [path]/filename.sql
- 备份文件名称
mysqldump是DOS系统下的命令
在使用时，无须进入mysql命令行；否则，将无法执行
示例
以root账户登录到MySQL服务器，使用mysqldump命令备份hospital数据库，将SQL脚本保存到e:\hospital.sql中
mysqldump -uroot -proot hospital > e:\hospital.sql
导出的SQL脚本中两种注释
- 以
--开头：关于SQL语句的注释信息 - 以
/*!开头,*/结尾：是关于MySQL服务器相关的注释
为保证账户密码安全，命令中可不写密码
但是，参数
-p必须有，回车后根据提示写密码
常用参数选项
| 参数 | 描述 |
|---|---|
| -add-drop-table | 在每个CREATE TABLE语句前添加DROP TABLE语句，默认是打开的，可以用-skip-add-drop-table取消 |
| –add-locks | 该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句 |
| 好处：防止记录被再次导入时，其他用户对表进行的操作 | |
| -t或-no-create-info | 只导出数据，而不添加CREATE TABLE语句 |
| -c或–complete-insert | 在每个INSERT语句的列上加上列名，在数据导入另一个数据库时有用 |
| -d或–no-data | 不写表的任何行信息，只转储表的结构 |
备份文件包含的主要信息
- 备份后文件包含信息MySQL服务器及mysqldump工具的版本号
- 备份账户的名称
- 主机信息
- 备份的数据库名称
- SQL语句注释和服务器相关注释
- CREATE和INSERT语句
6.4.4 使用Navicat备份数据库
Navicat也可以用于导出数据库的备份脚本
- 右键点选数据库->转储SQL文件->结构和数据…
- 选择导出文件的保存路径和文件名后，便可导出数据库脚本
6.5 恢复数据库
在需要恢复数据库数据时，对导出的SQL备份脚本执行导入操作
- 使用mysql命令
- 使用source命令
- 使用Navicat导入数据
6.5.1 使用mysql命令恢复数据库
mysql为DOS命令
mysql –u username –p [dbname] < filename.sql
-- –u 用户名
-- –p 数据库名
-- filename.sql 备份文件名
在执行该语句之前，必须在MySQL服务器中创建新数据库
因为导出的备份文件中只包含表的备份，而不包含创建的库的语句
因此执行导入操作时必须指定数据库名，且该数据库必须存在
6.5.2 使用source命令恢复数据库
除了在命令行中导入数据以外，还可以在数据库已连接状态下导入数据
source filename;
登录MySQL服务后使用
执行该命令前，先创建并选择恢复后的目标数据库
CREATE DATABASE hospitalDB; #创建数据库
USE hospitalDB; #选择要导入数据库的数据库
source e:\hospital.sql #导入数据
# SQL脚本文件后面不要加字符 ;
6.5.3 使用Navicat导入数据
Navicat中导入数据的操作步骤
- 右键单击要导入数据的数据库
- 右键单击
运行SQL文件…快捷菜单项 - 在导入窗口，选择要运行的SQL文件
- 点击
开始按钮开始导入数据
BCSP-玄子JAVA开发之JAVA数据库编程CH-06_MySQL数据库企业开发技术 3.27