DECLARE @name varchar(8)
set @name='微冷的雨'
select [StudentNo],[StudentName],[Birthday],[Address]
from [dbo].[Student]
where [StudentName]=@name
set @name='微冷的雨'
select [StudentNo],[StudentName],[Birthday],[Address]
from [dbo].[Student]
where [StudentName]=@name
--查找学号与微冷的雨相邻的学生信息
DECLARE @StudentNo int
declare @name varchar(8)
SET @name='微冷的雨'
select @StudentNo= StudentNo from [dbo].[Student]
where StudentName=@name
select *
from [dbo].[Student]
where (StudentNo=@StudentNo+5) or (StudentNo=@StudentNo-41)
GO
from [dbo].[Student]
where (StudentNo=@StudentNo+5) or (StudentNo=@StudentNo-41)
GO
--数字类型
declare @StudentNo int
SET @StudentNo=12
PRINT @StudentNo
--日期类型
declare @birthday datetime
SET @birthday='1998-02-02'
PRINT @birthday
declare @birthday datetime
SET @birthday='1998-02-02'
PRINT @birthday
--转换类型
-- 1 cast
declare @num1 int
SET @num1 = 5
PRINT 'num1的值为:'+cast(@num1 as nvarchar(32))
-- 2 convert
declare @num2 int
SET @num2 = 5
PRINT 'num2的值为:'+convert(nvarchar(32),@num2)
-- @@ERROR 最后一个T-SQL错误的错误号 例子
SELECT * form MySchool
SELECT @@ERROR
-- @@IDENTITY 最后一次插入的标识值
INSERT INTO Grade VALUES ('S3')
SELECT @@IDENTITY
-- @@SERVERNAME 本地服务器的名称
SELECT @@SERVERNAME
-- @@VERSION SQL Server 的版本信息
SELECT @@VERSION
-- 统计并显示 2013-08-09 的 oop 考试平均分
-- 如果平均分在70分以上,显示"考试成绩优秀",并显示前三名学生的考试信息
-- 如果在70分以下,显示"考试成绩较差",并显示后三名学生的考试信息
select * from result
declare @id int
select @id=subjectid from Subject WHERE subjectname='oop'
--PRINT @id
-- 1 查询平均分
declare @pingjun int
select @pingjun=AVG(studentresult) from result WHERE examdate >='2013-08-09' and examdate <'2013-08-10' and subjectid=@id
--PRINT @pingjun
--判断
if(@pingjun>=70)
begin
print '成绩优秀!'
--打印前三名成绩
select top 3 * from result where examdate >='2013-08-09' and examdate <'2013-08-10' and subjectid=@id 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=@id order by studentresult
end
-- 1 cast
declare @num1 int
SET @num1 = 5
PRINT 'num1的值为:'+cast(@num1 as nvarchar(32))
-- 2 convert
declare @num2 int
SET @num2 = 5
PRINT 'num2的值为:'+convert(nvarchar(32),@num2)
-- @@ERROR 最后一个T-SQL错误的错误号 例子
SELECT * form MySchool
SELECT @@ERROR
-- @@IDENTITY 最后一次插入的标识值
INSERT INTO Grade VALUES ('S3')
SELECT @@IDENTITY
-- @@SERVERNAME 本地服务器的名称
SELECT @@SERVERNAME
-- @@VERSION SQL Server 的版本信息
SELECT @@VERSION
-- 统计并显示 2013-08-09 的 oop 考试平均分
-- 如果平均分在70分以上,显示"考试成绩优秀",并显示前三名学生的考试信息
-- 如果在70分以下,显示"考试成绩较差",并显示后三名学生的考试信息
select * from result
declare @id int
select @id=subjectid from Subject WHERE subjectname='oop'
--PRINT @id
-- 1 查询平均分
declare @pingjun int
select @pingjun=AVG(studentresult) from result WHERE examdate >='2013-08-09' and examdate <'2013-08-10' and subjectid=@id
--PRINT @pingjun
--判断
if(@pingjun>=70)
begin
print '成绩优秀!'
--打印前三名成绩
select top 3 * from result where examdate >='2013-08-09' and examdate <'2013-08-10' and subjectid=@id 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=@id order by studentresult
end