Juhtum MySQL-is: 11 praktilist näidet

Informatec Digital » Andmebaasid » Juhtum MySQL-is: 11 praktilist näidet

MySQL-i funktsioon CASE on võimas tööriist, mis võimaldab teil oma päringutes teha tingimuslikke toiminguid. CASE abil saate hinnata erinevaid tingimusi ja saada konkreetseid tulemusi olenevalt sellest, kas need tingimused on täidetud või mitte. Selgitame seda funktsiooni praktiliste näidetega, mis aitavad teil omandada CASE kasutamise MySQL-is ja täiustada teie andmebaasihalduse oskusi.

Mis on MySQL-i funktsioon CASE?

MySQL-i CASE-funktsioon on tingimuslik avaldis, mis võimaldab teil hinnata erinevaid tingimusi ja tagastada konkreetseid tulemusi olenevalt sellest, kas need tingimused on täidetud. See on väga kasulik tööriist loogiliste toimingute tegemiseks päringute sees ja kohandatud tulemuste saamiseks kindlate kriteeriumide alusel.

CASE töötab sarnaselt IF-THEN-ELSE lausetega, kus saate määrata mitu tingimust ja väärtused, mis tagastatakse, kui need tingimused on täidetud. Kui ükski tingimus ei ole täidetud, saate määrata vaikeväärtuse ELSE-klausli abil.

Põhiline CASE süntaks MySQL-is

Funktsiooni CASE põhisüntaks MySQL-is on järgmine:

CASE
    WHEN condición1 THEN resultado1
    WHEN condición2 THEN resultado2
    ...
    WHEN condiciónN THEN resultadoN
    ELSE resultado_predeterminado
END

Siin on süntaksi iga osa selgitus:

• WHEN: määrab hinnatava tingimuse.
• THEN: näitab tulemust, mis tagastatakse, kui vastav tingimus on täidetud.
• ELSE: (Valikuline) Määrab tagastatava tulemuse, kui ükski ülaltoodud tingimustest ei ole täidetud.
• END: märgib CASE-avaldise lõppu.

Nüüd, kui teate põhisüntaksit, uurime mõnda praktilist näidet!

Näide 1: klassifitseerige õpilased nende keskmise järgi

Oletame, et teil on tabel nimega "õpilased" järgmiste veergudega: "id", "name" ja "avg". Soovite õpilasi järjestada GPA järgi, kasutades funktsiooni CASE. Saate seda teha järgmiselt:

SELECT nombre,
       CASE
           WHEN promedio >= 90 THEN 'Sobresaliente'
           WHEN promedio >= 80 THEN 'Notable'
           WHEN promedio >= 70 THEN 'Bien'
           WHEN promedio >= 60 THEN 'Suficiente'
           ELSE 'Insuficiente'
       END AS clasificacion
FROM estudiantes;

Selles näites kasutame iga õpilase keskmise hindamiseks ja vastava järgu määramiseks CASE-d. Kui keskmine on suurem kui 90 või sellega võrdne, klassifitseeritakse see kui "Väljapaistev". Kui see on vahemikus 80 kuni 89, klassifitseeritakse see "Märkimisväärseks" ja nii edasi. Kui keskmine on alla 60, klassifitseeritakse see "Ebapiisavaks".

Näide 2: tootekategooriate määramine

Kujutage ette, et teil on tabel nimega "products" veergudega "id", "name" ja "price". Soovite funktsiooni CASE abil määrata igale tootele hinna alusel kategooria. Saate seda teha järgmiselt:

SELECT nombre,
       CASE
           WHEN precio > 1000 THEN 'Premium'
           WHEN precio > 500 THEN 'Gama alta'
           WHEN precio > 100 THEN 'Gama media'
           ELSE 'Económico'
       END AS categoria
FROM productos;

Selles näites kasutame iga toote hinna hindamiseks ja vastava kategooria määramiseks sõna CASE. Kui hind on suurem kui 1000, klassifitseeritakse see “Premium”. Kui see on vahemikus 500 kuni 1000, klassifitseeritakse see tipptasemel ja nii edasi. Kui hind on väiksem kui 100 või sellega võrdne, klassifitseeritakse see “ökonoomseks”.

