实例数据库介绍

--模拟银行业务，设计简易版的银行数据库表结构，要求完成以下功能
--1.银行开户
--2.存钱
--3.查询余额
--4.取钱
--5.转账
--6.查看交易记录
--7.账户挂失
--8.账号注销


--表的设计
--1.账户信息表
--2.银行卡表
--3.交易信息表（存钱和取钱过程）
--4.转账信息表(存储转账信息记录)
--5.状态信息变化表(存储银行卡状态 1正常2挂失3冻结 4注销)

--indentity(m,n) m:开始值，n:步长

--表结构设计
Create table AccountInfo
(
AccountId int primary key identity(1,1),
AccountCode varchar(20) not null,
AccountPhone varchar(20) not null,
RealName varchar(20) not null,
OpenTime smalldatetime not null,

)
create table BankCard
(
CardNo varchar (30) primary key,
AccountId int not null,
CardPwd varchar(30) not null,
CardMoney money not null,
CardState int not null ,
CardTime smalldatetime default(getdate()),
)

create table CardExchange
(
ExchangeId int primary key identity(1,1),
CardNo varchar(30) not null,
MoneyInBank money not null,
MoneyOutBank money not null,
ExchangeTime smalldatetime not null,
)

create table CardTransfer
(
TransferId int primary key identity(1,1),
CardNoOut varchar(30) not null,
CardNoIn varchar(30) not null,
TransferMoney money not null,
TransferTime smalldatetime not null,


)

create table CardStateChange
(
StateId int primary key identity(1,1),
CardNo varchar(30) not null,
OldState int not null,
NewState int not null,
StateWhy varchar(200 ) not null,
StateTime smalldatetime not null,

)

--为刘备关羽张飞三个人进行开会操作
--刘备身份证：371929200111025401
--关羽身份证：371329200111025402
--张飞身份证：371329200111025403
insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('371929200111025401','123456789','刘备',getdate())
insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
values('6225125478544587',1,'123456',0,1)

insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('371929200111025402','12345678910','关羽',getdate())
insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
values('6225125478544588',2,'123456',0,1)
insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('371929200111025403','1234567891011','张飞',getdate())
insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
values('6225125478544589',3,'123456',0,1)

select * from AccountInfo
select * from BankCard


--进行存钱操作 刘备存2000 关羽存8000 张飞存500000
update BankCard set CardMoney=CardMoney+2000 where CardNo='6225125478544587'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225125478544587',2000,0,GETDATE())

--转账
--刘备给张飞转账1000元
update BankCard set CardMoney=CardMoney-1000 where CardNo='6225125478544587'
update BankCard set CardMoney=CardMoney+1000 where CardNo='6225125478544589'
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime)
values('6225125478544587','6225125478544589',1000,getdate())


select * from CardExchange