十年经验总结出的 5 项必备 SQL 技能
在我十年的 SQL 使用经验中，我磨练出了几项关键技能，这些技能能够显著提升数据库管理和数据操作能力。以下是关于这些技能的详细教程，并附有实际示例。
练习设置
--Your Preparation
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),
);
--Create customers
INSERT INTO Customers (LastName, FirstName, DOB) VALUES
('Au Yeung', 'David', '19801231')
, ('Chan', 'Peter', '19820115')
--Create products
INSERT INTO Products (ProductName, ProductCode, AvailableQuantity) VALUES
('Android Phone', 'A0001', 100)
, ('iPhone', 'I0001', 100)
--David bought 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 查找无订单客户
一项常见的任务是识别尚未下单的客户。这可以通过使用 LEFT JOIN 并结合 NULL 值检查来高效完成。
例子：
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）
插入新记录时，尤其是在批量操作中，确保不出现重复记录至关重要。使用 NOT EXISTS 语句可以有效地防止这种情况发生。
例子：
IF NOT EXISTS (SELECT 1 FROM Products WHERE ProductName = 'iPhone')
INSERT INTO Products (ProductName, ProductCode) VALUES ('iPhone', 'I0001')
ELSE
PRINT 'Duplicate Product Name!';
该查询会在尝试插入产品之前检查该产品是否已存在，从而维护数据完整性。
技巧三：利用临时表提高可读性
使用临时表可以简化复杂的查询，尤其是在处理子查询时。这有助于提高 SQL 代码的可读性和可维护性。
例子：
SELECT ProductUID
INTO #BestSeller
FROM OrderItems
WHERE IsDeleted = 0
GROUP BY ProductUID
HAVING SUM(Quantity) > 5;
SELECT * FROM Products WHERE ProductUID IN (SELECT * FROM #BestSeller);
DROP TABLE IF EXISTS #BestSeller;
在这里，我们创建一个临时表来保存畅销商品的订单 ID，使后续查询更加清晰。
技能四：利用公共表表达式 (CTE) 进行顺序查询
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 如何简化复杂的逻辑。
技能五：利用事务维护数据完整性
执行更新操作时，尤其是那些可能影响大量数据的操作，将操作封装在事务中至关重要。这种做法可以通过提交或回滚更改来确保数据完整性。
例子：
BEGIN TRAN;
UPDATE Products
SET AvailableQuantity = 0
WHERE ProductCode = 'I0001'
AND IsDeleted = 0;
-- Check the results before COMMIT
SELECT * FROM Products WHERE ProductCode = 'I0001';
-- Uncomment to commit or rollback
-- COMMIT;
-- ROLLBACK;
此交易可确保只有在您对结果满意的情况下才会应用更新，从而有助于避免意外后果。
结论
这五项技能——使用 LEFT JOIN 查找不匹配的记录、使用 NOT EXISTS 防止重复、使用临时表提高查询可读性、使用 CTE 处理复杂查询以及使用事务确保数据完整性——在 SQL 中至关重要。掌握这些技巧可以显著提高数据库管理的效率和效果。
欢迎在下方评论区分享您的经验或提出问题！
文章来源：https://dev.to/auyeungdavid_2847435260/5-essential-sql-skills-learned-from-a-decade-of-experience-5fek