事务、视图、索引、备份和恢复—— 使用Java实现数据库编程

一、什么是事务

• 事务是一种机制，一个操作序列，包含了一组数据库的操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么执行要么不执行。
• 事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是作为最小的控制单元来使用的，特别适用于多用户同时操作的数据库系统。
• 事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性。
  1. 原子性
     1. 事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行
  2. 一致性
     1. 当事务完成时，数据必须处于一致状态
  3. 隔离性
     1. 并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务
  4. 持久性
     1. 事务完成后,它对数据库的修改被永久保持

二、如何执行事务

1. 开始事务

   begin;
   	或
   start transaction;
   

   这个语句显示标记一个事务的起始点。

2. 提交事务

   commit;
   

   这个语句标志一个事务成功提交。自事务开始至提交语句之间执行的所有数据更新将永久保存在数据库文件中，并释放连接时占用的资源。

3. 回滚（撤销）事务

rollback;

​ 清除自事务起始点至该语句所做的所有数据更新操作，将数据状态回滚到事务开始前，并释放自事务控制的资源。

• 默认情况下，每条单独的SQL语句视为一个事务
• 关闭默认提交状态后，可手动开启、关闭事务

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

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

三、视图的基础知识

1. 什么是视图

   • 视图是保存在数据库中的 select 查询。
   • 视图是另一种查看数据库中一个或多个表中数据的方法。
   • 使用数据库的原因有两个
     • 处于安全考虑，用户不必看到整个数据库的结构，而隐藏部分数据
     • 符合用户日常业务逻辑，使他们更容易理解数据

2. 为什么需要视图

   • 借助视图能把复杂的代码进行封装起来，当需要制作成绩单事，只需要调用并执行对应的视图就可以轻松完成任务。

3. 视图的特点

   1. 视图是一张虚拟表
      1. 表示一张表的部分数据或多张表的综合数据
      2. 其结构和数据是建立在对表的查询基础上
   2. 视图中不存放数据
      1. 数据存放在视图所引用的原始表中
   3. 一个原始表，根据不同用户的不同需求，可以创建不同的视图

4. 视图的用途

   1. 筛选表中的行
   2. 防止未经许可的用户访问敏感数据
   3. 降低数据库的复杂程度
   4. 将多个物理数据库抽象为一个逻辑数据库

四、视图的语法

1. 使用 SQL 语句创建视图

CREATE VIEW view_name  
   AS
    <SELECT 语句>;

2. 使用 SQL 语句删除视图

DROP VIEW [IF EXISTS] view_name;

3. 使用 SQL 语句查看视图数据

SELECT 字段1, 字段2, …… FROM view_name;

4. 注意事项

   1. 视图中可以使用多个表
   2. 一个视图可以嵌套另一个视图
   3. 对视图数据进行添加、更新和删除操作直接影响所引用表中的数据
   4. 当视图数据来自多个表时，不允许添加和删除数据

5. 查看视图

   USE information_schema;
   SELECT * FROM views\G;
   

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

五、索引的基础知识

1. 什么是索引

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

2. 作用

   1. 大大提高数据库的检索速度
   2. 改善数据库性能

3. MySQL索引按存储类型分类

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

4. 常用索引类型

   1. 普通索引
      • 基本索引类型
      • 允许在定义索引的列中插入重复值和空值
   2. 唯一索引
      • 索引列数据不重复
      • 允许有空值
   3. 主键索引
      • 主键列中的每个值是非空、唯一的
      • 一个主键将自动创建主键索引
   4. 复合索引
      • 将多个列组合作为索引
   5. 全文索引
      • 支持值的全文查找
      • 允许重复值和空值
   6. 空间索引
      • 对空间数据类型的列建立的索引

六、索引的语法

1. 创建索引 默认不填是普通索引或者符合索引

CREATE [UNIQUE 唯一索引 |FULLTEXT 全文索引 |SPATIAL 空间索引 ] INDEX index_name
       ON table_name (column_name[length]…);

2. 删除索引

DROP  INDEX index_name ON table_name;

​ 删除表时，该表的所有索引同时会被删除

3. 创建索引的指导原则

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

      1. 频繁搜索的列
      2. 经常用作查询选择的列
      3. 经常排序、分组的列
      4. 经常用作连接的列（主键/外键）

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

      1. 仅包含几个不同值的列
      2. 表中仅包含几行

