Mysql med vanlig tabelluttrykk

Vanlig tabelluttrykk (CTE) er et viktig trekk ved MySQL som brukes til å generere et midlertidig resultatsett. Det kan brukes med hvilken som helst SQL -setning som SELECT, Sett inn, oppdatering osv. De kompliserte spørsmålene kan forenkles ved å bruke CTE. Resultatsettet for enhver spørring lagres som et objekt for den avledede tabellen på tidspunktet for utførelsen av spørringen. Men CTE kan være selvhenvisning som betyr at den samme spørringen kan henvises til flere ganger ved bruk av CTE. Av denne grunn er CTE -ytelse bedre enn det avledede tabellen. Med klausul brukes til å definere en CTE og mer enn en CTE kan defineres i en enkelt uttalelse ved hjelp av denne klausulen. Hvordan en CTE kan brukes i spørringen for å gjøre det mer lesbar og øke ytelsen til spørringen blir forklart i denne artikkelen.

Fordelene ved å bruke CTE:

• Det gjør spørringen mer lesbar.
• Det forbedrer spørringsytelsen.
• Det kan brukes som et alternativ til utsikten.
• Det er mulig å lage en kjetting av CTE for å forenkle spørringen.
• Rekursive spørsmål kan enkelt implementeres ved å bruke CTE.

Syntaks:

Med CTE-navn (kolonne1, kolonne2, ... kolonne) som (
Spørsmål
)
Velg * fra CTE-navn;

Her kan du definere enhver SQL -setning som spørring, velg, oppdatere, slette, sette inn eller opprette uttalelse. Hvis du definerer kolonneliste i med leddet, må antall kolonner i spørringen være det samme med antall kolonner definert i med leddet.

Forutsetning:

CTE -funksjonen støttes ikke av noen MySQL -versjon mindre enn 8.0. Så du må installere mysql 8.0 Før du øver på eksemplet med denne artikkelen. Du kan sjekke den for øyeblikket installerte versjonen av MySQL ved å kjøre følgende kommando.

$ mysql -v

Utgangen viser at MySQL versjon 8.0.19 er installert i systemet.

Hvis riktig versjon er installert, oppretter du en database som heter MYDB og lage to tabeller som heter Brukere og brukere_profil med noen data for å kjenne bruken av CTE i MySQL. Kjør følgende SQL -setninger for å gjøre oppgavene. Disse uttalelsene vil lage to relaterte tabeller som er navngitt Brukere og brukere_profil. Deretter vil noen data bli satt inn i begge tabellene ved å sette inn utsagn.

Opprette database MyDB;
Bruk MyDB;
Lag tabellbrukere (
Brukernavn varchar (50) primærnøkkel,
passord varchar (50) ikke null,
Status varchar (10) ikke null);
Lag tabellbrukere_profil (
Brukernavn varchar (50) primærnøkkel,
Navn varchar (50) ikke null,
adresse varchar (50) ikke null,
E -post varchar (50) ikke null,
Utenlandsk nøkkel (brukernavn) referanser brukere (brukernavn) på slett kaskade);
Sett inn brukerverdier
('admin', '7856', 'aktiv'),
('Staff', '90802', 'aktiv'),
('Manager', '35462', 'inaktiv');
Sett inn i brukere_profilverdier
('Admin', 'Administrator', 'Dhanmondi', '[email protected] ') ,
('Staff', 'Jakir Nayek', 'Mirpur', '[email protected] '),
('Manager', 'Mehr Afroz', 'Eskaton', '[email protected] ');

Bruk av enkel CTE:

Her en veldig enkel CTE kalt cte_users_profile opprettes der ingen feltliste er definert med CTE -navn i med leddet og den vil hente alle data fra brukere_profil bord. Deretter brukes den utvalgte uttalelsen til å lese alle poster fra cte_users_profile CTE.

Med cte_users_profile som (
Velg * fra brukere_profil
)
Velg * fra CTE_USERS_PROFIL;

