SqlServer基础语法（二）

先看⼀下腰实现的功能：⼀：创建数据库

/* 检查数据库是否存在，如果存在，删除此数据库 */if exists(select * from sysdatabases where name='bankDB') drop database bankDB go

/*创建数据库bankDB*/ create database bankDB on (

name='bankDB_data',

filename='d:\\bank\\bankDB.mdf', size=10, --增长的速度 filegrowth=15% )

log on (

--⽇志⽂件

name='bankDB_log',

filename='d:\\bank\\bankDB_log.ldf', size=5,

filegrowth=15% )

⼆：创建表的语句：

/* 创建表*/

use bankDB go

create table userInfo --⽤户信息表 (

customerID int identity(1,1), customerName char(8) not null, PID char(18) not null,

telephone char(13) not null, address varchar(50) ) go

create table cardInfo --银⾏卡信息表 (

cardID char(19) not null, curType char(5) not null, savingType char(8) not null, openDate datetime not null, openMoney money not null, balance money not null, pass char(6) not null, IsReportLoss bit not null, customerID int not null ) go

create table transInfo --交易信息表 (

transDate datetime not null, transType char(4) not null, cardID char(19) not null, transMoney money not null, remark text ) go

三：对表添加约束

/* 为userInfo表添加约束

customerID(顾客编号): ⾃动编号（标识列），从1开始，主键 PID(⾝份证号): 只能是18位或15位，⾝份证号唯⼀约束

telephone(联系电话): 格式为xxxx-xxxxxxxx或⼿机号13位 */

alter table userInfo add

constraint pk_customerID primary key(customerID), constraint chk_PID check(len(PID)=18 or len(PID)=15), constraint uq_PID unique(PID),

constraint chk_telephone check(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]' or len(telephone)=13) go

/* cardInfo 表的约束

cardID 卡号 必填，主健，银⾏的卡号规则和电话号码⼀样，⼀般前8位代表特殊含义，

如某总⾏某⽀⾏等。假定该⾏要求其营业厅的卡号格式为：1010 3576 xxxx xxx开始,每4位号码后有空格， 卡号⼀般是随机产⽣。

curType 货币种类 必填，默认为RMBsavingType 存款类型 活期/定活两便/定期

openDate 开户⽇期 必填，默认为系统当前⽇期openMoney 开户⾦额 必填，不低于1元balance 余额 必填，不低于1元,否则将销户

pass 密码 必填，6位数字，开户时默认为6个“8”IsReportLoss 是否挂失 必填，是/否值，默认为”否”

customerID 顾客编号 外键，必填，表⽰该卡对应的顾客编号，⼀位顾客允许办理多张卡号*/

alter table cardInfoadd

constraint pk_cardID primary key(cardID),

constraint ck_cardID check(cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'), constraint df_curType default 'RMB' for curType,

constraint ck_savingType check(savingType in ('活期','定活两便','定期')), constraint df_openDate default getdate() for OpenDate, constraint ck_openMoney check(openMoney>=1), constraint ck_balance check(balance>=1),

constraint ck_pass check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]'), constraint df_pass default '888888' for pass,

constraint df_IsReportLoss default 0 for IsReportLoss,

constraint fk_customerID foreign key(customerID) references userInfo(customerID)go

/* transInfo表的约束

transDate 交易⽇期 必填，默认为系统当前⽇期cardID 卡号 必填，外健，可重复索引

transType 交易类型 必填，只能是存⼊/⽀取transMoney 交易⾦额 必填，⼤于0remark 备注 可选输⼊，其他说明*/

alter table transInfoadd

constraint df_transDate default getdate() for transDate,

constraint ck_transType check(transType in ('存⼊','⽀取')),

constraint fk_cardid foreign key(cardid) references cardInfo(cardID), constraint ck_transMoney check(transMoney>0)

此业务的逻辑图： 四：插⼊数据：

/*

张三开户, ⾝份证:1234567012345, 电话:010-6778, 地址:北京海淀 开户⾦额:1000 活期 卡号: 1010 3576 1234 5678

李四开户， ⾝份证:3212456712345678, 电话:0478-44443333, 开户⾦额:1 定期 卡号:1010 3576 1212 1134 */

insert into userInfo(customerName,PID,telephone,address) values('张三','1234567012345','0102-6778','北京海淀')

insert into cardInfo(cardID,savingType, openMoney, balance, customerID) values('1010 3576 1234 5678','活期', 1000,1000,3)

insert into userInfo(customerName,PID,telephone) values('李四','3212456712345678','0478-44443333')

insert into cardInfo(cardID,savingType, openMoney, balance, customerID) values('1010 3576 1212 1134','定期',1,1,2)

select * from userInfo select * from cardInfo select * from transInfo

