Ukukhetha Idatha Ngaphakathi Kwezingxenye ku-SQL

Ukwethula isigatshana se-WHERE kanye NGEMINYE isimo

Ulimi Lokusebenza Oluhlelekile (SQL) lunikeza abasebenzisi be-database amandla okudala imibuzo eyenziwe ngokwezifiso ukuze bakhiphe ulwazi kusuka kubalwazi. Esihlokweni esandulele, sihlolisise ukukhipha ulwazi kusuka ku-database usebenzisa imibuzo ye-SQL SELECT . Ake sandise ngaleyo ngxoxo bese sihlola ukuthi ungenza kanjani imibuzo ephakanyisiwe ukuthola idatha ehambisana nezimo ezithile.

Ake sicabangele isibonelo esisekelwe ku-database yaseNorthwind evame ukusetshenziswa, evame ukuthumela ngemikhiqizo yedatha njengezifundo.

Nasi i-excerpt etafuleni lomkhiqizo wedatha:

Ithebula lomkhiqizo

ProductID	ProductName	I-SupplierID	OkungenaniPerUnit	Intengo yokukodwa	UnitsInStock
1	Chai	1	Amabhokisi 10 x izikhwama ezingu-20	18.00	39
2	Chang	1	Amabhodlela angu-24 - 12 oz	19.00	17
3	Isiraphu esisaziwayo	1	Amabhodlela ama-12 - 550 ml	10.00	13
4	I-Chef Anton's Cajun Seasoning	2	48 - 6 oz izimbiza	22.00	53
5	I-Gumbo Mix Mix ye-Chef Anton	2	36 amabhokisi	21.35	0
6	Ugogo we-Boysenberry Spread	3	12 - 8 oz izimbiza	25.00	120
7	Amapheya omile omalume ka-Uncle Bob's	3	12 - 1 lb ama-pkgs.	30.00	15

Imibandela Elula Yomngcele

Imingcele yokuqala esizoyibeka embuzweni wethu ihilela izimo ezilula zomngcele. Singacacisa lokhu ku-WHERE isigatshana sombuzo wokukhetha, usebenzisa izitatimende zesimo esilula esakhiwe ngama-operators ajwayelekile, njenge <,>,> =, ne <=.

Okokuqala, ake sizame umbuzo olula osivumela ukuba sikhiphe uhlu lwawo wonke imikhiqizo ku-database ene-UnitPrice engaphezulu kuka-20:

Khetha i-ProductName, UnitPrice KUSUKA imikhiqizo OKUNYE UnitPrice> 20.00

Lokhu kuveza uhlu lwemikhiqizo emine, njengoba kuboniswe ngezansi:

UnitName UnitPrice ------- -------- Umculi we-Gon Anton's Gumbo 21.35 I-Cajun Anton's Cajun Seasoning 22.00 Umama ka-Boysenberry usakazeka 25.00 Amaphilisi omisiwe kaMalume Bob 30.00

Singasebenzisa futhi isigatshana se-WHERE ngezinamba zezintambo. Lokhu ngokuyisisekelo kufanisa izinhlamvu ezinombolweni, ne-A emele inani 1 ne-Z emele inani 26. Isibonelo, singabonisa yonke imikhiqizo ngamagama aqala u-U, V, W, X, Y noma Z ngombuzo olandelayo:

Khetha i-ProductName kusuka kumikhiqizo LAPHO UMkhiqizoName> = 'T'

Okukhiqiza umphumela:

I-ProductName ------- Amapheya aphethwe yi-Uncle Bob's Organic Emile

Ukuveza amaRanges usebenzisa Imingcele

I-WHERE isigatshana sisivumela futhi ukuba sisebenzise isimo semibandela ngenani ngokusebenzisa izimo eziningi. Isibonelo, uma sifuna ukuthatha umbuzo wethu ngenhla futhi sinciphise imiphumela kumikhiqizo ngamanani phakathi kuka-15.00 no-20.00, singasebenzisa lo mbuzo olandelayo:

Khetha I-ProductName, i-UnitPrice KUNYE imikhiqizo KUNYE U-UnitPrice> 15.00 no-UnitPrice <20.00

Lokhu kuveza umphumela oboniswe ngezansi:

UnitName UnitPrice ------- -------- Chai 18.00 Chang 19.00

Ukuveza ama-Range NGAPHAKATHI

I-SQL iphinde inikeze isinqamuleli phakathi kwe-syntax eyanciphisa inani lemibandela esiyidingayo ukuze siyihlanganise futhi yenza umbuzo ufundeke kakhulu. Isibonelo, esikhundleni sokusebenzisa izimo ezimbili ze-WHERE ezingenhla, singakwazi ukuveza umbuzo ofanayo njengo:

Hlunga UMkhiqizoName, I-UnitPrice KUSUKELA kwimikhiqizo OKUNYE I-UnitPrice phakathi kuka-15.00 NO-20.00

Njengezinye izigaba zethu zomthetho, NGAPHAMBI kokusebenza ngamanani ezinsimbi. Uma sifuna ukuveza uhlu lwawo wonke amazwe aqala ngo-V, W noma i-X, singasebenzisa umbuzo:

Hlunga umkhiqizo wamagama kusuka kumikhiqizo lapho INDLELA yomkhiqizo phakathi "A" no "D"

Okukhiqiza umphumela:

I-ProductName ------- I-Aniseed Syrup Chai Chang Chef I-Anton's Gumbo Mix Chef I-Cajun Seasoning

Isigaba se-WHERE siyinxenye enamandla yolimi lwe-SQL evumela ukuthi ukhawulele imiphumela kumanani awela ngaphansi kwezigaba ezicacisiwe. Kuvame ukusetshenziselwa ukusiza ukuveza i-logic yebhizinisi futhi kufanele ibe yingxenye yesikhungo samathuluzi esicwaningi se-database.

Kuvame ukusiza ukufaka izigaba ezivamile zibe nenqubo egcinwe ukwenza kube kufinyeleleke kulabo abangenalo ulwazi lwe-SQL.