W MySQL każda instrukcja lub zapytanie generuje tymczasowy wynik lub relację. Do tego używa się popularnego wyrażenia tabelowego lub CTE nadaj nazwę temu tymczasowemu zestawowi wyników które istnieją w zakresie wykonania tej konkretnej instrukcji, np. CREATE, WSTAWIĆ , WYBIERAĆ , AKTUALIZACJA , USUWAĆ itp.
złap i wypróbuj Java
Niektóre z kluczowych punktów związanych z CTE to:
- Definiuje się go za pomocą Z klauzula.
- Klauzula WITH pozwala nam określić więcej niż jeden CTE w jednym zapytaniu.
- CTE może odwoływać się do innych CTE, które są częścią tej samej klauzuli WITH, ale te CTE powinny zostać zdefiniowane wcześniej.
- Zakres wykonania CTE istnieje w ramach konkretnej instrukcji, w której jest używany.
Składnia CTE MySQL
Składnia MySQL CTE obejmuje nazwę, opcjonalną listę kolumn i instrukcję/zapytanie definiujące wspólne wyrażenie tabelowe (CTE). Po zdefiniowaniu CTE możemy go użyć jako widoku w zapytaniach SELECT, INSERT, UPDATE i DELETE.
Poniżej znajduje się podstawowa składnia CTE w MySQL-a :
WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;
Ma to na celu zapewnienie, że liczba kolumn w argumentach CTE musi być taka sama, jak liczba kolumn w zapytaniu. Jeżeli nie zdefiniowaliśmy kolumn w argumentach CTE, użyte zostaną kolumny zapytania definiujące CTE.
Podobnie jak w przypadku tabeli pochodnej, nie można jej przechowywać jako obiektu i zostanie utracona zaraz po zakończeniu wykonywania zapytania. CTE zapewnia lepszą czytelność, a także zwiększa wydajność w porównaniu z tabelą wyprowadzoną.
W przeciwieństwie do tabeli pochodnej, CTE to a podzapytanie to może być odwoływanie się do siebie używając własnego imienia. Znany jest również jako rekurencyjny CTE i można do niego odwoływać się wielokrotnie w tym samym zapytaniu.
Niektóre z istotnych punktów związanych z rekurencyjnym CTE to:
- Definiuje się go za pomocą klauzuli WITH RECURSIVE.
- Rekurencyjny CTE musi zawierać warunek kończący.
- Będziemy używać rekurencyjnego CTE do generowania serii i przeglądania danych o strukturze hierarchicznej lub drzewiastej.
Składnia rekursywnego CTE MySQL
Poniżej znajduje się podstawowa składnia rekurencyjnego CTE w MySQL:
WITH RECURSIVE cte_name (column_names) AS ( subquery ) SELECT * FROM cte_name;
W tym przypadku podzapytanie jest zapytaniem MySQL, które odnosi się do siebie, używając nazwy cte jako własnej nazwy.
Przykłady CTE MySQL
Pozwól nam zrozumieć, jak CTE działa w MySQL na różnych przykładach. Tutaj będziemy korzystać z tabeli 'pracownicy' na demonstrację. Załóżmy, że ta tabela zawiera następujące dane:
okno.otwarte
Wykonaj następującą instrukcję, aby zrozumieć koncepcję CTE. W tym przykładzie nazwa CTE to pracownik_w_Kalifornii podzapytanie definiujące CTE zwraca trzy kolumny nazwa_emp, wiek_emp i miasto. Dlatego pracownik CTE_in_california zwróci wszystkich pracowników znajdujących się w Miasto Kalifornia .
Po zdefiniowaniu pracownika CTE w_californi, odwołaliśmy się do niego w pliku WYBIERAĆ oświadczenie dotyczące wyboru wyłącznie pracowników znajdujących się w Kalifornii.
WITH employees_in_california AS ( SELECT * FROM employees WHERE city = 'California' ) SELECT emp_name, emp_age, city FROM employees_in_california WHERE emp_age >= 32 ORDER BY emp_name;
Po wykonaniu powyższej instrukcji wyświetli się następujący wynik. Tutaj widzimy, że wynik zwraca tylko dane pracowników znajdujących się w Kalifornii.
pełna forma i d e
Bardziej zaawansowany przykład CTE MySQL
Załóżmy, że mamy tabelę o nazwie klient I zamówienie który zawiera następujące dane:
Tabela: klient
Tabela: zamówienia
Zobacz poniższą instrukcję wyjaśniającą zaawansowany przykład CTE przy użyciu WEWNĘTRZNE POŁĄCZENIE klauzula.
co to komputer
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 wykonaniu otrzymamy wynik jak poniżej:
Przykład rekurencyjnego CTE MySQL
Poniższe przykłady wyjaśniają działanie rekurencyjnego CTE. Rozważmy poniższą instrukcję, która generuje serię pierwsze pięć liczb nieparzystych :
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 'id' and 'n' 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'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 drugie, możemy użyć klauzuli WITH na początku podzapytania lub podzapytania tabeli pochodnej, jak poniżej:
SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table;
Po trzecie, możemy użyć klauzuli WITH bezpośrednio poprzedzającej instrukcje SELECT zawierające klauzulę SELECT, jak poniżej:
CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ...
Korzyści ze stosowania CTE
- Zapewnia lepszą czytelność zapytania.
- Zwiększa wydajność zapytania.
- CTE pozwala nam używać go jako alternatywy dla koncepcji VIEW
- Można go również wykorzystać do łączenia CTE w celu uproszczenia zapytania.
- Można go również używać do łatwego wdrażania zapytań rekurencyjnych.