67. SQL REPLACE Fonksiyonu Kullanımı

• REPLACE fonksiyonu ile istediğimiz bir metin içerisinde istediğimiz bir metini başka bir metinle ile değiştirebiliriz.
• Kendi tablomuzda bulunan veriler üzerinde de bu işlemi uygulayabilir sonuçları istediğimiz kolonda gösterebiliriz.
• Aşağıdaki örnekleri inceleyebiliriz.

Söz Dizimi
REPLACE (Ana_Metin, Ana_Metin_İçinde_Aranan_Değer,Değiştirilecek_Değer)

66. SQL PATINDEX Kullanımı

• SQL PATINDEX Fonksiyonu belirttiğimiz bir metinde yada tablomuzun herhangi bir kolonunda seçtiğimiz karakterin "değerin" başlangıç pozisyonunu döner.
• Eğer aradğımız karakter metnin içinde yok ise 0 döner. 
• Aşağıdaki örnekleri inceleyip sizlerde uygulayabilirsiniz.

Söz Dizimi:

PATINDEX( '%Aradığımız kalıp%' , İçinde_Arayacağımız_İfade) 

  
Not: Aynı şekilde Tablodaki alanların içinde de bu şekilde arama yapabiliriz.

65. SQL CONCAT Kullanımı

• SQL CONCAT fonksiyonu kısaca alanları birleştirmek için kullanılır.
• SELECT cümleciklerimizde direkt metinleri birleştirebileceğimiz gibi tablomuzdaki farklı alanları da istediğimiz sıra veya ek metinlerle birleştirip yeni bir kolonda gösterebiliriz.
• Aşağıdaki örneklerle pekiştirelim...

Not:Aynı şekilde tablomuzda alanları da birleştirebiliriz.

SQL Server'da Fonksiyon Oluşturma ve Kullanma

• Fonksiyonlar istenilen veri tiplerini alıp istenilen veri tiplerini veya bir tablo bile geri döndürebilir.

• Birden çok parametre vereceğimiz zaman bunları virgül ile ayırmamız gerekmektedir. 

Function Create(Fonksiyon  Oluşturma) 

• Örneğimizde Animals tablomuzdaki Kusların ortalama ağırlıklarını döndüren bir fonksiyon yazalım.

ÖRNEK:

CREATE FUNCTION fnc_OrtalamaAgirlik
(
@KusNo int
)
RETURNS int
AS
BEGIN
  DECLARE @Result int
  SET @Result = (SELECT AVG(Weight) FROM Animals.dbo.Kuslar WHERE KusNo <=@KusNo)
RETURN(@Result)
END

Function Kullanma(Fonksiyon  Kullanma)  

• İstediğimiz lokasyondan doğru path i vererek fonksiyonumuzu çağırabiliriz.

 ÖRNEK:

DECLARE @KusNo int
SET @KusNo = 5
SELECT dbo.fnc_OrtalamaAgirlik(@KusNo) AS Ortalama

SQL' de VIEW Tanımlama ve Kullanımı

• Öncelikle view lere neden ihtiyaç duyarız onu anlatalım.
• Vıewler kısaca ben bütün tablodaki sütunları değilde sadece şu şu sütunlar listelensin demek için kullanılır.
• Örneğin bir çalışan tablonuz olsun ve bu tabloda personelin adresinden tutun aldığı maaşa kadar bilgiler bulunsun.Bu durumda sadece herkese açık verileri tutan yeni bir tablo create etmek yerine belirli sütunları gösteren bir view create edebiliriz.
• Ayrıca parçalı sorgularımızı da view e bağlayabiliriz. Bu view üzerinden yeni sütun ekleyip silebiliriz. 
• Veri tabanı view da oluşan datayı saklamaz. Dolayısıyla bir viewun geçtiği SQL deyimi her çalıştırıldığında ilgili view sanal kümeyi yeniden oluşturur.

SÖZ DİZİMİ:

CREATE VIEW Kuslar_view
(KusNo,Adı)AS
SELECT KusNo,Adı FROM Kuslar

NOT: Eklediğimiz VIEW ın Yapıdaki yerini ve sorgusunu aşağıdaki resimde görebilirsiniz.

