MYSQL 공통 테이블 표현식(CTE)

MySQL에서는 모든 명령문이나 쿼리가 임시 결과나 관계를 생성합니다. 공통 테이블 표현식 또는 CTE는 다음과 같이 사용됩니다. 임시 결과 세트의 이름을 지정하십시오. CREATE와 같이 특정 문의 실행 범위 내에 존재하는 끼워 넣다 , 선택하다 , 업데이트 , 삭제 , 등.

튜플 자바

CTE와 관련된 몇 가지 핵심 사항은 다음과 같습니다.

이는 다음을 사용하여 정의됩니다. 와 함께 절.
WITH 절을 사용하면 단일 쿼리에 둘 이상의 CTE를 지정할 수 있습니다.
CTE는 동일한 WITH 절의 일부인 다른 CTE를 참조할 수 있지만 해당 CTE는 더 일찍 정의되어야 합니다.
CTE의 실행 범위는 CTE가 사용되는 특정 문 내에 존재합니다.

MySQL CTE 구문

MySQL CTE의 구문에는 이름, 선택적 열 목록, 공통 테이블 표현식(CTE)을 정의하는 명령문/쿼리가 포함됩니다. CTE를 정의한 후 이를 SELECT, INSERT, UPDATE 및 DELETE 쿼리의 뷰로 사용할 수 있습니다.

다음은 CTE의 기본 구문입니다. MySQL :

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

이는 CTE 인수의 열 수가 쿼리의 열 수와 동일해야 함을 보장하기 위한 것입니다. CTE 인수에 열을 정의하지 않은 경우 CTE를 정의하는 쿼리 열을 사용합니다.

파생 테이블과 마찬가지로 개체로 저장할 수 없으며 쿼리 실행이 완료되는 즉시 손실됩니다. CTE는 파생된 테이블에 비해 더 나은 가독성을 제공하고 성능도 향상시킵니다.

파생 테이블과 달리 CTE는 하위 쿼리 그것은 가능하다 자기 참조 자신의 이름을 사용합니다. 그것은 또한로 알려져 있습니다 재귀적 CTE 동일한 쿼리에서 여러 번 참조될 수도 있습니다.

해킹 처리

재귀 CTE와 관련된 몇 가지 필수 사항은 다음과 같습니다.

이는 WITH RECURSIVE 절을 사용하여 정의됩니다.
재귀적 CTE에는 종료 조건이 포함되어야 합니다.
계층적 또는 트리 구조 데이터의 계열 생성 및 탐색을 위해 재귀적 CTE를 사용합니다.

MySQL 재귀 CTE 구문

다음은 MySQL의 재귀 CTE의 기본 구문입니다.

 WITH RECURSIVE cte_name (column_names) AS ( subquery ) SELECT * FROM cte_name;

여기서 하위 쿼리는 cte_name을 자체 이름으로 사용하여 자체를 참조하는 MySQL 쿼리입니다.

MySQL CTE 예

다양한 예를 사용하여 MySQL에서 CTE가 어떻게 작동하는지 이해해 보겠습니다. 여기서는 테이블을 사용하겠습니다. '직원' 시연을 위해. 이 테이블에 다음 데이터가 포함되어 있다고 가정합니다.

CTE의 개념을 이해하려면 다음 명령문을 실행하십시오. 이 예에서 CTE 이름은 다음과 같습니다. 캘리포니아주 직원 , CTE를 정의하는 하위 쿼리는 emp_name, emp_age 및 city의 세 열을 반환합니다. 따라서 CTE Employee_in_california는 다음 위치에 있는 모든 직원을 반환합니다. 캘리포니아 시 .

CTE Employee_in_california를 정의한 후 이를 참조했습니다. 선택하다 캘리포니아에 위치한 직원만을 선택하기 위한 성명서입니다.

 WITH employees_in_california AS ( SELECT * FROM employees WHERE city = &apos;California&apos; ) SELECT emp_name, emp_age, city FROM employees_in_california WHERE emp_age &gt;= 32 ORDER BY emp_name;

