工作中必备的五个SQL技巧

在我的SQL日常工作中，有几个重要的小技巧可以提升我的数据库管理和数据操作能力。下面我将分享这些技能的详细教程，并附带实际案例，希望能为大家带来启发。

数据准备

--首先准备一些表数据CREATETABLE Customers (
    CustomerUID         UNIQUEIDENTIFIER DEFAULT(NEWID())NOTNULL,
    CustomerNumber      BIGINTIDENTITY(1,1)NOTNULL,
    LastName            NVARCHAR(100)NOTNULL,
    FirstName           NVARCHAR(100)NOTNULL,
    DOB                 DATENOTNULL,
    IsDeleted           BITNOTNULLDEFAULT0,
    CreateBy            NVARCHAR(100)NOTNULLDEFAULT'SYSTEM',
    CreateDate          DATETIMENOTNULLDEFAULT GETDATE(),
    ModifyBy            NVARCHAR(100)NOTNULLDEFAULT'SYSTEM',
    ModifyDate          DATETIMENOTNULLDEFAULT GETDATE(),CHECK(YEAR(DOB)>=1900),PRIMARYKEY(CustomerUID));CREATETABLE Products (
    ProductUID          UNIQUEIDENTIFIER DEFAULT(NEWID())NOTNULL,
    ProductName         NVARCHAR(1000)NOTNULL,
    ProductCode         NVARCHAR(1000)NOTNULL,
    AvailableQuantity   INTNOTNULL,
    IsDeleted           BITNOTNULLDEFAULT0,
    CreateBy            NVARCHAR(100)NOTNULLDEFAULT'SYSTEM',
    CreateDate          DATETIMENOTNULLDEFAULT GETDATE(),
    ModifyBy            NVARCHAR(100)NOTNULLDEFAULT'SYSTEM',
    ModifyDate          DATETIMENOTNULLDEFAULT GETDATE(),CHECK(AvailableQuantity >=0),PRIMARYKEY(ProductUID));CREATETABLE Orders (
    OrderUID            UNIQUEIDENTIFIER DEFAULT(NEWID())NOTNULL,
    CustomerUID         UNIQUEIDENTIFIER,
    OrderNumber         NVARCHAR(1000)NOTNULL,
    OrderDate           DATETIMENOTNULL,
    IsDeleted           BITNOTNULLDEFAULT0,
    CreateBy            NVARCHAR(100)NOTNULLDEFAULT'SYSTEM',
    CreateDate          DATETIMENOTNULLDEFAULT GETDATE(),
    ModifyBy            NVARCHAR(100)NOTNULLDEFAULT'SYSTEM',
    ModifyDate          DATETIMENOTNULLDEFAULT GETDATE(),PRIMARYKEY(OrderUID),FOREIGNKEY(CustomerUID)REFERENCES Customers(CustomerUID));CREATETABLE OrderItems (
    OrderItemUID        UNIQUEIDENTIFIER DEFAULT(NEWID())NOTNULL,
    OrderUID            UNIQUEIDENTIFIER,
    ProductUID          UNIQUEIDENTIFIER,
    Quantity            INTNOTNULL,
    IsDeleted           BITNOTNULLDEFAULT0,
    CreateBy            NVARCHAR(100)NOTNULLDEFAULT'SYSTEM',
    CreateDate          DATETIMENOTNULLDEFAULT GETDATE(),
    ModifyBy            NVARCHAR(100)NOTNULLDEFAULT'SYSTEM',
    ModifyDate          DATETIMENOTNULLDEFAULT GETDATE(),PRIMARYKEY(OrderItemUID),FOREIGNKEY(OrderUID)REFERENCES Orders(OrderUID),FOREIGNKEY(ProductUID)REFERENCES Products(ProductUID),);--创建用户INSERTINTO Customers (LastName, FirstName, DOB)VALUES('Au Yeung','David','19801231'),('Chan','Peter','19820115')--创建产品INSERTINTO Products (ProductName, ProductCode, AvailableQuantity)VALUES('Android Phone','A0001',100),('iPhone','I0001',100)--David 买了 10 个 iPhoneINSERTINTO Orders (CustomerUID, OrderNumber, OrderDate)VALUES((SELECTTOP1 CustomerUID FROM Customers WHERE FirstName ='David'AND IsDeleted =0),'ORD0001', GETDATE())INSERTINTO OrderItems (OrderUID, ProductUID, Quantity)VALUES((SELECTTOP1 OrderUID FROM Orders WHERE OrderNumber ='ORD0001'AND IsDeleted =0),(SELECTTOP1 ProductUID FROM Products WHERE ProductCode ='I0001'AND IsDeleted =0),10)SELECT*FROM Customers
