- 使用IF ELSE查询马行空是否选修了数据库课程,如果有显示“已选修”,没有显示“没选修”;
if exists( select * from Student,sc,course where SName = '马行空' and Student.SNo = sc.SNo and CName = '数据库') print '已选修' else print '没选修'
- 使用IF ELSE BEGIN END查询选修课程003的情况,如果不为空则显示“有学生选修”,并查询选修课程的人数,否则显示“无学生选修”;
if exists ( select * from sc where CNo = '003') begin print '有学生选修' select count(*) from sc where CNo = '003' end else print '无学生选修'
- 使用WHILE查询学号为00001、00002、00003、00004,课程号为002的成绩,并分别把成绩赋予给变量@s,然后显示出来;
declare @x int,@y varchar(5),@s int select @x = 1 set @y = '00001' while @y!='00005' begin select @s = score from sc where CNo ='002' and SNo = @y print @s set @x = @x +1 set @y = '0000' set @y = @y + convert(char(1),@x) end
- 使用WHILE BREAK查询学号为00001、00002、00003、00004,课程号为002的成绩,并分别把成绩赋予给变量@s,如果成绩为47,则显示“成绩过低”,程序结束,否则显示成绩;
declare @x int,@y varchar(5),@s int set @x =1 set @y = '00001' while @y != '00005' begin select @s = score from sc where CNo ='002' and SNo = @y if @s = 47 begin print '学生成绩过低' break end else begin print @s set @x = @x +1 set @y ='0000' set @y =@y+convert(char(1),@x) end end
- 使用WHILE CONTINUE查询学号为00001、00002、00003、00004,课程号为002的成绩,并分别把成绩赋予给变量x,如果成绩为47,则不显示,否则显示成绩;
declare @x int,@y varchar(5),@s int set @x =1 set @y = '00001' while @y != '00005' begin select @s = score from sc where CNo ='002' and SNo = @y set @x = @x +1 set @y ='0000' set @y =@y+convert(char(1),@x) if @s=47 continue print @s end
- 指示SQL SERVER等待10s后查询表Student;
waitfor delay '00:00:10' select * from Student
- 使用CASE表达式查询SC情况,如果CNO=001则显示“数据结构”,如果CNO=002则显示“数据库”,如果CNO=003则显示“C语言”,其它则显示“JAVA”;
select SNo,score,CNo=case CNo when '001' then '数据结构' when '002' then '数据库' when '003' then 'C语言' else 'JAVA' end from sc
- 使用GOTO查询名字为“吕小妹”的同学,如果存在,显示该同学的信息;否则显示“查无此人”;
if exists( select SNo from Student where SName = '吕小妹') goto noation else begin print '查无此人' return end noation: select * from Student where SName = '吕小妹'
- 自定义一个函数,实现如下功能:对于一个给定的学号,查询该学号是否在Student表中,如果在则返回0,否则返回-1,然后编程调用该函数测试;
create function SNo_getter(@sno char(5)) returns int AS BEGIN DECLARE @a int if exists( select * from Student where SNo = @sno) set @a = 0 else set @a = -1 return @a end select dbo.SNo_getter('123') as '123'; select dbo.SNo_getter('00001') as '00001'
- 自定义一个函数,实现如下功能:对于一个给定的学生姓名,查询该姓名是否在Student表中,如果有两个或两个以上相同的名字,则返回-2,如果只有唯一名字,则返回该学生所修的总学分(及格才算有学分),如果没有该名字,则返回-1,然后编程调用该函数测试;
create function SName_getter(@sname char(8)) returns int AS BEGIN declare @v int IF exists( select * from Student where SName = @sname) begin if ((select count(*) from Student where SName = @sname) > 1) set @v = -2 else select @v = count(c.CCredits) from Student s, sc, course c where s.SNo = sc.SNo and s.SName = @sname and c.CNo = sc.CNo and sc.Score >=60 end ELSE set @v = -1 return @v END select dbo.SName_getter('苗人凤') '苗人凤'; select dbo.SName_getter('图灵') '图灵';
- 自定义一个函数,实现如下功能:对于一个给定的课程号,查询没有修读该课程的学生名字。
create function name_getter(@cno char(5)) returns table AS return( select SName from Student where SNo not in( select SNo from sc where CNo = @cno)) select * from dbo.name_getter('001')
ZK数据库实验六(T-SQL中的控制语句)
最新推荐文章于 2023-01-15 19:40:10 发布