/*

张三的卡号取款900元，李四的卡号存款5000元 要求保存交易记录，以便客户查询和银⾏业务统计*/

--张三

/* 交易信息表插⼊交易信息 */

insert into transInfo(transType,cardID,transMoney) values('⽀取','1010 3576 1234 5678',900)

/*更新银⾏卡信息表中的现有余额*/ update cardInfo

set balance=balance-900

where cardID='1010 3576 1234 5678'

--李四

/* 交易信息表插⼊交易信息 */

insert into transInfo(transType,cardID,transMoney) values('存⼊','1010 3576 1212 1134',5000)

/*更新银⾏卡信息表中的现有余额*/ update cardInfo

set balance=balance+5000

where cardID='1010 3576 1212 1134'

五:常规业务操作：

/*---------修改密码-----*/

--1.张三（卡号为1010 3576 1234 5678）修改银⾏卡密码为123456--2.李四（卡号为1010 3576 1212 1134）修改银⾏卡密码为123123 update cardInfo set pass='123456'

where cardID ='1010 3576 1234 5678'

update cardInfo set pass ='123123'

where cardID='1010 3576 1212 1134'

/*--------- 李四的卡号挂失 ---------*/ update cardInfo set IsReportLoss=1

where cardID ='1010 3576 1212 1134'

select * from cardInfo

六：⼀些⽐较复杂的操作：

/* 统计银⾏的资⾦流通余额和盈利结算*/

--统计说明：存储代表资⾦流⼊，取款代表资⾦流出，假定存款利率为千分之三，贷款利率为千分之⼋

declare @inMoney money declare @outMoney money declare @profit money select * from transInfo

select @inMoney=SUM(transMoney) from transInfo where transType='存⼊' select @outMoney=SUM(transMoney) from transInfo where transType ='⽀取' print '银⾏流通⾦额总计为:'+convert(varchar(20),@inMoney-@outMoney)+'RMB' set @profit=@outMoney*0.008-@inMoney*0.003

print '盈利结算为:'+convert(varchar(20),@profit)+'RMB' go

DATEDIFF: 函数返回两个⽇期之间的天数。

/*--------查询本周开户的卡号,显⽰该卡相关信息-----------------*/SELECT * FROM cardInfo WHERE

(DATEDIFF(Day,getDate(),openDate)/*---------查询本⽉交易⾦额最⾼的卡号----------------------*/SELECT DISTINCT cardID FROM transInfo WHERE

transMoney=(SELECT Max(transMoney) FROM transInfo)/*---------查询挂失账号的客户信息---------------------*/

SELECT customerName as 客户姓名,telephone as 联系电话 FROM userInfo

WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1)/*------催款提醒： 如果发现⽤户账上余额少于200元，将致电催款。---*/

select customerName as 客户姓名, telephone as 联系电话, balance as 帐上余额 from userInfo,cardInfo

where userInfo.customerID =cardInfo.customerID and balance <200

DATEPART() 函数⽤于返回⽇期/时间的单独部分，⽐如年、⽉、⽇、⼩时、分钟等等。⼋：视图和索引的应⽤：

--1.创建索引：给交易表的卡号cardID字段创建重复索引 create nonclustered index idx_cardid on transInfo(cardID desc)

--按指定索引查询 张三（卡号为1010 3576 1234 5678）的交易记录 select * from transInfo ( index idx_cardid) where cardID= '1010 3576 1234 5678'

-- 创建视图：查询各表要求字段全为中⽂字段名。

create view view_userInfo --⽤户信息视图 as

select customerID as 客户编号, customerName as 开户名, PID as ⾝份证号, telephone as 电话号码, address as 住址 from userInfo

create VIEW view_cardInfo --银⾏卡信息表视图 AS

select cardID as 卡号,curType as 货币种类, savingType as 存款类型,openDate as 开户⽇期, balance as 余额,pass 密码,IsReportLoss as 是否挂失,customerID as 客户编号 from cardInfo GO

create VIEW view_transInfo --交易信息表视图 AS

select transDate as 交易⽇期,transType as 交易类型, cardID as 卡号,transMoney as 交易⾦额, remark as 备注 from transInfo GO

九：存取钱的存储过程：

1、取钱或存钱的存储过程

create procedure proc_takeMoney @card char(19), @m money, @type char(4),

@inputPass char(6)='' as

print '交易正在进⾏,请稍后...' if (@type='⽀取')

if(select pass from cardInfo where cardID=@card) <>@inputPass begin

raiserror('密码错误',16,1) return end

declare @myTransType char(4), @outmoney money, @mycardID char(19)

select @myTransType =transType, @outmoney=transMoney, @mycardID=cardID from transInfo

where cardID=@card

