print '本月交易金額最高的卡號'
select distinct cardID from transInfo 
where transMoney = (select max(transMoney) from transInfo
where datediff(Month,transDate,getdate()) = 0)
and datediff(Month,transDate,getdate()) = 0

/*select cardID ,max(select sum(transMoney) from transInfo 
where datediff(Month,transDate,getdate()) = 0 group by cardID)
 from transInfo*/

------------------------6.6查詢掛失帳號的客戶信息 ----------------
--1用子查詢方法
print '查詢掛失帳號的客戶信息 '
select 客戶姓名=customerName ,聯系電話=PID from  userInfo
where customerID in(select customerID from cardInfo where IsReportLoss=1)

--2內部連接方法
print '查詢掛失帳號的客戶信息 '
select 客戶姓名=customerName ,聯系電話=PID from  userInfo
inner join cardInfo on cardInfo.customerID =userInfo.customerID where IsReportLoss=1

-----------------------6.7催款提醒業務-----------------------------
print '每個月帳上余額少于200元的 客戶 致電催款'
select 客戶姓名=customerName ,聯系電話=PID ,帳戶余額=balance
from  userInfo inner join cardInfo on cardInfo.customerID =userInfo.customerID
where balance<200 and datediff(Month,openDate,getdate())=0

/*********************7創建索引和視圖*****************************/

----------------------7.1.1創建 交易表cardID字段索引,加快查詢速度------
if exists (select * from sysindexes where name='ix_transInfo_cardid')--判斷索引是否存在
drop index transInfo.ix_transInfo_cardid--如果存在則刪除
--drop index  ix_transInfo_cardid--錯誤
go
create NONCLUSTERED index  ix_transInfo_cardid--交易表
on transInfo(cardid)--按cardID字段索引
with fillfactor =70--索引因子
go
----------------------7.1.2測試 按索引查詢---------------------------------
print '按索引查詢'
select * from transInfo   (index=ix_transInfo_cardid)
where cardID=(select cardID from cardInfo where 
customerID =(select customerID from userInfo where customername='張三'))

select * from transInfo   (index=ix_transInfo_cardid)
where cardID='1010 3576 1212 1134'

----------------------7.2創建視圖，向用戶展示友好界面,創建中文字段視圖------------
--7.2.1 userInfo表的視圖
if exists (select * from sysobjects where name='view_userInfo')
drop view view_userInfo
go
create view view_userInfo
as select 客戶編號=customerID,開戶名=customerName ,
身份證號=PID,電話號碼= telephone,居住地址=address  
from userInfo
go
select * from view_userInfo--查看視圖

--7.2.2 cardInfo 表的視圖
if exists (select * from sysobjects where name='view_cardInfo')
drop view view_cardInfo
go
create view view_cardInfo
as select 卡號=cardID,貨幣種類=curType,存款種類=savingType,
開戶日期=openDate,余額=balance ,密碼=pass,
是否掛失=IsReportLoss,客戶編碼=customerID
from cardInfo
go

select * from view_cardInfo--查看視圖

--7.2.3 transInfo表的視圖
if exists (select * from sysobjects where name='view_transInfo')
drop view view_transInfo
go
create view view_transInfo
as 
select 交易日期=transDate,交易類型=transType,
卡號=cardID,交易金額=transMoney,備注=remark
from transInfo 
go
select * from view_transInfo--查看視圖

/**************************8創建觸發器******************************/
if exists (select * from sysobjects where name='trig_transInfo_insert')
drop trigger trig_transInfo_insert
go
create trigger trig_transInfo_insert
on transInfo
for insert
as
--定義變量：@cardID 交易卡號,@transMoney  交易金額,@transType 交易類型,　@balance 交易前余額 @newbalance交易后余額
declare @cardID  char(19),@transMoney money,@transType char(8),@balance money,@newbalance money
--獲取要交易的　交易卡號　交易金額　交易類型
select @cardID =cardID ,@transMoney=transMoney,@transType=transType from inserted
--獲取交易前的余額
select @balance=balance from cardInfo where cardID=@cardID
 
 if  not exists (select * from cardInfo where cardID=@cardID)--???沒起作用
 begin
 raiserror ('沒有該用戶',16,1)
 rollback tran
 return
 end 

--嵌套if 首先判斷交易類型,如果是支取再判斷余額是否足夠交易
if (@transType='存入')
 begin
  print '交易正在進行中,請稍候....'
  update cardInfo set balance=@balance+@transMoney where cardID=@cardID
  print '存儲成功,存儲金額為'+convert(varchar(20),@transMoney)
  --方法1
  SELECT @newbalance=balance from cardInfo where cardID=@cardID
  PRINT '卡號'+convert(varchar(19),@cardID)+'目前余額'+convert (varchar(8),@newbalance)
  --方法2
  --print '卡號'+convert(varchar(19),@cardID)+'目前余額'+convert (varchar(8),@balance+@transMoney)--對的
  
 end
