1、前言与目的

• 为了更好的了解SQL语法与知识体系,本文章以SQLServer2005为案例
• 提高自身对SQL脚本的认识。如：SQL的结构，SQL的关键字的使用
• 在深入了解SQL之后可以提高自己的认知度，比如：工作中不论是建表（建模）或是开发工作中，经过自身的学习，在不同的场景下使用它肯定会意想不到的收获的喜悦与丰收

2、学习三部曲

• 什么是SQL
• 为什么要SQL
• 怎么使用SQL

3、什么是SQL

• 结构化查询语言(Structured Query Language)简称SQL(发音：/?es kju? ?el/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

4、为什么要SQL

• 学习SQL的意义，就在于你学会以后，将掌握数据分析应聘中的SQL能力，并找到一份数据分析工作。例如社群会员扑克牌成功转行数据分析，现在是饿了么业务分析师，就是靠擅长SQL数据分析。

5、怎么使用SQL

• 会利用SQL操作关系数据库进行查询数据
• 使用SQL操作存储过程（创建、删除）等操作
• 对于数据库表的增、删、改、约束、索引，视图，触发器等操作
• 以上场景只是列了部分，在实际工作或学习过程中的场景会更多，就不列举。比如：使用SQL进行数据统计,报表的统计等操作.

6 、SQL实操场景使用

• 经过了解之后，咱们以银行ATM取款机系统项目案例实操进行分解
• SQLServer版本为：SQL Server 2005
• SQLServer 系统数据库表

• 表名表描述(作用)sysdatabases系统数据库，用于查询用户创建的数据库sysobjects系统对象库，用户查询用户创建的数据库的表或视图sysindexes查询数据库的表的索引

SQLServer 常用关键字

• 关键字关键字描述(作用)begin开始，在存储过程或声明声明式脚本中表示SQL语句的开始begin tran开始事务rollback tran事务回滚commit tran提交事务end结束，在存储过程或声明声明式脚本中表示SQL语句的结束view视图，在创建视图中使用go表示去执行的SQL语句proc创建存储过程关键字declare声明变量，变量以@进行修饰exec执行存储过程print打印信息output输出参数filename文件存储到某个地方filegrowth文件以百分之15增长size分配大小use使用当前数据库或说是切换到某个数据库alter修改或改变exists存在

• 创建bankDB数据库

-- `切换master系统主干数据库`
use master 
go
    SET NOCOUNT ON
    if exists(select * from sysdatabases where name='bankDB')
    drop database bankDB
go

--`exec xp_cmdshell 'mkdir D:\bank'  调用DOS创建文件夹`
--`创建一个名称为bankDB数据库`
create database bankDB
ON
(
    name='bankDB_data',
    filename='D:\bank\bankDB_data.mdf', --文件存储到操作系统D盘的bank
    size=5mb,   -- 分配5兆
    filegrowth=15% -- 文件以百分之15增长
)
log on
(
    name='bankDB_log',
    filename='D:\bank\bankDB_log.ndf',
    size=5mb,
    filegrowth=15%
)
go

• 切换到BANKDB数据库

USE BANKDB

• 在BANKDB数据库检测表是否存在

表名表描述(作用)cardInfo银行卡信息表transInfo交易信息表userInfo用户信息表

--`注意：删除表时有主外约束，首先删从表先`
go
--`银行卡信息表`
if exists(select * from sysobjects where name='cardInfo')
drop table cardInfo
go

--`交易信息表`
if exists(select * from sysobjects where name='transInfo')
drop table transInfo
go

--`用户信息表`
if exists(select * from sysobjects where name='userInfo')
drop table userInfo
go

• 在BANKDB数据库创建表SQL

--`创建用户信息表`
create table userInfo
(
customerID int identity(1,1) not null primary key, --顾客编号,主键
customerName varchar(10)not null,     --开户名
PID varchar(30)not null unique,      --身份证号,身份证号唯一约束
telephone varchar(15)not null,      --联系电话格式xxxx-xxxxxxxx或手机号11位
address varchar(250)        --居住地址,可选输入
)
go
--`创建银行卡信息表`
create table cardInfo
(
cardID  varchar(50)not null primary key ,  --卡号,主键,格式为:1010 3576 xxxx xxxx 后面是随机的
curType varchar(10)not null,     --货币种类
savingType varchar(20),       --存款类型,活期/定活两便/定期
openDate datetime not null default getdate(), --开户日期,默认为系统当前日期
openMoney Money not null,      --开户金额,
balance money not null,       --余额,不低一元否则将稍户
pass varchar(50) default('888888')not null,  --密码,6位数字,
IsReportLoss char(10)not null default('否'), --是否挂失,否/是 默认为"否"
customerID int not null       --顾客编号表示该卡对应的顾客的编号,一位顾客允许办理多张卡
)

go
--`创建交易信息表`
create table transInfo
(
transDate datetime not null default getdate(), --交易日期,默认为当前日期
cardID varchar(50) not null,     --卡号,外键可重复索引
transType varchar(10) not null,     --交易类型,只能是存入或支取
transMoney money not null,      --交易金额,大于零
remark varchar(250),       --备注,可选输入,其他说明
)
go

• 在BANKDB数据库的表添加约束条件SQL

-- `用户表`
alter table userInfo  add constraint ck_PID check(len(PID)>15 or len(PID)>18)--身份证号只能是18或15位,
--联系电话
alter table userInfo add constraint CK_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 telephone like '[0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

-- `银行信息表`
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]')
alter table cardinfo add constraint DF_curType default('RMB') FOR CURTYPE --默认为RMB
ALTER table cardinfo add constraint CK_savingType check (savingType in('活期','定活两便','定期'))
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 DF_pass check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]') --开户时密码 
alter table cardInfo add constraint FK_customerID foreign key(customerID)references userInfo(customerID)

