MYSQL COMMON TABLE EXPRESSION (CTE) - VÝUKOVÝ PROGRAM MYSQL

V MySQL každý príkaz alebo dotaz vytvára dočasný výsledok alebo vzťah. Používa sa bežný tabuľkový výraz alebo CTE pomenujte tieto dočasné výsledky ktoré existujú v rozsahu vykonávania tohto konkrétneho príkazu, ako napríklad CREATE, VLOŽIŤ , SELECT , AKTUALIZOVAŤ , VYMAZAŤ , atď.

ako vygenerovať náhodné číslo v jave

Niektoré z kľúčových bodov súvisiacich s CTE sú:

Definuje sa pomocou S doložka.
Klauzula WITH nám umožňuje zadať viac ako jeden CTE v jednom dotaze.
CTE môže odkazovať na iné CTE, ktoré sú súčasťou tej istej klauzuly WITH, ale tieto CTE by mali byť definované skôr.
Rozsah vykonávania CTE existuje v konkrétnom vyhlásení, v ktorom sa používa.

Syntax MySQL CTE

Syntax MySQL CTE obsahuje názov, voliteľný zoznam stĺpcov a príkaz/dotaz, ktorý definuje spoločný tabuľkový výraz (CTE). Po definovaní CTE ho môžeme použiť ako pohľad v dotazoch SELECT, INSERT, UPDATE a DELETE.

Nasleduje základná syntax CTE in MySQL :

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

Je potrebné zabezpečiť, aby počet stĺpcov v argumentoch CTE bol rovnaký ako počet stĺpcov v dotaze. Ak sme nedefinovali stĺpce v argumentoch CTE, použije sa stĺpce dotazu, ktoré definujú CTE.

Podobne ako odvodená tabuľka, nemôže byť uložená ako objekt a stratí sa hneď po vykonaní dotazu. CTE poskytuje lepšiu čitateľnosť a tiež zvyšuje výkon v porovnaní s odvodenou tabuľkou.

Na rozdiel od odvodenej tabuľky je CTE a poddotaz môže byť sebareferencovanie pomocou vlastného mena. Je tiež známy ako rekurzívne CTE a možno na ne odkazovať aj viackrát v rovnakom dotaze.

Niektoré zo základných bodov súvisiacich s rekurzívnym CTE sú:

Je definovaný pomocou klauzuly WITH RECURSIVE.
Rekurzívny CTE musí obsahovať ukončovaciu podmienku.
Rekurzívne CTE použijeme na generovanie sérií a prechádzanie hierarchických alebo stromovo štruktúrovaných údajov.

Rekurzívna syntax CTE MySQL

Nasleduje základná syntax rekurzívneho CTE v MySQL:

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

V tomto prípade je poddotaz dopytom MySQL, ktorý sa odkazuje sám na seba pomocou cte_name ako svojho vlastného názvu.

Príklady MySQL CTE

Poďme pochopiť, ako funguje CTE v MySQL pomocou rôznych príkladov. Tu použijeme tabuľku 'zamestnanci' na ukážku. Predpokladajme, že táto tabuľka obsahuje nasledujúce údaje:

zoradiť pole v jave

Vykonajte nasledujúci príkaz, aby ste pochopili koncept CTE. V tomto príklade je názov CTE zamestnanec_v_kalifornii , poddotaz, ktorý definuje CTE, vráti tri stĺpce emp_name, emp_age a city. Preto CTE zamestnanec_v_californii vráti všetkých zamestnancov, ktorí sa nachádzajú v Kalifornské mesto .

Po definovaní CTE zamestnanec_v_kalifornii sme naň odkazovali v SELECT vyhlásenie pre výber iba tých zamestnancov, ktorí sa nachádzajú v Kalifornii.

 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;

Po vykonaní vyššie uvedeného príkazu poskytne nasledujúci výstup. Tu vidíme, že výsledok vráti iba údaje o zamestnancoch, ktoré sa nachádzajú v Kalifornii.

program java ahoj

Pokročilejší príklad MySQL CTE

Predpokladajme, že máme tabuľku s názvom zákazníka a objednať ktorý obsahuje nasledujúce údaje:

Tabuľka: zákazník

Tabuľka: objednávky

Pozrite si nižšie uvedené vyhlásenie, ktoré vysvetľuje pokročilý príklad CTE pomocou VNÚTORNÉ SPOJENIE doložka.

 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);

Po vykonaní dostaneme výstup, ako je uvedené nižšie:

Príklad rekurzívneho CTE MySQL

Nasledujúce príklady vysvetľujú fungovanie rekurzívneho CTE. Zvážte nižšie uvedené vyhlásenie, ktoré generuje sériu prvých päť nepárnych čísel :

java switch int

 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>

Po druhé, môžeme použiť klauzulu WITH na začiatku poddotazu alebo poddotazu odvodenej tabuľky, ako je uvedené nižšie:

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

Po tretie, môžeme použiť klauzulu WITH bezprostredne pred príkazmi SELECT, ktoré obsahujú klauzulu SELECT, ako je uvedené nižšie:

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

Výhody používania CTE

Poskytuje lepšiu čitateľnosť dotazu.
Zvyšuje výkon dotazu.
CTE nám umožňuje použiť ho ako alternatívu ku konceptu VIEW
Môže byť tiež použitý ako reťazenie CTE na zjednodušenie dotazu.
Môže sa tiež použiť na jednoduchú implementáciu rekurzívnych dotazov.