else if (@transType='支取')
 begin  
  if(@balance-@transMoney<1)
   begin 
    raiserror('支取失敗，余額不足',16,1)
    print '卡號'+convert(varchar(19),@cardID)+'目前余額'+convert (varchar(8),@balance)
    rollback  tran--回滾事務，取消交易
    
   end 
  else 
   begin 
    print '交易正在進行中,請稍候....'
    update cardInfo set balance=@balance-@transMoney where cardID=@cardID
    print '支取成功,支取金額為:'+convert(varchar(20),@transMoney)
    --方法1
    SELECT @newbalance=balance from cardInfo where cardID=@cardID
    PRINT '卡號'+convert(varchar(19),@cardID)+'目前余額'+convert (varchar(8),@newbalance)
    --方法2
    --print '卡號'+convert(varchar(19),@cardID)+'目前余額'+convert (varchar(8),@balance-@transMoney)--對的
   end  
 end
go

--8.2.1測試觸發器：存入
declare @cardID2 char (19)
select @cardID2=cardID from cardInfo 
where customerID=(select customerID from userInfo where customername='李四')
if  not exists (select * from cardInfo where cardID=@cardID2)
 begin
  raiserror ('沒有該用戶',16,1)
  return
 end 
insert into transInfo(cardID,transType, transMoney)
values (@cardID2,'存入',100)

--8.2.2測試觸發器：支取
declare @card char(19)
select @card=cardID from cardInfo Inner join userinfo on 
cardInfo.customerID  =userInfo.customerID   where customername='張三'
if  not exists (select * from cardInfo where cardID=@card)
 begin
  raiserror ('沒有該用戶',16,1)
  return
 end 
insert into transInfo(cardID,transType, transMoney  )
values (@card,'支取',200) 

/*************************10創建存儲過程*****************************/
--------------------------10.1取錢或存錢的存儲過程-------------------
if exists (select * from sysobjects where name='proc_takeMoney')
drop procedure proc_takeMoney
go
create procedure proc_takeMoney
 @IDcard char(19),--交易卡號
 @m money,--交易金額
 @type char(8),--交易類型
 @inputpass char(6)=' '--取款密碼
as

if (@inputpass<>(select pass from  cardInfo where cardID=@IDcard))--判斷密碼是否相符
 begin 
  raiserror ('密碼錯誤,請核實',16,1)
  
  return 
 end 
if  not exists (select * from cardInfo where cardID=@IDcard)--判斷用戶信息表中是否存在取錢或存錢的用戶
 begin
  raiserror ('沒有該用戶',16,1)
  return
 end 

insert transInfo (cardID,transType,transMoney) values (@IDcard,@type,@m)
insert into transInfo values(default,@IDcard,@type,@m,default)

go

--測試：調用取錢或存錢的存儲過程
declare @card char(19)
select @card=cardID from cardInfo
where customerID =(select customerID from userInfo where customername='張三')
exec proc_takemoney @card,300,'支取','123456'--調用過程，執行取錢
go

declare @card2 char(19)
select @card2=cardID from cardInfo
where customerID =(select customerID from userInfo where customername='李四')
exec proc_takemoney @card2,300,'存入','123123'--調用過程，執行存錢
go

--------------------10.2產生隨機卡號的存儲過程proc_randCardID----------------
--說明*銀行卡號共１９位（４位移組，中間用空格隔開），
--對于某個銀行，前８個數字是固定的,前８位固定數字設置為1010 3576
--后面８個數字是固定的，后面的８個數字要求隨機的，并且唯一的，
--隨機種子＝當前月份數*10000＋當前的秒數*1000+當前的毫秒數
--產生了０～１的隨機數后，取小數點后８位，即: 0.xxxxxxxx
if exists (select * from sysobjects where name='proc_randCardID')
drop procedure proc_randCardID
go

create procedure proc_randCardID
@randCardID char(19)  output,
@id char(10)='1010 3576' --前8位
as 
declare @r numeric(15,8)--15位數，保留8位小數---隨機數
declare @tempStr  char (10)

select @r=rand((datepart(mm,getdate())*100000)+(datepart(ss,getdate())*1000)
  +datepart (ms,getdate()))--隨機數

set @tempStr=convert(char(10),@r)--隨機數　轉換類型
set @randCardID=@id+''+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4) 
go
--測試：調用隨機卡號的存儲過程
declare @mycardID char(19)
execute proc_randCardID @mycardID output--執行存儲過程
print '產生的隨機卡號為:'+@mycardID
go

------------------10.3開戶的存儲過程proc_openAccount--------------------
if exists (select * from sysobjects where name='proc_openAccount')
drop procedure proc_openAccount--如果開戶存儲過程則刪除
go

create procedure proc_openAccount--創建開戶存儲過程
--輸入參數
@customerName char(8),--客戶姓名
@PID char(18),--身份證
@telephone char(13),--電話號碼
@openMoney money,--開戶金額
@savingType char(8),--開戶類型
@address varchar(50)=''--地址

