十年经验总结：五个必备的SQL技能

十年来我在做SQL工作时，磨练出了一些关键技能，极大地提升了数据库管理和数据操作的能力。这里有详细的教程，介绍这些技能，并附有实用的例子。

    --您的准备
    CREATE TABLE Customers (
        CustomerUID         UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
        CustomerNumber      BIGINT IDENTITY(1,1) NOT NULL,
        LastName            NVARCHAR(100)    NOT NULL,
        FirstName           NVARCHAR(100)    NOT NULL,
        DOB                 DATE             NOT NULL,
        IsDeleted           BIT              NOT NULL DEFAULT 0,
        CreateBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
        CreateDate          DATETIME         NOT NULL DEFAULT GETDATE(),
        ModifyBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
        ModifyDate          DATETIME         NOT NULL DEFAULT GETDATE(),  
        CHECK (YEAR(DOB) >= 1900),
        PRIMARY KEY (CustomerUID) 
    );

    CREATE TABLE Products (
        ProductUID          UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
        ProductName         NVARCHAR(1000)   NOT NULL,
        ProductCode         NVARCHAR(1000)   NOT NULL,
        AvailableQuantity   INT              NOT NULL,
        IsDeleted           BIT              NOT NULL DEFAULT 0,
        CreateBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
        CreateDate          DATETIME         NOT NULL DEFAULT GETDATE(),
        ModifyBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
        ModifyDate          DATETIME         NOT NULL DEFAULT GETDATE(),
        CHECK (AvailableQuantity >= 0),
        PRIMARY KEY (ProductUID)
    );

    CREATE TABLE Orders (
        OrderUID            UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
        CustomerUID         UNIQUEIDENTIFIER,
        OrderNumber         NVARCHAR(1000)   NOT NULL,
        OrderDate           DATETIME         NOT NULL,
        IsDeleted           BIT              NOT NULL DEFAULT 0,
        CreateBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
        CreateDate          DATETIME         NOT NULL DEFAULT GETDATE(),
        ModifyBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
        ModifyDate          DATETIME         NOT NULL DEFAULT GETDATE(),
        PRIMARY KEY (OrderUID),
        FOREIGN KEY (CustomerUID) REFERENCES Customers(CustomerUID)
    );

    CREATE TABLE OrderItems (
        OrderItemUID        UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
        OrderUID            UNIQUEIDENTIFIER,
        ProductUID          UNIQUEIDENTIFIER,
        Quantity            INT              NOT NULL,
        IsDeleted           BIT              NOT NULL DEFAULT 0,
        CreateBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
        CreateDate          DATETIME         NOT NULL DEFAULT GETDATE(),
        ModifyBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
        ModifyDate          DATETIME         NOT NULL DEFAULT GETDATE(),
        PRIMARY KEY (OrderItemUID),
        FOREIGN KEY (OrderUID) REFERENCES Orders(OrderUID),
        FOREIGN KEY (ProductUID) REFERENCES Products(ProductUID)
    );

    --创建客户
    INSERT INTO Customers (LastName, FirstName, DOB) VALUES
    ('Au Yeung', 'David', '19801231')
    , ('Chan', 'Peter', '19820115')

    --创建产品
    INSERT INTO Products (ProductName, ProductCode, AvailableQuantity) VALUES
    ('Android 设备', 'A0001', 100)
    , ('iPhone', 'I0001', 100)

    --David 购买了 10 部 iPhone
    INSERT INTO Orders (CustomerUID, OrderNumber, OrderDate) VALUES
    ((SELECT TOP 1 CustomerUID FROM Customers WHERE FirstName = 'David' AND IsDeleted = 0)
    , 'ORD0001'
    , GETDATE())

    INSERT INTO OrderItems (OrderUID, ProductUID, Quantity) VALUES
    ((SELECT TOP 1 OrderUID FROM Orders WHERE OrderNumber = 'ORD0001' AND IsDeleted = 0)
    , (SELECT TOP 1 ProductUID FROM Products WHERE ProductCode = 'I0001' AND IsDeleted = 0)
    , 10)

    SELECT * FROM Customers
    SELECT * FROM Products
    SELECT * FROM Orders
    SELECT * FROM OrderItems

