Ingxelo yeMySQL IF ELSE enemizekelo ebonakalayo

Informatec Digital » Yolwazi » Ingxelo yeMySQL IF ELSE enemizekelo ebonakalayo

Kweli nqaku, ndiza kukubonisa indlela yokusebenzisa i-MySQL IF ELSE inkcazo ngemizekelo yokuphucula imibuzo yakho kwaye ufumane iziphumo ezichanekileyo. Ngale mizekelo ili-18 isebenzayo, siya kufunda indlela yokusebenzisa esi sixhobo sinamandla kwaye sifumane okuninzi kuluhlu lwedatha. Siza kuphonononga iimeko zobomi bokwenyani apho ukusebenzisa IF ELSE kwenza umahluko, ukusuka ekukhetheni idatha enemiqathango ukwenza imisebenzi enzima. Sele ulungele? Kulungile, masiyeni apho! Yilungele ukuyiqonda le ndlela kwaye uthathe izakhono zakho zeSQL ukuya kwinqanaba elilandelayo.

Yintoni IF ELSE statement in MySQL?

Ngaphambi kokuba singene kwimizekelo, kubalulekile ukuqonda ukuba yintoni inkcazo ye-IF ELSE kwi-MySQL. Ngokwenene, sisakhiwo esinemiqathango esivumela ukuba senze izenzo ezahlukeneyo ngokuxhomekeke ekubeni imeko ethile idibene okanye hayi. Oko kukuthi, ukuba imeko iyinyani, ibhloko enye yekhowudi iya kuphunyezwa, kwaye ukuba ibuxoki, ibhloko eyahlukileyo yekhowudi iya kuphunyezwa.

I-MySQL IF ELSE Ingxelo eneMizekeliso

Apha sidwelisa a itheyibhile enemizekelo eli-18 siza kubona kweli nqaku:

Umzekelo	Descripción
1	Ukusetyenziswa okusisiseko UKUBA OKUNYE
2	UKUBA KUNYE neemeko ezininzi
3	Ifakwe UKUBA OKUNYE
4	UKUBA OKUNYE nabaqhubi abanengqiqo
5	UKUBA OKUNYE ngemisebenzi yokudibanisa
6	UKUBA OKUNYE ne-subqueries
7	UKUBA OKUNYE neziguquguqukayo
8	UKUBA OKUNYE ne CASE
9	UKUBA OKUNYE nge-IFNULL
10	UKUBA OKUNYE nge-NULLIF
11	UKUBA OKUNYE nge YIKHO
12	UKUBA OKUNYE nge-IN
13	UKUBA OKUNYE nge PHAKATHI
14	UKUBA OKUNYE ngo-LIKE
15	UKUBA OKUNYE ngeREGEXP
16	UKUBA OKUNYE nge-DATE_FORMAT
17	UKUBA OKUNYE neGROUP_CONCAT
18	UKUBA OKUNYE NOKUBA

1. Ukusetyenziswa okusisiseko UKUBA OKUNYE

Makhe sijonge kumzekelo olula esiqale ngawo. Masithi sinetheyibhile ebizwa ngokuba “zimveliso” enala mimandla ilandelayo: i-id, igama, ixabiso kunye nesitokhwe. Sifuna ukufumana igama lemveliso kunye nomyalezo obonisa ukuba isitokhwe okanye hayi. Singayenza ngolu hlobo:

SELECT nombre, IF(stock > 0, 'Hay stock', 'No hay stock') AS stock 
FROM productos;

Kule meko, imeko stock > 0. Ukuba ithe yahlangatyezwa, umyalezo othi 'Ikwi-stock' iya kuboniswa, kwaye ukuba ayihlangatyezwanga, 'Iphelelwe sisitokhwe' iya kuboniswa.

2. UKUBA KUNYE neemeko ezininzi

Sinokusebenzisa UKUBA OKUNYE neemeko ezininzi. Umzekelo, masithi sifuna ukufumana igama lemveliso kunye nomyalezo obonisa ukuba ixabiso likhulu kune-100, lingaphantsi kwama-50, okanye phakathi kwama-50 kunye ne-100. Singenza ngolu hlobo:

