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

谁借莪１个温暖的怀抱￠ 2021-12-20 18:15 309阅读 0赞

--创建登录账户和数据库用户
    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:\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

记录常用的一些使用方式，以备需要的时候查看！

转载于:https://www.cnblogs.com/duanjt/p/5181394.html