Mysql z wspólnym wyrażeniem tabeli

Wspólne wyrażenie tabeli (CTE) jest ważną cechą MySQL, która jest używana do generowania tymczasowego zestawu wyników. Można go używać z dowolną instrukcją SQL, taką jak Select, Insert, Aktualizacja itp. Skomplikowane zapytania można uprościć za pomocą CTE. Zestaw wyników dowolnego zapytania jest przechowywany jako obiekt dla wyprowadzonej tabeli w momencie wykonywania zapytania. Ale CTE może być samoreferencją, co oznacza, że ​​to samo zapytanie można odwoływać wiele razy za pomocą CTE. Z tego powodu wydajność CTE jest lepsza niż tabela pochodna. Z klauzulą ​​służy do zdefiniowania CTE i więcej niż jeden CTE można zdefiniować w jednym instrukcji za pomocą tej klauzuli. W jaki sposób CTE można zastosować w zapytaniu, aby uczynić go bardziej czytelnym, a zwiększenie wydajności zapytania są wyjaśnione w tym artykule.

Korzyści z korzystania z CTE:

• To sprawia, że ​​zapytanie jest bardziej czytelne.
• Poprawia wydajność zapytania.
• Może być używany jako alternatywa dla widoku.
• Możliwe jest utworzenie łączenia CTE, aby uprościć zapytanie.
• Zapytania rekurencyjne można łatwo zaimplementować za pomocą CTE.

Składnia:

Z CTE-nazwa (kolumna 1, kolumna2,… kolumnan) jako (
Zapytanie
)
Wybierz * z CTE-nazwa;

Tutaj możesz zdefiniować dowolną instrukcję SQL jako zapytanie, wybierz, zaktualizuj, usuń, wstaw lub utwórz instrukcję. Jeśli zdefiniujesz listę kolumn za pomocą klauzuli, liczba kolumn w zapytaniu musi być taka sama z liczbą kolumn zdefiniowanych w klauzuli.

Warunek wstępny:

Funkcja CTE nie jest obsługiwana przez żadną wersję MySQL mniejszą niż 8.0. Więc musisz zainstalować MySQL 8.0 Przed ćwiczeniem przykładu tego artykułu. Możesz sprawdzić aktualnie zainstalowaną wersję MySQL, uruchamiając następujące polecenie.

$ mysql -v

Wyjście pokazuje, że MySQL wersja 8.0.19 jest zainstalowane w systemie.

Jeśli zainstalowana jest poprawna wersja, utwórz nazwę bazy danych MYDB i utwórz dwa nazwy TABELE użytkownicy I Users_profile z pewnymi danymi, aby poznać zastosowania CTE w MySQL. Uruchom następujące instrukcje SQL, aby wykonać zadania. Te stwierdzenia utworzą dwa powiązane tabele nazwane użytkownicy I Users_profile. Następnie niektóre dane zostaną wstawione w obu tabelach przez instrukcje wstawiania.

Utwórz bazę danych MYDB;
Użyj mydb;
Utwórz użytkowników tabeli (
Nazwa użytkownika Varchar (50) klucz podstawowy,
Hasło varchar (50) nie null,
status varchar (10) nie null);
Utwórz tabelę Users_profile (
Nazwa użytkownika Varchar (50) klucz podstawowy,
Nazwa varchar (50) nie null,
Adres varchar (50) nie null,
e -mail varchar (50) nie null,
Klucz zagraniczny (nazwa użytkownika) Odwołuje się użytkownikom (nazwa użytkownika) na Delete Cascade);
Wstaw do wartości użytkowników
(„admin”, „7856”, „Active”),
(„Staff”, „90802”, „Active”),
(„Manager”, „35462”, „nieaktywny”);
Wstaw do wartości Users_Profile
(„admin”, „administrator”, „dhanmondi”, „[email protected] ') ,
(„Staff”, „Jakir Nayek”, „Mirpur”, „[email protected] '),
(„Menedżer”, „Mehr afroz”, „eskaton”, „[email protected] ');

Używanie prostego CTE:

Tutaj bardzo proste CTE nazwane CTE_USERS_PROFILE jest utworzony tam, gdzie żadna lista pola nie jest zdefiniowana za pomocą nazwy CTE w klauzuli i odzyska wszystkie dane z Users_profile tabela. Następnie instrukcja Select służy do odczytania wszystkich rekordów z CTE_USERS_PROFILE Cte.

Z CTE_USERS_PROFILE AS (
Wybierz * Z Users_Profile
)
Wybierz * z cte_users_profile;

Następujące dane wyjściowe pojawią się po uruchomieniu instrukcji.

Korzystanie z prostej CTE z listą kolumn:

Możesz utworzyć CTE dokładniej, definiując listę pola z nazwą CTE w klauzuli. W takim przypadku nazwy pola zdefiniowane nazwą CTE będą takie same jak nazwy pola zdefiniowane w zapytaniu Wybierz wewnątrz klauzuli z. Tutaj, nazwa I e-mail Pola są używane w obu miejscach.

Z cte_users_profile (nazwa, e -mail) jako (
Wybierz nazwę, e -mail
Z Users_profile
)
Wybierz * z cte_users_profile;

Następujące dane wyjściowe pojawią się po uruchomieniu powyższej instrukcji.

Używanie prostego CTE z klauzulą ​​Where:

Wybierz instrukcję z klauzulą ​​Where można zdefiniować w instrukcji CTE, tak jak inne Wybierz zapytanie. Wybierz zapytanie z rekordami odzyskania z użytkownicy I Users_profile tabele, w których wartości Nazwa użytkowników pole są równe dla obu tabel i wartości nazwa użytkownika nie jest 'personel'.

Z CTE_USERS AS (
Wybierz użytkowników.Nazwa użytkownika, Users_profile.Nazwa, Users_profile.Adres, Users_profile.e-mail
Od użytkowników, Users_profile
Gdzie użytkownicy.Nazwa użytkownika = Users_profile.Nazwa użytkownika i Użytkownicy_profile.Nazwa użytkownika „Personel”
)
Wybierz nazwę jako nazwisko, adres jako adres
Z CTE_USERS;

Następujące dane wyjściowe pojawią się po uruchomieniu instrukcji.

Używanie prostego CTE z grupą według klauzuli:

Każda funkcja łączna może być używana w zapytaniu, która jest używana w CTE. Poniższa instrukcja CTE pokazuje użycie funkcji Wybierz zapytanie z Count (). Pierwsza instrukcja wyboru służy do wyświetlania wszystkich rekordów użytkownicy Tabela i ostatnia instrukcja Select służy do wyświetlania wyjścia CTE, które będą liczyć całkowitą liczbę użytkowników z użytkownicy Stół, który jest aktywny.

Wybierz * od użytkowników;
Z CTE_USERS AS (
Wybierz liczbę (*) jako całkowity
Od użytkowników
Gdzie status = „Active” grupa według statusu
)
Wybierz Total As „Total Active Users”
Z CTE_USERS;

Następujące dane wyjściowe pojawią się po uruchomieniu instrukcji.

Korzystanie z prostego CTE z operatorem Union:

Poniższe oświadczenie CTE pokazuje użycie operatora Unii w oświadczeniu CTE. Wyjście wyświetli wartości nazwa użytkownika z użytkownicy stół, gdzie status wartosc jest 'Nieaktywny'i inne wartości nazwa użytkownika z Users_profile tabela.

Z CTE_USERS AS (
Wybierz użytkowników.nazwa użytkownika
Od użytkowników
Gdzie status = „nieaktywny”
UNIA
Wybierz Users_profile.nazwa użytkownika
Z Users_profile
)
Wybierz * z cte_users;

Następujące dane wyjściowe pojawią się po uruchomieniu instrukcji.

Używanie prostego CTE z lewym dołączeniem:

Poniższe oświadczenie CTE pokazuje użycie lewej wspólnej w CTE. Wyjście wyświetli wartości nazwa I e-mail pola z Users_profile Tabela, stosując przyłączanie lewej na podstawie nazwa użytkownika pole między użytkownicy I Users_profile tabele i warunki, które odfiltrują te rekordy użytkownicy Tabela, gdzie wartość status Jest 'Nieaktywny'.

Z CTE_USERS AS (
Wybierz nazwę, e -mail
Z Users_profile
Zostawili użytkowników
Na użytkownikach.Nazwa użytkownika = Users_profile.Nazwa użytkownika, w której użytkownicy.status = „nieaktywny”
)
Wybierz * z cte_users;

Następujące dane wyjściowe pojawią się po uruchomieniu instrukcji.

Wniosek:

Jeśli chcesz zwiększyć wydajność zapytania i szybciej zwiększyć wyjście zapytania. W tym artykule pomoże użytkownikom MySQL w bardzo łatwo nauczyć.