模擬銀行實現ATM機取款系統

該系統使用( ( (SQLServer) ) )數據庫

功能介紹：

開戶（到銀行填寫開戶申請單）

取錢

存錢

查詢余額

轉賬

根據需求設計相對應的數據庫概念模型

流程分步詳解
@1 創建數據庫Bank_db

--創建數據庫 CREATE DATABASE Bank_db ON PRIMARY ( NAME = 'Bank_db_data', FILENAME = 'D:\DATA\Bank_db_data.mdf', SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 10% ) LOG ON ( NAME = 'Bank_db_log', FILENAME = 'D:\DATA\Bank_db_log.ldf', SIZE = 1MB, FILEGROWTH = 1MB )

@@ 2 根據E-R圖建表
注意:身份證號不能重復,聯系電話11位,所有涉及到的金額不能低于1元,默認密碼6個8

--創建用戶信息表 USE Bank_db CREATE TABLE userInfo ( userName varchar(32), userCard varchar(32) UNIQUE, userPhone varchar(11), userAddress varchar(128) ) --創建交易信息表 USE Bank_db CREATE TABLE tradeInfo ( tradeData varchar(16), tradeMark varchar(32), tradeType varchar(16), tradeMoney float CHECK(tradeMoney>1) ) --創建銀行卡信息表 USE Bank_db CREATE TABLE bankInfo ( bankType varchar(32), bankData varchar(32), bankMoney float CHECK(bankMoney>1), bankBalance float CHECK(bankBalance>1), bankPassword varchar(16) DEFAULT '888888', bankLose varchar(16) )

@@@3 插入測試數據:三張表都要添加數據,并且張三賬戶的余額不能低于2000
客戶信息表:
‘張三’,‘123456789012345’,‘01067898978’,‘北京海淀’
‘李四’,‘321245678912345678’,‘04784444333’
‘王五’,‘321245678912345679’,‘13566669999’,‘河南信陽’
‘李七’,‘321245678912345677’,‘13577778888’,‘河南洛陽’

--插入用戶信息表 INSERT INTO userInfo VALUES('張三','123456789012345','01067898978','北京海淀') INSERT INTO userInfo VALUES('李四','321245678912345678','04784444333','') INSERT INTO userInfo VALUES('王五','321245678912345679','13566669999','河南信陽') INSERT INTO userInfo VALUES('李七','321245678912345677','13577778888','河南洛陽')--插入交易信息表 INSERT INTO tradeInfo VALUES('2019/7/3/7','1010111112121134','轉賬',1000) INSERT INTO tradeInfo VALUES('2019/7/4/9','1010 3576 1212 1134','存款',2000) INSERT INTO tradeInfo VALUES('2019/7/5/8','987653321','存款',5000) INSERT INTO tradeInfo VALUES('2019/7/7/3','987655321','取款',3000)--插入銀行卡信息表 INSERT INTO bankInfo VALUES('轉賬','2019/7/3/7',1000,30000,'','否') INSERT INTO bankInfo VALUES('存款','2019/7/4/9',2000,32000,'','否') INSERT INTO bankInfo VALUES('存款','2019/7/5/8',5000,37000,'','否') INSERT INTO bankInfo VALUES('取款','2019/7/7/3',3000,34000,'','否')

@@@@ 4 常規業務模擬
1**. 修改密碼**
張三（卡號為1010111112121134）修改銀行卡密碼為123456
2. 銀行卡掛失
李四（卡號為1010 3576 1212 1134）因銀行卡丟失，申請掛失
3. 查詢余額3000~6000之間的定期卡號,顯示該卡相關信息
4. 查詢本月交易金額最高的卡號
5. 查詢掛失帳戶信息

