T-SQL Là Gì Hướng Dẫn Đầy Đủ Về Ngôn Ngữ Truy vấn SQL Server

15/06/2023

931

T-SQL Là Gì Hướng Dẫn Đầy Đủ Về Ngôn Ngữ Truy vấn SQL Server

Giới thiệu

T-SQL (Transact-SQL) là một ngôn ngữ truy vấn được sử dụng rộng rãi trong việc quản lý cơ sở dữ liệu và thực hiện các thao tác truy vấn trên hệ quản trị cơ sở dữ liệu SQL Server. Trong bài viết này, chúng ta sẽ đi vào chi tiết về T-SQL, khám phá các tính năng và cú pháp của nó, và hiểu rõ hơn về vai trò quan trọng của T-SQL trong việc làm việc với SQL Server.

Mục lục

T-SQL Là Gì?
- 1.1 T-SQL Được Sử Dụng Như Thế Nào Trong SQL Server?
- 1.2 T-SQL So Với SQL Tiêu Chuẩn
Cú Pháp Cơ Bản của T-SQL
- 2.1 Câu Truy Vấn SELECT
- 2.2 Cú Pháp INSERT INTO
- 2.3 Câu Lệnh UPDATE
- 2.4 Cú Pháp DELETE FROM
T-SQL Functions
- 3.1 T-SQL Built-in Functions
- 3.2 Tạo Custom Functions trong T-SQL
T-SQL Joins và Relationships
- 4.1 Inner Join
- 4.2 Outer Join (Left, Right, Full)
- 4.3 Self-Join
T-SQL Stored Procedures và Triggers
- 5.1 Lợi Ích của Stored Procedures
- 5.2 Tạo Stored Procedures trong T-SQL
- 5.3 Tạo Triggers trong T-SQL
T-SQL Indexes và Performance Optimization
- 6.1 Tìm Hiểu về Indexes
- 6.2 Cách Sử Dụng Indexes để Tối Ưu Hóa Hiệu Năng
- 6.3 Phân Trang dữ liệu trong T-SQL
T-SQL Error Handling và Transactions
- 7.1 Xử Lý Lỗi Trong T-SQL
- 7.2 Giao Dịch Trong T-SQL
T-SQL Security và Permissions
- 8.1 Quản Lý Quyền Truy Cập Với SQL Server Logins
- 8.2 Phân Quyền Trên Đối Tượng trong SQL Server
T-SQL XML và JSON
- 9.1 Lưu Trữ và Truy Xuất Dữ Liệu XML trong SQL Server
- 9.2 Làm Việc với Dữ Liệu JSON trong SQL Server
T-SQL Tips và Tricks
- 10.1 Sử Dụng Common Table Expressions (CTE)
- 10.2 Dynamic SQL trong T-SQL
- 10.3 Sử Dụng Temporary Tables
T-SQL Performance Monitoring và Optimization
- 11.1 Xem Thống Kê Hiệu Năng Với Dynamic Management Views (DMV)
- 11.2 Sử Dụng Execution Plans để Tối Ưu Hóa Câu Truy Vấn

T-SQL là gì?

T-SQL (Transact-SQL) là ngôn ngữ truy vấn mạnh mẽ dùng để làm việc với SQL Server, cho phép thực hiện các tác vụ như truy vấn dữ liệu, thêm, sửa, xóa dữ liệu, tạo và quản lý các đối tượng trong cơ sở dữ liệu như bảng, view, stored procedure, trigger, function, và nhiều hơn nữa.
T-SQL được phát triển bởi Microsoft và là một phần không thể thiếu trong việc phát triển ứng dụng và quản lý cơ sở dữ liệu trên SQL Server.

1. T-SQL Là Gì?

1.1 T-SQL Được Sử Dụng Như Thế Nào Trong SQL Server?

T-SQL là ngôn ngữ truy vấn chính được sử dụng trong SQL Server để thực hiện các hoạt động như truy xuất dữ liệu từ bảng, thêm, sửa, xóa dữ liệu, tạo và quản lý các đối tượng trong cơ sở dữ liệu. Với T-SQL, bạn có thể truy vấn cơ sở dữ liệu, tạo ra câu lệnh điều khiển logic, xử lý lỗi, và nhiều hơn nữa. Nó cung cấp một cú pháp linh hoạt và mạnh mẽ, cho phép bạn thực hiện các tác vụ phức tạp và tùy chỉnh theo nhu cầu của mình.

