创建数据库、表、主外键、各种约束、存储过程、视图、索引、事务使用、触发器、创建登录账号、数据库用户 Sql语句示例

-----------创建数据库----------------
user master
go
if
 exists (select * from sysdatabases where name=
'bankSystem'
)
    drop database bankSystem
go
create database bankSystem
on primary
(
    name='bank_data'
,
    filename='D:\bank\bank_data.mdf'
,
    size=5,
    filegrowth=15%
)
log on
(
    name='bank_log'
,
    filename='D:\bank\bank_log.ldf'
,
    size=5,
    filegrowth=15%
)
go

----------------建表并添加约束----------------
use bankSystem
go
---------------------表userInfo--------------
if
 exists (select * from sysObjects where name=
'userInfo'
)
    drop table userInfo
go
create table userInfo
(
    customerID int
 identity(1,1) not 
null
,
    customerName varchar(30) not null
,
    PID varchar(18) not null
,
    telephone varchar(13) not null
,
    address varchar(50)
)
go
alter table userInfo
add constraint PK_customID primary key (customerID)
alter table userInfo
add constraint UQ_pid unique (PID)
alter table userInfo
add constraint CK_pid check (pid like '4206[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 '4206[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
)

alter table userInfo
add constraint CK_telephone check (telephone like '13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
 or
                                    telephone like '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
)
------------表cardInfo---------------
if
 exists (select * from sysObjects where name=
'cardInfo'
)
    drop table cardInfo
go
create table cardInfo
(
    cardID varchar(18) not null
,
    curType varchar(10) not null
,
    savingType varchar(10) not null
,
    openDate datetime not null
,
    openMoney money not null
,
    balance money not null
,
    pass varchar(6) not null
,
    IsReportLoss bit not null
,
    customerID int
 not 
null
)
go
alter table cardInfo
add constraint PK_cardID primary key (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]'
)
alter table cardInfo
add constraint DF_curType default
 (
'RMB'
) 
for
 curType
alter table cardInfo
add constraint DF_openDate default
 (getDate()) 
for
 openDate
alter table cardInfo
add constraint CK_openMoney check (openMoney >=1)
alter table cardInfo
add constraint CK_balance check (balance>=1)
alter table cardInfo
add constraint CK_pass check (pass like '[0-9][0-9][0-9][0-9][0-9][0-9]'
)
alter table cardInfo
add constraint DF_pass default
 (888888) 
for
 pass
alter table cardInfo
add constraint DF_IsReportLoss default
 (0) 
for
 IsReportLoss
alter table cardInfo
add constraint FK_customerID foreign key (customerID) references userInfo (customerID)
alter table cardInfo
add constraint CK_savingType check (savingType like '活期'
 or savingType like 
'定活两期'
 or savingType like 
'定期'
)

---------------表transInfo-------------
if
 exists (select * from sysObjects where name=
'transInfo'
)
    drop table transInfo
go
create table transInfo
(
    transDate datetime not null
,
    cardID varchar(18) not null
,
    transType varchar(4) not null
,
    transMoney money not null
,
    remark text
)
go
alter table transInfo
add constraint DF_transDate default
 (getDate()) 
for
 transDate
alter table transInfo
add constraint FK_cardID foreign key (cardID) references cardInfo (cardID)
alter table transInfo
add constraint CK_transType check (transType like '存入'
 or transType like 
'支取'
)
alter table transInfo
add constraint CK_transMoney check (transMoney >0)
go

--------------插入数据----------------
insert into userInfo values ('张三'
,
'420656789012345'
,
'010-67898978'
,
'北京海淀'
)
insert into userInfo values ('李四'
,
'420645678912345678'
,
'0478-44443333'
,
default
)
insert into cardInfo values ('1010 3576 1212 113'
,
default
,
'定期'
,
'2007-10-10 11:54:36.812'
,1,1,
default
,
default
,2)
insert into cardInfo values ('1010 3576 1234 567'
,
default
,
'活期'
,
'2007-10-10 11:58:45.352'
,1000,1000,
default
,
default
,1)

insert into transInfo (transDate,transType,cardID,transMoney) values ('2007-10-24 11:56:36.812'
,
'支取'
,
'1010 3576 1234 567'
,900)
update cardInfo set
 balance=balance-900 where cardID=
'1010 3576 1234 567'
insert into transInfo (transDate,transType,cardID,transMoney) values ('2007-10-24 11:56:54.245'
,
'存入'
,
'1010 3576 1212 113'
,5000)
update cardInfo set
 balance=balance+5000 where cardID=