위의 명령문을 실행하면 다음과 같은 결과가 출력됩니다. 여기서는 결과가 캘리포니아에 있는 직원 데이터만 반환하는 것을 볼 수 있습니다.

mysql 왼쪽 조인

더욱 발전된 MySQL CTE 예

라는 이름의 테이블이 있다고 가정해 보겠습니다. 고객 그리고 주문하다 여기에는 다음 데이터가 포함됩니다.

테이블: 고객

표: 주문

다음을 사용하여 고급 CTE 예를 설명하는 아래 설명을 참조하세요. 내부 조인 절.

 WITH total_customer_2020 AS ( SELECT cust_id, name, occupation FROM customer INNER JOIN orders USING (cust_id) ORDER BY age ) SELECT * FROM orders JOIN total_customer_2020 USING (cust_id);

실행 후 아래와 같은 출력을 얻게 됩니다.

MySQL 재귀 CTE 예

다음 예에서는 재귀적 CTE의 작동을 설명합니다. 일련의 결과를 생성하는 아래 명령문을 고려하십시오. 처음 5개의 홀수 :

 WITH RECURSIVE odd_num_cte (id, n) AS ( SELECT 1, 1 union all SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>After executing the above statement, it will give the output as below:</p> <img src="//techcodeview.com/img/mysql-tutorial/80/mysql-common-table-expression-6.webp" alt="MySQL Common Table Expression (CTE)"> <p>The above statement consists of two parts one is non-recursive, and another is recursive.</p> <p> <strong>Non-recursive: SELECT 1, 1</strong> </p> <p>This part will produce the initial rows with two columns as &apos;id&apos; and &apos;n&apos; and a single row.</p> <p> <strong>Recursive: SELECT id+1, n+2 from odd_num_cte where id <5< strong> </5<></strong></p> <p>This part is responsible for adding rows to the previous output until the terminating condition (id <5) will not be satisfied. when the id reached 5, condition becomes false, and recursion process is terminated.< p> <h3>The WITH clause uses</h3> <p>MySQL provides many contexts to use the WITH clause for creating CTE. Let&apos;s discuss one by one in detail.</p> <p>First, we can use the WITH clause at the beginning of SELECT, UPDATE, and DELETE query as below.</p> <pre> WITH ... SELECT ... WITH ... UPDATE ... WITH ... DELETE ... </pre> <p>Second, we can use the WITH clause at the beginning of a subquery or a derived table subquery as below:</p> <pre> SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table; </pre> <p>Third, we can use the WITH clause immediately preceding of SELECT statements that include a SELECT clause as below:</p> <pre> CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ... </pre> <h3>Benefits of using CTE</h3> <ul> <li>It provides better readability of the query.</li> <li>It increases the performance of the query.</li> <li>The CTE allows us to use it as an alternative to the VIEW concept</li> <li>It can also be used as chaining of CTE for simplifying the query.</li> <li>It can also be used to implement recursive queries easily.</li> </ul> <hr></5)></p></5>

둘째, 아래와 같이 하위 쿼리 또는 파생 테이블 하위 쿼리의 시작 부분에 WITH 절을 사용할 수 있습니다.

 SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table;

셋째, SELECT 절을 포함하는 SELECT 문 바로 앞에는 아래와 같이 WITH 절을 사용할 수 있습니다.

안드로이드를 이용한 이미지 메시지 게임

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

CTE 사용의 이점

쿼리의 가독성이 향상됩니다.
쿼리 성능이 향상됩니다.
CTE를 사용하면 VIEW 개념의 대안으로 사용할 수 있습니다.
쿼리를 단순화하기 위해 CTE 연결로 사용할 수도 있습니다.
또한 재귀 쿼리를 쉽게 구현하는 데에도 사용할 수 있습니다.

TechCodeview