创建BankDB银行数据库,创建表,插…
创建BankDB银行数据库,创建表,插…
杨润康Bla 发表于2年前
创建BankDB银行数据库,创建表,插…
  • 发表于 2年前
  • 阅读 16
  • 收藏 0
  • 点赞 2
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

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

共有 人打赏支持
粉丝 15
博文 76
码字总数 0
×
杨润康Bla
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: