MySQL atvejis: 11 praktinių pavyzdžių

Informatec Digital » Duomenų bazės » MySQL atvejis: 11 praktinių pavyzdžių

MySQL funkcija CASE yra galingas įrankis, leidžiantis atlikti sąlygines operacijas jūsų užklausose. Naudodami CASE galite įvertinti skirtingas sąlygas ir pateikti konkrečius rezultatus, priklausomai nuo to, ar tos sąlygos tenkinamos, ar ne. Paaiškinsime šią funkciją praktiniais pavyzdžiais, kurie padės įsisavinti CASE naudojimą MySQL ir pagerinti duomenų bazių valdymo įgūdžius.

Kas yra CASE funkcija MySQL?

„MySQL“ funkcija CASE yra sąlyginis reiškinys, leidžiantis įvertinti skirtingas sąlygas ir pateikti konkrečius rezultatus, priklausomai nuo to, ar tos sąlygos tenkinamos. Tai labai naudinga priemonė logines operacijas atlikti užklausose ir gauti pritaikytus rezultatus pagal konkrečius kriterijus.

CASE veikia panašiai kaip IF-THEN-ELSE teiginių serija, kur galite nurodyti kelias sąlygas ir reikšmes, kurios turi būti grąžinamos, kai šios sąlygos įvykdomos. Jei netenkinama nė viena iš sąlygų, galite apibrėžti numatytąją reikšmę naudodami ELSE sąlygą.

Pagrindinė CASE sintaksė MySQL

Pagrindinė CASE funkcijos sintaksė MySQL yra tokia:

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

Čia yra kiekvienos sintaksės dalies paaiškinimas:

• WHEN: nurodo sąlygą, kurią reikia įvertinti.
• THEN: Nurodo rezultatą, kuris bus grąžintas, jei bus įvykdyta atitinkama sąlyga.
• ELSE: (Pasirenkama) Nurodo rezultatą, kurį reikia grąžinti, jei netenkinama nė viena iš pirmiau nurodytų sąlygų.
• END: pažymi CASE išraiškos pabaigą.

Dabar, kai žinote pagrindinę sintaksę, panagrinėkime keletą praktinių pavyzdžių!

1 pavyzdys: suskirstykite mokinius pagal jų vidurkį

Tarkime, kad turite lentelę „studentai“ su šiais stulpeliais: „id“, „name“ ir „avg“. Naudodami CASE funkciją norite reitinguoti studentus pagal jų GPA. Tai galite padaryti taip:

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;

Šiame pavyzdyje mes naudojame CASE, kad įvertintume kiekvieno mokinio vidurkį ir priskirtume atitinkamą reitingą. Jei vidurkis yra didesnis arba lygus 90, jis klasifikuojamas kaip „Nuostabus“. Jei jis yra nuo 80 iki 89, jis klasifikuojamas kaip „Įsidėmėtinas“ ir pan. Jei vidurkis yra mažesnis nei 60, jis klasifikuojamas kaip „Nepakankamas“.

2 pavyzdys: produktų kategorijų priskyrimas

Įsivaizduokite, kad turite lentelę „produktai“ su stulpeliais „id“, „pavadinimas“ ir „kaina“. Kiekvienam produktui norite priskirti kategoriją pagal jo kainą, naudodami funkciją CASE. Tai galite padaryti taip:

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;

Šiame pavyzdyje mes naudojame CASE, norėdami įvertinti kiekvieno produkto kainą ir priskirti atitinkamą kategoriją. Jei kaina didesnė nei 1000, ji klasifikuojama kaip „Premium“. Jei jis yra nuo 500 iki 1000, jis klasifikuojamas kaip „aukštos klasės“ ir pan. Jei kaina yra mažesnė arba lygi 100, ji klasifikuojama kaip „Ekonomiška“.

3 pavyzdys: Apskaičiuokite nuolaidas pagal įsigytą kiekį

Tarkime, kad turite lentelę „pardavimas“ su stulpeliais „id“, „product“ ir „quantity“. Kiekvienam išpardavimui taikomą nuolaidą norite apskaičiuoti pagal kiekį, įsigytą naudojant funkciją CASE. Tai galite padaryti taip:

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;

Šiame pavyzdyje mes naudojame CASE, norėdami įvertinti kiekvienos prekės įsigytą kiekį ir apskaičiuoti atitinkamą nuolaidą. Jei kiekis didesnis arba lygus 100, taikoma 20% nuolaida. Jei jis yra nuo 50 iki 99, taikoma 15% nuolaida ir pan. Jei kiekis mažesnis nei 20, nuolaida netaikoma.

4 pavyzdys: skaitinių reikšmių konvertavimas į diapazonus