'1010 3576 1212 113'

---------------修改密码--------------
update cardInfo set
 pass=
'123456'
 where cardID=
'1010 3576 1234 567'
update cardInfo set
 pass=
'123123'
 where cardID=
'1010 3576 1212 113'

-------------是否挂失------------
update cardInfo set
 IsReportLoss=1 where cardID=
'1010 3576 1212 113'

---------------统计银行资金流通余额和盈利结算--------------
declare @inMoney money,@outMoney money,@sumMoney money,@rateEnd money
select @inMoney=sum(transMoney) from transInfo where transType='存入'
select @outMoney=sum(transMoney) from transInfo where transType='支取'
set
 @sumMoney=@inMoney-@outMoney
set
 @rateEnd=@outMoney*0.008-@inMoney*0.003
print '银行流余额总计为：'
+convert(varchar(20),@sumMoney)+
'RMB'
print '盈利结算结果为：'
+convert(varchar(20),@rateEnd)+
'RMB'


---------------查询本周开户卡号----------------
select cardID from cardInfo where datepart(wk,openDate)=datepart(wk,getDate())


----------------查询本月交易金额最高卡号------------
select * from userInfo where customerID in
        (select customerID from cardInfo where cardID in
            (select cardID from cardInfo where datepart(wk,openDate)=datepart(wk,getDate())))

----------------本月交易金额最大的卡号为-----------------
select cardID from transInfo where transMoney=(
        select max(transMoney) from transInfo where datepart(mm,transDate)=datepart(mm,getDate()))

---------------------挂失帐号客户信息----------------
select customerName as
 客户姓名 telephone 
as
 电话 from userInfo where customerID 
in
        (select customerID from cardInfo where isreportloss = 1)

-------------------------催款提醒业务-------------
select customerName as
 客户姓名,telephone 
as
 电话,balance 
as
 卡上余额 from userInfo inner join cardInfo
        on userInfo.customerID=cardInfo.customerID where userInfo.customerID in
        (select customerID from cardInfo where balance < 200)

------------创建索引--------------
if
 exists (select * from sysindexes where name=
'IX_cardid'
)
    drop index transInfo.IX_cardid
go
create nonclustered index IX_cardid
    on transInfo (cardID)
    with fillfactor=70
go

select * from transInfo with(index=IX_cardid) where cardID ='1010 3576 1212 113'

------------创建视图-------------
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
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
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_userInfo
select * from view_cardInfo
select * from view_transInfo
go

---------------创建存储过程----------------
if
 exists (select * from sysobjects where name=
'proc_getAndoutMoney'
)
    drop proc proc_getAndoutMoney
go
create proc proc_getAndoutMoney
    @cardID varchar(19),
    @money money,
    @type char
(4),
    @inputpass char
(6)
    as
        if
(@type=
'支取'
)
            begin
                begin transaction
                declare @error int
                set
 @error = 0
                if
(@inputpass=(select pass from cardInfo where cardID=@cardID))
                    begin
                        update cardInfo set
 balance=balance-@money where cardID=@cardID
                        set
 @error=@error+@@error
                        insert into transInfo values (getDate(),@cardID,@type,@money,default
)
                        set
 @error=@error+@@error
                    end
                else
                    print '密码错误'
                if
 (@error<>0)
                    rollback transaction
                else
                    commit transaction
            end
        else
            begin
                update cardInfo set
 balance=balance+@money where cardID=@cardID
                insert into transInfo  values (getDate(),@cardID,@type,@money,default
)
            end
go
exec proc_getAndoutMoney '1010 3576 1212 113'
,500,
'存入'
,
'000000'
exec proc_getAndoutMoney '1010 3576 1234 567'
,300,
'支取'
,
'123446'
select * from cardInfo where cardID='1010 3576 1212 113'

-----------随机产生卡号----------
use bankSystem
go
if
 exists (select * from sysobjects where name=
'proc_randCardID'
)
    drop proc proc_randCardID
go
create proc proc_randCardID
    @randCardID varchar(18) output
    as
        declare @rand numeric(15,8)
        declare @tempStr varchar(16)
        select @rand=rand((datepart(mm,getDate())*100000+datepart(ss,getDate())*1000+datepart(ms,getDate())))
        set
 @tempStr=convert(varchar(16),@rand)
        set
 @randCardID=
'1010 3576 '
+subString(@tempStr,3,4)+
' '
+subString(@tempStr,7,3)
go
declare @mycardID varchar(18)
exec proc_randCardID @mycardID output
print '随机产生卡号为：'
+@mycardID