--`交易表`
--`只能是存入或支取`
alter table transInfo add constraint CK_transType check(transType in('存入','支取'))  --或者是(transType='存入' or  transType='支取')
alter table transinfo ADD constraint CK_transMoney check(transMoney>0)    --大于零
alter table transInfo add constraint FK_cardID foreign key(cardID) references cardInfo(cardID)

• 在BANKDB数据库的表添加数据

--`插入数据`
go
    insert into userInfo(customerName,PID,telephone,address) values('张三','1234567890123451','010-67898978','北京海淀区')
    insert into userInfo(customerName,PID,telephone) values('李四','32145678912345678','0478-44443333')
go
    insert into cardinfo values('1010 3576 1212 1134','RMB','定期',default,1.00,5001.00,888888,0,2)
    insert into cardinfo values('1010 3576 1234 5678','RMB','活期',default,1000.00,1000.00,888888,0,1)
    
    --`查询cardinfo`
    select * from cardinfo 
go
    insert into transInfo(cardID,transDate,transType,transMoney)values('1010 3576 1212 1134',getdate(),'存入',5000.00)
go

在BANKDB数据库的表修改(修改密码)

• 场景1: 当张三取款9000时，会交易信息表(transinfo)中添加一条交易记录，同时应自动更新卡信息（cardinfo）中的现有余额（减少900元），先假定手动插入更新信息

--`声明变量`
declare @cardID varchar(50)
    select @cardID=cardID from cardInfo  where customerID=(select customerID from userInfo where customerName='张三')
    update cardinfo set balance=balance-900  where customerid =(select customerid from userinfo   where customerName ='张三')
  
    insert into transInfo(transDate,cardID,transType,transMoney) values(getdate(),@cardID,'支取',900.00)
    --`查询cardinfo`
    select * from cardinfo

• 场景2: 修改密码
  张三（卡号为1010 3576 1234 5678）修改银行卡密码为123456，
  李四（卡号为1010 3576 1212 1134）修改银行卡密码为123123

update cardInfo set pass=case
 when cardid='1010 3576 1234 5678' then '123456'
 when cardid='1010 3576 1212 1134' then '123123'
end
select * from cardinfo

• 场景3: 银行卡挂失：李四（卡号1010 3576 1212 1134）因银行卡丢失，申请挂失

update cardinfo set IsReportLoss='是' where cardid='1010 3576 1212 1134'
select * from cardinfo

• 场景4: 统计银行和银行的资金流量通余和盈利结算。
  存入代表资金流入，支取代表资金流出。 资金流通金额=总存入量-总支取量假定存款利率为千分之8 盈利结算=总支取量x0.008-总存入量x0.003

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'
print '盈利结算为：'+convert(varchar(20),@OutMoney*0.008-@InMoney*0.003)+'RMB'

• 场景5: 查询本周开户的卡号，显示该卡相关信息

select * from cardinfo 
 where datename(wk,getdate())=datepart(wk,openDate)

select * from cardinfo
 where  datepart(wk,getdate())=datepart(wk,openDate)

• 场景6: 查询本月交易金额最高的卡号,在交易信息表中,采子查询和distinct去掉重复的卡号

select distinct卡号=cardid from transinfo
  where transMoney=(select max(transMoney)from transinfo
   where datepart(mm,transDate)=datepart(mm,getdate()))

• 场景7: 查询挂失账号的客户信息,利用子查询in 方式或内部inner join

