use mastergo--创建库if exists(select * from sysdatabases where name='bankDB')drop database bankDBcreate database bankDBon primary(name='bankDB_data',filename='E:\bank\bankDB_data.mdf',filegrowth=30%,size=5)log on(name='bankDB_log',filename='E:\bank\bankDB_log.ldf',size=2,filegrowth=10%)go--创建表userInfouse bankDBgoif exists(select * from sysobjects where name='userInfo')drop table userInfocreate table userInfo(customerID int identity(1,1) primary key,customerName varchar(12) not null,PID varchar(18) not null,telephone varchar(14) not null,address varchar(20),constraint CK_PID check (LEN(PID) between 15 and 18),constraint VK_telephone check(telephone like '____-________' or telephone like '___-________' 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]'),CONSTRAINT UQ_PID UNIQUE(PID))go--创建表cardInfoif exists(select * from sysobjects where name='cardInfo')drop table cardInfocreate table cardInfo(  cardID  CHAR(19) NOT NULL primary key,  curType  CHAR(5) NOT NULL default 'RMB',  savingType  CHAR(8) NOT NULL,  openDate  DATETIME NOT NULL default getDate(),  openMoney  MONEY NOT NULL,  balance  MONEY NOT NULL,  pass CHAR(6) NOT NULL default'888888',  IsReportLoss BIT  NOT NULL default '0',  customerID INT NOT NULL,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 CK_openMoney check(openMoney>=1),constraint CK_balance check(balance>=1),constraint CK_savingType check(savingType in ('活期','定活两便','定期')),constraint FK_customerID foreign key(customerID) REFERENCES userInfo(customerID))go--创建transInfo交易信息表if exists(select * from sysobjects where name='transInfo')drop table transInfoCREATE TABLE transInfo  (  transDate  DATETIME NOT NULL default getDate(),  transType  CHAR(4) NOT NULL,  cardID  CHAR(19) NOT NULL,  transMoney  MONEY NOT NULL,  remark  TEXT,constraint CK_transMoney check(transMoney >0),constraint CK_transType check(transType in('存入','支取')),constraint FK_cardID foreign key(cardID) references cardInfo(cardID))GO--插入userINfoinsert userInfo(customerName,PID,telephone,address)values('张三','123456789012345','010-67898978','北京海淀')insert userInfo(customerName,PID,telephone,address)values('李四','321245678912345678','0478-44443333','NULL')select * from userInfo--插入cardInfoINSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID) VALUES('1010 3576 1212 1134','定期',1,1,2)INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID) VALUES('1010 3576 1234 5678', '活期',1000,1000,1)SELECT * FROM userInfoSELECT * FROM cardInfo/*--------------交易信息表插入交易记录--------------------------*/INSERT INTO transInfo(transType,cardID,transMoney) VALUES('支取','1010 3576 1234 5678',900)  /*-------------更新银行卡信息表中的现有余额-------------------*/UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678'/*--------------交易信息表插入交易记录--------------------------*/INSERT INTO transInfo(transType,cardID,transMoney)       VALUES('存入','1010 3576 1212 1134',5000)   /*-------------更新银行卡信息表中的现有余额-------------------*/UPDATE cardInfo SET balance=balance+5000 WHERE cardID='1010 3576 1212 1134'GO/*---------修改密码-----*/--1.张三（卡号为1010 3576 1234 5678）修改银行卡密码为123456--2.李四（卡号为1010 3576 1212 1134）修改银行卡密码为123123update cardInfo set pass='123456' WHERE cardID='1010 3576 1234 5678' update cardInfo set pass='123123' WHERE cardID='1010 3576 1212 1134' SELECT * FROM cardInfo/*--------- 李四的卡号挂失 ---------*/update cardInfo set IsReportLoss=1 WHERE cardID='1010 3576 1212 1134' SELECT * FROM cardInfoGO--统计银行的资金流通余额set nocount ondeclare @inMoney money,@outMoney moneyselect @inMoney=sum(transMoney) from transInfo where transType='存入'select @outMoney=sum(transMoney) from transInfo where transType='支取'print '银行流通余额总计为:'+convert(varchar(20),@inMoney-@outMoney)+'RMB'--统计银行的盈利结算print '银行盈利总计为:'+convert(varchar(20),@outMoney*0.008-@inMoney*0.003)+'RMB'--查询本周开户的卡号SELECT * FROM cardInfo WHERE (DATEDIFF(Day,getDate(),openDate)