sql 笔记

--sql 变量赋值
DECLARE @name varchar(8)                                      --学生姓名
SET @name = '李文才'                                                  --使用SET赋值
SELECT StudentNo, StudentName, BornDate, Address
 FROM Student 
 WHERE StudentName = @name
/*--查找李文才的左右同桌--*/
DECLARE @StudentNo int                                          --学号
SELECT @StudentNo = StudentNo FROM Student  --使用SELECT赋值
  WHERE StudentName = @name
SELECT StudentNo, StudentName, BornDate, Address
 FROM Student 
 WHERE (StudentNo = @StudentNo+1) OR (StudentNo = @StudentNo-1)
GO
----------------------------------------------------------------

 

--sql 类型转换
SELECT StudentName + '的出生日期是' + 
          CAST(BornDate as varchar(50)) AS '学生信息'
FROM Student


SELECT StudentName, 
               CONVERT(varchar(50),BornDate,102) AS 出生日期
FROM Student

--计算年龄 大一岁 和 小一岁
DECLARE @NO int                                          -- 学号
SET @NO = 20011
DECLARE @date datetime                             -- 出生日期
DECLARE @year int                                       -- 出生年份
-- 获得学号是20011的学生姓名和年龄
SELECT StudentName 姓名,
               FLOOR(DATEDIFF(DY, BornDate, GETDATE())/365) 年龄
   FROM student  WHERE StudentNo=@NO

-- 查询输出比学号是20011的学生大1岁和小1岁的学生信息
SELECT  @date=BornDate FROM Student   -- 使用SELECT赋值
  WHERE StudentNo=@NO
SET @year = DATEPART(YY, @date)             
SELECT *  FROM Student 
   WHERE DATEPART(YY,BornDate) = @year + 1
                 OR DATEPART(YY,BornDate) = @year - 1
GO
CEILING 函数返回大于或等于所给数字表达式的最小整数。FLOOR 函数返回小于或等于所给数字表达式的最大整数。
 例如，对于数字表达式 12.9273，CEILING 将返回 13sql 语法，FLOOR 将返回 12。FLOOR 和 CEILING 返回值的数据类型都与输入的数字表达式的数据类型相同。

--sql while
WHILE (1 = 1)                                  --条件永远成立
  BEGIN
    SELECT @n=COUNT(*) FROM Result
      WHERE SubjectNo=@subNO AND ExamDate=@date 
           AND StudentResult < 60   --统计不及格人数
    IF (@n > 0)                                 --每人加2分
       UPDATE Result SET StudentResult=StudentResult+2 FROM Result
       WHERE SubjectNo=@subNO AND ExamDate=@date AND 
                     StudentResult < 95
    ELSE
       BREAK                                    --退出循环
  END

--sql case
SELECT 学号=studentNo, 课程编号=SubjectNo,
     成绩=StudentResult, 平均分=AVG(StudentResult)  
     等级=CASE  
       WHEN StudentResult <60 THEN '不及格'
       WHEN StudentResult BETWEEN 60 AND 69 THEN '差'
       WHEN StudentResult BETWEEN 70 AND 79 THEN '中'
       WHEN StudentResult BETWEEN 80 AND 89 THEN '良'
       ELSE '优'
　　　　end

     
  FROM Result

--sql GROUP BY dbo.Categories.CategoryName, dbo.Products.ProductName  大类 与 小类
SELECT dbo.Categories.CategoryName, dbo.Products.ProductName, SUM(CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) 
* (1 - dbo.[Order Details].Discount) / 100) * 100) AS ProductSales
FROM dbo.Categories INNER JOIN
dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID INNER JOIN
dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID ON dbo.Products.ProductID = dbo.[Order Details].ProductID
WHERE (dbo.Orders.ShippedDate BETWEEN '19970101' AND '19971231')
GROUP BY dbo.Categories.CategoryName, dbo.Products.ProductName