Følgende utgang vises etter å ha kjørt uttalelsen.

Bruk av enkel CTE med kolonneliste:

Du kan opprette CTE mer spesifikt ved å definere feltlisten med CTE -navn med klausul. I dette tilfellet vil feltnavnene som er definert med CTE -navnet være det samme som feltnavnene som er definert i SELECT -spørringen i With -leddet. Her, Navn og e -post Felt brukes begge steder.

Med cte_users_profile (navn, e -post) som (
Velg navn, e -post
Fra brukere_profil
)
Velg * fra CTE_USERS_PROFIL;

Følgende utdata vises etter å ha kjørt ovennevnte uttalelse.

Bruk av enkel CTE med hvor klausul:

SELECT -setningen med hvor leddet kan defineres i CTE -setning som en annen SELECT -spørring. SELECT -spørringen med hente poster fra Brukere og brukere_profil tabeller der verdiene til Brukernavn feltet er like for begge tabellene og verdien av Brukernavn er ikke 'personale'.

Med cte_usere som (
Velg brukere.Brukernavn, brukere_profil.Navn, brukere_profil.adresse, brukere_profil.e -post
Fra brukere, brukere_profil
Hvor brukere.brukernavn = brukere_profil.Brukernavn og brukere_profil.Brukernavn 'Staff'
)
Velg navn som navn, adresse som adresse
Fra CTE_USERS;

Følgende utgang vises etter å ha kjørt uttalelsen.

Bruk av enkel CTE med gruppe etter klausul:

Enhver aggregatfunksjon kan brukes i spørringen som brukes i CTE. Følgende CTE -setning viser bruk av Select Query med Count () -funksjonen. Den første valgte uttalelsen brukes til å vise alle poster over Brukere tabell og den siste valgte setningen brukes til å vise utdataene til CTE som vil telle det totale antallet brukere fra Brukere Tabell som er aktive.

Velg * fra brukere;
Med cte_usere som (
Velg Count (*) som total
Fra brukere
Hvor status = "aktiv" gruppe etter status
)
Velg total som 'totale aktive brukere'
Fra CTE_USERS;

Følgende utgang vises etter å ha kjørt uttalelsen.

Bruk av enkel CTE med unionsoperatør:

Følgende CTE -uttalelse viser bruken av unionsoperatøren i CTE -setningen. Utgangen vil vise verdiene til Brukernavn fra Brukere bord der status Verdien er 'Inaktiv'Og de andre verdiene av Brukernavn fra brukere_profil bord.

Med cte_usere som (
Velg brukere.Brukernavn
Fra brukere
Hvor status = 'inaktiv'
Union
Velg brukere_profil.Brukernavn
Fra brukere_profil
)
Velg * fra CTE_USERS;

Følgende utgang vises etter å ha kjørt uttalelsen.

Bruk av enkel CTE med venstre sammenføyning:

Følgende CTE -setning viser bruken av venstre sammen med CTE. Utgangen vil vise verdiene til Navn og e -post felt fra brukere_profil tabell ved å bruke venstre sammenføyning basert på Brukernavn felt mellom Brukere og brukere_profil tabeller og hvor tilstand, som vil filtrere disse postene fra Brukere tabell der verdien av status er 'Inaktiv'.

Med cte_usere som (
Velg navn, e -post
Fra brukere_profil
Venstre bli med brukere
På brukere.brukernavn = brukere_profil.Brukernavn der brukere.status = 'inaktiv'
)
Velg * fra CTE_USERS;

Følgende utgang vises etter å ha kjørt uttalelsen.

Konklusjon:

Hvis du vil øke spørringsytelsen og få spørsmål om spørringen raskere, er CTE det bedre alternativet enn andre MySQL -alternativer. Denne artikkelen vil hjelpe MySQL -brukere til å lære bruken av CTE for SELECT -spørringen veldig enkelt.