Transact Sql(String Fonk.-Matematik Fonk.-Tarih Fonk.-Top-Distinct-Escape-Group By-Having-Joinler-Insert-Update-Delete)

Bu yazımda T-SQL’e devam ediyorum.SQL de çok kullanacağımız inser,update ve delete işlemleri temel seviyede anlatmaya çalışacağım.Tabi bunlardan önce bazı fonksiyonlar ve kullanacağımız özelliklere değineceğim.
STRİNG FONKSİYONLARI
Aşağıda bazı string fonksiyonları verilmiştir.
LEFT : Soldan(baştan) belirtilen sayıda karakteri getirir

----select Left(Adi,2) from Personeller----

RIGHT :Sağdan(sondan) belirtilen sayıda karakteri getirir.

----select RIGHT(Adi,3) from Personeller----

Upper : Büyük harfe çevirir.

----select UPPER(Adi) from Personeller----

Lower : Küçük harfe çevirir.

----select LOWER(Adi) from Personeller----

Substring : Belirtilen indexten itibaren belirtilen sayıda karakter getir.

----select SUBSTRING(Adi,2,4) from Personeller----

Ltrim : Soldan boşlukları keser.

-----select LTRIM('         asdasdas ')----

Rtrim : Sağdaki boşlukları keser.

----select RTRIM('              asdasd            ')----

Reverse : Tersine çevirir.
Rtrim : Sağdaki boşlukları keser.

----select REVERSE(Adi) from Personeller----

Replace : Belirtilen ifadeyi,belirtilen ifade ile değiştirir.

----select REPLACE('Benim Adım Gençay','Gençay','Kür Şad')----

Not:Bu fonksiyonları iç içe kullanabiliriz.

----select SUBSTRING(UPPER(REVERSE(Adi)),3,3) from Personeller----

Charindex : Belirtilen karakterin veri içinde index numarasını verir.

----select MusteriAdi,CHARINDEX(' ',MusteriAdi) from Musteriler----

“Musteriler” tablosunun “MusteriAdi” kolonundan sadece soyadlarını çekelim.

----select SUBSTRING(MusteriAdi,CHARINDEX(' ',MusteriAdi),LEN(MusteriAdi)-CHARINDEX(' ',MusteriAdi)) from Musteriler----

SAYISAL DEĞER İŞLEMLERİ
Aşağıda bazı matematik fonksiyonları verilmiştir.

select 3+2 Şeklinde bir kullanım yapılabilir.
select PI() : Pi sayısını verecektir.
select SIN(PI()) : Sin alır.
select POWER(2,3) : Üs alır.
select ABS(-12) :Mutlak değer alır.
select RAND() : 0-1 arasında rastgele sayı üretir.
select FLOOR(RAND()*100) : Yuvarlama yapar.

NOT:Hazır fonksiyonları görebilmek için “Object Explorer”’dan herhangi bir veritabanına gelip Programmability – FUnctions bölümüne bakılabilir.

TARİH FONKSİYONLARI
Aşağıda bazı tarih fonksiyonları verilmiştir.

select GETDATE() : Bu günün tarihini verir.
select DATEADD(YEAR,999,GETDATE()) : ‘GETDATE()’ metodundaki tarihe 999 yıl ekler.
select DATEADD(DAY,999,GETDATE())  : ‘GETDATE()’ metodundaki tarihe 999 gün ekler.
select DATEADD(MONTH,999,GETDATE()) : ‘GETDATE()’ metodundaki tarihe 999 ay ekler.
select DATEDIFF(YEAR,'05.09.1992',GETDATE()) : İki tarih arasındaki yıl farkını hesaplar.
select DATEDIFF(MONTH,'05.09.1992',GETDATE())  :İki tarih arasındaki ay farkını hesaplar.
select DATEDIFF(DAY,'05.09.1992',GETDATE()) : İki tarih arasındaki gün farkını hesaplar.
select DATEPART(MONTH,GETDATE()) : Verilen tarihte, Yılın kaçıncı ayı.
select DATEPART(DAY,GETDATE()) : Verilen tarihte, Ayın kaçıncı günü.
select DATEPART(DW,GETDATE()) : Verilen tarihte,haftanın kaçıncı günü.

TOP
Sorgu sonucu gelen kayıtlarımın ilk kaç tanesinin geleceğini belirlemek için kullanıyorum.
select top 3 Adi from Personeller
Distinct
Bir kolondaki aynı verileri bir kabul eder.
“Personeller” tablosunda “Sehir” kolonunda birden fazla “London” var.

select distinct Sehir from Personeller

Eğer bu “distinct” kodunu kullanırsak , “London” bir tane gözükecektir.

ESCAPE (KAÇIŞ) KARAKTERLERİ