as
declare @mycardID_2  char(19)--卡號
,@openDate datetime--開戶時間
execute proc_randCardID @mycardID_2 output--調用隨機卡號存儲過程產生卡號
while exists (select * from cardINfo where cardID=@mycardID_2)--如果存在則重新產生新號
execute proc_randCardID @mycardID_2 output--產生隨機卡號
--向用戶表　userInfo　插入開戶　客戶姓名，身份證，電話號碼，地址
insert into userInfo  values (@customerName,@PID,@telephone,@address)

declare @cur_customerId int--顧客編號
select @cur_customerId=customerID from userInfo where PID=@PID --獲取新開戶顧客編號
--向銀行卡信息表 cardInfo 插入數據 卡號，存款類型，余額，顧客編號
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values (@mycardID_2,@savingType,@openMoney,@openMoney,@cur_customerId)


--方法１：子查詢　得到　開戶用戶的開戶時間
select distinct @openDate=openDate from cardinfo 
where  customerID = (select customerID from userinfo where customername=@customerName and customerID=@@identity)
--方法２:用內連接查詢　得到　開戶用戶的開戶時間
select distinct @openDate=openDate from cardinfo 
inner join userinfo on userinfo.customerID =cardinfo.customerID  where  customername=@customerName
--方法３：用變量 @cur_customerId 得到 開戶用戶的開戶時間
select distinct @openDate=openDate from cardinfo 
where  customerID = @cur_customerId
--開戶成功后-顯示 信息
print ''
print '尊敬的客戶 ：'+@customerName
print '恭喜您開戶成功!'
print '系統為您產生的隨機卡號為'+convert(varchar(19),@mycardID_2)
print '開戶日期'+convert(varchar(10),@openDate,111)+
'  開戶金額:'+convert(varchar(10),@openMoney)+'元'

go

--測試調用開戶的存儲過程proc_openAccount
execute proc_openAccount '王','334456889012678','0000-63598978',100,'活期','河南新鄉'

execute proc_openAccount @customername='趙',@PID ='213445789123422222',
@telephone='0760-44446666',@openMoney=1,@savingType='定期'

/**********************11創建事務 利用事務,模擬銀行轉賬功能*************/
if exists (select * from sysobjects where name='proc_transfer')--判斷存儲過程是否存在
drop procedure proc_transfer--如果存在則刪除
go
create procedure proc_transfer--創建存儲過程
--輸入參數
@card1 char(19),--支取卡號
@card2 char(19),--存入卡號
@transMoney money--交易金額
as
 begin tran --開始事務

if  not exists (select * from cardInfo where cardID=@card1) or
  not exists (select * from cardInfo where cardID=@card2)--判斷用戶是否存在
 begin
 raiserror ('沒有該用戶，請核實',16,1)
 rollback tran--回滾事務
 return
 end 
declare @errors int
     set @errors=0
declare @balance money
select @balance=balance from cardInfo where cardID=@card1--從銀行信息表獲取支取客戶的余額
insert into transInfo (transType,cardID,transMoney )values ('支取',@card1,@transMoney)--存在insert觸發器
set @errors=@errors+@@error
insert into transInfo (transType,cardID,transMoney )values ('存入',@card2,@transMoney)
set @errors=@errors+@@error


if (@errors>0 or @balance-@transMoney<1)--判斷支取用戶余額是否大于 將要 轉出的金額
 begin
  print'轉賬失敗，回滾事務'--此句沒有作用了
  rollback tran--回滾事務
 end
else 
 begin
  commit tran--提交事務
  print '轉賬成功' 
 end
go
 
--測試轉賬事務存儲過程 模擬從李四帳上轉2000元到張三帳上
print '開始轉賬，請稍候...'
declare @card1 char(19),@card2 char(19)--從用戶信息表中查詢李四的卡號
select @card1=cardID from cardInfo inner join userinfo 
on cardInfo.customerID=userinfo.customerID
where customername='李四'

select @card2=cardID from cardInfo --從用戶信息表 查詢張三的卡號
where customerID =(select customerID from userInfo where customername='張三')

exec proc_transfer @card1,@card2,2000--調用執行轉賬事務存儲過程

select * from view_cardInfo--查看銀行信息視圖
select * from view_transinfo--查看交易信息視圖

/*******************12創建登陸賬號和數據用戶*************************/
--1添加sql登錄賬號
if not exists(select * from master.dbo.syslogins where loginname='fifille')
    begin
      exec sp_addlogin 'fifille','841023'    --添加SQL登錄帳號       
    end
  go

--2創建數據庫用戶
exec sp_revokedbaccess  'fifilledbuser'--刪除
exec sp_grantdbaccess 'fifille','fifilledbuser'--添加
--3增加權限  為 fifilledbuser (增刪改查的權限)
grant select,insert,delete on transinfo to fifilledbuser
 
select * from userInfo--1用戶信息表userInfo
select * from cardInfo--2銀行卡信息表：cardInfo 
select * from transInfo--3交易信息表 transInfo

 

--臨時表
SELECT  *   INTO  #用戶信息表臨時表  FROM  userInfo

GO

SELECT  *  FROM  #用戶信息表臨時表
select * from systypes