--把張三的密碼修改為123456 SELECT tradeType FROM tradeInfo WHERE tradeMark = '1010111112121134' UPDATE bankInfo SET bankPassword = '123456' WHERE bankType IN(SELECT tradeType FROM tradeInfo WHERE tradeMark = '1010111112121134') --查詢修改后張三的密碼 SELECT bankPassword FROM bankInfo WHERE bankType IN(SELECT tradeType FROM tradeInfo WHERE tradeMark = '1010111112121134')--李四的銀行卡修改為掛失 SELECT tradeType FROM tradeInfo WHERE tradeMark = '1010 3576 1212 1134' UPDATE bankInfo SET bankLose = '掛失' WHERE bankType IN(SELECT tradeType FROM tradeInfo WHERE tradeMark = '1010 3576 1212 1134') --查詢李四的掛失 SELECT bankLose FROM bankInfo WHERE bankType IN(SELECT tradeType FROM tradeInfo WHERE tradeMark = '1010 3576 1212 1134')--查詢余額3000~6000之間的定期卡號,顯示該卡相關信息 SELECT * FROM bankInfo WHERE bankBalance BETWEEN 3000 AND 6000--查詢本月交易金額最高的卡號 SELECT TOP 1 tradeMark FROM tradeInfo ORDER BY tradeMoney DESC--查詢掛失帳戶信息 SELECT * FROM bankInfo WHERE bankLose = '掛失'

@@@@@ 5 索引和視圖
**1. 創建視圖：**為向客戶顯示信息友好，查詢各表，要求字段全為中文字段名， 3個表對應的視圖名稱分別為： view_userInfo,view_cardInfo,view_transInfo

--索引和視圖 --列的別名（中文字段） SELECT userName AS '開戶名',userCard AS '身份證號', userPhone AS '聯系電話', userAddress AS '居住地址'FROM userInfo SELECT tradeData AS '交易日期',tradeMark AS '卡號', tradeType AS '交易類型', tradeMoney AS '交易金額'FROM tradeInfo SELECT bankType AS '存款類型',bankData AS '開戶日期', bankMoney AS '開戶金額', bankBalance AS '余額',bankPassword AS '密碼', bankLose AS '是否掛失' FROM bankInfoCREATE VIEW view_userInfo ON FROM userInfo CREATE VIEW view_cardInfo ON FROM tradeInfo CREATE VIEW view_transInfo ON FROM bankInfo

@@@@@@6 T-SQL編程:存取款業務
張三的卡號（1010357612345678）取款900元，李四的卡號（1010357612121134）存款5000元，要求保存交易記錄，以便客戶查詢和銀行業務統計。
說明：當存錢或取錢（如300元）時候，會往交易信息表中添加一條交易記錄，同時應更新銀行卡信息表中的現有余額（如增加或減少300元）；

--存取款業務 DECLARE @zmoney float DECLARE @lmoney floatSELECT @zmoney = tradeMoney FROM tradeInfo WHERE tradeMark = '1010111112121134' UPDATE tradeInfo SET @zmoney = @zmoney - 900 WHERE tradeMark = '1010111112121134' PRINT @zmoneySELECT @lmoney = tradeMoney FROM tradeInfo WHERE tradeMark = '1010 3576 1212 1134' UPDATE tradeInfo SET @lmoney = @lmoney + 5000 WHERE tradeMark = '1010 3576 1212 1134' PRINT @lmoney

所有的步驟都已經展示給大家了@@@

如果有想看整個完整體系的話請看下面展示：

整體流程展示

