讲稿优化myschool数据库设计tp09

1、项目案例：银行ATM存取款机系统第九章训练的技能点训练的技能点使用使用T-SQL语句创建数据库和表结构语句创建数据库和表结构使用使用T-SQL语句编程实现用户业务语句编程实现用户业务使用事务和存储过程封装业务逻辑使用事务和存储过程封装业务逻辑使用视图简化复杂的数据查询使用视图简化复杂的数据查询2任务描述任务描述系统功能概述系统功能概述系统概述系统概述开户开户填写开户申请单填写开户申请单自动开通账户自动开通账户存取款存取款查询余额查询余额转账转账演示案例：银行演示案例：银行ATM存取款机系统存取款机系统3系统开发步骤系统开发步骤问题分析：整体开发思路问题分析：整体开发思路2-1明确需求明确需求数

2、据库设数据库设计计编码实现编码实现功能功能测试测试绘制绘制E-R图图绘制数据库模型绘制数据库模型图图使用三大范式规使用三大范式规范数据库结构范数据库结构4问题分析：整体开发思路问题分析：整体开发思路2-2银行业务处理银行业务处理每个用户可以开设多个银行卡账户每个用户可以开设多个银行卡账户每个银行卡账户可以有多笔交易每个银行卡账户可以有多笔交易设计符合第三范式的数据库结构设计符合第三范式的数据库结构根据银行业务绘制根据银行业务绘制E-R图图绘制数据库模型图绘制数据库模型图使用三大范式规范数据库结构使用三大范式规范数据库结构实现实现“ATM取款机系统取款机系统”的相关业务的相关业务5难点分析难点分

3、析1：数据库设计：数据库设计数据库设计步骤数据库设计步骤E-R图要素图要素实体实体关系关系一对一关系一对一关系一对多关系一对多关系多对多关系多对多关系数据库模型图数据库模型图数据库设计的三大规范数据库设计的三大规范第一范式第一范式第二范式第二范式第三范式第三范式6难点分析难点分析2：理解业务：理解业务ATM取款业务取款业务卡号构成卡号构成假设某行由假设某行由16位数字构成，每位数字构成，每4位数字一组，位数字一组，中间用空格隔开中间用空格隔开如：如：1010 3576 1001 1202前前8位数字固定的，表示发卡银行识别号（位数字固定的，表示发卡银行识别号（BIN）前前8位是位是1010 3

4、576后后8位中前位中前7位是开户顺序号，后位是开户顺序号，后1位是校验位位是校验位开户顺序号从开户顺序号从1开始，每开一户，该值加一开始，每开一户，该值加一校验位算法校验位算法以以8位随机数替代卡号的后位随机数替代卡号的后8位数字位数字客户用身份证开设账户客户用身份证开设账户开户金额不得小于开户金额不得小于1元元存款余额不得小于存款余额不得小于1元元7难点分析难点分析3：理解业务：理解业务ATM取款业务取款业务存款存款记录交易发生额，增加账户余额记录交易发生额，增加账户余额取款取款记录交易发生额，减少账户余额记录交易发生额，减少账户余额转账转账关系到二个账户，总账平衡关系到二个账户，总账平衡

5、转出账户：记录交易转入金额，减少该账户转出账户：记录交易转入金额，减少该账户的余额的余额转入账户：记录交易转出金额（同转入金额转入账户：记录交易转出金额（同转入金额），增加该账户余额），增加该账户余额8难点分析难点分析4：来理解业务：来理解业务ATM取款业务取款业务修改密码修改密码变更客户密码变更客户密码挂失挂失客户填写特殊业务申请单（挂失申请书），客户填写特殊业务申请单（挂失申请书），停止该账户发生存款支取交易停止该账户发生存款支取交易在客户信息中设置列记录客户当前账户状态在客户信息中设置列记录客户当前账户状态9开发计划开发计划用例用例1：数据库设计：数据库设计 40分钟分钟用例用例2：建库

