MySQL Alikysely esimerkkien kanssa

Mikä on alikysely SQL:ssä?

A alikysely on SELECT-kysely, joka on toisen kyselyn sisällä. Sisäistä valintakyselyä käytetään yleensä ulomman valintakyselyn tulosten määrittämiseen, joten tietokanta arvioi ensin sisäisen lausekkeen ja välittää sitten sen tulosteen ylöspäin.

Sisäistä kyselyä kutsutaan sisäinen kysely tai sisäkkäinen kysely, ja sen sisältävää lauseketta kutsutaan ulkoinen kyselyTarkastellaanpa alikyselyn syntaksia.

Yllä oleva kaavio näyttää lausekkeen yleisen muodon: ulompi SELECT antaa haluamasi sarakkeet ja hakasulkeissa oleva sisempi SELECT antaa arvon tai arvoluettelon, johon WHERE-lauseke vertaa.

Miksi käyttää alikyselyä?

Ennen kuin tarkastellaan eri tyyppejä, on hyödyllistä tietää, milloin alikysely ansaitsee paikkansa lausekkeessa.

Alikysely vastaa kysymykseen, jonka suodatinarvoa ei tiedetä etukäteen. Se on laskettava itse datasta. Yleinen asiakasvalitus MyFlix-videokirjastossa on elokuvien vähäinen määrä, ja johto haluaa ostaa elokuvia kategoriasta, jossa on vähiten nimikkeitä. Kukaan ei tiedä, mikä kategoria se on, ennen kuin tietokannasta kysytään, joten arvo on laskettava ensin ja sitten käytettävä suodattimena.

Alikyselyt ovat osoitteessatrackolmesta käytännön syystä:

• luettavuus: Jokainen logiikan osa sijaitsee omassa sulkeissa olevassa lohkossaan, joten lause on pikemminkin sarja pieniä kysymyksiä kuin yksi monimutkainen lauseke.
• Eristäminen: Sisäinen kysely voidaan suorittaa yksinään sen varmistamiseksi, että se palauttaa odotetun arvon, mikä helpottaa testausta ja virheenkorjausta huomattavasti.
• Joustavuus: Sama kaava toimii WHERE-, HAVING-, SELECT- ja FROM-lausekkeissa sekä INSERT-, UPDATE- ja DELETE-lausekkeissa.

Kompromissi on nopeus, jota tarkastellaan JOIN-vertailussa myöhemmin tässä artikkelissa.

Alikyselyiden tyypit MySQL

MySQL tukee kolmenlaisia ​​alikyselyitä, ja tyyppi määräytyy sisäisen kyselyn palauttaman tuloksen muodon mukaan. Jokainen tyyppi selitetään alla toimivan esimerkin avulla myflixdb-tietokantaa vasten.

1) Skalaari-alikysely

A skalaarinen alikysely palauttaa täsmälleen yhden rivin ja yhden sarakkeen, mikä tarkoittaa, että se palauttaa yhden arvon. Koska tulos on yksi arvo, sitä voidaan käyttää missä tahansa, missä literaaliarvo on sallittu. Palatakseni yllä olevaan MyFlix-ongelmaan, voit käyttää seuraavanlaista kyselyä:

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

Se antaa tuloksen:

Katsotaanpa, miten tämä kysely toimii.

Kuten toteutuskaaviosta näkyy, MySQL ensimmäiset juoksut SELECT MIN(category_id) FROM movies, vastaanottaa yhden arvon ja suorittaa vasta sitten ulkoisen kyselyn kyseisen arvon kanssa. Koska palautetaan yksi arvo, sallitut operaattorit ovat vakiovertailujoukko: =, <> (Tai !=), >, >=, <ja <=.

💡 Vinkki: Jos alikysely sijoitetaan jälkeen = palauttaa useamman kuin yhden rivin, MySQL aiheuttaa virheen 1242, Alikysely palauttaa useamman kuin yhden rivinVaihda operaattori asentoon IN, tai tiukenna sisäistä WHERE-lauseketta.

2) Rivi-alikysely

A rivin alikysely palauttaa myös yhden rivin, mutta kyseisellä rivillä voi olla useampi kuin yksi sarake. Ulompi kysely vertaa siis arvojen riviä rivinmuodostajaan yksittäisen arvon sijaan.

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

Sallitut operaattorit ovat samat yllä luetellut vertailuoperaattorit, joita sovelletaan koko riviin kerralla.

