ATM取款机设计

if exists(select * from sysdatabases where name='bankDB')
DROP database bankDB
go
 drop database bankDB
create database bankDB
on
(
  name='bankDB',
  filename='d:/bank/bankDB_data.mdf',
  size=3mb,
  filegrowth=15%
)
log on
(
  name='bankDB_log',
  filename='d:/bank/bankDB_log.ldf',
  size=3mb,
   filegrowth=16%
)
GO

USE bankDB
GO
 
create table user_Info --用户信息表
(
   customerID int identity(1,1),
   customerName char(8) not null,
   PID char(16) not null,
   telephone char(13) not null,
   address varchar(50)
)
 GO

create table cardInfo --银行卡信息表
(
   cardID char(19) not null,
   curType char(5) not null,
   savingType char(8) not null,
   openDate datetime not null,
   openMoney money not null,
   balance money not null,
   pass char(6) not null,
   IsReportLoss bit not null,
   customerID int not null

)
go
 
  create table transInfo --交易信息表

(
   transDate datetime  not null,
   transType char(4)  not null,
   cardID  char(19) not null,
   transMoney money not null,
   remark text
)
go

alter table user_Info
add constraint PK_customerID PRIMARY KEY(customerID),
    constraint CK_PID CHECK(len(PID)=18 or len(PID)=15),
    constraint UQ_PID UNIQUE(PID),
    constraint CK_telephone check(telephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'or telephone like
'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'or len(telephone)=13)
go
 

alter table cardInfo
add constraint PK_cardID primary key(cardID),
    constraint CK_cardID check(cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
    constraint DK_curType default('RMB')FOR curType,
    constraint CK_savingType  check(savingType in('活期','活定两便','定期')),
    constraint DK_openDate default(getdate())for openDate,
    constraint CK_openMoney check(openMoney>=1),
    constraint CK_balance check(balance>=1),
    constraint CK_pass check (pass like'[0-9][0-9][0-9][0-9][0-9][0-9]'),
    constraint DK_pass default('888888')for pass,
    constraint DK_IsReportLoss default(0)for IsReportLoss,
    constraint FK_customerID foreign key(customerID) references user_Info(customerID)
GO

alter table transInfo
add constraint CK_transType check(transType in('存入','支出')),
    constraint FK_cardID foreign key (cardID)REFERENCES cardInfo(cardID),
    constraint CK_transMoney check(transMoney>0),
    constraint DF_transDate default (getdate())for transDate
GO
set nocount on
insert into user_Info(customerName,PID,telephone,address)
values('张三','123456789012345','010-67898978','北京海淀')

insert into user_Info(customerName,PID,telephone)
values('李四','321245678912341','0478-44443333')

insert into cardInfo (cardID,savingType,openMoney,balance,customerID)
values('1010 3576 1234 5678','活期',1000,1000,2)

insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values('1010 3576 1212 1134','定期',1,1,11)

go

create view view_userInfo
as
  select customerID as 客户编号,customerName as 开户名,PID as 身份证,

  telephone as 电话号码, address as 居住地点 from user_Info
go

insert into view_userInfo
values ('王五','123450213658914','0457-56412365','湖南湘潭')
update view_userInfo
set 电话号码='1234567894123'where 开户名='王五'

select * from view_userInfo

create view aa_view
as
  select customerName,balance,savingType
from user_Info,cardInfo where user_Info.customerID=cardInfo.customerID