银行系统SQLServer案例.ppt

1、1 银行系统业务逻辑描述,（1）银行员工可登录系统 （2）登录后可以为客户创建客户账号表 （3）可以对客户账号进行增删改查 （4）用户可以进行存款、取款、转账等交易 （5）为每笔交易创建交易记录情况表 （6）可以查询交易情况,数据表结构设计,账号信息,交易信息,账号id,用户id,数据表结构设计,2.3 操作步骤,创建表,多表关联,SQL语言 实现 增、删、 改、查,单表查询 （模糊查询、 排序、 分类等）,多表查询 （多表连接、 嵌套）,常用函数 （统计计算Count、 Sum、Avg、 Max，时间函数、 字符串函数等）,数据增加,数据删除,数据修改,数据类型 、数据约束,外键,创建 数据

2、库,存储过程,创建数据库,创建数据库 CREATE DATABASE 数据库名 例如，创建mytest数据库 CREATE DATABASE mytest,银行系统数据库创建,use master go if exists(select 0 from sysDatabases where name=BankDB) drop database BankDB go create database BankDB go,创建表,银行系统表创建,创建员工信息表,use BankDB go if exists(select 0 from sysObjects where name=UserInfo) dr

3、op table UserInfo go create table UserInfo-员工信息表 ( id char(4) not null,-员工工号 name nvarchar(20) not null,-员工姓名 password binary(16) not null -登陆密码 ) go,创建客户账号表,if exists(select 0 from sysObjects where name=Account) drop table Account go create table Account-客户账号表 ( code char(16) not null,-客户账号 card va

4、rchar(18) not null,-身份证号 name nvarchar(100) not null,-客户名称 password binary(16) not null,-账号密码 openDate datetime not null,-开户日期 openMoney money not null,-开户金额 balance money not null -帐户余额 ) go,创建交易记录情况表,if exists(select 0 from sysObjects where name=Trade) drop table Trade go create table Trade-交易记录情况

5、表 ( id bigint identity(1,1) not null,-流水编号 code char(16) not null,-客户账号 tradeDate datetime not null,-交易日期 tradeMoney money not null,-交易金额 tradeType int not null,-交易类型 memo nvarchar(100) not null -交易描述 ) go,修改表,数据约束,银行系统的数据约束,alter table Account add constraint pk_account_code primary key(code), const

