最新新闻头条,厦门seo关键词优化培训,做平台网站产品上传,互联网公司排名广州模拟银行实现ATM机取款系统
该系统使用( ( (SQLServer) ) )数据库
功能介绍#xff1a;
开户#xff08;到银行填写开户申请单#xff09;取钱存钱查询余额转账
根据需求设计相对应的数据库概念模型 流程分步详解 1 创建数据库Bank_db
--创建数据库
CREATE DATABASE B…模拟银行实现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(tradeMoney1)
)
--创建银行卡信息表
USE Bank_db
CREATE TABLE bankInfo
(
bankType varchar(32),
bankData varchar(32),
bankMoney float CHECK(bankMoney1),
bankBalance float CHECK(bankBalance1),
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 bankInfo6 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(tradeMoney1)
)
--创建银行卡信息表
USE Bank_db
CREATE TABLE bankInfo
(
bankType varchar(32),
bankData varchar(32),
bankMoney float CHECK(bankMoney1),
bankBalance float CHECK(bankBalance1),
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取款机系统已经做好了