Įsivaizduokite, kad turite lentelę „darbuotojai“ su stulpeliais „id“, „vardas“ ir „amžius“. Norite konvertuoti darbuotojų amžių į diapazonus naudodami funkciją CASE. Tai galite padaryti taip:

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;

Šiame pavyzdyje mes naudojame CASE, norėdami įvertinti kiekvieno darbuotojo amžių ir priskirti atitinkamą diapazoną. Jei amžius yra didesnis arba lygus 60 metų, jis priskiriamas „senjorams“. Jei jums yra nuo 40 iki 59 metų, esate priskiriami „vidutinio amžiaus žmonėms“ ir pan. Jei amžius yra mažesnis nei 20 metų, jis klasifikuojamas kaip „Nepilnametis“.

5 pavyzdys: etikečių priskyrimas pagal kelias sąlygas

Tarkime, kad turite lentelę „užsakymai“ su stulpeliais „id“, „customer“, „total“ ir „status“. Kiekvienam užsakymui norite priskirti etiketes pagal jo bendrą sumą ir būseną, naudodami funkciją CASE su keliomis sąlygomis. Tai galite padaryti taip:

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;

Šiame pavyzdyje naudojame CASE su keliomis sąlygomis, kad įvertintume kiekvieno užsakymo sumą ir būseną bei priskirtume atitinkamą etiketę. Jei bendra suma yra didesnė nei 1000, o būsena yra „Pristatyta“, ji pažymima kaip „VIP“. Jei bendra suma yra didesnė nei 500, o būsena yra „Pristatyta“, ji pažymima kaip „Prioritetinis“. Jei būsena yra „Laukiama“, ji pažymėta kaip „Apdorojama“. Kitu atveju jis pažymėtas kaip „Įprastas“.

6 pavyzdys: Nulinių reikšmių tvarkymas naudojant CASE

Įsivaizduokite, kad turite lentelę „klientai“ su stulpeliais „id“, „vardas“ ir „el. paštas“. Kai kurie klientai gali neturėti el. laiško, todėl stulpelyje „el. paštas“ bus rodomos nulinės reikšmės. Galite naudoti CASE funkciją, kad tinkamai apdorotumėte šias nulines reikšmes. Pavyzdžiui:

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

Šiame pavyzdyje mes naudojame CASE, kad įvertintume, ar stulpelis „email“ yra nulinis. Jei nulinis, rodomas tekstas „No email“. Kitu atveju rodoma tikroji stulpelio „email“ reikšmė. Tai leidžia mums maloniai tvarkyti atvejus, kai trūksta kontaktinės informacijos.

7 pavyzdys: CASE derinimas su agregacinėmis funkcijomis

Funkciją CASE taip pat galima derinti su agregacinėmis funkcijomis, tokiomis kaip SUM, AVG, COUNT ir kt. Tarkime, kad turite lentelę „pardavimas“ su stulpeliais „id“, „produktas“, „kiekis“ ir „kaina“. Norite apskaičiuoti bendrą pardavimą pagal produktų kategoriją naudodami CASE ir SUM. Tai galite padaryti taip:

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;

Šiame pavyzdyje funkcijoje SUM naudojame CASE, kad apskaičiuotume bendrą pardavimą pagal produktų kategoriją. Jei kaina didesnė nei 1000, suma pridedama prie „premium_sales“. Jei kaina yra mažesnė arba lygi 1000, suma pridedama prie „regular_sales“. Tai leidžia mums gauti tarpines sumas pagal konkrečias sąlygas.

8 pavyzdys: CASE naudojimas WHERE sąlygose

Funkciją CASE taip pat galima naudoti sąlygoje WHERE, kad būtų galima filtruoti įrašus pagal konkrečias sąlygas. Tarkime, kad turite lentelę „darbuotojai“ su stulpeliais „id“, „vardas“, „departamentas“ ir „alga“. Norite nukreipti į darbuotojus, kurių atlyginimas yra didesnis nei jų skyriaus vidurkis. Tai galite padaryti taip:

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

Šiame pavyzdyje antrinėje užklausoje naudojame CASE, kad apskaičiuotume vidutinį atlyginimą pagal skyrių. Antrinėje užklausoje lyginamas kiekvieno darbuotojo skyrius su dabartiniu skyriumi ir apskaičiuojant vidurkį atsižvelgiama tik į to paties skyriaus darbuotojų atlyginimus. Tada pagrindinėje užklausoje išfiltruojame darbuotojus, kurių atlyginimas didesnis nei jų skyriui priskaičiuotas vidurkis.

9 pavyzdys: apskaičiuotų stulpelių generavimas naudojant CASE