select customerName as 客户姓名,联系电话=telephone from userinfo
  where customerId in(select customerID from cardinfo where IsReportLoss='是')

SELECT *FROM userinfo AS a INNER JOIN cardinfo AS p ON a.customerId=p.customerId where  IsReportLoss='是'

• 场景8: 催款提醒业务：例如某种的需要，每个月末，如果发现用户账上余额少于200元，将致电催款

select customerName as 客户姓名,联系电话=telephone,账上余额=balance
 from userInfo Inner Join cardInfo 
   on userinfo.customerId=cardInfo.customerId where balance<200

• 场景9: 创建索引和视图,表卡号cardId字段创建重复索引，以便加速查询，填充因子为70%

IF exists(select name from sysindexes where name='index_cardID')
drop index transInfo.index_cardID
go
create nonclustered index index_cardID
 on transInfo(cardid)
 with fillfactor=70
go

• 场景10:创建索引查询张三（卡号为1010 3576 1212 1134）的交易记录

select * from transInfo with(index =index_cardID)
 where cardid=(select cardid from cardinfo 
  where customerid=(select customerid from userinfo 
   where customerName='张三'))

• 场景11:创建视图：为了向客户显示信息友好，查询各表，要求字段全为中文字段名3个表 , 对应的视图为：view_userInfo 、view_cardInfo view_tranInfo

-- `view_userInfo 用户视图`
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
select * from view_userInfo


-- `view_cardInfo 卡信息视图`
if exists(select * from sysobjects where name='view_cardInfo')
    drop view view_cardInfo
go
create view view_cardInfo
as
  select  卡号=cardid,货币类型=curType, 存款类型=savingType,开户时间= openDate,
   开户金额=openMoney,余额=balance,密码=pass,是否挂失=IsReportLoss,客户编号=customerID
  from cardinfo
go
select * from view_cardInfo

--`view_tranInfo 交易信息视图`
if exists(select * from sysobjects where name='view_tranInfo')
    drop view view_tranInfo
go
    create view view_tranInfo
as
    select 交易日期=getdate(),交易类型=transType,卡号=cardId,交易金额=transMoney, 备注=remark from transinfo 
go
    select * from view_tranInfo

• 场景12: 创建存储过程 ，1.取钱或存钱的存储过程proc_takeMoney
  调用存储过程取钱或存钱，张三的卡号支取300（密码123456）,李四的卡号存入500

if exists(select * from sysobjects where name='proc_takeMoney')
drop proc proc_takeMoney
go
--`创建取款的存储过程`
create proc proc_takeMoney 
  @cardID char(22),
  @Money money,
  @type char(4),
  @inputPass varchar(6)=' ' 
as 
 declare @Mybalance money
 select @Mybalance=balance from cardInfo where cardid=@cardID

if (@type='支取')
begin
  if((select pass from cardinfo where cardID=@cardID)<>@inputPass)
   begin
  print '交易正进行,请稍后.....'
   raiserror('密码错误',16,1)
  return
   end
if(@Mybalance<=@Money) 
  begin
  raiserror('交易失败,余额不足',16,1)
   print '卡号 '+@cardID+' 余额 '+convert(varchar(10),@Mybalance)
  return 
  end 
else
  begin
  update cardinfo set balance=balance-@Money where cardID=@cardID
    print '交易成功，交易金额为'+convert(varchar(20),@Money)
  print '卡号 '+@cardID+' 余额 '+convert(varchar(10),@Mybalance)
  end
end
else
 begin
  update cardinfo set balance=balance+@Money where cardID=@cardID
   print '交易成功!交易金额为'+convert(varchar(10),@Money)
  print '卡号 '+@cardID+' 余额 '+convert(varchar(10),@Mybalance)
 end
--`插入数据`
insert into transInfo(transDate,cardID,transType,transMoney)
values(getdate(),@cardID,@type,@Money)
go

--`显示相关信息`
go
declare @cardID varchar(25),@balance money
select @cardID=cardid,@balance=balance from cardinfo 
  where customerid=(select customerid from userinfo 
   where customerName='张三')
exec proc_takeMoney @cardID,300,'支取','123456' --执行存储过程

--declare @cardID varchar(25),@balance money
select @cardID=cardid,@balance=balance from cardinfo 
 where customerid=(select customerid from userinfo 
  where customerName='李四')
--`插入记录`
exec proc_takeMoney @cardID,500,'存入',' '   --执行存储过程
go
select * from view_tranInfo
select * from view_cardInfo
select * from view_userInfo

• 场景13: 产生随机数卡号存储过程proc_randCardID

if exists(select * from sysobjects where name='proc_randCardID')
drop proc proc_randCardID
go
create proc proc_randCardID
 @randCardID char(25)output    --输出参数
