第9章 综合实战-银行ATM存取款机系统_数据库技术与应用课程设计银行atm存取款系统

在本章中需要掌握MySQL的用户管理，会使用SQL语句操作数据，会使用事物摆正数据的完整性，会创建并使用视图，会创建并使用索引，掌握数据库的备份和恢复，本章要求完成‘银行ATM存取款机系统’的设计与开发。

9.1 项目需求

某银行是一家民办的小型银行企业，现有十多万客户。现为该银行开发一套ATM存取款机系统，对银行日常的存取款业务进行管理，以保证数据的安全性，提高工作效率。
        要求根据银行存取款业务需求设计出符合第三范式的数据库结构，使用SQL创建数据库和表，并添加表约束，进行数据的增、删、改、查并按照银行的业务需求，运用视图和事物实现各项银行日常存款、取款和转账等业务曹组。

9.2 项目准备

1 环境准备

(1)数据库：MySQL5.7
(2)操作系统:Windows系列。

2技能准备

(1)会使用SQL语句创建数据库和表，并添加各种约束。
(2)会进行常见的SQL编程。
1 INSERT 语句 ：开户。
2 UODATE语句：存款或取款
3 DELETE语句：销户
4 聚合函数：月末汇总。
(3)会进行安全管理
安全管理：添加ATM系统的系统维护账号。
(4)会使用子查询并进行查询优化。
1 子查询：查询挂失账户的客户信息和催款业务等。
2 查询优化：查询指定卡号的交易记录。
(5)创建并使用视图：查询各表时显示有好的中文字段名，
(6)会创建存储过程并使用事物处理：本银行内账户间的转账。

9.3 核心知识解耦--MySQL用户管理

        在数据库中，通常包含很多重要的、敏感的数据，为了确保这些数据的安全性和完整性，在MySQL中，通过为不同的MySQL用户赋予不同的权限来实现这一目的，MySQL中的用户分为root用户和普通用户两种，前者是超级管理员，拥有最高的数据库权限，可以进行一切数据操作；后者只能拥有该用户被赋予的权限。

1 创建普通用户

安装MySQL后默认提供root用户管理账号，由于root用户拥有超级用户权限，因此容易引发误操作所导致的数据不安全问题。在实际开发中，除了一些必要的场合外，一般不建议使用root用户登录MySQL服务器，DBA(数据库管理员)为不同的数据库使用者创建一系列普通地用户，赋予不同的权限，以保障数据的安全性。创建用户账户的语法格式如下：
CREATE USER 'username'@'host' [IDENTIFIED BY [PASSWORD] `password`] ;
其中，IDENTIFIED BY 语句用来设置密码，默认时密码为空。其它参数如下。
username:表示常见的用户名。
host:表示指定用户登录的主机名，如果只是本地用户可使用‘localhost’，如果该用户可登录任何远程主机，可使用通配符‘%’。
PASSWORD：表示使用哈希值设置密码，为可选项。
password:表示用户登录时所应用的明文密码。
【示例1】
创建本地用于teacher,密码为123456，本地用户student,不需要密码。
SQL语句如下
CREATE USER teacher@localhost IDENTIFIED BY 123456;
CREATE USER student@localhost;
创建用户之后，可通过系统数据库mysql中的user表，查看已存在的用户，SQL语句如下：
use mysql;
SELECT host,user,authentication_string,select_priv,Insert_priv,Update_priv,Delete_priv FROM user\G;
从图中可以看出，user表中的host,user,authentication_string分别对应创建用户时指定的主机名、用户名、密码的哈希值。除此之外，还有一系列以‘_priv’字符串结尾的字段，这些字段决定了用户权限，它们的值只有Y和N，Y表示用户有对应的权限，N表示用户没有对应的权限，默认值是N。不难看出，使用CREATE USER语句创建的用户是未授权的，下面介绍一种能够同时授权的创建用户方式。
注意：user表中User和Host字段区分大小写，如‘STUDENT’，‘student’是两个不同的客户，在条件查询时需要指定正确的用户名和主机名，
如果删除了user表中已经存在的用户，例如，删除已创建的student用户，然后重新创建student用户，可能会出现错误提示。如果遇到类似问题，可以先运行‘FLUSH PRIVILEGES’，然后再创建用户。

