SQL Server

Declare trong SQL Server là gì? Cách dùng Declare SQL Server như thế nào? Hãy cùng thuthuatmeohay.com tìm hiểu cách khai báo biến trong SQL Server như thế nào nhé!

SQL Server là một trong số kiến thức mà mọi lập trình viên đều cần biết. Thực tế, nó không hề phức tạp như vẻ bề ngoài. Nếu chịu khó tìm hiểu, bạn sẽ thấy nó rất dễ sử dụng.

Trong SQL Server, bạn sẽ thấy có đầy đủ khái niệm về kiểu dữ liệu, biến và khai báo biến giống như những ngôn ngữ lập trình khác. thuthuatmeohay.com sẽ cung cấp cho bạn những kiến thức cơ bản về SQL Server. Ở bài viết này, chúng ta hãy cùng nhau học về gán biến trong SQL hay khai báo biến SQL như thế nào nhé!

Biến (Variable) dùng để lưu trữ các giá trị tạm thời trong quá trình thực thi thuật toán.

Cú pháp khai báo biến trong SQL Server

Để khai báo biến trong SQL Server, ta sử dụng câu lệnh DECLARE, được biểu diễn như sau:

DECLARE @variable_name datatype [ = initial_value ],
 @variable_name datatype [ = initial_value ],
 ...;

Tham số:

• variable_name: tên gán cho biến.
• datatype: kiểu dữ liệu của biến.
• initial_value: giá trị mặc định gán cho biến (không bắt buộc).

Khai báo một biến trong SQL Server

Dùng DECLARE khai báo một biến bất kì

DECLARE @quantrimang VARCHAR(50);

Câu lệnh DECLARE này khai báo một biến có tên @quantrimang, với kiểu dữ liệu VARCHAR và độ dài 50 kí tự.

Sau đó bạn thay đổi giá trị biến @quantrimang bằng cách sử dụng câu lệnh SET.

SET @quantrimang = 'Hello world';

Tiếp theo hãy thử với kiểu dữ liệu INT:

DECLARE @site_value INT;

Sử dụng câu lệnh SET để gán giá trị cho biến @site_value

SET @site_value = 10;

Vậy biến @site_value ở đây được gán cho số nguyên 10.

Khai báo nhiều biến trong SQL Server

Cách sử dụng lệnh như sau:

DECLARE @quantrimang VARCHAR(50),
        @site_value INT;

Trong ví dụ này, chúng ta có hai biến được khai báo: biến @quantrimang với kiểu dữ liệu VARCHAR(50) và biến @site_value kiểu dữ liệu INT.

Khai báo biến với giá trị mặc định trong SQL Server

Trong SQL Server, ta có thể gán giá trị mặc định cho biến ngay lúc khai báo.

DECLARE @quantrimang VARCHAR(50) = 'Hello world';

Vậy ở đây biến @quantrimang với kiểu dữ liệu VARCHAR và độ dài 50 kí tự được gán mặc định cho giá trị ‘Hello world’.

Tương tự ta khai báo với kiểu dữ liệu INT:

DECLARE @site_value INT = 10;

Khai báo nhiều hơn một biến có gán giá trị ban đầu

Cách sử dụng lệnh như sau:

DECLARE @quantrimang VARCHAR(50) = 'Hello world';
@site_value INT = 10;

Hai biến @quantrimang và @site_value đã được khai báo ở chung một lệnh và được gán giá trị ban đầu mặc định.

Khai báo một biến của bảng type

Ví dụ sau tạo một biến table, chứa giá trị được xác định trong mệnh đề OUTPUT của lệnh UPDATE. Hai câu lệnh SELECT theo sau trả về các giá trị trong @MyTableVar và kết quả của thao tác cập nhật trong bảng Employee. Kết quả trong cột INSERTED.ModifiedDate khác với các giá trị trong cột ModifiedDate của bảng Employee. Điều này là do trình kích hoạt AFTER UPDATE, cập nhật giá trị của ngày sửa đổi thành ngày hiện tại, được xác định trên bảng Employee. Tuy nhiên, các cột được trả về từ OUTPUT phản ánh dữ liệu trước khi được kích hoạt.

Ví dụ:

