1-) TSQL - Komutlar

 

• Like Komutu

 

% işareti ile kullanılır. % işareti genel önemli değil anlamına gelir.

 

--Baş harfi j geri kalan harfleri önemli olmayan personellerin adı soyadı

Select Adi,Soyadi from Personeller Where Adi Like 'j%'

 

--Adının ilk harfi r son harfi t olan personelleri getirir

select * from personeller where Adi Like 'r%t'

 

--Adın da an geçen personelleri listele

select * from personeller where Adi Like '%an%'

 

_ özel önemli değil operatörü

 

--isminin ilk harfi a, ikinci harfi fark etmez ve üçüncü harfi d olan personelleri listele

select * from personeller where Adi like 'a_d%'

 

[] ya da operatörü

 

--isminin ilk harfi n ya da m ya da r olan personelleri listele

select * from personeller where Adi like '[nmr]%'

 

--isminin içerisinde a ya da i geçen personeller

select * from personeller where Adi like '%[ai]%'

 

• Aggregate fonksiyonları

 

AVG fonksiyonu ortalama alır.

 

--sayısal değerlerde çalışır

select avg(personelID) from personeller

 

max fonksiyonu en büyük değeri getirir

 

select max(personelID) from personeller

 

min fonksiyonu en küçük değeri getirir

 

select min(personelID) from personeller

 

count toplam sayıyı verir

 

select count(*) from personeller

 

SUM verilen kolonda toplama işlemi yapar

 

select sum(NakliyeUcreti) from Satislar

 

• Tarih fonksiyonları

 

-- bu günün tarihini verir

select GETDATE()

 

--verilen tarihe verildiği kadar gün ay yıl ekler

select DATEADD(DAY,999,GETDATE()) --999 gün ekle

select DATEADD(MONTH,999,GETDATE()) --999 ay ekle

select DATEADD(YEAR,999,GETDATE()) --999 yıl ekle

 

--iki tarih arasında günü, ayı ve yılı hesaplar

select DATEDIFF(DAY,'05.09.1992',GETDATE())

select DATEDIFF(MONTH,'05.09.1992',GETDATE())

select DATEDIFF(YEAR,'05.09.1992',GETDATE())

 

• Group By

 

--hangi kategoride kaç tane ürün olduğunun listeleme

select KategoriID,Count(*) from urunler Group by KategoriID

--hangi personel kaç satış yapmış

select PersonelID, COUNT(*) from satıslar Group by PersonelID

 

--hangi personel toplam ne kadar nakliye ücreti ödemiş

select PersonelID, SUM(NakliyeUcreti) from satıslar Group by PersonelID

 

• Having

 

--kategori id si 5 den büyük olup 6 dan fazla olan ürünleri listele

--where group by dan önce having sonra kullanılır

select KategoriID,Count(*) from urunler where KategoriID>5 Group by KategoriID having COUNT(*)>6

 

• inner join

 

--genel mantık birleştirilmek istenilen tablolar seçilerek ilişkili kolanları on parametresiyle ilişkilendirmek

 

--hangi personel hangi satışları yapmıştır

select * from personeller p inner join satislar s on p.personelID = s.personelID

 

--hangi ürün hangi kategoride bulunmaktadır

select u.urunAdi, k.KategoriAdi from Urunler u inner join Kategoriler k o u.KategoriID=k.KategoriID

 

--beverages kategorisindeki ürünler nelerdir

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

 

--beverages kategorisindeki toplam kaç ürün var

select COUNT(u.urunAdi) from Kategoriler k inner join Urunler u on k.KategoriID=u.KategoriID where k.KategoriAdi = 'Beverages'

 

--hangi satışı hangi personel yapmış

select s.SatisID,p.Adi + ' ' + p.SoyAdi from Satislar s inner join Personeller p on s.PersonelID = p.PersonelID

 

 

--faks numarası null olmayan tedarikçilerden alınmış ürünler neler

select u.UrunAdi from Urunler u inner join Tedarikciler t on u.TedarikciID=t.TedarikciID where t.Faks<>'Null'

 

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

 

-- 1997 yılından sonra Nancy nin satış yaptığı firmaların isimleri 1997 dahil