NOT: Database imizde bulunan VIEW ler hakkında ayrıntılı bilgi almak için System sp sini kullabiliriz.

SÖZ DİZİMİ:

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

NOT: Ayrıca view içindeki sorgumuzu öğrenmek istediğimiz zaman da aşağıdaki sorgu cümlemizi kullanabiliriz.

SÖZ DİZİMİ:

SELECT * FROM  INFORMATION_SCHEMA.VIEWS

Vıewlerimizi silmek gayet basit.Drop ederek View imizi veritabanımızdan sileriz.

DROP VIEW [view adı]

NOT: Özetlemek gerekirse view ile kullanıcılara göstermek istediğimiz verileri gösterir istemediklerimiz yokmuş gibi yaparız.

T-SQL'de sp_rename System Prosedürünün Kullanılması

•  SQL de nesnelerimizin adlarını değiştirmek için sp_rename sistem procedürünü kullanılırız.

SÖZ DİZİMİ:

exec sp_rename @object_name, @new_object_name, @object_type

1-) Tablo adının değiştirilmesi 

SELECT object_id('Birds')
exec sp_rename  'Birds','Kuslar'

SELECT object_id('Birds')
SELECT * FROM Birds
Invalid Object Name 'Birds' hatası döndürecektir.

SELECT * FROM Kuslar
Sorgusunu çalıştırarsınız, Birds tablosundan taşıdığımız kolonları göreceksiniz.
İçerisinde data olduğu için kayıtlı verileri görebilirsiniz.

2-) Kolon adının değiştirilmesi : 

• Biraz önce create ettiğimiz ve adını Kuslar olarak değiştirdiğimiz tablonun name kolonunu Adı olarak değiştirelim..
• sp_rename prosedürünün bizim şimdiye kadar kullanmadığımız, default u 'object' olan bir parametresi daha var.  
• Kolonlar, sysobjects de yer almazlar ve birer database objesi değillerdir ve tablo ismi olmadan da birşey ifade etmezler. 
•  x tablonun y kolonu diye saklanırlar ve çağırılırlar.  Bu yüzden ismini değiştirdiğimiz nesnenin kolon tipinde olduğunu ve hangi tabloya ait olduğunu  da söylememiz gerekir.

exec sp_rename 'Kuslar.Name','Adı','COLUMN'

Kuslar tablomuza ait olan name isimli kolonu Adı olarak değiştirdik.

SELECT * FROM Kuslar
sorgusunu çağırarak kolon adının değiştiğini görebiliriz.

3-) Index değerinin değiştirilmesi

• sp_rename ile indexleri de yeniden adlandırabiliriz.

 exec sp_rename 'Kuslar.BirdId', 'KusNo','INDEX'


NOT: sp_rename ile syscomments de bulunan text alanındaki nesne ismini değiştiremeyiz.
Bu yüzden 'function','stored procedure','trigger' ve 'view' gibi programlanabilir nesneler için sp_rename i kullanmamalıyız.
Bu tür nesneler için sp_rename yerine önce bu nesleri drop(silme) edip sonra istediğimiz yeni isim ile yeniden create(oluşturma) edebiliriz.

NOT: Oluşturulan her bir nesne nin bir id si vardır ve sql serverda nesneler id leri ile saklanırlar.
object_id system function ını kullanarak objenin id sini öğrenebilir ve varolup olmadığından da böylecek emin olabiliriz.
object_id (objectname) null döndürüyorsa o isimde nesne ilgili database de yok demektir.

57. CONTINUE Komutunun kullanımı

• Program çalıştığı süreç içinde bazı durumlarda WHILE döngüsü içindeyken END e kadar gidip başa dönmesinden ziyade istediğimiz şartın içinde o an ki döngüsünü bitirmeden başa gönderir ve bir sonraki değer için bloğa girmesini sağlarız.
• Aşağıdaki örneğimizde sadece karesi çift sayı olan değerlerini yazılması sağlayabiliriz.