USE AdventureWorks2012;
GO
DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

Khai báo một biến của bảng type với các chỉ mục nội tuyến

Ví dụ sau tạo một biến table với một chỉ mục nội tuyến được nhóm lại với nhau và hai chỉ mục inline không nhóm:

DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    PRIMARY KEY CLUSTERED (EmpID),
    UNIQUE NONCLUSTERED (EmpID),
    INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID)
);
GO

Truy vấn sau trả thông tin về các chỉ mục được tạo ở truy vấn trước.

SELECT *
FROM tempdb.sys.indexes
WHERE object_id < 0;
GO

Khai báo một biến của kiểu bảng do người dùng định nghĩa

Ví dụ sau tạo một tham số có giá trị bảng hoặc biến bảng tên @LocationTVP. Điều này yêu cầu một kiểu bảng do người dùng định nghĩa tên LocationTableType.

DECLARE @LocationTVP
AS LocationTableType;

Bài trước: LITERAL (Hằng) trong SQL Server

Bài tiếp: SEQUENCE trong SQL Server

Pivot SQL Server là gì? Cách dùng pivot trong SQL Server như thế nào? Hãy cùng thuthuatmeohay.com tìm hiểu nhé!

Nếu đang nghiên cứu về lập trình ứng dụng và quản lý cơ sở dữ liệu, ắt hẳn bạn đã từng nghe tới SQL Server. Đây là kiến thức cơ bản mà bạn cần nắm khi muốn phát triển trong ngành lập trình.

Học cách sử dụng SQL Server không khó. Tương tự như ngôn ngữ lập trình khác, nó cũng bao gồm nhiều toán tử khác nhau và hàm Pivot trong SQL Server nhất định bạn phải biết.

Bài viết dưới đây sẽ cung cấp cho bạn kiến thức cơ bản về cách dùng toán tử PIVOT in SQL Server. Toán tử PIVOT giống như các toán tử quan hệ mà cho phép chuyển đổi biểu thức có giá trị trong bảng vào bảng khác. Thực tế, cả toán tử PIVOT và UNPIVOT đều tạo báo cáo đa chiều, giúp kết hợp và so sánh một số lượng lớn dữ liệu thật nhanh.

Bạn có thể dùng toán tử PIVOT khi cần chuyển đổi biểu thức giá trị bảng. Nó tách các giá trị duy nhất từ một cột thành nhiều cột trong kết quả cuối cùng. Nó cũng tổng hợp các giá trị cột còn lại trong kết quả cuối cùng. Giờ hãy cùng nhau đi sâu vào tìm hiểu chi tiết hơn nhé!

Trong SQL Server (Transact-SQL), mệnh đề PIVOT cho phép phân tích bảng chéo (cross tabulation) chuyển dữ liệu từ bảng này sang bảng khác, tức là lấy kết quả tổng hợp rồi chuyển từ dòng thành cột.

Ví dụ tính tổng rồi chuyền hàng thành cột trong bảng dữ liệu

Cú pháp mệnh đề PIVOT

SELECT cot_dautien AS <bidanh_cot_dautien>,
[giatri_chuyen1], [giatri_chuyen2], … [giatri_chuyen_n]
FROM
(<bang_nguon>) AS <bidanh_bang_nguon>
PIVOT
(
ham_tong (<cot_tong>)
FOR <cot_chuyen>
IN ([giatri_chuyen1], [giatri_chuyen2], … [giatri_chuyen_n])
) AS <bidanh_bang_chuyen>;

Tên biến hoặc giá trị biến

cot_dautien: Cột hoặc biểu thức sẽ thành cột đầu tiên trong bảng mới sau khi chuyển.

bidanh_cot_dautien: Tên của cột đầu tiên trong bảng mới sau khi chuyển.

giatri_chuyen1, giatri_chuyen2, … giatri_chuyen_n: Danh sách các giá trị cần chuyển.

bang_nguon: Lệnh SELECT đưa dữ liệu nguồn (dữ liệu ban đầu) vào bảng mới.

bidanh_bang_nguon: Bí danh của bang_nguon

ham_tong: Hàm tính tổng như SUM trong SQL Server, COUNT, MIN, MAX hay AVG.

