Icala ku-MySQL: Izibonelo Ezisebenzayo eziyi-11

Informatec Digital » Yolwazi » Icala ku-MySQL: Izibonelo Ezisebenzayo eziyi-11

Umsebenzi we-CASE ku-MySQL iyithuluzi elinamandla elikuvumela ukuthi wenze imisebenzi enemibandela ngaphakathi kwemibuzo yakho. Nge-CASE, ungakwazi ukuhlola izimo ezihlukene bese ubuyisela imiphumela ethile kuye ngokuthi leyo mibandela iyahlangatshezwa yini noma cha. Sizochaza lo msebenzi ngezibonelo ezingokoqobo ezizokusiza ukwazi ukusebenzisa i-CASE ku-MySQL futhi uthuthukise amakhono akho ekuphathweni kwesizindalwazi.

Uyini umsebenzi we-CASE ku-MySQL?

Umsebenzi we-CASE ku-MySQL uwukubonakaliswa okunemibandela okukuvumela ukuthi uhlole izimo ezahlukene futhi ubuyisele imiphumela ethile kuye ngokuthi lezo zimo ziyahlangatshezwana yini. Kuyithuluzi eliwusizo kakhulu lokwenza imisebenzi enengqondo ngaphakathi kwemibuzo yakho nokuthola imiphumela eyenziwe ngokwezifiso ngokusekelwe ezindinganisweni ezithile.

I-CASE isebenza ngendlela efanayo nochungechunge lwezitatimende IF-THEN-ELSE, lapho ungacacisa khona izimo eziningi kanye namanani azobuyiselwa lapho leyo mibandela ifinyelelwa. Uma kungekho neyodwa imibandela efinyelelwayo, ungachaza inani elizenzakalelayo usebenzisa isigatshana esithi ELSE.

I-syntax ye-CASE eyisisekelo ku-MySQL

I-syntax eyisisekelo yomsebenzi we-CASE ku-MySQL imi kanje:

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

Nansi incazelo yengxenye ngayinye ye-syntax:

• WHEN: Icacisa isimo okufanele sihlolwe.
• THEN: Ibonisa umphumela ozobuyiselwa uma isimo esihambisanayo sifinyelelwa.
• ELSE: (Ongakukhetha) Icacisa umphumela ozobuyiselwa uma kungekho neyodwa imibandela engenhla efinyelelwayo.
• END: Imaka isiphetho senkulumo ethi CASE.

Manje njengoba usuwazi i-syntax eyisisekelo, ake sihlole izibonelo ezithile ezisebenzayo!

Isibonelo sokuqala: Hlukanisa abafundi ngokwesilinganiso sabo

Ake sithi unetafula elibizwa ngokuthi “abafundi” elinamakholomu alandelayo: “id”, “igama”, kanye “nesilinganiso”. Ufuna ukukala abafundi nge-GPA yabo usebenzisa umsebenzi we-CASE. Ungakwenza ngale ndlela:

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;

Kulesi sibonelo, sisebenzisa i-CASE ukuze sihlole isilinganiso somfundi ngamunye bese sabela izinga elihambisanayo. Uma isilinganiso sikhulu noma silingana no-90, sihlukaniswa ngokuthi "Okuvelele." Uma iphakathi kuka-80 no-89, ihlukaniswa ngokuthi “Okuphawulekayo,” nokunye. Uma isilinganiso singaphansi kuka-60, sichazwa ngokuthi "Akwanele."

Isibonelo sesi-2: Ukunikeza izigaba zomkhiqizo

Ake sithi unetafula elibizwa ngokuthi “imikhiqizo” elinamakholomu “ubunikazi”, “igama” kanye “nentengo”. Ufuna ukwabela isigaba kumkhiqizo ngamunye ngokusekelwe enanini lawo usebenzisa umsebenzi we-CASE. Ungakwenza ngale ndlela:

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;

Kulesi sibonelo, sisebenzisa i-CASE ukuhlola intengo yomkhiqizo ngamunye futhi sinikeze isigaba esihambisanayo. Uma intengo ingaphezu kuka-1000, ihlukaniswa njenge-“Premium”. Uma iphakathi kuka-500 no-1000, ihlukaniswa ngokuthi “High-End,” njalo njalo. Uma intengo ingaphansi noma ilingana no-100, ihlukaniswa ngokuthi “Ezomnotho”.

Isibonelo sesi-3: Bala izaphulelo ngokusekelwe enanini elithengiwe

