Primer v MySQL: 11 praktičnih primerov

Informatec Digital » Baze podatkov » Primer v MySQL: 11 praktičnih primerov

Funkcija CASE v MySQL je zmogljivo orodje, ki vam omogoča izvajanje pogojnih operacij znotraj vaših poizvedb. S CASE lahko ocenite različne pogoje in vrnete določene rezultate glede na to, ali so ti pogoji izpolnjeni ali ne. To funkcijo bomo razložili s praktičnimi primeri, ki vam bodo pomagali obvladati uporabo CASE v MySQL in izboljšati vaše veščine pri upravljanju baze podatkov.

Kaj je funkcija CASE v MySQL?

Funkcija CASE v MySQL je pogojni izraz, ki omogoča ocenjevanje različnih pogojev in vrnitev določenih rezultatov, odvisno od tega, ali so ti pogoji izpolnjeni. Je zelo uporabno orodje za izvajanje logičnih operacij znotraj poizvedb in pridobivanje prilagojenih rezultatov na podlagi določenih meril.

CASE deluje podobno kot niz stavkov IF-THEN-ELSE, kjer lahko določite več pogojev in vrednosti, ki jih želite vrniti, ko so ti pogoji izpolnjeni. Če nobeden od pogojev ni izpolnjen, lahko definirate privzeto vrednost s klavzulo ELSE.

Osnovna sintaksa CASE v MySQL

Osnovna sintaksa funkcije CASE v MySQL je naslednja:

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

Tukaj je razlaga vsakega dela sintakse:

• WHEN: Določa pogoj, ki ga je treba oceniti.
• THEN: Označuje rezultat, ki bo vrnjen, če je izpolnjen ustrezen pogoj.
• ELSE: (Izbirno) Podaja rezultat, ki se vrne, če ni izpolnjen noben od zgornjih pogojev.
• END: Označi konec izraza CASE.

Zdaj, ko poznate osnovno sintakso, poglejmo nekaj praktičnih primerov!

Primer 1: Razvrstite študente glede na njihovo povprečje

Recimo, da imate tabelo z imenom "students" z naslednjimi stolpci: "id", "name" in "avg". Študente želite razvrstiti glede na njihov srednji uspeh s funkcijo CASE. To lahko storite tako:

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;

V tem primeru uporabljamo CASE za ovrednotenje povprečja vsakega študenta in dodelitev ustreznega ranga. Če je povprečje večje ali enako 90, je razvrščeno kot "Izjemno". Če je med 80 in 89, je razvrščen kot "izjemen" in tako naprej. Če je povprečje nižje od 60, je razvrščeno kot "nezadostno".

Primer 2: Dodeljevanje kategorij izdelkov

Predstavljajte si, da imate tabelo z imenom »izdelki« s stolpci »id«, »ime« in »cena«. Vsakemu izdelku želite dodeliti kategorijo glede na njegovo ceno s funkcijo CASE. To lahko storite tako:

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;

V tem primeru uporabljamo CASE za oceno cene vsakega izdelka in dodelitev ustrezne kategorije. Če je cena višja od 1000, je razvrščena kot "Premium". Če je med 500 in 1000, je razvrščen kot "High-End" in tako naprej. Če je cena nižja ali enaka 100, je razvrščena kot "varčna".

Primer 3: Izračun popustov glede na kupljeno količino

Recimo, da imate tabelo z imenom »prodaja« s stolpci »id«, »product« in »quantity«. Želite izračunati popust, ki se uporabi za vsako prodajo na podlagi količine, kupljene s funkcijo CASE. To lahko storite tako:

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;

V tem primeru uporabljamo CASE za ovrednotenje kupljene količine vsakega izdelka in izračun ustreznega popusta. Če je količina večja ali enaka 100, se upošteva 20% popust. Če je med 50 in 99, se upošteva 15% popust itd. Če je količina manjša od 20, popust ne velja.

Primer 4: Pretvarjanje številskih vrednosti v obsege

Predstavljajte si, da imate tabelo z imenom »zaposleni« s stolpci »id«, »ime« in »starost«. Starost zaposlenih želite pretvoriti v obsege s funkcijo CASE. To lahko storite tako:

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;