SELECT nombre, 
  IF(precio > 100, 'Caro', 
    IF(precio < 50, 'Barato', 'Precio medio')
  ) AS precio
FROM productos;

Kule meko, sisebenzisa iinkcazo ezifakwe kwindlwane IF ELSE ukuvavanya iimeko ezahlukeneyo.

3. Ifakwe kwindlwana UKUBA OKUNYE

Njengoko sibonile kumzekelo wangaphambili, singenza indlwane ezininzi IF ELSE iingxelo zokuvavanya iimeko ezahlukeneyo. Makhe sijonge omnye umzekelo. Masithi sifuna ukufumana igama lemveliso kunye nomyalezo obonisa ukuba isitokhwe sikhulu kune-100, ngaphantsi kwe-10, okanye phakathi kwe-10 kunye ne-100. Kwakhona, ukuba isitokhwe singaphantsi kwe-10, sifuna ukubonisa ukuba ngaba i-0 okanye ayikho. Singayenza ngolu hlobo:

SELECT nombre,
  IF(stock > 100, 'Stock alto',
    IF(stock < 10, 
      IF(stock = 0, 'Sin stock', 'Stock bajo'),
      'Stock medio'
    )
  ) AS stock
FROM productos;

Kule meko, sisebenzisa amanqanaba amathathu okuzalela ukuvavanya zonke iimeko eziyimfuneko.

4. UKUBA OKUNYE nabaqhubi abanengqiqo

Sisenokudibanisa UKUBA OKUNYE kunye nabaqhubi abanengqondo abafana no- AND kunye OKANYE. Umzekelo, masithi sifuna ukufumana igama lemveliso kunye nomyalezo obonisa ukuba ixabiso likhulu kune-100 kwaye isitokhwe singaphantsi kwe-10. Singayenza ngolu hlobo:

SELECT nombre,
  IF(precio > 100 AND stock < 10, 'Urgente reponer', 'OK') AS estado
FROM productos;

Kule meko, imeko precio > 100 AND stock < 10. Ukuba zombini iimeko zifezekisiwe, umyalezo 'Uzaliswa ngokungxamisekileyo' uya kuboniswa, kwaye ukuba awuhlangabezwanga, 'Kulungile' uya kuboniswa.

5. UKUBA OKUNYE kunye nemisebenzi yokudibanisa

Sinokudibanisa UKUBA OKUNYE kunye nemisebenzi yokudibanisa efana ne-SUM, AVG, MAX, njl. Umzekelo, masithi sifuna ukufumana yonke into ethengisiweyo kunye nomyalezo obonisa ukuba sigqithise injongo ye-10000 okanye hayi. Singayenza ngolu hlobo:

SELECT 
  SUM(total) AS total_ventas,
  IF(SUM(total) > 10000, '¡Objetivo superado!', 'No se alcanzó el objetivo') AS mensaje
FROM ventas;

Kule meko, sibala iintengiso ezipheleleyo nge-SUM kwaye emva koko sisebenzise IF ELSE ukubonisa umyalezo ngokusekelwe ekubeni ithagethi igqithiwe okanye hayi.

6. UKUBA OKUNYE kunye ne-subqueries

Sinokusebenzisa UKUBA OKUNYE nge subqueries. Umzekelo, masithi sifuna ukufumana igama leemveliso kunye nomyalezo obonisa ukuba ixabiso labo liphezulu na kune-avareji yexabiso lazo zonke iimveliso. Singayenza ngolu hlobo:

SELECT nombre,
  IF(precio > (SELECT AVG(precio) FROM productos), 
    'Precio superior a la media', 
    'Precio inferior o igual a la media'
  ) AS comparacion
FROM productos;

Kule meko, sisebenzisa i-subquery ukubala ixabiso eliqhelekileyo lazo zonke iimveliso kwaye sithelekise ixabiso lomntu ngamnye kwelo xabiso usebenzisa IF ELSE.

7. UKUBA OKUNYE neziguquguqukayo