6、、建表、建约束：建库、建表、建约束 20分钟分钟用例用例3：插入测试数据：插入测试数据 10分钟分钟用例用例4：模拟常规业务：模拟常规业务 30分钟分钟用例用例5：利用视图实现较复杂的数据查询：利用视图实现较复杂的数据查询 30分钟分钟用例用例6：使用存储过程实现业务处理：使用存储过程实现业务处理 90分钟分钟用例用例7：利用事务实现较复杂的数据更新：利用事务实现较复杂的数据更新 30分钟分钟10用例用例1：数据库设计：数据库设计8-1 需求说明需求说明根据银行根据银行ATM取款机业务处理流程设计数据取款机业务处理流程设计数据库库绘制绘制E-R图图绘制数据库模型图绘制数据库模型图使用第三范式规

7、范数据库表结构使用第三范式规范数据库表结构思路分析思路分析按照数据库设计步骤确定实体及实体关系按照数据库设计步骤确定实体及实体关系使用使用Visio绘制绘制E-R图图使用使用Visio绘制数据库模型图绘制数据库模型图使用第三范式规范数据库设计使用第三范式规范数据库设计讲解需求说明讲解需求说明11用例用例1：数据库设计：数据库设计8-2 难点提示难点提示寻找、确定寻找、确定ATM取款机系统的实体及实体间取款机系统的实体及实体间关系关系根据业务确定实体根据业务确定实体确定实体之间关系确定实体之间关系使用三大范式规范数据库设计使用三大范式规范数据库设计表内的每一个值都只能被表达一次表内的每一个值都只

8、能被表达一次表内的每一行都应该被唯一的标识（主键）表内的每一行都应该被唯一的标识（主键）表内不应该存储依赖于其他键的非键信息表内不应该存储依赖于其他键的非键信息完成时间：完成时间：40分钟分钟12用例用例1：数据库设计：数据库设计8-3提交设计文档提交设计文档小组讨论确定实体及实体关系（小组讨论确定实体及实体关系（10分钟）分钟）绘制绘制E-R图（图（10分钟）分钟）绘制数据库模型图（绘制数据库模型图（10分钟）分钟）使用第三范式规范数据库设计（使用第三范式规范数据库设计（10分钟）分钟）要求要求互相验证完成的程序功能互相验证完成的程序功能小组长检查本组完成情况小组长检查本组完成情况功能测试功

9、能测试13用例用例1：数据库设计：数据库设计8-4绘制绘制ER图、数据库模型图、规图、数据库模型图、规范数据库表结构范数据库表结构14客户信息表结构（客户信息表结构（UserInfo） 列名称列名称数据数据类型类型说明customerIDint客户编号客户编号自动编号（标识列），从自动编号（标识列），从1开始，主键开始，主键customerNamevarchar开户名开户名必填必填PIDvarchar身份证号身份证号必填，只能是必填，只能是18位或位或15位，位，身份证号唯一约束身份证号唯一约束telephonevarchar联系电话联系电话必填，格式为必填，格式为xxxx-xxxxxxxx或

10、或xxx-xxxxxxxx或手机号或手机号11位位addressvarchar居住地址居住地址可选输入可选输入用例用例1：数据库设计：数据库设计8-515列名称列名称数据数据类型类型说明说明cardIDchar卡号卡号必填，主键，银行的卡号规则和电话号码一样，必填，主键，银行的卡号规则和电话号码一样，一般前一般前8位代表特殊含义，如某总行某支行等。位代表特殊含义，如某总行某支行等。假定该行要求其营业厅的卡号格式为：假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxxx。每。每4位号码后有空格，卡号一位号码后有空格，卡号一般是随机产生般是随机产生curIDvarchar货币种类

11、货币种类外键，必填，默认为外键，必填，默认为RMBsavingIDvarchar存款类型存款类型外键，必填外键，必填openDatedatetime开户日期开户日期必填，默认为系统当前日期必填，默认为系统当前日期openMoneydecimal开户金额开户金额必填，不低于必填，不低于1元元balancedecimal余额余额必填，不低于必填，不低于1元元passwordvarchar密码密码必填，必填，6位数字，开户时默认为位数字，开户时默认为6个个“8”IsReportLoss bit是否挂失是否挂失必填，是必填，是/否值，默认为否值，默认为“否否”customerIDint客户编号客户编号