V tem primeru uporabljamo CASE za oceno starosti vsakega zaposlenega in dodelitev ustreznega obsega. Če je starost večja ali enaka 60, je razvrščena kot "starejša". Če ste stari med 40 in 59 let, ste razvrščeni kot »srednjih let« itd. Če je starost manjša od 20 let, je razvrščena kot "mladoletna".

Primer 5: Dodeljevanje oznak na podlagi več pogojev

Recimo, da imate tabelo z imenom »naročila« s stolpci »id«, »stranka«, »skupaj« in »status«. Vsakemu naročilu želite dodeliti oznake na podlagi njegove skupne vrednosti in statusa z uporabo funkcije CASE z več pogoji. To lahko storite tako:

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;

V tem primeru uporabljamo CASE z več pogoji za ovrednotenje vsote in statusa vsakega naročila ter dodelitev ustrezne oznake. Če je skupni znesek večji od 1000 in je status »Dostavljeno«, je označen kot »VIP«. Če je vsota večja od 500 in je stanje »Dostavljeno«, je označeno kot »Prednostno«. Če je stanje »V teku«, je označeno kot »V postopku«. V nasprotnem primeru je označen kot »Regular«.

Primer 6: Ravnanje z ničelnimi vrednostmi s CASE

Predstavljajte si, da imate tabelo z imenom »stranke« s stolpci »id«, »ime« in »e-pošta«. Nekatere stranke morda nimajo zapisane e-pošte, kar bi povzročilo ničelne vrednosti v stolpcu »e-pošta«. Za ustrezno obdelavo teh ničelnih vrednosti lahko uporabite funkcijo CASE. Na primer:

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

V tem primeru uporabimo CASE, da ocenimo, ali je stolpec »e-pošta« nič. Če je nič, se prikaže besedilo »Brez e-pošte«. V nasprotnem primeru se prikaže dejanska vrednost stolpca "email". To nam omogoča elegantno obravnavo primerov, ko manjkajo kontaktni podatki.

Primer 7: Kombinacija CASE z agregatnimi funkcijami

Funkcijo CASE je mogoče kombinirati tudi z agregatnimi funkcijami, kot so SUM, AVG, COUNT itd. Recimo, da imate tabelo z imenom »prodaja« s stolpci »id«, »product«, »quantity« in »price«. Želite izračunati skupno prodajo po kategoriji izdelkov z uporabo CASE in SUM. To lahko storite tako:

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;

V tem primeru uporabljamo CASE znotraj funkcije SUM za izračun skupne prodaje po kategoriji izdelkov. Če je cena višja od 1000, se znesek doda »premium_sales«. Če je cena nižja ali enaka 1000, se znesek doda v »regular_sales«. To nam omogoča, da dobimo delne vsote na podlagi posebnih pogojev.

Primer 8: Uporaba CASE v stavkih WHERE

Funkcijo CASE lahko uporabite tudi v klavzuli WHERE za filtriranje zapisov na podlagi posebnih pogojev. Recimo, da imate tabelo z imenom »zaposleni« s stolpci »id«, »ime«, »oddelek« in »plača«. Ciljati želite na zaposlene, katerih plača je nad povprečjem njihovega oddelka. To lahko storite tako:

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
);

V tem primeru uporabljamo CASE v podpoizvedbi za izračun povprečne plače po oddelkih. Podpoizvedba primerja oddelek vsakega zaposlenega s trenutnim oddelkom in za izračun povprečja upošteva samo plače zaposlenih v istem oddelku. Nato v glavni poizvedbi filtriramo zaposlene, katerih plača je višja od izračunanega povprečja za njihov oddelek.

Primer 9: Generiranje izračunanih stolpcev s CASE

Funkcijo CASE lahko uporabite tudi za ustvarjanje izračunanih stolpcev na podlagi posebnih pogojev. Recimo, da imate tabelo z imenom »naročila« s stolpci »id«, »stranka«, »skupaj« in »datum«. Želite ustvariti dodaten stolpec z imenom »popust«, ki uporablja različne odstotke popusta glede na skupno naročilo. To lahko storite tako:

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;