2 执行GRANT语句创建用户并授权

用户授权需使用GRANT语句，其语法格式如下：
GRANT priv_type ON databasename.tablename TO username@host [IDENTIFIED BY [PASSWORD] password][WITH GRANT OPTION];
priv_type:表示设置的用户操作权限，如果授予所有权限可使用ALL。MySQL中的权限有很多，以下列出了常用的数据库或表操作权限。
1CREATE和DROP权限，可以创建数据库和表，或删除已有数据库和表。
2 INSERT、DELETE、SELECT和UPDATE权限，允许在一个数据库现有的表上实现增、删、改、等操作。
3 ALTER权限，可以使用LTER TABLE来更改表的结构和重新命名表。
databasename.tablename:表示所创建用户账号的权限范围，即只能在指定数据库上使用此权限，如果给所有数据库和表授权，可以使用‘*.*’.
WITH GRANT OPTION:表示对新建立的用户赋予GRANT权限，可选项。
其余部分与CREATE USER语句一致。
【示例2】
创建名为xiaoming,密码为123456的本地用户账户，并给该用户赋予MySchool数据库中student表的增加数据和查询数据权限。
FLUSH PRIVILEGES;
GRANT INSERT,SELECT ON myschool.student TO xioaming@localhost IDENTIFIED BY '123456';

【示例3】
使用GRANT语句为已经创建的用户授权。
如为student@localhost用户授予MySchool数据库中view_student_result视图的查询权限。
GRANT SELECT ON myschool.view_student_result TO student@localhost;

3 使用mysqladmin命令修改root用户密码

grant all privileges on *.* to 'skip-grants user'@'skip-grants host' identified by ".";
mysqladmin -u username -p password 'newpassword';

【示例4】将root用户密码修改为1234
在DOS窗口中执行：
mysqladmin -u root -p password "1234";
使用mysql命令修改密码的用户应该为root用户。

4 使用SET命令修改用户密码

用户登录MySQL服务器后，可以使用SET命令修改当前用户密码，语法格式如下：
SET PASSWORD [FOR 'username'@host]=PASSWORD("newpassword");
其中PASSWORD()函数用于对密码加密，“newpassword”是设置的新密码。如果修改非当前登录用户的密码，则需使用FOR关键字指定要修改的用户名。
只有ROOT用户才能修改其他用户的密码，如果是普通用户，可省略FOR子句。

【示例5】
当登录用户为root时，使用SET命令将root密码修改为‘0000’，将teacher账户密码修改为‘8888’。

#修改当前登录用户密码
SET PASSWORD=PASSWORD("0000");
#修改其他用户密码
SET PASSWORD FOR teacher@loaclhost=PASSWORD("8888");

5删除普通客户

在MySQL数据库中，可以使用DROP USER语句删除普通用户，语法格式如下：
DROP USER username1@host [,username2@host];
该语句可以用于删除一个或多个MySQL普通用户。

【示例6】删除本地student用户
DROP USER student@localhost;

9.4 难点分析

该银行的ATM存取款业务如下
(1)银行未客户提供了各种银行存取款业务，如下：
 

(2)每个客户凭个人身份证在银行可以开设多个银行账户。开设账户时，客户需要提供的开户数据如下：
 

(3)银行为每个账户提供一张银行卡，每张银行卡可以存入一种币种的存款：银行卡账户信息如下：
 

(4)客户持银行卡在ATM存取款机上输入密码，经系统验证身份后可以办理存款、取款和转账等业务。银行在为客户办理业务时，需要记录每一笔账，账目交易信息表：
 

(5)该银行要求这套软件能实现银行客户的开户、存款、取款、转账各余额查询等业务，摆正银行储蓄业务方便、快捷，同时保证银行业务数据的安全性。
(6)为了使开发人员尽快了解银行业务，该银行提供了银行卡手工账户和存取款单据的样本数据，仅供项目开发时参考。
银行卡手工账户信息

银行卡存取款单据样本数据
 

9.5 项目实现思路

1 数据库设计

