<\/div>\n

Bu yaz\u0131m\u0131zda Sql Serverda,TRANSACT SQL (T-SQL) dilinde select komutuyla veritaban\u0131nda sorgulama i\u015flemleri ger\u00e7ekle\u015ftirece\u011fim.Where \u015fart\u0131 ile belirli kriterlerde veri se\u00e7imleri yapaca\u011f\u0131z.
\n\u00d6ncelikle Transact Sql \u2018e de\u011finelim.
\nTransact Sql<\/span><\/strong>
\nT-SQL ile veri \u00e7ekme,veri kaydetme,g\u00fcncelleme ve silme gibi veritaban\u0131na m\u00fcdahale edebilir,database olu\u015fturma,tablo olu\u015fturma ve veritaban\u0131 silme gibi i\u015flemlerde de kullanabiliriz.<\/p>\n

T-SQL b\u00fcy\u00fck k\u00fc\u00e7\u00fck harfe duyarl\u0131 bir dil de\u011fildir.
\n\u015eimdi,Select komutuyla ilgili anlat\u0131m ve \u00f6rneklere ge\u00e7meden \u00f6nce \u00fczerinde \u00e7al\u0131\u015fma yapmam\u0131z i\u00e7in \u00f6zel olarak Microsoft taraf\u0131ndan haz\u0131rlanm\u0131\u015f \u201cNorthwind\u201d adl\u0131 veritaban\u0131n\u0131 SQL Server\u0131m\u0131za Execute ediyoruz.<\/p>\n

\u015eimdi \u201cNew Query\u201d butonuna t\u0131klayarak kod sayfas\u0131na geliyoruz.<\/p>\n

\u201cAvailable Databases\u201d adl\u0131 a\u00e7\u0131l\u0131r pencereden \u201cNorthwind\u201d veritaban\u0131n\u0131 se\u00e7memiz gerekiyor.Ya da a\u015fa\u011f\u0131daki kodlarla hangi veritaban\u0131 \u00fczerinden \u00e7al\u0131\u015ft\u0131\u011f\u0131m\u0131z\u0131 g\u00f6sterebiliriz.<\/p>\n

