DECLARE @name nvarchar(32) --声明
set @name ='AA' --赋值
print @name --打印
--数字类型
--SQL 数据类型 float numberic(18,2) decimal(18,7)
declare @shuzi decimal(18,7)
set @shuzi =12.21
print @shuzi
--日期类型
declare @riqi datetime
set @riqi='1998-04-27'
print @riqi
select *from Grade --查询学生信息
insert into Grade
values ('高级')
select @@IDENTITY as 新插入的行 --查询新插入的最后一行
select @@SERVERNAME --查询计算机的版本名称
数据类型转换
--cast 和 convert
declare @i int
set @i=5
print '数据类型为'+cast(@i as nvarchar(32) )
declare @q int
set @q =10
print '数据类型为'+convert(nvarchar(32), @q)
---- if else 语句
declare @wd int
set @wd=18
if(@wd>=18)
begin
print '成年'
end
else
begin
print '不成年'
end
--练习:
--统计并显示2013-08-09 的oop考试平均分
--如果平均分在70分以上,显示“考试成绩优秀”,并显示前三名学生的考试信息
--如果在以下,显示“考试成绩较差”,并显示后三名学生的考试信息
select * from Result
--求出OOP的科目编号
declare @subid int
select @subid=Subjectid from Subject
where SubjectName='oop'
--查询平均分
declare @pjf int
select @pjf=AVG(Studentresult) from Result
where ExamDate >='2013-08-09' and ExamDate<'2013-08-10'
and @subid=Subjectid
if(@pjf>=70)
begin
print '成绩优秀'
select top 3* from Result
where ExamDate >='2013-08-09' and ExamDate<'2013-08-10'
and @subid=Subjectid
order by StudentResult desc
end
else
begin
print '成绩较差'
select top 3* from Result
where ExamDate>='2013-08-09' and ExamDate<'2013-08-10'
and @subid=SubjectId
order by StudentResult
end
---上机练习1
declare @qq nvarchar(32)
set @qq='★'
print @qq
print @qq+@qq
print @qq+@qq+@qq
print @qq+@qq+@qq+@qq
print @qq+@qq+@qq+@qq+@qq
declare @iq int=1
declare @j int=1
declare @outstring nvarchar(19)=''
while(@iq<=10)--改变循环次数
begin
set @j=1
while(@j<=@iq)
begin
set @outstring+='★'
set @j+=1
end
set @outstring+=Char(10)
set @iq+=1
end
print @outstring
declare @chusheng datetime
select @chusheng = birthday from student WHERE studentno=23311
declare @xingming nvarchar(32)
select @xingming=studentname from student WHERE studentno=23311
declare @nianling int
SET @nianling = FLOOR(DATEDIFF(Dy,@chusheng,GETDATE())/365)
PRINT '姓名是:'+cast(@xingming as nvarchar(32))+ ' 年龄是:'+cast(@nianling as nvarchar(32))
-- 上机三
-- 1 查询课程id
declare @id2 int
select @id2=subjectid from Subject WHERE subjectname='java'
-- 2 查询该学生的成绩 学号为 23219
declare @chengji int
select @chengji=studentresult from result where subjectid=@id2 and studentno=23219
print @chengji
if(@chengji>=85)
begin
Print '优秀!'
end
else if(@chengji>=70)
begin
Print '良好!'
end
else if(@chengji>=60)
begin
Print '中等!'
end
else
begin
Print '差!'
end
--上机2
DECLARE @NO int
SET @NO = 23270
SELECT StudentName,FLOOR(DATEDIFF(DY, Birthday, GETDATE())/365)
FROM student WHERE StudentNo=@NO
DECLARE @date datetime,@year int
SELECT @date=Birthday FROM Student WHERE StudentNo=@NO
print @date
SET @year = DATEPART(YY,@date)
SELECT * FROM Student WHERE DATEPART(YY,Birthday)=@year+1 or DATEPART(YY,Birthday)=@year-1
--****************** 7.经典while循环加分题目***************************************
--检查学生参加“oop”课最近一次考试是否有不及格(分及格)的学生。
--如有,每人加分,高于分的学生不再加分,直至所有学生这次考试成绩均及格
--code a little ,debug a little 编写一点点,调试一点点
--***********************************************************************
--01.找一个变量存储oop科目编号
declare @id int
select @id=SubjectID from Subject
where SubjectName='oop'
--02.找到一个变量,存储最近一次考试时间
declare @maxdate datetime
select @maxdate= MAX(examdate) from Result
where SubjectId= @id
---在查询一遍结果
select * from Result
where Subjectid=@id
and ExamDate=@maxdate
--03.投影出符合条件的人数
declare @sum int
select @sum=COUNT(StudentNo) from Result
where SubjectId=@id
and ExamDate=@maxdate
and StudentResult<70
while(@sum>0)
begin
--每人+2分
update Result set StudentResult+=2
where SubjectId=@id
and ExamDate=@maxdate
and StudentResult<95
select @sum =COUNT(studentno) from Result
where SubjectId=@id
and ExamDate=@maxdate
and StudentResult<70
end
--直角三角形 两重循环
declare @i int
declare @j int
declare @str nvarchar(100)
set @i=1
set @j=1
set @str=''
while(@i<=5)
begin
while(@j<=@i)
begin
set @str+='☆'
set @j+=1
end
print @str
set @i+=1
end
--三角形一重循环
declare @name1 nvarchar(32)
set @name1='☆'
declare @int int
set @int=1
while(@int<=5)
begin
print @name1
set @name1+='☆'
set @int+=1
end
go
---上机练习3
--查询学号是2012 的学生 JAVA课程最近一次成绩
---1 找一个课程位 java
declare @bui int
select @bui=Subjectid from Subject
where SubjectName='java'
---2 找到一个变量 来存储最近一次考试时间
declare @date datetime
select @date =MAX(examdate) from Result
where SubjectId=@bui
select a.StudentName,等级=
case
when b.StudentResult>90 then '☆☆☆☆'
when b.StudentResult between 80 and 89 then '☆☆☆'
when b.StudentResult between 70 and 79 then '☆☆'
when b.StudentResult between 60 and 69 then '☆'
else
'你要努力了!!!'
end
from Student a,Result b
where a.StudentNo=b.StudentNo
select a.StudentName,b.StudentResult from Student a,Result b
where a.StudentNo=b.StudentNo
t-sql 笔记
最新推荐文章于 2024-08-09 08:40:22 发布