Verilerimizi “Like” şartına göre çekerken,”Like” için özel anlamı olan “%,_,-” gibi ifadeler,eğer kayıtlarımızın içinde geçiyorsa,hatalı sonuç almamız olası bir durumdur.Bu gibi durumlar da “ESCAPE” karakterlerini kullanmalıyız.

select BolgeTanimi from Bolge where BolgeTanimi like 'deneme_%'

“Northwind” veritabanında çalıştığımızdan dolayı yukarıdaki kod parçasının sonuç tablosu hepimizde aynı olacaktır.Ben burada “deneme_” den sonra gerisi önemli değil demek isterken,sistem “deneme” den sonra gerisini önemli değil kabul edecektir.Bunun sebebi,”_” özel karakter olmasıdır.
‘deneme_%’ = ‘ilk altı harfi deneme olan,7. Harfi önemli olmayan ve geriside önemli olmayan’ demektir.

İşte bu sorunu halletmemiz için iki yolumuz var.

select BolgeTanimi from Bolge where BolgeTanimi like 'deneme[_]%'

Yukardaki yapıda gördüğünüz gibi ilk yol,özel anlam ifade eden karakteri köşeli parantez içine almaktır.
‘deneme[_]%’ = ‘ilk yedi harfi deneme_ sonrası önemli değil.’

Ya da,

select BolgeTanimi from Bolge where BolgeTanimi like 'deneme!_%'  escape '!'

Özel ifademizin önüne birnevi belirteç ifadesi koyduk.Bu ifade her türlü karakter olabilir.Ben ‘!’ koydum.Bu belirteçi koyduktan sonra escape ile belirteçimizi gösteriyoruz.

‘deneme!_%’  escape ‘!’ = ‘ilk 6 harf deneme olsun.Escape sayesinde ünlemden sonra gelenide özel karakter sayma.Yani ilk 7 harf deneme_ olsun.Gerisi mühim değil.’

Örnekler

select BolgeTanimi from Bolge where BolgeTanimi like 'deneme[%]%'

veya

select BolgeTanimi from Bolge where BolgeTanimi like 'deneme.%%' escape '.'

GROUP BY – HAVING

Bu başlığı anlatmak için kategorileme mantığından yola cıkalım.Örneğin elimizde üç kategori var.”Kategori1,Kategori2,Kategori3” bunların her birine sırasıyla rakamsal isim veriyorum {1,2,3}.

select Kategoriler,Count(Ornek) from Ornek

Mevzuyu fazla karıştırmadan daha somut örneklere geçmek istiyorum.

select KategoriID,COUNT(UrunAdi) from Urunler group by KategoriID

Bu yapıyı incelerseniz,KategoriId si 1 olan bir grup,2 olan başka bir grup …

NOT :Eğer “group by” ile gruplandırılmış bir kolonum varsa ve aggregate fonksiyonları kullanılıyorsa o kolona “where” değil “having” ile şart yazarız.Ve “having” ifadesi “group by” dan sonra gelmelidir.

Eğer “where” kullanılacaksa “group by” dan önce yazılmalıdır.

select KategoriID,COUNT(UrunAdi) from Urunler where KategoriID>4 group by KategoriID

Bakın yukardaki yapıda neler yaptık. Aggregate fonksiyonumuz var.Gruplandırılmış bir kolonumuz var.Ama neden “having” değilde “where” kullandık diye soracaksınız.Bunun nedeni,yazdığımız şart aggregate fonksiyon kullanılan kolon üzerine olmadığındandır.

Eğer aggregate fonksiyonu kullanan bir kolona şart yazmaya kalkarsak,

select KategoriID,COUNT(UrunAdi) from Urunler where COUNT(UrunAdi)<3 group by KategoriID

BU HATA VERECEKTİR.
DOĞRUSU ŞÖYLEDİR.

select KategoriID,COUNT(UrunAdi) from Urunler group by KategoriID having COUNT(UrunAdi)<3

Tekrar ediyorum,hem gruplama var,hemde Aggregate fonksiyon varsa ve üstelik Aggregate fonksiyona şart yazmamız gerekiyorsa,”where” değil “having” kullanılır.”having” “group by” dan sonra yazılır.

Eğer yukardaki gibi kriterler sınırlayıcı değilse,”where” kullanabilirsiniz ama unutmayın ki “group by” kodundan önce kullanmalısınız.

JOINLER (TABLO BİRLEŞTİRME YAPILARI)
Joinler genel olarak 3 başlık altında anlatılır.
1-Inner Join
2-Outer Join(Left,Right,Full)
3-Cross Join
Şimdi 1. Başlıktan anlatmaya başlayalım..
1-Inner Join
Bu yapı bir veya birden fazla tabloyu,ilişkili kolonlar üzerinden birleştirir ve tek bir tablo haline getirirler.Fakat dikkat etmemiz gereken nokta bu tablolardan sadece eşleşenleri getirirler.