Funkciją CASE taip pat galima naudoti apskaičiuotiems stulpeliams generuoti pagal konkrečias sąlygas. Tarkime, kad turite lentelę pavadinimu „užsakymai“ su stulpeliais „id“, „customer“, „total“ ir „data“. Norite sukurti papildomą stulpelį „nuolaida“, kuriame būtų taikomi skirtingi nuolaidos procentai, atsižvelgiant į bendrą užsakymo sumą. Tai galite padaryti taip:

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;

Šiame pavyzdyje mes naudojame CASE, kad sukurtume apskaičiuotą „nuolaidos“ stulpelį. Jei bendras užsakymas didesnis nei 1000, taikoma 10% nuolaida. Jei bendra suma didesnė nei 500, taikoma 5% nuolaida. Visais kitais atvejais nuolaida netaikoma. Šis apskaičiuotas stulpelis gali būti naudojamas tolesnei analizei arba papildomai informacijai pateikti užklausos rezultatuose.

10 pavyzdys: Sudėtingos logikos įgyvendinimas su įdėtais CASE sakiniais

Kai kuriais atvejais gali tekti įdiegti sudėtingesnę sąlyginę logiką naudojant įdėtus CASE sakinius. Tarkime, kad turite lentelę „mokiniai“ su stulpeliais „id“, „vardas“, „matematikos_klasis“ ir „kalbos_klasis“. Kiekvienam mokiniui norite priskirti kategoriją pagal matematikos ir kalbos pažymius. Tai galite padaryti taip:

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;

Šiame pavyzdyje naudojame įdėtus CASE sakinius, kad įgyvendintume sudėtingesnę sąlyginę logiką. Pirmiausia įvertiname, ar matematikos ir kalbos pažymiai yra didesni arba lygūs 90. Jei taip, priskiriama kategorija „Puikiai“. Tada įvertiname, ar abu pažymiai yra didesni arba lygūs 80. Jei taip, priskiriama kategorija „Pažymėtina“. Jei neįvykdoma nė viena iš aukščiau nurodytų sąlygų, pereiname į kitą įdėto CASE lygį. Čia vertiname, ar bent vienas iš pažymių (matematikos ar kalbos) yra didesnis ar lygus 70. Jei taip, priskiriama kategorija „Įprastas“. Jei netenkinama nė viena iš sąlygų, priskiriama kategorija „Reikia tobulinti“.

11 pavyzdys: užklausų optimizavimas naudojant CASE

Funkciją CASE taip pat galima naudoti norint optimizuoti užklausas ir išvengti kelių atskirų užklausų. Tarkime, kad turite lentelę „pardavimas“ su stulpeliais „id“, „produktas“, „kiekis“ ir „data“. Vienoje užklausoje norite gauti bendrą pardavimą per mėnesį ir bendrą pardavimą per metus. Tai galite padaryti taip:

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;

Šiame pavyzdyje funkcijoje SUM naudojame CASE, kad apskaičiuotume pardavimo sumas pagal mėnesį ir metus vienoje užklausoje. Kiekvienam mėnesiui įvertiname, ar pardavimo datos mėnuo atitinka konkretų mėnesį ir pridedame atitinkamą sumą. Analogiškai kiekvieniems metams įvertiname, ar pardavimo datos metai sutampa su konkrečiais metais ir pridedame atitinkamą sumą. Tai leidžia mums gauti visas bendras sumas vienoje efektyvioje užklausoje.

Geriausia praktika naudojant CASE MySQL

• Naudokite CASE tik tada, kai reikia, ir nenaudokite jos per daug, nes ji gali turėti įtakos užklausos našumui, jei naudojama per daug.
• Stenkitės, kad CASE posakiai būtų kuo paprastesni ir įskaitomi. Jei logika tampa per sudėtinga, apsvarstykite galimybę ją suskaidyti į kelias CASE išraiškas arba naudoti antrines užklausas.
• Naudokite CASE kartu su kitomis MySQL sąlygomis ir funkcijomis, kad išnaudotumėte visas jų galimybes, pvz., WHERE, ORDER BY, GRUPUOTI PAGAL ir agregavimo funkcijas.
• Būkite atsargūs įdėdami kelias CASE išraiškas, nes dėl to kodą gali būti sunku nuskaityti ir prižiūrėti. Jei reikia, pridėkite aiškinamųjų komentarų.

Dažnos klaidos naudojant CASE ir kaip jų išvengti

• Pamirškite ELSE sąlygą: Būtinai įtraukite ELSE sąlygą, kad galėtumėte tvarkyti atvejus, kai netenkinama nė viena iš sąlygų. Jei nenurodyta, pagal numatytuosius nustatymus bus priskirta NULL.
• Neužbaikite CASE išraiškos su END: Nepamirškite, kad CASE išraiška visada užbaigiama raktiniu žodžiu END. Priešingu atveju gausite sintaksės klaidą.
• Naudojant nesuderinamus duomenų tipus: Įsitikinkite, kad kiekvienos sąlygos WHEN pateikti rezultatai yra to paties duomenų tipo. Jei sumaišysite duomenų tipus, galite gauti netikėtų rezultatų arba klaidų.
• Neatsižvelgiant į sąlygų eilę: CASE sąlygos įvertinamos tokia tvarka, kokia jos pasirodo. Būtinai nustatykite konkretesnes sąlygas prieš bendresnes, kad gautumėte norimus rezultatus.

