use master go --創建庫 if exists(select * from sysdatabases where name='bankDB') drop database bankDB create database bankDB on 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--創建表userInfo use bankDB go if exists(select * from sysobjects where name='userInfo') drop table userInfo create 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--創建表cardInfo if exists(select * from sysobjects where name='cardInfo') drop table cardInfo create 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 transInfo CREATE 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--插入userINfo insert userInfo(customerName,PID,telephone,address)values('張三','123456789012345','010-67898978','北京海淀') insert userInfo(customerName,PID,telephone,address)values('李四','321245678912345678','0478-44443333','NULL') select * from userInfo --插入cardInfo INSERT 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 userInfo SELECT * 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）修改銀行卡密碼為123123 update 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 cardInfo GO--統計銀行的資金流通余額 set nocount on declare @inMoney money,@outMoney money select @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)<DATEPART(weekday,openDate)) /*---------查詢本月交易金額最高的卡號----------------------*/ SELECT DISTINCT cardID FROM transInfo WHERE transMoney=(SELECT Max(transMoney) FROM transInfo) /*---------查詢掛失賬號的客戶信息---------------------*/ SELECT customerName as 客戶姓名,telephone as 聯系電話 FROM userInfo WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1) /*------催款提醒： 如果發現用戶賬上余額少于200元，將致電催款。---*/ SELECT customerName as '客戶姓名',聯系電話=telephone FROM userInfo INNER JOIN cardInfo ON userInfo.customerID =cardInfo.customerID WHERE balance<200--1.創建索引：給交易表的卡號cardID字段創建重復索引 create NONCLUSTERED INDEX index_cardID ON transInfo(cardID)WITH FILLFACTOR=70 GO --2.按指定索引查詢 張三（卡號為1010 3576 1212 1134）的交易記錄 SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID='1010 3576 1234 5678' GO --3.創建視圖：查詢各表要求字段全為中文字段名。 create VIEW view_userInfo --銀行卡信息表視圖（其他表同理）AS select customerID as 客戶編號,customerName as 開戶名, PID as 身份證號,telephone as 電話號碼,address as 居住地址 from userInfo GO--隨機產生卡號 declare @r numeric(15,8),@randCardID char(19),@tempStr varchar(10),@preID varchar(10) set @preID='1010 3576 ' select @r=rand((datepart(mm,getDate())*100000)+(datepart(ss,getdate())*1000)+datepart(ms,getdate()) ) set @tempStr=''+convert(varchar(10),@r) set @randCardID=@preID+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4) print @randCardID
