/****************2創建數據庫*******************/
use master
go

if exists (select * from sysdatabases where name='bankDB')--檢查系統中是否已存在數據庫atm
drop database bankDB --如果存在則刪除

go
exec xp_cmdshell 'mkdir G:\二期學習\SQL Server\bank' ,no_output--調用DOS命令創建文件夾
create database bankDB --新建數據庫
on primary  --主文件
(
name = 'atm_data', --邏輯名稱
filename='G:\二期學習\SQL Server\bank\bankDB_data.mdf', --物理名稱
size=10 , --初始大小
filegrowth=15%  --增長率
)
log on --日志文件
(
name='atm_log', --邏輯名稱
filename='G:\二期學習\SQL Server\bank\bankDB_log.ldf',--物理名稱
size=1 , --初始大小
filegrowth=10% --增長率
)
go

/**********************3建表***********************/
use bankDB
go
-------------------1用戶信息表userInfo------------------
if exists (select * from sysobjects where name='userInfo')
drop table userInfo
go
create table userInfo
(
customerID  int identity(1,1) not null,--顧客編號，自動編號（標識列),從1開始,主鍵
customerName varchar (8) not null ,--開戶名
PID varchar(18) not null,--身份證號,必填，只能是18位或15位，身份證號唯一約束
telephone varchar(18) not null,--聯系電話,必填，格式為xxxx-xxxxxxxx或手機號13位
address varchar(20)--居住地址,可選輸入
)

--------------------2銀行卡信息表cardInfo------------------
if exists (select * from sysobjects where name='cardInfo')
drop table  cardInfo
go

create table cardInfo
(
cardID char (19) not null,--卡號，必填，主健,銀行的卡號規則和電話號碼一樣，一般前8位代表特殊含義 假定該行要求其營業廳的卡號格式為：1010 3576 xxxx xxx開始,每4位號碼后有空格，卡號一般是隨機產生.
curType  char(4) not null, --貨幣種類 
savingType char(8), --存款類型 活期/定活兩便/定期
openDate datetime not null,--開戶日期 
openMoney money not null, --開戶金額 
balance money not null, --余額  
pass int  not null ,--密碼  
IsReportLoss bit  not null, --是否掛失  
customerID int not null --顧客編號 
)
go
------------------3交易信息表transInfo -------------------
if exists (select * from sysobjects where name='transInfo')
drop table  transInfo
go

create table transInfo
(
transDate  datetime not null,--交易日期，必填，默認為系統當前日期
cardID char (19) not null,--卡號，必填，外健，可重復索引
transType  char(8) not null,--交易類型，必填，只能是存入/支取
transMoney money not null,--交易金額，必填，大于0
remark text--備注，可選輸入，其他說明
)
go

/************************4添加約束*****************************/

-----------------------1用戶信息表userInfo 添加約束--------------

--主鍵約束customerID 顧客編號 自動編號（標識列）
if exists (select * from sysobjects where name='pr_customerID')
alter table userInfo
drop constraint pr_customerID
alter table userInfo
add constraint pr_customerID primary key (customerID)

--檢查約束customerID 身份證號 只能是18位或15位
if exists (select * from sysobjects where name='ck_PID')
alter table userInfo
drop constraint ck_PID
alter table userInfo
add constraint ck_PID check (
PID like '[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]'
or PID like '[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,x]'
)


--唯一約束customerID身份證號 
if exists (select * from sysobjects where name='uq_PID')
alter table userInfo
drop constraint uq_PID
alter table userInfo
add constraint uq_PID  unique(customerID)


--檢查約束telephone電話號碼格式為xxxx-xxxxxxxx或手機號13位
if exists (select * from sysobjects where name='ck_telephone')
alter table userInfo
drop constraint ck_telephone
alter table userInfo
add constraint ck_telephone check 
(telephone like '1[3,5][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or
telephone like '[0][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or
telephone like '[0][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' )
--(len(telephone)-13 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][0-9])

 

-----------------2銀行卡信息表cardInfo 添加約束------------------
--主鍵約束 cardID 卡號
if exists (select * from sysobjects where name='pr_cardID')
alter table cardInfo
drop constraint pr_cardID
alter table cardInfo
add constraint pr_cardID primary key (cardID)

--檢查約束 cardID 卡號格式為：1010 3576 xxxx xxx開始
if exists (select * from sysobjects where name='ck_cardID')
alter table cardInfo
drop constraint ck_cardID
alter table cardInfo
add constraint ck_cardID check 
(cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]')