ÖRNEK:
DECLARE @Sayi INT
SET @Sayi=1
WHILE (@Sayi<15)
BEGIN
    IF((@Sayi*@Sayi)%2=1)
    BEGIN
        SELECT @Sayi=@Sayi+1
        CONTINUE
    END
    BEGIN
        PRINT CAST(@Sayi AS VARCHAR(5))+' sayısını karesi='+ CAST(@Sayi*@Sayi AS VARCHAR(5))
    END
    SELECT @Sayi=@Sayi+1
END

Ders 58'e Geçebilirsiniz.

53. IF..ELSE Yapısının Kullanılması

• Diğer programlama dillerinde olduğu gibi SQLde de bloklar kullanılır.
• Eğer işlenecek komut satırları birden fazla ise mutlaka BEGIN..END yapısı kullanılmalıdır.
• IF..ELSE blokları komutların belirli bir koşula bağlı olarak çalışmasını sağlar.

SÖZ DİZİMİ:
IF Koşul
{
   Deyim
}
ELSE
{
   Deyim
}
ÖRNEK:
//ortalama fiyatın düşük veya yüksek olup olmadığını buluyoruz.

DECLARE @ortalamafiyat DECIMAL(10,2)
SELECT @ortalamafiyat=AVG(UnitPrice) FROM Products
PRINT 'Ortalama Birim Fiyatı='+CAST(@ortalamafiyat AS VARCHAR(5))
IF(@ortalamafiyat<30)
BEGIN
    PRINT 'Düşük Fiyat'
END
ELSE
BEGIN
    PRINT 'Yüksek Fiyat'
END

Ders 54'e geçebilirsiniz.

52. SQL KARAKTER FONKSİYONLARI

• Karakter değerleri üzerinde işlem yapmak için karakter fonksiyonları kullanılır.

1-) CHAR() Fonksiyonu
-ASCII kodu verilen karakteri gösterir.

SELECT CHAR(69)  // E harfi

SELECT ASCII('B') // 66

2-) CHARINDEX() Fonksiyonu
-Bir metin içerisinde istedilen metin parçasını istediğimiz konumdan itibaren arar.

SELECT CHARINDEX('Aranacak metin','İçinde arancak metin',0)

SELECT CHARINDEX('tr','sqltr.blogspot.com',0) // Sonuç 4 döner.

3-) LEFT() Fonksiyonu
-Metinde sol taraftan istediğimiz uzunluktaki metni alır.

SELECT LEFT('sqltr.blogspot.com',5) // Sonuç 'sqltr' döner.

4-) LEN() Fonksiyonu
-Metnin uzunluğunu verir.

SELECT LEN('sqltr.blogspot.com') // Sonuç 18

5-) LOWER() Fonksiyonu
-Metnin tamamını küçük harfe çevirir.

SELECT LOWER('SQLtr.BLOGspot.com') // Sonuç 'sqltr.blogspot.com'

6-) LTRIM() Fonksiyonu
-Metnin başında bulunan boşluk karakterlerini siler.

SELECT LTRIM('  sqltr.blogspot.com')  // Sonuç 'sqltr.blogspot.com'

7-) RIGHT() Fonksiyonu
-Metinde sağ taraftan istediğimiz uzunluktaki metni alır.

SELECT RIGHT('sqltr.blogspot.com',3) // Sonuç 'com' döner.

8-) RTRIM() Fonksiyonu
-Metnin sonunda bulunan boşluk karakterlerini siler.

SELECT RTRIM('sqltr.blogspot.com  ')  // Sonuç 'sqltr.blogspot.com'

picture8

9-) REVERSE() Fonksiyonu
-Metni ters çevirir.

SELECT REVERSE('sqltr.blogspot.com') // Sonuç 'moc.topsgolb.rtlqs'

10-) SUBSTRING() Fonksiyonu
-Belirli bir metinde belirli bir karakterden itibaren belirli sayıda karakteri almak için kullanılır.

SELECT SUBSTRING('sqltr.blogspot.com',1,5) // 'sqltr' döner.

Not: Dikkatli olmanız gereken String ifadelerde ilk karakter 1. karakterdir. 0.karakter değildir.

11-) UPPER() Fonksiyonu
-Metnin tamamını büyük harfe çevirir.

SELECT UPPER('SQLtr.BLOGspot.com') // Sonuç 'SQLTR.BLOGSPOT.COM'

12-) STUFF() Fonksiyonu