Näide 3: Arvutage allahindlused ostetud koguse põhjal

Oletame, et teil on tabel nimega "müük" veergudega "id", "product" ja "quantity". Soovite arvutada igale müügile rakendatava allahindluse funktsiooni CASE abil ostetud koguse põhjal. Saate seda teha järgmiselt:

SELECT producto,
       CASE
           WHEN cantidad >= 100 THEN 0.20
           WHEN cantidad >= 50 THEN 0.15
           WHEN cantidad >= 20 THEN 0.10
           ELSE 0
       END AS descuento
FROM ventas;

Antud näites kasutame CASE-d iga toote ostetud koguse hindamiseks ja vastava allahindluse arvutamiseks. Kui kogus on suurem või võrdne 100-ga, rakendub 20% allahindlus. Kui see jääb 50 ja 99 vahele, rakendub 15% allahindlus jne. Kui kogus on alla 20, allahindlust ei kohaldata.

Näide 4: arvväärtuste teisendamine vahemikeks

Kujutage ette, et teil on tabel nimega "töötajad" veergudega "id", "nimi" ja "vanus". Soovite teisendada töötajate vanused vahemikeks, kasutades funktsiooni CASE. Saate seda teha järgmiselt:

SELECT nombre,
       CASE
           WHEN edad >= 60 THEN 'Senior'
           WHEN edad >= 40 THEN 'Mediana edad'
           WHEN edad >= 20 THEN 'Joven'
           ELSE 'Menor de edad'
       END AS rango_edad
FROM empleados;

Selles näites kasutame CASE-i iga töötaja vanuse hindamiseks ja vastava vahemiku määramiseks. Kui vanus on 60-st suurem või võrdne, klassifitseeritakse see "eakateks". Kui olete 40–59-aastane, klassifitseeritakse teid "keskealiseks" ja nii edasi. Kui vanus on alla 20 aasta, klassifitseeritakse see "alaealiseks".

Näide 5: siltide määramine mitme tingimuse alusel

Oletame, et teil on tabel nimega "tellimused" veergudega "id", "customer", "total" ja "status". Soovite määrata igale tellimusele sildid selle kogusumma ja oleku alusel, kasutades mitme tingimusega funktsiooni CASE. Saate seda teha järgmiselt:

SELECT cliente,
       CASE
           WHEN total > 1000 AND estado = 'Entregado' THEN 'VIP'
           WHEN total > 500 AND estado = 'Entregado' THEN 'Prioritario'
           WHEN estado = 'Pendiente' THEN 'En proceso'
           ELSE 'Regular'
       END AS etiqueta
FROM pedidos;

Selles näites kasutame mitme tingimusega CASE-d, et hinnata iga tellimuse kogusummat ja olekut ning määrata sobiv silt. Kui kogusumma on suurem kui 1000 ja olek on "Tarnitud", märgitakse see kui "VIP". Kui kogusumma on suurem kui 500 ja olek on „Tarnitud”, märgitakse see kui „Prioriteedi”. Kui olek on "Ootel", on see sildiga "Töötlemisel". Vastasel juhul on sellel silt "Tavaline".

Näide 6: nullväärtuste käsitlemine CASE-ga

Kujutage ette, et teil on tabel nimega "kliendid" veergudega "id", "nimi" ja "e-post". Mõnel kliendil ei pruugi e-kirja salvestatud olla, mistõttu veerus „e-post” kuvatakse nullväärtused. Nende nullväärtuste sobivaks käsitlemiseks saate kasutada funktsiooni CASE. Näiteks:

SELECT nombre,
       CASE
           WHEN email IS NULL THEN 'Sin correo electrónico'
           ELSE email
       END AS informacion_contacto
FROM clientes;

Selles näites kasutame CASE-i, et hinnata, kas veerg „e-post” on tühi. Kui null, kuvatakse tekst "E-posti pole". Vastasel juhul kuvatakse veeru "e-post" tegelik väärtus. See võimaldab meil graatsiliselt käsitleda juhtumeid, kus kontaktandmed puuduvad.

Näide 7: CASE kombineerimine koondfunktsioonidega