cot_tong: Cột hoặc biểu thức được dùng với ham_tong.

cot_chuyen: Cột chứa giá trị cần chuyển.

bidanh_bang_chuyen: Bí danh của bảng sau khi chuyển.

Mệnh đề PIVOT có thể dùng trong các phiên bản sau của SQL Server: SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

Để thực hiện theo các bước trong hướng dẫn, hãy xem phần DDL để tạo bảng và DML để tạo dữ liệu ở cuối bài viết này rồi thử chạy trên chính cơ sở dữ liệu của bạn.

Ví dụ với mệnh đề PIVOT

Ta có bảng nhanvien với các dữ liệu như dưới đây.

Chạy lệnh SQL dưới đây để tạo truy vấn chéo bằng mệnh đề PIVOT.

SELECT ‘TongLuong’ AS TongLuongTheoPhong,
[30], [45]
FROM
(SELECT id_phong, luong
FROM nhanvien) AS BangNguon
PIVOT
(
SUM(luong)
FOR id_phong IN ([30], [45])
) AS BangChuyen;

Kết quả trả về sẽ như dưới đây.

Ví dụ trên tạo một bảng sau khi đã chuyển dữ liệu, cho biết tổng lương của phòng có ID là 30 và phòng có ID là 45. Kết quả nằm trên 1 hàng với 2 cột, mỗi cột là 1 phòng.

Xác định cụ thể cột trong bảng mới của truy vấn chéo

Trước tiên cần xác định trường thông tin nào muốn đưa vào bảng chuyển. Ở ví dụ này là TongLuong làm cột đầu tiên, sau đó là 2 cột id_phong 30 và id_phong 45.

SELECT “TongLuong’ AS TongLuongTheoPhong,
[30], [45]

Xác định dữ liệu trong bảng nguồn

Tiếp theo là xác định lệnh SELECT sẽ trả về dữ liệu nguồn cho bảng mới.

Ở ví dụ này là id_phong và luong từ bảng nhanvien.

(SELECT id_phong, luong
FROM nhanvien) AS BangNguon

Cần chỉ ra bí danh cho truy vấn nguồn, trong ví dụ này là BangNguon.

Xác định hàm tính tổng

Hàm có thể dùng trong truy vấn chéo gồm SUM, COUNT, MIN, MAX và AVG. Ở ví dụ này là hàm tính tổng SUM.

PIVOT
(SUM(luong)

Xác định giá trị cần chuyển

Cuối cùng là giá trị cần chuyển để đưa vào kết quả. Đây sẽ là tiêu đề cột trong truy vấn chéo.

Ở ví dụ này, chúng ta chỉ cần trả về id_phòng 30 và 45. Các giá trị này sẽ là tên cột trong bảng mới. Cần nhớ là những giá trị này là danh sách có giới hạn của các giá trị id_phong và không nhất thiết phải chứa tất cả các giá trị.

FOR id_phong IN ([30], [45])

DDL/DML cho các ví dụ

Nếu có CSDL và muốn làm thử những ví dụ trong hướng dẫn dùng lệnh PIVOT trên, bạn sẽ cần có DDL/DML.

DDL – Data Definition Language là các lệnh tạo bảng (CREATE TABLE) để dùng trong ví dụ về mệnh đề PIVOT.

CREATE TABLE phong
( id_phong INT NOT NULL, 
ten_phong VARCHAR(50) NOT NULL, 
CONSTRAINT pk_phong PRIMARY KEY (id_phong)
) ;

CREATE TABLE nhanvien
( so_nhanvien INT NOT NULL, 
ho VARCHAR(50) NOT NULL, 
ten VARCHAR(50) NOT NULL, 
luong INT, 
id_phong INT,
CONSTRAINT pk_nhanvien PRIMARY KEY (so_nhanvien)
) ;

DML – Data Manipulation Language là các lệnh INSERT để tạo dữ liệu cần thiết cho bảng.

INSERT INTO phong
(id_phong, ten_phong)
VALUES 
(30, ‘Ketoan’);

INSERT INTO phong
(id_phong, ten_phong)
VALUES
(45, ‘Banhang’);

INSERT INTO nhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES 
(12009, ‘Nguyen’, ‘Huong’, 54000, 45);

INSERT INTO nhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES
(34974, ‘Pham’, ‘Hoa’, 80000, 45);

INSERT INTO nhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES
(34987, ‘Phan’, ‘Lan’, 42000, 45);

INSERT INTO nhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES 
45001, ‘Tran’, ‘Hue’, 57500, 30);