-Metnin içinde belirttiğimiz uzunluktaki karakteri siler ve onun yerine belirlediğimiz uzunlukta karakter ekler.

- STUFF ( metin, başlangıç karakteri, kaç karakter, yeni eklenecek metin)

SELECT STUFF('sqltr.blogspot.com',1,5,'aileye')

13-) CONCAT() Fonksiyonu 
Ders- 65

14-) PATINDEX() Fonksiyonu
Ders- 66

15-) REPLACE() Fonksiyonu 
Ders- 67




Ders 53'e geçebilirsiniz.

SQL Server'da Stored Procedure

• SQL ifadeleri SQL Serverda Stored Procedure olarak saklanmaktadır. 
• Basit örneklemek gerekirse " SELECT * FROM Birds " ifadesini saklayabiliriz. Ama büyük yapılarda kullanılan Stored Procedureler daha komplekstir. 
• Stored Procedure'ler birden fazla tablonun farklı JOIN işlemleri ile bağlanmasıyla ve parametreli veya parametresiz değerlerin WHERE koşullarıyla kısıtlanmasıyla istenilen sonuça hızlı ve sade bir şekilde ulaşmamızı sağlar.

ÖRNEK:
CREATE PROCEDURE Person_Selection
@yas INT

AS

IF(Yas> 19)
SELECT * FROM Person WHERE Yas > @yas ORDER BY Yas
ELSE
SELECT * FROM Person WHERE Yas> @yas and Eriskin=1 ORDER BY Yas

GO

45. DELETE Kullanımı

• DELETE ifadesi tablomuzdaki bir veya birden fazla kayıdın silinmesini sağlar. 
• Eğer koşul belirtmessek bütün kayıtları tablomuzdan siler. O yüzden DELETE ifadesini kullanırken dikkat etmeli ve BEGIN TRANSACTİON ..yapısını kullanmamız önem arz etmektedir.

SÖZ DİZİMİ: DELETE FROM Table
                       WHERE <Sütun_Adı>=<Koşul_Değeri>

ÖRNEK: Sorgumuzda Employees tablumuzdaki bir kayıdı silmek isteyelim.

SORGU: SELECT * FROM Employees
                DELETE FROM Employees WHERE EmployeeID=10
                SELECT * FROM Employees

Ders 46'ya Geçebilirsiniz.

44. UPDATE Kullanımı

• UPDATE deyimi tablomuzda bulunan verileri güncellemek, değiştirmek için kullanılır.

SÖZ DİZİMİ:
         UPDATE <tablo adı>
         SET <sütun adı>= <yeni değer>
         WHERE <sütun adı> = <eski değer> 

ÖRNEK: Sorgumuzda Çalışan tablomuzda bir kaydımızı güncellemek isteyelim. 'Margaret' isimli çalışanın terfi aldığını düşünürsek gerekli güncellemeyi yapalım. 2 tane aynı SELECT sorgusunu farklı görmek için koydum.

SORGU:SELECT * FROM Employees

   UPDATE Employees SET Title='Sales Manager' WHERE EmployeeID=4
   SELECT * FROM Employees

Ders 45' e Geçebilirsiniz.

42. INSERT...SELECT Kullanımı

• Bir sorgunun sonuç kümesini yeni bir tabloya kayıt olarak atmak istersek INSERT... SELECT yapısını kullanırız. 
• Bu durumda dikkat etmemiz gereken 2 durum vardır:

1. Değer girdiğimiz alanın default(varsayılan) bir  değerinin olup olmadığına,
2. Tablonun alanlarının boş(NULL) değere izin verip vermediğine dikkat etmeliyiz. Eğer NULL değere izin vermiyorsa gerekli veri tipinde mutlaka değer göndermeliyiz.

ÖRNEK: Sorgumuzda çalışanlarında birer müşteri olduğu göz önünde bulundurularak müşteri tablosuna kaydedilmesi istenmektedir.

SORGU: INSERT customers
SELECT SUBSTRING(firstname,1,2)+SUBSTRING(lastname,1,3)
,lastname, firstname, title, address, city
,region, postalcode, country, homephone, NULL
FROM employees

Ders 43'e geçebilirsiniz.