4. 使用索引时注意事项

   1. 查询时减少使用*返回全部列，不要返回不需要的列
   2. 索引应该尽量小，在字节数小的列上建立索引
   3. WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
   4. 避免在ORDER BY子句中使用表达式

5. 查看索引

show index from 表名；

• Table：创建索引的表
• Non_unique：索引是否非唯一
• Key_name：索引的名称
• Column_name：定义索引的列字段
• Seq_in_index：该列在索引中的位置
• Null：该列是否能为空值
• Index_type：索引类型

七、数据库的备份与恢复

1. mysqldump命令——MySQL一个常用的备份工具
   1. 将CREATE和INSERT INTO语句保存到文本文件
   2. 属于DOS命令

mysqldump [options 选项参数 ] –u username 用户名 –h host 登录主机名 –ppassword 密码 
dbname 备份的数据名 [tbname1[,tbname2……]需要备份的表名（省略则备份所有表）]>filename.sql 备份后的文件位置 名称

• mysqldump是DOS系统下的命令，
• 在使用时无须进入mysql命令行，否则将无法执行
• 为保证账户密码安全，命令中可不写密码，但参数“-p”必须有，回车后根据提示写密码
  3. 常用参数选项

参数	描述
-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语句的列上加上列名，在数据导入另一个数据库时有用
-c或–complete-insert	不写表的任何行信息，只转储表的结构

2. 备份文件包含的主要信息

   1. 备份后文件包含信息MySQL及mysqldump工具版本号
   2. 备份账户的名称
   3. 主机信息
   4. 备份的数据库名称
   5. SQL语句注释和服务器相关注释
   6. CREATE和INSERT语句

3. 备份数据库

   1. 使用mysql命令恢复数据库 dod命令

      mysql –u 用户名 –p [数据库名] < 备份文件路径与文件名
      

      1. 注意
         1. mysql为DOS命令
         2. 在执行该语句之前，必须在MySQL服务器中创建新数据库，如果不存在恢复数据库过程将会出错

   2. 使用source命令恢复数据库 SQL命令

      source 备份文件路径与文件名;
      

4. 导出数据——可实现数据库服务器间移动数据

SELECT  *  FROM tablename 
         [WHERE contion]
         INTO OUTFILE '备份文件路径与文件名' [OPTION]

4. 导入数据——导入数据前应确保目标表已存在！

 LOAD DATA INFILE '备份文件路径与文件名' INTO TABLE 表名 [OPTION]

八、总结

3. 导出数据——可实现数据库服务器间移动数据

~~~mysql
SELECT  *  FROM tablename 
       [WHERE contion]
       INTO OUTFILE '备份文件路径与文件名' [OPTION]

4. 导入数据——导入数据前应确保目标表已存在！

 LOAD DATA INFILE '备份文件路径与文件名' INTO TABLE 表名 [OPTION]

八、总结

九、用户管理

1. 创建普通用户

   create user `用户名`@`主机名 本地是localhost` [identified  by `密码`]
   

   CREATE USER语句创建的用户是未授权的

2. 创建用户同时授权或为已存在的用户授权

   grant 用户操作权限列表 (所有权限是all) on 库名.表名（可访问所有表是*.*）
   to `用户名`@`主机名 本地是localhost` identified  by `密码  可省略`
   

   1. 常用操作权限

      1. CREATE和DROP权限
      2. INSERT、DELETE、SELECT和UPDATE权限
      3. ALTER权限

   2. 注意

      1. 如果授予所有权限， priv_type可使用ALL
      2. 为所有数据库和表授权，权限范围可使用*.*

3. 查看所有用户即权限

   USE mysql;
   SELECT  * FROM user;
   

4. 使用mysqladmin命令修改root账户密码——dos命令

   mysqladmin –u 用户名 –p password "新密码"
   

   mysqladmin需在DOS命令行执行，回车后输入原密码

5. 使用SET命令修改用户密码

   SET PASSWORD  [FOR `用户名`@`主机名`]= PASSWORD("新密码")
   

   1. 注意

      1. 只有超级管理员用户（如root用户）才能修改其他用户密码，如果是普通用户，可修改自己的密码

   2. 示例

      1. #修改当前用户密码

         SET PASSWORD = PASSWORD("0000"); 
         

      2. #修改其他用户密码

         SET PASSWORD  FOR `teacher`@`localhost`= PASSWORD("8888");        
         

6. 删除用户

   DROP USER `用户1`@`主机名`[,`用户2`@`主机名`……];
   

   1. 示例

      DROP USER `student`@`localhost`;       
      

十、银行ATM存取款机系统

SQL语句

#修改张三（卡号为1010357612345678）银行卡密码为123456
UPDATE cardinfo SET `password`=123456 WHERE cardID=1010357612345678;
#修改李四（卡号为1010357612121134）银行卡密码为123123
UPDATE cardinfo SET `password`=123123 WHERE cardID=1010357612121134;
#查看已更改的数据
SELECT * FROM cardinfo;
#李四（卡号为1010357612121134）因银行卡丢失，申请挂失
UPDATE cardinfo SET IsReportLoss=1 WHERE cardID=1010357612121134;
#查看已更改的数据
SELECT c.cardID AS '卡号',curID AS '货币',savingName AS '储蓄种类',openDate AS '开户日期' ,openMoney AS '开户金额',balance AS '余额',`password` AS '密码',IsReportLoss AS '是否挂失'  FROM 
userinfo AS u INNER JOIN  cardinfo AS c ON c.customerID=u.customerID
INNER JOIN deposit AS d ON c.savingID=d.savingID;
#统计银行总存入总支取金额
SELECT tradeType,AVG(tradeMoney) FROM tradeinfo GROUP BY tradeType;
#查询本周开户的卡号,显示该卡相关信息
SELECT DISTINCT cardID FROM tradeInfo WHERE  tradeMoney=
	(SELECT MAX(tradeMoney) FROM tradeInfo
	 WHERE MONTH(tradeDate)=MONTH(NOW())
		AND YEAR(tradeDate)=YEAR(NOW()));
#查询本月交易金额最高的卡号
SELECT cardID FROM tradeInfo WHERE  tradeMoney=
	(SELECT MAX(tradeMoney) FROM tradeInfo WHERE MONTH(tradeDate)=MONTH(NOW()) AND YEAR(tradeDate)=YEAR(NOW() ) );
#查询挂失客户
SELECT * FROM cardinfo WHERE IsReportLoss=1;
#催款提醒业务
SELECT * FROM userInfo AS u INNER JOIN cardInfo AS c ON  u.customerID=c.customerID WHERE c.balance<200;


#输出银行客户记录
CREATE VIEW vw_userInfo AS SELECT customerID AS '客户编号',customerName AS '开户名',PID AS '身份证号',telephone AS '电话号码',address AS '居住地址' FROM userinfo;
#输出银行卡记录
CREATE VIEW vw_cardInfo AS SELECT c.cardID AS '卡号',customerName AS '客户',curID AS '货币种类',savingName AS '储蓄种类',openDate AS '开户日期' ,balance AS '余额',`password` AS '密码',IsReportLoss AS '是否挂失'  FROM 
userinfo AS u INNER JOIN  cardinfo AS c ON c.customerID=u.customerID
INNER JOIN deposit AS d ON c.savingID=d.savingID;
#输出银行卡信息
CREATE VIEW vw_tradeInfo AS SELECT tradeDate AS '交易日期', tradeType AS '交易类型',cardID AS '卡号' , tradeMoney AS '交易金额' ,remark AS '备注' FROM tradeinfo;

SELECT * FROM vw_userInfo;
SELECT * FROM vw_cardInfo;
SELECT * FROM vw_tradeInfo;

#从卡号为“1010357612121134”的账户中转出300元给卡号为“1010357612345678”的账户
BEGIN;
UPDATE cardinfo SET balance=-300 WHERE cardID=1010357612121134;
INSERT  INTO tradeinfo (cardID,tradeMoney,tradeType) VALUES (1010357612121134,300,'支取');
UPDATE cardinfo SET balance=+300 WHERE cardID=1010357612345678;
INSERT  INTO tradeinfo (cardID,tradeMoney,tradeType) VALUES (1010357612345678,300,'存入');
COMMIT;#操作成功
#rollback;#回滚事务