Singasebenzisa UKUBA OKUNYE kunye nezinto eziguquguqukayo ukugcina nokusebenzisa amaxabiso kwimibuzo yethu. Umzekelo, cinga ukuba sifuna ukufumana yonke into ethengisiweyo kunye nomyalezo obonisa ukuba ngaba sigqithile kwiinjongo esizigcine kuguquko. Singayenza ngolu hlobo:

SET @objetivo = 10000;

SELECT 
  SUM(total) AS total_ventas,
  IF(SUM(total) > @objetivo, '¡Objetivo superado!', 'No se alcanzó el objetivo') AS mensaje 
FROM ventas;

Kule meko, siqala sichaza i-variable @objetivo ngexabiso 10000 kwaye ke siyisebenzisa kwimeko IF ELSE.

8. UKUBA OKUNYE ne CASE

Enye indlela yokusebenzisa UKUBA OKUNYE kukudibanisa nayo ingxelo yeCASE. Umzekelo, masithi sifuna ukufumana igama lemveliso kunye nokuhlelwa kwexabiso layo ukuya kwi-'Cheap', 'Medium' okanye 'Ixabiso eliphezulu' ngokusekelwe kuluhlu oluthile. Singayenza ngolu hlobo:

SELECT nombre,
  CASE 
    WHEN precio < 50 THEN 'Barato'
    WHEN precio BETWEEN 50 AND 100 THEN 'Medio'
    ELSE 'Caro'
  END AS clasificacion
FROM productos;

Kule meko, sisebenzisa i-CASE ukuvavanya iimeko ezahlukeneyo kwaye sinikeze ixabiso ngokusekelwe kwisiphumo. Yenye indlela yokwenza into enye kunye neengxelo ezifakwe kwindlwane IF ELSE.

9. UKUBA OKUNYE ngeIFNULL

Umsebenzi we-IFNULL usivumela ukuba siphathe amaxabiso angenanto kwimibuzo yethu. Sinokuyidibanisa no-IF ELSE ukunika ixabiso elingagqibekanga xa sidibana nexabiso elingenanto. Umzekelo, cinga ukuba sifuna ukufumana igama lemveliso kunye nexabiso layo, kodwa ukuba ixabiso alikho, sifuna ukubonisa umyalezo 'Umbuzo'. Singayenza ngolu hlobo:

SELECT nombre, IFNULL(precio, 'Consultar') AS precio
FROM productos;

Kulo mzekelo, sisebenzisa i-IFNULL ukuvavanya ukuba ixabiso alikho kwaye ukuba kunjalo, bonisa 'Umbuzo' umyalezo endaweni yexabiso elingenanto.

10. UKUBA OKUNYE nge-NULLIF

Umsebenzi we-NULLIF uyafana no-IFNULL, kodwa endaweni yokuphatha amaxabiso angenanto, iyasivumela ukuba sithelekise amaxabiso amabini kwaye sibuyisele NULL ukuba ayalingana. Sinokuyidibanisa ne-IF ELSE ukwabela elinye ixabiso xa imeko ethile ifezekisiwe. Umzekelo, cinga ukuba sifuna ukufumana igama lemveliso kunye nexabiso layo, kodwa ukuba ixabiso ngu-0, sifuna ukubonisa umyalezo 'Mahala'. Singayenza ngolu hlobo:

SELECT nombre, IF(NULLIF(precio, 0) IS NULL, 'Gratis', precio) AS precio
FROM productos;

Kule meko, sisebenzisa i-NULLIF ukuthelekisa ixabiso kunye no-0 kwaye sibuyisele i-NULL ukuba ziyalingana. Sisebenzisa ke IF ELSE ukuvavanya ukuba isiphumo seNULLIF AYIKHO kwaye ukuba kunjalo, bonisa umyalezo 'Mahala' endaweni yexabiso.

11. UKUBA KUKHO nge-EKHOSI

Igatya elithi EXISTS lisivumela ukuba sikhangele ukuba kukho nayiphi na irekhodi ehlangabezana nemeko ethile kwi-subquery. Sinokuyidibanisa kunye IF ELSE ukwenza izigqibo ngokusekelwe kwisiphumo. Umzekelo, masithi sifuna ukufumana igama labathengi kunye nomyalezo obonisa ukuba bayifakile iodolo okanye hayi. Singayenza ngolu hlobo:

