Sqlserver 以前我在学校T-sql建ATM取款机的sql语句

use master
go
--创建库
if exists(select * from sysdatabases where name='bankDB')
drop database bankDB
create database bankDB
on primary
(
name='bankDB_data',
filename='E:\bank\bankDB_data.mdf',
filegrowth=30%,
size=5
)log on
(
name='bankDB_log',
filename='E:\bank\bankDB_log.ldf',
size=2,
filegrowth=10%
)
go

--创建表userInfo
use bankDB
go
if exists(select * from sysobjects where name='userInfo')
drop table userInfo
create table userInfo(
customerID int identity(1,1) primary key,
customerName varchar(12) not null,
PID varchar(18) not null,
telephone varchar(14) not null,
address varchar(20),
constraint CK_PID check (LEN(PID) between 15 and 18),
constraint VK_telephone check(telephone like '____-________' or telephone like '___-________' 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]'),
CONSTRAINT UQ_PID UNIQUE(PID)
)
go

--创建表cardInfo
if exists(select * from sysobjects where name='cardInfo')
drop table cardInfo
create table cardInfo(
  cardID  CHAR(19) NOT NULL primary key,
  curType  CHAR(5) NOT NULL default 'RMB',
  savingType  CHAR(8) NOT NULL,
  openDate  DATETIME NOT NULL default getDate(),
  openMoney  MONEY NOT NULL,
  balance  MONEY NOT NULL,
  pass CHAR(6) NOT NULL default'888888',
  IsReportLoss BIT  NOT NULL default '0',
  customerID INT NOT NULL,
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 CK_openMoney check(openMoney>=1),
constraint CK_balance check(balance>=1),
constraint CK_savingType check(savingType in ('活期','定活两便','定期')),
constraint FK_customerID foreign key(customerID) REFERENCES userInfo(customerID)
)
go


--创建transInfo交易信息表
if exists(select * from sysobjects where name='transInfo')
drop table transInfo
CREATE TABLE transInfo  
(
  transDate  DATETIME NOT NULL default getDate(),
  transType  CHAR(4) NOT NULL,
  cardID  CHAR(19) NOT NULL,
  transMoney  MONEY NOT NULL,
  remark  TEXT,
constraint CK_transMoney check(transMoney >0),
constraint CK_transType check(transType in('存入','支取')),
constraint FK_cardID foreign key(cardID) references cardInfo(cardID)
)
GO



--插入userINfo
insert userInfo(customerName,PID,telephone,address)values('张三','123456789012345','010-67898978','北京海淀')
insert userInfo(customerName,PID,telephone,address)values('李四','321245678912345678','0478-44443333','NULL')
select * from userInfo
--插入cardInfo
INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID) VALUES('1010 3576 1212 1134','定期',1,1,2)
INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID) VALUES('1010 3576 1234 5678', '活期',1000,1000,1)
SELECT * FROM userInfo
SELECT * FROM cardInfo

/*--------------交易信息表插入交易记录--------------------------*/
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'
GO


/*---------修改密码-----*/
--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' 
SELECT * FROM cardInfo
/*--------- 李四的卡号挂失 ---------*/
update cardInfo set IsReportLoss=1 WHERE cardID='1010 3576 1212 1134' 
SELECT * FROM cardInfo
GO

--统计银行的资金流通余额
set nocount on
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'


--查询本周开户的卡号
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 FROM userInfo INNER JOIN cardInfo ON  userInfo.customerID =cardInfo.customerID WHERE balance<200



--1.创建索引：给交易表的卡号cardID字段创建重复索引
create NONCLUSTERED INDEX index_cardID ON transInfo(cardID)WITH FILLFACTOR=70
GO
--2.按指定索引查询 张三（卡号为1010 3576 1212 1134）的交易记录
SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID='1010 3576 1234 5678'
GO
--3.创建视图：查询各表要求字段全为中文字段名。
create VIEW view_userInfo  --银行卡信息表视图（其他表同理）
  AS 
    select customerID as 客户编号,customerName as 开户名, PID as 身份证号,
        telephone as 电话号码,address as 居住地址  from userInfo
GO


--随机产生卡号
declare @r numeric(15,8),@randCardID char(19),@tempStr varchar(10),@preID varchar(10)
set @preID='1010 3576 '
select @r=rand((datepart(mm,getDate())*100000)+(datepart(ss,getdate())*1000)+datepart(ms,getdate())  )
set @tempStr=''+convert(varchar(10),@r)
set @randCardID=@preID+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4)
print @randCardID