常用语句（六）print、beign ... end、procedure、declare、@、exec

最新推荐文章于 2024-12-22 18:48:51 发布

原创最新推荐文章于 2024-12-22 18:48:51 发布 · 860 阅读

0 ·

CC 4.0 BY-SA版权

T-SQL 专栏收录该内容

7 篇文章

订阅专栏

本文档通过SQL语句展示了如何处理学生的成绩数据，包括条件判断、平均分计算、成绩提升逻辑及存储过程的创建与使用。示例中包含了成绩的插入、选择最低分、更新分数等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/*
*/
if(select MIN(grade) from score where sno=20130204)>75
	print '良好'
else if(select MIN(grade) from score where sno=20130204)>60
	print '及格'
else
	print '不及格'
select MIN(grade) from score where sno=20130204
go
/*
*/
create table testScore(scno int not null identity(1,1) primary key,mark int check(mark between 0 and 100))
go
truncate table testScore
go
insert into testScore(mark) values (64);
insert into testScore(mark) values (57);
insert into testScore(mark) values (52);
insert into testScore(mark) values (72);
insert into testScore(mark) values (47);
select avg(mark) from testScore
go
select * from testScore
go
while(select avg(mark) from testScore)<80
begin
	update testScore set mark=mark*1.05
	if(select min(mark) from testScore)>60
		break
end
select * from testScore
go
/*
*/
select MIN(grade) from score where sno='20130206'
go
/*
*/
declare @zuixiaofen int
select @zuixiaofen=MIN(grade) from score where sno='20130206'
print @zuixiaofen
if @zuixiaofen>60
	print '及格'
else
	print '不及格'
/*
*/
create procedure usp_show_student as select * from student where age>21
go
exec usp_show_student
drop procedure usp_show_student
go
/*
*/
--定义存储过程包括输入参数和输出参数
if exists(select * from sysobjects where name='usp_s1')
	drop procedure usp_s1
go
/*create procedure usp_s1 @myscore int as select sno from score where grade<@myscore
go
exec usp_s1 60*/
create procedure usp_s1 @myscore int,@mymax int output as select sno from score where grade<@myscore select @mymax=MAX(grade) from score
go
declare @mymax int
--执行存储过程，必须声明输出参数
exec usp_s1 60,@mymax output
print @mymax
/*
*/