--创建登录账户和数据库用户

exec sp_addlogin 'sysAdmin',''

exec sp_grantdbaccess 'sysAdmin','aa'

--给数据库用户赋权限

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

--建立数据库前的判断

Use master

GO

if exists(select * from sysdatabases where name='bankDB')

    drop database bankDB

GO

exec xp_cmdshell 'md d:\bank',no_output

go

--建立数据库

create database bankDB

on

(

    name='bankDB_data',

    filename='D:\bank\bankDB.mdf',

    size=3,

    filegrowth=15%

)

log on

(

    name='bankDB_log',

    filename='D:\bank\bankDB_log.ldf',

    size=3,

    filegrowth=15%

)

GO

use bankDB

GO

--建立银行卡信息 表

if exists(select * from sysobjects where name='cardInfo')

    drop table cardInfo

GO

create table cardInfo

(

    cardID varchar(20) not null,

    curType varchar(5) not null,

    savingType nvarchar(4) not null,

    openDate datetime not null,

    openMoney money not null,

    balance money not null,

    pass varchar(6) not null,

    IsReportLoss bit not null,  --是否挂失,1表示挂失

    customerID int not null

)

go

--检查约束

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 PK_cardID primary key(cardID)

--默认约束

alter table cardInfo

    add constraint DF_curType default('RMB') for curType

--创建外键约束

alter table cardInfo

    add constraint FK_customerID foreign key(customerID) references userInfo(customerID)

--创建唯一约束

alter table userInfo

    add constraint UK_PID unique(cardID)

--创建索引

create unique nonclustered index IX_cardID on cardInfo(cardID) with fillfactor=70

--创建视图

create view v_userInfo as

select customerID 客户编号,customerName 开户用户,PID 身份证号,telephone 电话,address 地址 from userInfo

go

select * from v_userInfo

--创建存储过程

--产生随机号的存储过程

if exists(select * from sysobjects where name='proc_randCardID')

    drop proc proc_randCardID

go

create proc proc_randCardID

@i varchar(10),

@rand varchar(20) output

as

declare @b varchar(10)

declare @r numeric(15,8)

select @r=rand(datepart(mm,getdate())*100000+datepart(ss,getdate())*1000+datepart(ms,getdate()))

select @b=substring(convert(varchar(30),@r),3,4)+' '+substring(convert(varchar(30),@r),7,4)

set @rand=@i+' '+@b

go

--测试获得随机数

declare @result varchar(20)

exec proc_randcardID '1524 2222',@result output

select @result

go