5 kỹ năng SQL cần thiết đúc kết từ một thập kỷ kinh nghiệm
Bài viết này sẽ cung cấp một số kỹ năng quan trọng giúp nâng cao đáng kể việc quản lý cơ sở dữ liệu và thao tác dữ liệu, kèm ví dụ thực tế.
Thiết lập cho ví dụ trong bài viết này
--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
Và sau đây là các kỹ năng hữu ích các bạn có thể học được qua ví dụ bài tập trên:
Kỹ năng 1: Tìm kiếm khách hàng chưa đặt hàng bằng LEFT JOIN
Một nhiệm vụ phổ biến là xác định những khách hàng chưa đặt bất kỳ đơn hàng nào. Điều này có thể được thực hiện hiệu quả bằng cách sử dụng LEFT JOIN kết hợp với việc kiểm tra giá trị NULL.
VD:
SELECT c.*
FROM Customers c
LEFT JOIN Orders o ON o.CustomerUID = c.CustomerUID AND o.IsDeleted = 0
WHERE o.OrderUID IS NULL;
Câu truy vấn này truy xuất tất cả khách hàng không có đơn hàng liên quan, cho phép bạn nhắm mục tiêu họ cho các chiến lược tiếp thị hoặc tương tác.
Kỹ năng 2: Tránh trùng lặp với NOT EXISTS
Khi chèn các bản ghi mới, đặc biệt là trong các hoạt động hàng loạt, việc đảm bảo không xảy ra trùng lặp là rất quan trọng. Việc sử dụng NOT EXISTS có thể ngăn chặn điều này một cách hiệu quả.
VD:
IF NOT EXISTS (SELECT 1 FROM Products WHERE ProductName = 'iPhone')
INSERT INTO Products (ProductName, ProductCode) VALUES ('iPhone', 'I0001')
ELSE
PRINT 'Duplicate Product Name!';
Câu truy vấn này kiểm tra xem sản phẩm đã tồn tại chưa trước khi cố gắng chèn nó, do đó duy trì tính toàn vẹn dữ liệu.
Kỹ năng 3: Nâng cao khả năng đọc với bảng tạm thời
Việc sử dụng bảng tạm thời có thể đơn giản hóa các truy vấn phức tạp, đặc biệt là khi xử lý các truy vấn con. Điều này cải thiện khả năng đọc và bảo trì mã SQL của bạn.
VD:
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;
Ở đây, chúng ta tạo một bảng tạm thời để lưu trữ ID đơn hàng của các mặt hàng bán chạy nhất, giúp truy vấn tiếp theo rõ ràng hơn.
Kỹ năng 4: Sử dụng biểu thức bảng chung (CTE) cho các Truy vấn tuần tự
CTE có lợi cho việc tạo các truy vấn dễ đọc và có tổ chức hơn, đặc biệt là khi xử lý dữ liệu tuần tự hoặc các mối quan hệ phân cấp.
VD:
;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 này truy xuất đơn hàng gần đây nhất cho mỗi khách hàng, thể hiện cách CTE có thể đơn giản hóa logic phức tạp.
Kỹ năng 5: Sử dụng giao dịch để đảm bảo tính toàn vẹn dữ liệu
Khi thực hiện cập nhật, đặc biệt là những cập nhật có khả năng ảnh hưởng đến phần lớn dữ liệu của bạn, việc gói các hoạt động của bạn trong một giao dịch là điều cần thiết. Thực tiễn này cho phép bạn đảm bảo tính toàn vẹn dữ liệu bằng cách cam kết hoặc khôi phục các thay đổi.
VD:
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;
Giao dịch này đảm bảo rằng các bản cập nhật của bạn chỉ được áp dụng nếu bạn hài lòng với kết quả, giúp tránh những hậu quả không mong muốn.
Kết luận
Năm kỹ năng này — sử dụng LEFT JOIN để tìm các bản ghi không khớp, ngăn chặn trùng lặp với NOT EXISTS, nâng cao khả năng đọc truy vấn với các bảng tạm thời, sử dụng CTE cho các truy vấn phức tạp và đảm bảo tính toàn vẹn dữ liệu với các giao dịch — là vô giá trong SQL. Nắm vững các kỹ thuật này có thể cải thiện đáng kể hiệu quả và hiệu lực của bạn trong việc quản lý cơ sở dữ liệu.
Cảm ơn các bạn đã theo dõi!
All rights reserved
