Mysql con espressione di tabella comune

L'espressione della tabella comune (CTE) è una caratteristica importante di MySQL che viene utilizzata per generare un set di risultati temporanei. Può essere utilizzato con qualsiasi istruzione SQL come selezione, inserimento, aggiornamento, ecc. Le query complicate possono essere semplificate utilizzando CTE. Il set di risultati di qualsiasi query viene archiviato come oggetto per la tabella derivata al momento dell'esecuzione delle query. Ma CTE può essere auto-riferenziamento, il che significa che la stessa query può essere referenziata più volte usando CTE. Per questo motivo, le prestazioni CTE sono migliori della tabella derivata. Con la clausola viene utilizzata per definire un CTE e più di un CTE può essere definito in una singola istruzione usando questa clausola. In che modo un CTE può essere applicato nella query per renderlo più leggibile e aumentare le prestazioni della query sono spiegati in questo articolo.

Vantaggi dell'utilizzo di CTE:

• Rende la query più leggibile.
• Migliora le prestazioni delle query.
• Può essere usato come alternativa alla vista.
• È possibile creare un concatenamento di CTE per semplificare la query.
• Le query ricorsive possono essere implementate facilmente utilizzando CTE.

Sintassi:

Con cte-name (column1, column2, ... columnn) come (
Domanda
)
Seleziona * da CTE-NAME;

Qui, è possibile definire qualsiasi istruzione SQL come query, selezionare, aggiornare, eliminare, inserire o creare un'istruzione. Se si definisce l'elenco delle colonne con la clausola, il numero di colonne nella query deve essere lo stesso con il numero di colonne definite con la clausola.

Prerequisito:

La funzione CTE non è supportata da nessuna versione MySQL inferiore a 8.0. Quindi, devi installare mysql 8.0 prima di praticare l'esempio di questo articolo. È possibile controllare la versione attualmente installata di MySQL eseguendo il comando seguente.

$ mysql -v

L'output mostra che MySQL versione 8.0.19 è installato nel sistema.

Se la versione corretta è installata, crea un database denominato mydb e crea due tabelle chiamate utenti E utenti_profile con alcuni dati per conoscere gli usi di CTE in MySQL. Esegui le seguenti istruzioni SQL per svolgere le attività. Queste dichiarazioni creeranno due tabelle correlate denominate utenti E utenti_profile. Successivamente, alcuni dati verranno inseriti in entrambe le tabelle da inserisci dichiarazioni.

Crea database mydb;
Usa mydb;
Crea utenti di tabelle (
Nome utente Varchar (50) Chiave primaria,
Password Varchar (50) non NULL,
status varchar (10) non null);
Crea table utenti_profile (
Nome utente Varchar (50) Chiave primaria,
Nome varchar (50) non null,
Indirizzo Varchar (50) non null,
Email Varchar (50) non NULL,
Foreign Key (Nome utente) Riferimenti Utenti (nome utente) su Elimina Cascade);
Inserire nei valori degli utenti
('Admin', '7856', 'Active'),
("Staff", "90802", "Active"),
('Manager', '35462', 'inattivo');
Inserisci nei valori utenti_profili
("Admin", "Amministratore", "Dhanmondi", '[email protected] ') ,
("Staff", "Jakir Nayek", "Mirpur", "[email protected] '),
('Manager', 'Mehr Afroz', 'Eskaton', '[email protected] ');

Uso del semplice CTE:

Qui un CTE molto semplice chiamato cte_users_profile viene creato dove non è definito alcun elenco di campi con il nome CTE con la clausola e recupererà tutti i dati dal utenti_profile tavolo. Successivamente, l'istruzione Select viene utilizzata per leggere tutti i record cte_users_profile Cte.

Con cte_users_profile come (
Seleziona * da utenti_profile
)
Seleziona * da cte_users_profile;

Il seguente output apparirà dopo aver eseguito l'istruzione.

Utilizzo del semplice CTE con elenco di colonne:

È possibile creare CTE in modo più specifico definendo l'elenco dei campi con il nome CTE con la clausola. In questo caso, i nomi dei campi definiti con il nome CTE saranno gli stessi dei nomi del campo definiti nella query selezionata all'interno della clausola. Qui, nome E e-mail I campi sono utilizzati in entrambi i luoghi.

Con cte_users_profile (nome, email) come (
Seleziona Nome, Email
Da utenti_profile
)
Seleziona * da cte_users_profile;

La seguente output apparirà dopo aver eseguito l'istruzione sopra.

Uso di CTE semplice con la clausola dove:

L'istruzione seleziona con la clausola dove può essere definita nell'istruzione CTE come un'altra query selezionata. La query seleziona con record di recupero da utenti E utenti_profile tabelle in cui i valori di UsersName il campo sono uguali per entrambe le tabelle e il valore di nome utente non è 'personale'.

Con cte_users come (
Seleziona gli utenti.Nome utente, utenti_profile.Nome, Users_profile.Indirizzo, utenti_profile.e-mail
Dagli utenti, utenti_profile
Dove gli utenti.nome utente = utenti_profile.Nome utente e utenti_profile.Nome utente "personale"
)
Seleziona nome come nome, indirizzo come indirizzo
Da cte_users;

Il seguente output apparirà dopo aver eseguito l'istruzione.

Uso di CTE semplice con gruppo per clausola:

Qualsiasi funzione aggregata può essere utilizzata nella query utilizzata in CTE. La seguente istruzione CTE mostra l'uso della funzione Select query con count (). La prima istruzione selezionata viene utilizzata per visualizzare tutti i record di utenti La tabella e l'ultima istruzione selezionata vengono utilizzate per visualizzare l'output di CTE che conta il numero totale di utenti da utenti Tavolo che sono attivi.

Seleziona * dagli utenti;
Con cte_users come (
Seleziona COUNT (*) come totale
Dagli utenti
Dove status = gruppo "attivo" per stato
)
Seleziona il totale come "utenti attivi totali"
Da cte_users;

Il seguente output apparirà dopo aver eseguito l'istruzione.

Uso di CTE semplice con operatore sindacale:

La seguente dichiarazione CTE mostra l'uso dell'operatore sindacale nella dichiarazione CTE. L'output visualizzerà i valori di nome utente da utenti tavolo dove il stato Il valore è 'Inattivo'e gli altri valori di nome utente da utenti_profile tavolo.

Con cte_users come (
Seleziona gli utenti.nome utente
Dagli utenti
Dove status = 'inattivo'
UNIONE
Seleziona Users_Profile.nome utente
Da utenti_profile
)
Seleziona * da cte_users;

Il seguente output apparirà dopo aver eseguito l'istruzione.

Utilizzo di CTE semplice con un join a sinistra:

La seguente istruzione CTE mostra l'uso di sinistra in CTE. L'output visualizzerà i valori di nome E e-mail campi da utenti_profile tabella applicando un join a sinistra in base a nome utente campo tra utenti E utenti_profile tabelle e dove condizioni, che filtreranno quei record da utenti tabella dove il valore di stato È 'Inattivo'.

Con cte_users come (
Seleziona Nome, Email
Da utenti_profile
Utenti di sinistra
Sugli utenti.nome utente = utenti_profile.Nome utente in cui gli utenti.status = 'inattivo'
)
Seleziona * da cte_users;

Il seguente output apparirà dopo aver eseguito l'istruzione.

Conclusione:

Se si desidera aumentare le prestazioni delle query e ottenere l'uscita della query più veloce, allora il CTE è l'opzione migliore rispetto ad altre opzioni MySQL. Questo articolo aiuterà gli utenti MySQL a imparare l'uso di CTE per la query selezionata molto facilmente.