点击这里进入全屏模式，点击这里退出全屏模式

一个常见的任务是找出没有任何订单的客户。这可以通过LEFT JOIN配合检查NULL值来高效地完成。（LEFT JOIN是一种数据库查询方法）

例如:

    SELECT c.*
    FROM Customers c 
    LEFT JOIN Orders o ON o.CustomerUID = c.CustomerUID AND o.IsDeleted = 0
    WHERE o.OrderUID IS NULL;
    -- 以下是查询没有订单的客户：  

切换到全屏模式, 退出全屏

此查询语句检索没有任何订单记录的客户，这样您可以针对他们制定营销策略或参与活动。

在插入新记录时，尤其是在批量操作的情况下，我们一定要避免重复出现。使用 NOT EXISTS 可以有效避免这种情况。

例子:，比如说

-- 检查产品表中是否存在 'iPhone'，如果不存在则插入新产品
IF NOT EXISTS (SELECT 1 FROM Products WHERE ProductName = 'iPhone') 
    INSERT INTO Products (ProductName, ProductCode) VALUES ('iPhone', 'I0001')
ELSE
    PRINT '产品名称重复了!';

全屏显示 退出全屏

这个查询语句在插入前检查产品是否已存在，从而确保数据的完整性。

技巧 3：通过临时表提升可读性

用临时表可以简化复杂的查询，特别是在涉及到子查询的时候。这可以让您的SQL代码更易读和更易维护。

例子:

    -- 将产品唯一标识符选择到名为#BestSeller的临时表中
    SELECT ProductUID
    INTO #BestSeller
    FROM OrderItems
    WHERE IsDeleted = 0
    GROUP BY ProductUID
    HAVING SUM(Quantity) > 5;

    -- 查询产品表中产品唯一标识符在#BestSeller临时表中的所有产品信息
    SELECT * FROM Products WHERE ProductUID IN (SELECT * FROM #BestSeller);

    -- 如果存在#BestSeller临时表，则删除
    DROP TABLE IF EXISTS #BestSeller;

进入全屏模式 退出全屏

这里，我们将创建一个临时表来存储畅销商品的订单ID，以便使后续查询更清晰。

CTE（常见表表达式）有助于使查询更易读、更整洁，尤其是在处理顺序数据或层级结构时。

例子:

    ;WITH cte AS (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY CustomerUID ORDER BY CreateDate DESC) AS rn
        FROM Orders
    )
    SELECT * 
    FROM cte
    WHERE rn = 1;

以下代码使用CTE（公用表表达式）来选择每个客户的最新订单。

点击此处进入全屏模式 点击此处退出全屏

这个CTE（公用表表达式）获取每位客户的最近一次订单，展示了CTE如何简化处理复杂信息的过程。

在进行更新时，尤其是可能影响大量数据的更新，将你的操作放在一个事务里是非常必要的。这样你可以通过提交或回滚来确保数据的完整性。

比如：

    BEGIN TRAN;

    UPDATE Products
    SET AvailableQuantity = 0
    WHERE ProductCode = 'I0001'
    AND IsDeleted = 0;

    -- 在提交前查看结果
    SELECT * FROM Products WHERE ProductCode = 'I0001';

    -- 取消注释以提交或回滚事务
    -- COMMIT;
    -- ROLLBACK;

全屏模式。退出全屏。

这项交易确保您在满意结果后再进行更新，从而避免不必要的麻烦。

这些五种技能——使用 LEFT JOIN 查找未匹配的记录，用 NOT EXISTS 避免重复，通过临时表增强查询的可读性，利用 CTE 来处理复杂的查询，以及通过事务保证数据完整性——在 SQL 中非常宝贵。掌握这些技术可以显著提高你在数据库管理中效率和效果。

欢迎，在下面的评论区分享你的经历或提出问题！