Funktsiooni CASE saab kombineerida ka koondfunktsioonidega, nagu SUM, AVG, COUNT jne. Oletame, et teil on tabel nimega "müük" veergudega "id", "product", "quantity" ja "price". Soovite arvutada kogumüügi tootekategooriate kaupa kasutades CASE ja SUM. Saate seda teha järgmiselt:

SELECT
    SUM(CASE WHEN precio > 1000 THEN cantidad ELSE 0 END) AS ventas_premium,
    SUM(CASE WHEN precio <= 1000 THEN cantidad ELSE 0 END) AS ventas_regulares
FROM ventas;

Selles näites kasutame kogumüügi arvutamiseks tootekategooriate kaupa funktsiooni SUM sees sõna CASE. Kui hind on suurem kui 1000, lisatakse summa reale “lisatasu_müük”. Kui hind on väiksem kui 1000 või sellega võrdne, lisatakse summa reale “tavamüük”. See võimaldab meil saada konkreetsetel tingimustel põhinevaid vahesummasid.

Näide 8: CASE kasutamine WHERE-klauslites

Funktsiooni CASE saab kasutada ka WHERE-klauslis kirjete filtreerimiseks konkreetsete tingimuste alusel. Oletame, et teil on tabel nimega "töötajad" veergudega "id", "nimi", "osakond" ja "palk". Soovite sihtida töötajaid, kelle palk on nende osakonna keskmisest kõrgem. Saate seda teha järgmiselt:

SELECT nombre, departamento, salario
FROM empleados
WHERE salario > (
    SELECT AVG(CASE WHEN e.departamento = empleados.departamento THEN e.salario ELSE NULL END)
    FROM empleados e
);

Selles näites kasutame alampäringus klahvi CASE, et arvutada keskmine palk osakondade kaupa. Alampäring võrdleb iga töötaja osakonda praeguse osakonnaga ja arvestab keskmise arvutamisel ainult sama osakonna töötajate palkasid. Seejärel filtreerime põhipäringus välja töötajad, kelle palk on kõrgem kui nende osakonnale arvestatud keskmine.

Näide 9: Arvutatud veergude genereerimine CASE abil

Funktsiooni CASE saab kasutada ka konkreetsete tingimuste alusel arvutatud veergude genereerimiseks. Oletame, et teil on tabel "tellimused" veergudega "id", "klient", "kokku" ja "kuupäev". Soovite luua täiendava veeru nimega "allahindlus", mis rakendab sõltuvalt tellimuse kogusummast erinevaid allahindlusprotsente. Saate seda teha järgmiselt:

SELECT id, cliente, total,
       CASE
           WHEN total > 1000 THEN total * 0.10
           WHEN total > 500 THEN total * 0.05
           ELSE 0
       END AS descuento,
       fecha
FROM pedidos;

Selles näites kasutame arvutatud „allahindluse” veeru loomiseks klahvi CASE. Kui tellimus on kokku suurem kui 1000, rakendub 10% allahindlus. Kui kogusumma on suurem kui 500, rakendub 5% allahindlus. Muul juhul allahindlust ei kohaldata. Seda arvutatud veergu saab kasutada edasiseks analüüsiks või päringutulemustes lisateabe kuvamiseks.

Näide 10: Keerulise loogika rakendamine pesastatud CASE-lausetega

Mõnel juhul peate võib-olla rakendama keerukamat tingimusloogikat, kasutades pesastatud CASE-lauseid. Oletame, et teil on tabel nimega "õpilased" veergudega "id", "name", "math_grade" ja "language_grade". Soovite määrata igale õpilasele kategooria nende matemaatika- ja keelehinnete põhjal. Saate seda teha järgmiselt:

SELECT nombre,
       CASE
           WHEN nota_matematicas >= 90 AND nota_lenguaje >= 90 THEN 'Excelente'
           WHEN nota_matematicas >= 80 AND nota_lenguaje >= 80 THEN 'Notable'
           ELSE
               CASE
                   WHEN nota_matematicas >= 70 OR nota_lenguaje >= 70 THEN 'Regular'
                   ELSE 'Necesita mejorar'
               END
       END AS categoria