Ake sithi unetafula elibizwa ngokuthi "sales" elinamakholomu "id", "product", kanye "quantity". Ufuna ukubala isaphulelo esisetshenziswa endalini ngayinye ngokusekelwe enanini elithengwe kusetshenziswa umsebenzi we-CASE. Ungakwenza ngale ndlela:

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;

Kulesi sibonelo, sisebenzisa i-CASE ukuhlola inani elithengiwe lomkhiqizo ngamunye futhi sibale isaphulelo esihambisanayo. Uma inani likhulu noma lilingana no-100, kusetshenziswa isaphulelo esingu-20%. Uma kuphakathi kuka-50 no-99, isaphulelo esingu-15% sisetshenziswa, njalo njalo. Uma inani lingaphansi kwama-20, asikho isaphulelo esisebenzayo.

Isibonelo sesi-4: Ukuguqula amanani ezinombolo abe ububanzi

Ake sithi unetafula elibizwa ngokuthi “izisebenzi” elinamakholomu “id”, “igama”, kanye “nobudala”. Ufuna ukuguqula iminyaka yesisebenzi ibe ububanzi usebenzisa umsebenzi we-CASE. Ungakwenza ngale ndlela:

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;

Kulesi sibonelo, sisebenzisa i-CASE ukuhlola iminyaka yesisebenzi ngasinye futhi sinikeze ububanzi obuhambisanayo. Uma iminyaka yobudala ingaphezu noma ilingana no-60, ihlukaniswa ngokuthi "Omdala". Uma uneminyaka ephakathi kuka-40 no-59, uhlukaniswa ngokuthi “abadala abaphakathi,” njalo njalo. Uma iminyaka yobudala ingaphansi kwama-20, ihlukaniswa ngokuthi "Minor".

Isibonelo sesi-5: Ukunikeza amalebula ngokusekelwe ezimeni eziningi

Ake sithi unetafula elibizwa ngokuthi “ama-oda” elinamakholomu “i-id”, “ikhasimende”, “inani”, “nesimo”. Ufuna ukwabela amalebula ku-oda ngalinye ngokusekelwe enanini lalo kanye nesimo usebenzisa umsebenzi we-CASE onemibandela eminingi. Ungakwenza ngale ndlela:

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;

Kulesi sibonelo, sisebenzisa i-CASE enemibandela eminingi ukuze sihlole kokubili inani nesimo se-oda ngalinye futhi sinikeze ilebula elifanele. Uma isamba sikhulu kuno-1000 futhi isimo sithi “Okulethwayo”, sibhalwe ngokuthi “VIP”. Uma isamba sikhulu kuno-500 futhi isimo sithi "Kudilithiwe," simakwe ngokuthi "Okubalulekile." Uma isimo sithi “Kusalindile”, sibhalwe ngokuthi “Kusaqhubeka”. Uma kungenjalo, ibhalwe ngokuthi “Okuvamile.”

Isibonelo sesi-6: Ukuphatha amanani angenalutho nge-CASE

Ake sithi unetafula elibizwa ngokuthi “amakhasimende” elinamakholomu “ubunikazi”, “igama” kanye “ne-imeyili”. Amanye amakhasimende angase angabi nayo i-imeyili efayeleni, okungaholela kumanani angenalutho kukholamu “ye-imeyili”. Ungasebenzisa umsebenzi we-CASE ukuphatha la manani angenalutho ngokufanelekile. Ngokwesibonelo:

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

Kulesi sibonelo, sisebenzisa i-CASE ukuhlola ukuthi ikholomu "ye-imeyili" ayinalutho yini. Uma kungekho, umbhalo othi “Ayikho i-imeyili” uyavezwa. Uma kungenjalo, inani langempela lekholomu "ye-imeyili" liyaboniswa. Lokhu kusivumela ukuthi siphathe kahle izimo lapho ulwazi lokuxhumana lungekho.

Isibonelo sesi-7: Ukuhlanganisa i-CASE nemisebenzi Ehlanganisiwe

Umsebenzi we-CASE ungaphinda uhlanganiswe nemisebenzi ehlanganisiwe efana ne-SUM, AVG, COUNT, njll. Ake sithi unetafula elibizwa ngokuthi “sales” elinamakholomu “id”, “product”, “quantity”, “neprice”. Ufuna ukubala inani elithengisiwe ngesigaba somkhiqizo usebenzisa i-CASE ne-SUM. Ungakwenza ngale ndlela:

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;