12、外键，必填外键，必填银行卡信息表结构（银行卡信息表结构（CardInfo） 用例用例1：数据库设计：数据库设计8-616交易信息表结构交易信息表结构 （TradeInfo）用例用例1：数据库设计：数据库设计8-7字段名称字段名称数据类型数据类型说明transDatedatetime交易日期交易日期必填，默认为系统当前日期必填，默认为系统当前日期cardIDvarchar卡号卡号外键，必填外键，必填transTypeint交易类型交易类型必填，只能是存入必填，只能是存入/支取支取transMoneydecimal交易金额交易金额必填，大于必填，大于0remarkvarchar备注备注可选输入，其

13、他说明可选输入，其他说明17存款类型表结构（存款类型表结构（Deposit） 用例用例1：数据库设计：数据库设计8-8字段名称字段名称数据数据类型类型说明savingIDint存款类型号存款类型号自动编号（标识列）从自动编号（标识列）从1 1开始，主键开始，主键savingNamevarchar存款类型名称存款类型名称必填必填descripvarchar描述描述可空可空18共性问题集中讲解共性问题集中讲解常见问题及解决办法常见问题及解决办法代码规范问题代码规范问题调试技巧调试技巧19用例用例2：建库、建表、建约束：建库、建表、建约束4-1 需求说明需求说明使用使用SQL语言创建数据库语言创建数

14、据库BankDB数据库保存在数据库保存在D:bank目录下，文件增长率为目录下，文件增长率为15%使用使用SQL语言创建表语言创建表存款类型表存款类型表客户信息表客户信息表银行卡信息表银行卡信息表交易信息表交易信息表使用使用SQL语言在每个表上添加约束语言在每个表上添加约束主键约束、外键约束、主键约束、外键约束、CHECK约束、默认约约束、默认约束、束、非空约束非空约束讲解需求说明讲解需求说明20用例用例2：建库、建表、建约束：建库、建表、建约束4-2 建库与建表建库与建表-建库语句：建库语句：CREATE DATABASE bankDB ON ( NAME=, FILENAME=, SIZE

15、=, FILEGROWTH= ) LOG ON ( )-建表语句：建表语句：CREATE TABLE 表名表名 ( customerID INT IDENTITY(1,1), customerName CHAR(8) NOT NULL, )文件增长率文件增长率数据文件数据文件日志文件日志文件自动编号，从自动编号，从1开始开始非空非空/必填必填完成时间：完成时间：10分钟分钟21用例用例2：建库、建表、建约束：建库、建表、建约束4-3加约束、建关系部分加约束、建关系部分 -建约束语句：建约束语句：ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIM

16、ARY KEY(cardID), CONSTRAINT CK_cardID CHECK(cardID LIKE 1010 3576 0-9), CONSTRAINT DF_curType DEFAULT(RMB) FOR curType CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID), CONSTRAINT UQ_PID UNIQUE(PID), .主键约束主键约束检查约束检查约束外键约束（建关系）外键约束（建关系）唯一约束唯一约束默认约束默认约束完成时间：完成时间：10分钟分钟2

17、2共性问题集中讲解共性问题集中讲解常见问题及解决办法常见问题及解决办法代码规范问题代码规范问题调试技巧调试技巧23用例用例2：建库、建表、建约束：建库、建表、建约束4-4代码审查代码审查审查创建数据库、表的审查创建数据库、表的SQL代码代码审查添加约束的审查添加约束的SQL代码代码要求要求互相验证完成的程序功能互相验证完成的程序功能小组长检查本组完成情况小组长检查本组完成情况功能测试功能测试24用例用例3：插入测试数据：插入测试数据4-1 需求说明需求说明使用使用SQL语言向每个表中插入至少语言向每个表中插入至少5条记录条记录思路分析思路分析先增加主表的记录，后增加子表的记录先增加主表的记录，