SELECT*FROM Products
SELECT*FROM Orders
SELECT*FROM OrderItems

技巧1：巧用LEFT JOIN找出无订单用户

在日常工作中，我们经常需要找出那些尚未下过任何订单的客户。通过结合LEFT JOIN和NULL检查，这项任务会变得很简单。

写法:

SELECT c.*FROM Customers c 
LEFTJOIN Orders o ON o.CustomerUID = c.CustomerUID AND o.IsDeleted =0WHERE o.OrderUID ISNULL;

这个查询能够快速找出所有没有关联订单的客户。

技巧2：避免不存在的重复

在批量插入新记录时，确保数据不重复至关重要。利用NOT EXISTS子句，我们可以有效预防重复记录的出现。

写法:

IFNOTEXISTS(SELECT1FROM Products WHERE ProductName ='iPhone')INSERTINTO Products (ProductName, ProductCode)VALUES('iPhone','I0001')ELSEPRINT'Duplicate Product Name!';

该查询在尝试插入产品之前检查产品是否已经存在，从而保持数据完整性。

这里提到的应用场景，在现实工作中极具实用价值。设想一下，面对大量的非结构化数据，需要先对这些信息进行梳理，以便于策划和执行各类专项促销活动。在这种情境下，我们往往会发现，为了确保数据的精准度，适当降低一些处理速度是可以接受的。

技巧3：用临时表格增强可读性

面对复杂的查询，尤其是那些涉及多个子查询的情况，使用临时表能够大大简化操作，同时提升SQL代码的可读性和可维护性。

写法:

SELECT ProductUID
INTO#BestSellerFROM OrderItems
WHERE IsDeleted =0GROUPBY ProductUID
HAVINGSUM(Quantity)>5;SELECT*FROM Products WHERE ProductUID IN(SELECT*FROM#BestSeller);DROPTABLEIFEXISTS#BestSeller;

这里，我们创建一个临时表来保存畅销商品的订单id，使后续查询更加清晰。

技巧4：利用公共表表达式(cte)进行顺序查询

CTE（公共表表达式）在创建可读性强、组织有序的查询方面表现突出，尤其适用于处理顺序数据或层次关系。

写法:

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

这个CTE检索每个客户的最新订单，体现了CTE在简化复杂逻辑方面的强大能力。

技巧5：使用事务实现数据完整性

当执行更新时，尤其是那些可能影响大部分数据的更新，将操作包装在事务中是非常重要的。这种做法允许通过提交或回滚更改来确保数据完整性。

写法:

BEGINTRAN;UPDATE Products
SET AvailableQuantity =0WHERE ProductCode ='I0001'AND IsDeleted =0;-- Check the results before COMMITSELECT*FROM Products WHERE ProductCode ='I0001';-- Uncomment to commit or rollback-- COMMIT;-- ROLLBACK;

此事务确保更新仅在对结果满意时应用，有助于避免意外后果。

结尾

这五项技能——左连接查找不匹配记录、预防重复数据、临时表提升查询可读性、CTE优化复杂查询以及事务保障数据完整性——在日常SQL中经常使用。