41.SQL INSERT.. VALUES Kullanımı

• INSERT ifadesini VALUES ile birlikte kullanarak istediğimiz tabloya yeni kayıt atabiliriz. 
• Dikkat etmemiz gereken eklediğimiz verilerin eklediğimiz tablodaki sütün tanımlamalarına uyumlu olması yani VARCHAR tipinideki bir sütuna INTEGER değeri yazmamalıyız.

ÖRNEK: Sorgumuzda Nakliyatçı firma listemize yeni bir firma ekleyelim. ShipperID değerini girmedik. Çünkü ShipperID sütunu Primary Key olarak tanımlanmış. Yani default olarak 1 arttırım ile otomatik kayıt atmaktadır.

SORGU:SELECT * FROM Shippers
INSERT Shippers(CompanyName,Phone)
VALUES('IstanbulKargo','(0232) 456-89-89')
SELECT * FROM Shippers

Ders 42'ye geçebilirsiniz.

40.SUBQUERİES- EXISTS ve NOT EXISTS Kullanımı

• Bazı durumlarda sorgu sonucu dönen listeyle ilgili değilde herhangi bir değer dönüp dönmediyle ilgileniriz. 
• İşte böyle durumlarda EXISTS ve NOT EXISTS kullanırız. 
• EXISTS ve NOT EXISTS kullanımı TRUE veya FALSE değerleri döndürür.

Örnek: Birinci sorguda ‘3/05/1997’ ve '4/05/1997' tarihleri arasında sipariş almış olan çalışanlar EXIST işlevi yardımıyla tespit edilip, bilgileri listelenmiştir. Aynı sonuca tabloları JOIN ile birleştirerek te ulaşabiliriz. İkinci sorgudaki DISTINCT ifadesi her çalışanın tekbir kez görüntülenmesini sağlar.

Sorgu-1:
SELECT EmployeeId,LastName,FirstName
FROM Employees AS e
WHERE EXISTS
(SELECT * FROM Orders AS o
     WHERE e.EmployeeId=o.EmployeeId
    AND o.OrderDate BETWEEN '3/5/1997' AND '4/5/1997') 

Sorgu-1:
SELECT DISTINCT FirstName,LastName, e.Employeeid
FROM Orders AS o
INNER JOIN Employees AS e
ON o.Employeeid =e.Employeeid
WHERE o.OrderDate BETWEEN '3/5/1997' AND '4/5/1997'

Ders 41' e geçebilirsiniz.

39. SUBQUERIES-HAVING Dönüşümü

• İlişkili alt sorgularla elde ettiğimiz sonuçları aynı zamanda HAVING işlemi ile de elde edebiliriz. Bu bize kullanım çeşitliliği sağlar.

Örnek: İnceleyeceğimiz iki örnekte aynı kategorideki ürünlerden birim fiyatı ortalama birim fiyatından yüksek olan ürünleri kategoriye göre listelenmesini istiyoruz. İlk örneğimizde herhangi bir dönüşüm yapmadan gerekli koşullarımızı sağlıyoruz.

Sorgu:
SELECT ProductName,CategoryID,UnitPrice FROM Products AS p1
    WHERE p1.UnitPrice>(SELECT AVG(UnitPrice) FROM Products AS p2 WHERE p1.CategoryId=p2.CAtegoryId)
    ORDER BY CategoryId

Örnek: Bu örneğimizde INNER JOIN ve HAVING kullanarak aynı sonucu listeliyoruz.

Sorgu:
SELECT p1.ProductName,p1.CategoryID,p1.UnitPrice FROM Products AS p1
    INNER JOIN Products AS p2 WITH (NOLOCK) ON p1.CategoryID=p2.CategoryID
    GROUP BY p1.ProductName,p1.CategoryID,p1.UnitPrice
    HAVING p1.UnitPrice>AVG(p2.UnitPrice)
    ORDER BY p1.CategoryId

Ders 40 'a geçebilirsiniz.

37. SUBQUERIES-İlişkili Alt Sorgular

• Aslında iç sorgular ve dış sorgular belirli bir alan yani sütun üzerinden ilişkili ve birbirlerine bağlıdır. 
• Bunu genelde WHERE koşulu üzerinden sağlarız.

