/*TEMEL KAVRAMLAR Veritabanı nedir? Bilgisayarda tutulan, üzerinde işlem yapılmasına uygun düzenli bir yapıda tutulan, birbiriyle ilişkili veri topluluğuna veritabanı denir. Kurumlar işlerini yapabilmek için verilerini uygun bir şekilde organize etmelidirler. Bunun için veri yapısı modellenir ve buna uygun veritabanı yaratılır. DBMS (Database Management System - Veritabanı Yönetim Sistemi-VTYS) nedir? VTYS ler verileri oluşturmak, üzerlerinde değişiklik yapmak, silmek, veriler üzerinde sorgulamalar yapmak, veri bütünlüğü ve güvenliğini sağlamak, yedeğini almak, yedeği geri yüklemek vb. görevleri yerine getirmek için gerçekleştirilmiş yazılımlardır. Veriler, VTYS 'lerde tutulur ve yönetilir. VTYS TARİHÇE Veriler önceleri düz metin dosyalarında tutuluyorlardı. Veri miktarı küçük olduğu için çalışma açısından(çok uygun olmasa da) amaca hizmet ediyordu. Veriler genel olarak birtakım özel karakterlerle ayrılmış alanlar ve satırlarda barındırılıyordu. Alanları ayırmak için virgül, noktalı virgül veya Tab gibi karakterler; satırları ayırmak içinse “CR”+”LF” vb kullanılıyordu. VTYS ‘ler ilk olarak 1970 li yıllarda mainframe sistemlerde ortaya çıkmıştır. örnek olarak Oracle ve IBM DB2 verilebilir. Mainframe sistemler çok güçlü bir bilgisayar(büyük miktarda bellek, güçlü işlemci/işlemciler, geniş kapasiteli, çok hızlı ve aynı anda bir çok talebe cevap verebilen sabit diskler) ve bu bilgisayara bağlı çok sayıda ekran ve klavyeden oluşur. Ekran ve klavyeden oluşan makinalara terminal(dumb veya dummy terminal) denir. Terminaller aracılığıyla mainframe de oturumlar açılır. Her şey mainframein belleğinde diskinde ve işlemcisinde gerçekleşir. Bu yüzden oldukça sağlam ve güvenli bir yapıdır, dolayısıyla kurumlar için çok önemli olan veritabanlarının tutulması için idealdirler. VTYS ‘leri PC lerin ortaya çıkması üzerine PC ler üzerine de taşınmıştır. PC'lerin yaygınlaşması ile network'ler ortaya çıkıyor ve ilk network kurma türü olarak FileShare mimarisi ortaya çıktı. 90'lar ile birlikte PC'lerde çalışmak için tasarlanan Fileshare mimarisi ile çalışan dBase, Paradox, Access gibi RDBMS'ler çıkıyor. Bu dönemde network'lerde ortaya çıkıyor ve .mdb file share edilmiş halde bir makinada duruyor, bir klasör ya da dosya share etmiş, ve diğer makinalarda bir akıllı, asıl işi yapan client DB programı var, server'daki dosyayı paylaşımdan alıp kullanıyorlar. Bunun dezavantajı büyük, çünkü programlar tüm client'lara kurulmalı, ayrıca farklı client'lar server'a erişirken defalarca server'a gidip geliyor ve bu da network trafiği oluşturuyor. Az sayıda makinada belki olabilir ama daha fazlası kötü. Ayrıca server'daki db dosyası tam yetki ile paylaştırılıyor. En temel problemi veriye erişimin yavaş olması - WAN'da performans açısından çok yavaş. FileShare mimarisinin yetersiz kalması ile Client-Server mimarisi ortaya çıkar. Sunucuda bir server yazılımı var ve client'larda herhangi bir program olabilir. DB'nin kendisi gene sunucuda. Ancak bu mimaride client'lar bazı işleri yapıyor, gidip server'dan talepte bulunuyor. SQL Server talebi alıyor ve cevap veriyor. Bu çalışma yöntemi ile FileShare mimarisinin eksilerinin üstesinden gelindi. (FileShare mimarisinde ana makina yalnızca dosyayı paylaştırır, başka işlevi yoktur.) Büyük çapta veriyle çalışan büyük özel ve devlet kuruluşları, üniversite, hastane, askeri kurumlar vs. verilerini mainframe sistemlerde tutmaya devam ederken, orta büyüklükteki kuruluşlar mainframe maliyetine göre çok daha düşük maliyetli, ama performans olarak yeterli, PC tabanlı İstemci/Sunucu(Client/Server) mimarili istemlerde verilerini tutmaya başlamışlardır. Bunlara örnek olarak MS SQL Server, Oracle, Sybase vs verilebilir. Daha küçük kuruluşlar ise yine PC ler üzerinde tutulan dosya paylaşımı mimarili çok daha ucuz VTYS leri kullanmaktadırlar. Bunlara örnek olarak DBASE, Access, Paradox vs verilebilir. İstemci/Sunucu mimarisi genellikle bir veya daha fazla sayıda PC tabanlı sunucu ve çok sayıda PC istemciden oluşur. İstemci, sunucudan istekte bulunur, sunucu istemcinin talebi doğrultusunda kendi üzerinde bulunan veritabanından sonucu bulur ve kullanıcıya geri döndürür. İstemci ile Sunucu arasındaki veri iletişimi iki türlü olabilir 2 Tier veya n Tier. 2 Tier de istemci isteklerini doğrudan sunucuya iletip, cevabı da sunucudan doğrudan alır. n Tier de ise istemci ile sunucu arasında bir veya daha fazla bilgisayar bulunur. İstemci aradaki bilgisayardan talepte bulunur bu bilgisayar kendi üzerinde gereken işlemleri gerçekleştirip sunucudan istekte bulunur, aldığı cevabı da yine üzerinde birtakım işlemler gerçekleştirerek istemciye yollar. Örneğin internet ile ilgili bu mimarinin kullanımında tüm client'lara client program kurulamayacağına göre client browser'a sahiptir ve bununla web server'a erişir. Buna da web based mimari denilmektedir. Daha sonrası Service Oriented Architecture(SOA). Web servisleri en bilinen örneği. RDBMS (Relational Database Management System- İlişkisel Veritabanı Yönetim Sistemi) VTYS lerde günümüzde kullanılan veri-veritabanı modeli ilişkisel(relational) modeldir. İlişkisel modelden önce hiyerarşik(hierarchical)veritabanı modeli ve ağ(network) veritabanı modelleri vardır. Bu modeller günümüzde kullanılmamaktadır. Son yıllarda ilişkisel modelin yanında ODBMS(Object Database Management System-Nesne Yönelimli Veritabanı Yönetim Sistemi) ve ORDBMS(Object Relational Database Management System- Nesne İlişkisel Veritabanı Yönetim Sistemi) (önceleri adı extended relational model) de birtakım alanlarda kullanılmaya başlamıştır. İlişkisel model 1969 ve 1970 yıllarında IBM araştırmacısı E. F. Codd tarafından, hiyerarşik ve ağ veritabanı modellerindeki eksiklikleri gidermek ve büyük çapta veriler üzerinde daha kolay ve esnek işlem yapabilmek için geliştirilmiştir. Bu modelde veriler ilişkilerde yani tablolarda tutulur ve model adını buradan alır. (Genel bir yanılgı modelin adını tablolar arasındaki ilişkilerden aldığıdır). Tablolarda veriler kayıtlardan(record)(ilişkisel teoride tuple) ve alanlardan(field) (ilişkisel teoride attribute) oluşur. Kayıtların ve kolonların sırası önemli değildir. Herhangi bir veriye kayıttaki Birincil Anahtar(Primary Key-PK) aracılığıyla erişilebilir. Bu modelde veri tutarsızlığı ve yer harcamaya neden olan veri tekrarından kaçınmak için, tablo yapısı normalizasyon kuralları çerçevesinde oluşturulur ve tablolar arasında mantıksal bağlantılar yani ilişkiler(relations) kurulur. İlişkisel model çok güçlü teorik yapısı ve pratik uygulamalarıyla veritabanı dünyasında çok önemli bir yere sahiptir ve uzunca bir süre daha böyle olacağı da açıktır. Yine de birtakım eksik noktaları vardır: 1- En büyük eksikleri nesne yapısında olmamaları. Günümüzde Object Oriented yazılımlar geliştirilmekte. Uygulama kodunda ilgili tablo bir class ile temsil edilmek zorunda. Yani, yazdığımız programda tabloya karşılık gelen class'ta tablodaki field'ler sınıfın birer field'i olarak temsil edilir ve bundan sonraki işlemler zahmetlidir. Mesela bir stok tablosu 100'lerce kolondan oluşabilir ve DB'deki field'lerin kısıtlarını nesnenin field'lerinde de gerçekleştirmemiz gerekir. Bu problem için günümüzde birtakım mapping araçları kullanılmaktadır. Özetle Relational Model'de tabloyu uygulama koduna taşımakta problemler yaşanabilir. LinQ ve Hybernate bu konuda kullanılan mapping araçlarıdır. Ayrıca, son olarak DB'nin yapısı değişirse onunla bağlı uygulama kodunun da değiştirilmesi gerekir. 2) Üzerinde değişik veri tiplerini tutmak konusunda-özellikle çoklu ortam verileri-BLOB(Binary Large Object) (resim , video vs) lar, 3) Relational DB'de tablolarda inheritence kullanılamıyor. Mesela bir taban tablodan türeyen üç alt-tablo yapılamıyor. Bu yapı gerçekleştirilemediği için bir çok tabloda işlem yapılacağında anlaşılabilirliği düşürmeyi göze alarak JOIN'lerle tabloları birleştirip işlem gerçekleştirmekteyiz. ODBMS ve ORDBMS ler bu eksiklikleri gidermek için geliştirilmiştir; ancak bu konulardaki avantajlarının yanında çok büyük eksikleri vardır. Bu sistemler günümüzdeki RDBMS'lerin yapısını karşılamıyor. İlişkisel modele sınıflar(classes), sarmalama(encapsulation) ve kalıtım(inheritance) gibi OO özelliklerin katılmasıyla oluşturulan ORDBMS ler uygun çözüm olabilir görünmektedir. Genel kullanım amaçlarına göre veritabanları Genel kullanım amaçlarına göre veritabanlarını ikiye ayrılır. Bunlar: Kurumların üzerinde günlük işlemlerini yaptıkları, veriler ekleyip, değiştirip, sildikleri veritabanlarına, on-line transaction processing (OLTP) veritabanları denir. Bu veritabanları bir kurumun yaşamsal öneme sahip verilerinin tutulduğu veritabanlarıdır. Örneğin kurumların müşteri bilgileri, alım, satım ve sipariş bilgileri, alacak verecekleri, stok bilgileri, banka işlemleri vs. bu veritabanlarında tutulur. Kurumların uzun süreçlere yayılmış, büyük çapta veriler üzerinden raporlar almak amacıyla kullandıkları veritabanlarına on-line analytical processing (OLAP) veritabanları denir. OLAP veritabanları genellikle kurumun OLTP veritabanlarından, Excel, text oluşturulur. OLTP ve OLAP veritabanlarında veriler, kullanım amaçlarına uygun olarak düzenlenmişlerdir. OLTP veritabanlarında işlemlerin, hızlı ve hatasız olması için veritabanı normalize edilmiştir; OLAP veritabanları ise sadece sorgulama amaçlıdır ve buna göre düzenlenir, sorgu sonuçlarını hızlı döndürebilmek için denormalize edilmiştir. Veriler girildikçe üst ve orta yönetime raporlanması gerekir ancak raporlama için OLTP DB'ler performans kaybı olmasın diye kullanılmaz. Bunun yerine Integration Service kullanılarak DB'deki belirlenen veriler birtakım gerekli düzenleme işlemleri yapıldıktan sonra bir veri ambarına alınır (Veri ambarı (Dataware House da denir, aslında bildiğimiz DB'den bir farkı yok.): Günlük, üzerinde çalışan bir ya da bir çok kaynaktan gelen verileri tutan yer. Daha sonra bu veri ambarından raporlar çekilir.) Veri ambarına neden gerek var ? Çünkü data'lar birden çok server'dan farklı formatlarda gelir, bunlar veri ambarında ortak bir yapıya kavuşturulur, veri üzerinde düzenlemeler yapılır, ayrıca raporlama işlemlerini OLTP türü DB'ye yaptırırsak ekstradan performans kaybı oluşur. Verileri tüm server'lardan bir veri ambarına Integration Services ile toplarız. DataWare house'a günlük veriler dolar. */ CREATE TABLE Stok( StokKod char(10) NOT NULL, StokAd varchar(100) NOT NULL, Aciklama varchar(500) NULL, Fiyat] decimal(8, 2) NULL, KdvOrani int NOT NULL, Miktar int NOT NULL, AcilisTarihi datetime NULL, SatilabilirMi bit NOT NULL, Satici varchar(50) NULL, UrunGrubu varchar(50) NULL, CONSTRAINT PK_StokKod PRIMARY KEY CLUSTERED (StokKod ASC) ) select * from Stok --Satici kolonu ekle alter table Stok Add Satici varchar(50) null --Ram lerin satıcısını 'Arena' yap update Stok set Satici = 'Arena' where StokKod Like 'RAM%' --where left(StokKod, 3) = 'RAM' de yazılabilirdi. --HardDisklerin satıcısını 'Index' yapalım update Stok set Satici = 'Index' where left(StokKod, 3) = 'DSK' --RAM ve DSK lar dışında verileri sil --begin tran Delete from Stok --From yazılmayabilir where left(StokKod, 3) = 'DSK' --commit/rollback --Sonuç ekranı CTRL-R ile görünür/görünmez hale getirilebilir --Object Explorer F8 ile görünür hale getirilebilir --Stok tablosundan sadece harddisklerin StokKodu ve Adını döndürün Select StokKod, StokAd from Stok where left(StokKod, 3) = 'DSK' --Kolonlara takma ad(Alias) verilebilir Select StokKod StoğunKodu, StokAd AS StoğunAdı from Stok --AS seçimlik --Kolonlara takma adlarda boşluk olacağı zaman '[ ]' içine alınmalıdır Select StokKod [Stoğun Kodu], StokAd AS [Stoğun Adı] from Stok --Sorguya yeni kolon olarak sabit değerler eklenebilir Select StokKod [Stoğun Kodu], StokAd AS [Stoğun Adı], 1 [Stokta var mı], 'Ali Can' Satıcısı from Stok --Kolonlarla işlemler yapılabilir Select StokKod, StokAd, Fiyat, cast(Fiyat * 1.1 as decimal(8, 2)) as [%10 Zamlı fiyat] from Stok --Tablo adını seçip Alt-F1 yaparak tablo hakkında bilgi alabilirsiniz --Stok tablosunda urunlerin urun gruplarını tutmak için UrunGrup tablosu create table UrunGrup ( UrunGrupKod char(3) not null Primary key, UrunGrupAd varchar(100) not null ); --alter table Stok --drop column UrunGrubu ; alter table Stok Add UrunGrubu char(3) null; select * from Stok select * from UrunGrup update Stok Set UrunGrubu = left(StokKod, 3) --StokTablosundaki UrunGrubunda bulunan verileri UrunGrup tablosuna ekleyelim Insert UrunGrup (UrunGrupKod, UrunGrupAd) Select distinct UrunGrubu, UrunGrubu from Stok update UrunGrup Set UrunGrupAd = 'Harddisk' where UrunGrupKod = 'DSK'; update UrunGrup Set UrunGrupAd = 'Memory' where UrunGrupKod = 'RAM'; --UrunGrubu, StokKodu ve StokAdını döndüren sorgu Select UrunGrubu, StokKod, StokAd from Stok --UrunGrubu Adı, StokKodu ve StokAdını döndüren sorgu Select UrunGrup.UrunGrupAd, Stok.StokKod, Stok.StokAd from Stok join UrunGrup on Stok.UrunGrubu = UrunGrup.UrunGrupKod /* Veritabanı tasarımı Ciddi bir db tasarlanırken tasarım önce başka bir yapılır, tasarım görsel olarak oluşturulur ve bu programa şu DB Server'da git ve bu db'yi oluştur denir. Programın kendisi gerekli SQL işlemlerini gerçekleştirerek SQL Server'da DB'yi oluşturur. Erwin, Visio, Rational Rose görsel DB tasarımında kullanılan başlıca akla gelen araçlardır. E-R(Entity-Relationship) Diagram'ları Database Entity-Relation Diagram: Tabloları ve tablolar arasındaki ilişkileri gösteren diyagrama denir. Kısaltması E-R Diagram'dır. Entity, DB'de varlık anlamına gelir. DB'lerde varlıklar tablolarla temsil edilir. Tablolar arasındaki çizgilere ise relation denir. Entity-Relationship Diagram'lardaki relation çizgilerinde çeşitli ilişki şekilleri vardır. 1) Bire çok (One to Many): Primary Key'deki kolon birdir, fakat foreign key'deki many'dir. (One tarafı primary key tablosudur, Many tarafı foreign key tablosudur.) 2) Çoka Çok (Many to Many): Örneğin, Birden çok stok birden çok ürün grubuna sahip. Veriler, VTYS 'lerde tutulur ve yönetilir. Veriler, VTYS 'lerde tutulur ve yönetilir. 3) Bire bir (One to One): Bir tablonun primary key'i diğer bir tablonun primary key'ine bağlı ise bu ilişki türü one to one'dır. (Bire bir ilişkilerin çoğu tek bir tabloda da yapılabilir.) İlişki kurarken ilişkinin bir tarafı primary key olmalıdır. Bu tercih edilendir ancak kesin şart değildir. Alternatif olarak ilişki kurulan tablodaki kolon unique'se de olabilir. İlişki biçimi ilişkide primary key'lerin nasıl kullanıldığı, kullanılıp kullanılmadığı ile alakalıdır. NORMALİZASYON Normalizasyon kuralları Tablolarda yapılacak sorgulama, ekleme, silme güncelleme işlemlerinde sorun yaşanmaması için tablonun tasarımında uyulması gereken kurallardır. Tabloların Normalizasyon kurallarına uygun hale getirilmesi işlemine normalizasyon denir. 1. Kural a) Veri tekrarından kaçınılmalıdır. Bir tabloda tekrar eden veriler varsa, bu veriler başka bir tabloya alınmalı, bir kod verilmeli, kodun bulunduğu kolon primary key yapılmalı ve Ana tabloya bu kod yazılmalıdır. Ve ilk tablo ile ikinci tablo arasında foreign key ilişkisi kurulmalıdır. b) Alanlardaki(Field) veriler atomik olmalıdır, (yani bir alanda tek bir veri olmalıdır); ayrıca veri kaleminin bir özelliği birden fazla kolona da yazılmamalıdır. İki tabloyu birbirine bağlayan bir tablo yapılmalı(junction table), bu tablonun iki kolonunu da kapsayan bir composite primary key tanımlanmalıdır. 2. Kural Bir tabloda bir composite primary key var ise, tablonun diğer kolonları bu keyin tamamına bağlı olmalıdır, sadece bir kolonuna değil 3. Kural Bir tabloda primary key dışındaki tüm kolonlar primary keye bağlı olmalıdır Başka bir kolona değil. */ Insert UrunGrup values ('POR', 'Portable') create table StokUrunGrup ( StokKod char(10) not null, UrunGrupKod char(3) not null ) insert StokUrunGrup --Tüm kolonlara veri yazdığımız için kolon listesi vermedik select StokKod, UrunGrubu from Stok select * from StokUrunGrup --Stok tablosundan UrunGrubu kolonunu silelim Alter table Stok drop column UrunGrubu --'DSK320' ürünü için 'POR' ürün grubu tanımlaması yapalım insert StokUrunGrup values ('DSK320', 'POR') --Her stoğun ait olduğu ürüngrubu adı, stok kodu ve stok adını döndüren sorgu Select UrunGrup.UrunGrupAd, Stok.StokKod, Stok.StokAd from Stok join StokUrunGrup on Stok.StokKod = StokUrunGrup.StokKod join UrunGrup on StokUrunGrup.UrunGrupKod = UrunGrup.UrunGrupKod delete from StokUrunGrup where StokKod = 'DSK320' and UrunGrupKod = 'POR' insert StokUrunGrup values ('DSK320', 'POR') --Aynı ürün için aynı ürün grubunun bir kereden fazla tanımlanmaması için --StokUrunGrup tablosuna primary key koyalım --Bu key iki kolondan oluşacaktır. Birden fazla kolondon oluşan keylere --composite key denir Alter table StokUrunGrup Add Constraint PK_StokKod_UrunGrupKod Primary Key (StokKod, UrunGrupKod) insert StokUrunGrup values ('DSK320', 'POR') --çalıştırıldığında primary key hatası verir. --VERİ TİPLERİ /* Veri tipleri Sql Serverde tabloların kolonlarında kullanılacak veri tipleri şunlardır: Karakter Veri Tipleri Char: Sabit uzunluklu karakter verisi. Herhangi alfabetik, sayısal karakter ve noktalama işareti kabul eder. Parantez içerisinde, girilebilecek maksimum karakter sayısı verilmelidir(Bu sayı en fazla 8000 olabilir) Girilen verinin kaç karakter olduğuna bakılmaksızın disk üzerinde girilen maksimum karakter sayısı kadar byte yer kullanılacaktır. VarChar: Değişken uzunluklu karakter verisi. Herhangi alfabetik, sayısal karakter ve noktalama işareti kabul eder. Parantez içerisinde, girilebilecek maksimum karakter sayısı verilmelidir(Bu sayı en fazla 8000 olabilir). Girilen veri kaç karakter ise disk üzerinde o kadar byte yer kullanılacaktır. Bir kolonun veri tipinin Char(10) veya VarChar(10) veri yapılmasında fark nedir? Char(10) verildiğinde o kolona istenirse hiç veri girilmesin veya 1 karakter ya da 10 karakter veri girilsin disk üzerinde sabit 10 byte yer kullanılacaktır. VarChar(10)'da ise en fazla 10 byte olabilir diyoruz, eğer 1 karakter veri girilirse 1 byte 10 karakter veri girilirse 10 byte yer kullanılacaktır. Görüldüğü üzere Char daha çok yer götürüyor ancak VarChar'a göre daha hızlı çalışmaktadır çünkü, VarChar bir alanla işlem yapılırken önce alanın kaç karakter olduğuna ilişkin okuma işlemi yapıp daha sonra bu sayıdaki karakter için işlemi yapar, bu da VarChar'ı Char'a göre daha yavaş yapar. O halde ne zaman Char ne zaman VarChar kullanılmalı? Eğer girilecek veri hep maksimum ayarlanacak karakter sayısına yakın veya eşit ise Char kullanılmalıdır. Bu durumda fazla bir yer kaybı olmayacaktır. Örneğin telefon alan kodu her zaman 3 karakterdir, Char kullanmak uyundur. Ancak FirmaAdi alanı için varchar daha anlamlıdır; çünkü firma adlarının uzunluğu çok farklı olabilmektedir. Bu konuda son söz olarak VarChar çoğunlukla, Char ı daha seyrek kullanacaksınız. Nchar ve NvarChar, Char ve VarChar la her konuda aynıdır. Tek ve çok önemli farkları Unicode veri tutmalarıdır. Bu bir Nchar veya NvarChar alana dünyanın tüm alfabelerinden veri girebileceğiniz anlamına gelir. Ancak Char ve Varchar da her karakter için 1 byte disk alanı kullanılırken Nchar ve Nvarchar da 2 byte yer kullanılır. (Bu da bir alana en fazla 4000 karakter Nchar ve Nvarchar veri yazılabileceği anlamına gelir.) Varchar(Max) : Maksimum 2 Gb a kadar değişken uzunlukta karakter verisi tutar(2,147,483,647 karakter). Veri normal Char ve Varchar gibi tutulur. Text Maksimum 2 Gb a kadar karakter verisi tutar(2,147,483,647 karakter). Veri yığın şeklinde tutulduğu için veri okuma ve yazmak için özel fonksiyonlar kullanmak gerekir. Artık kullanılmıyor yerine varchar(max) kullanılıyor. Ntext Maksimum 2 Gb a kadar Unicode karakter verisi tutar(1,073,741,823 karakter). Veri yığın şeklinde tutulduğu için veri okuma ve yazmak için özel fonksiyonlar kullanmak gerekir. Artık kullanılmıyor yerine varchar(max) kullanılıyor. Tamsayı veri tipleri Bit: Sadece 0 veya 1 değeri alabilir(Management Studio da True veya False olarak görünür, ama işlem yapılırken 0 veya 1 kullanılmalıdır) Diskte 1 byte yer kullanır. Ancak bir tabloda 8 adete kadar bit veri tipli kolon var ise, hepsi için toplam 1 byte yer kullanılacaktır. TinyInt: 0 ile 255 arası tamsayı veri tutar. Dolayısıyla 1 byte disk alanı kullanır. SmallInt: -32,768 ile 32767 arası tamsayı veri tutar. 2 byte disk alanı kullanır. Int: -2,147,483,648) ile 2,147,483,647. arası tamsayı veri tutar. 4 byte disk alanı kullanır. BigInt: -9.223.372.036.854.775.808 ile 9.223.372.036.854.775.807 arası tamsayı veri tutar. 8 byte disk alanı kullanır. Sabit Noktalı Sayılar: Numeric ile Decimal veri tipleri aynıdır. -10^38 +1 ile 10^38 -1 arası veri tutar. Para işlemleri için en doğru seçimdir. Çünkü decimal türü otomatik yuvarlama yapmaz. Money ABD'de para konusunda virgülden sonra 4 hane tutulur, bunun için decimal'in virgülden sonra 4 hane tutan hali olarak money türü kullanılır. Türkiyede virgülden sonra 2 dijit duyarlı decimal kullanıyoruz. SmallMoney ABD'de para konusunda virgülden sonra 4 hane tutulur, bunun için decimal'in virgülden sonra 4 hane tutan hali olarak money türü kullanılır. Türkiyede virgülden sonra 2 dijit duyarlı decimal kullanıyoruz. Kayar Noktalı sayılar: Real: 3.40E + 38 ile 3.40E + 38 arası veri tautar Float: -1.79E + 308 ile 1.79E + 308 arası veri tutar Kayan noktalı türler gerek duyduklarında kendi kendilerine yuvarlama yapabilirler. Bu para işlemlerinde istenen bir durum değildir, bu yüzden para işlemlerinde kullanılmazlar. Daha çok bilimsel hesaplamalarda tercih edilirler. Smalldatetime: 1.1.1900 ile 6.6.2079 arası tarih verisini dakika duyarlıkla tutan veri tipidir. 4 byte yer kullanır Datetime: 1.1.1753, ile 31.12.9999 arası tarih verisini 3.33 milisaniye(diğer bir deyişle saniyenin binde üçü) duyarlıkla tutan veri tipidir. 8 byte yer kullanır. Date Datetime: 1.1.1 ile 31.12.9999 arası tarih verisini gün duyarlıkla tutar. 3 byte yer kullanır Time 00:00:00.0000000 ile 23:59:59.9999999 arası zaman verisini 100 nanosaniye duyarlıkla tutar. Duyarlığa göre 3-5 byte yer kullanır Datetime2: 01-01-0001 00:00:00.0000000 ile 31-12-9999 23:59:59.9999999 arası tarih verisini 100 nanosaniye duyarlıkla tutan veri tipidir. 6-8 byte yer kullanır. DatetimeOffset 01-01-0001 00:00:00.0000000 ile 31-12-9999 23:59:59.9999999 arası UTC(Coordinated Universal Time) (Evrensel Saat veya Greenwich Saati). tarih verisini 100 nanosaniye duyarlıkla tutan veri tipidir. 6-8 byte yer kullanır. İkili(Binary) veri tipleri Bilgisayarlarda her şey 0 lar ve 1 ler halinde yani İkili sayı sisteminde tutulur. O halde ikili veri ses, video, Excel dokumanı, exe dosya yani bilgisayarda tutulan herhangi bir veri olabilir. Bu tür verilerin kendisini db'de tutalım, yoksa verileri disk üzerinde klasörlerde tutup, dosyanın yolunu(path) bir varchar alanda path'ini mi tutalım ? Veritabanında tutmak veritabanı dosyalarının çok büyümesine ve yönetiminde(yedekleme, geri yükleme vs) zorluklara yol açabilir. Veritabanı dışında tutmak da farklı yönden benzeri zorluklar doğurur. Eğer ikili veri, veritabanınızın temel verisiyse, örneğin firmanızın işi resim veya video satmaksa veya güvenlikle ilgili bir iş yapıyorsanız parmak izini vs veritabanında tutmanız; ikili veri daha önemsiz bir veriyse, örneğin personelinizin fotoğrafları, ürün fotoğrafları veya videosu gibi. Bu verileri disk üstünde koyup, veritabanında sadece yolunu tutmanız daha doğru gözükmektedir. Binary 8000 byte kadar sabit uzunluklu ikili veri tutar varbinary 8000 byte kadar değişken uzunluklu ikili veri tutar image 2 Gb a kadar değişken uzunluklu ikili veri tutar DB > Programmatibility > Types > System Data Types'tan DBMS'nin kullandığı veri türleri görüntülenebilir. Diğer Veri Tipleri Sql_variant Bu veri tipine text, ntext, timestamp, ve sql_variant dışındaki tüm veri tipleri yazılabilir ancak yazarken türünü bildirmek gerekir. Gelecek versiyonlarda kaldırılması muhtemeldir; bu yüzden kullanılması tavsiye edilmez Timestamp Veritabanı bazında benzersiz bir numara üretmekte kullanılır. Satırda her değişiklik olduğunda değişir. Bu yüzden bu verinin eşanlamlısı rowversion veri tipidir. Sysname Sistem tarafından oluşturulmuş bir kullanıcı tanımlı veri tipidir. nvarchar(128) ile aynıdır ve veritabanı nesnelerinin isimlerini tutmak için kullanılabilir. uniqueidentifier: Global bir benzersiz tanımlayıcı tutar. Üretilen veri Dünyada benzersiz bir veridir. Bir kolonun türünü uniqueidentifier yapmamız, değer üretmesi için yeterli değil. Uniqueidentifier kolonlarda veriyi üretmek için sütunun Defaultuna veya binding olarak default'una newid() yazmak gereklir. Xml Xml verisini doğrudan tutar. xml veriyi indeksleyebilir. Node bazında sorgulama yapılabilir. timestamp Bu veri türlerine ek olarak, programlama yaparken değişkenlere atanabilecek veri tipleri de vardır. Bunlar: Cursor Bir kürsöre olan referansı tutar. Table Table veri tipi sütun tanımlamada kullanılmaz, daha sonra kullanmak üzere sorguların sonuçlarını saklamak için kullanılır. Transact-SQL değişkenlerinde ve kullanıcı fonksiyonlarında kullanılır. Kullanıcı Tanımlı Veri Tipleri Bunların dışında kullanıcılar kendi veri tiplerini de tanımlayabilir. EXEC sp_addtype ud_Ad, varchar(80), 'NOT NULL' GO CREATE TABLE Firma (… StokKod char(10), StokAd ud_Ad, …) */ --Datetime2 saniyenin 10.000.000 declare @Tarih1 Datetime2 select @Tarih1 = '2009-06-27 18:06:52.0000001' SELECT @Tarih1 select @Tarih1 = '2009-06-27 18:06:52.9999999' SELECT @Tarih1 --Text veri tipi yerine varchar(Max) kullanılmalı. --İkisi de 2 Gb a kadar veri tutabilir --Varchar(Max) ın avantajları -- 1- Varchar gibi okunup yazılabilir. Text veritipiyle işlem yapmak için özel fonksiyonlar gerekiyordu -- 2- Varchar(Max) veri, sığıyorsa satırda tutulur, sığmıyorsa satır dışında --Unicode veri tipleri -- Nchar, Nvarchar ve Ntext --Unicode dünyadaki tüm alfabelerin karakterlerinden oluşur. --Unicode olmayan verilerde her bir harf için 1 byte kullanılırken --Unicode de 2 byte kullanılır select * Into StokTarihce --Sorgu sonucunda yeni bir tablo "StokTarihce" olusur from Stok Alter table StokTarihce add Versiyon Timestamp Alter table StokTarihce Add Constraint PK_StokTarihce Primary Key (Versiyon, StokKod) create table Satici ( SaticiNo int identity(1, 1) not null, SaticiAd varchar(100) not null, Adres varchar(200) null, Sehir int not null, VergiDairesi varchar(100) not null, VergiNo char(11) not null, TelefonNo char(10) null, Bakiye decimal(12, 2) default (0), Constraint PK_SaticiNo Primary Key (SaticiNo) ) create table Sehir ( SehirNo int not null Primary Key, SehirAd varchar(100) ) update Sehir Set SehirAd = Upper(Left(SehirAd, 1)) + Lower(substring(SehirAd, 2, len(SehirAd) - 1)) Select * from Satici insert Satici (SaticiAd, Adres, Sehir, VergiDairesi, VergiNo, TelefonNo) values ('Arena Bilgisayar Ltd.Şti.', null, 344, 'Şişli', '1111111111', '1111111111') update Satici Set Sehir = 34 Where Sehir = 344 --Satici tablosunda Sehir kolonuna, Sehir tablosundaki SehirNo lardan başka --veri girilmesini engellmek için Foreign Key ile bağlayalım alter table Satici add constraint FK_Sehir_SehirNo Foreign Key (Sehir) References Sehir (SehirNo) --Kullanılan bağlantının kullandığı veritabanını değiştirmek için use Bute select * from Stok select * from StokUrunGrup --Fiyatı 40 tl den yüksek Stokların Kodu ve adı select StokKod, StokAd from Stok where Fiyat > 40 --Fiyatına %20 zam yapıldığında 40 tl den fazla olacak Stokların kodu ve adı select StokKod, StokAd from Stok where Fiyat * 1.2 > 40 --Normalde Fiyatı 40 tl nin altında olan ancak Fiyatına %20 zam yapıldığında --40 tl den fazla olacak Stokların kodu ve adı select StokKod, StokAd from Stok where Fiyat < 40 and Fiyat * 1.2 > 40 --Normalde Fiyatı 40 tl nin altında olan ancak Fiyatına %20 zam yapıldığında --40 tl den fazla olacak Stokların Fiyatlarına %20 zam yapalım update Stok Set Fiyat = Fiyat * 1.2 where Fiyat < 40 and Fiyat * 1.2 > 40 --Stok açılış tarihi bilinen Stokların Kodu, adı ve açılış tarihi select StokKod, StokAd, AcilisTarihi from Stok where AcilisTarihi is not null /* Kısıtlar(Constraints) Tablolara veriler girilirken, verilerin bazı kurallara uygun olması sık istenen bir durumdur. Örneğin bazı sayısal alanlara sıfırdan küçük değerlerin girilmesi istenmeyen bir durum olabilir örneğin bir stok tablosunda eldeki stok miktarının eksi değerlere düşmesi genellikle istenmez. Bu gibi durumlarda bir kısıtla bu durumun oluşması engellenebilir. Kısıtlarla yapılacak işler tetiklerle, kurallarla(rule) veya defaultlarla dayapılabilir. Ancak kısıtlar diğerlerine göre bu tür denetimler için daha uygundur. Çeşitli amaçlara yönelik 5 tür kısıt vardır. Bunlar: PRIMARY KEY Bu kısıt sütun içinde verinin benzersiz olmasını sağlar, yani briden fazla satırın bu sütununda aynı değerde birden fazla veri olamaz. Sütun NULL değer kabul etmez. Primary key olarak seçilen kolondaki veriler mümkünse değişmeyecek bir veriler olmalıdır. PK bir veya birden fazla sütundan oluşabilir. Bu tür PK lara Composite Key denir. Hemen hemen her tablonun PK sı olması gerekir. Belki geçici amaçla veri depolamak için kullanılan veya loglama amaçlı kullanılan vs tablolarda PK olmayabilir. NULL Değer Alabilme(Nullability) NULL/NOT NULL: Sütunun NULL değeri alıp/alamayacağını belirler Null değer, veri tipinden bağımsız olarak her alanda kullanılabilir ve alanda değer olmadığını gösterir. Bu yüzden Null boş stringden, boşluk karakterinden veya görünmeyen herhangi bir karakterden veya sıfırdan vs farklı birşeydir. Null ile ilgili dikkatli olunması gereken önemli bir nokta, herhangi bir sorguda bir değer ile Null veya iki Null değer karşılaştırıldığında sonucun eşit çıkıp çıkmayacağıdır. Normalde tanımsız belirsiz, boş bir alanın başka bir alanla eşit olamayacağıdır; çünkü iki değer varsa bunların karşılaştırılmasından söz edilebilir, olmayan şeyler veya olan bir şeyle olmayan bir şey karşılaştırılamaz. Yine de Null boş da olsa bir değer gibi düşünülebilir. Bu yüzden SQL Server bu konuda iki seçenek sunar. Kullanıcı vereceği bir komutla sunucu bazında yapılan bir ayarla gerçekleştirilir. Buna göre SET ANSI_NULLS ON yapıldığında sunucu, bir değerle Null bir değer veya iki Null değeri karşılaştırdığında sonuç bilinmez yani Null çıkar. SET ANSI_NULLS OFF yapıldığında ise sunucu, bir değerle Null bir değer karşılaştırdığında sonuç bilinmez yani Null çıkar; ancak iki Null değeri karşılaştırdığında sonuç True çıkar. Bu ayar ne olursa olsun ORDER BY, GROUP BY ve DISTINCT anahtar sözcükleri kullanılan sorgularda Null değerli alanlar eşit olarak değerlendirilir ve onlar bir grup olarak düşünülür. Bir alanın Null değer içerip içermediğini WHERE değer = NULL değer <> NULL ile karşılaştırmak yerine, WHERE değer IS NULL veya WHERE değer IS NOT NULL ile karşılaştırmak her zaman tercih edilmelidir. İlk seçenek sunucunun ayarına göre değişik sonuç verebilir. İkinci seçenekte ayar ne olursa olsun değer boşsa sonuç TRUE, doluysa FALSE çıkar. CHECK: Sütunda sadece belirlene koşula uyan değerlerin kabul edilmesini sağlar. Aynı sütun için birden fazla CHECK kısıtı kullanılabilir. Alter Table Stok ADD CONSTRAINT CK_Miktar CHECK (Miktar > 0) UNIQUE Bu kısıt sütun içinde verinin tek olmasını sağlar, yani bu sütunu aynı değerine sahip iki satır olamaz. Sütunda NULL değer olabilir; ama en fazla bir tane. CREATE TABLE Stok StokAd Varchar(100) UNIQUE … ) UNIQUE kısıtı aşağıdaki şekilde sonradan eklenebilir: ALTER TABLE Personel ADD CONSTRAINT UQ_PersonelAd UNIQUE(PersonelAd) Bir tabloda UNIQUE'liği bozan kayıtlar aşağıdaki sorgu ile bulunabilir: SELECT COUNT(*), PersonelAd FROM Personel GROUP BY PersonelAd HAVING COUNT(*) > 1 FOREIGN KEY Bu kısıt tablolar arasındaki ilişkiyi tanımlar; yani bu tablodaki bir sütunun başka bir tablodaki sütuna bağlı olduğunu belirler. FK olarak tanımlanmış sütuna, bağlı olduğu tablodaki sütundaki değerlerin dışında değer girilemez. Bir tablodaki foreign key başka bir tablodaki PK veya UNIQUEkolonuna bağlanabilir. Bu sayede veri bütünlüğü sağlanmış olur. Örneğin olmayan bir müşteriye fatura kesemezsiniz. Aşağıdaki gibi bir ilişki tanımıyla bu gerçekleştirilir. CREATE TABLE Musteri ( … MusteriKod Varchar(10) PRIMARY KEY, …) CREATE TABLE Fatura ( FaturaID int IDENTITY(1,1), MusteriKod Varchar(10) FOREIGN KEY REFERENCES Musteri(MusteriKod) Default Bir alana kullanıcı değer girmediği zaman alanın otomatik olarak bir değer alması istendiğinde kullanılır. Default’lar tablo yaratılırken veya değiştirilirken tanımlanabilir. Defaultlarda sabitler ve fonksiyonlar kullanılabilir. Sabit(Constant) CREATE TABLE Stok ( … StokAciklama varchar(100) DEFAULT 'Açıklama Yok', Kdv int DEFAULT 18 … ) Fonksiyon(function) CREATE TABLE Stok ( … StokAcmaTarihi DEFAULT getdate(), … ) */ --null ile yapılan karşılaştırmalar is null/is not null ile yapılmalıdır declare @d1 int, @d2 int; set @d1 = null; set @d2 = null; if @d1 = @d2 select 1 else select 0 --Sonuç 0 --StokUrunGrup tablosunda olmayan ürünlere grup tanımlanmasını engellemek --için StokKod kolonunu Stok tablosundaki StokKod kolonuna bağlayalım Alter Table StokUrunGrup Add constraint FK_StokUrunGrup_StokKod Foreign Key (StokKod) references Stok (StokKod) Alter Table StokUrunGrup drop constraint FK_StokKod Alter Table StokUrunGrup Add constraint FK_StokUrunGrup_UrunGrup Foreign Key (UrunGrupKod) references UrunGrup (UrunGrupKod) Alter Table Stok Add SaticiKod int null update Stok Set SaticiKod = Satici.SaticiNo from Stok join Satici on Stok.Satici = left(Satici.SaticiAd, charIndex(' ', Satici.SaticiAd) - 1) select left(Satici.SaticiAd, charIndex(' ', Satici.SaticiAd) - 1) from Satici Alter Table Stok Drop column Satici --StokKod, StokAd ve SaticiAdini döndüren sorgu Select Stok.StokKod, Stok.StokAd, Satici.SaticiAd from Stok join Satici on Stok.SaticiKod = Satici.SaticiNo Alter table Stok Add SaticiNo int null Update Stok Set Stok.SaticiNo = Satici.SaticiNo from Satici join Stok on Stok.Satici = left(Satici.SaticiAd, charIndex(' ', Satici.SaticiAd + ' ') -1) Alter table Stok drop column Satici Alter table Sehir Add Constraint PK_SehirNo Primary Key (SehirNo) create table Musteri ( MusteriNo int identity(1, 1) not null, MusteriAd varchar(100) not null, Adres varchar(200) null, Sehir int not null, VergiDairesi varchar(100) null, VergiNo char(11) null, TcNo char(11) null, KurumsalMi bit null, TelefonNo char(10) null, Bakiye decimal(10, 2) null default(0), Constraint PK_MusteriNo Primary Key (MusteriNo), Constraint FK_Musteri_Sehir Foreign Key (Sehir) References Sehir (SehirNo) ) create table Fatura ( FaturaNo int identity(1, 1) not null, MusteriNo int not null, Tarih datetime not null, --2008 de date IrsaliyeNo char(10) not null, ToplamTutar decimal(10, 2) not null, Kdv decimal(10, 2) not null, GenelToplam decimal(10, 2) not null, Constraint PK_FaturaNo Primary Key (FaturaNo), Constraint FK_Fatura_Musteri Foreign Key (MusteriNo) References Musteri (MusteriNo) ) Alter Table Stok Add constraint PK_StokKod Primary Key (StokKod) create table FaturaDetay ( FaturaDetayNo int identity(1, 1) not null, FaturaNo int not null, StokKod char(10) not null, BirimFiyat decimal(10, 2) not null, Adet int not null, ToplamTutar decimal(10, 2) not null, Constraint PK_FaturaDetayNo Primary Key (FaturaDetayNo), Constraint FK_FaturaDetay_Fatura Foreign Key (FaturaNo) References Fatura (FaturaNo), Constraint FK_FaturaDetay_Stok Foreign Key (StokKod) References Stok (StokKod) ) insert into Musteri (MusteriAd, Sehir, VergiDairesi, VergiNo, TcNo, KurumsalMi, TelefonNo) values ('B Bank', 34, 'Şişli', '22222222222', null, 1, '2222222222') select * from Musteri insert into Fatura (MusteriNo, Tarih, IrsaliyeNo, ToplamTutar, Kdv, GenelToplam) values (2, '20090621', '2222222222', 100, 18, 118) select * from Fatura insert into FaturaDetay (FaturaNo, StokKod, BirimFiyat, Adet, ToplamTutar) values (1, 'RAM256SD', 30, 2, 60) insert into FaturaDetay (FaturaNo, StokKod, BirimFiyat, Adet, ToplamTutar) values (1, 'DSK320', 40, 1, 40) --Bugün müşterilere, hangi ürünler kaçar adet satılmış, SELECT Musteri.MusteriNo, Musteri.MusteriAd, FaturaDetay.StokKod, Stok.StokAd, FaturaDetay.Adet FROM Fatura INNER JOIN FaturaDetay ON Fatura.FaturaNo = FaturaDetay.FaturaNo INNER JOIN Musteri ON Fatura.MusteriNo = Musteri.MusteriNo INNER JOIN Stok ON FaturaDetay.StokKod = Stok.StokKod WHERE Fatura.Tarih = convert(varchar, getdate(), 112) select convert(varchar, getdate(), 112) --112 formatı "yyyyMMdd" --Stok miktarları belli bir miktarın altına düştüğünde --otomatik alım siparişi oluşturma yapısını kuralım Alter table Stok Add MinimumMiktar int null Alter table Stok Add MaximumMiktar int null --Her bir Satıcı için Alım siparişleri oluşturalım --Siparişte, minimum miktarın altına düşen ürünler için --Maximum Miktara tamamlayacak kadar sipariş verilmelidir. Select StokKod, StokAd, MaximumMiktar - Miktar As SiparisAdedi from Stok where Miktar < MinimumMiktar Select SaticiAd, StokKod, StokAd, MaximumMiktar - Miktar As SiparisAdedi from Stok join Satici on Stok.SaticiNo = Satici.SaticiNo where Miktar < MinimumMiktar order by SaticiAd --desc -- Her bir satıcıya ayrı sipariş oluşturmak için declare @SaticiNo int declare cur_Satici cursor for Select SaticiNo from Satici open cur_Satici fetch next from cur_Satici into @SaticiNo while (@@fetch_status = 0) begin --select @SaticiNo Select Satici.SaticiAd, StokKod, StokAd, MaximumMiktar - Miktar As SiparisAdedi from Stok join Satici on Stok.SaticiNo = Satici.SaticiNo where Miktar < MinimumMiktar and Stok.SaticiNo = @SaticiNo fetch next from cur_Satici into @SaticiNo end close cur_Satici deallocate cur_Satici select PI() --Float ve Real veri tipleri yuvarlama yaptığından muhasebe ile ilgili --işlerde kullanılmazlar. declare @sayi decimal set @sayi = 1 select @sayi/3 * 3 --sonuc 2.9999 declare @sayi2 float set @sayi2 = 1 select @sayi2/3 * 3 --sonuc 3 --yuvarlama yaptı select GETDATE() waitfor delay '00:00:00.005' select GETDATE() select * from StokTarihce alter table StokTarihce Add DegistirmeTarihi datetime default (getdate()) update Stok Set Aciklama = 'İlk Açıklama' where StokKod = 'dsk320' create trigger tr_StokGuncelle on Stok for update as begin insert StokTarihce (StokKod, StokAd, Aciklama, Fiyat, KdvOrani, Miktar, AcilisTarihi, SatilabilirMi, SaticiNo, MinimumMiktar, MaximumMiktar, FransızcaAdi, OrijinalAdi) Select * from deleted end select newid() select '02-00-54-55-4E-01 ' + convert(varchar, getdate(), 121) SELECT * FROM tABLE_3 select * from Stok FOR XML Auto /*Identity Identity kolonu istenen bir sayıdan başlayıp istenen artım miktarıyla sayıların otomatik olarak oluşturulmasını sağlar. Kolon veri tipi integer tipli olmalıdır. Identity kolonu genel olarak PK olarak kullanılır; ama bunun dışında da otomatik artım istenen herhangi bir kolonda kullanılabilir. Yalnız identity kolonunda arada boşluklar kalabileceği unutulmamalıdır. Örneğin bir satır silinirse silinmiş olan identity değeri bir daha kullanılmayacaktır. */ --Sql Server 2008 'de Alter table ile birden fazla kolon --aynı komutta eklenebilir ALTER TABLE dbo.Stok ADD FransizcaAdi varchar(100) COLLATE French_CI_AS NULL, OrijinalAdi nchar(10) NULL --Sql Server 2008 'de Insert.. Values ile birden fazla satır --aynı komutta eklenebilir insert into dbo.UrunGrup Values ('EKR', 'Ekran'), ('EKK', 'Ekran Kartı'), ('USB', 'USB Drive') --StokAdı ile FransızcaAdı aynı olan ürünler select * from Stok where StokAd = FransizcaAdi Collate French_CI_AS --Stok tablosundaki OrijinalAdi kolonunun adını OrijinalAd yapalım EXECUTE sp_rename N'Stok.OrijinalAdi', N'OrijinalAd', 'COLUMN' /* SQL Komut Tipleri 1- DDL(Data Definition Language) Komutları Veritabanı nesneleri ve programlama nesneleri yaratma, değiştirme, silme komutlarıdır. a) Nesne yaratma komutları Create Database Create Table Create Procedure Create View... b) Nesne tanımı değiştirme komutları Alter Database Alter Table Alter Procedure Alter View ... c) Nesne silme komutları Drop Database Drop Table Drop Procedure Drop View... 2- DML(Data Manipulation Language) Komutları Veriler üzerinde işlem yapan komutlardır. a) Select b) Insert c) Update d) Delete 3- DCL(Data Control Language) Komutları Veriler ve veritabanı ve programlama nesneleri için yetkilendirme komutlarıdır a) Grant Yetki verir b) Deny Yasaklar c) Revoke Yetkiyi veya yasaklamayı kaldırır Sistem veritabanları : 1) Master Sql serverin çalışması için gerekli verileri tutar Master veritabanı bozulursa Sql server de kullanılmaz hale gelir --Sql server e kimler bağlanabilir Select * from Sys.SysLogins --Sql Serverdeki veritabanları Select * from Sys.SysDatabases 2) Model Yeni bir veritabanı yaratılırken şablon olarak kullanılır. Yeni veritabanının bir çok özelliği (Özellikle belirtilmezse) model veritabanından gelir 3) Msdb Zamanlanmış görevlerle ilgili tüm bilgiler(Görevin tanımı, Alarmlar, Operatörler vs) burada tutulur 4) TempDb Geçici nesnelerin(Geçici tablolar, geçici sp ler) tutulduğu veritabanıdır. Yine Sql Server birtakım işlemleri yaparken geçici olarak veri depolamak için de tempdb yi kullanır. */ create table #ZamliStok ( StokKod char(10), ZamliFiyat decimal(10, 2) ) Insert #ZamliStok Select StokKod, Fiyat * 1.1 from Stok Select StokKod, Fiyat * 1.1 ZamliFiyat into #ZamliStok2 from Stok Select * from #ZamliStok2 --sp_help ile bir tablonun tanımına bakabiliriz --(Alt-F1 sp_help i çağırır) sp_help #ZamliStok2 --sp_helpdb ile bir veritabanının tanımına bakabiliriz sp_helpdb hb /* Create Database komutu */ --DateTime select datename(mi, '19671106') select datediff(ss, '19671106', getdate()) create table ##ZamliStok ( StokKod char(10), ZamliFiyat decimal(10, 2) ) --Veritabanının Collation'unu değiştirmek için Alter Database Hb Collate Turkish_CI_AS Alter Table Stok Add Tarih2 Date /* Files/Filegroups Bir veritabanı temelde 2 dosyadan oluşur 1- .mdf (master data file) veritabanındaki tüm veri(tablo ve diğer nesnelerin tanımlar ve tablolardaki veriler) burada bulunur 2- .ldf (log file) mdf dosyasınn bütünlüğünü korumak ve gerektiğinde verilerin kurtarılması için kullanılır veritabanlarına başka veri(ndf file) ve log dosyaları eklenebilir. Olası senaryolar: 1- .mdf dosyasının bulunduğu diskte yer kalmadığında: Bu durumda servere yeni disk takılır bu diskte mdf dosyası ile aynı Filegroup ta bir ndf dosyası yaratılır. 2- Veritabanının daha hızlı çalışması için: Yeni disk takılır bu diskte mdf dosyası ile aynı Filegroup ta bir ndf dosyası yaratılır. Bu durumda birden fazla diske veri yazılıp okunacağı için performans artar 3- Yoğun kullanılan bir tablonun hızlı çalışması için Yeni disk takılır bu diskte mdf dosyası ile farklı Filegroup(yeni filegrup yaratılmalıdır) ta bir ndf dosyası yaratılır. Tablo oluşturulurken yeni yaratılan filegroup seçilir. 4- Yedekleme ve geriyükleme Özellikle büyük ölçüde arşiv verisi varsa bunların her gün yedeğini almak ve gerektiğinde geri yüklemek gereksizdir. Yeni disk takılır bu diskte mdf dosyası ile farklı Filegroup(yeni filegrup yaratılmalıdır-bu filegroup readonly yapılır) ta bir ndf dosyası yaratılır. Arşiv tabloları yeni filegroup a konur. */ --Stok tablosunu farklı bir filegroup ta oluşturmak için: ALTER DATABASE [Hb] ADD FILEGROUP [StokFileGroup] ALTER DATABASE [Hb] ADD FILE ( NAME = N'StokData', FILENAME = N'e:\Sql\StokData.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [StokFileGroup] Create table Stok ( ... ) on StokFileGroup --Domain Integrity (Tablolar arası veri bütünlüğü) ALTER TABLE dbo.Satici WITH NOCHECK ADD CONSTRAINT FK_Satici_Sehir FOREIGN KEY (Sehir) REFERENCES dbo.Sehir (SehirNo) ON UPDATE NO ACTION ON DELETE NO ACTION --WITH NOCHECK ile FK nın oluşturulması aşamasında veri bütünlüğü --kontrolu yapılması engellenebilir --Bu durumda daha sonra bütünlüğü bozan vcerileri bulmak için: --1- Select Satici.SaticiAd, Satici.Sehir, Sehir.SehirAd from Satici left outer join Sehir on Satici.Sehir = Sehir.SehirNo Where Sehir.SehirAd is null --2- Select * from Satici where Sehir not in (Select SehirNo from Sehir) --3- Select Sehir from Satici except --İlk sonuç setinde olup ta ikincide olmayan satırlar Select SehirNo from Sehir --FK yı disable etmek için ALTER TABLE dbo.Satici NOCHECK CONSTRAINT FK_Satici_Sehir --FK yı tekrar enable etmek için ALTER TABLE dbo.Satici CHECK CONSTRAINT FK_Satici_Sehir /* FK larda Primary key tablosundaki verinin silinmesi veya primary key verisinin güncellenmesi durumunda FK ne olacaktır. Burada 4 seçenek var: NO ACTION : İzin yok CASCADE : FK tablosundaki veri de silinir/güncellenir SET NULL : FK tablosundaki veri ye Null konur(İzin veriliyorsa) SET DEFAULT : FK tablosundaki ilgili kolonda Default tanımlanmışsa o konulur Genelde NO ACTION seçilir */ ALTER TABLE dbo.Satici WITH NOCHECK ADD CONSTRAINT FK_Satici_Sehir FOREIGN KEY (Sehir) REFERENCES dbo.Sehir (SehirNo) ON UPDATE NO ACTION ON DELETE NO ACTION --CHECK kısıtı --Bir kolondaki verinin bir koşula bağlı olmasını sağlar --Stok tablosunda miktar 0 ın altında olamaz ALTER TABLE Musteri drop CONSTRAINT CK_Musteri_VergiNo_TcNo ALTER TABLE Musteri add CONSTRAINT CK_Musteri_VergiNo_TcNo CHECK ((VergiNo is null and TcNo is not null and KurumsalMi = 0) or (VergiNo is not null and TcNo is null and KurumsalMi = 1)) --Check kısıtlarında başka tablolara referans edilemez. --Bu durumlarda ilgili kısıt ancak bir trigger yazılarak oluşturulabilir --Bir müşteriya mal satılırken(Fatura tablosuna satır eklenirken), --kredisinden fazla mal verilmemesini sağlayan trigger alter trigger tr_MusteriBakiyesiKontrolu on Fatura for insert as begin declare @AlabilecegiMalTutari decimal(10,2) declare @MusteriNo int, @GenelToplam decimal (10, 2) select @MusteriNo = MusteriNo, @GenelToplam = GenelToplam from inserted select @AlabilecegiMalTutari = KrediLimiti - Bakiye from Musteri where MusteriNo = @MusteriNo if (@GenelToplam > @AlabilecegiMalTutari) begin raiserror('Musteri kredi limiti yetersiz!', 16, 1) rollback end else update Musteri set Bakiye = Bakiye + @GenelToplam where MusteriNo = @MusteriNo end --Unique(Benzersiz olma) kısıtı --Bir kolondaki verinin benzersiz olmasını sağlar. 1 adet Null veri olabilir. --Stok tablosunda Stok adının Unique olmasını sağlayalım ALTER TABLE Stok ADD CONSTRAINT UQ_StokAd Unique (StokAd) --Bir databasedeki tum tabloların schemasını 'dbo' yapmak için sp_MSforeachtable 'ALTER SCHEMA dbo TRANSFER ?'; --Tum musterilerin Nakliye(Shipping) adreslerini dondurelim select Customer.FirstName + ' ' + Customer.LastName, Address.AddressLine1 + ' ' + Address.AddressLine2, City, CountryRegion from Customer join CustomerAddress on Customer. CustomerID = CustomerAddress.CustomerID join Address on CustomerAddress.AddressID = Address.AddressID where CustomerAddress.AddressType = 'Shipping' --Address.AddressLine2 ler null olduğu için tüm adresler NULL dondu. Bunu aşmak için --Isnull kullanabiliriz select Customer.FirstName + ' ' + Customer.LastName, Address.AddressLine1 + ' ' + isnull(Address.AddressLine2, ''), City, CountryRegion from Customer join CustomerAddress on Customer. CustomerID = CustomerAddress.CustomerID join Address on CustomerAddress.AddressID = Address.AddressID where CustomerAddress.AddressType = 'Shipping' --Bu durumda da adreslerde sağda birer ' ' karakteri kaldı. --Bunu rtrim ile yok edebiliriz select Customer.FirstName + ' ' + Customer.LastName, rtrim(Address.AddressLine1 + ' ' + isnull(Address.AddressLine2, '')), City, CountryRegion from Customer join CustomerAddress on Customer. CustomerID = CustomerAddress.CustomerID join Address on CustomerAddress.AddressID = Address.AddressID where CustomerAddress.AddressType = 'Shipping' --Satis personeli Pamela0 oloan Musteriler select * from Customer Where SalesPerson like '%Pamela0' --Pamela0 işten ayrıldı, yerine Ahmet0 geldi. Pamelenın musterilerini --Ahmet e devredelim --Hatalı bir işlem yapma olasılığına karşı --Update islemlerinden once Tablonun yedegini alalım select * into Customer_20090718 from Customer --Hatalı bir is yaptık update Customer set SalesPerson = '123' Where SalesPerson = 'adventure-works\pamela0' --Geri dondurmek icin update Customer set Customer.SalesPerson = Customer_20090718.SalesPerson from Customer join Customer_20090718 on Customer_20090718.CustomerID = Customer.CustomerID --Geri dondurmek icin --Tablo adlarını uzun uzun yazmak yerine takma adlar kullanalım update C set C.SalesPerson = C2.SalesPerson from Customer C join Customer_20090718 C2 on C2.CustomerID = C.CustomerID --Çalışanlar birden fazla firmada aynı adla kayıtlı iseler --biz update i yaparken firma dını bozmamamız lazım --SalesPerson da hem firma adı hem de personel adı var --İkisi '\' ile ayrılmış. O halde '\' bularak ikisini ayırabiliriz select left(SalesPerson, charIndex('\', SalesPerson) -1) CompanyName, right(SalesPerson, len(SalesPerson)- charIndex('\', SalesPerson)) SalesPersonName, substring(SalesPerson, charIndex('\', SalesPerson) + 1, 500) from Customer --Firma adı aynı kalıp, sadece SalesPerson u gunceller Update Customer set SalesPerson = left(SalesPerson, charIndex('\', SalesPerson) -1) + '\Ahmet0' where SalesPerson like '%Pamela0' --Description u 100 karakterden fazla olan ürünlerin adı SELECT distinct Product.Name FROM Product INNER JOIN ProductModel ON Product.ProductModelID = ProductModel.ProductModelID INNER JOIN ProductModelProductDescription ON ProductModel.ProductModelID = ProductModelProductDescription.ProductModelID INNER JOIN ProductDescription ON ProductModelProductDescription.ProductDescriptionID = ProductDescription.ProductDescriptionID WHERE LEN(ProductDescription.Description) > 100 --Kategorisi 'Mountain Bike' olan ürünlerin Adını ve Liste fiyatını Liste fiyatına göre --büyükten küçüğe sıralı olarak döndürelim SELECT Name, ListPrice FROM Product WHERE ProductCategoryID = 5 order by ListPrice desc --İsimlerde ',' den sonra Size var o halde ',' den sonrasını almayalım SELECT left(Name, charIndex(',' , Name) - 1), ListPrice FROM Product WHERE ProductCategoryID = 5 order by ListPrice desc --Aynı kayıttan birden fazla dönüyor --bir kere dönmesi için distinct kullanalım SELECT distinct left(Name, charIndex(',' , Name) - 1), ListPrice FROM Product WHERE ProductCategoryID = 5 order by ListPrice desc --Kategorisi 'Mountain Bike' olan ürünlerin Liste fiyatlarıyla --her bir satıştaki Satış fiyatını(UnitPrice) farkını bulalaım --Ürün adı(Product.Name), SiparisNo(SalesOrderDetail.OrderId), --Listefiyati(Product.ListPrice), satiş fiyatı(SalesOrderDetail.UnitPrice) , --Indirim Orani, Satis Tarihi ve MusteriAdi --ve aralarındaki fark SELECT P.Name, SOD.SalesOrderID, P.ListPrice, SOD.UnitPrice, P.ListPrice - SOD.UnitPrice Indirim, '%' + cast(((P.ListPrice - SOD.UnitPrice)/ P.ListPrice) * 100 as varchar) IndirimOrani, convert(varchar, SOH.OrderDate, 104) Satistarihi, C.FirstName + ' ' + C.LastName Name FROM Product P join SalesOrderDetail SOD on P.ProductID = SOD.ProductID join SalesOrderHeader SOH on SOD.SalesOrderID = SOH.SalesOrderID join Customer C on C.CustomerID = SOH.CustomerID WHERE ProductCategoryID = 5 order by ListPrice desc /* Tablo Birleştirme(Join) Inner Join Tabloları birleştirirken sadece koşula uyan sonuçları döndürür. */ --Eski usulde tablo birleştirme (join) SELECT P.ProductId, P.Name, P.ListPrice, PC.Name FROM Product P, ProductCategory PC where PC.ProductCategoryID = p.ProductCategoryID and p.Name like 'a%' --Yeni syntax SELECT P.ProductId, P.Name, P.ListPrice, PC.Name FROM Product P Inner join ProductCategory PC on PC.ProductCategoryID = p.ProductCategoryID --Equi join Where p.Name like 'a%' --Inner sözcüğü seçimlik --İki syntax arasında dönen değerler veya hız açısından bir fark yoktur --Karşılaştırma yaparken eşitlik koşulu dışında koşullar da kullanılabilir SELECT P.ProductId, P.Name, P.ListPrice, PC.Name FROM Product P join ProductCategory PC on PC.ProductCategoryID > p.ProductCategoryID --Koşul herhangi bir şey olabilir SELECT P.ProductId, P.Name, P.ListPrice, PC.Name FROM Product P join ProductCategory PC on 1 = 1 --Outer join --Satılmış olan Ürünlerin Id, ad ları ve hangi siparişte ve kaç liradan satıldıkları Select P.ProductId, P.Name, OD.SalesOrderID, OD.UnitPrice from Product P join SalesOrderDetail OD on P.ProductId = OD.ProductID --Tüm Ürünlerin Id, ad ları ve hangi siparişte ve kaç liradan satıldıkları --Hiç satılmamış olanların da Id, ad ları görünsün Select P.ProductId, P.Name, OD.SalesOrderID, OD.UnitPrice from Product P left outer join SalesOrderDetail OD on P.ProductId = OD.ProductID --outer sözcüğü seçimlik --Left Outer Join (Left join) de inner joindeki gibi koşulu sağlayan satırlar döner --Ancak ayrıca soldaki(join sözcüğünün solunda olan tablo) tabloda olup koşula uymayan satırlar da döner Select P.ProductId, P.Name, OD.SalesOrderID, OD.UnitPrice from Product P left outer join SalesOrderDetail OD on P.ProductId = OD.ProductID --Right Outer Join (Right join) de inner joindeki gibi koşulu sağlayan satırlar döner --Ancak ayrıca sağdaki tabloda olup koşula uymayan satırlar da döner Select P.ProductId, P.Name, OD.SalesOrderID, OD.UnitPrice from SalesOrderDetail OD right outer join Product P on P.ProductId = OD.ProductID --Product tablosundaki Color kolonunu normalize bir hale getirelim --Color tablosunu yaratalım create table Color ( ColorId int identity(1, 1) Primary Key, Name varchar(100) not null ) --Product tablosundaki renkleri birer defa Color tablosuna ekleyelim Insert Color Select distinct Color from Product where Color is not null --Product tablosuna Renk Id lerini tutmak için kolon ekleyelim Alter Table Product Add ColorId int --Product tablosundaki ColorId ye Color tablosundaki Id leri set edelim Update P set ColorId = C.ColorId From Product P join Color C on P.Color = C.Name --Artık Color kolonunu silebiliriz Alter Table Product Drop column Color --Her bir ürünün ID si adı, Liste fiyatı ve Rengini döndürelim Select P.ProductID, P.Name, P.ListPrice, C.Name from Product P left join Color C on P.ColorId = C.ColorId --Eski left join syntax ı (*=) --Eski right join syntax ı (=*) --Artık kullanılmıyor Select P.ProductID, P.Name, P.ListPrice, C.Name from Product P, Color C where P.ColorId *= C.ColorId --Full Outer Join(Full Join) --Ürünlerlerin renklerini döndürelim, ancak rengi belli olmayan ürünler de gelsi --ayrıca bir renkte ürün yoksa bile o renk te gözüksün Select P.ProductID, P.Name, P.ListPrice, C.Name from Product P full join Color C on P.ColorId = C.ColorId --Cross join --Tablolardaki tum satırları her hangi bir koşul olmadan --birbirleriyle eşleştirerek döndürür --Tum ürünleri tum renklerle eşleştiriniz --ProductName, Color Select P.Name, C.Name from Product P cross join Color C --Self join --Bir tablonun kendisiyle join edilmesidir --Ürünler hangi ürünlerle birlikte satılmışlar Select SOD1.SalesOrderID, SOD1.ProductID, SOD2.ProductID from SalesOrderDetail SOD1 join SalesOrderDetail SOD2 on SOD1.SalesOrderId = SOD2.SalesOrderId where SOD1.ProductID < SOD2.ProductID Order by SalesOrderId Select SOD1.SalesOrderID, SOD1.ProductID, P1.Name, SOD2.ProductID, P2.Name from SalesOrderDetail SOD1 join SalesOrderDetail SOD2 on SOD1.SalesOrderId = SOD2.SalesOrderId join Product P1 on SOD1.ProductID = P1.ProductID join Product P2 on SOD2.ProductID = P2.ProductID where SOD1.ProductID < SOD2.ProductID Order by SOD1.SalesOrderID --Personel tablosu oluşturalım Create Table Personel ( PersonelID int identity(1, 1) not null, Name varchar(100) not null, AddressID int null, Telephone char(10) null, ManagerID int null, Constraint PK_PersonelID Primary Key (PersonelID), Constraint FK_ManagerID_PersonelID Foreign Key (ManagerID) References Personel (PersonelID), Constraint FK_AddressID_AddressID Foreign Key (AddressID) References Address (AddressID) ) Insert Personel values ('Ali Can', 445, '1111111111', null); Insert Personel values ('Ayşe Can', 446, '1111111112', 1); Insert Personel values ('Fatma Gör', 447, '1111111113', 2); Insert Personel values ('Kemal Gör', 448, '1111111114', 3); Insert Personel values ('Fatma Görele', 449, '1111111115', 3); Insert Personel values ('Cemil İpek', 450, '1111111116', 4); Insert Personel values ('Cemal İpek', 451, '1111111117', 6); --Personelin adı, yoneticisinin adı select P1.Name PersonelName, P2.Name ManagerName from Personel P1 left join Personel P2 on P1.ManagerID = P2.PersonelID --Müdürü yoksa 'Genel Müdür' yazdıralım select P1.Name PersonelName, Case when P2.Name is null then 'Genel Müdür' else P2.Name End ManagerName from Personel P1 left join Personel P2 on P1.ManagerID = P2.PersonelID --Ürün karlılık oranlarını listeleyelim --Ürün adı, Maliyeti, satış fiyatı, karlılık orani, karlılık orani adı --karlılık orani adı hesaplaması: %50 nin altı 'Karli', %50-%100 arası --'Cok Karli', %100 üzeri 'Super Karli' Select Name, StandardCost, ListPrice, (ListPrice - StandardCost)/StandardCost * 100, case when (ListPrice - StandardCost)/StandardCost * 100 < 50 then 'Karli' when (ListPrice - StandardCost)/StandardCost * 100 >= 50 and (ListPrice - StandardCost)/StandardCost * 100 < 100 then 'Cok Karli' else 'Super Karli' End [Karlilik Orani] from Product --%50 ile %100 arasını şöyle de kontrol edebiliriz. Select Name, StandardCost, ListPrice, (ListPrice - StandardCost)/StandardCost * 100, case when (ListPrice - StandardCost)/StandardCost * 100 < 50 then 'Karli' when (ListPrice - StandardCost)/StandardCost * 100 between 50 and 99 then 'Cok Karli' else 'Super Karli' End [Karlilik Orani] from Product --Adres cadde mi bulalım --İçinde Street, St., Ave, Rue geçenler cadde Select AddressID, AddressLine1, case When AddressLine1 like '%Street%' or AddressLine1 like '%St.%' or AddressLine1 like '%Ave%' or AddressLine1 like '%Rue%' then 1 else 0 End IsStreet from Address --Urunlerin Size i numeric ise 'Numarasi var', 'S, L, M, XL' ise 'Bedeni Var' yazalım select Name, case when Isnumeric(Size) = 1 then 'Numarasi var' when Size in ('S', 'L', 'M', 'XL') then 'Bedeni var' else 'Size Yok' end Size from Product create table deneme ( a int identity(1, 1), b datetime ) insert deneme values ('20090101') insert deneme values ('20090102') insert deneme values ('20090103') insert deneme values ('20090104') insert deneme values ('20090105') insert deneme values ('20090101') select a, DATENAME(dw, b) from deneme --sorusundan dönen İngilizce gün isimlerini case ile --Türkçeye çevirelim select a, case when DATENAME(dw, b) = 'Sunday' then 'Pazar' when DATENAME(dw, b) = 'Monday' then 'Pazartesi' when DATENAME(dw, b) = 'Tuesday' then 'Salı' when DATENAME(dw, b) = 'Wednesday' then 'Çarşamba' when DATENAME(dw, b) = 'Thursday' then 'Perşembe' when DATENAME(dw, b) = 'Friday' then 'Cuma' when DATENAME(dw, b) = 'Saturday' then 'Cumartesi' else 'Case i yanlış yazmışınız' end, case when DATENAME(dw, b) IN ('Sunday', 'Saturday') then 'Tatil günü' else 'İş günü' end from deneme --Karşılaştırma '=' karşılştırması ise daha kısa yazılabilir select a, case DATENAME(dw, b) when 'Sunday' then 'Pazar' when 'Monday' then 'Pazartesi' when 'Tuesday' then 'Salı' when 'Wednesday' then 'Çarşamba' when 'Thursday' then 'Perşembe' when 'Friday' then 'Cuma' when 'Saturday' then 'Cumartesi' else 'Case i yanlış yazmışınız' end, case DATENAME(dw, b) when 'Sunday' then 'Tatil günü' when 'Saturday' then 'Tatil günü' else 'İş günü' end from deneme --Veri Özetleme(Aggregation) --Bir veri topluluğu içinden özet veriler çıkarmak işlemidir. --Bunun için Özetleme fonksiyonları kullanılır. --En pahalı ürün ne kadar? select max(ListPrice) from Product --En ucuzu select min(ListPrice) from Product select max(ListPrice) EnPahaliFiyat, min(ListPrice) EnUcuzFiyat, avg(ListPrice) OrtalamaFiyat from Product --Kaç çeşit ürünümüz var? select count(*) from Product --count(*) sorgudan kaç satır döndüğünü sayar --Liste fiyatı 1000 dolardan yüksek kaç çeşit ürün var? select count(*) from Product where ListPrice > 1000 --Kaç personelin yöneticisi var select count(ManagerID) from Personel --Firmada kaç yönetici var select count(distinct ManagerID) from Personel --Bugüne kadar kaç çeşit ürün satmışız select count(distinct ProductID) from SalesOrderDetail --Kaç farklı renkte ürünümüz var? select count(distinct ColorID) from Product --Bizde tanımlı olan renklerden kaç tanesinden hiç ürünümüz yok? select count(distinct c.ColorID) - count(distinct p.ColorID) from Product p right join Color c on c.ColorID = p.ColorID --Bugüne kadar toplam kac paralık ürün satmışız select sum(TotalDue) from SalesOrderHeader --virgülden sonra 2 haneye yuvarlamak için select round(sum(TotalDue), 2) from SalesOrderHeader --virgülden sonra 1 hane göstermek için select cast(sum(TotalDue) as decimal(10, 1)) from SalesOrderHeader --Varyansı select var(TotalDue) from SalesOrderHeader --Standart sapması select stdev(TotalDue) from SalesOrderHeader -- Veri Gruplama(Grouping) --Her müşteriye toplam kaç paralık satış yapmışız? select CustomerID, sum(TotalDue) from SalesOrderHeader group by CustomerID --Her müşteriye toplam kaç defa satış yapmışız? select CustomerID, count(*) from SalesOrderHeader group by CustomerID --Her renkte kaç ürünümüz var? Select c.Name, count(ProductID) from Product p right join Color c on c.ColorID = p.ColorID group by c.Name order by count(ProductID) desc /* Select cümlesinin sıralaması Bu sıralamaya uyulmazsa hata verir select from join where group by having order by */ --Kaç bay kaç bayan musterimiz var? Select Title, count(*) from Customer group by Title --Her müşteriye 1000 USD üzerinde toplam kaç defa satış yapmışız? select CustomerID, count(*) from SalesOrderHeader where TotalDue > 1000 group by CustomerID having CustomerID > 1 --Adres tablosunda Her şehirde kaç adres var Select City, count(*) from Address group by City --Adres tablosunda Kanadada her şehirde kaç adres var Select City, count(*) from Address where CountryRegion = 'Canada' group by City --Adres tablosunda her ülkede her şehirde kaç adres var --Ülke, Şehire göre sıralı Select CountryRegion, City, count(*) from Address group by CountryRegion, City Order by CountryRegion, City --Şehire göre sıralayalım Select CountryRegion, City, count(*) from Address group by CountryRegion, City Order by City --Adres tablosunda Kanadada her şehirde 3 ten fazla kaç adres var Select City, count(*) from Address where CountryRegion = 'Canada' and City like 'M%' group by City --Her bir kategoride kaçar Product var Select PC.Name, count(*) from Product P join ProductCategory PC on P.ProductCategoryID = PC.ProductCategoryID group by PC.Name --Her bir kategoride ve modelde kaçar Product var --Kategori ve Model kırılımlarına göre ürün sayısı Select PC.Name, PM.Name, count(*) from Product P join ProductCategory PC on P.ProductCategoryID = PC.ProductCategoryID join ProductModel PM on PM.ProductModelID = P.ProductModelID group by PC.Name, PM.Name order by 1, 2 desc --order by da kolon numarası kullanılabilir Select PC.Name, PM.Name, count(*) Adet from Product P join ProductCategory PC on P.ProductCategoryID = PC.ProductCategoryID join ProductModel PM on PM.ProductModelID = P.ProductModelID group by PC.Name, PM.Name order by Adet desc --order by da takma ad(Alias)kullanılabilir --Ürünleri satildikları gün sayisina göre gruplayalım --Hala satışta olan ürünler 'Satışı devam ediyor' grubunda --gösterilmelidir select case when SellEndDate is not null then cast(datediff(dd, SellStartDate, SellEndDate) as varchar) else 'Satisi Devam Ediyor' end , count(*) from Product group by case when SellEndDate is not null then cast(datediff(dd, SellStartDate, SellEndDate) as varchar) else 'Satisi Devam Ediyor' end order by 1 desc Select P.Name, PC.Name, PM.Name from Product P join ProductCategory PC on P.ProductCategoryID = PC.ProductCategoryID join ProductModel PM on PM.ProductModelID = P.ProductModelID order by colorID -- order by da kullanılan kolon, kolon listesinde olmayabilir --Hangi ülke, şehir e toplam ne kadarlık satış yapmışız --Firmanın "Main Office" adresinin şehiri satış yapılan şehirdir select a.CountryRegion, a.City, sum(soh.TotalDue) from SalesOrderHeader soh join CustomerAddress ca on ca.CustomerID = soh.CustomerID join Address a on a.AddressID = ca.AddressID where ca.AddressType = 'Main Office' group by a.CountryRegion, a.City order by a.CountryRegion, a.City --Hangi ülke, şehir e toplam ne kadarlık satış yapmışız --Firmanın "Main Office" adresinin şehiri satış yapılan şehirdir --Hiç satış yapılmayan Şehirler de 0 olaraK dönsün select a.CountryRegion, a.City, isnull(cast(sum(soh.TotalDue) as varchar), 'Satış Yok') from SalesOrderHeader soh right join CustomerAddress ca on ca.CustomerID = soh.CustomerID right join Address a on a.AddressID = ca.AddressID where ca.AddressType = 'Main Office' group by a.CountryRegion, a.City order by a.CountryRegion, a.City --Hangi ülke, şehir e kaçar adet satış yapmışız --Firmanın "Main Office" adresinin şehiri satış yapılan şehirdir --Hiç satış yapılmayan Şehirler de 0 olaraK dönsün select a.CountryRegion, a.City, a.StateProvince, count(SalesOrderID) from SalesOrderHeader soh right join CustomerAddress ca on ca.CustomerID = soh.CustomerID right join Address a on a.AddressID = ca.AddressID where ca.AddressType = 'Main Office' group by a.CountryRegion, a.City, a.StateProvince --having count(SalesOrderID) > 1 order by a.CountryRegion, a.City, a.StateProvince --Hangi ülke, şehir e kaçar adet satış yapmışız --Firmanın "Main Office" adresinin şehiri satış yapılan şehirdir --Hiç satış yapılmayan Şehirler de 0 olaraK dönsün --Ülke toplam satışları da görünsün select a.CountryRegion, a.StateProvince, a.City, count(SalesOrderID) from SalesOrderHeader soh right join CustomerAddress ca on ca.CustomerID = soh.CustomerID right join Address a on a.AddressID = ca.AddressID where ca.AddressType = 'Main Office' group by a.CountryRegion, a.StateProvince , a.City with rollup --having count(SalesOrderID) > 1 order by a.CountryRegion, a.StateProvince, a.City --Hangi kategoride hangi renkte kaç ürün var --group by Select PC.Name, C.Name, count(*) from Product P join dbo.ProductCategory PC on P.ProductCategoryID = PC.ProductCategoryID join Color C on C.ColorID = p.ColorID group by PC.Name, C.Name order by PC.Name, C.Name --Her kategoride kaç ürün var onu da gösterelim --with rollup Select PC.Name, C.Name, count(*) from Product P join dbo.ProductCategory PC on P.ProductCategoryID = PC.ProductCategoryID join Color C on C.ColorID = p.ColorID group by PC.Name, C.Name with rollup order by PC.Name, C.Name --Ayrıca kategori farkı gözetmeksizin her renkte kaç ürün --var onu da görelim --with cube Select PC.Name, C.Name, count(*) from Product P join dbo.ProductCategory PC on P.ProductCategoryID = PC.ProductCategoryID join Color C on C.ColorID = p.ColorID group by PC.Name, C.Name with cube order by PC.Name, C.Name --Top komutu --Dönen sonuç seti içinden baştan itibaren istenen sayıda satırn --getirilmesini sağlar --Doğrudan bir sayı verilebilir --En pahalı ilk üç ürünün ID si Adı , List price use AdventureWorksLT select top 3 ProductID, Name, ListPrice from Product order by ListPrice desc use Hb select top 3 StokKod, StokAd, Fiyat from Stok order by Fiyat desc --Veya % verilebilir --Tüm ürünlerin içinde en pahalı ilk % 10 u select top 10 percent StokKod, StokAd, Fiyat from Stok order by Fiyat desc --Kaç adet veri döneceği bir değişkenle verilebilir --SqlServer 2000 de yok declare @Adet int set @Adet = 3 select top (@Adet) StokKod, StokAd, Fiyat from Stok order by Fiyat desc --Sıralamanın yapıldığı kolonda, dönen son değerden birden fazla --kolon varsa o satırları da getirmesi için 'with ties' eklenir use Hb select top 3 with ties StokKod, StokAd, Fiyat from Stok order by Fiyat desc --Union --Birden fazla sonuç setini birleştirir --Müşteri ad, adresini döndüren sorgu select C.FirstName + ' ' + c.LastName Name, A.AddressLine1 + ' ' + isnull(A.AddressLine2, '') + ' ' + A.City + ' ' + A.StateProvince Address from Customer C join CustomerAddress ca on ca.CustomerID = C.CustomerID join Address a on a.AddressID = ca.AddressID where ca.AddressType = 'Main Office' union --Personelin Adı ve adresi select P.Name, A.AddressLine1 + ' ' + isnull(A.AddressLine2, '') + ' ' + A.City + ' ' + A.StateProvince from Personel P join Address a on a.AddressID = p.AddressID union select 'Ahmet Ak', '99450 Highway 159 North Humble Texas' order by 1 --union da dönen sonuç setlerinin kolon sayıları aynı --ve veritipleri uyumlu olmalıdır --Kolon adları olarak İlk tablonun kolon adları döner --Union All --Union dönen sonuc setini distinct işlemine tabi tutar. --Bu bazen istenmeyen bir durumdur --Ayrıca union distinct yapabilmek için uzun zaman harcar --Union all ile dönen sonuç setlerinin distinct işlemine tabi --tutulması engellenir select C.FirstName + ' ' + c.LastName Name, A.AddressLine1 + ' ' + isnull(A.AddressLine2, '') + ' ' + A.City + ' ' + A.StateProvince Address from Customer C join CustomerAddress ca on ca.CustomerID = C.CustomerID join Address a on a.AddressID = ca.AddressID where ca.AddressType = 'Main Office' union all --Personelin Adı ve adresi select P.Name, A.AddressLine1 + ' ' + isnull(A.AddressLine2, '') + ' ' + A.City + ' ' + A.StateProvince from Personel P join Address a on a.AddressID = p.AddressID union all select 'Ahmet Ak', '99450 Highway 159 North Humble Texas' order by 1 --Hem Musterimiz Hem personelimiz olan Kişiler --Intersect iki sorgu sonucunun kesişimini döndürür select C.FirstName + ' ' + c.LastName Name from Customer C join CustomerAddress ca on ca.CustomerID = C.CustomerID join Address a on a.AddressID = ca.AddressID where ca.AddressType = 'Main Office' intersect --Personelin Adı ve adresi select P.Name from Personel P join Address a on a.AddressID = p.AddressID --Müşteri adres listes(Personel olup müşteri olanlar hariç) --except 1. sonuç setinde olup 2. de olmayan satırları döndürür select C.FirstName + ' ' + c.LastName Name, A.AddressLine1 + ' ' + isnull(A.AddressLine2, '') + ' ' + A.City + ' ' + A.StateProvince Address from Customer C join CustomerAddress ca on ca.CustomerID = C.CustomerID join Address a on a.AddressID = ca.AddressID where ca.AddressType = 'Main Office' except --Personelin Adı ve adresi select P.Name, A.AddressLine1 + ' ' + isnull(A.AddressLine2, '') + ' ' + A.City + ' ' + A.StateProvince from Personel P join Address a on a.AddressID = p.AddressID --Alt sorgular(SubQueries) --Bir sorgunun içinde başka sorguların kullanılmasıdır --En pahalı ürünümüz kaç para? --Bu iki sorguyla bulunabilir select max(ListPrice) from Product select Name from Product Where ListPrice = 3578.27 --Veya bu iki sorgu içiçe kullanlılarak alt sorguyla yapılabilir select Name from Product Where ListPrice = (select max(ListPrice) from Product) --Bugüne kadar en az bir kere satılmış ürünerlin ID ve adı Select distinct ProductId from SalesOrderDetail select ProductID, Name from Product where ProductID IN (Select distinct ProductId from SalesOrderDetail) --Satılan en pahalı ilk 3 ürünü hangi müşteriler almış select distinct c.CustomerID, c.FirstName + ' ' + c.LastName from SalesOrderHeader soh join SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID join Customer c on soh.CustomerID = c.CustomerID where sod.ProductID IN (select distinct ProductID from SalesOrderDetail where UnitPrice * (1 - UnitPriceDiscount) IN (Select distinct top 3 UnitPrice * (1 - UnitPriceDiscount) from dbo.SalesOrderDetail order by UnitPrice * (1 - UnitPriceDiscount) desc ) ) --ListPrice sine göre en pahalı 2. ürünün adı select Name, ListPrice from Product where ListPrice = (select max(ListPrice) from Product where ListPrice <> ( select max(ListPrice) from Product ) ) --Hiç satılmamış ürünlerin ad ve fiyati select Name, ListPrice from Product where ProductID not in (select distinct ProductID from dbo.SalesOrderDetail ) --Hiç satılmamış renkler select * from Color where ColorID not in (select distinct ColorID from Product where ProductID in (select distinct ProductID from dbo.SalesOrderDetail ) and ColorID is not null ) select * from Color where ColorID not in (1, 2, 4, 5, 6, 7, 8, 9) --Açık hali select * from Color where ColorID <> 1 and ColorID <> 2 and ColorID <> 4 . . and ColorID <> 9 select * from Color where ColorID in (1, 2, 4, 5, 6, 7, 8, 9) --Açık hali select * from Color where ColorID = 1 or ColorID = 2 or ColorID = 4 . . or ColorID = 9 alter table Personel add Maas decimal(10, 2) null, Ikramiye decimal(10, 2) null, IkramiyeAdedi int null --Maas ve Ikramiye (7-ManagerID) * 1000 ve Ikramiye adedi herkes 2 --ManagerID null ise 0 alınmalıdır begin tran update Personel set Maas = (7 - isnull(ManagerID, 0)) * 1000, Ikramiye = (7 - isnull(ManagerID, 0)) * 1000, IkramiyeAdedi = 2 select * from Personel commit --En dusuk maas alan 2 kisi select top 2 * from Personel order by Maas --CustomerID 1-100 1 nolu Personelin musterisidir --CustomerID 101-200 2 nolu Personelin musterisidir . . . --Buna gore her personel kaç adet satis yapmistir? select '1 nolu personelin satis sayisi=', count(*) from dbo.SalesOrderHeader where CustomerID between 1 and 100 union select '2 nolu personelin satis sayisi=', count(*) from dbo.SalesOrderHeader where CustomerID between 101 and 200 union . . --Daha kisa yoldan select cast(PersonelID as varchar) + ' nolu personelin satis sayisi=', count(*) from dbo.SalesOrderHeader join Personel on CustomerID between (PersonelID - 1) * 100 + 1 and (PersonelID) * 100 group by PersonelID select cast(PersonelID as varchar) + ' nolu personelin satis sayisi=', count(*) from dbo.SalesOrderHeader, Personel where CustomerID between (PersonelID - 1) * 100 + 1 and (PersonelID) * 100 group by PersonelID --En çok tutarda alım yapan Musteriye toplam alım tutarının %5 i --kadar bonus tanımlayalım ve bunu customer tablosunda Bonus kolonuna yazalım alter table Customer add Bonus decimal(10, 2) default (0) not null select top 1 CustomerID, sum(Subtotal) from dbo.SalesOrderHeader group by CustomerID order by sum(Subtotal) desc update Customer Set Bonus = (select top 1 sum(Subtotal) from dbo.SalesOrderHeader group by CustomerID order by sum(Subtotal) desc ) * 0.05 where CustomerID = (select top 1 CustomerID from dbo.SalesOrderHeader group by CustomerID order by sum(Subtotal) desc ) --Toplam olarak en çok alım yapan ilk 3 Musteriye toplam alım tutarlarının %5 i --kadar bonus tanımlayalım ve bunu customer tablosunda Bonus kolonuna yazalım --Bu sonuctan bir tablo yaratalim select top 3 CustomerID, sum(Subtotal) Amount --into EnYuksekAlimYapan3Musteri from dbo.SalesOrderHeader group by CustomerID order by sum(Subtotal) desc select * from EnYuksekAlimYapan3Musteri update c set Bonus = eya.Amount * 0.05 from Customer c join EnYuksekAlimYapan3Musteri eya on c.CustomerID = eya.CustomerID select * from Customer where Bonus > 0 drop table EnYuksekAlimYapan3Musteri --Tablo yaratmanın dezavantajlari şunlar: -- 1- Tablo yaratma yetkiniz olmayabilir -- 2- Tabloyu sonradan silmeniz gerekir --Customer tablosunda tum Bonuslari 0 yapalim update c set Bonus = 0 from Customer c --join yazarken boyle kullanilmak zorunda --veya update Customer set Bonus = 0 --Bunun yerine geçici tablo(temporary table) kullanılabilir select top 3 CustomerID, sum(Subtotal) Amount into #EnYuksekAlimYapan3Musteri from dbo.SalesOrderHeader group by CustomerID order by sum(Subtotal) desc select * from #EnYuksekAlimYapan3Musteri update c set Bonus = eya.Amount * 0.05 from Customer c join #EnYuksekAlimYapan3Musteri eya on c.CustomerID = eya.CustomerID select * from Customer where Bonus > 0 --Bunun yerine türetilmiş tablo(derived table) kullanılabilir update c set Bonus = eya.Amount * 0.05 from Customer c join (select top 3 CustomerID, sum(Subtotal) Amount from dbo.SalesOrderHeader group by CustomerID order by sum(Subtotal) desc ) eya on c.CustomerID = eya.CustomerID --En yuksek maas alan 4-6 arası Personelin adı ve Maasları select * from (Select top 3 * from (select top 6 Name, Maas from Personel order by Maas desc) Ilk6Maas Order by Maas) Maas4_6 Order by Maas desc --Product categorisine, ülkeler ve şehirlere top lam ne kadarlık satış yapmışız --Kategori adı, ülke, şehir adı, toplam satış(Main Office adresi baz alınacaktır) select pc.Name, a.CountryRegion, a.City, sum(sod.LineTotal) from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID join ProductCategory pc on pc.ProductCategoryID = p.ProductCategoryID join SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID --join Customer c on c.CustomerID = soh.CustomerID --customer i eklemek gereksiz soh de CustomerID var join CustomerAddress ca on ca.CustomerID = soh.CustomerID and ca.AddressType = 'Main Office' --and ca.AddressType = 'Main Office' eklenmezse birden fazla adresi olan aynı musteri icin --birden fazla satır gelebilir. Burada kaldırılınca aynı geldi cunku 2 adresi olan musterilerin --her iki adresi de aynı ulke ve sehirde join Address a on a.AddressID = ca.AddressID group by pc.Name, a.CountryRegion, a.City order by pc.Name, a.CountryRegion, a.City --Birden fazla adresi olan muşteriler SELECT CustomerID, COUNT(*) FROM CustomerAddress group by CustomerID having COUNT(*) > 1 --990 nolu product bir kampanyayla satıldığı için bu sonuçtan çıkaralım select pc.Name, a.CountryRegion, a.City, sum(sod.LineTotal) from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID join ProductCategory pc on pc.ProductCategoryID = p.ProductCategoryID join SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID --join Customer c on c.CustomerID = soh.CustomerID --customer i eklemek gereksiz soh de CustomerID var join CustomerAddress ca on ca.CustomerID = soh.CustomerID and ca.AddressType = 'Main Office' join Address a on a.AddressID = ca.AddressID where sod.ProductID <> 990 group by pc.Name, a.CountryRegion, a.City order by pc.Name, a.CountryRegion, a.City --Bu sonuç setiyle bir önceki sonuç seti aynı mı Select (select sum(Toplam) from (select pc.Name, a.CountryRegion, a.City, sum(sod.LineTotal) Toplam from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID join ProductCategory pc on pc.ProductCategoryID = p.ProductCategoryID join SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID join CustomerAddress ca on ca.CustomerID = soh.CustomerID and ca.AddressType = 'Main Office' join Address a on a.AddressID = ca.AddressID where sod.ProductID <> 990 group by pc.Name, a.CountryRegion, a.City ) CusWithout990 ) As Product990siz, (select sum(Toplam) from (select pc.Name, a.CountryRegion, a.City, sum(sod.LineTotal) Toplam from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID join ProductCategory pc on pc.ProductCategoryID = p.ProductCategoryID join SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID join CustomerAddress ca on ca.CustomerID = soh.CustomerID and ca.AddressType = 'Main Office' join Address a on a.AddressID = ca.AddressID --where sod.ProductID <> 990 group by pc.Name, a.CountryRegion, a.City ) CusWith990 ) As Product990li --Hangi satırlar için 990 no lu urunlu ve 990 nolu urunsuz sonuçlar farklı select pc.Name, a.CountryRegion, a.City, sum(sod.LineTotal) Toplam into #CusWithout990 from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID join ProductCategory pc on pc.ProductCategoryID = p.ProductCategoryID join SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID join CustomerAddress ca on ca.CustomerID = soh.CustomerID and ca.AddressType = 'Main Office' join Address a on a.AddressID = ca.AddressID where sod.ProductID <> 990 group by pc.Name, a.CountryRegion, a.City select pc.Name, a.CountryRegion, a.City, sum(sod.LineTotal) Toplam into #CusWith990 from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID join ProductCategory pc on pc.ProductCategoryID = p.ProductCategoryID join SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID join CustomerAddress ca on ca.CustomerID = soh.CustomerID and ca.AddressType = 'Main Office' join Address a on a.AddressID = ca.AddressID --where sod.ProductID <> 990 group by pc.Name, a.CountryRegion, a.City select w.Name, w.CountryRegion, w.City, wo.Toplam, w.Toplam from #CusWithout990 wo join #CusWith990 w on w.Name = wo.Name and w.CountryRegion = wo.CountryRegion and w.City = wo.City where wo.Toplam <> w.Toplam select sum(LineTotal) from SalesOrderDetail where ProductID = 990 --Aynısı Alt sorgu ile yapılırsa --Hangi satırlar için 990 no lu urunlu ve 990 nolu urunsuz sonuçlar farklı select w.Name, w.CountryRegion, w.City, wo.Toplam, w.Toplam from (select pc.Name, a.CountryRegion, a.City, sum(sod.LineTotal) Toplam from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID join ProductCategory pc on pc.ProductCategoryID = p.ProductCategoryID join SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID join CustomerAddress ca on ca.CustomerID = soh.CustomerID and ca.AddressType = 'Main Office' join Address a on a.AddressID = ca.AddressID where sod.ProductID <> 990 group by pc.Name, a.CountryRegion, a.City ) wo join (select pc.Name, a.CountryRegion, a.City, sum(sod.LineTotal) Toplam from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID join ProductCategory pc on pc.ProductCategoryID = p.ProductCategoryID join SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID join CustomerAddress ca on ca.CustomerID = soh.CustomerID and ca.AddressType = 'Main Office' join Address a on a.AddressID = ca.AddressID --where sod.ProductID <> 990 group by pc.Name, a.CountryRegion, a.City ) w on w.Name = wo.Name and w.CountryRegion = wo.CountryRegion and w.City = wo.City where wo.Toplam <> w.Toplam --Product categorisine, ülkeler ve şehirlere toplam ne kadarlık satış yapmışız --Kategori adı, ülke, şehir adı, toplam satış(Main Office adresi baz alınacaktır) --990 nolu product bir kampanyayla satıldığı için bu sonuçtan çıkaralım --Bir kategoride 10 dan az ürün varsa bu kategorileri sonuçtan hariç tutalım select pc.Name, a.CountryRegion, a.City, sum(sod.LineTotal) from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID join ProductCategory pc on pc.ProductCategoryID = p.ProductCategoryID join SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID --join Customer c on c.CustomerID = soh.CustomerID --customer i eklemek gereksiz soh de CustomerID var join CustomerAddress ca on ca.CustomerID = soh.CustomerID and ca.AddressType = 'Main Office' join Address a on a.AddressID = ca.AddressID where sod.ProductID <> 990 group by pc.Name, a.CountryRegion, a.City order by pc.Name, a.CountryRegion, a.City --Bugün itibariyle nakliyesi gerçekleşmeyen sipariş var mi?(DueDate den bakılacak) --SalesOrderHeader de OrderDate, DueDate, ShipDate e CustomerID nin son rakamı --kadar ay ve gun ekleyelim --begin tran update SalesOrderHeader set OrderDate = DateAdd(month, cast(right(CustomerID, 1) as int), (OrderDate + cast(right(CustomerID, 1) as int))), DueDate = DateAdd(month, cast(right(CustomerID, 1) as int), (DueDate + cast(right(CustomerID, 1) as int))), ShipDate = DateAdd(month, cast(right(CustomerID, 1) as int), (ShipDate + cast(right(CustomerID, 1) as int))) --4 yıl ekleyelim update SalesOrderHeader set OrderDate = DateAdd(year, 4, OrderDate), DueDate = DateAdd(year, 4, DueDate), ShipDate = DateAdd(year, 4, ShipDate) -- Yıllara ve aylara göre sipariş adedi(OrderDate üzerinden) select year(getdate()) select month(getdate()) select datepart(yy, getdate()) select datepart(year, getdate()) select datepart(MM, getdate()) select datepart(month, getdate()) select year(OrderDate) Yil, month(OrderDate) Ay, count(*) Adet into #Satis from SalesOrderHeader group by year(OrderDate), month(OrderDate) order by year(OrderDate), month(OrderDate) --2008 yılında her ay bir onceki aya gore satis adedi değişimini gösterelim select * from #Satis where Yil = 2008 select s2.Yil, s2.Ay, s2.Adet - s1.Adet from #Satis s1 join #Satis s2 on s2.Ay - 1 = s1.Ay where s2.Yil = 2008 --Hangi kategoriden toplam kac adet urun satmisiz select pc.Name, count(*), sum(sod.OrderQty) from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID join ProductCategory pc on pc.ProductCategoryID = p.ProductCategoryID group by pc.Name --Bisikletlerin yaninda pompa paket olarak verilmeye baslandi --Bunu SalesOrderDetail de IsPumpGiven adli bit veri tipli kolonda --tutalim(Bu yanlis bir tasarim, dogrusunu yapacagiz) alter table SalesOrderDetail add IsPumpGiven bit null --Satilan bisikletler select sod.* from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID where p.ProductCategoryID in (1, 5, 6, 7) --Deneme için SalesOrderDetailId sinin son rakami 1 olanlara --pompa verildi yapalim update SalesOrderDetail set isPumpGiven = 1 where right(SalesOrderDetailID, 1) = 1 --Bir urunun yaninda paket olarak baska urunler verilmesi --icin dogru tasarim bunu ayri bir tabloda tutmaktır create table SalesOrderDetailBundle ( SalesOrderDetailID int not null, BundleProductID int not null constraint PK_SalesOrderDetailBundle primary key (SalesOrderDetailID, BundleProductID) constraint FK_SalesOrderDetailID foreign key (SalesOrderDetailID) references SalesOrderDetail (SalesOrderDetailID), constraint FK_BundleProductID foreign key (BundleProductID) references Product (ProductID) ) --Daha once IsPumpGiven alani 1 olan SalesOrderDetailler --bu tabloya satir olarak gelmeli select * from Product where ProductCategoryID = 40 select * from ProductCategory --IsPumpGiven alanı 1 olan ve 'Mountain Bikes'(ProductCategoryID = 5) satilmis --olan SalesOrderDetailer için SalesOrderDetailBundle tablosuna 845 nolu --pompa verilmiş olması icin satir girelim insert SalesOrderDetailBundle select SalesOrderDetailID, 845 from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID where p.ProductCategoryID = 5 and sod.IsPumpGiven = 1 --ProductCategoryID = 10 olan urunden bugune kadar almis olanlara --ProductID = 711 Kask hediye edecegiz insert SalesOrderDetailBundle select SalesOrderDetailID, 711 from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID where p.ProductCategoryID = 10 --Yanında bundle urun verilen satislar getirelim --Tarih, satilanurun, bundle verilen urun getirilmeli --(IsPumpGiven ile 845 no lu urun verilmistir) --SalesOrderDetailBundle ile verilenler select soh.OrderDate, sod.ProductID, p1.Name, sodb.BundleProductID, p2.Name from SalesOrderDetail sod join Product p1 on p1.ProductID = sod.ProductID join SalesOrderHeader soh on sod.SalesOrderID = soh.SalesOrderID join SalesOrderDetailBundle sodb on sod.SalesOrderDetailID = sodb.SalesOrderDetailID join Product p2 on p2.ProductID = sodb.BundleProductID --IsPumpGiven ile verilenler select soh.OrderDate, sod.ProductID, p1.Name, 845, p2.Name from SalesOrderDetail sod join Product p1 on p1.ProductID = sod.ProductID join SalesOrderHeader soh on sod.SalesOrderID = soh.SalesOrderID join Product p2 on p2.ProductID = 845 where IsPumpGiven = 1 --veya select soh.OrderDate, sod.ProductID, p1.Name, 845, (Select Name from Product where ProductID = 845) from SalesOrderDetail sod join Product p1 on p1.ProductID = sod.ProductID join SalesOrderHeader soh on sod.SalesOrderID = soh.SalesOrderID where IsPumpGiven = 1 --İki sonucu Union ile birlestirelim select sod.SalesOrderDetailID, soh.OrderDate, sod.ProductID, p1.Name, sodb.BundleProductID, p2.Name from SalesOrderDetail sod join Product p1 on p1.ProductID = sod.ProductID join SalesOrderHeader soh on sod.SalesOrderID = soh.SalesOrderID join SalesOrderDetailBundle sodb on sod.SalesOrderDetailID = sodb.SalesOrderDetailID join Product p2 on p2.ProductID = sodb.BundleProductID union select sod.SalesOrderDetailID, soh.OrderDate, sod.ProductID, p1.Name, 845, (Select Name from Product where ProductID = 845) from SalesOrderDetail sod join Product p1 on p1.ProductID = sod.ProductID join SalesOrderHeader soh on sod.SalesOrderID = soh.SalesOrderID where IsPumpGiven = 1 order by 1, 2, 4 select * from Product where ProductID = 875 --113311 nolu SalesOrderDetailID ye verdigimiz pump sorunlu, yeni bir tane verecegiz --ancak bunu Bundle tablosuna yazıyoruz insert SalesOrderDetailBundle values (113311, 845) --SalesOrderDetail bazında kacar adet bundle urun verdik --SalesOrderDetailID, Count select SalesOrderDetailID, sum(Adet) from (select sod.SalesOrderDetailID, count(*) Adet from SalesOrderDetail sod join SalesOrderDetailBundle sodb on sod.SalesOrderDetailID = sodb.SalesOrderDetailID group by sod.SalesOrderDetailID union all select sod.SalesOrderDetailID, 1 Adet from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID where IsPumpGiven = 1 and p.ProductCategoryID <> 5 ) d group by SalesOrderDetailID --IsPumpGiven alanı 1 olan ve 'Mountain Bikes'(ProductCategoryID = 5) --olan urunler icin, birlestirilen her iki sorgudan --sonuc geliyor. Bu kayitlari 1 eksiltmemiz lazım select isnull(d1.SalesOrderDetailID, d2.SalesOrderDetailID), isnull(d1.Adet, 0) + isnull(d2.Adet, 0) from (select sod.SalesOrderDetailID, count(*) Adet from SalesOrderDetail sod join SalesOrderDetailBundle sodb on sod.SalesOrderDetailID = sodb.SalesOrderDetailID group by sod.SalesOrderDetailID ) d1 full join (select sod.SalesOrderDetailID, 1 Adet from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID where IsPumpGiven = 1 and p.ProductCategoryID <> 5 ) d2 on d1.SalesOrderDetailID = d2.SalesOrderDetailID --İlişkili Altsorgular(Correlated Subqueries) --Bu sorgularda once disaridaki sorgu calisir ve her bir satir icin altsorguya bir deger gonderir --Alt sorgu bu degeri de kullanarak bir sonuc uretir --Disaridaki sorgu bu sonucu kullanarak sonucu dondurur --Alt sorgu dis sorgudaki her satir icin tekrar tekrar calisir --PersonelAdi, YoneticisininAdi ni sonduren sorgu select p.Name, isnull(m.Name, 'Yoneticisi Yok') from Personel p left join Personel m on p.ManagerID = m.PersonelID select * from Personel select * from Personel --Aynisi correlated subquery ile select p.Name, isnull((select m.Name from Personel m where m.PersonelID = p.ManagerID), 'Yoneticisi Yok') from Personel p --Her bir musteri 2009 yili icerisinde bizden kac defa urun almis --(Correlated subquery ile yapilacak) --CustomerID, CustomerName, Count select CustomerID, FirstName + ' ' + Lastname CustomerName, (select count(*) from SalesOrderHeader where CustomerID = c.CustomerID and year(OrderDate) = 2009 ) Adet from Customer c --Personel Prim hesaplama --Bunun icin SalesOrderHeader tablosuna SalesPersonID kolonun u ekleyelim alter table SalesOrderHeader add SalesPersonID int --Personelin bugune kadar yapmis oldugu satistan elde etikleri prim --(yaptiklari satisin % 3 u prim olarak hesaplanmalidir) --PersonelID, PersonelName, Prim --1. yontem join ile select p.PersonelID, p.Name, sum(SubTotal) * 0.03 from Personel p join SalesOrderHeader soh on p.PersonelID = soh.SalesPersonID group by p.PersonelID, p.Name --2. yontem correlated subquery ile select p.PersonelID, p.Name, (select sum(SubTotal) * 0.03 from SalesOrderHeader soh where soh.SalesPersonID = p.PersonelID) from Personel p --Primi olmayanlar gelmesin select p.PersonelID, p.Name, (select sum(SubTotal) * 0.03 from SalesOrderHeader soh where soh.SalesPersonID = p.PersonelID) from Personel p where --(select sum(SubTotal) * 0.03 from SalesOrderHeader soh where soh.SalesPersonID = p.PersonelID) > 0 (select count(*) from SalesOrderHeader soh where soh.SalesPersonID = p.PersonelID) > 0 --veya select * from (select p.PersonelID, p.Name, (select sum(SubTotal) * 0.03 from SalesOrderHeader soh where soh.SalesPersonID = p.PersonelID) Prim from Personel p) d where Prim is not null --3. yontem turetilmis tablo ile select p.PersonelID, p.Name, pp.Prim from Personel p join (select SalesPersonID, sum(SubTotal) * 0.03 Prim from SalesOrderHeader group by SalesPersonID) pp on p.PersonelID = pp.SalesPersonID --Toplam satis 50.000 USD ye kadar prim, 50.000 in ustune %3 prim select p.PersonelID, p.Name, (sum(SubTotal) - 50000) * 0.03 from Personel p join SalesOrderHeader soh on p.PersonelID = soh.SalesPersonID group by p.PersonelID, p.Name having sum(SubTotal) > 50000 --2. yontem select p.PersonelID, p.Name, case when (select sum(SubTotal) from SalesOrderHeader soh where soh.SalesPersonID = p.PersonelID) > 50000 then ((select sum(SubTotal) from SalesOrderHeader soh where soh.SalesPersonID = p.PersonelID) - 50000) * 0.03 else 0 end from Personel p --3. yontem turetilmis tablo ile select p.PersonelID, p.Name, pp.Prim from Personel p join (select SalesPersonID, (sum(SubTotal) - 50000) * 0.03 Prim from SalesOrderHeader group by SalesPersonID having sum(SubTotal) > 50000) pp on p.PersonelID = pp.SalesPersonID --Prim hesaplama 50.000 e kadar 0, 50.000 150.000 arası %2, 150.000 useri % 3, --toplam prim max 4.000 USD olabilir select p.PersonelID, p.Name, cast(case when sum(SubTotal) <= 150000 then (sum(SubTotal) - 50000) * 0.02 else case when (150000 - 50000) * 0.02 + (sum(SubTotal) - 150000) * 0.03 <= 4000 then (150000 - 50000) * 0.02 + (sum(SubTotal) - 150000) * 0.03 else 4000 end end as decimal(6, 2)) from Personel p join SalesOrderHeader soh on p.PersonelID = soh.SalesPersonID group by p.PersonelID, p.Name having sum(SubTotal) > 50000 --Exists --En az 1 kere satilmis urunlarin ProductID si ve adi select ProductID, Name from Product where ProductID in (select distinct ProductID from SalesOrderDetail) select ProductID, Name from Product p where exists (select * from SalesOrderDetail soh where soh.ProductID = p.ProductID) --Bugune kadar hic satis yapmamis olan Personel select PersonelID, Name from Personel p where not exists (select * from SalesOrderHeader soh where soh.SalesPersonID = p.PersonelID) --Bir sipariste en az bir kere tek basina satilmis urunler nelerdir --ProductID, ProductName select sod.ProductID , p.Name from SalesOrderDetail sod join Product p on sod.ProductID = p.ProductID where SalesOrderID in (Select SalesOrderID from SalesOrderDetail group by SalesOrderID having count(*) = 1) --Bizden Bisiklet(ProductCategory 1, 5, 6, 7) alıp, --kask(helmet)(ProductCategory 35) almayan Musteriler select CustomerID, FirstName + ' ' + LastName Name from Customer where CustomerID in (select distinct soh.CustomerID from SalesOrderHeader soh join SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID join Product p on p.ProductID = sod.ProductID where p.ProductCategoryID in (1, 5, 6, 7) except select distinct soh.CustomerID from SalesOrderHeader soh join SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID join Product p on p.ProductID = sod.ProductID where p.ProductCategoryID = 35 ) --Veritabanının sahibini değiştirmek için sp_changedbowner 'sa' /* Gecici Tablolar(Temporary tables) Tempdb veritabanında olusur, olusturuldugu sırada bagli bulunan veritabanında gibi davranir. Bircok acidan normal bir tablo gibidir. Indeksler olusturulabilir, tablo istendiginde drop edilebilir. 2 tur temporary tablo var. Bunlar: 1- Local Temporary Tables Sadece olustugu baglamda gorulebilir ve baglam sona erdiginde yok olur. Baglamin sona ermesi beklenemeden isi bittiginde drop etmek iyi bir pratiktir. Tablo adının basina '#' isareti konularak olusturulmalıdır 2- Global Temporary Tables Sadece olustugu baglamda degil baskaları tarafindan da gorulebilir Tum referanslar yok oldugunda yok olur. Tablo adının basina '##' isareti konularak olusturulmalıdır */ --Ulke ve sehir bazında kacar musterimiz var(Main Office adresine gore) SELECT isnull(CountryRegion, '') Ulke, case when City is null and CountryRegion is not null then 'Ulke toplami' when City is null and CountryRegion is null then 'Tum ulkeler' else City end Sehir, count(*) Adet FROM Address a JOIN CustomerAddress ca ON a.AddressID = ca.AddressID and AddressType = 'Main Office' group by CountryRegion, City with rollup order by CountryRegion, City --Buradan Tum Ulkeler, Ulke Toplamlari ve her ulkede en fazla musterimiz olan --sehirleri dondurelim SELECT isnull(CountryRegion, '') Ulke, case when City is null and CountryRegion is not null then 'Ulke toplami' when City is null and CountryRegion is null then 'Tum ulkeler' else City end Sehir, count(*) Adet into #UlkeSehirMusteri FROM Address a JOIN CustomerAddress ca ON a.AddressID = ca.AddressID and AddressType = 'Main Office' group by CountryRegion, City with rollup order by CountryRegion, City select Ulke, Sehir, Adet from #UlkeSehirMusteri where Sehir in ('Tum ulkeler', 'Ulke toplami') union select usa.Ulke, usa.Sehir, usa.Adet from #UlkeSehirMusteri usa join (select Ulke, max(Adet) MaxAdet from #UlkeSehirMusteri where Sehir not in ('Tum ulkeler', 'Ulke toplami') group by Ulke ) uma on usa.Ulke = uma.Ulke and usa.Adet = uma.MaxAdet select Ulke, Sehir, Adet from (SELECT isnull(CountryRegion, '') Ulke, case when City is null and CountryRegion is not null then 'Ulke toplami' when City is null and CountryRegion is null then 'Tum ulkeler' else City end Sehir, count(*) Adet FROM Address a JOIN CustomerAddress ca ON a.AddressID = ca.AddressID and AddressType = 'Main Office' group by CountryRegion, City with rollup ) d where Sehir in ('Tum ulkeler', 'Ulke toplami') union select usa.Ulke, usa.Sehir, usa.Adet from (SELECT isnull(CountryRegion, '') Ulke, case when City is null and CountryRegion is not null then 'Ulke toplami' when City is null and CountryRegion is null then 'Tum ulkeler' else City end Sehir, count(*) Adet FROM Address a JOIN CustomerAddress ca ON a.AddressID = ca.AddressID and AddressType = 'Main Office' group by CountryRegion, City with rollup ) usa join (select Ulke, max(Adet) MaxAdet from (SELECT isnull(CountryRegion, '') Ulke, case when City is null and CountryRegion is not null then 'Ulke toplami' when City is null and CountryRegion is null then 'Tum ulkeler' else City end Sehir, count(*) Adet FROM Address a JOIN CustomerAddress ca ON a.AddressID = ca.AddressID and AddressType = 'Main Office' group by CountryRegion, City with rollup ) d2 where Sehir not in ('Tum ulkeler', 'Ulke toplami') group by Ulke ) uma on usa.Ulke = uma.Ulke and usa.Adet = uma.MaxAdet --Common Table Expressions(CTE) --Bir sorguda birden fazla ayni turetilmis tabloyu kullanmak gerekirse uygun --cozumdur. Alternatif olarak temporary tablokullanılabilir; --ancak temporary tablo diske yazarken zaman kaybeder, --ayrıca bazı durumlarda tek bir sorguyla sonucu dondurmek gerekebilir. with UlkeSehirMusteri as (SELECT isnull(CountryRegion, '') Ulke, case when City is null and CountryRegion is not null then 'Ulke toplami' when City is null and CountryRegion is null then 'Tum ulkeler' else City end Sehir, count(*) Adet FROM Address a JOIN CustomerAddress ca ON a.AddressID = ca.AddressID and AddressType = 'Main Office' group by CountryRegion, City with rollup) select Ulke, Sehir, Adet from UlkeSehirMusteri where Sehir in ('Tum ulkeler', 'Ulke toplami') union select usa.Ulke, usa.Sehir, usa.Adet from UlkeSehirMusteri usa join (select Ulke, max(Adet) MaxAdet from UlkeSehirMusteri where Sehir not in ('Tum ulkeler', 'Ulke toplami') group by Ulke ) uma on usa.Ulke = uma.Ulke and usa.Adet = uma.MaxAdet select top 10 * into ##AddressTop10 from Address /* Gorunumler(View) Kaydedilmis select cumleleridir. Bir tablo gibi işlev gorurler Kullanim amaclari: 1- Surekli kullanilan sorgular kaydedilmis olur. Her defa yeniden yazmaya gerek kalmaz. Ayrıca bir kere yazilir ve yetkisi olan herkes gorunumu kullanabilir 2- Kolon bazinda yetkilendirme yapmak icin kullanılabilirler (Alternatif olarak kolon bazinda yetkilendirme de yapilabilir) --Maas bilgileri haric Personel bilgilerini donduren gorunum */ create view vw_Personel as select PersonelID, Name, AddressID, Telephone, ManagerID from Personel select * from vw_Personel --3- Satir bazinda yetkilendirme yapmak icin kullanılabilir --Sadece bisikletleri gormek yetkisi vermek icin create view vw_Bikes as select * from Product where ProductCategoryID in (1, 5, 6, 7) --4- Gorunum kullanicisi alttaki sorgunun karmasikligini --bilmek zahmetinden kurtulmasi --5- Altttaki yapiyi kullanicidan gizlemek icin kullanilabilir --Gorunumun tanimini degistirmek icin Alter view komutu --kullanilmalidir. --select * ile olusturulmus bir gorunumun altindaki tabloya --kolon eklendiginde, gorunume de eklenmis olmaz. Bunun icin --taniminda hic bir yapmadan view u alter etmektir. alter table product add deneme int select * from vw_Bikes -- yeni kolon gorunmez alter view vw_Bikes as select * from Product where ProductCategoryID in (1, 5, 6, 7) select * from vw_Bikes -- yeni kolon gorunur --Gorunumler uzerinden insert, delete ve update belli kosulllarla --yapilabilir. Bunlar: --1- Gorunum tek tablodan olusuyorsa veri eklenirken, --gorunum tarafindan icerilmeyen kolonlar null a izin --vermelidir veya default degerleri olmalidir --Personel tablosuna vw_Personel gorunumu uzerinden veri ekleyelim insert vw_Personel (Name, AddressID, Telephone, ManagerID) values ('Gul Guler', 9, '1232345', 3) --2- Gorunum birden fazla tablodan olusuyorsa --Insert ve update islemlerinden sadece bir tablo etkilenmelidir --ProductID, Name ve Color name ini donduren bir gorunum yapalim create view vw_ProductColor as Select p.ProductID, p.Name, c.Name Color from Product p join Color c on p.ColorID = c.ColorID select * from vw_ProductColor update vw_ProductColor set Name = Name + '-Siyah', Color = 'Siyah' where ProductID = 680 -- hata verir update vw_ProductColor set Name = Name + '-Siyah' where ProductID = 680 --Product tablosunda 680 nolu urunun adinin sonuna '-Siyah' ekler update vw_ProductColor set Color = 'Siyah' where ProductID = 680 --Color tablosunda 'Black' olan Color u 'Siyah' yapar --3-Gorunumun taniminda group by, aggregation fonksiyonari veya union, intersect, --except varsa veya nondeterministic fonksiyonlar(getdate(), rand()) varsa --veya hesaplanmis kolonlar varsa --bu gorunumlerde update insert, delete yapilamaz --Satir bazinda yetkilendirme yapilan gorunumlerde, sadece kosula uyan --satirlarin(yani gorunumun gosterebilecegi) -- insert edilmesini zorlamak icin "with check option" kullanilmalidir create view vw_deneme as select * from Color where ColorID <= 5 select * from vw_deneme insert vw_deneme values(100, 'dene') --eklenir --Bunu engellemek icin alter view vw_deneme as select * from Color where ColorID <= 5 with check option insert vw_deneme values(101, 'dene') --eklenemez insert vw_deneme values(0, 'dene') --eklenir --Bir gorunumun tanimin sifrelemek icin 'with encryption' secenegi kullanılabilir alter view vw_deneme --with encryption as select * from Color where ColorID <= 5 with check option --Tum musteri, personel, ve diger kisi ve kurumlarin adini ve adresini --donduren gorunum alter view vw_Bayram as select p.Name, a.AddressLine1 + ' ' + isnull(a.AddressLine2, '') Address, a.City, a.CountryRegion from Personel p join Address a on p.AddressID = a.AddressId union select c.FirstName + ' ' + Lastname, a.AddressLine1 + ' ' + isnull(a.AddressLine2, ''), a.City, a.CountryRegion from Customer c join CustomerAddress ca on c.CustomerID = ca.CustomerID join Address a on ca.AddressID = a.AddressId and ca.AddressType = 'Main Office' --Nesne, hangi nesnelere bagimliyi bulmak icin sp_depends 'vw_Bayram' --Nesneleri rename etmek icin sp_rename 'vw_Bayram', 'vw_Address' create index IX_Product_ProductName on Product (Name) create index IX_Product_ListPrice on Product (ListPrice desc) select * from Product where Name like 'A%' select * from Product order by Name select * from Product order by ListPrice desc --Indexed view --Gorunumler uzerinde index yaratmak gerekirse, gorunum schema bound yapilmalidir alter view vw_Address with schemabinding as select p.Name, a.AddressLine1 + ' ' + isnull(a.AddressLine2, '') Address, a.City, a.CountryRegion from dbo.Personel p join dbo.Address a on p.AddressID = a.AddressId --union --select c.FirstName + ' ' + Lastname, --a.AddressLine1 + ' ' + isnull(a.AddressLine2, ''), --a.City, a.CountryRegion --from dbo.Customer c --join dbo.CustomerAddress ca on c.CustomerID = ca.CustomerID --join dbo.Address a on ca.AddressID = a.AddressId and ca.AddressType = 'Main Office' begin tran alter table Personel drop column Name --bu kolon silinemez, cunku gorunumde kullaniliyor rollback begin tran alter table Personel drop column IkramiyeAdedi --bu kolon silinebilir, cunku gorunumde kullanilmiyor rollback create unique clustered index IX_Address_Name on vw_Address (Name) --Sistem Goronumleri --1- INFORMATION_SCHEMA gorunumleri --ANSI uyumlu RDBMS lerde olması zorunlu gorunumlerdir --Bir database deki tablolari gormek icin --ANSI INFORMATION_SCHEMA gorunumunden select * from INFORMATION_SCHEMA.TABLES --SQL serverdeki sistem tablosunda select * from sysobjects where xtype = 'u' --Sistem gorunumunden select * from sys.Tables --Kolon listesi INFORMATION_SCHEMA gorunumunden select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Address' --Sistem tablosundan select * from syscolumns where id = object_id('Address') --Sistem gorunumunden select * from sys.columns where object_id = object_id('Address') -- Bir view, sp vs tanimini gormek icin sp_helptext 'sys.Tables' --Acik sessionlari gormek icin sp_who --Bir sessionda calisan komutu gormek icin dbcc inputbuffer(54) --Bir sessionu sonlandirmak icin kill 54 --Stored Procedures --Personel tablosuna kayıt eklemek icin sp --Sistem Stored Procedureleri --Sistem sp --Kullanici sp leri --Adları 'sp_' ile baslamamalidir --sp leri begin end icine yazmak zorunlu degildir; --ama yazmak iy bir pratiktir --Tüm ürünlerin ProductID, Name ve Rengini döndüren sp create proc up_UrunRenkGetir as begin Select p.ProductID, p.Name, c.Name from Product p join Color c on p.ColorID = c.ColorID end exec up_UrunRenkGetir --Istenen bir renkteki tüm ürünlerin ProductID, Name döndüren sp create proc up_RengeGoreUrunGetir @Renk varchar(100) as begin Select p.ProductID, p.Name from Product p join Color c on p.ColorID = c.ColorID where c.Name = @Renk end exec up_RengeGoreUrunGetir 'Black' --Parametreli Sp lerde başta verilen parametrenin --hatali olmadigini kontrol etmek iyi bir pratiktir alter proc up_RengeGoreUrunGetir @Renk varchar(100) as begin if (select count(*) from Color where Name = @Renk) = 0 begin Raiserror('Verilen renk hatalidir', 16, 1) return end Select p.ProductID, p.Name from Product p join Color c on p.ColorID = c.ColorID where c.Name = @Renk end exec up_RengeGoreUrunGetir 'Blac' select * into Customer_20090822 from Customer --Adinin tamami veya bir kismi verilen musteriyi silen sp alter proc up_Musterisil @MusteriAd varchar(100) as begin delete dbo.Customer_20090822 where Firstname like '%' + @MusteriAd + '%' end exec up_Musterisil 'a' --sp_rename 'up_Musterisil', 'up_AdaGoreMusteriSil' exec up_AdaGoreMusteriSil 'ben' --Sp ile 1 den fazla musteri silinmemelidir --Bonusu olan musteri silinmemelidir alter proc up_AdaGoreMusteriSil @MusteriAd varchar(100) as begin if (select count(*) from dbo.Customer_20090822 where Firstname like '%' + @MusteriAd + '%' ) > 1 begin Raiserror('Birden fazla müşteri silinemez!', 16, 1) return end if (select Bonus from dbo.Customer_20090822 where Firstname like '%' + @MusteriAd + '%' ) > 0 begin Raiserror('Bonusu olan müşteri silinemez!', 16, 1) return end delete dbo.Customer_20090822 where Firstname like '%' + @MusteriAd + '%' end --Personel ekleyen sp alter proc up_PersonelEkle @Name varchar(100), @AddressID int, @Telephone char(10) = null, @ManagerID int --= 1 as begin if not exists (Select * from Personel Where PersonelID = @ManagerID) begin Raiserror('Yonetici gecersiz!!', 16, 1) return end if not exists (Select * from Address Where AddressID = @AddressID) begin Raiserror('Adres gecersiz!!', 16, 1) return end insert Personel (Name, AddressID, Telephone, ManagerID) values (@Name, @AddressID, @Telephone, @ManagerID) end exec up_PersonelEkle 'Ahmet Gül', 9 exec up_PersonelEkle 'Ahmet Güler', 9, default, 1 exec up_PersonelEkle @AddressID = 9, @Name = 'Ahmet Gülerce', @ManagerID = 1 select * from Personel --Eklenen personelin ID sini dondurelim --en sonda select ile alter proc up_PersonelEkle @Name varchar(100), @AddressID int, @Telephone char(10) = null, @ManagerID int = 1 as begin if not exists (Select * from Personel Where PersonelID = @ManagerID) begin Raiserror('Yonetici gecersiz!!', 16, 1) return end if not exists (Select * from Address Where AddressID = @AddressID) begin Raiserror('Adres gecersiz!!', 16, 1) return end insert Personel (Name, AddressID, Telephone, ManagerID) values (@Name, @AddressID, @Telephone, @ManagerID) select @@identity end exec up_PersonelEkle 'Metin Güler', 9 --output parametresi ile alter proc up_PersonelEkle @PersonelID int out,--put @Name varchar(100), @AddressID int, @Telephone char(10) = null, @ManagerID int = 1 as begin if not exists (Select * from Personel Where PersonelID = @ManagerID) begin Raiserror('Yonetici gecersiz!!', 16, 1) return end if not exists (Select * from Address Where AddressID = @AddressID) begin Raiserror('Adres gecersiz!!', 16, 1) return end insert Personel (Name, AddressID, Telephone, ManagerID) values (@Name, @AddressID, @Telephone, @ManagerID) select @PersonelID = @@identity end declare @PID int exec up_PersonelEkle @PID Out, 'Güven Gül', 9 select @PID --Personel bilgilerini degistiren sp alter proc up_PersonelGuncelle @PersonelID int, @Name varchar(100), @AddressID int, @Telephone char(10), @ManagerID int as begin if not exists (Select * from Personel Where PersonelID = @ManagerID) begin Raiserror('Yonetici gecersiz!!', 16, 1) return end if not exists (Select * from Address Where AddressID = @AddressID) begin Raiserror('Adres gecersiz!!', 16, 1) return end if @PersonelID = @ManagerID begin Raiserror('Kişi kendi yoneticisi olamaz!!', 16, 1) return end update Personel Set Name = @Name, AddressID = @AddressID, Telephone = @Telephone, ManagerID = @ManagerID where PersonelID = @PersonelID end exec up_PersonelGuncelle 26, 'Jeremy Bentham', 11, '1111111', 2 select * from Personel --Birden fazla döviz tipiyle çalışmao için yapi kuralim create table Kur ( Tarih date primary key, Usd decimal(10, 4), Eur decimal(10, 4) ) create proc up_KurEkle @Usd decimal(10, 4), @Eur decimal(10, 4), @Tarih date = null as begin if @Tarih is null set @Tarih = getdate() insert Kur values (@Tarih, @Usd, @Eur) end exec up_KurEkle 1.48, 2.12 exec up_KurEkle 1.492, 2.1234, '20090821' exec up_KurEkle 1.503, 2.14, '20090820' --Müşterilerin toplam alimlarini bu günkü kurdan Euro cinsinden dondurelim --MusteriAdi, Toplam alim tutari --istenen bir tarihteki kura göre tutari donduren sp sini yazalim create proc up_MusteriToplamAlim_Eur @Tarih date as select c.Firstname + ' ' + c.Lastname Name, cast(sum(soh.TotalDue) * (select Eur / Usd from Kur where Tarih = convert(varchar, @Tarih, 112)) as decimal(10, 2)) from SalesOrderHeader soh join Customer c on c.CustomerID = soh.CustomerID group by c.Firstname + ' ' + c.Lastname exec up_MusteriToplamAlim_Eur '20090822' -- create table MusteriToplamAlim ( MusteriAdi varchar(100), ToplamAlim decimal(10, 4) ) --sp çelışmasından dönen sonuc bir tabloya insert edilebilir insert MusteriToplamAlim exec up_MusteriToplamAlim_Eur '20090822' --sp ler eger bir scriptte ilk satirda iseler --basina exec konulmadan da calisabilirler select * from Product up_MusteriToplamAlim_Eur '20090822' --hata select * from Product exec up_MusteriToplamAlim_Eur '20090822' --çalışır --Urun fiyatlarında toplu fiyat degisikligi yapan sp --Oran, ArtisMi(default 1) alter proc up_TopluFiyatDegistir @Oran int, @ArtisMi bit = 1 as begin --if @ArtisMi = 1 -- update Product -- Set ListPrice = ListPrice * (100 + @Oran)/100 --else -- update Product -- Set ListPrice = ListPrice * (100 - @Oran)/100 update Product Set ListPrice = ListPrice * case @ArtisMi when 1 then (100 + cast(@Oran as decimal(10, 4)))/100 else (100 - cast(@Oran as decimal(10, 4)))/100 end end select * from Product exec up_TopluFiyatDegistir 20, 0 select * from Product --Urun kategorisine gore toplu fiyat degistirme sp alter proc up_KategoriyeGoreTopluFiyatDegistir @Oran int, @Kategori varchar(100), @ArtisMi bit = 1 as begin declare @YeniOran decimal(10, 4) if @ArtisMi = 1 set @YeniOran = (100 + cast(@Oran as decimal(10, 4)))/100 else set @YeniOran = (100 - cast(@Oran as decimal(10, 4)))/100 update p Set p.ListPrice = p.ListPrice * @YeniOran from Product p join ProductCategory pc on p.ProductCategoryID = pc.ProductCategoryID where pc.Name = @Kategori end select * from Product where ProductCategoryID in (5,6) exec up_KategoriyeGoreTopluFiyatDegistir 10, 'Mountain Bikes', 1 select * from Product where ProductCategoryID in (5,6) /* Transactions(İşlem) Bütün olarak ele alınması gereken birden fazla işlem parçasından oluşmuş işlemlere transaction denir Bütün olarak ele alınmaktan kasıt transaction i oluşturan işlem parçalarının tamamının başarıyla sonuçlanması durumunda işlemin başarılı kabul edileceği ve yapılan tüm işlem parçalarının kalıcı olarak veritabanına yazılacağı(commit); herhangi bir işlem parçasının başarısız olması durumuna tüm işlem parçalarının geri sarılacağı(rollback) ve hiç bir işlemin veritabanında kalıcı olarak yazılmayacağı anlamındadır. Rollback işleminden sonra veritabanı transaction başladığı andaki durumuna donmus olacaktır. Transaction lar genellikle exception yönetimiylr ele alınırlar ve bir hata oluşması durumunda işlem rollback edilir. Hata oluşmazsa işlem commit ile kalıcı hale getirilir. Ancak exception oluşması dışında kontrol edilmesi gereken durumlar da vardır. Örneğin bir kaydın etkilenmesini beklediğiniz bir durumda işlem sonrası kaç kayıt etkilendiğini kontrol edip bir kayıt dışında ise işlemi rollback etmek gerekebilir. */ /* Exception Handling(Istisna-Hata yönetimi) Çalıştırılan kodda oluşan hataların ele alınmasını sağlayan bir mekanizmadır. Sql Server 2005 öncesinde her işlemden sonra @@Error flagi nin kontrol edilmesiyle yapılıyordu. 2005 ile birlikte hata donmesi muhtemel kod bir try-catch bloguna alınarak kontrol gerceklestirilmektedir Genel yapı aşağıdaki şekildedir: Begin Try . . . End Try Begin Catch . . End Catch Burada try blogunda hata oluşması durumunda işleyiş catch bloguna gecmekte ve oradan devam etmektedir. Hata olusmamsı durumunda try icindeki tum kod çalıştıktan sonra, işleyiş catc blogunu atlayıp, catch sonrasından devam etmektedir. */ --Customer tablosuna Bakiye(Balance) kolonu ekleyelim Alter table Customer Add Balance decimal(18, 4) default (0) not null --Invoice tablosu alusturalim create table Invoice ( InvoiceID int identity(1, 1) not null, CustomerID int not null, InvoiceDate date not null, Total decimal(18, 4) not null, Vat decimal(18, 4) not null, GrantTotal decimal(18, 4) not null, Constraint PK_Invoice Primary Key (InvoiceID), Constraint FK_Invoice_Customer Foreign Key (CustomerID) References Customer (CustomerID) ) -- Invoice e bir satir eklendiginde Customer in --bakiyesini de Total kadar artiralim begin tran Begin try insert into Invoice (CustomerID, InvoiceDate, Total, Vat, GrantTotal) values (1, '20090823', 1000, 180, 1180) update Customer Set Balance = Balance - 1180 where CustomerID = 1 commit End Try Begin Catch rollback End Catch --Musteri bakiyesine, bakiye -2000 den daha küçük olamaz kısıtı koyalım Alter table Customer Add Constraint CK_Balance Check (Balance >= -2000) --Bu durumda yukarıdaki script 2 kez çalıştırılırsa başta Musteri bakiyesi 0 ise --birinci çalıştırma başarılı sonuclanacak, yani fatura eklenip, musteri bakiysi -1180 --yapılacaktır. Ancak 2. çalıştırmada musteri bakiyesi -2000 den kucuk olamayacağı için --İşlem başarısız olup, Fatura da eklenmeyecektir. --Siparisten fatura olustur sp si Alter table SalesOrderHeader Add IsInvoiced bit default (0) not null /* İs kuralları 1- SalesOrderID si alınır 2- IsInvoiced alaninin 0 oldugu test edilir 3- Total = SubTotal + Freight, Vat = TaxAmt 4- Gunun tarihiyle Invoice tablosuna eklenir 5- Customer Balance tutar (-) olarak eklenir 6- SalesOrderHeader IsInvoiced alani 1 yapilir */ create proc up_SiparistenFaturaOlustur @SalesOrderID int as begin declare @CustomerID int declare @GrandTotal decimal(18, 4) declare @Mesaj varchar(1000) if not exists (select * from SalesOrderHeader where SalesOrderID = @SalesOrderID) begin set @Mesaj = cast(@SalesOrderID as varchar) + ' nolu sipariş bulunamadı, işlem yapılamadı!' raiserror(@Mesaj, 16, 1) return end if (select IsInvoiced from SalesOrderHeader where SalesOrderID = @SalesOrderID) <> 0 begin set @Mesaj = cast(@SalesOrderID as varchar) + ' nolu sipariş zaten faturalanmış, işlem yapılamadı!' raiserror(@Mesaj, 16, 1) return end begin tran begin try insert into Invoice (CustomerID, InvoiceDate, Total, Vat, GrantTotal) select CustomerID, getdate(), SubTotal + Freight, TaxAmt, SubTotal + Freight + TaxAmt from SalesOrderHeader where SalesOrderID = @SalesOrderID select @CustomerID = CustomerID, @GrandTotal = SubTotal + Freight + TaxAmt from SalesOrderHeader where SalesOrderID = @SalesOrderID update Customer set Balance = Balance - @GrandTotal where CustomerID = @CustomerID update SalesOrderHeader set IsInvoiced = 1 where SalesOrderID = @SalesOrderID commit end try begin catch raiserror('Fatura oluşturulamadı!', 16, 1) rollback end catch end select CustomerID, Balance from Customer where CustomerID = 106 exec up_SiparistenFaturaOlustur 71776 select CustomerID, Balance from Customer where CustomerID = 106 --Yukarıdaki sp ye Bonus kulanımını ekleyelim. --Sipariş tutarı Bonustan büyükse tum Bonus kullanilmali ve kalan tutar musteri bakiyesine --borc olarak eklenmeidir --Bonus tutarı Sipariş tutarı büyükse Bonus kolonu guncellenmeli --İçinde aradığımız bir sozcuk gecen sql nesnelerini bulmak için bir sp yazalım --Bu sp yi master de oluşturursak adı da 'sp_' ile başlarsa, bu sp serverdeki --tum veritabanlarında, o veritabanı bağlamında çalışacaktır. Çünkü sql server --çalıştırılmak istenen bir prosedür 'sp_' ile başlıyorsa, bu prosedürü önce --içinde bulunulan veritabanında arayacak, orada bulamazsa master e bakıp, bulursa --oradan çalıştıracktır use master go create proc sp_Find @Aranan varchar(8000) as begin select object_name(id) from syscomments where text like '%' + @Aranan + '%' end use AdventureWorksLT go sp_Find 'Product' /* Functions Sistem Fonksiyonları Sql serverde tanımlı bir fonksiyonlardır. */ select @@VERSION select @@SPID select DATENAME(dd, getdate()) --29 select DATENAME(dw, getdate()) --Saturday select DATEDIFF(DD, '19671106', getdate()) SELECT ISDATE('-2009808') SELECT FLOOR(12.12) --12 SELECT CEILING(12.12) --13 SELECT ROUND(312.15499, 2) --13.15 SELECT ROUND(312.12, -2) --300 SELECT RAND() SELECT DB_ID() SELECT DB_ID('Deneme') select * from master.dbo.sysdatabases select replace(replace(replace(replace(replace(replace(upper('şükrü'), 'Ü', 'U'), 'Ş', 'S'), 'Ö', 'O'), 'Ğ', 'G'), 'Ç', 'C'), 'İ', 'I') --Coalesce --İlk null olmayan değeri döndürür Select ProductID, Name, coalesce(DiscontinuedDate, SellEndDate, SellStartDate) from Product select coalesce(null, 1, null, null) --1 select coalesce(null, 1, 2, null) --1 select coalesce(null, null, 2) --2 select coalesce(null, null, null) --UDF(User Defined Functions) --Kullanıcılar tarafından oluşturulan fonksiyonlardır. 2 türü vardır: --1-Scalar fonksiyonlar --Kullanıcı tarafından tanımlanan ve tek bir değer döndüren fonksiyonlardır ALTER function uf_BuyutTurkceKarakterKaldir(@Deger varchar(8000)) returns varchar(8000) as begin return replace(replace(replace(replace(replace(replace(upper(@Deger), 'Ü', 'U'), 'Ş', 'S'), 'Ö', 'O'), 'Ğ', 'G'), 'Ç', 'C'), 'İ', 'I') end SELECT dbo.uf_BuyutTurkceKarakterKaldir('tEŞEKKÜRLEr') SELECT PersonelID, dbo.uf_BuyutTurkceKarakterKaldir(Name), Maas from Personel --ColorID si alıp Color Name i donduren fonksiyon create function uf_RenkGetir(@ID int) returns varchar(100) as begin declare @Renk varchar(100) select @Renk = Name from Color where ColorID = @ID return @Renk end select dbo.uf_RenkGetir(5) --En cok hangi renkten ürünümüz var select top 1 dbo.uf_RenkGetir(ColorID) from Product group by ColorId order by COUNT(*) desc --20090821 tarihinde her bir müşterinin bizden yaptığı --toplam alışveriş o gunun kurundan tl cinsinden donduren sorgu select CustomerID, SUM(TotalDue) * (Select Usd from Kur where Tarih = '20090821') from SalesOrderHeader where convert(varchar, OrderDate, 112) <= '20090821' group by CustomerID --Gunluk USD kurunu donduren fonksiyon create function uf_GunlukDolarKuruGetir (@Tarih datetime) returns decimal(10, 4) as begin return (Select Usd from Kur where Tarih = @Tarih ) end --Fonksiyonla bulalım select CustomerID, SUM(TotalDue) * dbo.uf_GunlukDolarKuruGetir('20090821') from SalesOrderHeader where convert(varchar, OrderDate, 112) <= '20090821' group by CustomerID --Her sozcugun ilk harfini buyuten fonksiyon --Oracle de Initcap fonksiyonu --While kullanımı declare @Sayi int, @i int set @Sayi = 10 set @i = 1 select ('Başlıyor') while (@i <= 10) begin select @i set @i = @i + 1 end select 'Bitti' --'len' bir karakter katarının uzunlugunu bulur select len('Deneme') --6 ALTER function uf_IlkHarfBuyut (@Cumle varchar(8000)) returns varchar(8000) as Begin declare @Sayi int, @i int select @Sayi = len(@Cumle), @i = 2 declare @YeniCumle varchar(8000) set @YeniCumle = Upper(left(@Cumle, 1)) + SUBSTRING(@Cumle, 2, 1) while (@i < @Sayi) begin declare @Karakter char(1) set @Karakter = substring(@Cumle, @i, 1) if (@Karakter = ' ') set @YeniCumle = @YeniCumle + upper(substring(@Cumle, @i + 1, 1)) else set @YeniCumle = @YeniCumle + substring(@Cumle, @i + 1, 1) set @i = @i + 1 end return @YeniCumle End SELECT dbo.uf_IlkHarfBuyut('bir UNICODE karakter') alter function uf_GunlukKurGetir (@Tarih datetime, @DovizCinsi char(3)) returns decimal(10, 4) as begin declare @Kur decimal(10, 4) set @Kur = -1 if @DovizCinsi = 'Usd' Select @Kur = Usd from Kur where Tarih = @Tarih else if @DovizCinsi = 'Eur' Select @Kur = Eur from Kur where Tarih = @Tarih return @Kur end select dbo.uf_GunlukKurGetir('20090829', 'Eur') select dbo.uf_GunlukKurGetir('20090821', 'Er') select dbo.uf_GunlukKurGetir('20090821', 'Eur') --Tum satisları tl, usd ve eur listeleyelim '20090821' kuru alinmalidir select SalesOrderID, CustomerID, OrderDate, cast(TotalDue as decimal(18, 4)) USD, cast(TotalDue * dbo.uf_GunlukKurGetir('20090821', 'Usd') as decimal(18, 4)) TL, cast(TotalDue * dbo.uf_GunlukKurGetir('20090821', 'Usd') / dbo.uf_GunlukKurGetir('20090821', 'Eur') as decimal(18, 4)) Eur from SalesOrderHeader --2- Tablo değerli fonksiyonlar(Table valued udf) --Tablo donduren fonksiyonlardır. 2 türü vardir --a) Inline table valued udf --Tek bir select cumlesinden olusur --Siyah renkli urunleri donduren gorunum GO create view vw_SiyahRenkliUrunlerİi as select * from Product where dbo.uf_RenkGetir(ColorId) = 'Black' GO SELECT 1 select * from vw_SiyahRenkliUrunler create function uf_RengeGoreUrunGetir (@Renk varchar(100)) returns table as --begin end icine yazılamaz return (select * from Product where dbo.uf_RenkGetir(ColorId) = @Renk ) select * from uf_RengeGoreUrunGetir('Black') select * from uf_RengeGoreUrunGetir('Red') --b) multistatement table-valued function --Tablo tanımı fonksiyon return değerinde tanımlanır --fonksiyonun içinde içine veri doldurulurdoldurulur CREATE FUNCTION [dbo].[ufnGetAllCategories]() RETURNS @retCategoryInformation TABLE ( -- Columns returned by the function [ParentProductCategoryName] [nvarchar](50) NULL, [ProductCategoryName] [nvarchar](50) NOT NULL, [ProductCategoryID] [int] NOT NULL ) AS BEGIN WITH CategoryCTE([ParentProductCategoryID], [ProductCategoryID], [Name]) AS ( SELECT [ParentProductCategoryID], [ProductCategoryID], [Name] FROM SalesLT.ProductCategory WHERE ParentProductCategoryID IS NULL UNION ALL SELECT C.[ParentProductCategoryID], C.[ProductCategoryID], C.[Name] FROM SalesLT.ProductCategory AS C INNER JOIN CategoryCTE AS BC ON BC.ProductCategoryID = C.ParentProductCategoryID ) INSERT INTO @retCategoryInformation SELECT PC.[Name] AS [ParentProductCategoryName], CCTE.[Name] as [ProductCategoryName], CCTE.[ProductCategoryID] FROM CategoryCTE AS CCTE JOIN SalesLT.ProductCategory AS PC ON PC.[ProductCategoryID] = CCTE.[ParentProductCategoryID]; RETURN; END; --GO bir scriptin birden fazla scriptmis gibi ayrı ayrı --ele alınmasını sağlar. Örneğin Create komutları bir scriptin --içindeki tek komut olması zorunludur. Eğer scriptimizde --birden fazla create komutu varsa bunları GO ile ayırmamız gerekir --GO ayrıca Sql server in Komut satırından çalışan araçlarında --yazılan komutların çalışmasını sağlar DECLARE @A INT SET @A = 1 GO SELECT @A --Tetikler(Triggers) --Tablo bazında tanımlanır. Tabloda gerçekleşen insert, update ve delete işlemleri için --otomatik çalışan özel bir stored procedure dir. 2 türü vardır: --1- For/After triggerleri --İşlem gerçekleştikten sonra çalışırlar --Color tablosunda yapılan silme işlemlerini loglayan trigger create table SilinenRenkLog ( Tarih Datetime not null, Kullanici varchar(100) not null, SilinenRenkID int not null, SilinenRenkAdi varchar(100) not null ) create trigger trg_SilinenKayitLog on Color for delete as begin insert SilinenRenkLog select Getdate(), suser_sname(), ColorID, Name from deleted end insert Color values (1000, 'Ak') insert Color values (1001, 'Kara') insert Color values (1002, 'Mor') delete Color where ColorID >= 1000 --Color tablosunda yapılan ekleme, guncelleme ve silme --işlemlerini loglayan trigger create table RenkLog ( Tarih Datetime not null, Kullanici varchar(100) not null, Islem varchar(20) not null, RenkID int not null, RenkAdi varchar(100) not null ) create trigger trg_RenkIslemLog on Color for insert, update, delete as begin insert RenkLog select Getdate(), suser_sname(), 'Eklenen', * from inserted insert RenkLog select Getdate(), suser_sname(), 'Silinen', * from deleted end --2- instead of triggerleri --Yapılmak istenen işlemin yerine çalışır alter trigger trg_ins_SilinenKayitLog on Color instead of delete as begin insert RenkLog select Getdate(), suser_sname(), 'Silme teşebbüsü', ColorID, Name from deleted end /*İşlem sırası 1. Instead of trigger çalışır 2. Check constraints çalışır 3. Referential integrity constraints(Foreign Keys) çalışır 4. inserted/deleted tables oluşur 5. insert/update/delete işlemi gerçekleştirilir 6. For/After trigger çalışır */ --Invoice tablosuna kayıt eklendiğinde Musterinin bakiyesini --GrantTotal kadar eksilten trigger /* update TabloAdi set KolonAdi = Deger, KolonAdi = Deger,... from TabloAdi join ... where */ create trigger trg_MusteriBakiyeGuncelle on Invoice after Insert as Begin update c set c.Balance = c.Balance - i.GrandTotal from Customer c join inserted i on c.CustomerID = i.CustomerID End select c.Balance, i.* from Customer c join (select 2 CustomerID, InvoiceDate, Total, Vat, GrandTotal from Invoice) i on c.CustomerID = i.CustomerID