FROM estudiantes;

Selles näites kasutame keerukama tingimusloogika rakendamiseks pesastatud CASE-lauseid. Esiteks hindame, kas nii matemaatika kui ka keelehind on 90-st suuremad või sellega võrdsed. Kui jah, määratakse kategooria „Suurepärane”. Seejärel hindame, kas mõlemad hinded on 80-st suuremad või sellega võrdsed. Kui jah, määratakse kategooria „Märkimisväärne”. Kui ükski ülaltoodud tingimustest ei ole täidetud, liigume pesastatud CASE järgmisele tasemele. Siin hindame, kas vähemalt üks hinnetest (matemaatika või keel) on suurem või võrdne 70-ga. Kui jah, määratakse kategooria "Tavaline". Kui ükski tingimus ei ole täidetud, määratakse kategooria "Vajab täiustamist".

Näide 11: päringute optimeerimine CASE-ga

Funktsiooni CASE saab kasutada ka päringute optimeerimiseks ja mitme eraldi päringu vältimiseks. Oletame, et teil on tabel nimega "müük" veergudega "id", "toode", "kogus" ja "kuupäev". Soovite saada ühe päringuga kogumüügi kuus ja kogumüügi aastas. Saate seda teha järgmiselt:

SELECT
    SUM(CASE WHEN MONTH(fecha) = 1 THEN cantidad ELSE 0 END) AS ventas_enero,
    SUM(CASE WHEN MONTH(fecha) = 2 THEN cantidad ELSE 0 END) AS ventas_febrero,
    -- ... (continúa para los demás meses)
    SUM(CASE WHEN YEAR(fecha) = 2022 THEN cantidad ELSE 0 END) AS ventas_2022,
    SUM(CASE WHEN YEAR(fecha) = 2023 THEN cantidad ELSE 0 END) AS ventas_2023
FROM ventas;

Selles näites kasutame funktsiooni SUM sees sõna CASE, et arvutada müügi kogusumma kuu ja aasta kaupa ühes päringus. Iga kuu kohta hindame, kas müügikuupäeva kuu ühtib konkreetse kuuga ja lisame vastava summa. Samamoodi hindame iga aasta kohta, kas müügikuupäeva aasta ühtib konkreetse aastaga ja lisame vastava summa. See võimaldab meil saada kõik kogusummad ühes tõhusas päringus.

Parimad tavad CASE-i kasutamisel MySQL-is

• Kasutage CASE-i ainult vajaduse korral ja vältige selle ülekasutamist, kuna see võib liigsel kasutamisel päringu jõudlust mõjutada.
• Püüdke hoida CASE-väljendid võimalikult lihtsad ja loetavad. Kui loogika muutub liiga keeruliseks, kaaluge selle jagamist mitmeks CASE-avaldisteks või alampäringute kasutamist.
• Kasutage CASE-i koos teiste MySQL-i klauslite ja funktsioonidega, et nende potentsiaali täielikult ära kasutada, näiteks WHERE, ORDER BY, GROUP BY ja liitmisfunktsioonid.
• Olge mitme CASE-avaldise pesastamisel ettevaatlik, kuna see võib muuta teie koodi lugemise ja hooldamise keeruliseks. Vajadusel lisage selgitavad kommentaarid.

Levinud vead CASE-i kasutamisel ja nende vältimine

• Unustage klausel ELSE: Lisage kindlasti klausel ELSE, et käsitleda juhtumeid, kui ükski tingimus ei ole täidetud. Kui pole määratud, määratakse vaikimisi NULL.
• Ärge lõpetage CASE-avaldist sõnaga END: Ärge unustage lõpetada CASE-avaldis alati märksõnaga END. Vastasel juhul saate süntaksivea.
• Ühildumatute andmetüüpide kasutamine: Veenduge, et iga WHEN tingimuse tagastatud tulemused oleksid sama andmetüübiga. Kui kasutate erinevaid andmetüüpe, võite saada ootamatuid tulemusi või vigu.
• Ei arvesta tingimuste järjekorda: CASE tingimusi hinnatakse nende ilmumise järjekorras. Soovitud tulemuste saavutamiseks seadke täpsemad tingimused üldisematest ettepoole.

