MySQL WITH: 공통 테이블 표현식(CTE)

MySQL WITH: 공통 테이블 표현식(CTE)

2022-06-03 last update

7 minutes reading MySQL MariaDB
공통 테이블 표현식(CTE)은 임시 결과 세트를 생성하는 데 사용되는 MySQL의 중요한 특성입니다.SELECT, INSERT, UPDATE 등 모든 SQL 문장에 사용할 수 있습니다. CTE를 사용하면 복잡한 조회를 간소화할 수 있습니다.쿼리를 실행할 때, 모든 쿼리의 결과 집합은 파생 테이블의 대상으로 저장됩니다.그러나 CTE는 직접 참조할 수 있습니다. 이는 CTE를 사용하여 동일한 질의를 여러 번 참조할 수 있음을 의미합니다.따라서 파생 테이블보다 CTE 성능이 우수합니다.WITH 자구는 하나의 CTE를 정의하는 데 사용되며, 이 자구를 사용하면 한 문장에 여러 개의 CTE를 정의할 수 있습니다.본고는 CTE를 조회에 응용하여 더욱 읽을 수 있고 조회의 성능을 향상시키는 방법을 설명하고자 한다.

CTE 사용의 이점:

  • 이것은 조회를 더욱 읽을 수 있게 한다.
  • 검색 성능을 향상시켰다.
  • 뷰의 대체용으로 사용할 수 있습니다.
  • 질의를 단순화하기 위해 CTE 링크를 만들 수 있습니다.
  • CTE를 사용하면 귀속 질의를 쉽게 수행할 수 있습니다.
  • 구문:


    WITH CTE-Name (column1,column2,… columnn) AS (
    Query
    )
    SELECT * FROM CTE-Name;
    여기에서 모든 SQL 문장을 검색, 선택, 업데이트, 삭제, 삽입 또는 생성으로 정의할 수 있습니다.WITH 자문에서 열 목록을 정의하는 경우 질의의 열 수는 WITH 자문에서 정의한 열 수와 같아야 합니다.

    선행 조건:


    8.0 이하의 MySQL 버전에서는 CTE 기능이 지원되지 않습니다.따라서 본문의 예시를 연습하기 전에 MySQL 8.0을 설치해야 합니다.다음 명령을 실행하여 현재 설치된 MySQL 버전을 확인할 수 있습니다.
    $ mysql -V
    출력 디스플레이 시스템에 MySQL 버전 8.0.19가 설치되어 있습니다.

    올바른 버전이 설치되어 있으면 mydb라는 데이터베이스를 만들고users와users\uprofile라는 두 개의 테이블을 만듭니다. 이 테이블은 CTE가 MySQL에서 사용하는 방법을 알아보기 위해 일부 데이터를 포함합니다.이러한 작업을 수행하려면 다음 SQL 문을 실행합니다.이 문장들은users와users\u프로필이라는 두 개의 관련 표를 만들 것입니다.다음은 INSERT 문구를 통해 두 테이블에 데이터를 삽입합니다.
    CREATE DATABASE mydb;
    USE mydb;
    
    CREATE TABLE users (
    username VARCHAR(50) PRIMARY KEY,
    password VARCHAR(50) NOT NULL,
    status VARCHAR(10) NOT NULL);
    
    CREATE TABLE users_profile (
    username VARCHAR(50) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    FOREIGN KEY (username) REFERENCES users(username) ON DELETE CASCADE);
    
    INSERT INTO users values
    ('admin' , '7856', 'Active'),
    ('staff' , '90802', 'Active'),
    ('manager' , '35462', 'Inactive');
    
    INSERT INTO users_profile values
    ('admin', 'Administrator' , 'Dhanmondi', '[email protected]' ) ,
    ('staff', 'Jakir Nayek' , 'Mirpur', '[email protected]' ),
    ('manager', 'Mehr Afroz' , 'Eskaton', '[email protected]' );

    간단한 CTE 사용:


    CTE\uusers\uprofile라는 아주 간단한 CTE를 만들었습니다. 이 중 with 자구에는 CTE 이름을 사용하여 필드 목록을 정의하지 않았습니다.users\uprofile 테이블에서 모든 데이터를 검색합니다.다음 SELECT 문장은 cte\U users\U profile cte에서 모든 기록을 읽는 데 사용됩니다.
    WITH cte_users_profile AS (
    SELECT * FROM users_profile
    )
    SELECT * FROM cte_users_profile;
    문장을 실행하면 다음 출력이 표시됩니다.

    열 목록을 사용하는 간단한 CTE:


    with 자문에서 CTE name을 사용하여 필드 목록을 정의하면 보다 구체적으로 CTE를 만들 수 있습니다.이 경우 CTE 이름으로 정의된 필드 이름은 with 자구의 SELECT 질의에 정의된 필드 이름과 같습니다.여기서 이름과 전자 메일 필드는 두 위치에서 모두 사용됩니다.
    WITH cte_users_profile(name, email) AS (
    SELECT name, email
    FROM users_profile
    )
    SELECT * FROM cte_users_profile;
    상기 문장을 실행하면 다음과 같은 출력이 나타납니다.

    WHERE 자구가 있는 간단한 CTE 사용:


    다른 SELECT 조회와 마찬가지로 CTE 문장에 WHERE 자구가 있는 SELECT 문장을 정의할 수 있습니다.users와 users\u 프로필 테이블에서 기록된 SELECT 조회를 검색합니다. 그 중에서 usersname 필드의 값은 이 두 테이블에 대해 모두 같고 username의 값은 "staff"가 아닙니다.
    WITH cte_users AS (
    SELECT users.username, users_profile.name, users_profile.address, users_profile.email
    FROM users, users_profile
    WHERE users.username = users_profile.username and users_profile.username <> 'staff'
    )
    SELECT name as Name , address as Address
    FROM cte_users;
    문장을 실행하면 다음 출력이 표시됩니다.

    간단한 CTE를 GROUP BY 자구와 함께 사용합니다.


    CTE에서 사용할 수 있는 질의에서 집계 함수를 사용할 수 있습니다.다음 CTE 구문은 SELECT query with COUNT() 함수의 사용법을 보여 줍니다.첫 번째 SELECT 문장은users표의 모든 기록을 표시하는 데 사용되고, 마지막 SELECT 문장은CTE의 출력을 표시하는 데 사용되며, 이 출력은users표에서 활성 상태인 사용자의 총수를 통계합니다.
    SELECT * FROM users;
    WITH cte_users AS (
    SELECT COUNT(*) as total
    FROM users
    WHERE status='Active' GROUP BY status
    )
    SELECT total as `Total Active Users`
    FROM cte_users;
    문장을 실행하면 다음 출력이 표시됩니다.

    단순 CTE 및 UNION 연산자 사용:


    다음 CTE 구문은 CTE 구문에서 UNION 연산자를 사용하는 것을 보여 줍니다.상태 값이 "Inactive"인 users 테이블의 username 값과 users\u profile 테이블의 username 추가 값을 출력합니다.
    WITH cte_users AS (
    SELECT users.username
    FROM users
    WHERE status = 'Inactive'
    UNION
    SELECT users_profile.username
    FROM users_profile
    )
    SELECT * FROM cte_users;
    문장을 실행하면 다음 출력이 표시됩니다.

    왼쪽 연결이 있는 간단한 CTE 사용:


    다음 CTE 구문은 CTE에서 LEFT JOIN을 사용하는 것을 보여 줍니다.출력은 users\uprofile 테이블의name와 이메일 필드의 값을 표시합니다. 방법은 users와users\uprofile 테이블 사이의 username 필드와 WHERE 조건을 기반으로 왼쪽 연결을 적용합니다. 이것은 users 테이블에서 상태 값이'Inactive'인 기록을 선별합니다.
    WITH cte_users AS (
    SELECT name, email
    FROM users_profile
    LEFT JOIN users
    ON users.username= users_profile.username WHERE users.status = 'Inactive'
    )
    SELECT * FROM cte_users;
    문장을 실행하면 다음 출력이 표시됩니다.

    결론:


    질의 성능을 향상시키고 빠른 질의 출력을 원한다면 CTE는 다른 MySQL 옵션보다 더 좋은 옵션입니다.본고는 MySQL 사용자들이 SELECT 조회에서 CTE의 사용을 쉽게 배울 수 있도록 도와줄 것이다.