SQL逻辑判断(begin end)

use master
--创建文件夹
exec xp_cmdshell 'md d:project'
exec xp_cmdshell 'dir d:'
--判断数据库是否存在
if exists(select * from sysdatabases where name='stuDB')
--如果存在先删除
drop database stuDB
--创建数据库
create database stuDB
--创建主数据库文件
  on primary(
 name='stuDB_data_1',
 filename='d:\project\stuDB_data_1.mdf',
 size=5mb,
 maxsize=100mb,
 filegrowth=10%
),

--创建次要数据库文件
(
 name='stuDB_data_2',
 filename='d:\project\stuDB_data_2.ndf',
 size=5mb,
 maxsize=100mb,
 filegrowth=10%
 )
--创建日志文件
  log on(
 name='stuDB_log_1',
 filename='d:\project\stuDB_log_1.ldf'
),
  --创建其他日志文件
(
 name='stuDB_log_2',
 filename='d:\project\stuDB_log_2.ldf'
)
--批处理
go

use stuDB

select * from sysdatabases
select * from sysobjects

创建约束 constraint
主键 primary key
唯一约束 unique
检查约束 check
莫认约束 default
外键约束 foreign
引用  references
创建约束 alter table 表名
 add constraint 为约束起名字
删除约束 alter table 表名
 drop constraint 约束名


use stuDB
drop table stuInfo
drop table stuMarks

create table stuInfo(
 stuName varchar(20) not null,
 stuNo char(6) not null,
 stuSex char(2) not null,
 stuAge int not null, 
 stuSeat int identity (1,1),
 stuAddress varchar(50) not null
)
go

create table stuMarks(
 examNo char(7),
 stuNo char(6) not null,
 writtwenExam int not null,
 labeExam int not null
)
go

alter table stuInfo --主键约束
 add constraint PK_stuNo primary key (stuNo)

alter table stuInfo --唯一约束
 add constraint UK_stuId unique (stuId)

alter table stuInfo --默认约束
 add constraint DK_stuAddress default('地址不详') for stuAddress

alter table stuInfo --检查约束
 add constraint CK_stuAge check (stuAge between 15 and 40)

alter table stuMarks--外键约束
 add constraint FK_stuNo foreign key (stuNo)references stuInfo(stuNo)

alter table stuMarks --检查约束
 add constraint CK_writtwenExam check(writtwenExam between 0 and 100)

alter table stuMarks--检查约束
 add constraint CK_labeExam check(labeExam between 0 and 120)

alter table stuMarks--删除约束
 drop constraint FK_stuNo

 


insert into stuInfo values('张秋丽','s25301','男',18,'北京海淀')

insert into stuInfo values('李文才','s25302','男',28,default)

insert into stuInfo values('李斯文','s25303','女',22,default)

insert into stuInfo values('欧阳俊雄','s25304','女',34,default)

insert into stuInfo values('梅超风','s25318','女',23,default)


select * from stuInfo

declare @name varchar(20)--定义一个局部变量

set @name='李文才'--为变量赋值

declare @seat int--定义个变量

select @seat=stuSeat from stuInfo where stuName=@name--查找stuName的编号并且为stuSeat赋值

select * from stuInfo where (stuSeat=@seat+1)or(stuSeat=@seat-1)--查询姓名并且stuSeat+1 or stuSeat-1

set nocount on --不显示影响的行数

print @@error

select * from stuInfo
--常用的全局变量
print '最后一个T-SQL错误的错误号:'+@@error

print '最后一次插入的标识值'+ @@identity

print '本系统的版本号为:'+@@version

print '本地服务器的名称:'+@@servername
--不常用的全局变量
print '当前使用的语言的名称:'+@@language

print '可以创建的同时连接的最大数目:'+@@max_connections

print '受上一个SQL语句影响的行数:'+@@rowcount

print '当前连接打开的事物数:'+@@trancount


insert into stuMarks values('s271811','s25303',80,58)

insert into stuMarks values('s271813','s25302',50,90)

insert into stuMarks values('s271816','s25301',77,82)

insert into stuMarks values('s271818','s25318',45,65)

select * from stuMarks
select * from stuInfo

declare @avg int
select @avg=avg(writtwenExam) from stuMarks
print '本班成绩为:'+convert(varchar(4),@avg)
if(@avg<70)
 begin
  print '本班成绩较差:'
         select top 3 * from stuMarks order by writtwenExam
 end
    else
 begin
  print '成绩优秀:'
   select top 3 * from stuMarks order by writtwenExam desc
 end
--先统计未及格人数
while(1=1)
  begin
 declare @noopass int
 select @noopass=count(*) from stuMarks where writtwenExam<60
 if(@noopass>0)
  begin
   update stuMarks set writtwenExam=writtwenExam+2
  end
 else
  begin
  break;
 end
end
select * from stuMarks

--笔试成绩有两个成绩未及格的状态
while(1=1)
 begin
  declare @noopass int 
 select @noopass=count(*) from stuMarks where writtwenExam<60
 if(@noopass=2)
  begin
  break;
     end
 else
  begin
  update stuMarks set writtwenExam=writtwenExam-2
 end
end

--采用美国的ABCDE等级来评定(笔试成绩)
select *, 成绩=case
 when writtwenExam<60 then 'E'
 when writtwenExam between 61 and 70 then 'D'
 when writtwenExam between 71 and 80  then 'C'
 when writtwenExam between 81 and 90 then 'B'
 else
  'A'
end
from stuMarks
--增加一列平均分并采用美国的ABCDE等级来评定(笔试成绩)
select *,平均成绩=(writtwenExam+labeExam)/2, 等级=case
 when writtwenExam<60 then '差'
 when writtwenExam between 61 and 70 then '一般'
 when writtwenExam between 71 and 80  then '中'
 when writtwenExam between 81 and 90 then '良'
 else
  '优'
end
from stuMarks