1.2 T-SQL So Với SQL Tiêu Chuẩn

T-SQL là một phần của SQL tiêu chuẩn và được Microsoft bổ sung thêm nhiều tính năng và cú pháp riêng. Mặc dù T-SQL có thể chạy trên hầu hết các hệ quản trị cơ sở dữ liệu khác, nhưng một số tính năng đặc biệt chỉ có sẵn trong SQL Server. Ví dụ, T-SQL hỗ trợ các stored procedure, trigger và view, cung cấp tính năng xử lý lỗi và transaction mạnh mẽ, và có thể tương tác tốt với các tính năng của SQL Server như Full-Text Search, XML và JSON.

2. Cú Pháp Cơ Bản của T-SQL

2.1 Câu Truy Vấn SELECT

Câu truy vấn SELECT được sử dụng để truy xuất dữ liệu từ bảng trong cơ sở dữ liệu. Với cú pháp SELECT, bạn có thể lựa chọn các cột cần hiển thị, áp dụng điều kiện lọc dữ liệu, sắp xếp kết quả, và thậm chí thực hiện các phép tính trên dữ liệu trước khi trả về kết quả. Dưới đây là một ví dụ về câu truy vấn SELECT đơn giản:

SELECT * FROM Customers;

Trong ví dụ trên, chúng ta truy xuất tất cả các dòng và cột từ bảng “Customers”.

2.2 Cú Pháp INSERT INTO

Câu lệnh INSERT INTO được sử dụng để thêm dữ liệu mới vào một bảng. Bạn cần chỉ định tên bảng và các giá trị cần chèn. Dưới đây là một ví dụ về cú pháp INSERT INTO:

INSERT INTO Customers (Name, Email) VALUES ('John Doe', 'john.doe@example.com');

Trong ví dụ trên, chúng ta thêm một khách hàng mới vào bảng “Customers” với các giá trị tương ứng cho cột “Name” và “Email”.

2.3 Câu Lệnh UPDATE

Câu lệnh UPDATE được sử dụng để cập nhật dữ liệu trong bảng. Bạn cần chỉ định tên bảng, cột cần cập nhật, và giá trị mới. Dưới đây là một ví dụ về cú pháp UPDATE:

UPDATE Customers SET Email = 'new-email@example.com' WHERE CustomerID = 1;

Trong ví dụ trên, chúng ta cập nhật giá trị của cột “Email” thành”new-email@example.com” cho khách hàng có CustomerID = 1 trong bảng “Customers”.

2.4 Cú Pháp DELETE FROM

Câu lệnh DELETE FROM được sử dụng để xóa dữ liệu từ bảng. Bạn cần chỉ định tên bảng và điều kiện để xác định dòng cần xóa. Dưới đây là một ví dụ về cú pháp DELETE FROM:

DELETE FROM Customers WHERE CustomerID = 1;

Trong ví dụ trên, chúng ta xóa khách hàng có CustomerID = 1 khỏi bảng “Customers”.

3. T-SQL Functions

3.1 T-SQL Built-in Functions

T-SQL cung cấp nhiều hàm tích hợp sẵn (built-in functions) để thực hiện các phép tính và xử lý dữ liệu. Ví dụ, hàm SUM được sử dụng để tính tổng các giá trị trong một cột, hàm LEN được sử dụng để đếm số ký tự trong một chuỗi, và hàm GETDATE được sử dụng để lấy thời gian hiện tại. Dưới đây là một ví dụ về sử dụng hàm tích hợp sẵn trong T-SQL:

SELECT SUM(Price) AS TotalPrice FROM Products;

Trong ví dụ trên, chúng ta tính tổng giá trị của cột “Price” trong bảng “Products” và đặt kết quả vào cột mới có tên “TotalPrice”.

3.2 Tạo Custom Functions trong T-SQL

Ngoài các hàm tích hợp sẵn, bạn cũng có thể tạo ra các hàm tùy chỉnh (custom functions) trong T-SQL. Hàm tùy chỉnh giúp tái sử dụng mã logic phức tạp và thực hiện các phép tính đặc biệt theo yêu cầu. Để tạo một custom function, bạn cần sử dụng câu lệnh CREATE FUNCTION và xác định tên và cấu trúc của hàm. Dưới đây là một ví dụ về tạo custom function trong T-SQL:

CREATE FUNCTION CalculateDiscount (@price decimal(10,2), @discount decimal(5,2))
RETURNS decimal(10,2)
AS
BEGIN
    DECLARE @discountedPrice decimal(10,2);
    SET @discountedPrice = @price - (@price * @discount);
    RETURN @discountedPrice;
END;

Trong ví dụ trên, chúng ta tạo một custom function có tên “CalculateDiscount” nhận vào hai tham số là “price” và “discount”. Hàm này tính toán giá trị được giảm giá dựa trên giá và tỷ lệ chiết khấu, và trả về kết quả.

4. T-SQL Joins và Relationships

4.1 Inner Join

Inner Join là một loại join trong T-SQL được sử dụng để kết hợp dữ liệu từ hai hoặc nhiều bảng dựa trên một điều kiện liên kết. Inner Join lấy các dòng dữ liệu từ cả hai bảng mà thoả mãn điều kiện liên kết. Dưới đây là một ví dụ về sử dụng Inner Join:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Trong ví dụ trên, chúng ta kết hợp bảng “Orders” và “Customers” thông qua cột “CustomerID”, và trả về OrderID và CustomerName trong kết quả.

4.2 Outer Join (Left, Right, Full)

Outer Join là một loại join trong T-SQL cho phép lấy tất cả các dòng từ một bảng (outer table) và kết hợp với các dòng tương ứng trong bảng khác (inner table) dựa trên một điều kiện liên kết. Có ba loại Outer Join: Left Join, Right Join và Full Join.

Left Join lấy tất cả các dòng từ bảng bên trái (left table) và kết hợp với các dòng tương ứng từ bảng bên phải (right table), nếu có. Nếu không có dòng tương ứng từ bảng bên phải, thì các giá trị của các cột từ bảng bên phải sẽ là NULL.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Trong ví dụ trên, chúng ta lấy tất cả các khách hàng từ bảng “Customers” và kết hợp với các đơn hàng từ bảng “Orders”, và trả về CustomerName và OrderID. Kết quả sẽ chứa tất cả các khách hàng, bất kể có đơn hàng hay không.

Right Join tương tự như Left Join, nhưng nó lấy tất cả các dòng từ bảng bên phải (right table) và kết hợp với các dòng tương ứng từ bảng bên trái (left table).

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Trong ví dụ trên, chúng ta lấy tất cả các đơn hàng từ bảng “Orders” và kết hợp với các khách hàng từ bảng “Customers”, và trả về CustomerName và OrderID. Kết quả sẽ chứa tất cả các đơn hàng, bất kể có khách hàng hay không.

Full Join lấy tất cả các dòng từ cả hai bảng (left table và right table) và kết hợp chúng dựa trên điều kiện liên kết. Nếu không có dòng tương ứng trong một bảng, thì các giá trị của các cột trong bảng đó sẽ là NULL.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Trong ví dụ trên, chúng ta lấy tất cả các khách hàng từ bảng “Customers” và kết hợp với tất cả các đơn hàng từ bảng “Orders”, và trả về CustomerName và OrderID. Kết quả sẽ chứa tất cả các khách hàng và tất cả các đơn hàng, bất kể có dòng tương ứng hay không.

4.3 Self-Join

Self-Join là một loại join trong T-SQL cho phép kết hợp dữ liệu từ cùng một bảng. Self-Join được sử dụng khi bạn muốn so sánh dữ liệu từ các dòng khác nhau trong cùng một bảng. Để thực hiện Self-Join, bạn cần sử dụng các cấu trúc bí danh (alias) để xác định các bảng con của bảng gốc.

SELECT e.EmployeeName, m.EmployeeName AS ManagerName
FROM Employees e
INNER JOIN Employees m ON e.ManagerID = m.EmployeeID;

Trong ví dụ trên, chúng ta kết hợp bảng “Employees” với chính nó thông qua cột “ManagerID”, và trả về tên của nhân viên và tên của quản lý tương ứng.

5. T-SQL Stored Procedures và Triggers

5.1 Lợi Ích của Stored Procedures

Stored Procedure là một khối mã T-SQL được lưu trữ trong cơ sở dữ liệu và có thể được gọi và thực thitừ các ứng dụng hoặc các câu lệnh truy vấn khác. Lợi ích của Stored Procedure bao gồm:

Tái sử dụng mã logic: Stored Procedure cho phép bạn lưu trữ và tái sử dụng mã logic phức tạp một cách dễ dàng. Thay vì viết lại mã lệnh nhiều lần, bạn có thể gọi Stored Procedure để thực hiện công việc đã được định nghĩa trước đó.
Tăng hiệu suất: Stored Procedure có thể được biên dịch và lưu trữ trong bộ nhớ cache của cơ sở dữ liệu, giúp cải thiện hiệu suất truy vấn. Khi được gọi nhiều lần, Stored Procedure không cần phải biên dịch lại từ đầu mà chỉ cần được thực thi từ bộ nhớ cache.
Quản lý bảo mật: Stored Procedure cho phép bạn kiểm soát quyền truy cập vào cơ sở dữ liệu. Bạn có thể cung cấp quyền truy cập cho người dùng chỉ để thực thi Stored Procedure mà không cần cung cấp quyền truy cập trực tiếp vào các bảng dữ liệu.

5.2 Tạo Stored Procedure

Để tạo Stored Procedure trong SQL Server, bạn cần sử dụng câu lệnh CREATE PROCEDURE và xác định tên của Stored Procedure, các tham số (nếu có), và mã logic trong khối BEGIN…END. Dưới đây là một ví dụ về tạo Stored Procedure:

CREATE PROCEDURE GetCustomerOrders
    @CustomerID int
AS
BEGIN
    SELECT *
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;

Trong ví dụ trên, chúng ta tạo một Stored Procedure có tên “GetCustomerOrders” nhận vào một tham số là “CustomerID”. Mã logic bên trong Stored Procedure sẽ truy xuất các đơn hàng từ bảng “Orders” dựa trên CustomerID được cung cấp.

5.3 Lợi Ích của Triggers

Trigger là một đối tượng trong SQL Server cho phép bạn kích hoạt và thực thi tự động các hành động hoặc câu lệnh khi có sự kiện xảy ra trên bảng hoặc cơ sở dữ liệu. Lợi ích của Triggers bao gồm:

Tuân thủ logic doanh nghiệp: Trigger cho phép bạn áp dụng các quy tắc logic doanh nghiệp vào cơ sở dữ liệu. Bạn có thể xác định các hành động tự động để thay đổi dữ liệu khi có sự thay đổi trong bảng hoặc cơ sở dữ liệu.
Bảo vệ dữ liệu: Trigger cho phép kiểm soát và giám sát các thay đổi dữ liệu. Bạn có thể xác định các ràng buộc và kiểm tra để đảm bảo tính toàn vẹn của dữ liệu.
Ghi lại lịch sử dữ liệu: Trigger cung cấp khả năng ghi lại lịch sử dữ liệu. Bằng cách kích hoạt các hành động tự động khi có thay đổi, bạn có thể lưu trữ thông tin về các sự kiện và các phiên bản trước của dữ liệu.

5.4 Tạo Trigger

Để tạo Trigger trong SQL Server, bạn cần sử dụng câu lệnh CREATE TRIGGER và xác định tên của Trigger, loại Trigger (BEFORE hoặc AFTER), sự kiện (INSERT, UPDATE, DELETE) và mã logic trong khối BEGIN…END. Dưới đây là một ví dụ về tạo Trigger:

CREATE TRIGGER UpdateOrderDate
ON Orders
AFTER UPDATE
AS
BEGIN
    IF UPDATE(OrderDate)
    BEGIN
        INSERT INTO OrderHistory (OrderID, OldOrderDate, NewOrderDate, UpdatedDate)
        SELECT OrderID, OLD.OrderDate, NEW.OrderDate, GETDATE()
        FROM inserted;
    END
END;

Trong ví dụ trên, chúng ta tạo một Trigger có tên “UpdateOrderDate” cho bảng “Orders”. Trigger này được kích hoạt sau khi có sự cập nhật trên bảng. Trong mã logic của Trigger, chúng ta kiểm tra xem cột “OrderDate” có được cập nhật hay không bằng cách sử dụng “IF UPDATE(OrderDate)”. Nếu cột “OrderDate” được cập nhật, chúng ta thêm một bản ghi vào bảng “OrderHistory” để ghi lại thông tin về OrderID, OldOrderDate, NewOrderDate và ngày cập nhật.

Các câu lệnh trên đây cung cấp một hướng dẫn cơ bản về các khái niệm và công cụ quan trọng trong T-SQL. Có nhiều khía cạnh và tính năng khác trong T-SQL mà bạn có thể khám phá.

Đặng Lê Nam