ATM机小案例(库、表、约束、视图、存储过程和事务)

--创建数据库
use master
go
if exists (select * from sysdatabases where name = 'bankDB')
drop database bankDB
go
create database bankDB
on
(
name = 'bankDB_data',
filename = 'E:/SQL Server/项目/bankDB_data.mdf',
size = 3mb,
filegrowth = 15%
)
log on
(
name = 'bankDB_log',
filename = 'E:/SQL Server/项目/bankDB_log.ldf',
size = 2mb,
maxsize = 10mb,
filegrowth = 1mb
)
go

--创建表——userInfo
use bankDB
go
if exists (select * from sysobjects where name = 'userInfo')
drop table userInfo
go
create table userInfo
(
customerID int primary key identity(1,1) not null,    --顾客ID
customerName varchar(20) not null,    --顾客姓名
PID varchar(18) not null,    --身份证号
telephone varchar(13) not null,   --联系电话
address varchar(50) null   --地址
)
select * from userInfo
go

--创建表——cardInfo
use bankDB
go
if exists (select * from sysobjects where name = 'cardInfo')
drop table cardInfo
go
create table cardInfo
(
cardID varchar(19) primary key not null,    --卡号
pass varchar(6) not null,    --密码
curType varchar(10) not null,    --货币种类
savingType varchar(10) not null,    --存款类型
openDate datetime not null,   --开户日期
openMoney money not null,    --开户金额
balance money not null,   --余额
customerID int not null,   --顾客ID
    IsReportLoss bit not null     --是否挂失
)
select * from cardInfo
go

--创建表——transInfo
use bankDB
go
if exists (select * from sysobjects where name = 'transInfo')
drop table transInfo
go
create table transInfo
(
transDate datetime not null,    --交易日期
cardID varchar(19) not null,   --卡号
transType varchar(10) not null,   --交易类型，存入或支取
transMoney money not null,    --交易金额
remark varchar(50) null    --备注
)
select * from transInfo
go

--创建约束——userInfo
alter table userInfo add constraint CK_PID check (len(PID )=15 or len(PID )=18),   --18位或15位
       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 telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), --格式“xxxx-xxxxxxxx”或手机号11位
       constraint UQ_PID unique (PID),    --身份证号唯一
       constraint DF_addresss default('地址不详') for address    --地址默认为‘地址不详’
go

--创建约束——cardInfo
alter table cardInfo add 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 DF_curType default('RMB') for curType,
       constraint CK_savingType check (savingType in( '活期','定活两便','定期')),
       constraint DF_openDate default(getDate()) for openDate,
       constraint CK_openMoney check (openMoney>=1),
       constrain