SELECT nombre,
  IF(EXISTS(SELECT * FROM pedidos WHERE cliente_id = clientes.id),
    'Con pedidos',
    'Sin pedidos'
  ) AS estado
FROM clientes;

Kule meko, sisebenzisa i-EXISTS kunye ne-subquery ukujonga ukuba kukho naziphi na ii-odolo kumthengi ngamnye. Emva koko sisebenzise UKUBA OKUNYE ukubonisa umyalezo osekelwe kwisiphumo.

12. UKUBA OKUNYE nge-IN

Umsebenzisi IN uyasivumela ukuba sijonge ukuba ixabiso likuluhlu lwamaxabiso. Sinokuyidibanisa kunye IF ELSE ukwenza izigqibo ngokusekelwe kwisiphumo. Umzekelo, masithi sifuna ukufumana igama leemveliso kunye nomyalezo obonisa ukuba ngaba zezobuxhakaxhaka bombane okanye udidi 'lweKhaya'. Singayenza ngolu hlobo:

SELECT nombre,
  IF(categoria IN ('Electrónica', 'Hogar'),
    'Producto destacado',
    'Producto regular'
  ) AS tipo
FROM productos;

Kule meko, sisebenzisa u-IN ukujonga ukuba udidi lwemveliso ludweliswe phantsi 'kwe-Electronics' okanye 'eKhaya'. Emva koko sisebenzise UKUBA OKUNYE ukubonisa umyalezo osekelwe kwisiphumo.

13. UKUBA OKUNYE kunye PHAKATHI

PHAKATHI komsebenzisi uyasivumela ukuba sijonge ukuba ixabiso lingaphakathi koluhlu lwamaxabiso. Sinokuyidibanisa kunye IF ELSE ukwenza izigqibo ngokusekelwe kwisiphumo. Umzekelo, masithi sifuna ukufumana igama leemveliso kunye nomyalezo obonisa ukuba ixabiso labo liphakathi kwama-50 kunye ne-100. Singayenza ngolu hlobo:

SELECT nombre,
  IF(precio BETWEEN 50 AND 100,
    'Precio en rango',
    'Precio fuera de rango'
  ) AS rango
FROM productos;

Kule meko, sisebenzisa PHAKATHI ukujonga ukuba ixabiso lemveliso liphakathi kwe-50 kunye ne-100. Emva koko, sisebenzisa IF ELSE ukubonisa umyalezo osekelwe kwisiphumo.

14. UKUBA OKUNYE ngo-LIKE

Umsebenzisi othi LIKE uyasivumela ukuba sikhangele iipateni kwimitya yokubhaliweyo. Sinokuyidibanisa kunye IF ELSE ukwenza izigqibo ngokusekelwe kwisiphumo. Umzekelo, masithi sifuna ukufumana igama leemveliso kunye nomyalezo obonisa ukuba igama labo liqulethe igama elithi 'iPhone'. Singayenza ngolu hlobo:

SELECT nombre,
  IF(nombre LIKE '%iPhone%',
    'Producto de Apple',
    'Otro producto'
  ) AS tipo
FROM productos;

Kule meko, sisebenzisa u-LIKE ukujonga ukuba igama lemveliso liqulethe igama elithi 'iPhone'. Emva koko sisebenzise UKUBA OKUNYE ukubonisa umyalezo osekelwe kwisiphumo.

15. UKUBA OKUNYE ngeREGEXP

Umsebenzisi weREGEXP uyasivumela ukuba sikhangele iipateni ezintsonkothileyo kwimitya yokubhaliweyo usebenzisa amabinzana aqhelekileyo. Sinokuyidibanisa kunye IF ELSE ukwenza izigqibo ngokusekelwe kwisiphumo. Umzekelo, masithi sifuna ukufumana igama lomthengi kunye nomyalezo obonisa ukuba i-imeyile yabo iyasebenza okanye hayi. Singayenza ngolu hlobo:

