use bankDB
GO
--創建索引：
if exists (select * from sysindexes where name='index_transInfo_cardID')
drop index transInfo.index_transInfo_cardID
GO
create nonclustered index index_transInfo_cardID
on transInfo(cardID)
with fillfactor=70
GO
select * from transInfo(index=index_transInfo_cardID) where cardID='1010 3576 1234 5678'
GO

--創建視圖：
create view view_userInfo
as
select 客戶編號=customerID,開戶名=customerName,身份證號=PID,電話號碼=telephone,居住地址=address from userInfo
GO
create view view_cardInfo
as
select 卡號=cardID,貨幣種類=curType,存款類型=savingType,開戶日期=openDate,開戶金額=openMoney,余額=balance,密碼=pass,是否掛失=IsReportLoss,顧客編號=customerID from cardInfo
GO
create view view_transInfo
as
select 交易日期=transDate,卡號=cardID,交易類型=transType,交易金額=transMoney,備注=remark from transInfo
GO
select * from view_userInfo
select * from view_cardInfo
select * from view_transInfo
GO