--創建數據庫 CREATE DATABASE Bank_db ON PRIMARY ( NAME = 'Bank_db_data', FILENAME = 'D:\DATA\Bank_db_data.mdf', SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 10% ) LOG ON ( NAME = 'Bank_db_log', FILENAME = 'D:\DATA\Bank_db_log.ldf', SIZE = 1MB, FILEGROWTH = 1MB ) --創建用戶信息表 USE Bank_db CREATE TABLE userInfo ( userName varchar(32), userCard varchar(32) UNIQUE, userPhone varchar(11), userAddress varchar(128) ) --創建交易信息表 USE Bank_db CREATE TABLE tradeInfo ( tradeData varchar(16), tradeMark varchar(32), tradeType varchar(16), tradeMoney float CHECK(tradeMoney>1) ) --創建銀行卡信息表 USE Bank_db CREATE TABLE bankInfo ( bankType varchar(32), bankData varchar(32), bankMoney float CHECK(bankMoney>1), bankBalance float CHECK(bankBalance>1), bankPassword varchar(16) DEFAULT '888888', bankLose varchar(16) ) --插入用戶信息表 INSERT INTO userInfo VALUES('張三','123456789012345','01067898978','北京海淀') INSERT INTO userInfo VALUES('李四','321245678912345678','04784444333','') INSERT INTO userInfo VALUES('王五','321245678912345679','13566669999','河南信陽') INSERT INTO userInfo VALUES('李七','321245678912345677','13577778888','河南洛陽')--插入交易信息表 INSERT INTO tradeInfo VALUES('2019/7/3/7','1010111112121134','轉賬',1000) INSERT INTO tradeInfo VALUES('2019/7/4/9','1010 3576 1212 1134','存款',2000) INSERT INTO tradeInfo VALUES('2019/7/5/8','987653321','存款',5000) INSERT INTO tradeInfo VALUES('2019/7/7/3','987655321','取款',3000)--插入銀行卡信息表 INSERT INTO bankInfo VALUES('轉賬','2019/7/3/7',1000,30000,'','否') INSERT INTO bankInfo VALUES('存款','2019/7/4/9',2000,32000,'','否') INSERT INTO bankInfo VALUES('存款','2019/7/5/8',5000,37000,'','否') INSERT INTO bankInfo VALUES('取款','2019/7/7/3',3000,34000,'','否')--把張三的密碼修改為123456 SELECT tradeType FROM tradeInfo WHERE tradeMark = '1010111112121134' UPDATE bankInfo SET bankPassword = '123456' WHERE bankType IN(SELECT tradeType FROM tradeInfo WHERE tradeMark = '1010111112121134') --查詢修改后張三的密碼 SELECT bankPassword FROM bankInfo WHERE bankType IN(SELECT tradeType FROM tradeInfo WHERE tradeMark = '1010111112121134')--李四的銀行卡修改為掛失 SELECT tradeType FROM tradeInfo WHERE tradeMark = '1010 3576 1212 1134' UPDATE bankInfo SET bankLose = '掛失' WHERE bankType IN(SELECT tradeType FROM tradeInfo WHERE tradeMark = '1010 3576 1212 1134') --查詢李四的掛失 SELECT bankLose FROM bankInfo WHERE bankType IN(SELECT tradeType FROM tradeInfo WHERE tradeMark = '1010 3576 1212 1134')--查詢余額3000~6000之間的定期卡號,顯示該卡相關信息 SELECT * FROM bankInfo WHERE bankBalance BETWEEN 3000 AND 6000--查詢本月交易金額最高的卡號 SELECT TOP 1 tradeMark FROM tradeInfo ORDER BY tradeMoney DESC--查詢掛失帳戶信息 SELECT * FROM bankInfo WHERE bankLose = '掛失'--索引和視圖 --列的別名（中文字段） SELECT userName AS '開戶名',userCard AS '身份證號', userPhone AS '聯系電話', userAddress AS '居住地址'FROM userInfo SELECT tradeData AS '交易日期',tradeMark AS '卡號', tradeType AS '交易類型', tradeMoney AS '交易金額'FROM tradeInfo SELECT bankType AS '存款類型',bankData AS '開戶日期', bankMoney AS '開戶金額', bankBalance AS '余額',bankPassword AS '密碼', bankLose AS '是否掛失' FROM bankInfoCREATE VIEW view_userInfo ON FROM userInfo CREATE VIEW view_cardInfo ON FROM tradeInfo CREATE VIEW view_transInfo ON FROM bankInfo--存取款業務 DECLARE @zmoney float DECLARE @lmoney floatSELECT @zmoney = tradeMoney FROM tradeInfo WHERE tradeMark = '1010111112121134' UPDATE tradeInfo SET @zmoney = @zmoney - 900 WHERE tradeMark = '1010111112121134' PRINT @zmoneySELECT @lmoney = tradeMoney FROM tradeInfo WHERE tradeMark = '1010 3576 1212 1134' UPDATE tradeInfo SET @lmoney = @lmoney + 5000 WHERE tradeMark = '1010 3576 1212 1134' PRINT @lmoney

ATM取款機系統已經做好了！！！

總結

以上是生活随笔為你收集整理的ATM取款机系统的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。