Örnek: Aşağıdaki örneğimizde her bir ürün için verilmiş en yüksek sipariş miktarını listelemekteyiz.

Sorgu: 
SELECT DISTINCT ProductId,Quantity
FROM [order details] AS ord1
WHERE Quantity=( SELECT MAX(Quantity)FROM [order details] AS ord2
                  WHERE ord1.productid=ord2.productid )
ORDER BY ProductId

Ders 38'e geçebilirsiniz.

36. SUBQUERIES-Sonuç Listesinin Deyim Olarak Kullanılması

• Sorgularımızda başka bir sorguyu deyim yani yeni bir parametre olarak kullanabiliriz. 
• Bu amaç için kullanacağımız sorgu ya tek satır sonuç döndürmeli yada tek bir sütun değer listesi döndürmeli.
• Bir değerler listesi geri döndüren sorgular IN anahtar kelimesi içeren WHERE yantümcesi yerine kullanılabilir. 

Örnek: Sorgumuzda ortalamayı bulup satılan ürünlerimiz ayrıntılı birim fiyatlarını ve bu birim fiyatlarının genel ürün potansiyelinde ortalama ürün fiyatına oranla durumunu göstermekte. Burda alt sorguları kullanarak daha anlaşılabilir sorgu yazabiliriz.

Sorgu: 

SELECT ProductName,UnitPrice,UnitsinStock,
(SELECT AVG(UnitPrice) FROM Products) AS Average,
UnitPrice-(SELECT AVG(UnitPrice) FROM Products) AS Difference
FROM Products 

 

Ders 37' geçebilirsiniz.

35. SUBQUERIES-Sonuç Listesinin Tablo Olarak Kullanılması

• Sorgularımızda FROM dan sonra tanımladığımız tablo bağlanıtısı yerine tablo değilse sorgu yazabiliriz. 
• Sonucunda da ilerde bahsedeceğim TEMP TABLE kullanmış oluruz. 
• Dönen sonuç listesi temp tablo gibi kullanılıp AS ile ad verilip sütunlarını istediğimiz gibi kullanabiliriz.

Örnek: Sorgumuzda Çalışan tablomuzdan bize lazım olanları alıyoruz. Daha anlaşılabilir hale getirip temp tablomuzu kullanarak yeni bir sorguya bağlıyoruz.

Sorgu: 
SELECT Calisan.Kimlik, Calisan.Isim, Calisan.Soyad FROM
(SELECT EmployeeID AS Kimlik,LastName AS Soyad,FirstName AS Isim FROM Employees)
 AS Calisan

Ders 36'ya geçebilirsiniz.

34. SUBQUERIES (ALT SORGULAR)

• Kısaca Alt Sorgu bir sorgu içine gömülmüş SELECT sorgularıdır. 
• Çoğu durumlarda  Alt Sorguyu JOIN ifadelerine dönüştürebiliriz. 
• Genelde Büyük sorgulardaki karmaşıklığı azaltmak, anlaşılabilirliği arttırmak için kullanılırlar. 
• Mantık olarak burda yaptığımız bir sorgu sonucu gelen sonuç listesine dayalı yeni sorgular çalıştırmak için kullanırız. 
• Bilmeniz gereken Alt Sorgularda JOIN işlemi daha hızlı işlemektedir. 
• Ayrıca daha karmaşık SP'ler yazmaya başladığınızda bunu daha iyi anlayacaksınız. 
• Scriptlerin anlaşılabilirliği, yazılımcıdan bağımısızlığı ne kadar iyi sağlanırsa yazdığınız sorgular o kadar uzun ömürlü ve kullanılabilir olur. 
• Kural olarak ise Alt Sorguları parantez içinde yazmalıyız.

İlgili Konular;

35. SUBQUERIES-Sonuç Listesinin Tablo Olarak Kullanılması 
36. SUBQUERIES-Sonuç Listesinin Deyim Olarak Kullanılması 
37. SUBQUERIES-İlişkili Alt Sorgular 
38. ALT SORGULAR - JOIN Dönüşümü
39. SUBQUERIES-HAVING Dönüşümü 
40.SUBQUERİES- EXISTS ve NOT EXISTS Kullanımı