declare @mybalance money select @mybalance=balance from cardInfo

where cardID=@card

if (@type ='⽀取')

if(@mybalance >=@m +1)

update cardInfo set balance =balance -@m where cardID=@mycardID else begin

raiserror('交易失败!余额不⾜!',16,1)

print '卡号'+@card+' 余额: '+convert(varchar(20),@mybalance) return end

else --存⼊

update cardInfo set balance=balance+@m where cardID=@card

print '交易成功！交易⾦额: '+convert(varchar(20),@m) select @mybalance=balance

from cardInfo where cardID =@card

print '卡号 '+@card+' 余额: '+convert(varchar(20),@mybalance) insert into transInfo(transType,cardID,transMoney) values(@type,@card, @m) go

--raiserror 的作⽤： raiserror 是⽤于抛出⼀个错误

--2、调⽤存储过程取钱或存钱 张三取300, 李四存500

--现实中的取款机依靠读卡器读出张三的卡号，这⾥根据张三的名字查询出卡号来模拟

declare @card char(19) select @card=cardID from cardInfo,userInfo

where cardInfo.customerID=userInfo.customerID and customerName='张三' exec proc_takeMoney @card,300, '⽀取', '123456'

declare @card char(19) select @card=cardID from cardInfo,userInfo

where cardInfo.customerID=userInfo.customerID and customerName='李四' exec proc_takeMoney @card,500, '存⼊'

select * from cardInfo select * from transInfo

--产⽣随机卡号的存储过程

create procedure proc_randCardID @randCardID char(19) OUTPUT AS

DECLARE @r numeric(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='1010 3576 '+SUBSTRING(@tempStr,3,4)+' '+SUBSTRING(@tempStr,7,4)

--测试产⽣随机卡号

DECLARE @mycardID char(19)

EXECUTE proc_randCardID @mycardID OUTPUTprint '产⽣的随机卡号为：'+@mycardIDGO

--开户的存储过程

create procedure proc_openAccount @customerName char(8), @PID char(18),

@telephone char(13), @openMoney money, @savingType char(8), @address varchar(50)='' as

declare @mycardID char(19), @cur_customerID int exec proc_randCardID @mycardID OUTPUT

while exists (select * from cardInfo where cardID=@mycardID) exec proc_randCardID @mycardID OUTPUT

print '尊敬的客户，开户成功!系统为您产⽣了随机卡号:'+@mycardID

print '开户⽇期'+convert(char(10), getdate(),111)+' 开户⾦额:'+convert(varchar(30),@openMoney) if not exists(select * from userInfo where PID=@PID)

insert into userInfo(customerName,PID, telephone, address) values(@customername, @PID, @telephone, @address) select @cur_customerID=customerID

from userInfo where PID =@PID

insert into cardInfo(cardID,savingType, openMoney, balance, customerID)

values(@mycardID, @savingType, @openMoney, @openMoney, @cur_customerID)

--调⽤存储过程重新开户

exec proc_openAccount '王五' ,'3344568012678','2222-635978','1000','活期','河南新乡' exec proc_openAccount '赵⼆' ,'2134456712342222','0760-44446666',1,'定期' select * from userInfo select * from cardInfo

--转帐的事务存储过程

create procedure proc_trans @card1 char(19), @card2 char(19), @outmoney money as

begin tran

print '开始转帐,请稍后......' declare @errors int set @errors=0

exec proc_takeMoney @card1, @outmoney, '⽀取', '123123' set @errors=@errors+@@ERROR

exec proc_takeMoney @card2, @outmoney, '存⼊' set @errors=@errors+@@ERROR if(@errors>0) begin

print '转帐失败!' rollback tran end else begin

print '转帐成功!' commit tran end go

--测试转帐存储过程(从李四帐户转帐2000到张三的帐户)

declare @card1 char(19), @card2 char(19)

select @card1=cardID from cardInfo,userInfo

where cardInfo.customerID=userInfo.customerID and userInfo.customerName='李四'

select @card2=cardID from cardInfo,userInfo

where cardInfo.customerID=userInfo.customerID and userInfo.customerName='张三'

exec proc_trans @card1, @card2, 2000

select * from userInfo select * from cardInfo select * from transInfo

--添加SQL登录帐号

if not exists (select * from master.dbo.syslogins where loginname='Admin') begin

exec sp_addlogin 'Admin','1234'

EXEC sp_defaultdb 'Admin','bankDB' --修改登录默认数据库为bankDB end

--创建数据库⽤户 use bankDB go

exec sp_grantdbaccess 'admin','adminuser'

--给数据库⽤户授权

grant select,insert,update,delete on userInfo to adminuser

grant select,insert,update,delete on cardInfo to adminuser grant select,insert,update,delete on transInfo to adminuser