--用DECLARE声明变量;用SET给变量赋值--要注意，如果声明的变量类型不是SQL所属的，就会报错。DECLARE @j AS INT;SET @j = 10;DECLARE @i AS float;SET @i = 10.0;DECLARE @m AS CHAR;SET @m=‘M‘;DECLARE @n AS DATETIME =‘2014-9-28 9:18:16‘;USE TSQLFundamentals2008DECLARE @empname AS NVARCHAR(61)SET @empname = (SELECT firstname+N‘ ‘+lastname FROM HR.Employees WHERE empid=9)SELECT @empname AS hah;DECLARE @FN AS NVARCHAR(20),@LN AS NVARCHAR(40)SET @FN = (SELECT firstname FROM HR.Employees WHERE empid = 7)SET @LN = (SELECT lastname FROM HR.Employees WHERE empid = 7)SELECT @FN AS 姓,@LN AS 名;--用赋值SELECT语句赋值，结果只会输出最后一行；用SET赋值更安全DECLARE @emp_name AS NVARCHAR(60)SELECT @emp_name=firstname+‘ ‘+lastname FROM HR.Employees WHERE mgrid = 2SELECT @emp_name AS 雇员名字--批处理PRINT ‘fb‘--显示在消息里SELECT * FROM HR.Employees --显示在结果里GOPRINT ‘fb2‘SELECT * FROM Sales.OrdersGOPRINT ‘fb3‘SELECT * FROM Sales.ShippersGO--下面的程序会报错！注意原因！其次还包括视图，rule等。。。if OBJECT_ID(‘[dbo].[HwocaoSA]‘,‘p‘) IS NOT NULLdrop procedure [dbo].[HwocaoSA]--GOCREATE PROCEDURE HwocaoSAASBEGINselect * from [HR].[Employees]END--【有点疑问】书P324[书上说下面这段代码是不可行的，但是事实上是可行的。why?]IF OBJECT_ID(‘Table_1‘,‘U‘) IS NOT NULLDROP TABLE Table_1 create table Table_1(hihi int)ALTER TABLE Table_1 ADD HEHE INTSELECT hihi,HEHE FROM Table_1GO 100--流程控制元素--IF...ELSE...IF YEAR(CURRENT_TIMESTAMP) <> YEAR(DATEADD(day,1,CURRENT_TIMESTAMP))--<>是不等于，正式，！=不正式PRINT ‘Today is the last day of year‘;ELSEPRINT ‘Today is not the last day of year‘;--WHILE循环DECLARE @i AS INT;SET @i = 1;WHILE @i <=10BEGINPRINT @i;SET @i = 1+@i;--最开始这刚没加‘SET‘ 报错。SET是赋值的意思啊亲！！END--跳出当前循环：BREAKDECLARE @F AS INT;SET @F = 10;WHILE @F > 0BEGINIF (@F < 2) BREAK;PRINT @F;SET @F = @F -1;END--if else和while结合使用SET NOCOUNT ON;IF OBJECT_ID(‘TABLE_1‘,‘U‘) IS NOT NULL --如果用户表TABLE_1不为空DROP TABLE TABLE_1CREATE TABLE TABLE_1(num1 INT NOT NULL PRIMARY KEY)GODECLARE @i AS INT;SET @i = 1;WHILE @i < 124BEGININSERT INTO TABLE_1(num1) VALUES (@i)SET @i = @i +1;ENDGOSELECT * FROM TABLE_1GO--用游标来计算Sales.CustOrders视图中每个客户每个月的连续订货量--如果不知道是怎么执行的，可以单步调试下SET NOCOUNT ON;USE TSQLFundamentals2008;DECLARE @Result TABLE(  custid INT,  ordermonth DATETIME,  qty INT,  runqty INT,  PRIMARY KEY(custid,ordermonth));DECLARE  @custid AS INT,  @prvcustid AS INT,  @ordermonth AS DATETIME,  @qty AS INT,  @runqty AS INT; --runqty为客户当前连续总订货量  DECLARE C CURSOR FAST_FORWARD FOR  --基于一个查询声明了一个游标  SELECT custid,ordermonth,qty  FROM Sales.CustOrders  ORDER BY custid,ordermonth;OPEN C   --打开游标FETCH NEXT FROM C INTO @custid,@ordermonth,@qty;--将游标移到下一条并相应操作SELECT @prvcustid = @custid,@runqty=0;WHILE @@FETCH_STATUS = 0  --当还没有超出游标的最后一行时，通过循环来遍历每个记录BEGIN  IF @custid <> @prvcustid    SELECT @prvcustid = @custid,@runqty = 0;      SET @runqty = @runqty + @qty;    INSERT INTO @Result VALUES(@custid, @ordermonth, @qty, @runqty);    FETCH NEXT FROM C INTO @custid,@ordermonth,@qty;ENDCLOSE C;DEALLOCATE C;SELECT   custid,  CONVERT(VARCHAR(7), ordermonth, 121) AS ordermonth,  qty,  runqtyFROM @ResultORDER BY custid, ordermonth;--局部临时表USE TSQLFundamentals2008;IF OBJECT_ID(‘tempdb.dbo.#MyOrderTotalsYear‘) IS NOT NULL  DROP TABLE dbo.#MyOrderTotalsYear;GOSELECT   YEAR(O.orderdate) AS orderyear,  SUM(OD.qty) AS qtyINTO dbo.#MyOrderTotalsByYearFROM Sales.Orders AS O  JOIN Sales.OrderDetails AS OD    ON OD.orderid = O.orderidGROUP BY YEAR(orderdate);SELECT Cur.orderyear,Cur.qty AS curyearqty,Prv.qty AS prvyearqtyFROM dbo.#MyOrderTotalsByYear AS Cur  LEFT OUTER JOIN dbo.#MyOrderTotalsByYear AS Prv    ON Cur.orderyear = Prv.orderyear + 1;    --全局临时表CREATE TABLE dbo.##Globals--创建一个全局临时表(  id sysname NOT NULL PRIMARY KEY,--SQL SERVER在内部用这个类型来代表标识符  val SQL_VARIANT NOT NULL --一种通用的数据类型，差不多可以保存任何基础类型的值)--任何人都可以向全局临时表中插入数据行INSERT INTO dbo.##Globals(id,val)VALUES(N‘i‘,CAST(10 AS INT));--表变量[只对当前批处理可见]DECLARE @MyOrderTotalsByYear TABLE(  orderyear INT NOT NULL PRIMARY KEY,  qty INT NOT NULL);INSERT INTO @MyOrderTotalsByYear(orderyear, qty)  SELECT     YEAR(O.orderdate) AS orderyear,    SUM(OD.qty) AS qty    FROM Sales.Orders AS O     JOIN Sales.OrderDetails AS OD      ON OD.orderid = O.orderid  GROUP BY YEAR(orderdate);  SELECT Cur.orderyear,Cur.qty AS curyearqty, Prv.qty AS prvyearqtyFROM @MyOrderTotalsByYear AS Cur  LEFT OUTER JOIN @MyOrderTotalsByYear AS Prv    ON Cur.orderyear = Prv.orderyear +1;    --表类型【额，原样复制代码，但是竟然报错了……】USE TSQLFundamentals2008;IF TYPE_ID(‘dbo.OrderTotalsByYear‘) IS NOT NULL  DROP TYPE dbo.OrderTotalsByYear;  CREATE TYPE dbo.OrderTotalsByYear AS TABLE(  orderyear INT NOT NULL PRIMARY KEY,  qty INT NOT NULL);DECLARE @MyOrderTotalsByYears AS dbo.OrderTotalsByYear;INSERT INTO @MyOrderTotalsByYears(orderyear, qty)  SELECT     YEAR(O.orderdate) AS orderyear,    SUM(OD.qty) AS qty  FROM Sales.Orders AS O    JOIN Sales.OrderDetails AS OD      ON O.orderid = OD.orderid  GROUP BY YEAR(orderdate);  SELECT orderyear,qty FROM @MyOrderTotalsByYears;--P338动态SQL待续。。。