Bu yapı ile maksimum 7 tablo birleştirilebilir.

Şimdi birkaç örnek yapalım.
“Gene Nortwind tablosunda çalışıyoruz.”
Soru’’
Hangi personelim (adı ve soyadı ile birlikte), toplam kaç adetlik satış yapmış.Satış adedi 100 den fazla olanlar gelsin, personelin adının baş harfi M olan kayıtlar gelsin ?

Öncelikle soruyu algoritmaya dökmek için istediği bilgilerin hangi tabloda olduğunu saptamamız gerek.
Personelin adı ve soyadını ancak “Personeller” tablosundan,yaptığı satış adedinede ancak “Satışlar” tablosundan erişebilirim.Bu durumda iki tablo arasında ilişkili kolon bulmalıyım.Bu iki tabloya baktığımızda “PersonelId” kolonları üzerinden ilişkilidir.Bu iki tabloyu birleştirerek tek tablo haline getirip o şekilde sorgu yazmalıyım.

select Personeller.Adi,Personeller.SoyAdi,Count(Satislar.SatisID) from Personeller inner join Satislar on Personeller.PersonelID=Satislar.PersonelID where Personeller.Adi like 'M%' group by Personeller.Adi,Personeller.SoyAdi having Count(Satislar.SatisID) > 100

Soru’’
Hangi ürün hangi kategoride?

select u.UrunAdi,k.KategoriAdi from Urunler as u inner join Kategoriler as k on u.KategoriID = k.KategoriID

Soru’’
Beverages kategorisindeki ürünlerim.

select u.UrunAdi from Urunler as u inner join Kategoriler as k on u.KategoriID=k.KategoriID where k.KategoriAdi = 'Beverages'

Soru’’
Beverages  kategorisindeki ürünlerimin sayısı kaçtır.

select Count(u.UrunID) from Urunler as u inner join Kategoriler as k on u.KategoriID=k.KategoriID where k.KategoriAdi='Beverages'

Soru’’
Seafood kategorisindeki ürünlerin listesi(Urunler,Kategoriler)

select Urunler.UrunAdi from Urunler inner join Kategoriler on Kategoriler.KategoriID = Urunler.KategoriID where Kategoriler.KategoriAdi = 'Seafood'

Soru’’
Seafood kategorisindeki ürünlerin sayısı ?(Urunler,Kategoriler)

select k.KategoriAdi,COUNT(u.UrunAdi) from Urunler as u inner join Kategoriler as k on u.KategoriID=k.KategoriID where k.KategoriAdi = 'Seafood' group by k.KategoriAdi

Soru’’
Hangi satışı hangi çalışanım yapmış ?(Satislar,Personeller)

select p.Adi,s.SatisID from Personeller as p inner join Satislar as s on p.PersonelID = s.PersonelID

Soru’’
Hangi personelim toplam kaç adet satış yapmış ?

select p.Adi,Count(s.SatisID) from Personeller as p inner join Satislar as s on p.PersonelID=s.PersonelID group by p.Adi

Soru’’
En çok satış yapan personelim?

select top 1 p.Adi,Count(s.SatisID) from Personeller as p inner join Satislar as s on p.PersonelID=s.PersonelID group by p.Adi order by Count(s.SatisID) desc

Soru’’
Faks numarası “null” olmayan tedarikcilerden alınmış ürünler(Urunler,Tedarikciler)

select Tedarikciler.Faks from Urunler inner join Tedarikciler on Urunler.TedarikciID=Tedarikciler.TedarikciID where Tedarikciler.Faks is not null

Burada dikkat etmemiz gereken nokta “is not null” komutudur.Bunun anlamı “null” olmayan demektir.

Soru’’
1997 yılından sonra Nancy nin satış yaptığı firmaların isimleri: (1997 dahil)(Musteriler,Satislar,Personeller)

select Musteriler.SirketAdi from Musteriler inner join Satislar on Musteriler.MusteriID = Satislar.MusteriID inner join Personeller on Satislar.PersonelID=Personeller.PersonelID where Personeller.Adi = 'Nancy' and Year(Satislar.SatisTarihi) > 1996

Soru’’
Limited olan tedarikçilerden  alınmış seafood kategorisindeki ürünlerimin toplam satış tutarı.(Urunler ,Kategoriler,Tedarikciler )

select SUM(u.HedefStokDuzeyi*u.BirimFiyati) from Kategoriler as k inner join Urunler as u on k.KategoriID=u.KategoriID inner join Tedarikciler as t on t.TedarikciID = u.TedarikciID where k.KategoriAdi='Seafood' and t.SirketAdi like '%ltd.%'