Kulesi sibonelo, sisebenzisa i-CASE ngaphakathi komsebenzi we-SUM ukubala inani eliphelele lokuthengisa ngesigaba somkhiqizo. Uma intengo ingaphezu kuka-1000, inani lengezwa kokuthi “premium_sales”. Uma intengo ingaphansi noma ilingana no-1000, inani lengezwa kokuthi “regular_sales”. Lokhu kusivumela ukuthi sithole amanani amancane asuselwa ezimeni ezithile.

Isibonelo sesi-8: Ukusebenzisa i-CASE kumshwana othi LAPHO

Umsebenzi we-CASE ungasetshenziswa futhi esigatshaneni esithi LAPHO ukuhlunga amarekhodi ngokusekelwe ezimweni ezithile. Ake sithi unetafula elibizwa ngokuthi “izisebenzi” elinamakholomu “id”, “igama”, “umnyango”, kanye “neholo”. Ufuna ukukhomba abasebenzi abamholo wabo ongaphezu kwesilinganiso somnyango wabo. Ungakwenza ngale ndlela:

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

Kulesi sibonelo, sisebenzisa i-CASE kumbuzo omncane ukubala umholo omaphakathi ngomnyango. I-subquery iqhathanisa umnyango wesisebenzi ngasinye nomnyango wamanje futhi icabangela kuphela amaholo ezisebenzi emnyangweni ofanayo ukuze kubalwe isilinganiso. Bese, embuzweni oyinhloko, sihlunga abasebenzi abaholo labo lingaphezu kwesilinganiso esibalwe emnyangweni wabo.

Isibonelo 9: Ukukhiqiza amakholomu abaliwe nge-CASE

Umsebenzi we-CASE ungaphinda usetshenziselwe ukukhiqiza amakholomu abaliwe ngokusekelwe ezimeni ezithile. Ake sithi unethebula elibizwa ngokuthi “ama-oda” elinamakholomu “i-id”, “ikhasimende”, “inani”, “nosuku”. Ufuna ukudala ikholomu eyengeziwe ebizwa ngokuthi "isaphulelo" esebenzisa amaphesenti ahlukene esaphulelo kuye ngesamba se-oda. Ungakwenza ngale ndlela:

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;

Kulesi sibonelo, sisebenzisa i-CASE ukuze sikhiqize ikholomu “yesaphulelo” ebaliwe. Uma isamba se-oda sikhulu kuno-1000, kusetshenziswa isaphulelo esingu-10%. Uma isamba sikhulu kuno-500, kusetshenziswa isaphulelo esingu-5%. Kunoma isiphi esinye isimo, asikho isaphulelo esisebenzayo. Le kholomu ebaliwe ingasetshenziselwa ukuhlaziya okwengeziwe noma ukubonisa ulwazi olwengeziwe emiphumeleni yombuzo.

Isibonelo se-10: Ukusebenzisa ingqondo eyinkimbinkimbi ngezitatimende ze-CASE esidlekeni

Kwezinye izimo, kungase kudingeke ukuthi usebenzise ingqondo enemibandela eyinkimbinkimbi usebenzisa izitatimende ze-CASE ezifakwe esidlekeni. Ake sithi unethebula elibizwa ngokuthi “abafundi” elinamakholomu “id”, “igama”, “math_grade” kanye “nebanga_lolimi”. Ufuna ukwabela umfundi ngamunye isigaba ngokusekelwe kumabanga akhe ezibalweni nolimi. Ungakwenza ngale ndlela:

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;

Kulesi sibonelo, sisebenzisa izitatimende ze-CASE ezifakwe esidlekeni ukuze sisebenzise ingqondo enemibandela eyinkimbinkimbi. Okokuqala, sihlola ukuthi ingabe womabili amamaki ezibalo nawolimi makhulu noma alingana no-90. Uma kunjalo, isigaba "Esihle kakhulu" sabelwe. Bese, sihlola ukuthi womabili amabanga makhulu yini noma alingana no-80. Uma kunjalo, isigaba "Okuphawulekayo" sabelwe. Uma kungekho kwezimo ezingenhla okuhlangatshezwana nazo, sidlulela kuleveli elandelayo ye-CASE esidleke. Lapha, sihlola ukuthi ingabe okungenani elilodwa lamabanga (izibalo noma ulimi) likhulu noma lilingana nama-70. Uma kunjalo, isigaba "Esivamile" sabelwe. Uma kungekho neyodwa imibandela ehlangatshezwana nayo, isigaba esithi “Idinga Ukuthuthukiswa” siyanikezwa.

