sql语句

USE Accounting;
DECLARE @Ident int;

INSERT INTO Orders
(CustomerNo,OrderDate,EmployeeID)
VALUES
(1,GETDATE(),1);

SELECT @Ident = @@IDENTITY;//系统函数

INSERT INTO OrderDetails
(OrderID,PartNo,Description,UnitPrice,Qty)
VALUES
(@Ident,'2R2416','Cylinder Head',1300,2);

SELECT 'The OrderID of the INSERTed row is ' + CONVERT(varchar(8),@Ident);
GO//结束一段批处理，后面的相当于另一个文件中的语句
<span style="font-family: Arial, Helvetica, sans-serif;">//GO是sqlcmd，management studio命令</span>

USE AdventureWorks2008;
DECLARE @Test money;
SELECT @Test = MAX(UnitPrice) FROM Sales.SalesOrderDetail;
SELECT @Test;
GO


//CREATE DEFAULT
//CREATE PROCEDURE
//CREATE RULE
//CREATE TRIGGER
//CREATE VIEW
//ALTER TABLE：：：需要结束时才能利用该修改
///要加入其它语句，组合，必须加GO，不然出错

//sqlcmd是windows命令

IF EXISTS(
<span style="white-space:pre">	</span>SELECT s.name AS SchemaName, t.name AS TableName
<span style="white-space:pre">		</span>FROM sys.schemas s
<span style="white-space:pre">		</span>JOIN sys.tables t
<span style="white-space:pre">			</span>ON s.schema_id = t.schema_id
<span style="white-space:pre">		</span>WHERE s.name = 'dbo'
<span style="white-space:pre">			</span>AND t.name = 'OurIFTest'
<span style="white-space:pre">			</span>)
<span style="white-space:pre">	</span>DROP TABLE OurIFTest;

IF NOT EXISTS(
<span style="white-space:pre">	</span>SELECT s.name AS SchemaName, t.name AS TableName
<span style="white-space:pre">		</span>FROM sys.schemas s
<span style="white-space:pre">		</span>JOIN sys.tables t
<span style="white-space:pre">			</span>ON s.schema_id = t.schema_id
<span style="white-space:pre">		</span>WHERE s.name = 'dbo'
<span style="white-space:pre">			</span>AND t.name = 'OurTFTest'
<span style="white-space:pre">			</span>)
<span style="white-space:pre">	</span>BEGIN<span style="white-space:pre">			</span>//执行块开始
<span style="white-space:pre">		</span>PRINT 'Table dbo.OurIFTest not found.';
<span style="white-space:pre">		</span>PRINT 'CREATING:Table dbo.OurIFTest';
<span style="white-space:pre">		</span>CREATE TABLE OurIFTest(
<span style="white-space:pre">			</span>Coll int PRIMARY KEY
<span style="white-space:pre">			</span>);
<span style="white-space:pre">	</span>END
<span style="white-space:pre">	</span>ELSE
<span style="white-space:pre">		</span>PRINT 'WARNING:Skipping CREATE as table already exists';

//CASE
Use yk_db1;
GO

SELECT TOP 10 SalesOrderID,
<span style="white-space:pre">		</span>SalesOrderID % 10 AS 'Last Digit',
<span style="white-space:pre">		</span>Position = CASE SalesOrderID % 10
<span style="white-space:pre">				</span>WHEN 1 THEN 'First'
<span style="white-space:pre">				</span>WHEN 2 THEN 'Second'
<span style="white-space:pre">				</span>WHEN 3 THEN 'Third'
<span style="white-space:pre">				</span>WHEN 4 THEN 'Fourth'
<span style="white-space:pre">				</span>ELSE 'Somethin Else'
<span style="white-space:pre">			</span>   END
FROM Sales.SalesOrderHeader;

DECLARE @Markup money;
DECLARE @Multiplier money;

SELECT @Marup = .10;
SELECT @Multiplier = @Markup + 1;

SELECT TOP 10 ProductID,Name ,ListPrice,
<span style="white-space:pre">	</span>ListPrice * @Multiplier AS "Marked Up Price",
<span style="white-space:pre">		</span>"New Price" = CASE WHEN FLOOR(ListPrice * @Multiplier + .24) ---------FLOOR<span style="font-family: Arial, Helvetica, sans-serif;">向下取整
</span><span style="font-family: Arial, Helvetica, sans-serif;"><span style="white-space:pre">								</span>> FLOOR(ListPrice * @Multiplier)</span><span style="font-family: Arial, Helvetica, sans-serif;">					</span>
<span style="white-space: pre;">					</span>THEN FLOOR(ListPrice * @Multiplier) +.95
<span style="font-family: Arial, Helvetica, sans-serif;">							</span><span style="font-family: Arial, Helvetica, sans-serif;">   WHEN FLOOR(ListPrice * @Multiplier + .5)
</span><span style="font-family: Arial, Helvetica, sans-serif;">								</span><span style="font-family: Arial, Helvetica, sans-serif;">> FLOOR(ListPrice * @Multiplier)
</span><span><span style="font-family:Arial, Helvetica, sans-serif;">	<span style="white-space:pre">				</span></span></span><span style="font-family: Arial, Helvetica, sans-serif;">					</span><span style="font-family: Arial, Helvetica, sans-serif;">THEN FLOOR(ListPrice * @Multiplier) + .75
</span><span><span style="font-family:Arial, Helvetica, sans-serif;">	<span style="white-space:pre">		</span></span></span><span style="font-family: Arial, Helvetica, sans-serif;">				</span><span style="font-family: Arial, Helvetica, sans-serif;">   ELSE FLOOR(ListPrice * @Multiplier) + .49
</span><span><span style="font-family:Arial, Helvetica, sans-serif;">	<span style="white-space:pre">			</span></span></span><span style="font-family: Arial, Helvetica, sans-serif;">			</span><span style="font-family: Arial, Helvetica, sans-serif;">      END
</span><span style="font-family: Arial, Helvetica, sans-serif;">FROM Prodection.Prcduct
</span><span style="font-family: Arial, Helvetica, sans-serif;">WHERE ProductID % 10 = 0
</span><span style="font-family: Arial, Helvetica, sans-serif;">ORDER BY ProductID DESC;</span>