--字符串类型
declare @name nvarchar(32) -- 声明
set @name ='库里'
-- 输出语句
print @name
-- 数字类型
declare @balance decimal(18,2)
set @balance=16.23
print @balance
--日期类型 存储‘火火’出生日期
declare @birthday datetime
set @birthday ='1998-02-18'
print @birthday
-- 数据类型转换 cast as
declare @num int
set @num = 500000
print '这个数是：'+ cast(@num as nvarchar(32))
-- 数据类型转换 convert
declare @num1 int
set @num1 = 3000
print '这个数是：：：'+ convert(nvarchar(32),@num1)
-- if else
declare @age int
set @age = 15
if(@age>=18)
begin
print'可以观看'
end
else
begin
print'不可以观看！再长几年吧'
end
--练习
--统计并显示2013-08-09的oop考试的平均分
--如果平均分在70分以上，显示“考试成绩优秀”，并显示前三名学生的考试信息
--如果在以下，显示“考试成绩较差”，并显示后三名学生的考试成绩
select * from result
order by examdate
--1.求出oop课程对应的课程编号
declare @subid int
select @subid=SubjectId from Subject
where subjectname='oop'
--2.查询平均分
declare @avg int
select @avg=avg(Studentresult) from result
where examdate>='2013-08-09' and examdate<'2013-08-10'
and subjectid=@subid
if(@avg>=70)
begin
print '成绩优秀'
--打印前三名的成绩
select top 3 * from result
where examdate>='2013-08-09' and examdate<'2013-08-10'
and subjectid=@subid
order by studentresult desc
end
else
begin
print '成绩较差'
--打印前三名的成绩
select top 3 * from result
where examdate>='2013-08-09' and examdate<'2013-08-10'
and subjectid=@subid
order by studentresult
end
-- 上机一
declare @tu nvarchar(32)
set @tu ='☆'
print @tu
print @tu+@tu
print @tu+@tu+@tu
print @tu+@tu+@tu+@tu
print @tu+@tu+@tu+@tu+@tu
-- 上机二
select studentno as '学号',StudentName as '姓名',FLOOR(DATEDIFF(dy,BornDate,getdate())/365)
as '年龄' from dbo.Student where studentno='S1101001'
declare @yy int
select @yy=DATEPART(YY,BornDate) from student where studentno='S1101001'
select * from student where DATEPART(YY,BornDate)=@yy+1 or DATEPART(YY,BornDate)=@yy-1
go
-- 上机三
-- 1 查询课程id
declare @id2 int
select @id2=subjectid from Subject WHERE subjectname='java'
-- 2 查询该学生的成绩 学号为 23219
declare @cheng int
select @cheng=Studentresult from result where subjectid=@id2 and studentno=23219
--print @chengji
if(@cheng>=85)
begin
Print '优秀!'
end
else if(@cheng>=70)
begin
Print '良好!'
end
else if(@cheng>=60)
begin
Print '中等!'
end
else
begin
Print '差!'
end
转载请注明原文地址: https://ju.6miu.com/read-500079.html
