--10-1
DECLARE @name VARCHAR(20) --定义用户名
DECLARE @addr VARCHAR(20) --定义地址
SET @name='张敏'     --使用set给用户名赋值
SELECT @addr=addr FROM UserInfo --使用select存储张敏的地址
WHERE UserName=@name
SELECT * FROM UserInfo WHERE Addr=@addr --筛选数据
--10-2
DECLARE @bookName nvarchar(100),@author nvarchar(100)
--声明多个变量
SET @bookName='java' 
SET @author='孙天琴' 
   --使用SET给多个变量赋值
SElECT @bookName='c#',@author='明日'
   --使用SELECT给多个变量赋值
SET @bookName=(SELECT BookName FROM BookInfo)
   --SET将多个查询的结果赋值给变量出错

SELECT @author=Author FROM BookInfo
  --使用SELECT将查询的最有一个值赋给变量
SET @author=(SELECT Author FROM BookInfo WHERE Price<0)
  --查询结果不存在时将NULL赋给变量
SELECT @author='林林'
SELECT @author=Author FROM BookInfo WHERE Price<0
  --查询结果不存在时@author保持原值

--10-3
PRINT  'SQLServer版本：'+@@version+',服务器名称:'+@@servername
SELECT @@servername AS '服务器名称',GETDATE() AS '当前日期'

--10-4
  PRINT  '    ★   '
  PRINT  '   ★★  '
  PRINT  '  ★★★ '
  PRINT  ' ★★★★'
  PRINT  '★★★★★'

--星号
  DECLARE @star VARCHAR(2)
  DECLARE @ch VARCHAR(1)
  SET @star='★'
  SET @ch=' '
  PRINT @ch+@ch+@ch+@ch+@star
  PRINT @ch+@ch+@ch+@star+@star
  PRINT @ch+@ch+@star+@star+@star
  PRINT @ch+@star+@star+@star+@star
  PRINT @star+@star+@star+@star+@star
--10-5
--声明变量
  DECLARE @money DECIMAL
  DECLARE @count INT  
  --赋值
  SELECT @count=SUM(Quantity),
  @money=SUM(Price*Discount*Quantity)
  FROM SalesMaster a,SalesDetails b
  WHERE a.SalesMasterID=b.SalesMasterID
  AND DATEPART(yy,a.SalesDate)=2013
  --输出
  PRINT '2013年的销售总额为:'+@money+'元'
  PRINT '2013年的销售总量为:'+@count+'本'
--10-6

IF EXISTS(SELECT * FROM BookInfo WHERE BookID not in 
          (SELECT DISTINCT BookID FROM SalesDetails)
          )
 BEGIN
      PRINT '没有销售过的书籍是:'
      SELECT BookName,Author,PublishDate,Price
      FROM BookInfo a WHERE BookID not in
      (SELECT DISTINCT BookID FROM SalesDetails)
 END
 ELSE
     PRINT '所有的书籍都销售过'


--10-7
DECLARE @salesId INT 
 DECLARE @quantity INT
 DECLARE @saleDate DATETIME
 SELECT TOP 1  @salesId=SalesMasterID,@saleDate=SalesDate
 FROM SalesMaster ORDER BY SalesDate DESC
 
 SELECT @quantity=SUM(Quantity)
 FROM SalesDetails WHERE SalesMasterID=@salesId
 
 IF(@quantity>10)
 PRINT CONVERT(VARCHAR(10),@saleDate,112)+'销售良好,销售的数量是'+CAST(@quantity AS VARCHAR(10))+'本'
 ELSE IF(@quantity<5)
      PRINT CAST(@saleDate AS VARCHAR(20))+'销售一般,销售的数量是'+CAST(@quantity AS VARCHAR(10))+'本'
      ELSE
          PRINT CAST(@saleDate AS VARCHAR(20))+'销售差,销售的数量是'+CAST(@quantity AS VARCHAR(10))+'本'

--10-8
SELECT 书名=BookName,原价=a.Price,
      销售价=b.Discount*b.Price,
      销售数量=b.Quantity,折扣=CASE discount
      WHEN 1 THEN '全价'
      WHEN 0.105 THEN '九五折'
      WHEN 0.10 THEN '九折'
      ELSE '特价'
      END
      FROM BookInfo a, SalesDetails b
      WHERE a.BookID=b.BookID

--10-9
 SELECT 书名=a.BookName,销售数量=ISNULL(SUM(b.quantity),0),
      销售等级=CASE 
      WHEN SUM(b.quantity) > 50 THEN '优秀'
      WHEN SUM(b.quantity) between 30 AND 50 THEN '良好'
      WHEN SUM(b.quantity) between 10 AND 210 THEN '一般'
      WHEN SUM(b.quantity) between 1 AND 10 THEN '差'
      ELSE '没有销售'
      END
      FROM BookInfo a left join SalesDetails b
      ON a.BookID=b.BookID
      GROUP BY a.BookName 
      ORDER BY SUM(b.quantity) DESC

--10-10
DECLARE @i INT
DECLARE @j INT
DECLARE @str VARCHAR(110)
SET  @i=1

WHILE  @i<10
  BEGIN
     SET  @j=1
     SET @str=''
     WHILE @j<=@i
        BEGIN
          SET @str= @str+CAST(@i AS VARCHAR(1))+' x '+CAST(@j AS VARCHAR(1))+'='+CAST(@i*@j AS VARCHAR(2))+'    '
          SET  @j=@j+1
        END
    PRINT @str
    SET  @i=@i+1
  END

--10-11
--创建历史销售记录表
  CREATE TABLE His_Sales
  (Id INT IDENTITY(1,1) PRIMARY KEY,
   BookId CHAR(5) NOT NULL,
   BookName VARCHAR(20) NOT NULL,
   SaleDate DATETIME,
   Quantity INT,
   Discount FLOAT,
   Price MONEY,
   SalePrice MONEY)
  GO
  --插入历史记录
  INSERT INTO Sales
  SELECT b.BookID,c.BookName ,a.SalesDate,
  b.Quantity,b.Discount,b.Price,b.Discount*b.Price
  FROM SalesMaster a, SalesDetails b, BookInfo c
  WHERE a.SalesMasterID=b.SalesMasterID
  AND b.BookID=c.BookID
  AND DATEPART(yy,a.SalesDate)<2013
  GO
  --删除销售明细记录
  DELETE FROM SalesDetails 
  WHERE SalesMasterID in
  (SELECT SalesMasterID
  FROM SalesMaster WHERE DATEPART(yy,SalesDate)<2013)
  --删除销售主记录
  DELETE  FROM SalesMaster WHERE DATEPART(yy,SalesDate)<2013
  GO