-------------开户存储过程--------------
set
 nocount on
if
 exists (select * from sysobjects where name=
'proc_openAccount'
)
    drop proc proc_openAccount
go
create proc proc_openAccount
    @customerName varchar(8),
    @PID varchar(18),
    @telephone char
(13),
    @openMoney money,
    @savingType char
(8),
    @address varchar(50)=' '
    as
        declare @cardID varchar(18)
        declare @customerID int
        while
(1=1)
            begin
                exec proc_randCardID @cardID output
                if
 not exists (select * from cardInfo where cardID=@cardID)
                    break
                else
                    continue
            end
        if
 (@openMoney<1)
            return
        else
            begin
                begin transaction
                    declare @error int
                    set
 @error=0
                    insert into userInfo values (@customerName,@PID,@telephone,@address)
                    set
 @error=@error+@@error
                    select @customerID=customerID from userInfo where customerName=@customerName
                    set
 @error=@error+@@error
                    insert into cardInfo (cardID,savingType,openMoney,balance,customerID)
                            values (@cardID,@savingType,@openMoney,@openMoney,@customerID)
                    set
 @error=@error+@@error
                if
(@error<>0)
                    rollback transaction
                else
                    commit transaction
            end
        print '尊敬的客户，开户成功！系统为您产生的随机卡号为：'
+@cardID+
' 开户日期 '
+
                convert(varchar(30),getDate(),111)+' 开户金额 '
+convert(varchar(10),@openMoney)
go
exec proc_openAccount '王五'
,
'420656889012678'
,
'2222-63598978'
,1000,
'活期'
,
'河南新乡'


-----------------转账事务------------------
if
 exists (select * from sysobjects where name=
'proc_transfer'
)
    drop proc proc_transfer
go
create proc proc_transfer
    @card1 char
(18),
    @card2 char
(18),
    @outMoney money
    as
        begin transaction
            declare @error int
            set
 @error=0
            update cardInfo set
 balance=balance-@outMoney where cardID=@card1
            set
 @error=@error+@@error
            insert into transInfo values (getDate(),@card1,'支取'
,@outMoney,
default
)
            set
 @error=@error+@@error
            update cardInfo set
 balance=balance+@outMoney where cardID=@card2
            set
 @error=@error+@@error
            insert into transInfo values (getDate(),@card2,'存入'
,@outMoney,
default
)
            set
 @error=@error+@@error
            if
 (@error<>0)
                rollback transaction
            else
                commit transaction
go
exec proc_transfer '1010 3576 1212 113'
,
'1010 3576 1234 567'
,2000
select * from transInfo where cardID='1010 3576 1212 113'
select * from transInfo where cardID='1010 3576 1234 567'

-------------------创建登录帐号和数据库用户---------------------
exec sp_addlogin 'sysAdmin'
,
'1234'
exec sp_grantdbaccess 'sysAdmin'
,
'sysAdminBankUser'
grant insert,update,delete,select on userInfo,cardInfo,transInfo to sysAdminBankUser

补上触发器：

--update触发器
set
 nocount on
if
 exists (select * from sysobjects where name =
'trig_ppr_Update'
)
    drop trigger trig_ppr_Update
go
create trigger trig_ppr_Update
    on PlanPropertyRelation
    --with encryption --加密
        for
 update
            as
                declare @Plans_Id int
,@currentId 
int
,@childId 
int
,@childNewLevel 
int
                declare @oldFatherId int
,@newFatherId 
int
                select @oldFatherId=PPR_ProExtend_ID from deleted
                select @newFatherId=PPR_ProExtend_ID from inserted
                if
(@oldFatherId=@newFatherId)
                    return
;
                else
                begin
                    select @Plans_Id=Plans_ID,@childId=Property_ID,@currentId=PlanPropertyRelation_ID from inserted
                    delete from PropertyValuesRelation where PlanPropertyRelation_ID=@currentId
                end
go

--删除触发器，删除前触发
set
 nocount on
if
 exists (select * from sysobjects where name =
'trig_ppr_BeforeDelete'
)
    drop trigger trig_ppr_BeforeDelete
go
create trigger trig_ppr_BeforeDelete
    on PlanPropertyRelation
    --with encryption --加密
        INSTEAD OF delete
            as
            declare @oldId int
,@error 
int
            select @oldId=PlanPropertyRelation_ID from deleted
            delete from PropertyValuesRelation where PlanPropertyRelation_ID=@oldId
            delete from PlanPropertyRelation where PlanPropertyRelation_ID=@oldId
go