SQLServer创建用户、数据库、表、约束、存储过程、视图

--创建登录账户和数据库用户
exec sp_addlogin 'sysAdmin','123456'
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:ank',no_output
go
 
--建立数据库
create database bankDB
on
(
    name='bankDB_data',
    filename='D:ankankDB.mdf',
    size=3,
    filegrowth=15%
)
log on
(
    name='bankDB_log',
    filename='D:ankankDB_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