18、后增加子表的记录难点提示难点提示插入数据表的先后顺序插入数据表的先后顺序插入客户记录时，先要查询插入客户记录时，先要查询完成时间：完成时间：10分钟分钟25用例用例3：插入测试数据：插入测试数据4-2张三和李四开户：张三和李四开户：SET NOCOUNT ON -不显示受影响的条数信息不显示受影响的条数信息INSERT INTO userInfo(customerName,PID,telephone,address ) VALUES(张三张三,123456789012345, ,北京海淀北京海淀)INSERT INTO cardInfo(cardID,savingType,openMoney

19、,balance,customerID) VALUES(1010 3576 1234 5678, 活期活期,1000,1000,1)INSERT INTO userInfo(customerName,PID,telephone) VALUES(李四李四, , )INSERT INTO cardInfo(cardID,savingType,openMoney,balance, customerID) VALUES(1010 3576 1212 1134,定期定期,1,1,2)SELECT * FROM userInfoSELECT * FROM cardInfoGO26用例用例3：插入测试数据：

20、插入测试数据4-3张三的卡号取款张三的卡号取款900元，李四的卡号存款元，李四的卡号存款5000元元/*-交易信息表插入交易记录交易信息表插入交易记录-*/INSERT INTO transInfo(transType,cardID,transMoney) VALUES(支取支取,1010 3576 1234 5678,900) /*-更新银行卡信息表中的现有余额更新银行卡信息表中的现有余额-*/UPDATE cardInfo SET balance=balance-900 WHERE cardID=1010 3576 1234 5678/*-交易信息表插入交易记录交易信息表插入交易记录-*/

21、INSERT INTO transInfo(transType,cardID,transMoney) VALUES(存入存入,1010 3576 1212 1134,5000) /*-更新银行卡信息表中的现有余额更新银行卡信息表中的现有余额-*/UPDATE cardInfo SET balance=balance WHERE cardID=1010 3576 1212 1134GO27用例用例3：插入测试数据：插入测试数据4-4功能测试功能测试使用查询语句测试插入的数据是否正确（使用查询语句测试插入的数据是否正确（5分分钟）钟）代码审查代码审查审查插入测试数据的审查插入测试数据的SQL代码（

22、代码（5分钟）分钟）要求要求互相验证完成的程序功能互相验证完成的程序功能小组长检查本组完成情况小组长检查本组完成情况功能测试功能测试演示案例演示案例3：插入测试数据：插入测试数据28用例用例4：模拟常规业务：模拟常规业务4-1需求说明需求说明修改客户密码修改客户密码办理银行卡挂失办理银行卡挂失统计银行资金流通余额和盈利结算统计银行资金流通余额和盈利结算银行资金流通余额银行资金流通余额=总存入金额总存入金额-总支取金额总支取金额盈利结算盈利结算=总支取金额总支取金额 * 0.008 总存入金额总存入金额 * 0.003查询本周开户的卡号，显示该卡相关信息查询本周开户的卡号，显示该卡相关信息查询本

23、月交易金额最高的卡号查询本月交易金额最高的卡号查询挂失账号的客户信息查询挂失账号的客户信息催款提醒业务催款提醒业务讲解需求说明讲解需求说明29用例用例4：模拟常规业务：模拟常规业务4-2思路分析思路分析修改客户密码修改客户密码办理银行卡挂失办理银行卡挂失使用使用UPDATE语句实现密码变更和卡挂失语句实现密码变更和卡挂失UPDATE WHERE 统计银行资金流通余额和盈利结算统计银行资金流通余额和盈利结算使用聚合函数使用聚合函数SUM()和数据类型转换函数和数据类型转换函数CONVERT()SELECT inMoney=SUM() FROM WHERE 查询本周开户的卡号，显示该卡相关信息查询

24、本周开户的卡号，显示该卡相关信息日期函数日期函数DATEDIFF()和和DATEPART()完成时间：完成时间：15分钟分钟30用例用例4：模拟常规业务：模拟常规业务4-3思路分析思路分析查询本月交易金额最高的卡号查询本月交易金额最高的卡号使用子查询和使用子查询和DISTINCT关键字去掉重复的卡关键字去掉重复的卡号号SELECT FROM transInfo WHERE transMoney = ( SELECT FROM )查询挂失账号的客户信息查询挂失账号的客户信息使用子查询使用子查询IN 或内联接查询或内联接查询INNER JOINSELECT FROM userInfo WHERE

