ms sql 学习应用

学习MS sql的实例

--必须选择master的实例数据库在判断数据库时

use master
 检测数据库信息

go
 if exists (select * from sysdatabases where name='stuDB')
  drop database stuDB
go

---调用dos命令执行创建目录
 exec xp_cmdshell 'mkdir E:\StuInfo'
go

----创建数据库
  create database stuDB
 on primary
(
   name='stuDB',
   filename='E:\StuInfo\stuDB_data.mdf',//创建数据库库的主文件
   size=10,
   maxsize=20,
   filegrowth=10%
)
log on            //创建数据库的日志文件的信息
(
  name='stuDBLog',
  filename='E:\StuInfo\stuDBLog_log.ldf',
  size=20,
  maxsize=40,
  filegrowth=10%
)
go

----------使用前必须选择创建数据库执行批处理
 use stuDB
go 

---判断数据库中表是否存在的信息

 if exists(select * from sysobjects where name='stuInfo')
  drop table stuInfo
go
  create table stuInfo
 (
   stuNo varchar(7) not null,
   stuName varchar(32) not null,
   stuSex varchar(4) not null,
   stuSeat int not null identity (1,1),
   stuAge int not null,
  stuAddress varchar(50)
  )
go

-------------添加各种约束的信息
   alter table stuInfo
      add constraint pk_stuNo primary key(stuNo)  --主键约束
  alter table stuInfo
      add constraint ck_stuSex check( stuSex in ('男' ,'女'))  --check约束
  alter  table  stuInfo
     add  constraint  df_stuAddress default('地址不详') for stuAddress  --默认约束
  alter table stuInfo
     add constraint ck_stuAge check(stuAge between 15 and 40)
  alter table stuInfo
      add constraint uq_stuNo unique(stuNo)    --唯一约束
  alter table stuInfo
       add constraint ck_stuNo check(stuNo like 's253__')        
  
  alter table stuInfo  ---------删除约束信息
    drop constraint ck_stuNo

go
   if exists(select * from sysobjects where name='stuMarks')
      drop table stuMarks
go
 create table stuMarks
(
   examNo varchar(7) not null,
   stuNo varchar(7) not null,
   writtenexam int not null,
  labexam int not null
 )
go
 alter table stuMarks
   add constraint pk_stuNoMarks primary key(stuNo)
  alter table stuMarks  --创建外键约束信息
    add constraint fk_stuNo foreign key(stuNo)
          references stuInfo(stuNo)
 alter table stuMarks  
   add constraint df_writtenexam default(0) for writtenexam

 alter table stuMarks
   add constraint df_labexam default(0) for labexam

go

insert into stuInfo (stuName ,stuNo,stuSex,stuAge,stuAddress)
select '我的爱人','s25301','女',18,'河南南阳' union
select '晓丽','s25302','女',18,'河南南阳' union
select '小白','s25303','男',18,'河南南阳'
go

insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
  values('小丽','s25304','女',16,'河南淅川')

 

insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
  values('阿朵','s25305','女',16,default)
go

use studb
go
 -----分配权限

 grant select ,update,insert  on stuinfo to xiaobai;
 grant select on stuInf to jy;
 grant create table to bailonggang;
go
  select * from stuInfo;
  select * from stuMarks;
 go

 

----插入多行时使用
 insert into stuMarks(examNo,stuNo,writtenexam,labexam)
  select 's271811','s25301' ,80,20 union
  select 's271813','s25304' ,88,70 union
  select 's271816','s25302' ,48,50 union
  select 's271815','s25303' ,45,30

go

 

---声明变量的时使用
 declare @name varchar(34)
  set @name='晓丽'
 declare @seat int
  select @seat=stuseat from stuInfo where stuname =@name
   select * from stuinfo where (stuseat=@seat-1) or (stuseat=@seat+1)
go

 

 

---服务器变量的使用必须为@@

print '服务器的名称'+@@servername
select @@servername as '服务器的名称'

insert into stuInfo(stuNo,stuName,stuSex,stuAge,stuAddress)
values('梅超风','s25301','女',23,default)
print '当前的错误的号为：'+convert(varchar(5),@@error)

update stuInfo set stuAge=89 where stuname='小白'
print '当前的错误的号为：'+convert(varchar(5),@@error)

go

-----在mssql中 if判断的使用
declare @avag float
  select @avag=avg(writtenexam) from stuMarks
 if(@avag>70)
  begin
      print '本班的笔试成绩比较优秀：'+convert(varchar(5),@avag)
       select top 3 * from stuMarks order by writtenexam desc
  end
 else
   begin
       print '本班的笔试成绩比较差：'+convert(varchar(5),@avag)
       select top 3 * from stuMarks order by writtenexam asc
     
   end

go

 

-----mssql 中判断的使用（无条件比较常用）

 

declare @n int
 while(1=1)
 begin
    select @n=count(*) from stuMarks where writtenexam<60
     if(@n>0)
      begin
             update stuMarks set writtenexam=writtenexam+2
      end
    else
      break;
 end 

go
select * from stuMarks

go

----有条件的判断的使用

while (select avg(labexam)from stuMarks)<70
begin
    update stuMarks set labexam=labexam+2
     if(select max(labexam) from stuMarks )>80
            break;
      else
           continue;
end

go

select * from stuMarks

go

---case end的经典使用的

declare @n int
  while(1=1)
  begin
    select @n=count(*) from stuMarks where labexam<60
    if(@n>0)
     begin
              update stuMarks set  labexam=labexam+
                     case
                            when  labexam between 80 and 89 then 1
                            when  labexam between 70 and 79 then 2
                            when  labexam between 60 and 69 then 3
                             else  5
                     end
     end
    else
         break
  end 
go

 

 
  select examno stuno ,writtenexam ,labexam ,
            等级=case
                        when  labexam>90 then 'A'
                        when  labexam between 80 and 89 then 'B'
          when  labexam between 70 and 79 then 'C'
                        when  labexam between 60 and 69 then 'D'
                        else 'E'
                 end
    from stuMarks

go

 

declare  @n int
  while(1=1)
  begin
         select @n=count(*) from stuMarks where labexam <60
          if(@n<0)
          begin
               update  stuMarks set labexam=labexam+2
          end
          else
             break;
  end
   update stumarks set labexam=100 where labexam>100

 go

select writtenexam,stuno,labexam ,
    等级=
        case
          when  labexam> 90 then 'A'
            when  labexam between 80 and 90 then 'B'
          when  labexam between 70 and 80 then 'C'
          when  labexam between 60 and 70 then 'D'
          else 'E'
        end
    from stuMarks

go