创建BankDB银行数据库，创建表，插…

use master

if exists (select * from sysdatabases where name='BankDB')

drop database BankDB

go

create database BankDB

on primary

(

name='BankDB_data',

filename='E:\BankDB_data.mdf',

size=20MB,

maxsize=550MB,

filegrowth=15MB

)

log on 

(

name='BankDB_log',

filename='E:\BankDB_log.ldf',

size=20MB,

maxsize=550MB,

filegrowth=15MB

)

go

use BankDB

if exists (select * from sysobjects where name='UserInfo')

drop table UserInfo

go

create table UserInfo

(

customerID int identity(1,1) primary key,--客户编号

customerName varchar(5) not null,--客户姓名

PID varchar(20) not null,--身份证号

telephone varchar(20) not null, --联系电话

address varchar(100) --地址 

)

go

alter table UserInfo

add constraint ck_PIDLength check(len(PID)=18 or len(PID)=15),

constraint uq_PID unique(PID),

constraint ck_telephone check(len(telephone)=11 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]')

go

if exists (select * from sysobjects where name='Deposit')

drop table Deposit

go

create table Deposit

(

savingID int identity(1,1) primary key,--存款类型编号

savingName varchar(20) not null,--存款类型名称

descrip   varchar(50)--描述

)

go

if exists (select * from sysobjects where name='CardInfo')

drop table CardInfo

go

create table CardInfo

(

cardID char(20) primary key,--卡号

curID varchar(20) not null,--货币种类 --无外键

savingID int not null,--存款类型

opendate datetime not null,--开户日期

openMoney decimal not null,--开户金额

balance decimal not null,--余额

password varchar(20) not null,--密码

isReportLoss bit not null,--是否挂失

customerID int not null--客户编号

)

go

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_curID default('RMB') for curID,

constraint fk_savingID foreign key(savingID) references Deposit(savingID),

constraint df_openDate default(getdate()) for opendate,

constraint ck_openMoney check(openMoney>=1),

constraint ck_balance check(balance>=1),

constraint ck_password check(len(password)=6),

constraint df_password default('888888') for password,

constraint df_isReportLoss default('0') for isReportLoss,

    constraint fk_customerID foreign key(customerID) references UserInfo(customerID)

go

if exists (select * from sysobjects where name='TradeInfo')

drop table TradeInfo

go

create table TradeInfo

(

transDate datetime not null,--交易日期

cardID char(20) not null,--卡号

transType int not null,--交易类型

transMoney decimal not null,--交易金额

remark varchar(50)--备注

)

go

alter table TradeInfo

add constraint df_transDate default(getdate()) for transDate,

constraint fk_cardID foreign key(cardID) references CardInfo(cardID),

constraint ck_transType check(transType in (1,0)),

constraint ck_transMoney check(transMoney>0)

go

--插入数据Deposit

INSERT INTO deposit (savingName,descrip) VALUES ('活期','按存款日结算利息')

INSERT INTO deposit (savingName,descrip) VALUES ('定期一年','存款期是1年')

INSERT INTO deposit (savingName,descrip) VALUES ('定期二年','存款期是2年')

INSERT INTO deposit (savingName,descrip) VALUES ('定期三年','存款期是3年')

INSERT INTO deposit (savingName) VALUES ('定活两便')

INSERT INTO deposit (savingName) VALUES ('通知')

INSERT INTO deposit (savingName,descrip) VALUES ('零存整取一年','存款期是1年')

INSERT INTO deposit (savingName,descrip) VALUES ('零存整取二年','存款期是2年')

INSERT INTO deposit (savingName,descrip) VALUES ('零存整取三年','存款期是3年')

INSERT INTO deposit (savingName,descrip) VALUES ('存本取息五年','按月支取利息')

---UserInfo

INSERT INTO userInfo(customerName,PID,telephone,address )

      VALUES('张三','123456789012345','010-67898978','北京海淀')

INSERT INTO userInfo(customerName,PID,telephone)

      VALUES('王五','567891234532124670','010-44443333')

     

     

INSERT INTO userInfo(customerName,PID,telephone)

      VALUES('李四','321245678912345678','0478-4444333')--号码11位，要符合约束

INSERT INTO userInfo(customerName,PID,telephone)

      VALUES('丁六','567891321242345618','0752-4334554')

     

--CardInfo

INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)

      VALUES('1010 3576 1212 1130',2,1,1,3)

INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)

      VALUES('1010 3576 1234 5678',1,1000,1000,1)

     

INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)

      VALUES('1010 3576 1212 1134',2,1,1,2)--customerID要表里有才能插入成功

INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)

      VALUES('1010 3576 1212 1004',2,1,1,4)

--TradeInfo

INSERT INTO tradeInfo(transType,cardID,transMoney) 

      VALUES(0,'1010 3576 1234 5678',900) 

INSERT INTO tradeInfo(transType,cardID,transMoney) 

      VALUES(1,'1010 3576 1212 1130',300)  

INSERT INTO tradeInfo(transType,cardID,transMoney) 

      VALUES(1,'1010 3576 1212 1004',1000) 

INSERT INTO tradeInfo(transType,cardID,transMoney) 

      VALUES(0,'1010 3576 1234 5678',900)  

INSERT INTO tradeInfo(transType,cardID,transMoney) 

      VALUES(1,'1010 3576 1212 1134',5000)  

     

select * from CardInfo

select * from Deposit

select * from TradeInfo

select * from UserInfo