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 货币种类 必填，默认为RMB
savingType 存款类型 活期/定活两便/定期
openDate 开户日期 必填，默认为系统当前日期
openMoney 开户金额 必填，不低于1元
balance 余额 必填，不低于1元,否则将销户
pass 密码 必填，6位数字，开户时默认为6个“8”
IsReportLoss 是否挂失 必填，是/否值，默认为”否”
customerID 顾客编号 外键，必填，表示该卡对应的顾客编号，一位顾客允许办理多张卡号
*/
alter table cardInfo
add
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 交易金额 必填，大于0
remark 备注 可选输入，其他说明
*/
alter table transInfo
add
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)
此业务的逻辑图：
四：插入数据：
/*
张三开户, 身份证:123456789012345, 电话:010-67898978, 地址:北京海淀
开户金额:1000 活期 卡号: 1010 3576 1234 5678
李四开户， 身份证:321245678912345678, 电话:0478-44443333,
开户金额:1 定期 卡号:1010 3576 1212 1134
*/
insert into userInfo(customerName,PID,telephone,address)
values('张三','123456789012345','0102-67898978','北京海淀')
insert into cardInfo(cardID,savingType, openMoney, balance, customerID)
values('1010 3576 1234 5678','活期', 1000,1000,3)
insert into userInfo(customerName,PID,telephone)
values('李四','321245678912345678','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)<DATEPART(weekday,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 OUTPUT print '产生的随机卡号为：'+@mycardID GO
--开户的存储过程
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 '王五' ,'334456889012678','2222-63598978','1000','活期','河南新乡' exec proc_openAccount '赵二' ,'213445678912342222','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