25、customerID IN ( SELECT FROM )催款提醒业务催款提醒业务使用子查询使用子查询IN 或内联接查询或内联接查询INNER JOINSELECT FROM userInfo INNER JOIN 完成时间：完成时间：15分钟分钟31用例用例4：模拟常规业务：模拟常规业务4-4功能测试功能测试检查点检查点 （5分钟）分钟）正确修改客户密码正确修改客户密码正确办理银行卡挂失，查询获得挂失账号的正确办理银行卡挂失，查询获得挂失账号的客户信息客户信息正确查询本月交易金额最高的卡号正确查询本月交易金额最高的卡号正确统计银行资金流通余额和盈利结算正确统计银行资金流通余额和盈利结算正确查

26、询本周开户的卡号，显示该卡相关信正确查询本周开户的卡号，显示该卡相关信息息正确催款提醒业务正确催款提醒业务要求要求互相验证完成的程序功能互相验证完成的程序功能小组长检查本组完成情况小组长检查本组完成情况功能测试功能测试演示案例演示案例4：模拟常规业务：模拟常规业务32共性问题集中讲解共性问题集中讲解常见问题及解决办法常见问题及解决办法代码规范问题代码规范问题调试技巧调试技巧33用例用例5：利用视图实现数据查询：利用视图实现数据查询2-1需求说明需求说明为客户提供以下为客户提供以下3个视图供其查询该客户数据个视图供其查询该客户数据客户基本信息：客户基本信息：vw_userInfo银行卡信息：银行

27、卡信息：vw_cardInfo银行卡交易信息：银行卡交易信息：vw_tradeInfo提供友好界面，要求各列名称为中文描述提供友好界面，要求各列名称为中文描述调用创建的视图获得查询结果调用创建的视图获得查询结果讲解需求说明讲解需求说明34用例用例5：利用视图实现数据查询：利用视图实现数据查询2-1思路分析思路分析创建视图创建视图调用视图调用视图CREATE VIEW AS GOSELECT FROM 完成时间：完成时间：25分钟分钟35用例用例5：利用视图实现数据查询：利用视图实现数据查询2-2功能测试功能测试检查点检查点 （5分钟）分钟）正确创建视图正确创建视图正确使用视图获得查询数据正确使

28、用视图获得查询数据要求要求互相验证完成的程序功能互相验证完成的程序功能小组长检查本组完成情况小组长检查本组完成情况功能测试功能测试演示案例演示案例5：利用视图实现数据查询：利用视图实现数据查询36用例用例6：用存储过程实现业务处理：用存储过程实现业务处理9-1需求说明需求说明产生随机卡号产生随机卡号完成开户业务完成开户业务完成取款或存款业务完成取款或存款业务根据卡号打印对账单根据卡号打印对账单根据指定显示的页数和每页的记录数分页显根据指定显示的页数和每页的记录数分页显示数据示数据查询、统计指定时间段内没有发生交易的账查询、统计指定时间段内没有发生交易的账户信息户信息统计指定时间段内某地区客户在

29、银行卡交易统计指定时间段内某地区客户在银行卡交易量和交易额。如果不指定地区，则查询所有量和交易额。如果不指定地区，则查询所有客户的交易量和交易额客户的交易量和交易额讲解需求说明讲解需求说明37用例用例6：用存储过程实现业务处理：用存储过程实现业务处理9-2思路分析思路分析完成取款或存款业务完成取款或存款业务创建存储过程创建存储过程CREATE PROCEDURE proc_takeMoney card char(19)， type char(4) , inputPass char(6)= AS -SQL语句语句 IF() GO存储过程的参数存储过程的参数有默认值的参数，放在最后有默认值的参数，