Soru’’
Adında “a” harfi olan personellerin satış id si 10500 den büyük olan satışlarının toplam tutarını(miktar * birimfiyat) ve bu satışların hangi tarihte gerçekleştiğini listele.

select p.Adi,s.SatisTarihi,SUM(sd.Miktar*sd.BirimFiyati) from Personeller as p inner join Satislar as s on p.PersonelID=s.PersonelID inner join [Satis Detaylari] as sd on s.SatisID=sd.SatisID where s.SatisID>10500 and p.Adi like '%a%' group by p.Adi,s.SatisTarihi

Soru’’
Personellerimin bağlı olarak çalıştığı kişileri listele ?

select p1.Adi,p2.Adi from Personeller as p1 inner join Personeller as p2 on p1.PersonelID = p2.BagliCalistigiKisi

Outer Join(Left,Right,Full)
Inner join sayesinde eşleşen kayıtları getirebiliyorduk.Şimdi ise eşleşmeyen kayıtlarıda getirmek için outer joinleri kullanacağız.

LEFT / LEFT OUTER JOIN : join ifadesinin solundaki tablodan tüm kayıtları getirir.Sağındaki tablodan eşleşenleri yan yana eşleşmeyenleri null olarak getirir. Sql Server 2000 ve öncesi için left outer join yazılmalı. Sonrası için left join yeterlidir.

select * from Filmler left  join Oyuncular on Filmler.FilmId = Oyuncular.FilmId

RIGHT JOIN : joinin sağındaki tablonun tamamını getirecek, solundakinden eşleşenleri aynı satırsda eşleşmeyenleri de null olarak getirecek.

select * from Filmler right join Oyuncular on Filmler.FilmId = Oyuncular.FilmId

FULL JOIN : joinin 2 tarafındaki tablolardan eşleşen eşleşmeyen hepsini getirir.

select * from Oyuncular full join Filmler on Filmler.FilmId = Oyuncular.FilmId

CROSS JOIN : Matematikteki kartezyen çarpım işlemini yapar. Bir tablodaki tüm kayıtları diğer tablodaki tüm kayıtlarla teker teker eşleştirir.

select * from Personeller cross join Bolge

INSERT (KAYIT EKLEME)
T-SQL sayesinde veritabanına veri ekleyebiliriz.Bunuda “insert” komutuyla gerçekleştirebiliriz.

Genel anlamda şu yapıdadır.

Insert into TabloAdı values(eklenecek veriler)

insert into Personeller(Adi,SoyAdi) values('Gençay','Yıldız')

Yukardaki yapıda “Personeller” tablosundaki “Adi” ve “Soyadi” kolonlarına sırasıyla “Gençay” ve “Yıldız” verileri eklenmiştir.Diğer kolonlar “null” olacaktır.

Dikkat etmeniz gereken nokta , otomatik artan identity kolona değer gönderilemez.
Eğer bir tablodaki tüm kolonlara değer gönderilecekse, () içine kolon isimlerinizi yazmak zorunda değiliz.

insert into Oyuncular values('Gencay','Yildiz')

Eğer “Oyuncular” tablosundaki bütün kolonlara değer göndereceğimi söylediğim halde göndermezsem hata verecektir.

insert into Oyuncular values('Gencay')

Hata verecektir.

Bir kolona değer gönderirken o kolonla uyumlu tipte değer gönderilmelidir.

UPDATE (GÜNCELLEME)

Veritabanındaki bilgileri güncellememiz gerektiği zaman Update komutunu kullanırız.
Genel yapısı şu şekildedir.

Update Tabloİsmi set Veri where şart

update Personeller set Adi='g' where Adi = 'Gençay'

Yukarda gördüğünüz gibi “Personeller” tablosunda adı “Gençay” olan verilerin adını “g” harfine çevir diyorum.

Eğer güncellenecek olan kaydı şart olarak belirtmezsek,tablomuzdaki bütün kayıtlar değiştirilecektir.

Yani…

update Filmler set FilmAdi = 'Arog'

Burada “Filmler” tablosundaki “FilmAdi” kolonundaki tüm veriler “Arog” olacaktır.Şart olmadığı için hepsi değişecektir.

DELETE (KAYIT SİLME)

Veritabanından kayıt silmek için Delete yapısını kullanırız.

Genel yapısı şu şekildedir.

Delete from TABLOADİ where şart

delete from Oyuncular

Eğer bu yapıdada şartı yazmazsak veritabanının içine limon sıkmış oluruz.Bu şekilde “Oyuncular” tablosundaki bütün veriler silinecektir.

delete from Oyuncular where OyuncuId > 10

“Oyuncular” tablosunda “OyuncuId” si 10’dan büyük olan kayıtları sil.

NOT : Select, Update,Delete, Insert komutlarına genel olarak DML (Data Manipulation Language)  ismi verilir.