----------------------------------T-SQL(MSSQL_SERVER独有语言)-------------------------------
--T-SQL基本命令：
--DECLARE 声明
--PRINT  输出
--getDate 获得时间
--CONVERT 类型转换
--CAST  类型转换
DECLARE @name VARCHAR(60);
SET @name = '左晓芬';
PRINT @name + '电话号码：' + CONVERT(VARCHAR(11), 13419546540);
PRINT @name + '电话号码：' + CAST(13419546540 AS VARCHAR(11));

 

--全局变量：
--标识： @@
--例如：@@version , @@ERROR , @@CONNECTIONS , @@IDENTITY
SELECT @@VERSION AS '版本' , @@ERROR AS '错误数' ,
@@CONNECTIONS AS '连接数' , @@IDENTITY AS '最后标识';

 

--局部变量：
--标识： @
--例如：@sumPreson , @avgMark , @countStudent
DECLARE @sum INT;
SELECT @sum=stuNO FROM stuInfo WHERE stuName='郭丽';
PRINT @sum;
SELECT * FROM stuInfo WHERE stuNO=10006;


--利用自定义变量查询(查询名字叫杨荣凯同学的学号相邻的两个同学信息)
DECLARE @stuNO INT;
SELECT @stuNO = stuNO FROM stuInfo
WHERE stuName = '杨荣凯';
SELECT * FROM stuInfo
WHERE stuNO = @stuNO - 1 OR stuNO = @stuNO + 1;


--逻辑控制语句IF ELSE
--语法：
-- IF()
--  BEGIN
--   ......
--  END
-- ELSE
--  BEGIN
--   ......
--  END
-->例子一：
IF EXISTS (SELECT stuNO FROM stuInfo WHERE stuName='左晓芬')
 BEGIN
  PRINT '这个同学存在！';
 END
ELSE
 BEGIN
  PRINT '这个同学不存在';
 END


-->例子二：
-->统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，
-->并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，
-->并显示后三名学员的考试信息 。
DECLARE @avgWriteExam FLOAT;
SELECT @avgWriteExam = AVG(labExam) FROM stuMark;
PRINT @avgWriteExam
IF(@avgWriteExam > 70)
 BEGIN
  PRINT '成绩优秀！';
 END
ELSE
 BEGIN
  PRINT '本班成绩较差';
 END

 

 

--循环控制语句 WHILE
--语法：
--WHILE()
--  BEGIN
--   ......
--  END
-->例子一：
-->累加1到100
DECLARE @sum INT , @i INT;
SET @sum = 0 ;
SET @i = 0 ;
WHILE(@i < 100)
 BEGIN
  SET @i = @i + 1;
  SET @sum = @sum + @i;
 END
PRINT '1到100累加后的值为 : '+CONVERT(VARCHAR(10),@sum);
--当要向标量赋予一个常量值时 需要在变量前面加上SET

-->例子二
-->创建一张表,并向里插入10000条数据
IF EXISTS ( SELECT * FROM sysobjects WHERE name='test')
DROP TABLE test ;
CREATE TABLE test(
 id INT IDENTITY(1,1) PRIMARY KEY,
 name VARCHAR(50)
);

DECLARE @count INT;
SET @count = 0 ;
WHILE(@count < 10000)
 BEGIN
  INSERT INTO test VALUES('神'+CAST( @count AS VARCHAR(10) ));
  SET @count = @count + 1 ;
 END
SELECT * FROM test;

 


--多分支语句 CASE   WHEN   THEN   WHEN   THEN   ELSE   END
--语法：
-- CASE
--  WHEN ......  THEN ......
--  WHEN ......  THEN ......
--  ELSE ......
-- END
-->例子:查询成绩表笔试成绩等级
SELECT * , '等级' =
CASE
 WHEN writtenExam >= 90 THEN 'A'
 WHEN writtenExam BETWEEN 80 AND 90 THEN 'B'
 WHEN writtenExam BETWEEN 70 AND 80 THEN 'C'
 WHEN writtenExam BETWEEN 60 AND 70 THEN 'D'
 ELSE 'E'
END
FROM stuMark 


--------------------------------------------------------------------------------------

 

----------------------------------实际应用的例子--------------------------------------
-->取出TEST表中后100条数据，并按升序排列
SELECT * FROM (SELECT TOP 100 * FROM test ORDER BY id DESC) AS T ORDER BY id ASC

-->取出test表中第10行到第100行的数据
SELECT TOP 100 * FROM test EXCEPT SELECT TOP 9 * FROM test     --第一种
SELECT TOP 91 * FROM test WHERE id NOT IN(SELECT TOP 9 id FROM test )    --第二种

-->本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，
-->先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，
-->再看是否都通过，如此反复提分，直到所有人都通过为止 。
DECLARE @count INT ;
SELECT @count = COUNT(*) FROM stuMark WHERE writtenExam < 60;
WHILE(@count > 0)
 BEGIN
  UPDATE stuMark SET writtenExam = writtenExam + 2 WHERE writtenExam < 60 ;
  SELECT @count = COUNT(*) FROM stuMark WHERE writtenExam < 60;
 END
SELECT * FROM stuMark;          --第一种

DECLARE @minMark FLOAT;
SELECT @minMark = MIN(writtenExam) FROM stuMark ;
PRINT '这个班级最低分为：'+CAST( @minMark AS VARCHAR(10) );
WHILE(@minMark < 60)
 BEGIN
  UPDATE stuMark SET writtenExam = writtenExam + 2 WHERE writtenExam < 60;
  SELECT @minMark = MIN(writtenExam) FROM stuMark ;
 END
SELECT * FROM stuMark;          --第二种


-->则根据如下规则对机试成绩进行反复加分，
-->直到平均分超过85分为止。请编写T-SQL语句实现。
-->90分以上：   不加分
-->80－89分：   加1分
-->70－79分：   加2分
-->60－69分：   加3分
-->60分以下：   加5分
DECLARE @avg FLOAT;
SELECT @avg = AVG(labExam) FROM stuMark
WHILE(@avg <= 85)
 BEGIN
  UPDATE stuMark SET labExam = labExam +
  CASE
   WHEN labExam >= 90 THEN 0
   WHEN labExam BETWEEN 80 AND 90 THEN 1
   WHEN labExam BETWEEN 70 AND 80 THEN 2
   WHEN labExam BETWEEN 60 AND 70 THEN 3
   ELSE 5
  END
  SELECT @avg = AVG(labExam) FROM stuMark
 END
SELECT * FROM stuMark


-------------------------------------------------------------------------------------------------