{"id":259,"date":"2009-02-08T16:31:00","date_gmt":"1970-01-01T00:00:00","guid":{"rendered":"http:\/\/www.yazilimmutfagi.com\/?p=259"},"modified":"2009-02-08T16:31:00","modified_gmt":"1970-01-01T00:00:00","slug":"sql-server-da-kullanilan-fonksiyonlar-ve-deyimler","status":"publish","type":"post","link":"https:\/\/www.yazilimmutfagi.com\/index.php\/2009\/02\/08\/sql-server-da-kullanilan-fonksiyonlar-ve-deyimler\/","title":{"rendered":"SQL Server da Kullanilan Fonksiyonlar ve Deyimler"},"content":{"rendered":"

 <\/p>\n

Merhaba arkadaslar,<\/span><\/p>\n

Bu makalemizde SQL de sikça kullanilan bazi fonksiyonlarin ve deyimlerinin anlatimini yapacagiz.<\/span><\/p>\n

1-<\/span>      <\/span><\/span><\/span>IN: <\/strong>Sikça kullanilan bir SQL deyimidir. Kosul belirtirken kullaniriz .<\/span><\/span> <\/span><\/p>\n

a.<\/span>      <\/span><\/span><\/span>Mesela Musteri adli tablomuzda asagidaki kayitlar olsun ve biz de id’si 2 ve 3 olan kayitlari listelemek istiyoruz;<\/span><\/p>\n

\"\"<\/span><\/span><\/p>\n

SELECT<\/span> *<\/span> FROM<\/span> MUSTERI WHERE<\/span> ID IN<\/span> (<\/span>2,<\/span>3) <\/span><\/span><\/p>\n

\"\"<\/span><\/p>\n

.<\/a><\/p>\n

 <\/span><\/p>\n

2-<\/span>      <\/span><\/span><\/span>DATEDIFF(d,birinci Tarih, ikinci Tarih )<\/strong> : Iki tarih arasindaki farki döndürür. Çokça Ihtiyaciniz olacagini düsünüyorum<\/span><\/span><\/p>\n

DECLARE<\/span> @Date1 smalldatetime<\/span> <\/span><\/p>\n

DECLARE<\/span> @Date2 smalldatetime<\/span> <\/span><\/p>\n

 <\/span><\/p>\n

SET<\/span> @Date1 =<\/span> ‘2009.10.01’<\/span><\/span><\/p>\n

SET<\/span> @Date2 =<\/span> ‘2009.11.01’<\/span><\/span><\/p>\n

 <\/span><\/p>\n

SELECT<\/span> DATEDIFF<\/span>(<\/span>d,<\/span>@Date1,<\/span>@Date2)<\/span><\/span><\/p>\n

Sonuç = <\/span>31 <\/span><\/p>\n

3-<\/span>      <\/span><\/span><\/span>UPPER(str):<\/strong> STR metnindeki karakterlerin hepsini büyük harfe çevirir.<\/span><\/span><\/p>\n

DECLARE<\/span> @strBuyuk nvarchar<\/span>(<\/span>50)<\/span><\/span><\/p>\n

SET<\/span> @strBuyuk =<\/span> ‘Osman KALender’<\/span><\/span><\/p>\n

SELECT<\/span> UPPER<\/span>(<\/span>@strBuyuk)<\/span><\/span><\/p>\n

Sonuç<\/span> : OSMAN KALENDER<\/span><\/p>\n

4-<\/span>      <\/span><\/span><\/span>LOWER(str):<\/strong> STR metnindeki karakterlerin hepsini küçük harfe çevirir.<\/span><\/span><\/p>\n

DECLARE<\/span> @strKucuk nvarchar<\/span>(<\/span>50)<\/span><\/span><\/p>\n

SET<\/span> @strKucuk =<\/span> ‘Osman KALender’<\/span><\/span><\/p>\n

SELECT<\/span> LOWER<\/span>(<\/span>@strKucuk)<\/span><\/span><\/p>\n

 <\/span><\/p>\n

Sonuç<\/span> : osman kalender<\/span><\/p>\n

 <\/span><\/p>\n

5-<\/span>      <\/span><\/span><\/span>BETWEEN: <\/strong>Verilen kosuldaki iki deger arasinda olan kayitlari listelemek için kullanilir. <\/span><\/span><\/p>\n

 <\/span><\/p>\n

SELECT<\/span> *<\/span> FROM<\/span> MUSTERI WHERE<\/span> ID BETWEEN<\/span> 1 AND<\/span> 2<\/span><\/p>\n

 <\/span><\/p>\n

\"\"<\/span><\/span><\/p>\n

 <\/span><\/p>\n

6-<\/span>      <\/span><\/span><\/span>IsNull(@Deger1,@Deger2)<\/strong>  <\/span>: Isnull fonksiyonu eger @Deger1 NULL ise @Deger2 yi degilse @Deger1 i döndürür.<\/span><\/span><\/p>\n

DECLARE<\/span> @Deger1,@Deger2 int<\/span><\/span><\/p>\n

SET<\/span> @DEger2 =<\/span> 5<\/span><\/p>\n

SELECT<\/span> ISNULL<\/span>(<\/span>@Deger1,<\/span>@Deger2)<\/span><\/span><\/p>\n

Sonuç<\/span>: 5<\/span>   <\/span><\/strong><\/span><\/span><\/p>\n

@Deger1 degiskeni set edilmedigi için NULL dur. Bu yüzden de @Deger2 degiskenin sonucunu döndürmüstür.<\/span><\/p>\n

.<\/a><\/p>\n

7-<\/span>      <\/span><\/span><\/span>MAX, MIN, AVG<\/span><\/span>:<\/span><\/strong>  <\/span><\/span>Sorgu cümlesindeki en büyük degeri, en küçük degeri ve degerlerin ortalamasini bulmak için kullanilir.<\/span><\/p>\n


\n<\/span>\"\"<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

SELECT<\/span> MAX<\/span>(<\/span>UCRET),<\/span>MIN<\/span>(<\/span>UCRET),<\/span>AVG<\/span>(<\/span>UCRET)<\/span> FROM<\/span> MUSTERI<\/span><\/p>\n

 <\/span><\/p>\n

\"\"<\/span> <\/span><\/span><\/p>\n

 <\/span><\/p>\n

8-      <\/span><\/span><\/span> <\/span><\/span>UNION ve UNION ALL: <\/span><\/strong>Join deyimine benzer is yapar. Fakat Join den farkli olarak deger döndürmesini istedigimiz tablolarda ya ayni sayida kolon bulunacak ya da ayni sayida kolon seçecegiz. Sadece UNION kullanirsak iki tabloda ayni olan degerleri tekrarlamaz ve bir kere gösterir. UNION ALL kullanirsak iki tabloda ayni degerler olsa bile hepsini gösterir.
\n
\n<\/span><\/span><\/p>\n

MUSTERI tablosu
\n\"\"<\/span><\/span><\/p>\n

 <\/span><\/p>\n

CALISANLAR tablosu<\/span><\/p>\n

\"\"<\/span><\/p>\n


\n<\/span>SELECT<\/span> *<\/span> FROM<\/span> MUSTERI <\/span><\/p>\n

UNION ALL <\/span><\/p>\n

SELECT<\/span> *<\/span> FROM<\/span> CALISANLAR<\/span><\/p>\n

\"\"<\/span><\/p>\n

 <\/span><\/span><\/p>\n

Bir sonraki makalede görüsmek üzere….<\/span><\/span><\/p>","protected":false},"excerpt":{"rendered":"

Bu makalemizde SQL de sikça kullanilan bazi fonksiyonlarin ve deyimlerinin anlatimini yapacagiz. (IN, DATEDIFF, UPPER, LOWER, BETWEEN, IsNull, MAX, MIN, AVG, UNION, UNION ALL)<\/p>\n","protected":false},"author":550,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[422],"tags":[6327,5991,6478,6429,5297,6454,6469,6418,4957,6132,6444,5820,6216,6286,6488,3877,4557,5120,5151,5586],"_links":{"self":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/259"}],"collection":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/users\/550"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/comments?post=259"}],"version-history":[{"count":0,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/259\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/media?parent=259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/categories?post=259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/tags?post=259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}