V tem primeru uporabljamo CASE za ustvarjanje izračunanega stolpca »popust«. Če je skupno naročilo večje od 1000, se upošteva 10% popust. Če je skupni znesek večji od 500, se upošteva 5% popust. V drugih primerih popust ne velja. Ta izračunani stolpec lahko uporabite za nadaljnjo analizo ali za prikaz dodatnih informacij v rezultatih poizvedbe.

Primer 10: Implementacija kompleksne logike z ugnezdenimi stavki CASE

V nekaterih primerih boste morda morali implementirati bolj zapleteno pogojno logiko z uporabo ugnezdenih stavkov CASE. Recimo, da imate tabelo z imenom "students" s stolpci "id", "name", "math_grade" in "language_grade". Vsakemu študentu želite dodeliti kategorijo glede na njegove ocene pri matematiki in jeziku. To lahko storite tako:

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;

V tem primeru uporabljamo ugnezdene stavke CASE za implementacijo bolj zapletene pogojne logike. Najprej ocenimo, ali sta ocena matematike in jezika enaka ali večja od 90. Če je tako, se dodeli kategorija »Odlično«. Nato ocenimo, ali sta obe oceni večji ali enaki 80. Če je tako, je dodeljena kategorija »Pomembno«. Če nobeden od zgornjih pogojev ni izpolnjen, se premaknemo na naslednjo raven ugnezdenega CASE. Tukaj ocenimo, ali je vsaj ena od ocen (matematika ali jezik) višja ali enaka 70. Če je tako, se dodeli kategorija »Regular«. Če nobeden od pogojev ni izpolnjen, se dodeli kategorija »Potrebne so izboljšave«.

Primer 11: Optimiziranje poizvedb s CASE

Funkcijo CASE lahko uporabite tudi za optimizacijo poizvedb in izogibanje več ločenim poizvedbam. Recimo, da imate tabelo z imenom »prodaja« s stolpci »id«, »product«, »quantity« in »date«. V eni poizvedbi želite pridobiti skupno prodajo na mesec in skupno prodajo na leto. To lahko storite tako:

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;

V tem primeru uporabljamo CASE znotraj funkcije SUM za izračun skupnih prodaj po mesecih in letih v eni poizvedbi. Za vsak mesec ocenimo, ali se mesec datuma prodaje ujema z določenim mesecem in dodamo ustrezen znesek. Podobno za vsako leto ocenimo, ali se leto datuma prodaje ujema z določenim letom, in dodamo ustrezen znesek. To nam omogoča, da dobimo vse vsote v eni učinkoviti poizvedbi.

Najboljše prakse pri uporabi CASE v MySQL

• CASE uporabite le, kadar je to potrebno, in se izogibajte njegovi pretirani uporabi, saj lahko vpliva na učinkovitost poizvedbe, če se uporablja pretirano.
• Poskusite ohraniti CASE izraze čim bolj preproste in berljive. Če logika postane preveč zapletena, razmislite o razdelitvi na več izrazov CASE ali uporabi podpoizvedb.
• Uporabite CASE v kombinaciji z drugimi stavki in funkcijami MySQL, da v celoti izkoristite njihov potencial, na primer v WHERE, ORDER BY, GROUP BY in združevalne funkcije.
• Bodite previdni pri gnezdenju več izrazov CASE, saj lahko to oteži branje in vzdrževanje vaše kode. Po potrebi dodajte pojasnjevalne komentarje.

Pogoste napake pri uporabi CASE in kako se jim izogniti

• Pozabite na stavek ELSE: Ne pozabite vključiti klavzule ELSE za obravnavo primerov, ko ni izpolnjen nobeden od pogojev. Če ni navedeno, bo privzeto dodeljena NULL.
• Ne zaključite izraza CASE z END: Ne pozabite, da izraz CASE vedno končate s ključno besedo END. V nasprotnem primeru boste prejeli sintaktično napako.
• Uporaba nezdružljivih tipov podatkov: Prepričajte se, da so rezultati, ki jih vrne vsak pogoj WHEN, iste vrste podatkov. Če mešate vrste podatkov, lahko dobite nepričakovane rezultate ali napake.
• Ne glede na vrstni red pogojev: Pogoji v CASE so ovrednoteni v vrstnem redu, v katerem so prikazani. Bodite prepričani, da bolj specifične pogoje postavite pred bolj splošne, da dosežete želene rezultate.

