Giới thiệu về MySQL CTE

Theo dõi Code Tu Tam trên

Kiến thức MySql Cơ Bản

25/06/2023 -

Post Views: 114

lượt xem

Rate this post

Trong bài viết này, chúng ta sẽ cùng học cách sử dụng MySQL CTE hoặc biểu thức bảng thông thường (common table expression) để xây dựng các truy vấn phức tạp theo cách dễ đọc hơn.

// Chú ý: MySQL đã giới thiệu biểu thức bảng chung hoặc tính năng CTE kể từ phiên bản 8.0, vì vậy bạn nên có MySQL 8.0+ để thực hành với các câu lệnh trong hướng dẫn này.

Mục lục

common table expression or CTE là gì ?

common table expression là một tập kết quả tạm thời được đặt tên chỉ tồn tại trong phạm vi thực thi của một câu lệnh SQL đơn lẻ, ví dụ: SELECT, INSERT, UPDATE, hoặc DELETE.

Tương tự như bảng dẫn xuất , CTE không được lưu trữ dưới dạng đối tượng và chỉ tồn tại trong khi thực hiện truy vấn.

Không giống như bảng dẫn xuất, CTE có thể tự tham chiếu ( CTE đệ quy ) hoặc có thể được tham chiếu nhiều lần trong cùng một truy vấn. Ngoài ra, CTE cung cấp khả năng đọc và hiệu suất tốt hơn so với bảng dẫn xuất.

Cú pháp MySQL CTE

Cấu trúc của CTE bao gồm tên, danh sách cột tùy chọn và truy vấn xác định CTE. Sau khi CTE được xác định, bạn có thể sử dụng nó làm dạng xem trong , , SELECT, INSERThoặc câu lệnh.UPDATEDELETECREATE VIEW

Sau đây minh họa cú pháp cơ bản của CTE:

WITH cte_name (column_list) AS (
    query
) 
SELECT * FROM cte_name;

Lưu ý rằng số cột trong tệp query phải giống với số cột trong tệp column_list. Nếu bạn bỏ qua column_list, CTE sẽ sử dụng danh sách cột của truy vấn xác định CTE

Các ví dụ MySQL CTE đơn giản

Chúng tôi sẽ sử dụng bảng customers từ cơ sở dữ liệu mẫu để trình diễn:

Ví dụ sau minh họa cách sử dụng CTE để truy vấn dữ liệu từ bảng customers trong cơ sở dữ liệu mẫu .

Lưu ý rằng ví dụ này chỉ nhằm mục đích trình diễn để giúp bạn dễ hiểu khái niệm CTE.

WITH customers_in_usa AS (
    SELECT 
        customerName, state
    FROM
        customers
    WHERE
        country = 'USA'
) SELECT 
    customerName
 FROM
    customers_in_usa
 WHERE
    state = 'CA'
 ORDER BY customerName;

Trong ví dụ này, tên của CTE là customers_in_usa, truy vấn xác định CTE trả về hai cột customerName và state. Do đó,CTE customers_in_usa trả lại tất cả các khách hàng ở Hoa Kỳ.

Sau khi xác định CTE customers_in_usa, chúng tôi đã tham chiếu nó trong SELECT để chọn những khách hàng ở California.

Xem ví dụ sau:

WITH topsales2003 AS (
    SELECT 
        salesRepEmployeeNumber employeeNumber,
        SUM(quantityOrdered * priceEach) sales
    FROM
        orders
            INNER JOIN
        orderdetails USING (orderNumber)
            INNER JOIN
        customers USING (customerNumber)
    WHERE
        YEAR(shippedDate) = 2003
            AND status = 'Shipped'
    GROUP BY salesRepEmployeeNumber
    ORDER BY sales DESC
    LIMIT 5
)
SELECT 
    employeeNumber, 
    firstName, 
    lastName, 
    sales
FROM
    employees
        JOIN
    topsales2003 USING (employeeNumber);

Trong ví dụ này, CTE trả về 5 đại diện bán hàng hàng đầu trong năm 2003. Sau đó, chúng tôi đã tham chiếu đến CTE topsales2003 để có thêm thông tin về đại diện bán hàng bao gồm tên và họ.

Một ví dụ MySQL CTE nâng cao hơn

Xem ví dụ sau:

WITH salesrep AS (
    SELECT 
        employeeNumber,
        CONCAT(firstName, ' ', lastName) AS salesrepName
    FROM
        employees
    WHERE
        jobTitle = 'Sales Rep'
),
customer_salesrep AS (
    SELECT 
        customerName, salesrepName
    FROM
        customers
            INNER JOIN
        salesrep ON employeeNumber = salesrepEmployeeNumber
)
SELECT 
    *
FROM
    customer_salesrep
ORDER BY customerName;

Trong ví dụ này, chúng tôi có hai CTE trong cùng một truy vấn. CTE đầu tiên ( salesrep) lấy các nhân viên có chức danh công việc là đại diện bán hàng. CTE thứ hai ( customer_salesrep) tham chiếu CTE đầu tiên trong mệnh đề INNER JOIN để biết đại diện bán hàng và khách hàng mà mỗi đại diện bán hàng phụ trách.

Sau khi có CTE thứ hai, chúng tôi truy vấn dữ liệu từ CTE đó bằng cách sử dụng một câu lệnh SELECT đơn giản với mệnh dề ORDER BY.

Cách sử dụng mệnh đề `WITH`

Có một số ngữ cảnh mà bạn có thể sử dụng mệnh đề WITH để tạo các biểu thức bảng chung:

Đầu tiên, một mệnh đề WITH có thể được sử dụng ở đầu các câu lệnh SELECT, UPDATE, và DELETE:

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

Thứ hai, một mệnh đề WITH có thể được sử dụng ở đầu truy vấn con hoặc truy vấn con bảng dẫn xuất:

SELECT ... WHERE id IN (WITH ... SELECT ...);

SELECT * FROM (WITH ... SELECT ...) AS derived_table;

Thứ ba, một mệnh đề WITH có thể được sử dụng ngay trước SELECT các mệnh đề bao gồm một mệnh đề SELECT:

CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...