USE MASTER--连接系统数据库
IF EXISTS(select 1 from master..sysdatabases where name='bankDB')
DROP DATABASE bankDB
GO
-----------------------------------------------建库------------------
--打开外围服务器
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
--新建文件夹
exec xp_cmdshell 'MD e:\数据库'
CREATE DATABASE bankDB
ON
(
NAME ='bankDB',
FILENAME='e:\数据库\bankDB.mdf',
SIZE = 10,
MAXSIZE=500,
FILEGROWTH=15%
)
GO
--------------------------------------------------建表-----------------
USE bankDB
--用户信息表
IF EXISTS(select 1 from bankDB..sysobjects where name='userInfo')
DROP TABLE userInfo
GO
CREATE TABLE userInfo
(
customerID int identity(1,1) PRIMARY KEY , --顾客编号(自动增长 主键)
customerName varchar(20) not null, --开户名
PID varchar(20)UNIQUE not null, --身份证(18-15位数 唯一约束)
telephone varchar(20) not null, --联系电话(****-********或手机号11位数)
[address] ntext --联系地址
)
--银行卡信息表
IF EXISTS(select 1 from bankDB..sysobjects where name='cardInfo')
DROP TABLE cardInfo
GO
CREATE TABLE cardInfo
(
cardID varchar(20) primary key , --卡号 (格式为1010 3576 **** ***(*部分是随机产生))
curType varchar(10) default('RMB') not null,--货币种类 (默认为RMB)
savingType varchar(10), --存款类型 (活期/定活两便/定期)
openDate datetime default(getdate()) not null,--开户日期 (默认为当前时间)
openMoney money check(openMoney <1) not null, --开户金额 (不能低于1元)
dalance money check(dalance <1) not null, --余额 (不能低于1元 否则将销户)
pass varchar(20) default(888888) not null,--密码 (6位数 开户时默认为6个8)
IsReportLoss bit default(0) not null, --是否过失 (是/否 默认为否 1是 0否)
customerID int not null --顾客编号 (外键 该卡号对应的顾客编号 一个用户可办多张卡)
)
--交易信息表
IF EXISTS(select 1 from bankDB..sysobjects where name='transInfo')
DROP TABLE transInfo
GO
CREATE TABLE transInfo
(
transDate datetime not null, --交易日期(默认为当前时间)
cardID varchar(20) not null, --卡号(外键 可重复索引)
transType varchar(10) not null, --交易类型(只能是存入/支取)
transMoney money check(transMoney>0) not null, --交易金额(大于0)
remark ntext --备注(其它说明)
)
------------------------------约束-------------------------------
--约束电话和手机号码
if(object_id('uq_pid') is not null)
begin
alter table userinfo
drop constraint uq_pid
end
if(object_id('ck_PID') is not null)
begin
alter table userinfo
drop constraint ck_PID
end
if(object_id('ck_telephone') is not null)
begin
alter table userinfo
drop constraint ck_telephone
end
alter table userInfo
add constraint ck_PID check(len(pid) in (15,18)),
constraint uq_PID unique(pid),
constraint ck_telephone check(telephone like '1[35][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]'--010-12345678
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][0-9]'--0719-12345678
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]')--0719-1234567
----------------------------------------------设置卡号为随机数(方法1)---------------------------
declare @r decimal(10,8)
declare @time varchar(25)
set @time=convert(varchar,getdate(),120)+convert(varchar,datepart(ms,getdate()))
set @time=replace(@time,' ','')
set @time=replace(@time,':','')
set @time=replace(@time,'-','')
set @time=substring(@time,8,len(@time)-1)
--select @time--查看获得的随机数
select @r=rand(convert(bigint,@time))
set @time=cast(@r as varchar)
--select @time
set @time=substring(@time,3,len(@time)-1)
print @time
print '1001'+convert(varchar(10),@time)
----------------------------------------------设置卡号为随机数(方法2用了 存储过程)---------------------------
create proc proc_randCardID
@cardid varchar(19) output
as
declare @r numeric(8,8)
set @cardid='1010 3657 '
while(1=1)
begin
set @r=rand(datepart(mm,getdate())*100000+datepart(ss,getdate())*1000+datepart(ms,getdate()))
declare @temp char(8)
set @temp=substring(convert(varchar,@r),3,8)
set @cardid=@cardid+substring(@temp,1,4)+' '+substring(@temp,5,4)
if not exists(select 1 from cardinfo where cardid=@cardid)
break
end
--测试（调用存储过程）
declare @card varchar(19)
exec proc_randCardID @card output
print @card
*/ |