Alternative za CASE v MySQL

Čeprav je CASE zmogljiva funkcija, lahko v nekaterih primerih razmislite o nekaterih alternativah:

• Izrazi IF: The Funkcija IF v MySQL vam omogoča, da ocenite pogoj in vrnete vrednost, če je pogoj resničen, in drugo vrednost, če je napačen. Je enostavnejša alternativa za primere edinstvenih pogojev.
• Iskalne tabele: V nekaterih primerih lahko uporabite ločene iskalne tabele za shranjevanje pogojev in ustreznih rezultatov. Nato lahko te tabele združite z glavno tabelo, da dobite želene rezultate.
• Shranjeni pogledi ali funkcije: Če imate zapletene poizvedbe, ki ponavljajoče se uporabljajo CASE, lahko razmislite o ustvarjanju pogledov oz shranjene funkcije da zaokroži to logiko in poenostavi nadaljnje poizvedbe.

Pogosto zastavljena vprašanja o CASE v MySQL

1. Ali lahko uporabljam CASE v kombinaciji z drugimi funkcijami MySQL?

Da, CASE lahko uporabljate v kombinaciji z drugimi funkcijami MySQL, kot so agregatne funkcije (SUM, AVG, COUNT itd.), funkcije datuma in časa (LETO, MESEC, DAN itd.), nizovne funkcije (CONCAT, SUBSTRING, LENGTH itd.) in več.

2. Ali obstaja omejitev števila pogojev WHEN, ki jih lahko uporabim v izrazu CASE?

Za število pogojev WHEN, ki jih lahko uporabite v izrazu CASE, ni posebne omejitve. Vendar ne pozabite, da lahko veliko število pogojev vpliva na berljivost kode in zmogljivost poizvedbe. Če imate veliko pogojev, razmislite o poenostavitvi logike ali razdelitvi na več izrazov CASE.

3. Ali lahko uporabim podpoizvedbe znotraj izraza CASE?

Da, podpoizvedbe lahko uporabite znotraj izraza CASE, tako v pogojih WHEN kot v rezultatih THEN. To vam omogoča izvajanje bolj zapletenih izračunov ali primerjav na podlagi rezultatov drugih poizvedb.

4. Kako lahko obravnavam ničelne vrednosti v izrazu CASE?

Ničelne vrednosti v izrazu CASE lahko obravnavate z uporabo pogoja IS NULL ali IS NOT NULL. Uporabite lahko na primer CASE WHEN column IS NULL THEN 'Null value' ELSE column END, da dodelite določeno vrednost, ko je stolpec ničen, in vrnete dejansko vrednost, ko ni.

Zaključki primera v Mysqlu

Funkcija CASE v MySQL je zmogljivo in vsestransko orodje, ki vam omogoča izvajanje pogojnih operacij znotraj vaših poizvedb. S CASE lahko ocenite različne pogoje in vrnete določene rezultate glede na to, ali so ti pogoji izpolnjeni ali ne. Primeri, predstavljeni v tem članku, vam dajejo trdne temelje, da začnete uporabljati CASE v lastnih poizvedbah in ga prilagodite svojim posebnim potrebam.

Pri uporabi CASE upoštevajte najboljše prakse, kot so preprosti in berljivi izrazi, uporaba CASE v kombinaciji z drugimi stavki in funkcijami MySQL ter po potrebi upoštevanje alternativ. Z vajo in eksperimentiranjem boste lahko v celoti izkoristili CASE v MySQL in izboljšali učinkovitost ter berljivost svojih poizvedb.

Če imate dodatna vprašanja ali potrebujete več primerov, lahko poiščete dodatne vire ali si ogledate uradno dokumentacijo MySQL. Nadaljujte z raziskovanjem in izkoriščanjem moči CASE v svojih projektih baze podatkov!

Dodatni viri:

• Uradna dokumentacija MySQL o funkciji CASE
• Dodatni primeri uporabe CASE v MySQL