select p.Adi,m.MusteriAdi,s.SatisTarihi from Personeller p inner join Satislar s on p.PersonelID = s.PersonelID inner join Musteriler m on s.MusteriID = m.MusteriID where p.Adi='Nancy' and YEAR(s.SatisTarihi) >=1997

 

--limited olan tedarikçilerden alınmış seafood kategorisindeli ürünleirin toplam satış fiyatı

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

 

• inner join + group by

 

--hangi personel toplam kaç adetlik satış yapmış satış adedi 100 den fazla olanlar ve personelin adının baş harfi M olan kayıtlar gelsin

 

select p.Adi+' '+ p.SoyAdi, COUNT(s.SatisID) from Personeller p inner join Satislar s on p.PersonelID=s.PersonelID where p.Adi like 'm%' group by p.Adi+' '+p.SoyAdi having COUNT(s.SatisID)>100

 

-- hangi personel kaç adet satış yapmış

select p.Adi, COUNT(*) from Personeller p inner join Satislar s on p.PersonelID=s.PersonelID Group by p.Adi

 

-- adında a harfi olan personellerin satış id si 10500 den büyük olan satışlarının toplam tutarını (miktar*birim fiyat) ve bu satışların hangi tarihte gerçekleştiğini listele

 

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

 

 

 

• insert komutu

 

-- insert komutu ile select sorgusu sonucu gelen verileri farklı tabloya kaydetme

insert OrnekPersoneller Select Adi,Soyadi from Personeller

 

--select sorgusu sonucu gelen verileri farklı bir tablo oluşturarak kaydetme

select PersonelID, Adi,Soyadi,Ulke into OrnekPersoneller from Personeller

 

• Update

 

update OrnekPersoneller set Adi='Mehmet' where Adi='Nancy'

 

-- update sorgusunda subquery ile güncelleme yapmak

update Urunler set UrunAdi=(Select Adi from Personeller where PersonelID=3)

 

• Delete

 

--kategori id si 3 den küçük olanları sil

delete from Urunler where KategoriID<3

 

• Rollup

 

-- bir satışda satılan ürünlerin ara toplamını verir select SatisID,UrunID,SUM(Miktar) from [Satis Detaylari] group by SatisID,UrunID with rollup     --having ile kullanımı select SatisID,UrunID,SUM(Miktar) from [Satis Detaylari] group by SatisID,UrunID with rollup having SUM(Miktar) >100

 

• With cube

--bir ürünün hangi satışta ne kadar satıldığını ve toplamını verir select SatisID,UrunID,SUM(Miktar) from [Satis Detaylari] group by SatisID,UrunID with cube       --having ile kullanımı select SatisID,UrunID,SUM(Miktar) from [Satis Detaylari] group by UrunID,SatisID with cube having Sum(miktar)>100

• Constraint

 

--default Consraint: kolona ekleme yapılırken eğer değer atanmaz ise default olarak girilen değer kolona atılmaktadır   alter table OrnekTablo add constraint KolonConstraint default 'Boş' for kolon1   alter table OrnekTablo add constraint Kolon2Constarint default -1 for Kolon2   --insert işlemi insert OrnekTablo(kolon2) values(0) insert OrnekTablo(kolon1) values('ornek bir deger')

 

--check Consraint: bir kolona girilecek olan verinin belirli bir şarta uymasını zorunlu tutar

 

alter table OrnekTablo add constraint Kolon2Kontrol check ((kolon2*5)%2=0)

 

--with Nocheck komutu: check constraint de tablodaki daha önceki değerleri görmezden gelmesini sağlar

 

alter table OrnekTablo with nocheck add constraint Kolon2Kontrol2 check ((kolon2*5)%2=0)

 

--belirlenen kolonda tekrar eden kayıt olmasını engeller

alter table OrnekTablo add Constraint OrnekTabloUnique Unique (kolon2)

 

• Trigger

 

       Burdaki triggerlar bi işlem gerçekleşmesinin ardından gerçekleşen işlemlerdir.

 

 --personel tablosundan bir verinin silinmesinin ardından hangi verinin kim tarafından hangi tarihte silindiğini belirlenen tabloya kaydeden trigger

 

 --deleted tablosu bir veri silinmeden önce ram de oluşturulan tablodur silinme işlemi öncelikle bu tablo üzerinden gerçekleşir başarılı olursa ana tablodan silinme işlemi yapılır

 

