T-SQL 片段收藏

 

存储过程

CREATE PROCEDURE spInsertOrUpdateProduct
--有则更新，否则插入
    @ProductName NVARCHAR(50) ,
    @ProductNumber NVARCHAR(25) ,
    @StdCost MONEY
AS
    IF EXISTS ( SELECT  *
                FROM    Production.Product
                WHERE   ProductNumber = @ProductNumber )
        UPDATE  Production.Product
        SET     Name = @ProductName ,
                StandardCost = @StdCost
        WHERE   ProductNumber = @ProductNumber
    ELSE
        INSERT  INTO Production.Product
                ( Name ,
                  ProductNumber ,
                  StandardCost
                )
                SELECT  @ProductName ,
                        @ProductNumber ,
                        @StdCost
                    
GO

 触发器

CREATE TRIGGER tr_DelProduct ON Production.Product
    FOR DELETE
AS
    IF ( SELECT COUNT(*)
         FROM   sales.SalesOrderDetail
                INNER JOIN DELETED ON salesorderdetail.ProductID = DELETED.productid
       ) > 0
        BEGIN
            RAISERROR ('Cannot delete a product with sales orders',14,1)
            ROLLBACK TRANSACTION
            RETURN
        END

 自定义函数

CREATE FUNCTION dbo.fn_LastOfMonth ( @TheDate DATETIME )
RETURNS DATETIME
AS
    BEGIN
        DECLARE @FirstOfMonth DATETIME
        DECLARE @DaysInMonth INT
        DECLARE @RetDate DATETIME
        SET @FirstOfMonth = DATEADD(mm, DATEDIFF(mm, 0, @TheDate), 0)
        SET @DaysInMonth = DATEDIFF(d, @FirstOfMonth,
                                    DATEADD(m, 1, @FirstOfMonth))
        RETURN DATEADD(d,@DaysInMonth-1,@FirstOfMonth)
    END

 查看表的元数据，也就是数据的数据

SELECT  *
FROM    sys.columns
WHERE   [object_id] = OBJECT_ID('Production.Product')

不建议用上面的系统试图
可以用数据库视图

IF NOT EXISTS ( SELECT  *
                FROM    INFORMATION_SCHEMA.TABLES
                WHERE   TABLE_NAME = 'Address'
                        AND TABLE_NAME = 'Person' )
    DROP TABLE PERSON.Address
GO

 修改对象

ALTER PROCEDURE spInsertOrUpdateProduct
    @ProductName NVARCHAR(50) ,
    @ProductNumber NVARCHAR(25) ,
    @StdCost MONEY ,
    @ListPrice MONEY
AS
    BEGIN TRY
        BEGIN TRANSACTION
        IF EXISTS ( SELECT  *
                    FROM    Production.Product
                    WHERE   ProductNumber = @ProductName )
            UPDATE  Production.Product
            SET     Name = @ProductName ,
                    StandardCost = @StdCost
            WHERE   ProductNumber = @ProductNumber
        ELSE
            INSERT  INTO production.Product
                    ( Name ,
                      ProductNumber ,
                      StandardCost ,
                      ListPrice
                    )
                    SELECT  @ProductName ,
                            @ProductNumber ,
                            @StdCost ,
                            @ListPrice
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        DECLARE @ErrMsg VARCHAR(1000)
        SET @ErrMsg = ERROR_MESSAGE()
        ROLLBACK TRANSACTION
        RAISERROR(@ErrMsg,14,1)
        RETURN
    END CATCH

 添加和删除表列

ALTER TABLE Production.Product
ADD LeadTime SMALLINT NULL

ALTER TABLE production.Product
DROP COLUMN LeadTime

 WITH TIES用法，找出最贵的一个商品，但最贵的有好多个

SELECT TOP(1) WITH TIES * FROM Production.Product
ORDER BY ListPrice DESC 
--返回5条记录

 交叉表查询

CREATE TABLE [Test]
    (
      [id] [int] IDENTITY(1, 1)
                 NOT NULL ,
      [name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS
                            NULL ,
      [subject] [nvarchar](50) COLLATE Chinese_PRC_CI_AS
                               NULL ,
      [Source] [numeric](18, 0) NULL
    )
ON  [PRIMARY]
GO
INSERT  INTO [test]
        ( [name], [subject], [Source] )
VALUES  ( N'张三', N'语文', 60 ) ,
        ( N'李四', N'数学', 70 ),
        ( N'王五', N'英语', 80 ),
        ( N'王五', N'数学', 75 ),
        ( N'王五', N'语文', 57 ),
        ( N'李四', N'语文', 80 ),
        ( N'张三', N'英语', 100 );
Go 

SELECT  *
FROM    test

-------方法一----------
SELECT  name ,
        SUM(CASE subject
              WHEN '数学' THEN source
              ELSE 0
            END) AS '数学' ,
        SUM(CASE subject
              WHEN '英语' THEN source
              ELSE 0
            END) AS '英语' ,
        SUM(CASE subject
              WHEN '语文' THEN source
              ELSE 0
            END) AS '语文'
FROM    test
GROUP BY name
------方法二--------
DECLARE @sql VARCHAR(8000)
SET @sql = 'select name,'
SELECT  @sql = @sql + 'sum(case subject when ''' + subject + ''' 
then source else 0 end) as ''' + subject + ''','
FROM    ( SELECT DISTINCT
                    subject
          FROM      test
        ) AS a
SELECT  @sql = LEFT(@sql, LEN(@sql) - 1) + ' from test group by name'
EXEC(@sql)
go

 游标

USE Northwind
GO

DECLARE curProduct CURSOR
FOR
    SELECT  ProductID ,
            ProductName
    FROM    dbo.Products

DECLARE @ProdID INT
DECLARE @ProdName NVARCHAR(100)

OPEN curProduct
FETCH NEXT FROM curProduct INTO @ProdID, @ProdName
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @ProdName            
        FETCH NEXT FROM curProduct INTO @ProdID, @ProdName
    END

CLOSE curProduct
DEALLOCATE curProduct
---------------------------------------------

 CASE用法1

USE AdventureWorks2008
GO
SELECT  ProductID ,
        Name ,
        ListPrice ,
        ProductSubcategoryID ,
        CASE ProductSubcategoryID
          WHEN 1 THEN 'Mountain Bike'
          WHEN 2 THEN 'Road Bike'
          WHEN 3 THEN 'Touring Bike'
          WHEN NULL THEN 'Something Else'
          ELSE '(No SubCategory)'
        END AS SubCategory
FROM    Production.Product
----------------------------------------

 CASE用法2

SELECT  ProductID ,
        Name ,
        ListPrice ,
        ProductSubcategoryID ,
        SubCategory = CASE ProductSubcategoryID
                        WHEN 1 THEN 'Mountain Bike'
                        WHEN 2 THEN 'Road Bike'
                        WHEN 3 THEN 'Touring Bike'
                        WHEN NULL THEN 'Something Else'
                        ELSE '(No Subcategory)'
                      END
FROM    Production.Product
-----------------------------------------------------

 自动增长列操作1

SET IDENTITY_INSERT myTable ON --关闭自动增长
INSERT myTable(myID, myDescription)
VALUES(5,'This will work')
SET IDENTITY_INSERT myTable OFF --开启自动增长
--------------------------------------------------------------------

 表连接

http://blog.163.com/ji_1006/blog/static/10612341201310221261829/