6、raint ck_account_card check(len(card) in (15,18), constraint df_account_openDate default(getdate() for openDate, constraint ck_account_openMoney check(openMoney0), constraint ck_account_balance check(balance0) go,银行数据库中的约束,数据约束,alter table Trade add constraint pk_trade_id primary key(id), constraint

7、 df_trade_date default(getdate() for tradeDate, constraint ck_trade_money check(tradeMoney0), constraint fk_trade_code foreign key(code) references Account(code) go,银行系统的数据插入,alter table UserInfo add constraint pk_userInfo_id primary key(id) go -password:123456 insert into UserInfo values(0001,Tom,0

8、 xe10adc3949ba59abbe56e057f20f883e),练习,数据操作,银行系统中的数据操作,向员工信息表插入数据,-password:123456 insert into UserInfo values(0001,Tom,0 xe10adc3949ba59abbe56e057f20f883e),SQL查询,简单的select子句,where子句,常用比较条件,其他比较条件,like关键字,GROUP BY子句,Having子句,order by子句,银行系统的统计计算,统计计算 查询余额最大的账户 SELECT * FROM Account WHERE balance IN

9、(SELECT MAX(balance) FROM Account ); 按交易类型统计交易数量 SELECT COUNT(id), tradeType FROM Trade GROUP BY tradeType;,常用函数,日期函数 插入一条交易信息 INSERT INTO Trade (code, tradeDate) VALUES(*, getdate(); 字符串函数 显示账户号的后8位 SELECT Substring(code,8,16) FROM Account;,日期函数,1.当前系统日期、时间 getdate() 2. 在向指定日期加上一段时间的基础上，返回新的 dateti

10、me 值 例如：向日期加上2天 dateadd(day,2,2004-10-15) -返回：2004-10-17 00:00:00.000 3. 返回跨两个指定日期的日期和时间边界数。 datediff(day,2004-09-01,2004-09-18) -返回：17 datediff(day,2004-09-18,2004-09-01) -返回：-17 4. 返回代表指定日期的指定日期部分的整数。 datepart(month, 2004-10-15) -返回 10 5. 返回代表指定日期的指定日期部分的字符串 datename(weekday, 2004-10-15) -返回：星期五,字

11、符串函数,多表查询,SELECT COUNT(id), tradeType FROM Trade GROUP BY tradeType;,左连接,右连接,银行系统的数据库查询,单表查询 查询名字为“Mike”的账户详细信息 SELECT * FROM Account where name=Mike; 多表连接查询 查询“Mike”的交易情况 SELECT A.name, T. tradeDate, T. tradeMoney,T.tradeType FROM Trade as T INNER JOIN Account as A on T.code=A. code WHERE A.name=Mi

12、ke；,例子,数据增加 增添新的账户 （用存储过程实现） 数据删除 按账户名删除账户 DELETE FROM Account WHERE name=Mike; 数据修改 修改某条交易信息 UPDATE Trade SET tradeMoney=$100.00 WHERE id=*；,视 图,T-SQL简介,流程控制,存储过程,没有参数的存储过程,带有参数的存储过程,银行系统的存储过程,（1）生成客户账号 （2）开户 （3）交易存取 （4）交易转账,（1）生成客户账号,if exists(select 0 from sysObjects where name=up_makeAccountCode

13、) drop proc up_makeAccountCode go create proc up_makeAccountCode-生成客户账号 code char(16) output as while (1=1) begin set code=51826859+right(convert(numeric(8,8),rand(),8) if exists(select 1 from Account where code=code) continue else break end go,（1）生成客户账号,执行生成客户账号,declare code char(16) exec up_makeAc

14、countCode code output print code,（2）开户,if exists(select 0 from sysObjects where name=up_accountOpen) drop proc up_accountOpen go create proc up_accountOpen-开户 code char(16), name nvarchar(100), card varchar(18), pass binary(16), money money, memo nvarchar(100)=开户 as declare date datetime declare err

15、or int,（2）开户,set date=getDate() set error=0 begin tran insert into Account values(code,card,name,pass,date,money) set error=error insert into Trade values(code,date,money,0,memo) set error=error+error if(error=0) commit tran else rollback tran go,（2）开户,执行开户,declare code char(16) exec up_makeAccountC

16、ode code output exec up_accountOpen code,Tom 0 x202CB962AC59075B964B07152D234B70,1000 exec up_accountOpen code,Mike 0 x202CB962AC59075B964B07152D234B70,1000 select * from account select * from trade,（3）交易存取,if exists(select 0 from sysObjects where name=up_trad

17、eRecord) drop proc up_tradeRecord go create proc up_tradeRecord-交易存取 code char(16), money money, type int, memo nvarchar(100) as declare error int set error=0 begin tran,（3）交易存取,insert into Trade values(code,default,money,type,memo) set error=error if(type=0) update Account set balance=balance+money

18、 where code=code else update Account set balance=balance-money where code=code set error=error+error if(error=0) commit tran else rollback tran go,（3）交易存取,执行交易存取,exec up_tradeRecord 5182685995183208,100,0,存钱 exec up_tradeRecord 5182685995183208,100,1,取钱 select * from account select * from trade,（4）交

19、易转账,if exists(select 0 from sysObjects where name=up_tradeTransfer) drop proc up_tradeTransfer go create proc up_tradeTransfer-交易转账 codeOut char(16), codeIn char(16), money money, memo nvarchar(100) as declare date datetime declare error int set date=getDate() set error=0 begin tran,（4）交易转账,update Account set balance=balance-money where code=codeOut set error=error update Account set balance=balance+mon