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

长时间不写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