(1)完成银行ATM存取款机系统数据库设计
明确银行ATM存取款机系统地实体、实体属性以及实体之间的关系。
提示：在充分理解银行业务需求后，围绕银行的需求进行分析，确认与银行ATM存取款机有紧密关系的实体，并得到每个实体的必要属性。
(2)根据业务需求分析多个实体之间的关系，实体之间的关系可以是一对一、一对多和多对多
(2)为银行ATM存取款机系统数据库绘制E-R图。要求如下：
1 使用数据库设计工具，把设计数据库第一步的结果(即分析得到的银行ATM存取款机系统地实体，实体属性以及实体之间的关系)，用E-R图表示。
2 E-R图中要体现各实体之间的关系。
(3)为银行ATM存取款机绘制数据库模型图，要求如下；
1 使用数据库设计工具，把E-R图中的实体转换为数据库中的表对象，并为表中每一列指定数据类型和长度。
2 在数据库模型汇总要标识表的主键和外键。
(4)规范数据库结构设计
使用第三范式对数据库表结构进行规范化。
提示：规范数据库表结构的同时，要考虑软件运行性能。必要时可以违反第三范式的要求，适当增加冗余数据，减少表链接，以空间换时间。

2 创建数据库以及登录用户并授权

(1)创建数据库
使用CREATE DATABASE语句创建ATM存取款机系统数据库bankDB。
创建数据库时要求检测是否存在数据库bankDB，如果存在，则因该先删除再创建。
(2)创建登录用户并授权
创建普通用户bankMaster,可以再任意主机登录MySQL服务器，具有数据库bankDB的所有权限，密码为1234.
从系统数据MySQL的user表中查看已创建的用户信息。
使用bankMaster用户登录MySQL服务器
 

3 创建表、约束

(1)创建表
根据前面设计出的ATM存取款机系统地数据库结构，使用CREATE TABLE语句创建表结构。
创建表时要求检测是都存在该表结构，如果存在，则应该先删除再创建。
(2)创建外键约束根据银行业务，为相应的表添加外键约束，使用ALTER TABLE ADD语句。
在为表添加外键约束时，要先添加主表的主键约束，再添加子表的外键约束。

4 插入测试数据

(1)使用SQL语句向已经创建数据库的每个表中插入测试信息。
(2)卡号由人工指定，向相关表中插入如下数据：
 

(3)插入交易信息：张三的卡号取款900元，李四的卡号存款5000元，要求保存交易记录，以便客户查询和进行银行业务统计。
        例如，当张三取款900元时，会向交易信息表(transInfo)中添加一条交易数据，同时应自动更新银行卡信息表(cardInfo)中现有约(减少900元)
注意：插入测试数据时需要注意以下几点。
(1)在使用SQL语句插入各表中的数据时要保证业务数据的一致性和完整性。
(2)当客户持银行卡办理存款、取款业务时，银行要记录每笔交易账目，并修改该银行卡的存款金额。
(3)每个表至少要插入3-5条记录。

提示：注意各表中数据插入的顺序，为了保证主外键的关系，建议先插入主表中的数据，在插入子表中的数据。
(2)客户取款时需要记录‘交易账目’，并修改存款金额，它可能需要分为一下两不完成。
在交易信息表中插入交易记录
INSERT INTO transInfo (transType,cardId,transMoney) VALUES('支取','1010',900);
2更新银行卡信息表中的现有余额
UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010';

5 模拟常规业务

编写SQL语句实现银行的如下日常业务。
(1)修改客户密码
修改张三的银行卡密码为12345，修改李四的密码为12332
(2)办理银行卡挂失
李四由于银行卡丢失，申请挂失。
(3)统计银行总存入金额和总支取金额
(4)查询本周开户信息
提示：查询本周开户的卡号。
(5)查询本月交易金额最高的卡号。
(6)查询挂失客户。
(7)催款业务。

6 创建、使用客户友好信息视图

(1)为了向客户提供友好的用户界面，使用SQL语句创建如下三个视图，并使用这些视图查询输出各表信息。
view_userInfo:输出银行客户记录。
view_cardInfo:输出银行卡记录
view_transInfo:输出银行卡的交易记录。
(2)各表显示的列全为中文。
 

7 创建存储过程完成转账

从卡号为‘1010’的账户中转出300元给卡号‘1010’的账户，即李四转账300元给张三。要求：创建存储过程，并使用事务处理。转账成功时，提交事物。