--檢查約束  存款類型 活期/定活兩便/定期
if exists (select * from sysobjects where name='ck_savingType')
alter table cardInfo
drop constraint ck_savingType
alter table cardInfo
add constraint ck_savingType 
check (savingType in('活期','定活兩便','定期'))

--默認約束 curType貨幣種類 默認RMB
if exists (select * from sysobjects where name='df_curType')
alter table cardInfo
drop constraint df_curType
alter table cardInfo
add constraint df_curType 
default ('RMB') for curType 

--默認約束 openDate開戶日期 默認為系統當前日期
if exists (select * from sysobjects where name='df_openDate')
alter table cardInfo
drop constraint df_openDate
alter table cardInfo
add constraint df_openDate 
default (getdate()) for openDate


--檢查約束 openMoney開戶金額 不低于1元
if exists (select * from sysobjects where name='ck_openMoney')
alter table cardInfo
drop constraint ck_openMoney
alter table cardInfo
add constraint ck_openMoney 
check (openMoney>=1)

--檢查約束 balance余額 不低于1元,否則將銷戶
if exists (select * from sysobjects where name='ck_balance')
alter table cardInfo
drop constraint ck_balance
alter table cardInfo
add constraint ck_balance 
check (balance>=1)

--檢查約束 pass密碼為6位
if exists (select * from sysobjects where name='ck_pass')
alter table cardInfo
drop constraint ck_pass
alter table cardInfo
add constraint ck_pass 
check (len(pass)=6)

--默認約束 pass密碼默認888888
if exists (select * from sysobjects where name='df_pass')
alter table cardInfo
drop constraint df_pass
alter table cardInfo
add constraint df_pass 
default (888888) for pass

--默認約束 IsReportLoss 是否掛失  是/否值，默認為”否”
if exists (select * from sysobjects where name='df_IsReportLoss')
alter table cardInfo
drop constraint df_IsReportLoss
alter table cardInfo
add constraint df_IsReportLoss 
default (0) for IsReportLoss 

--檢查約束  IsReportLoss 是否掛失  只能為是/否值 
if exists (select * from sysobjects where name='ck_IsReportLoss')
alter table cardInfo
drop constraint ck_IsReportLoss
alter table cardInfo
add constraint ck_IsReportLoss 
check (IsReportLoss in (1,0))

--外鍵約束 customerID 表示該卡對應的顧客編號,一位顧客允許辦理多張卡號
if exists (select * from sysobjects where name='fr_customerID')
alter table cardInfo
drop constraint fr_customerID
alter table cardInfo
add constraint fr_customerID 
foreign key (customerID) references userInfo(customerID)

 

------------------3交易信息表transInfo 添加約束---------------

--默認約束 transDate交易日期，默認為系統當前日期
if exists (select * from sysobjects where name='df_transDate')
alter table transInfo 
drop constraint df_transDate
alter table transInfo 
add constraint df_transDate 
default (getdate()) for transDate

--外鍵約束 cardID 卡號
if exists (select * from sysobjects where name='pr_cardID2')
alter table transInfo 
drop constraint pr_cardID2
alter table transInfo 
add constraint pr_cardID2
foreign key (cardID) references  cardInfo(cardID)

--檢查約束 transType 交易類型 只能是存入/支取
if exists (select * from sysobjects where name='ck_transType')
alter table transInfo
drop constraint ck_transType
alter table transInfo
add constraint ck_transType 
check (transType in('存入','支取'))

--檢查約束 transMoney 交易金額 大于0
if exists (select * from sysobjects where name='ck_transMoney')
alter table transInfo
drop constraint ck_transMoney
alter table transInfo
add constraint ck_transMoney 
check (transMoney>0)


/*********************5插入數據*******************************/
---------------1測試 插入 userInfo 用戶信息表 和 cardInfo銀行卡信息表 數據------
insert userInfo (customerName, PID ,telephone, address)
values  ( '張三','123456789012345','010-67898978','北京海淀' )
insert cardInfo (cardID ,savingType ,openMoney,balance,customerID)
values ('1010 3576 1234 5678','活期',1000,1000,@@identity)

insert userInfo (customerName, PID ,telephone)
values('李四','321245678912345678','0478-44443333')
insert cardInfo (cardID ,savingType ,openMoney,balance,customerID)
values ('1010 3576 1212 1134','定期',1 ,1 ,@@identity)


-- delete  from cardInfo  delete  from userInfo 

-----------------2測試 手動取款交易 插入交易信息表transInfo數據--------------
--測試1 手動添加交易表信息,更新卡號信息表中的數據
declare @cardid  char(19)--定義變量 @cardid 卡號