3) Taulukon alikysely

A taulukon alikysely palauttaa useita rivejä ja usein useita sarakkeita, joten ulkokyselyssä on käytettävä joukko-operaattoria, kuten IN, NOT IN, ANY, ALLtai EXISTS.

Oletetaan, että haluat niiden jäsenten nimet ja puhelinnumerot, jotka ovat vuokranneet elokuvan eivätkä ole vielä palauttaneet sitä, jotta voit soittaa heille muistutuksen antamiseksi. Voit käyttää seuraavanlaista kyselyä:

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

Katsotaanpa, miten tämä kysely toimii.

Tässä tapauksessa sisäinen kysely palauttaa useamman kuin yhden tuloksen, joten jäsennumeroiden luettelo luovutetaan IN operaattori ja jokainen vastaava jäsen palautetaan.

Alikyselyiden sisäkkäisyys useilla tasoilla

Tähän mennessä olet nähnyt kaksi tasoa. Alikysely voi sisältää myös toisen alikyselyn, joka tuottaa kolminkertaisen sisäkkäisen lausekkeen.

Oletetaan, että johto haluaa palkita eniten maksavan jäsenen. Voimme suorittaa seuraavanlaisen kyselyn:

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

Sisimpänä oleva kysely löytää suurimman maksun, keskimmäisenä kysely muuntaa kyseisen summan jäsennumeroksi ja ulompana kysely muuntaa jäsennumeron nimeksi. Yllä oleva kysely antaa seuraavan tuloksen:

Alikyselyiden käyttäminen INSERT-, UPDATE- ja DELETE-komentojen kanssa

Alikyselyt eivät rajoitu SELECT-lausekkeisiin. Sama hakasulkeissa oleva kaava toimii datanmuokkauslausekkeiden sisällä, mikä mahdollistaa kokonaisen rivijoukon muuttamisen yhdellä kertaa ilman väliaikaisen taulukon luomista.

INSERT ja alikysely. Alikysely voi tarjota lisättävät rivit, jotka kopioivat tietoja taulukosta toiseen. SELECT-kyselyn sarakeluettelon on oltava linjassa INSERT-kyselyn sarakeluettelon kanssa.

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

PÄIVITYS alikyselyllä. Tässä sisäinen kysely päättää, mitä rivejä kosketaan. Alla oleva esimerkki merkitsee jokaisen jäsenen, jonka vuokraus on vielä maksamatta.

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

POISTA alikyselyllä. Sama idea poistaa rivit, jotka täyttävät toisessa taulukossa olevan ehdon.

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️ Varoitus: MySQL ei salli lausekkeen muokkaavan taulukkoa ja valitsevan samasta taulukosta FROM-lausekkeen alikyselyn sisällä. Jos virhe 1093 ilmenee, rivitä sisäinen kysely johdettuun taulukkoon, esimerkiksi SELECT * FROM (SELECT ...) AS tSiten, että MySQL materialisoi tuloksen ennen muutoksen käyttöönottoa. On myös viisasta suorittaa ensin sisäinen SELECT ja varmistaa rivien määrä ennen PÄIVITYS tai POISTA tuotannossa.

Alikyselyt vs. liitokset

Sekä alikysely että JOIN voivat yhdistää tietoja useammasta kuin yhdestä taulukosta, joten luonnollinen kysymys on, kumpaan niistä kannattaa tarttua.

Liitoksiin verrattuna alikyselyt ovat helppokäyttöisiä ja helppolukuisia. Ne eivät ole yhtä monimutkaisia ​​kuin Liitosten, ja siksi niitä käytetään usein mm. SQL-aloittelijat.

Mutta alikyselyillä on suorituskykyongelmia. Liitoksen käyttäminen alikyselyn sijaan voi joskus parantaa suorituskykyä jopa 500-kertaisesti, koska optimoija voi ratkaista liitoksen yhdellä kertaa sen sijaan, että se arvioisi sisäistä lauseketta toistuvasti.

Jos valinta on annettu, on suositeltavaa käyttää JOIN-komentoa alikyselyssä. Alikyselyitä tulisi käyttää vararatkaisuna vain silloin, kun et voi käyttää JOIN-operaatiota edellä mainitun saavuttamiseksi.

Alikyselyt on myös helppo jakaa yksittäisiin loogisiin osiin, mikä on erittäin hyödyllistä, kun testaus ja kyselyiden virheenkorjaus.

UKK