T-SQL 编程小结 (SQL Server)

--变量，局部变量一个@，全局变量 两个@DECLARE @name varchar(16)SET @name='哈哈'PRINT @name--查询赋值变量SELECT @name=filmName FROM filmInfo WHERE filmID='GH20070518300005'PRINT @name--T -SQL查找 某某 同学及她的左右同桌DECLARE @seat intSELECT @seat=seat  FROM Stuinfo  where xm='某某' SELECT * FROM stuinfo WHERE (seat=@seat+1) or (seat=@seat-1)--全局变量print @@MAX_CONNECTIONS --可以创建的同时连接的最大数目print  @@IDENTITY  --最后一次插入的标识值（当前连接）print @@VERSION    ---当前SQL SERVER 的版本号print @@ERROR  --错误号，如果为0 表示成功--查询比座位号6的同学 大一岁和小一岁的同学的信息DECLARE @age intDECLARE @name varchar(20)SELECT @name=xm,@age=datediff(year,csrq,getdate()) FROM xsb WHERE seat=6SELECT @name as 名称,@age as 年龄SELECT * FROM xsb WHERE (datediff(year,csrq,getdate())=@age+1) OR (datediff(year,csrq,getdate())=@age-1)--IF ELSE , BEGIN END类似{},只有一个子语句的话 可以不要。DECLARE @myavg floatSELECT @myavg=round(AVG(writtenExam),1) FROM stuMarksPRINT '平均分：'+CONVERT(varchar(5),@myavg)IF(@myavg>70)BEGINPRINT  '本班成绩比较优秀，前三为：'SELECT TOP 3 * FROM stuMarks ORDER BY writtenExam DESC --倒序ENDELSEBEGINPRINT '本班成绩较差，后三名为：'SELECT TOP 3  *  FROM stuMarks ORDER BY writtenExam  --升序 END--WHILE循环语句--循环输出DECLARE @ix intSET @ix=10WHILE(@ix>=0)BEGINPRINT @ixSET @ix=@ix-1END--case 分支--CASE-- WHEN 条件 THEN 结果-- WHEN 条件 THEN 结果--  ELSE 其它结果--ENDDECLARE @score intSET @score=80SELECT CASE WHEN @score<60 THEN 'E'WHEN @score BETWEEN 60 AND 69 THEN 'D'WHEN @score BETWEEN 70 AND 79 THEN 'C'ELSE 'A'END