Isibonelo se-11: Ukuthuthukisa imibuzo nge-CASE

Umsebenzi we-CASE ungaphinda usetshenziselwe ukuthuthukisa imibuzo futhi ugweme imibuzo eminingi ehlukene. Ake sithi unetafula elibizwa ngokuthi “sales” elinamakholomu “id”, “product”, “quantity”, kanye “nedethi”. Ufuna ukuthola inani eliphelele lokuthengisa ngenyanga kanye nesamba sokuthengisa ngonyaka ngombuzo owodwa. Ungakwenza ngale ndlela:

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;

Kulesi sibonelo, sisebenzisa i-CASE ngaphakathi komsebenzi we-SUM ukuze sibale isamba sokuthengisa ngenyanga kanye nonyaka embuzweni owodwa. Inyanga ngayinye, sihlola ukuthi ingabe inyanga yedethi yokuthengisa iyafana yini nenyanga ethile bese sengeza inani elihambisanayo. Ngokufanayo, unyaka ngamunye, sihlola ukuthi unyaka wedethi yokuthengisa uyahambisana yini nonyaka othile bese sengeza inani elihambisanayo. Lokhu kusivumela ukuthi sithole wonke amanani embuzweni owodwa osebenzayo.

Izindlela ezihamba phambili uma usebenzisa i-CASE ku-MySQL

• Sebenzisa i-CASE kuphela uma kudingeka futhi ugweme ukuyisebenzisa ngokweqile, njengoba ingaphazamisa ukusebenza kombuzo uma isetshenziswe ngokweqile.
• Zama ukugcina izisho ze-CASE zilula futhi zifundeka ngangokunokwenzeka. Uma umqondo uba yinkimbinkimbi kakhulu, cabanga ukuwehlukanisa ube yizinkulumo eziningi ze-CASE noma usebenzise imibuzo engaphansi.
• Sebenzisa i-CASE ngokuhlanganisa nezinye izigatshana ze-MySQL nemisebenzi ukuze usebenzise ngokugcwele amandla azo, njengokuthi KUPHI, UKUHLELA NGOKU, I-GROUP BY kanye nemisebenzi yokuhlanganisa.
• Qaphela lapho ufaka izinkulumo eziningi ze-CASE, njengoba kungenza ikhodi yakho ibe nzima ukuyifunda nokuyigcina. Uma kunesidingo, engeza amazwana achazayo.

Amaphutha ajwayelekile uma usebenzisa i-CASE nokuthi ungawagwema kanjani

• Khohlwa isigatshana ELSE: Qiniseka ukuthi ufaka isigatshana esithi ELSE ukuze uphathe amacala lapho kungekho neyodwa imibandela efinyelelwayo. Uma kungashiwongo, i-NULL izokwabelwa ngokuzenzakalelayo.
• Ungasiqedi isisho esithi CASE ngokuthi END: Khumbula ukuvala njalo isisho se-CASE ngegama elingukhiye elithi END. Uma kungenjalo uzothola iphutha le-syntax.
• Ukusebenzisa izinhlobo zedatha ezingahambelani: Qiniseka ukuthi imiphumela ebuyiswa yisimo ngasinye esithi WHEN iwuhlobo lwedatha olufanayo. Uma uhlanganisa izinhlobo zedatha, ungase uthole imiphumela noma amaphutha angalindelekile.
• Ukungabheki ukuhleleka kwemibandela: Izimo ku-CASE zihlolwa ngokulandelana kwazo. Qiniseka ukuthi ubeka izimo eziqondile ngaphambi kwezijwayelekile ukuze uthole imiphumela oyifunayo.

Ezinye izindlela ze-CASE ku-MySQL

Nakuba i-CASE iwumsebenzi onamandla, kukhona ezinye izindlela ongase uzicabangele ezimweni ezithile:

• IF Izinkulumo: The IF umsebenzi ku-MySQL ikuvumela ukuthi uhlole isimo bese ubuyisela inani uma isimo siyiqiniso kanye nelinye inani uma lingamanga. Kuyindlela elula yokukhetha izimo ezihlukile.
• Bheka amatafula: Kwezinye izimo, ungasebenzisa amathebula okubheka ahlukene ukuze ugcine izimo nemiphumela ehambisanayo. Ungabe usuhlanganisa lawa mathebula nethebula elikhulu ukuze uthole imiphumela oyifunayo.
• Ukubuka Okugciniwe noma Imisebenzi: Uma unemibuzo eyinkimbinkimbi esebenzisa i-CASE ngokuphindaphindiwe, ungase ucabange ukudala imibono noma imisebenzi egciniwe ukuhlanganisa lokho kunengqondo nokwenza lula imibuzo elandelayo.

Imibuzo Evame Ukubuzwa mayelana ne-CASE ku-MySQL

1. Ngingakwazi ukusebenzisa i-CASE ngihlanganise neminye imisebenzi ye-MySQL?

Yebo, ungasebenzisa i-CASE ngokuhlanganisa neminye imisebenzi ye-MySQL, efana nemisebenzi ehlanganisiwe (SUM, AVG, COUNT, njll.), imisebenzi yedethi nesikhathi (YEAR, INYANGA, USUKU, njll.), imisebenzi yeyunithi yezinhlamvu (CONCAT, SUBSTRING, LENGTH, njll.), nokunye.

2. Ingabe kukhona umkhawulo enanini lokuthi LAPHO izimo engingazisebenzisa ku- CASE expression?

Awukho umkhawulo othize enanini lemibandela ethi WHEN ongayisebenzisa kusisho esithi CASE. Nokho, khumbula ukuthi izimo eziningi zingathinta ukufundwa kwekhodi nokusebenza kombuzo. Uma unezimo eziningi, cabanga ukwenza ingqondo ibe lula noma ukuyihlephula ibe yizinkulumo eziningi ze-CASE.

3. Ngingakwazi ukusebenzisa imibuzo engezansi ngaphakathi kwenkulumo ye-CASE?

Yebo, ungasebenzisa imibuzo emincane ngaphakathi kwenkulumo ethi CASE, kokubili ezimeni okuthi WHEN kanye nemiphumela THEN. Lokhu kukuvumela ukuthi wenze izibalo eziyinkimbinkimbi noma ukuqhathanisa ngokusekelwe emiphumeleni yeminye imibuzo.

4. Ngingawaphatha kanjani amanani angenalutho kusisho se-CASE?

Ungakwazi ukuphatha amanani angenalutho kusisho esithi CASE usebenzisa isimo esithi IS NULL noma IS NOT NULL. Isibonelo, ungasebenzisa i-CASE WHEN ikholomu ILIZE BESE 'Inani elingenalutho' ELSE ikholomu END ukuze unikeze inani elithile lapho ikholomu ingenalutho futhi ubuyisele inani langempela uma lingelona.

Iziphetho Zecala ku-Mysql

Umsebenzi we-CASE ku-MySQL iyithuluzi elinamandla neliguquguqukayo elikuvumela ukuthi wenze imisebenzi enemibandela ngaphakathi kwemibuzo yakho. Nge-CASE, ungakwazi ukuhlola izimo ezihlukene bese ubuyisela imiphumela ethile kuye ngokuthi leyo mibandela iyahlangatshezwa yini noma cha. Izibonelo ezivezwe kulesi sihloko zikunikeza isisekelo esiqinile sokuqala ukusebenzisa i-CASE emibuzweni yakho futhi uyivumelanise nezidingo zakho ezithile.

Khumbula ukulandela imikhuba emihle kakhulu uma usebenzisa i-CASE, njengokugcina izinkulumo zilula futhi zifundeka, ukusebenzisa i-CASE kuhlangene nezinye izigaba nemisebenzi ye-MySQL , kanye nokucabangela ezinye izindlela uma kufaneleka. Ngokuzijwayeza kanye nokuhlola, uzokwazi ukusebenzisa ngokugcwele i-CASE ku-MySQL futhi uthuthukise ukusebenza kahle kanye nokufunda kwemibuzo yakho.

Uma uneminye imibuzo eyengeziwe noma udinga izibonelo ezengeziwe, zizwe ukhululekile ukucinga izinsiza ezengeziwe noma uthintane nemibhalo esemthethweni ye-MySQL. Qhubeka uhlola futhi usebenzise amandla e-CASE kumaphrojekthi akho esizindalwazi!

Izinsizakusebenza ezingeziwe:

• Imibhalo esemthethweni ye-MySQL ngomsebenzi we-CASE
• Izibonelo ezengeziwe zokusebenzisa i-CASE ku-MySQL