MSSQL 练习进行时,数据表的基本操作及约束的灵活使用


   --检查数据库是否存在,如果存在删除,不存在直接建库-----
   if exists (select * from master.dbo.sysdatabases where name='on_db')
            drop database on_db
            go
    --建库开始--
    create database on_db
           on  primary
           (
            name='on_db_mdb',
            filename = 'f:\cmdcomm\on_db.mdf' ,
            size =3mb,
            filegrowth=15%
            )
            log on
            (
            name = on_db_log,
            filename='f:\cmdcomm\on_db.ldf',
            size=3mb,
            filegrowth=15%
            )
            go
           
        ---------------------------------------
        use on_db
        --建立用户表--
      
        create table userInfo1(
                              userID int not null identity(1,1) primary key ,
                              userNmae char(8) not null,
                              userAddress varchar(50) ,
                              userIphone char(13) not null ,
                              userPid  char(18) not null
                              )
                              go
          -----银行卡信息建立-----------
          create table cardinfo(
              cardID char(19) not null primary key,
              cartype char(5) ,
              savtype char(8) not null,
              opendate datetime not null,
              openmoney money not null,
              balause   money not null,
              pass char(6) not null,
              isreportloss bit not null,  
               customerid int not null
               )
               go
            -----------------交易信息表----------
            create table transinfo(
            c_id int identity(1,1) constraint PK_Tran_cid primary key ,                
            transdate datetime not null,
            transtype char(6) not null,
            cardID char(19) constraint FK_CardID foreign key(cardid) references  cardinfo(cardID)ON DELETE CASCADE,
            transmoney money not null,
            remark text
            )
            go
            /*
            建表工作完成
            */
           /*各表视图操作*/
          
            create view V_userinfo1
                as
                select        userID  as 用户ID,
                              userNmae  as 用户名,
                              userAddress as 用户地址 ,
                              userIphone as 用户手机 ,
                              userPid    as 用户身份证号
                              from userInfo1
                             
                              go
          -----银行卡信息建立-----------
          create view  V_cardinfo
                 as
                 select
              cardID  as 银行卡ID,
              cartype as 银行卡类型,
              savtype as 存储类型,
              opendate as 开户日期 ,
              openmoney as 开户金额,
              balause   as 余额,
              pass      as  密码,
              isreportloss as 是否挂失,  
               customerid  as 顾客卡号
               from cardinfo
               go
            -----------------交易信息表----------
            create view V_transinfo
            as
            select
            c_id as 交易ID ,                
            transdate  as 交易日期,
            transtype as 交易类型,
            cardID    as 银行卡ID,
            transmoney  as 交易金额,
            remark      as 备注
            from  transinfo
           
            go
            ---表功能约束--
            alter table dbo.userInfo1
                  add constraint ck_pid check (len(userPid)=18 or len(userPid)=15),
                      CONstraint uq_userPID unique (userPID),
                      constraint ch_userIphone check (userIphone 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]' or
                                 userIphone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'or len(userIphone)=11)
                                 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]'), 
                      constraint  df_curtype  default('rmb') for cartype, 
                      constraint  ck_savingtype  check(savtype in ('活期','定活两便','定期')), 
                      constraint  df_opendate  default(getdate()) for opendate, 
                      constraint  ck_openmoney  check(openmoney>=1), 
                      constraint  ck_balance  check(balause>=1), 
                      constraint  ck_pass  check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]'), 
                      constraint  df_pass  default('888888') for pass, 
                      constraint  df_isreportloss default(0) for isreportloss 
                      GO
        /* transinfo表的约束 
 
transtype       必填,只能是存入/支取  
cardid      卡号  必填,外健,可重复索引 
transmoney  交易金额    必填,大于0 
transdate   交易日期    必填,默认为系统当前日期 
remark      备注  可选输入,其他说明 
*/ 
 
                            alter table transinfo 
                                  add constraint  ck_transtype  check(transtype in ('存入','支取')), 
                                     -- constraint  fk_cardid  foreign key(cardid) references cardinfo(cardid), 
                                      constraint  ck_transmoney  check(transmoney>0), 
                                      constraint  df_transdate default(getdate()) for transdate 
                                     
                                     GO
                                    
                                    
     -------------插入记录-----------
                            
  set nocount on
  insert into userInfo1(userNmae,userAddress,userIphone,userPid) values
        ('李小二' ,'合肥市瑶海区','0551-23839832', '340123949029199238' ) 
  insert into userInfo1(userNmae,userAddress,userIphone,userPid) values
        ('赵树根' ,'河南信阳','13655329810', '331345678909876' ) 
  insert   into cardinfo(cardID,cartype,savtype,opendate,openmoney,customerid,isreportloss,balause)
           values ('1010 3576 7852 3698','RMB','定期','2013-3-26','32502.65',112,0,'8992,33')
 insert   into cardinfo(cardID,cartype,savtype,opendate,openmoney,customerid,isreportloss,balause)
           values ('1010 3576 5615 3685','美元','活期','2018-3-26','82012.65',113,0,'18992,33')
           select * from V_userinfo1 
           select * from V_cardinfo 
           go
          --------修改密码--------\
update cardinfo
       set pass='123456'
       where cardID= '1010 3576 5615 3685'
       go
     update cardinfo
       set pass='132133'
       where cardID= '1010 3576 7852 3698'
       go 
       select * from V_cardinfo
       /*挂失银行卡*/
       update cardinfo
       set isreportloss=1
       where cardID='1010 3576 5615 3685'
       go
       select * from V_cardinfo
      
      
 --银行流水操作---
 declare @inmoney money
 declare @outmoney money
 declare @profilt  money
 select * from transinfo
 select @inmoney = SUM( transmoney) from transinfo where (transtype='存入')
  select @outmoney = SUM( transmoney) from transinfo where (transtype='支出')
  print '银行流通余额总为:' + convert(varchar(20),@inmoney - @outmoney) + 'rmb' 
set @profilt = @outmoney*0.008 - @inmoney*0.003 
print '盈利结算为:'+ convert(varchar(20),@profilt) + 'rmb' 
go 
 
select * from transinfo 


select *  
from cardinfo  
where (datediff(day,getdate(),opendate) < datepart(weekday,opendate)) 

  • blogger

版权所有:bokeren ---- 《MSSQL 练习进行时,数据表的基本操作及约束的灵活使用
本文地址:http://www.bokeren.cc/post-129.html
除非注明,文章均为 《悠然生活的平淡博客-博客人linux运维之家》 原创转载请注明本文地址,谢谢。


相关推荐

你肿么看?

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。