SELECT nombre,
  IF(email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Z]{2,}$',
    'Email válido',
    'Email inválido'
  ) AS validacion
FROM clientes;

Kule meko, sisebenzisa i-REGEXP ngentetha eqhelekileyo ukujonga ukuba i-imeyile yomthengi idibana nefomathi esebenzayo. Emva koko sisebenzise UKUBA OKUNYE ukubonisa umyalezo osekelwe kwisiphumo.

16. UKUBA OKUNYE nge-DATE_FORMAT

DATE_FORMAT umsebenzi uyasivumela ukuba sifomathe imihla kwimibuzo yethu. Sinokuyidibanisa kunye IF ELSE ukwenza izigqibo ngokusekelwe kwisiphumo. Umzekelo, masithi sifuna ukufumana igama leodolo kunye nomyalezo obonisa ukuba zifakwe ngempelaveki okanye hayi. Singayenza ngolu hlobo:

SELECT id,
  IF(DATE_FORMAT(fecha, '%w') IN (0, 6),
    'Fin de semana',
    'Día de semana'
  ) AS dia
FROM pedidos;

Kule meko, sisebenzisa i-DATE_FORMAT ukufumana usuku lweveki yomhla we-odolo (0 ngeCawa, 6 ngoMgqibelo). Sisebenzisa ke UKUBA OKUNYE nge-IN ukujonga ukuba usuku ngu-0 okanye 6 kwaye sibonise umyalezo osekelwe kwisiphumo.

17. UKUBA OKUNYE neGROUP_CONCAT

GROUP_CONCAT umsebenzi uyasivumela ukuba sidibanise amaxabiso ukusuka kwimigca emininzi ukuya kumtya omnye. Sinokuyidibanisa kunye IF ELSE ukwenza izigqibo ngokusekelwe kwisiphumo. Umzekelo, masithi sifuna ukufumana igama lomthengi kunye nomyalezo obonisa ukuba bathengile 'i-iPhone' okanye 'i-Samsung'. Singayenza ngolu hlobo:

SELECT c.nombre,
  IF(GROUP_CONCAT(p.nombre) LIKE '%iPhone%', 
    'Compró iPhone',
    IF(GROUP_CONCAT(p.nombre) LIKE '%Samsung%',
      'Compró Samsung',
      'No compró iPhone ni Samsung'
    )
  ) AS mensaje
FROM clientes c
JOIN pedidos pe ON c.id = pe.cliente_id
JOIN productos p ON pe.producto_id = p.id
GROUP BY c.id;

Kulo mzekelo, sisebenzisa GROUP_CONCAT ukudibanisa amagama eemveliso ezithengwe ngumthengi ngamnye. Sisebenzisa ke UKUBA ELSE ngo-LIKE ukujonga ukuba umtya ophumayo uqulethe 'i-iPhone' okanye 'i-Samsung' kwaye ubonise umyalezo osekelwe kwisiphumo.

18. UKUBA OKUNYE NOKUBA

La UKUBA negatya Isivumela ukuba sihluze iziphumo zombuzo nge GROUP BY ngokusekelwe kwiimeko ezithile. Sinokuyidibanisa kunye IF ELSE ukwenza izigqibo ngokusekelwe kwisiphumo. Ngokomzekelo, masithi sifuna ukufumana inani lee-odolo ngomthengi ngamnye kunye nomyalezo obonisa ukuba ngaba "uMthengi we-VIP" (ii-odolo ezili-10+) okanye "uMthengi oQhelekileyo." Singayenza ngolu hlobo:

SELECT c.nombre, COUNT(p.id) AS total_pedidos,
  IF(COUNT(p.id) > 10,
    'Cliente VIP',
    'Cliente regular'
  ) AS tipo_cliente
FROM clientes c
JOIN pedidos p ON c.id = p.cliente_id
GROUP BY c.id
HAVING COUNT(p.id) > 5;