30、放在最后调用存储过程调用存储过程EXEC proc_takeMoney 1010 3576 0823 3262, 支取支取,123456检测支取金额是否大于当前存款金额加检测支取金额是否大于当前存款金额加1完成时间：完成时间：10分钟分钟38用例用例6：用存储过程实现业务处理：用存储过程实现业务处理9-3思路分析思路分析产生随机卡号产生随机卡号创建存储过程创建存储过程CREATE PROCEDURE proc_randCardID randCardID char(19) OUTPUT SELECT r = RAND (随机种子随机种子 ) SET randCardID =SUBSTRING(t

31、empStr,3,4) .GO 调用存储过程调用存储过程DECLARE mycardID char(19)EXECUTE proc_randCardID mycardID OUTPUTOUTPUT表示传出的参数表示传出的参数产生产生0-1的随机数的随机数字符串截取函数字符串截取函数调用带调用带output输出参数的存储过程输出参数的存储过程完成时间：完成时间：10分钟分钟39用例用例6：用存储过程实现业务处理：用存储过程实现业务处理9-4思路分析思路分析完成开户业务完成开户业务创建存储过程创建存储过程CREATE PROCEDURE proc_openAccount 存储过程参数存储过程参数A

32、S -SQL语句语句 WHILE EXISTS( ) EXEC PROCEDURE proc_randCardID GO调用存储过程调用存储过程EXEC proc_openAccount 李四李四, , ,1,定期定期调用产生随机卡号存储过程，获得唯一的卡号调用产生随机卡号存储过程，获得唯一的卡号包括：开户名、身份证号、包括：开户名、身份证号、电话号码、开户金额、存款类型和地址电话号码、开户金额、存款类型和地址完成时间：完成时间：15分钟分钟40用例用例6：用存储过程实现业务处理：用存储过程实现业务处理9-5思路分析思路分析根据卡号打印对账单根据卡号打印对账单创建存储过程创建存储过程CREAT

33、E PROCEDURE proc_CheckSheet cardID varchar(19)AS SELECT FROM WHERE cardID = cardID PRINT GO调用存储过程调用存储过程EXEC proc_CheckSheet 1010 3576 1212 1134根据输入的卡号查找相关交易记录根据输入的卡号查找相关交易记录完成时间：完成时间：10分钟分钟41共性问题集中讲解共性问题集中讲解常见问题及解决办法常见问题及解决办法代码规范问题代码规范问题调试技巧调试技巧42用例用例6：用存储过程实现业务处理：用存储过程实现业务处理9-6思路分析思路分析根据指定显示的页数和每页的

34、记录数分页显示根据指定显示的页数和每页的记录数分页显示数据数据创建存储过程创建存储过程CREATE PROCEDURE proc_pagingDisplay page int = 1, records int = 10AS SET statement=SELECT TOP + EXEC SP_EXECUTESQL GO调用存储过程调用存储过程EXEC proc_pagingDisplay 2执行动态生成的执行动态生成的 T-SQL 语句语句 拼写拼写T-SQL语句语句完成时间：完成时间：10分钟分钟43用例用例6：用存储过程实现业务处理：用存储过程实现业务处理9-7思路分析思路分析查询、统计在

35、指定时间段内没有发生交易的账查询、统计在指定时间段内没有发生交易的账户信息户信息创建存储过程创建存储过程CREATE PROCEDURE proc_getWithoutTrade Num int output, Amount decimal(18,2) output, AS SELECT FROM WHERE cardID NOT IN ( ) GO调用存储过程调用存储过程EXEC proc_getWithoutTrade NUM OUTPUT, Amount OUTPUT, 存储过程的输出参数存储过程的输出参数不存在交易记录不存在交易记录完成时间：完成时间：10分钟分钟44共性问题集中讲解共性问题集中讲解常见问题及解决办法常见问题及解决办法代码规范问题代码规范问题调试技巧调试技巧45用例用例6：用存储过程实现业务处理：用存储过程实现业务处理9-8思路分析思路分析统计指定时间段内某地区客户在银行卡交易量和统计指定时间段内某地区客户在银行卡交易量和交易额交易额如果不指定地区，则查询所有客户的交易量和交如果不指定地区，则查询所有客户的交易量和交易额易额创建存储过程创建存储过程CREATE PROCEDURE proc_getTradeInfo AS S