還有兩個小步驟沒完成,樓主可以自己看看!
使用母版
如果存在(select * from sysdatabases,其中name='bankDB ')
刪除數據庫bankDB
去
-構建bankDB。
創建數據庫bankDB
在
(
name='bankDB ',
filename='D:\bank\bankDB.mdf ',
文件增長=15%
)
使用bankDB
去
-創建用戶信息表(userInfo)
創建表用戶信息
(
customerID INT IDENTITY(1,1),
customerName VARCHAR(10)不為空,
PID數值(18,0)不為空,
電話VARCHAR(15)不為空,
地址VARCHAR(30)
)
去
-創建信息表的約束
更改表用戶信息添加
約束PK_custonerID主鍵(customerID),
約束CK_PID檢查(len(PID)=18或len(PID)=15),
約束UQ_PID唯壹(PID)、
約束CK _電話檢查(類似於“[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]”的電話或類似於“[0-9][0-9][0-9][0-9][0-9][0-9]
-創建銀行卡信息表(cardInfo)
創建表卡信息
(
cardID VARCHAR(20)不為空,
curType VARCHAR(6)不為空,
savingType VARCHAR(8),
openDate日期時間不為空,
openMoney MONEY不為空,
余額不為空,
傳遞VARCHAR(6)不為空,
isReportLoss位不為空,
customerID INT不為空
)
去
-創建銀行卡信息表的約束
更改表卡信息添加
約束PK_cardID主鍵(cardID),
約束CK_cardID檢查(cardID like ' 10103576[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
約束DF _ curType curType默認為人民幣,
Constraintck _ saving type check(保存類型in('當前','固定方便','常規')),
約束DF_openDate默認GETDATE() FOR openDate,
約束CK _ open money CHECK(open money & gt;=1),
約束CK _余額檢查(balance & gt=1),
約束CK _通過檢查(LEN(pass)=6),
約束DF_pass默認為888888 ',
isReportLoss約束DF_isReportLoss默認值為' 0 ',
約束FK _客戶ID外鍵(客戶ID)引用了用戶信息(客戶ID)
去
-創建交易信息表(transInfo)
創建表傳輸信息
(
transDate日期時間不為空,
cardID VARCHAR(20)不為空,
transType VARCHAR(4)不為空,
transMoney貨幣不為空,
NTEXT評論道
)
去
-創建交易信息表的約束
更改表傳輸信息添加
transDate的約束DF_transDate DEFAULT GETDATE()
約束FK _卡ID外鍵(卡ID)引用卡Info(卡ID),
Constraintck _ trans類型檢查(transType= '存款'或trans type = '取款'),
約束CK_transMoney檢查(transMoney & gt0)
-測試插入的數據。
插入userInfo值('張三',' 123456789012340 ',' 010-67898978 ','北京海澱')。
插入userInfo值('李四',' 320504198607221250 ',' 0512-65331652 ','江蘇蘇州')。
插入userInfo值('王武',' 32050419860722333 ',' 0512-65323442 ','江蘇南京')。
插入userInfo值('劉釗',' 320504198607131313 ','江蘇蘇州')。
插入userInfo值('方六',' 320504194407131543 ',' 0512-65331243 ','江蘇鹽城')。
插入到cardinfo值(' 1010357121134 ','人民幣','活期賬戶',' 2006-10-065438。
插入到cardinfo值(' 101035712125555 ','人民幣','活期賬戶',' 2006-10-01 ',65438+。
插入到cardinfo值(' 1010 3576 2323 3343 ','人民幣','活期賬戶',' 2006-12-01 ',15345.0000,6500。
插入cardinfo值(' 1010 3576 1456 3454 ','人民幣','活期賬戶',' 2006-11-28 ',5467.765656566657
插入到cardinfo值(' 1010 3576 1356 3554 ','人民幣','當前',' 2006-12-05 ',22143.0000,6500。
-插入違反約束的語句。
插入到userInfo值('沈飛',' 123456789 ',' 0512-67998978 ','江蘇蘇州')-違反了身份證長度的檢查約束。
插入到userInfo值('申大',' 3205041986071313 ',' 0512-67998979 ','江蘇蘇州')-唯壹違反身份證的。
插入到cardinfo值(' 1010 3576 1456 3424 ','人民幣','當前',' 2006-11-28 ',0.0000,0,' 1的檢查約束
插入到運輸信息值(',' 1010 3576 1456 3424 ','取款',1000 ' ')-交易卡號必須存在於cardInfo中,這違反了主鍵和外鍵的約束。
-查看所有表格
select * from userInfo
select * from cardInfo
select * from transInfo
-創建壹個存儲過程:根據卡號修改密碼。
CREATE PROC PROC _ change pass @ incardID VARCHAR(20),@inoldPass VARCHAR(6),@newPass1 VARCHAR(6),@newpass2 VARCHAR(6)
如同
DECLARE @oldPass VARCHAR(6)
如果存在(SELECT * FROM card info WHERE card id = @ incar did)
開始
SELECT @ old pass = pass FROM card info WHERE card id = @ incar did
IF(@oldpass=@inoldPass)
開始
IF(@newpass1=@newpass2)
開始
更新card info SET pass = @ new pass 1其中cardID=@incardID
打印“密碼修改成功!”
結束
其他
打印“請確認您的新密碼!”
結束
其他
打印'您輸入的原密碼有誤,請重新輸入!'
結束
其他
打印'您輸入的卡號不存在,請重新輸入!'
-測試用於更改密碼的存儲過程。
EXEC proc _ change pass ' 1010 3576 12 1134 ',' 888888 ',' 123456 ',' 123456 '
-創建壹個用於銀行卡掛失的存儲過程。
CREATE PROC _ loss card @ inCustomerName VARCHAR(10),@inPID NUMERIC(18,0),@ Intel phone VARCHAR(15),@inAddress VARCHAR(30),@inCardPass VARCHAR(6)
如同
聲明@customerName VARCHAR(10)、@PID NUMERIC(18,0)、@telephone VARCHAR(15)、@address VARCHAR(30)、@cardPass VARCHAR(6)
SELECT @客戶名稱=客戶名稱,@PID=PID,@電話=電話,@地址=地址FROM userInfo其中PID=@inPID
SELECT @ card pass = pass FROM card info WHERE customerID =(SELECT customerID FROM userInfo WHERE PID = @ inPID)
IF((@ customerName = @ inCustomerName)AND(@ PID = @ inPID)AND(@ Intel phone = @ telephone)AND(@ in address = @ address)AND(@ inCardPass = @ card pass))
開始
更新card info SET is report loss = 1,其中customerID=(從userInfo中選擇customerID,其中PID=@inPID)
結束
其他
打印“您的信息不正確,請檢查並重新輸入!”
-測試丟失報告的存儲過程。
EXEC proc_lossCard '王武',' 32050419860722333 ',' 0512-65323442 ','江蘇南京',' 88888 '
-銀行發行量統計
申報“內部資金”和“外部資金”
select @ in money = sum(trans money)from trans info其中transmype = ' deposit '
select @ out money = sum(trans money)from trans info where trans type = '取款'
PRINT '銀行流通總額為:'+convert (varchar (20),@ inmoney-@ outmoney)+'RMB '
PRINT '利潤結算:'+convert (varchar (20),@ out money * 0.008-@ in money * 0.003)+' RMB '
去
-查詢月度交易冠軍的銀行卡信息。
SELECT * FROM transInfo其中
打印轉換(VARCHAR(20),@money)
SELECT * FROM cardInfo其中
-查詢半年未交易的卡信息。
SELECT * FROM card info WHERE card id NOT IN(SELECT card id FROM trans info WHERE DATEDIFF(mm,transDate,getdate())& gt;7)//////////
-查詢本周開立的賬戶。
SELECT * FROM card info where DATEDIFF(WEEK,openDate,getdate())& lt;1
-查詢掛失賬戶的客戶信息。
SELECT * FROM userInfo where customerID IN(SELECT customerID FROM card info where is report loss = 1)
-催款提醒業務
選擇customerName作為客戶名稱,電話作為聯系號碼,余額作為余額自。
userInfo內部聯接card info ON userInfo . customerid = card info . customerid WHERE balance & lt;200和datepart(day,getdate())& gt;=28
-向transInfo表的cardID字段添加非聚集索引。
使用FILLFACTOR=70在transInfo(cardID)上創建非聚集索引IX_transInfo_cardID
-測試指數
SELECT * FROM transInfo(INDEX = IX _ transInfo _ cardID)其中cardID = ' 1010 3576 1212 1134 '
-創建視圖
創建視圖view_userInfo
如同
從userInfo中選擇customerID作為客戶號,customerName作為客戶名,PID作為ID號,telephone作為電話號碼,address作為聯系地址。
創建視圖view_cardInfo
如同
選擇卡號為cardId,幣種為curType,存款類型為savingType,開戶日期為開戶日期,開戶金額為openMoney,余額為賬戶余額,密碼為pass,是否掛失,客戶號為cardInfo中的客戶號。
創建視圖view_transInfo
如同
選擇交易日期為交易日期,卡號為卡號,交易類型為交易類型,交易金額為交易金額,備註為來自交易信息。
-看風景
SELECT * FROM view_userInfo
SELECT * FROM view_cardInfo
SELECT * FROM view_transInfo
-創建壹個觸發器
創建觸發器trig_trans
關於transInfo
用於插入
聲明@transType VARCHAR(4)
-創建觸發器trig_trans。
丟棄觸發器trig_trans
在transInfo上創建觸發器trig_trans
用於插入
如同
聲明@transType VARCHAR(4)、@transMoney MONEY、@cardID VARCHAR(20)、@balance MONEY、@customerName VARCHAR(10)
SELECT @transType=transType,@transMoney=transMoney,@cardID=cardID FROM inserted
SELECT @ balance = balance FROM card info其中cardID=@cardID
SELECT @ customerName = customerName FROM userInfo WHERE customerID =(SELECT customerID FROM cardInfo WHERE cardID = @ cardID)
開始TRAN
IF(@ transType = ' retraction ')
開始
如果(@ balance-@ trans money & lt;1)-如果提現後余額小於1元。
開始
打印“交易正在進行,請稍等...”
roll back trans-取消事務並回滾。
打印'余額不足,交易失敗!'
結束
其他
開始
打印“交易正在進行,請稍等...”
commit ran-交易成功。
更新card info SET balance = @ balance-@ trans money其中cardID=@cardID
打印'交易成功,'+convert (varchar (10),@ customername)+'您正在取錢,當前余額為'+convert (varchar (20),@ balance-@ transmoney)+'人民幣'。
結束
結束
其他
開始
打印“交易正在進行,請稍等...”
提交TRAN
更新card info SET balance = @ balance+@ trans money其中cardID=@cardID
'打印'交易成功,'+convert (varchar (10),@ customername)+'您進行了存款操作,當前余額為'+convert (varchar (20),@ balance+@ transmoney)+'人民幣'。
結束
插入運輸信息值(',' 101035712125555 ','取款',999,' aa ')。
-為存款和取款創建壹個存儲過程。
drop proc proc_transInfo
CREATE PROC PROC _ trans info @ customerName VARCHAR(10),@transMoney MONEY,@transType VARCHAR(4),@pass VARCHAR(6)= ' '
如同
DECLARE @pwd VARCHAR(6)
DECLARE @cardID VARCHAR(20)
Select @ pwd = pass,@ cardid = cardinfo from cardinfo where customerid =(select customerid from userInfo where customername = @ customername)-userInfo name字段查詢的用戶的卡號和密碼。
打印'您的卡號是'+convert (varchar (20),@ CARDID)-輸出用戶的卡號。
IF(@transType=' deposit ')
開始
打印“交易正在進行,請稍等...”
插入到transInfo值(',@cardID,@transType,@transMoney,'')
'打印'交易成功,您已成功從卡中存入'+convert (varchar (20),@ transmoney '。
結束
其他
開始
IF(@pwd=@pass)
開始
打印“交易正在進行,請稍等...”
插入到transInfo值(',@cardID,@transType,@transMoney,'')
打印'取款成功,您已成功退卡'+convert (varchar (20),@ transmoney)。
結束
其他
開始
打印“密碼錯誤,請重新輸入!”
結束
結束
去
EXEC proc_transInfo '王武',100,'取款',' 888888 '-模擬存取款。
-創建壹個用於生成隨機卡號的存儲過程。
創建PROC PROC _ RANDcardID @ mycardID VARCHAR(20)輸出
如同
DECLARE @rand NUMERIC(15,8)
DECLARE @ randcardID VARCHAR(10)
SELECT @rand=RAND(CONVERT(INT,DATEPART(mm,GETDATE()) * 100000)+
CONVERT(INT,DATEPART(ss,GETDATE()) * 1000)+
CONVERT(INT,DATEPART(ms,GETDATE()))
SET @randcardID = SUBSTRING(CONVERT(VARCHAR(10),@rand),3,4)+' '+SUBSTRING(CONVERT(VARCHAR(10),@ rand),7,4)
SET @ mycardID = ' 1010 3576 '+' '+@ randcardID
去
-調用隨機卡號生成存儲過程來生成隨機卡號。
DECLARE @mycardID VARCHAR(20)
EXEC proc_RANDcardID @mycardID輸出
PRINT '生成的隨機卡號是'+@mycardID。
-為開戶創建壹個存儲過程。
刪除過程proc_openAccount
CREATE PROC _ openAccount @ customerName VARCHAR(10)、@PID NUMERIC(18,0)、@telephone VARCHAR(15)、@openMoney MONEY、@savingType VARCHAR(8)、@address VARCHAR(30)
如同
WHILE(1=1)
開始
DECLARE @mycardID VARCHAR(20)
EXEC proc_RANDcardID @mycardID輸出
如果不存在(從cardInfo中選擇cardID,其中cardID=@mycardID)
開始
插入用戶信息值(@客戶名稱,@PID,@電話,@地址)
聲明@customerID INT
SELECT @ customerID = customerID FROM userInfo其中PID=@PID
插入到cardInfo(cardID,savingType,openMoney,balance,customerID)值(@mycardID,@savingType,@openMoney,@openMoney,@customerID)
打印‘開始開戶,請稍等……’
“打印”賬戶開立成功!妳的卡號是'+@mycardID。
PRINT '開戶日期:'+CONVERT(VARCHAR(20),GETDATE())
PRINT '開戶金額為:'+convert (varchar (20),@ openmoney)。
破裂
結束
結束
去
-模擬開戶,調用存儲過程proc_openAccount。
EXEC proc_openAccount '沈飛',' 320504198607221447 ',' 13914048959 ',3999,'當前','蘇州職業大學'
-為帳戶間轉帳創建壹個存儲過程。
刪除過程過程_事務
CREATE PROC PROC _ trans @ out cardid VARCHAR(20),@incardID VARCHAR(20),@ transMoney MONEY
如同
聲明@outbalance MONEY,@inbalance MONEY,@isReportLoss1 BIT,@isReportLoss2 BIT
SELECT @ out balance = balance FROM card info其中cardID=@outcardID
SELECT @ in balance = balance FROM card info WHERE card id = @ incar did
SELECT @ isReportLoss 1 = isReportLoss FROM card info其中cardID=@outcardID
SELECT @ isReportLoss 2 = isReportLoss FROM card info其中cardID=@incardID
IF(@isReportLoss1 =0且@isReportLoss2=0)
開始
如果(@ outbalance-@ trans money & lt;1)
開始
打印“交易正在進行,請稍候...”
'打印'交易失敗,余額不足,不能轉賬!'
PRINT '您的余額為:'+convert (varchar (20),@ outbalance)+'RMB '
結束
其他
開始
打印“交易正在進行,請稍候...”
插入transinfo(卡,交易類型,transmoney)值(@ outcardid,'取款',@transMoney)。
插入transinfo(卡、交易類型、交易金額)值(@ incremental、' deposit '、@transMoney)。
更新card info SET balance = @ out balance-@ trans money其中cardID=@outcardID
更新card info SET balance = @ in balance+@ trans money其中cardID=@incardID
打印“傳輸成功!”
打印
結束
結束
其他
打印:其中壹張卡已掛失,請自首!'交易失敗'
-模擬轉移。
EXEC proc _ trans ' 1010 3576 1356 3554 ',' 10 3576 1212 1134 ',1000
-添加登錄帳戶
EXEC SP_ADDLOGIN 'philip ',' 65331652 '
-創建數據庫用戶
EXEC SP _ GRANTDBACCESS ' philip '
-授予三個表添加、刪除和更改查詢的權限。
授予philip選擇、插入、刪除和更新用戶信息的權限
授予philip對cardInfo進行選擇、插入、刪除和更新的權限
授予philip選擇、插入、刪除和更新transInfo的權限
-授予存儲過程執行權限。
將proc_changePass上的EXEC授予philip
將proc_lossCard上的EXEC授予philip
將proc_transInfo上的EXEC授予philip
將proc_RANDcardID上的EXEC授予philip
將proc_openAccount上的EXEC授予philip
將proc_trans上的EXEC授予philip