CASE alternatyvos MySQL

Nors CASE yra galinga funkcija, tam tikrais atvejais galite apsvarstyti keletą alternatyvų:

• IF išraiškos: IF funkcija MySQL leidžia įvertinti sąlygą ir grąžinti reikšmę, jei sąlyga teisinga, ir kitą reikšmę, jei ji klaidinga. Tai paprastesnė alternatyva unikalių sąlygų atvejais.
• Paieškos lentelės: kai kuriais atvejais sąlygoms ir atitinkamiems rezultatams saugoti galite naudoti atskiras paieškos lenteles. Tada šias lenteles galite sujungti su pagrindine lentele, kad gautumėte norimus rezultatus.
• Išsaugoti vaizdai arba funkcijos: Jei turite sudėtingų užklausų, kuriose CASE naudojamas nuolat, galite apsvarstyti galimybę sukurti rodinius arba saugomos funkcijos kad būtų galima apibendrinti tą logiką ir supaprastinti vėlesnes užklausas.

Dažnai užduodami klausimai apie CASE MySQL

1. Ar galiu naudoti CASE kartu su kitomis MySQL funkcijomis?

Taip, CASE galite naudoti kartu su kitomis MySQL funkcijomis, tokiomis kaip agregacinės funkcijos (SUM, AVG, COUNT ir kt.), datos ir laiko funkcijos (YEAR, MONTH, DAY ir kt.), eilutės funkcijos (CONCAT, SUBSTRING, LENGTH ir kt.).

2. Ar yra apribojimas sąlygų WHEN, kurias galiu naudoti CASE išraiškoje, skaičius?

Nėra konkretaus apribojimo sąlygų WHEN, kurią galite naudoti CASE išraiškoje, skaičius. Tačiau atminkite, kad daug sąlygų gali turėti įtakos kodo skaitomumui ir užklausos našumui. Jei turite daug sąlygų, apsvarstykite galimybę supaprastinti logiką arba suskaidyti ją į kelias CASE išraiškas.

3. Ar galiu naudoti antrines užklausas CASE išraiškoje?

Taip, CASE išraiškoje galite naudoti antrines užklausas tiek WHEN sąlygose, tiek tada rezultatuose. Tai leidžia atlikti sudėtingesnius skaičiavimus ar palyginimus pagal kitų užklausų rezultatus.

4. Kaip galiu tvarkyti nulines reikšmes CASE išraiškoje?

Nulines reikšmes CASE išraiškoje galite tvarkyti naudodami sąlygą IS NULL arba IS NOT NULL. Pavyzdžiui, galite naudoti CASE WHEN stulpelį NULL, THEN 'Null reikšmė' ELSE stulpelio END, kad priskirtumėte konkrečią reikšmę, kai stulpelis yra nulinis, ir grąžintumėte tikrąją vertę, kai jos nėra.

Bylos išvados naudojant MySQL

CASE funkcija MySQL yra galingas ir universalus įrankis, leidžiantis atlikti sąlygines operacijas su užklausomis. Naudodami CASE galite įvertinti skirtingas sąlygas ir pateikti konkrečius rezultatus, priklausomai nuo to, ar tos sąlygos tenkinamos, ar ne. Šiame straipsnyje pateikti pavyzdžiai suteikia tvirtą pagrindą pradėti naudoti CASE savo užklausose ir pritaikyti ją pagal savo poreikius.

Naudodami CASE, nepamirškite laikytis geriausios praktikos, pavyzdžiui, išlaikyti išraiškas paprastas ir skaitomas, naudoti CASE kartu su kitomis MySQL sąlygomis ir funkcijomis ir, kai tinkama, apsvarstyti alternatyvas. Praktikuodami ir eksperimentuodami galėsite visapusiškai išnaudoti CASE galimybes MySQL ir pagerinti užklausų efektyvumą bei skaitomumą.

Jei turite papildomų klausimų ar reikia daugiau pavyzdžių, nedvejodami ieškokite papildomų išteklių arba peržiūrėkite oficialią MySQL dokumentaciją. Toliau tyrinėkite ir naudokitės CASE galia savo duomenų bazės projektuose!

Papildomi resursai:

• Oficiali MySQL dokumentacija apie CASE funkciją
• Papildomi CASE naudojimo MySQL pavyzdžiai