as 
 declare @R numeric(15,8),@tempStr varchar(20) --15位数,保留8位小数
 select @R=rand((datepart(mm,getdate())*100000)+
 (datepart(ss,getdate())*1000)+datepart(ms,getdate()))
set @tempStr=convert(varchar(25),@R)  --存放随机数
set @randCardID='1010 3576'+' '+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4)--截取
go

--`执行存储过程`
declare @RandID char(19)
exec proc_randCardID @RandID output  --输出参数与创建存储过程一一对应

• 场景14: 开户的存储过程Proc_openAccount

if exists(select * from sysobjects where name='Proc_openAccount')
drop proc Proc_openAccount
go
create proc Proc_openAccount
 @customerName varchar(20),
 @PID char(20),
 @telephone varchar(20),
 @openMoney money,
 @savingType varchar(20),
 @address varchar(100)=' '　　
as
declare @RandID char(19)
exec proc_randCardID @RandID output  --输出参数与创建存储过程一一对应
 while exists(select * from cardInfo where cardID=@RandID)
exec proc_randCardID @RandID output  --输出参数与创建存储过程一一对应

print '尊敬的客户,开户成功!系统为你产生的随机数卡号为: '+@RandID
print '开户日期'+convert(varchar(10),getdate(),111)+'开户金额为:'+convert(varchar(10),@openMoney)
--`插入数据`
insert into userInfo(customerName,PID,telephone,address)values(@customerName,@PID,@telephone,@address)
--`声明变量`
declare @customerID int 
select @customerID=customerID from userinfo where PID=@PID
exec proc_randCardID @RandID output 
--`插入数据`
insert into cardinfo(cardid,curType,savingType,openDate,openMoney,balance,pass,customerID)
values(@RandID,default,@savingType,getdate(),@openMoney,@openMoney,default,@customerID)
go
--`执行存储过程`
exec Proc_openAccount '王五','3344568890126780','2222-63598978',1000,'活期','河南新乡'
exec Proc_openAccount '赵二','213445678912342222','2222-44446666',1,'定期',' '

select * from view_tranInfo
select * from view_cardInfo
select * from view_userInfo

• 场景15: 创建事务,同银行的帐号间一般都支持功能，因为转帐的规则是：一方支取另一方存入相应金额。这两步要么同时成立要么同时失败所以采用事务处理。如果任何一方失败可以撤销操作，现模拟从李四转帐2000到张三的帐号上要求显示转帐结果；是否转帐成功，转帐双方金额，采用存储过程，需要三个参数转出方卡号和转帐金额

create proc proc_transfer 
 @card1 char(19),
 @card2 char(19),
 @outMoney money 
as
--`开始事务`
begin tran
print '开始转账，请稍后........'
declare @error int
 set @error=0  --初始化
exec proc_takeMoney @card1,@outMoney,'支取','123123'  --调用取款存储过程
 set @error=@error+@@error
exec proc_takeMoney @card2,@outMoney,'存入'
 set @error=@error+@@error
if(@error>0)
 begin 
  print '交易失败'
  rollback tran 
 end
else 
 begin
  print '交易成功，交易金额：'+convert(varchar(9),@outMoney)
  commit tran
 end
go

--`执行`
--`从李四的账号转账2000元到张三`
declare @card1 varchar(22),@card2 varchar(22)
    select @card1=cardid from cardinfo inner join userinfo  on userinfo.customerid=cardinfo.customerid where userinfo.customerName='李四'
    select @card2=cardid from cardinfo inner join userinfo  on userinfo.customerid=cardinfo.customerid where userinfo.customerName='张三'
exec proc_transfer @card1,@card2,2000

select * from view_tranInfo
select * from view_cardInfo
select * from view_userInfo

• 场景16: 创建登录账号和数据库用户.sql登录账号Admin密码为1234作为系统维护的账号具有对上述3个表增、删、改、查的权限阶段

exec sp_addlogin 'Admin','1234' --SQL Server 
exec sp_grantdbaccess 'Admin','sysAdminDBUser'
grant select,insert,update,delete on userInfo to  sysAdminDBUser
grant select,insert,update,delete on cardInfo to  sysAdminDBUser
grant select,insert,update,delete on transInfo to  sysAdminDBUser

7、总结

• 此文从整体来以银行ATM取款机系统项目案例从建库，建表，添加索引，存储过程，视图、插数据，创建用户，授权等操作，以实际场景演练了一遍。
• 此案例是在N久年前在学习课本中老师布置一道作业，通过自身完成的实操，仅供参考学习,非某个银行ATM的数据库SQL脚本，此文章属于本人原创可以转载或收藏