create trigger triggerPersoneller

on Personeller

after delete

as

declare @Adi nvarchar(max), @Soyadi nvarchar(max)

select @Adi = Adi,@Soyadi=Soyadi from deleted

Insert LogTablosu values ('Adı ve Soyadı ' + @Adi + ' ' + @Soyadi + ' olan personel ' + suser_name() + ' tarafından ' + Cast(getdate() as nvarchar(max)) + ' tarihinde silinmiştir.')

 

 

--Personeller tablosunda update gerçekleştiği anda devreye giren ve bir log tablosuna adı... olan personel ... yeni adıyla değiştirilerek ... kullanıcı tarafından ... tarihinde güncellendi şeklinde rapor yazan trigger

 

create trigger triggerPersonellerUpdate

on Personeller

after update

as

declare @EskiAdi nvarchar(max), @YeniAdi nvarchar(max)

select @EskiAdi = Adi from deleted

select @YeniAdi = Adi from inserted

Insert LogTablosu values ('Adı ' + @EskiAdi + ' olan personel ' + @YeniAdi + ' yeni adıyla değiştirilerek ' + suser_name() + ' tarafından ' + Cast(getdate() as nvarchar(max)) + ' tarihinde güncellenmiştir.')

 

 

Burdaki trigger yapılmak istenen işlem yerine kendi yazdığımız kodların çalışmasına olanak sağlar.

 

--personeller tablosunda update gerçekleştiği anda yapılacak güncelleştirme yerine bir log tablosuna "Adı ... olan personel ... yeni adıyla değiştirilerek ... kullanıcı tarafından ... tarihinde güncellenmek istendi."

 

create trigger trgPersonellerInstead

on Personeller

Instead of update

as

declare @EskiAdi nvarchar(max), @YeniAdi nvarchar(max)

select @EskiAdi = Adi from deleted

select @YeniAdi = Adi from inserted

insert LogTablosu values('Adı ' + @EskiAdi + ' olan personel ' + @YeniAdi + ' yeni adıyla değiştirilerek ' + SUSER_NAME() + ' kullanıcısı tarafından ' + CAST(GETDATE() as nvarchar(max)) + ' tarhinde güncellenmek istendi.')

 

-- personeller tablosunda adı "Andrew" olan kaydın silinmesini engelleyen ama diğerlerine izin veren triggerı yazalım.

 

create trigger andrewTrigger

on Personeller

after delete

as

declare @Adi nvarchar(max)

select @Adi = Adi from deleted

if @Adi='Andrew'

begin

print 'Bu kayıtı silemezsiniz'

rollback -- yapılan tüm işlemleri geri alır.

end

 

--triggerı devre dışı bırakma

 

disable trigger OrnekTrigger on Personeller

 

--triggerı tekrar aktifleştirme

 

Enable Trigger OrnekTrigger on Personeller

 

• Row Number

--ilgili tabloda adına göre indexleme işleme yapan komut primary kolonundan bağımsız bir şekilde çalışır   select ROW_NUMBER() over(order by Adi) indexer, * from Personeller

 

--partition by komutu diğer verilen parametreye göre  gruplama yaparak MusteriID si aynı olanlar içerisinde özel bir indeksleme gerçekleştiriyor   select ROW_NUMBER() over(partition by MusteriID order by OdemeTarihi) indexer, * from Satislar

 

 

• Temporal Tables(System-Versioned)

 

-- *** Temporal Tables (System Versioned) ***

--Temporal tables ile raporlama ve takip mekanizması oluşturacağımız tablolarda primary key tanımlanmış bir kolon olmalı

--takibi sağlayacağımız ve kaydını tutacağımız tablonun içerisinde bir başlangıç(StartDate) bir de bitiş(EndDate) niteliğinde iki adet "datetime2" tipinde kolonların bulunması gerekmektedir.

--eğer bir tabloda temporal tables aktifse o tabloda truncate işlemi gerçekleştirilemez

 

--Temporal table oluşturma

create table DersKayitlari

(

DersID int primary key identity(1,1),

Ders nvarchar(max),

Onay bit,

StartDate datetime2 generated always as row start not null,

EndDate datetime2 generated always as row end not null,

period for system_time(StartDate, EndDate)

)

 

with (system_versioning = on (history_table = dbo.DersKayitlariLog))