\n
\r\n----use Northwind----\r\n<\/pre>\n
\n
NOT:E\u011fer \u201cAvailable Databases\u201d k\u0131sm\u0131nda ba\u015fka veritaban\u0131 se\u00e7ili oldu\u011fu halde yukar\u0131daki komutu kod sayfas\u0131na yazarsan\u0131z,bundan sonra \u201cNorthwind\u201d\u00a0 adl\u0131 veritaban\u0131 i\u015flevsel olacakt\u0131r.<\/p>\n
VER\u0130 \u00c7EKME \u0130\u015eLEM\u0130 (SELECT)<\/strong><\/span>
\n\u201cNorthwind\u201d adl\u0131 veritaban\u0131na g\u00f6z atarsan\u0131z \u201cPersoneller\u201d adl\u0131 bir tablo g\u00f6receksiniz.Bu tablodaki b\u00fct\u00fcn bilgileri se\u00e7mek istersek e\u011fer a\u015fa\u011f\u0131daki \u015fekilde kodlamal\u0131s\u0131n\u0131z,<\/p>\n
\n
\r\n----select * from Personeller----\r\n<\/pre>\n
\n
Buradaki *,\u201dPersoneller\u201d tablosundaki t\u00fcm kolonlar\u0131 temsil etmektedir.Fakat performans a\u00e7\u0131s\u0131ndan * yerine t\u00fcm kolonlar\u0131n ismini teker teker yazmak daha avantajl\u0131d\u0131r.
\n\u201cPersoneller\u201d adl\u0131 tablodan sadece Ad ve Soyad kolonlar\u0131n\u0131 se\u00e7mek istersek e\u011fer,<\/p>\n
\n
\r\n----select Adi,SoyAdi from Personeller----\r\n<\/pre>\n
\n
Bu \u015fekilde istedi\u011fimiz kolonlar\u0131 virg\u00fclle yaz\u0131p \u00e7ekebiliriz.<\/p>\n
Yukarda \u00e7ekti\u011fimiz kolonlar\u0131n sat\u0131r ba\u015fl\u0131klar\u0131,kolon isimleriyle ayn\u0131d\u0131r.\u00c7ekilen kolonlara istedi\u011fimiz alias tan\u0131mlamay\u0131 \u015fu \u015fekilde yap\u0131yoruz.<\/p>\n
\n
\r\n----select Adi as ADI,SoyAdi as SOYADI from Personeller----\r\n<\/pre>\n
\n
\u00d6nce kolonun ad\u0131n\u0131 yazd\u0131ktan sonra \u201cas\u201d operat\u00f6r\u00fcn\u00fc kullanarak o kolona vermek istedi\u011fimiz ismi(alias) yaz\u0131yoruz.<\/p>\n
E\u011fer yazaca\u011f\u0131m\u0131z alias ta bo\u015fluk karakteri varsa,\u00d6RN(AD SOYAD), yukar\u0131daki gibi \u201cas\u201d operat\u00f6r\u00fcnden sonra o alias\u0131 yazarsak hata verecektir.Bu hatay\u0131 \u00f6nlemek i\u00e7in k\u00f6\u015feli parantez kullan\u0131lmal\u0131d\u0131r.<\/p>\n
\n
\r\n----select Adi as [\u00d6\u011frenci Ad\u0131]\u00a0 from Personeller----\r\n<\/pre>\n
\n
Ayn\u0131 \u015fekilde \u00fczerinde \u00e7al\u0131\u015fmam\u0131z gereken tablonunda isminde bo\u015fluk karakteri varsa k\u00f6\u015feli parantez i\u00e7inde yazabiliriz.<\/p>\n
\n
\r\n----select BirimFiyati from [Satis Detaylari]----\r\n<\/pre>\n
\n
\u015eimdi ise iki \u201cPersoneller\u201d tablosundan Ad ve Soyad\u0131 \u00e7ekelim.Ama iki tabloda ayr\u0131 ayr\u0131 kolonlarda gelmesini de\u011filde tek bir kolonda gelmesini istiyoruz.Bu i\u015flem iki kolonu birle\u015ftirerek tek kolon haline getiriyor.Tabi haliyle kolonlar\u0131n ismini birle\u015ftirmedi\u011fi i\u00e7in ben a\u015fa\u011f\u0131daki \u00f6rnekte \u201cas\u201d operat\u00f6r\u00fcyle birle\u015ftirilmi\u015f kolonlara isim verdim.<\/p>\n
\n
\r\n----select Adi + ' ' + SoyAdi as [AD SOYAD] from Personeller----\r\n<\/pre>\n
\n
Yukar\u0131daki \u00f6rnekte g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi \u201cAdi\u201d ve \u201cSoyadi\u201d kolonlar\u0131n\u0131n aras\u0131na virg\u00fcl de\u011filde, birle\u015ftirme operat\u00f6r\u00fc olan + i\u015fareti konmustur.Tek t\u0131rnaklar ise iki kolonun birle\u015fmesi sonucu araya bo\u015fluk koydurmaya yar\u0131yor.<\/p>\n
Unutmamam\u0131z gereken bir nokta var.+ operat\u00f6r\u00fc ile bir tek metinsel verileri birle\u015ftirebiliriz.<\/p>\n
\u015eimdi \u201cPersoneller\u201d tablosunda \u201cAdi\u201d,\u201dSoyadi\u201d kolonlar\u0131yla birlikte \u201cIseBaslamaTarihi\u201d kolonunu + operat\u00f6r\u00fcyle birle\u015ftirelim.<\/p>\n
\n
\r\n----select Adi+ ' ' + SoyAdi + ' ' + IseBaslamaTarihi from Personeller----\r\n<\/pre>\n
\n
Yukardaki komutlar\u0131 \u00e7al\u0131\u015ft\u0131rd\u0131ktan sonra hatayla kar\u015f\u0131laca\u011f\u0131z.\u00c7\u00fcnk\u00fc birka\u00e7 sat\u0131r \u00fcstte dedi\u011fim gibi + operat\u00f6r\u00fc metinsel ifadelerde kullan\u0131l\u0131r.Ancak biz burada iki metinsel ifadeyle birlikte datetime tipli \u201cIseBaslamaTarihi\u201d kolonunuda birle\u015ftirmeye cal\u0131st\u0131k.<\/p>\n
Bu birle\u015ftirmenin olmas\u0131 i\u00e7in bu tipi metinsel tipe \u00e7evirmemiz laz\u0131m.<\/p>\n
\n
\r\n----select Adi+ ' ' + SoyAdi +' '+ CONVERT(nvarchar(50),IseBaslamaTarihi,104) from Personeller----\r\n<\/pre>\n
\n
Convert metoduyla \u201cIseBaslamaTarihi\u201d adl\u0131 datetime tipi nvarchar(50) ye d\u00f6n\u00fc\u015ft\u00fcrd\u00fcm.Yukardaki yap\u0131da \u201c,104\u201d ifadesi ise tarihin nas\u0131l bi\u00e7imlendirilece\u011fidir.<\/p>\n
Convert metoduyla tip d\u00f6n\u00fc\u015f\u00fcm\u00fc yap\u0131labildi\u011fi gibi Cast metoduylada yap\u0131labilir.Sadece aradaki tek fark tarih bi\u00e7imini bunda se\u00e7emiyoruz.<\/p>\n
\n
\r\n---- select Adi + ' ' + SoyAdi + ' ' + CAST(IseBaslamaTarihi as varchar(50)) from Personeller----\r\n<\/pre>\n
\n
Yukarda Cast metodunu kullan\u0131rken parantezin i\u00e7inde \u201cas\u201d operat\u00f6r\u00fcyle birdaha kar\u015f\u0131la\u015f\u0131yoruz.Bu operat\u00f6r sayesinde cast metodunda tip de\u011fi\u015fimi ger\u00e7ekle\u015fiyor.<\/p>\n
SORGUYA \u015eART \u0130FADES\u0130 YAZMAK(WHERE)<\/strong><\/span><\/p>\n
T-SQL de kolonlar\u0131 \u00e7ektikten sonra veriler aras\u0131nda \u201cWhere\u201d komutuyla sorgulama yapabiliyoruz.\u00d6rn, \u201cPersoneller\u201d tablosunda \u015fehri \u201cLondon\u201d olan ki\u015filerin \u201cAd\u0131\u201d kolonunu \u00e7ekelim.Yani \u201cPersoneller\u201d tablosundaki \u201cAdi\u201d tablosunu tam de\u011filde,\u015fehir \u015fart\u0131na uyan veriler listelenecek.<\/p>\n
\n
\r\n---- select Adi from Personeller Where Sehir= 'London' ----\r\n<\/pre>\n
\n
Yukar\u0131daki yap\u0131da \u201cWhere\u201d kodu sayesinde \u201cSehir\u201d kolonunda \u201cLondon\u201d olan verileri buluyor ve adlar\u0131n\u0131 yazd\u0131r\u0131yor.<\/p>\n
\u015eehri \u201cLondon\u201d olan verilerin ad ve soyadlar\u0131n\u0131(istedi\u011fimiz b\u00fct\u00fcn verilerini) a\u015fa\u011f\u0131daki \u015fekilde \u00e7ekebiliriz.<\/p>\n
\n
\r\n---- select Adi + ' ' + SoyAdi as [Ad ve Soyad] from Personeller where Sehir='London'----\r\n<\/pre>\n
\n
\u015eehri \u201cLondon\u201d olan ve ad\u0131 \u201cRobert\u201d olan verinin i\u015fe ba\u015flama tarihini s\u0131ralayal\u0131m.<\/p>\n
\n
\r\n---- select IseBaslamaTarihi from Personeller where Sehir='London' and Adi='Robert'----\r\n<\/pre>\n
\n
Yukarda g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi \u201cWhere\u201d komutundan sonra \u201cand\u201d komutunu kullanarak \u015fartlar\u0131m\u0131z\u0131 \u00e7o\u011faltabiliyoruz.
\n\u015eehri \u201cLondon\u201d ya da \u201cRedmond\u201d olan verilerin ad\u0131n\u0131 s\u0131ralayal\u0131m.<\/p>\n
\n
\r\n---- select Adi from Personeller Where Sehir='London' or Sehir='redmond'----\r\n<\/pre>\n
\n
Yukar\u0131da \u201cor\u201d komutuyla \u015fehri \u201cLondon\u201d ve ya \u201cRedmond\u201d olan ki\u015filerin ad\u0131n\u0131 se\u00e7ebildik.<\/p>\n
\u015eimdide ad\u0131 \u201cRobert\u201d soyad\u0131 \u201cKing\u201d olan ki\u015finin t\u00fcm bilgilerini \u00e7ekmek istiyorum.<\/p>\n
\n
\r\n---- select * from Personeller where Adi='Robert' and SoyAdi='King'----\r\n<\/pre>\n
\n
ID numaras\u0131 5 ten k\u00fc\u00e7\u00fck ve e\u015fit olan kolonlar\u0131n adlar\u0131n\u0131 se\u00e7elim.<\/p>\n
\n
\r\n---- select Adi,PersonelID from Personeller where PersonelID <= 5----\r\n<\/pre>\n
\n
Yukar\u0131daki gibi b\u00fcy\u00fck,k\u00fc\u00e7\u00fck,e\u015fit vs gibi \u015fartlar\u0131 a\u015fa\u011f\u0131daki sembollerle ifade edebiliyoruz.
\n<>\u00a0 : E\u015fit de\u011filse.
\n<=\u00a0 :K\u00fc\u00e7\u00fck ve e\u015fitse.
\n>=\u00a0 :B\u00fcy\u00fck ve e\u015fitse.
\n=\u00a0\u00a0\u00a0 :E\u015fitse.
\n——<\/p>\n
E\u011fer elimizde datetime tipli veri varsa, bu veri i\u00e7inde g\u00fcn, ay, y\u0131l, saat vs gibisinden bilgilerde vard\u0131r.Bize sadece g\u00fcn,ay,y\u0131l vs laz\u0131msa,bunu elde etmek i\u00e7in tarih fonksiyonlar\u0131n\u0131 kullanmal\u0131y\u0131z.<\/p>\n
\u015eimdi bir \u00f6rnek yapal\u0131m.1993 y\u0131l\u0131ndan sonra i\u015fe ba\u015flayanlar\u0131 listeleyelim.Bunu yapabilmek i\u00e7in datetime tipindeki \u201cIseBaslamaTarihi\u201d\u2019den sadece y\u0131l\u0131 se\u00e7mem gerekecek.<\/p>\n
\n
\r\n---- select IseBaslamaTarihi from Personeller where YEAR(IseBaslamaTarihi)>=1993----\r\n<\/pre>\n
\n
Yukar\u0131da g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi \u201cYEAR()\u201d metoduyla datetime tipinin y\u0131l\u0131na ula\u015fabiliyoruz.<\/p>\n
Peki May\u0131s ay\u0131nda do\u011fan personelleri listelersek,<\/p>\n
\n
\r\n---- select Adi from Personeller where MONTH(IseBaslamaTarihi)=05 ----\r\n<\/pre>\n
\n
Yukar\u0131da g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi \u201cMONTH()\u201d metoduyla datetime tipinin ay\u0131na ula\u015fabiliyoruz.<\/p>\n
Peki do\u011fum g\u00fcn\u00fc ay\u0131n 29 u olmayan personelleri listeleyelim<\/p>\n
\n
\r\n---- select Adi from Personeller where DAY(IseBaslamaTarihi) <>29 ----\r\n<\/pre>\n
\n
Yukar\u0131da g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi \u201cDAY()\u201d metodula datetime tipinin g\u00fcn\u00fcne ula\u015fabiliyoruz.
\nDo\u011fum y\u0131llar\u0131 1950 ile 1965 y\u0131llar\u0131 aras\u0131ndaki personellerin ad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n---- select Adi from Personeller where YEAR(DogumTarihi)>=1950 and YEAR(DogumTarihi)<=1965----\r\n<\/pre>\n
\n
Aral\u0131k belirten ifadeler yukar\u0131daki \u015fekilde yap\u0131labilece\u011fi gibi \u201cbetween\u201d\u00a0 komutu kullanarakta yapabiliriz.\u201dbetween\u201d komutu aras\u0131nda demektir.<\/p>\n
\n
\r\n---- select * from Personeller where YEAR(DogumTarihi) between 1950 and 1965----\r\n<\/pre>\n
\n
Ya\u015fad\u0131\u011f\u0131 \u015fehir, London,Tacoma,ya da Kirkland olan personellerin ad\u0131n\u0131 listeleyelim.<\/p>\n
\n
\r\n---- select adi from Personeller where Sehir='London' or Sehir='Tacoma' or Sehir='Kirkland'----\r\n<\/pre>\n
\n
Yukar\u0131da g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi bir yap\u0131da s\u00fcrekli \u201cor,or,or..\u201d kullan\u0131yorsak bunu \u201cin\u201d operat\u00f6r\u00fc ilede yapabiliriz.<\/p>\n
\n
\r\n---- select * from Personeller where Sehir in ('London','Tacoma','Kirkland')----\r\n<\/pre>\n
\n
L\u0130KE SORGULARI<\/span><\/strong>
\nVerilerimiz aras\u0131nda belirli kriterlere g\u00f6re sorgulama yapabiliriz.
\n\u00d6rn;\u0130sminin ba\u015f harfi \u015fuysa,son harfi buysa,son \u00fc\u00e7 harfi,ilk d\u00f6rt harfi,metnin i\u00e7inde bu varsa \u2026 gibisinden kriterlere g\u00f6re sorgulama yapmak istiyorsak \u201cLike\u201d sorgusunu kullan\u0131r\u0131z.<\/p>\n
\u015eimdi \u00f6rneklere ge\u00e7elim.
\n\u0130sminin ba\u015f harfi \u201cj\u201d olan personellerin ad\u0131n\u0131 soyad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n---- select Adi,SoyAdi from Personeller where Adi like 'j%'----\r\n<\/pre>\n
\n
Yukar\u0131da g\u00f6rd\u00fc\u011f\u00fcn\u00fcz kodlarda \u201cLike\u201d komutundan sonra iki t\u0131rnak i\u00e7inde \u2018j%\u2019 yazd\u0131k.Bu ifadenin a\u00e7\u0131klamas\u0131 \u201cba\u015f harfi j olan,gerisi \u00f6nemli de\u011fil.\u201d demektir.<\/p>\n
\u0130sminin son harfi \u201cy\u201d olan personellerin ad\u0131n\u0131 soyad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n---- select adi,soyadi from Personeller where Adi like '%y'----\r\n<\/pre>\n
\n
Ba\u015f harfindekiyle ayn\u0131 mant\u0131k olan \u2018%y\u2019 ifadesi \u201cson harfi y olan,gerisi \u00f6nemli de\u011fil.\u201d Demektir.<\/p>\n
\u0130sminin son \u00fc\u00e7 harfi \u201cert\u201d olan personellerin ad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller where Adi Like '%ert'----\r\n<\/pre>\n
\n
\u2018%ert\u2019 = \u201cSon \u00fc\u00e7 harfi ert olan,gerisi \u00f6nemli de\u011fil.\u201d<\/p>\n
\u0130sminin ilk harfi \u201cr\u201d son harfi \u201ct\u201d olan personellerin ad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller where Adi Like 'r%t'----\r\n<\/pre>\n
\n
\u2018r%t\u2019 =\u201dilk harfi r,son harfi t olan,gerisi \u00f6nemli de\u011fil.\u201d
\nAsl\u0131nda a\u015fa\u011f\u0131daki \u015fekilde de yap\u0131labilir ama gerek yok.<\/p>\n
\n
\r\n---- select adi,soyadi from Personeller where Adi like 'r%' and Adi like '%t'----\r\n<\/pre>\n
\n
\u0130sminin i\u00e7inde \u201can\u201d ge\u00e7en personellerin ad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller where Adi like '%an%'----\r\n<\/pre>\n
\n
\u2018%an%\u2019 = \u201ci\u00e7inde an ge\u00e7en,gerisi \u00f6nemli de\u011fil.\u201d
\nBurada dikkat etmemiz gereken nokta, verimizin i\u00e7inde ge\u00e7en \u201can\u201d s\u00f6zc\u00fc\u011f\u00fc ba\u015fla,sonda olmayacak diye bir \u015fart yok.\u201dAnkara\u201d da ba\u015ftaki ilk iki karakter oldu\u011fu halde bu veri bu \u015fartda se\u00e7ilecektir.<\/p>\n
\u0130sminin ba\u015f harfi \u201cn\u201d olan,i\u00e7indede \u201can\u201d ge\u00e7en personellerin ad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller where Adi like 'n%an%' ----\r\n<\/pre>\n
\n
\u2018n%an%\u2019 = \u201cBa\u015f harfi n olan,i\u00e7inde de an olan,gerisi \u00f6nemli de\u011fil.\u201d<\/p>\n
\u0130sminin ilk harfi \u201ca\u201d,ikinci harfi fark etmez,\u00fc\u00e7\u00fcnc\u00fc harfi ise \u201cd\u201d olan personellerin ad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n----- select Adi,Soyadi from Personeller where Adi like 'a_d%'----\r\n<\/pre>\n
\n
Yukar\u0131daki yap\u0131da,t\u0131rnak i\u00e7indeki \u201c_\u201d karakteri es ge\u00e7 anlam\u0131ndad\u0131r.Yani \u201cilk harfi a,ikinci harfi es ge\u00e7,\u00fc\u00e7\u00fcnc\u00fc harfi d olan,gerisi \u00f6nemli de\u011fil.\u201d Demektir.<\/p>\n
\u0130lk harfi \u201cm\u201d,2.3.4. fark etmez,5. \u201ca\u201d olan personelin ad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller where Adi like 'm___a%'----\r\n<\/pre>\n
\n
..Ya da..<\/span><\/strong>
\n\u0130sminin ilk harfi \u201cn\u201d ya da \u201cm\u201d ya da \u201cr\u201d olan personelin ad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller where Adi like '[nmr]%'----\r\n<\/pre>\n
\n
Yukar\u0131daki yap\u0131da,t\u0131rnak i\u00e7inde olan k\u00f6\u015feli parantez tek bir haneyi temsil edip ya da mant\u0131\u011f\u0131n\u0131 i\u015fliyor.Yani \u201c[nmr]=ilk harf n ya da m ya da r olan,gerisi \u00f6nemli de\u011fil.\u201d<\/p>\n
\u0130sminin i\u00e7inde \u201ca\u201d ya da \u201ci\u201d ge\u00e7en personelin ad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller where Adi like '%[ai]%'----\r\n<\/pre>\n
\n
\u2018%[ai]%\u2019 = \u201ci\u00e7inde a ya da i ge\u00e7en,di\u011ferleri \u00f6nemsiz olan\u201d<\/p>\n
\u0130sminin ba\u015f harfi \u201ca\u201d ile \u201ck\u201d aras\u0131nda alfabetik s\u0131raya g\u00f6re herhangi bir harf olan personellerin ad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller where Adi like '[a-k]%'----\r\n<\/pre>\n
\n
Yukar\u0131daki yap\u0131da t\u0131rnak i\u00e7inde \u2018[a-k]%\u2019 ifadesini a\u00e7\u0131klarsak e\u011fer,k\u00f6\u015feli parantezler bildi\u011fimiz gibi tek haneyi temsil edip,ya da mant\u0131\u011f\u0131n\u0131 i\u015fliyor.Tire(-) ise \u201ca\u201d ile \u201ck\u201d karakterleri aras\u0131ndaki alfabetik s\u0131ray\u0131 inceliyor.\u201d%\u201d ifadesi ise bildi\u011finiz gibi gerisi \u00f6nemsiz demektir.<\/p>\n
\u0130sminin ba\u015f harfi \u201ca\u201d olmayan personellerin ad\u0131n\u0131 yazd\u0131ral\u0131m.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller where Adi like '[^a]%'----\r\n<\/pre>\n
\n
Yukar\u0131da g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi \u201c^\u201d i\u015fareti olmayan anlam\u0131na gelmektedir.<\/p>\n
Verilen kolonun karakter say\u0131s\u0131n\u0131 almak istiyorsak \u201cLen()\u201d fonksiyonunu kullanmam\u0131z gerekecektir.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller where LEN(Adi) < 6----\r\n<\/pre>\n
\n
Yukardaki kodlarda ad\u0131ndaki karakter say\u0131s\u0131 6\u2019dan k\u00fc\u00e7\u00fck olan ki\u015filerin ad soyadlar\u0131n\u0131 getir dedik.<\/p>\n
SIRALAMA(ORDER BY)<\/span><\/strong>
\nVerilerimizi \u00e7ekti\u011fimizde istedi\u011fimiz kolona g\u00f6re s\u0131ralama yapmak isteyebiliriz.\u201dorder by\u201d komutuyla s\u0131ralama yapabiliriz.\u201dorder by\u201d \u2018\u0131n a\u00e7\u0131klamas\u0131 \u201c\u015funa g\u00f6re s\u0131rala\u201d demektir.\u015eimdi bir \u00f6rnek \u00fczerinde g\u00f6sterelim.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller order by SoyAdi----\r\n<\/pre>\n
\n
Yukar\u0131daki yap\u0131da dikkat ederseniz art\u0131k \u201cWhere\u201d komutu kullanm\u0131yoruz.\u00c7al\u0131\u015ft\u0131\u011f\u0131m\u0131z tablonun ismini yazd\u0131ktan sonra \u201corder by\u201d yazarak s\u0131ralama yap\u0131lmas\u0131n\u0131 istedi\u011fimiz kolonu yazd\u0131k.
\nBurada dikkat etmemiz gereken nokta,kolonun i\u00e7inde metinsel ifadeler varsa alfabetik olarak s\u0131ralar.Say\u0131sal verilerde ise k\u00fc\u00e7\u00fckten b\u00fcy\u00fc\u011fe do\u011fru s\u0131ralar.<\/p>\n
NOT:\u201dorder by\u201d varsay\u0131lan olarak k\u00fc\u00e7\u00fckten b\u00fcy\u00fc\u011fe s\u0131ralar.(asc)<\/p>\n
\n
\r\n--- select Adi,Soyadi from Personeller order by SoyAdi asc----\r\n<\/pre>\n
\n
\u201casc\u201d kodu olursa b\u00fcy\u00fckten k\u00fc\u00e7\u00fc\u011fe s\u0131ralar.Olmazsa varsay\u0131lan olarak ayn\u0131 oldu\u011fu i\u00e7in de\u011fi\u015fiklik olmaz.<\/p>\n
E\u011fer b\u00fcy\u00fckten k\u00fc\u00e7\u00fc\u011fe s\u0131ralatmak istiyorsan\u0131z \u201cdesc\u201d komutunu kullanmak yeterlidir.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller order by SoyAdi desc----\r\n<\/pre>\n
\n
Personellerim do\u011fum y\u0131llar\u0131na g\u00f6re s\u0131ral\u0131 gelsin.<\/p>\n
\n
\r\n---- select Adi,Soyadi from Personeller order by YEAR(IseBaslamaTarihi) desc----\r\n<\/pre>\n
\n
Personellerim \u015fehirlerine g\u00f6re s\u0131ral\u0131 gelsin.<\/p>\n
\n
\r\n---- select Adi,Soyadi,Sehir from Personeller order by Sehir----\r\n<\/pre>\n
\n
Yukardaki yap\u0131ya bakarsan\u0131z i\u00e7inde \u201cLondon\u201d olan 4 veri var.S\u0131ralatma yapt\u0131\u011f\u0131m\u0131z kolonda ayn\u0131 veriler varsa,o verileri kendi aralar\u0131nda ba\u015fka bir kolonda s\u0131ralatma yapabiliriz.Ayn\u0131 \u015fekilde o kolonda da tekrarlayan varsa gene onlar\u0131 ba\u015fka\u00a0 kolonda s\u0131ralatma yapabiliriz<\/p>\n
\u015eimdi personellerim \u015fehirlerine g\u00f6re s\u0131ralans\u0131n,e\u011fer ayn\u0131 \u015fehirde olan varsa adlar\u0131na g\u00f6re s\u0131ralans\u0131n.<\/p>\n
\n
\r\n---- select adi, soyadi ,sehir from Personeller order by Sehir,Adi----\r\n<\/pre>\n
\n
Yukar\u0131daki yap\u0131da g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi \u00f6nce \u015fehire g\u00f6re s\u0131ralama yap\u0131ld\u0131,tekrarlayanlar varsa ad\u0131na g\u00f6re s\u0131ralama yap\u0131ld\u0131.<\/p>\n
Do\u011fum y\u0131l\u0131 1960 dan b\u00fcy\u00fck olan personellerimi soyisimlerine g\u00f6re s\u0131rala.<\/p>\n
\n
\r\n---- select Adi,Soyadi,Sehir from Personeller where YEAR(DogumTarihi) > 1960 order by SoyAdi----\r\n<\/pre>\n
\n
AGGREGATE FONKS\u0130YONLAR<\/span><\/strong>
\n— AVG : ORTALAMA ALIR
\n— MAX : EN B\u00dcY\u00dcK DE\u011eER\u0130 BULUR
\n— MIN : EN K\u00dc\u00c7\u00dcK DE\u011eER\u0130 BULUR
\n— COUNT : TOPLAM SAYISINI VER\u0130R
\n–SUM : TOPLAMINI VER\u0130R<\/p>\n
Toplam ka\u00e7 adet personelim var.<\/p>\n
\n
\r\n---- select COUNT(PersonelID) as [Toplam Personel Say\u0131s\u0131] from Personeller----\r\n<\/pre>\n
\n
Yukarda g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi \u201cCount()\u201d metodu kolondaki kay\u0131t say\u0131s\u0131n\u0131 verir.O kolondaki \u201cnull\u201d de\u011ferleri saymaz.<\/p>\n
En b\u00fcy\u00fck ID numaral\u0131 personelimi getir.<\/p>\n
\n
\r\n---- select MAX(PersonelID) from Personeller----\r\n<\/pre>\n
\n
En k\u00fc\u00e7\u00fck ID numaral\u0131 personelimi getir.<\/p>\n
\n
\r\n--- select MIN(PersonelID) from Personeller----\r\n<\/pre>\n
\n
Personellerimin IDlerinin ortalamas\u0131 ka\u00e7t\u0131r?<\/p>\n
\n
\r\n---- select AVG(PersonelID) from Personeller----\r\n<\/pre>\n
\n
NOT: Sorgu k\u0131sm\u0131nda matematiksel i\u015flemlerde yap\u0131l\u0131r.<\/p>\n
\n
\r\nselect (SUM(PersonelID)-9)\/(COUNT(PersonelID)-1) from Personeller\r\n<\/pre>\n
\n","protected":false},"excerpt":{"rendered":"
Bu yaz\u0131m\u0131zda Sql Serverda,TRANSACT SQL (T-SQL) dilinde select komutuyla veritaban\u0131nda sorgulama i\u015flemleri ger\u00e7ekle\u015ftirece\u011fim.Where \u015fart\u0131 ile belirli kriterlerde veri se\u00e7imleri yapaca\u011f\u0131z.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22],"tags":[72,73,74,75,76],"class_list":["post-158","post","type-post","status-publish","format-standard","hentry","category-veritabani","tag-aggregate","tag-like","tag-order-by","tag-select","tag-where"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/comments?post=158"}],"version-history":[{"count":0,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/158\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media?parent=158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/categories?post=158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/tags?post=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}