Kulo mzekelo, sisebenzisa IQELA NGENXA YEQELA ukuqokelela ii-odolo ngomthengi kwaye sibale inani lee-odolo zento nganye ene-COUNT. Emva koko sisebenzisa i-HAVING ukuhluza kuphela abathengi abane-odolo ezingaphezulu kwe-5. Okokugqibela, sisebenzisa UKUBA OKUNYE ukubonisa umyalezo ngokusekelwe ekubeni ii-odolo zizonke zikhulu kuno-10 okanye hayi.

FAQ kwi IF ELSE MySQL enemizekelo

1. Yintoni IF ELSE statement in MySQL?

Ingxelo ethi IF ELSE in I-MySQL yisakhiwo esinemiqathango esivumela ukuba senze izenzo ezahlukeneyo ngokuxhomekeke ekubeni imeko ethile ifezekisiwe okanye hayi.

2. Indlela yokusebenzisa IF ELSE statement in MySQL?

Isivakalisi esisisiseko sengxelo ye-IF ELSE kwiMySQL simi ngolu hlobo lulandelayo:

IF(condición, valor_si_verdadero, valor_si_falso)

3. Ngaba iinkcazo ezininzi ze-IF ELSE zinokufakwa kwi-MySQL?

Ewe, ungenza indlwane ezininzi IF ELSE iingxelo zokuvavanya iimeko ezahlukeneyo kwaye wenze izigqibo ezisekelwe kwiziphumo.

4. Ngaba IF ELSE inokudityaniswa neminye imisebenzi kunye namagatya eMySQL?

Ewe, UKUBA ELSE inokudibaniswa neminye imisebenzi emininzi ye-MySQL kunye namagatya, njengemisebenzi edibeneyo, i-subqueries, i-CASE, i-IFNULL, i-NULLIF, i-EXISTS, IN, PHAKATHI, NJENGO, REGEXP, DATE_FORMAT, GROUP_CONCAT, HAVING, kunye nokunye.

5. Uthini umahluko phakathi kwe-IF ELSE kunye ne-CASE kwi-MySQL?

Zombini IF ELSE kunye ne CASE zisivumela ukuba senze izenzo ezahlukeneyo ngokusekelwe kwiimeko ezithile. Umahluko omkhulu kukuba IF ELSE isetyenziselwa ukuvavanya imeko enye kwaye iqhube isenzo ukuba idibene okanye ayikho, ngelixa i-CASE isetyenziselwa ukuvavanya iimeko ezininzi kunye nokuqhuba izenzo ezahlukeneyo ngokusekelwe kwisiphumo.

6. Ngaba kukho nayiphi na imida ekusebenziseni IF ELSE kwiMySQL?

Akukho zithintelo ezibalulekileyo ekusebenziseni i-IF ELSE kwi-MySQL, ngaphaya kwemida yengqiqo kunye ne-syntax yeemeko ezivavanywayo. Nangona kunjalo, kubalulekile ukuqaphela ukuba ukusetyenziswa ngokugqithiseleyo okanye ukusetyenziswa kakubi kwe-IF ELSE kunokuchaphazela ukusebenza kwemibuzo kwaye yenze ikhowudi ibe nzima ukuyifunda nokugcina.

Ukuqukumbela IF ELSE MySQL ngemizekelo

Kule nqaku, siye safunda ukuba yintoni inkcazo ye-IF ELSE kwi-MySQL kunye nendlela esetyenziselwa ngayo ukwenza izenzo ezahlukeneyo ngokusekelwe kwiimeko ezithile. Siye sabona i-18 imizekelo ebonakalayo yokusetyenziswa of UKUBA OKUNYE, ukudibanisa kunye neminye imisebenzi ye-MySQL kunye namagatya afana ne-aggregate imisebenzi, subqueries, CASE, IFNULL, NULLIF, EXISTS, IN, PHAKATHI, LIKE, REGEXP, DATE_FORMAT, GROUP_CONCAT, kunye NOKUBA.

Ndiyathemba ukuba le mizekelo ibe luncedo kwaye ikuncede uqonde ngcono indlela yokusebenzisa UKUBA OKUNYE kwindawo yakho Imibuzo ukusuka kwi-MySQL. Kwaye ungalibali ukwabelana ngeli nqaku nabahlobo bakho kunye noogxa bakho ukuba ulifumene linomdla!