select @cardid=cardid from cardInfo --從userInfo用戶表中獲取客戶為 張三 的卡號cardid
where customerID=(select customerID from userInfo where customername='張三')

insert into transinfo (transtype,cardid,transmoney)
values ('支取',@cardid,900)--向交易信息表transInfo插入數據：張三 支取 900元 卡號為@cardid 

update cardinfo set balance =balance-900 --更改 cardInfo銀行卡信息表 張三 余額balance 減去900
where customerID =(select customerID from userInfo where customername='張三')

--測試2  手動添加交易表信息,更新卡號信息表中的數據
declare @cardid2  char(19)--定義變量 @cardid 卡號

select @cardid2=cardid from cardInfo  --從userInfo用戶表中獲取客戶為 李四 的卡號cardid
where customerID=(select customerID from userInfo where customername='李四')

insert into transinfo (transtype,cardid,transmoney)
values ('存入',@cardid2,5000)--向交易信息表transInfo插入數據：李四 存入 5000元 卡號為@cardid2 

update cardinfo set balance =balance+5000 --更改 cardInfo銀行卡信息表 李四 余額balance 增加5000
where customerID =(select customerID from userInfo where customername='李四')

 

/******************************6常規業務模擬***************************/

----------------------6.1修改密碼--------------------------
--說明*修改張三(卡號為 1010 3576 1234 5678) 銀行卡號密碼為123456
update  cardInfo set pass=123456 
where customerid=(select customerid from userInfo where customername='張三')

--說明*修改李四(卡號為 1010 3567 1212 1134) 銀行卡號密碼為123123
update  cardInfo set pass=123123
where customerid=(select customerid from userInfo where customername='李四')
 
select * from cardInfo--2銀行卡信息表：cardInfo 

------------------------6.2掛失帳號----------------------------
--說明*李四 (卡號為1010 3567 1212 1134) 因銀行卡丟失,申請掛失
--說明*修改張三(卡號為 1010 3576 1234 5678) 銀行卡號密碼為123456
update  cardInfo set pass=123456 
where customerid=(select customerid from userInfo where customername='張三')

--說明*修改李四(卡號為 1010 3567 1212 1134) 銀行卡號密碼為123123
update  cardInfo set pass=123123
where customerid=(select customerid from userInfo where customername='李四')select * from cardInfo--2銀行卡信息表：cardInfo 

------------------------6.3統計銀行的支金流通余額和盈利結算----------
--說明*存入 代表資金流入,支取 代表資金流出

--declare @inMoney money
--select @inMoney- sum(transMoney) from transInfo where (transType-'存入')-sum(transMoney) from transinfo where transType-'支取')--錯誤不要了

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'
--盈利結算-總支取量*0.008-總存入量*0.003
print '盈利結算為'+left(convert(varchar(10),@outMoney*0.008-@inMoney*0.003),4)+'RMB'
 

--------------------------6.4查詢本周開戶的卡號-----------------------
/*declare @xtday datetime
set @xtday- datepart(dw,getdate())

declare @a datetime 
select  @a-openDate from cardInfo where cardID-'1010 3576 1212 1134'
print datediff (weekday,@a,getdate())
print datediff (dd,@a,getdate())
print @xtday 
print datepart(dw,getdate())-1*/

--print '本周開戶的卡號信息如下:'--錯誤的
--select cardID from cardInfo 
--where datediff(day,datepart(weekday,opendate),datepart(weekday,getdate()))<7--錯了
--where datename(dw,opendate)-datename(dw,getdate()) and month(openDate)-month(getdate())and year(openDate)-year(getdate())--同一天的

--顯示樣式1
print '本周開戶的卡號信息如下:'
select cardID from cardInfo 
where datediff (dd,opendate,getdate())<-(datepart(dw,getdate())-1)
select * from cardInfo 
--顯示樣式2
print '本周開戶的卡號信息如下:'
declare @temp int 
select 客戶姓名=customerName,聯系電話=telephone ,開戶金額=openMoney,
開戶日期=opendate from userInfo inner
join cardinfo on  userinfo.customerID=cardinfo.customerID 
where datediff(dd,opendate,getdate())<=(datepart(dw,getdate())-1)
select * from cardinfo

select datepart(dw,getdate())-1  --求出當前周幾
select datediff(dd,opendate,getdate()) from cardinfo
--函數第1個參數放天數,第2個參數填寫列中字段,第3個是當前日期(求出2個日期之間的差值) 

 


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