INSERT INTO nhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES 
(75623, ‘Vu’, ‘Hong’, 65000, 30);

Những lỗi thường gặp khi dùng PIVOT trong SQL Server

PIVOT không hợp nhất nhiều hàng

Hãy xét ví dụ bên dưới. Chúng ta sẽ triển khai cả PIVOT và UNPIVOT cho bảng này và so sánh kết quả của cả hai bảng.

SELECT Name,[Year] , Sales FROM
(
   SELECT [Year], Pankaj,Rahul,Sandeep FROM
   (SELECT Name, [Year] , Sales FROM Employee )Tab1
PIVOT
(
   SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2
)Tab
UNPIVOT
(
   Sales FOR Name IN (Pankaj,Rahul,Sandeep)
) AS TAb2

Kết quả:

Bạn có thể thấy hai bảng cho kết quả giống nhau.

Như bạn thấy pivot SQL Server là một mệnh đề hữu ích trong lập trình. Nó giúp người dùng chuyển đổi dữ liệu trong bảng dễ dàng. Hi vọng bài viết giúp bạn hiểu rõ hơn về cách dùng Pivot SQL Server.

Bài trước: Truy vấn con SUBQUERY trong SQL Server

Bài sau: Các kiểu dữ liệu trong SQL Server

Exists trong SQL Server là gì?Cách dùng NOT Exists trong SQL Server như thế nào? Hãy cùng thuthuatmeohay.com tìm hiểu nhé!

SQL Server là kiến thức mà mọi lập trình viên đều cần biết. Khi sử dụng SQL Server, bạn phải làm quen với rất nhiều hàm hay mệnh đề, Exists là một trong số đó.

Trong SQL Server (Transact-SQL) điều kiện EXISTS được dùng để kết hợp với truy vấn nội bộ (subquery). Điều kiện được đáp ứng nếu truy vấn nội bộ trả về ít nhất 1 hàng. Điều kiện này có thể dùng trong lệnh SELECT, INSERT, UPDATE hoặc DELETE.

Cú pháp mệnh đề EXISTS trong SQL Server

WHERE EXISTS ( subquery);

Tên biến hoặc giá trị biến

subquery

Subquery – truy vấn nội bộ là một lệnh SELECT. Nếu truy vấn này trả về ít nhất 1 bản ghi trong bộ kết quả thì mệnh đề EXISTS được đánh giá là đúng và điều kiện EXISTS được đáp ứng. Nếu truy vấn nội bộ không trả về bản ghi nào, mệnh đề EXISTS được đánh giá là sai và điều kiện EXISTS không được đáp ứng.

Lưu ý

Sử dụng điều kiện EXISTS không hiệu quả do truy vấn nội bộ RE-RUN (chạy lại) trên mỗi hàng trong bảng ở truy vấn ngoài. Có nhiều cách hiệu quả hơn mà không cần dùng điều kiện EXISTS.

Ví dụ – với lệnh SELECT

Lệnh SELECT dùng với điều kiện EXISTS như dưới đây.

SELECT * 
FROM nhanvien
WHERE EXISTS (SELECT * 
              FROM danhba
              WHERE nhanvien.ho = danhba.ho
              AND nhanvien.ten = danhba.ten);

Ví dụ này sẽ trả về tất cả các bản ghi từ bảng nhanvien khi có ít nhất 1 bảng ghi trong bảng danhba có họ và tên khớp với họ và tên trong bảng nhanvien.

Ví dụ – lệnh SELECT dùng NOT EXISTS

Điều kiện EXISTS có thể kết hợp với toán tử NOT.

SELECT * 
FROM nhanvien
WHERE NOT EXISTS (SELECT *
                  FROM danhba
                  WHERE nhanvien.ho = danhba.ho
                  AND nhanvien.ten = danhba.ten);

Kết quả trả về là tất cả các bản ghi trong bảng nhanvien nếu không có bản ghi họ và tên nào trong bảng danhba khớp với họ và tên trong bảng nhanvien.

Ví dụ – lệnh INSERT

Đây là ví dụ dùng lệnh INSERT với điều kiện EXISTS

INSERT INTO danhba
(danhba_id, danhba_ten)
SELECT nhacung_id, nhacung_ten
FROM nhacung
WHERE EXISTS (SELECT * 
              FROM donhang
              WHERE nhacung.nhacung_id = donhang.nhacung_id);

Ví dụ – lệnh UPDATE

Dưới đây là ví dụ lệnh UPDATE dùng điều kiện EXISTS.

UPDATE nhacung
SET nhacung_ten = (SELECT khachhang.ten
                   FROM khachhang
                   WHERE khachhang.khachhang_id = nhacung.nhacung_id)
WHERE EXISTS (SELECT khachhang.ten
              FROM khachhang
              WHERE khachhang.khachhang_id = nhacung.nhacung_id);

Ví dụ – lệnh DELETE

Lệnh DELETE cũng có thể dùng với điều kiện EXISTS như dưới đây.

DELETE FROM danhba
WHERE EXITS (SELECT * 
             FROM nhanvien
             WHERE nhanvien.ho = danhba.ho );

Cách dùng NOT với điều kiện EXIST trong SQL Server

Lệnh NOT được dùng để trích xuất những bản ghi điều kiện WHERE được cung cấp bởi người dùng là NOT TRUE hoặc FALSE.

Ví dụ này lấy STUDENT_NAME từ bảng STUDENT, cho điều kiện được đưa ra. Ví dụ: department.student_id=student.student_id là FALSE.

Kết quả:

Nếu thấy cả hai bảng đều rõ ràng, bạn sẽ biết cả hai tên được đưa ra, nằm trong STUDENT_ID không được nhắc tới ở bảng DEPARTMENT, vì thế, nó là kết quả.

Nếu không dùng lệnh NOT ở đây, nó sẽ cho kết quả là Neena và Johny.

Lưu ý: Dù STUDENT_ID cho STUDENT_NAME Kashish hiện nằm trong bảng DEPARTMENT. Nó chưa được hiển thị trong kết quả ở đây.

EXISTS vs. JOIN

Toán tử EXISTS SQL Server trả về TRUE hoặc FALSE, còn mệnh đề JOIN trả về các hàng từ bảng khác.

Bạn dùng toán tử EXISTS để kiểm tra xem liệu một truy vấn phụ có trả về bất kỳ hàng và chu kỳ ngắn nào đó ngay khi nó chạy. Mặt khác, bạn dùng JOIN để tập kết quả bằng cách kết hợp nó với các cột từ bảng liên quan.

Thực tế, bạn dùng EXISTS khi cần kiểm tra sự tồn tại của các hàng từ bảng liên quan mà không phải trả về dữ liệu từ chúng.

Ví dụ về EXISTS và IN

Lệnh sau dùng toán tử IN để tìm các đơn hàng của khách từ San Jose:

SELECT
    *
FROM
    sales.orders
WHERE
    customer_id IN (
        SELECT
            customer_id
        FROM
            sales.customers
        WHERE
            city = 'San Jose'
    )
ORDER BY
    customer_id,
    order_date;

Lệnh sau dùng toán tử EXISTS, trả về kết quả tương tự:

SELECT
    *
FROM
    sales.orders o
WHERE
    EXISTS (
        SELECT
            customer_id
        FROM
            sales.customers c
        WHERE
            o.customer_id = c.customer_id
        AND city = 'San Jose'
    )
ORDER BY
    o.customer_id,
    order_date;

Trên đây là những điều cơ bản bạn cần biết về EXISTS trong SQL Server. Đây là một lệnh điều kiện hữu ích mà bạn nhất định nên biết khi tìm hiểu về SQL Server. Hi vọng những kiến thức cơ bản kể trên sẽ giúp bạn lập trình cùng SQL Server hiệu quả hơn.

Bài trước: Lệnh TRUNCATE TABLE trong SQL Server

Bài sau: Mệnh đề GROUP BY trong SQL Server