Alternatiivid CASE-le MySQL-is

Kuigi CASE on võimas funktsioon, võite teatud juhtudel kaaluda mõnda alternatiivi.

• IF Avaldised: Ei IF-funktsioon MySQL-is võimaldab teil hinnata tingimust ja tagastada väärtuse, kui tingimus on tõene, ja teise väärtuse, kui see on väär. See on lihtsam alternatiiv ainulaadsete tingimuste korral.
• Otsingu tabelid: Mõnel juhul saate tingimuste ja vastavate tulemuste salvestamiseks kasutada eraldi otsingutabeleid. Seejärel saate soovitud tulemuste saamiseks need tabelid põhitabeliga ühendada.
• Salvestatud vaated või funktsioonid: kui teil on keerulisi päringuid, mis kasutavad CASE-d korduvalt, võite kaaluda vaadete loomist või salvestatud funktsioonid selle loogika kapseldamiseks ja järgnevate päringute lihtsustamiseks.

Korduma kippuvad küsimused CASE kohta MySQL-is

1. Kas ma saan kasutada CASE-d koos teiste MySQL-i funktsioonidega?

Jah, saate CASE-d kasutada koos teiste MySQL-i funktsioonidega, nagu koondfunktsioonid (SUM, AVG, COUNT jne), kuupäeva ja kellaaja funktsioonid (YEAR, MONTH, DAY jne), stringifunktsioonid (CONCAT, SUBSTRING, LENGTH jne) ja palju muud.

2. Kas tingimuste WHEN arv, mida saan CASE-avaldises kasutada, on piiratud?

CASE-avaldises kasutatavate WHEN-tingimuste arvul pole konkreetset piirangut. Siiski pidage meeles, et koodi loetavust ja päringu jõudlust võivad mõjutada suur hulk tingimusi. Kui teil on palju tingimusi, kaaluge loogika lihtsustamist või jagamist mitmeks CASE-avaldiseks.

3. Kas ma saan CASE-avaldises kasutada alampäringuid?

Jah, saate CASE-avaldises kasutada alampäringuid nii tingimustes WHEN kui ka SIIS tulemustes. See võimaldab teiste päringute tulemuste põhjal teha keerukamaid arvutusi või võrdlusi.

4. Kuidas ma saan käsitleda nullväärtusi CASE-avaldises?

Saate käsitleda nullväärtusi CASE-avaldises tingimusega IS NULL või IS NOT NULL. Näiteks saate kasutada veergu CASE WHEN IS NULL THEN 'Null value' ELSE veeru LÕPP, et määrata konkreetne väärtus, kui veerg on null, ja tagastada tegelik väärtus, kui see ei ole.

Juhtumi järeldused MySQL-is

MySQL-i funktsioon CASE on võimas ja mitmekülgne tööriist, mis võimaldab teil oma päringutes teha tingimuslikke toiminguid. CASE abil saate hinnata erinevaid tingimusi ja saada konkreetseid tulemusi olenevalt sellest, kas need tingimused on täidetud või mitte. Selles artiklis esitatud näited annavad teile kindla aluse, et alustada CASE-i kasutamist oma päringutes ja kohandada seda oma konkreetsetele vajadustele.

CASE-i kasutamisel pidage meeles parimaid tavasid, näiteks hoidke avaldised lihtsad ja loetavad, kasutage CASE-i koos teiste MySQL-i klauslite ja funktsioonidega ning kaaluge vajadusel alternatiive. Harjutamise ja katsetamise abil saate CASE-i MySQL-is täiel määral ära kasutada ning oma päringute tõhusust ja loetavust parandada.

Kui teil on lisaküsimusi või vajate rohkem näiteid, otsige julgelt lisaressursse või tutvuge ametliku MySQL-i dokumentatsiooniga. Jätkake CASE-i võimsuse uurimist ja ärakasutamist oma andmebaasiprojektides!

Lisaressursid:

• Funktsiooni CASE ametlik MySQL-i dokumentatsioon
• Täiendavad näited CASE-i kasutamisest MySQL-is