Yapacağımız sorgular için yarışma adında bir database oluşturuyoruz ve bu database il, Uye, Soruturu, Soru ve İstatistik tablolarından oluşuyor. Birincil ve ikincil anahtarlarımız doğru bir şekilde oluştursak sorgu sırasında hata alma şansımız azalır. Buna dikkat etmek gerekir.
Create Database Yarisma
use Yarisma
Create Table İl
(
İlPNo int Primary Key,
İlAdi varchar(30),
)
Create Table Uye
(
UNo int Primary Key identity (1,1),
UAdi varchar(30)NOT NULL,
UASoyadi varchar(30)NOT NULL,
UBTrh datetime Null,
İlPNo int foreign key references İl(İlPNo),
USifre varchar(30)NOT NULL DEFAULT('123abc'),
GirSay int Default(0),
Sonpuan int
)
Create Table SoruTur
(
STNo int Primary Key identity (1,1),
StAdi varchar(30)NOT NULL,
)
Create Table Soru
(
SNo int Primary Key identity (1,1),
SBaslik varchar(30)NOT NULL,
C1 varchar(2),
C2 varchar(2),
C3 varchar(2),
C4 varchar(2),
C5 varchar(2),
STNo int foreign key references SoruTur(STNo),
SPuan int NOT NULL,
UNo int foreign key references Uye(UNo),
)
Create Table İstatistik
(
UNo int foreign key references Uye(UNo),
AldTrh datetime,
ToplamPuan int,
)
-------------------------------------------------------------------------------
Alter İfadesini Kullanmak
--Tabloya yeni bir sutun ekleme için kullanılır.
alter table Uye add UAdres varchar(30)
-- Tabloda bulunan bir sutunun değişken türünü değiştirir.
alter table uye alter column uadresi int
-- Tabloda buluna bir sutunu siler
alter table Uye drop column UAdres
-------------------------------------------------------------------------------
Drop İfadesini Kullanmak
--Nesne, tablo veya veri tabanını siler.
Drop Database Okul
Drop Table Öğrenci
Truncate Table ogrenci(tablonun tüm içeriğini siler.
)
Delete Table ogrenci(istenilen kayıtları siler
)
delete from Uye where UNo=10 gibi...
------------------------------------------------------------------------------
DML (Veri İşleme Dili)
1)SELECT İfadesi
select * from Uye --> Uye tablosunu listeler.
select UAdi,UASoyadi from Uye --> Uye tablosunda
UAdi,UASoyadi sütunları listelenir.
a)WHERE ifadesi (koşul ifadesi)
select * from Uye where İlPNo<9 -->
İlPNo'su 9 dan küçük olan Uyler listelenir.
b)AND,OR ifadesi
select * from Uye where İlPNo<9 and USifre>=15 -->
İlPNo' su 9'dan küçük ve
USifre 15'e eşit veya büyük olanlar listelenir.
select * from Uye where İlPNo<9 or USifre>=15 -- herhangi bir koşul sağlanırsa listelenir
c) IN, NOT IN ifadesi
-- Fazla şartın bulundugu sorgularda kullanılarak
or yapısından kurtulabiliriz.
select uadi,usoyadi,ilpno from uye where ilpno IN(1,3,4) --> Burada sadece ilpno'su 1,3,4 olan uyelerin uadi,usoyadi,ilpno bilgileri listelenir.
select * from İl where İlPNo NOT IN(1,2,3,4) -->
İlPNo 1,2,3,4 olmayan uyelerin tüm bilgileri listelenir.
d) LİKE ifadesi
-- Kayıt arama esnasında kullanılır.
select * from Uye where UAdi LIKE('____M') --> Beşinci harfi M olan üyeleri listeler
select * from Uye where UAdi LIKE('____a_') --> Beşinci harfi A olan uyeleri listeler.
select * from Uye where UAdi LIKE('%M') --> Son harfi m olan
select * from Uye where UAdi LIKE('M%') --> Baş Harfi M olan ueleri listeler
select * from Uye where UAdi LIKE('%M%') --> İçerisinden M harfi geçen uyeleri listeler
select * from Uye where UAdi LIKE('B____') --> Beş harfli Baş harfi B olan üyeleri listeler.
-------------------------------------------------------------------------------------------------------
2) INSERT İfadesi
-- Tablolara yeni veri girmek için kullanılır.
insert into il values(20,'denizli') --> il tablosuna pilaka no su 20 olan denizli ilini ekledik. Parantez içerisini tabloda kullanılan sutun kadar doldurabiliriz.
insert into il values(11,'Bilecik')
Select * from il --> eklendiğini görmek için il tablosunu listeliyoruz.
--------------------------------------------------------------------------------------------
3)UPDATE ifadesi
-- Tabloda bulunun kayıtları değiştirmek güncellemek için kullanılır.
update il set iladi='ankara' where ilpno=6 --> plakası 6 olan şehrin ismini ankara yapıYoruz. set terimiyle değiştirmek istedİğimiz sÜtun üzerindE işlem yapıyoruz.
------------------------------------------------------------------------------------------------------------
4)DELETE ifadesi
--Tablonun içerisindeki kayıtların tamamını veya belirli koşula uyanları siler.
delete from Soru where SNo=10 -->
SNo' su 10 olan soruyu siler.
delet from Uye where UNO=5 -->
UNO 'su 5 olan üyeyi siler.
------------------------------------------------------------------------------------------------------------
DCL (Veri Kontrol Dili)
1) GRANT ifadesi
--Kullanıcıya çeşitli izinler vermek için kullanılır.
grant all on yarisma to user1 --> user1 kullanıcısına tüm izinleri verir.
grant create table on yarisma to user2 --> user2 kullanıcısına tablo oluşturma izni verir.
2)DENY ifadesi
-- Kulanıcının veritabanı ve veritabanındaki nesleler üzeindeki çeşitli işlemleri yapmasını engellemek için kullanılır.
deny all on yarisma to user1 -->
user1 kullanıcısının tüm izinlerini kapatır.
deny create table on yarisma to user1 -->
user1 kullanıcısından tablo oluşturma yetkisini elinden alırız.
deny insert, delete, update,select on yarisma to user1 --> insert, delete, update ve select işlemlerini
user1 kullanamaz.
3)REVOKE ifadesi
-- Engellenen tüm ifadeleri kaldırır.
revoke all from public
revoke select on yarisma from public -- secim için engellenen yetkileri kaldırır.
-----------------------------------------------------------------------------------------------------------------
TEK TABLO İÇİNDE SORGULAMALAR
1) DISTINCT
--Tekrarlanan satır ve sütünları engeller
Select distinct UAdi From Uye
SORGU SONUCUNU SIRALAMA
--2 ORDER BY>>>> tabloda kayıtların sıralanmasını sağlar.
Select UNo From Uye order by UNo asc -->
asc üyeleri
UNo 'suna göre
küçükten büyüğe sıralar.
Select UNo From Uye order by UNo desc -->
desc üyeleri
UNo 'suna göre büyükten küçüğe sıralar.
BETWEEN, AND yapısı
--Birden fazla şartı sıralar
Select *from Uye where UAdi LIKE 'B%' or UNo between 5 and 7 -- Baş harfi Bile başlayan veya
UNo 'su 5 ile 7 arasında olan üyeleri listeleriz.
AS ifadesi
-- Sütüna takma isim vermek için veya ismini değiştirmek için
Select UAdi AS "üyeadı" from Uye -->
UAdi sütununun adını as ifadesiyle üyeadi olarak değiştirdik.
----- Sütünlar üzerinde aritmetiksel işlemler--------
Select SPuan, Spuan+10 as "name" from Soru --> Burada soru tablosunun
SPuan sütununu ve
SPuan 'a 10 ekleyerek
as ifadesiyle sütunun ismini
name yaparak listeledik.
--------------Sütunların birleştirilmesi--------------
Select UNo, (UAdi +' '+ UASoyadi) as "adsoyad" from Uye -->
UNo ile birlikte
adsoyad İsimli yeni oluşturulan sütunun altında
UAdi ve
UASoyadi birleştirerek
Uye tablosunun istenilen sütunları listelenmiştir.
Koşula bağlı sorgulamalarda where ve order by kullanılacaksa önce where yazılmalı
Select *from Uye where UNo>=3 order by UNo -->
UNo' su 3'e eşit ve büyük olanları küçükten büyüğe sıralar.
------------FONKSİYONLAR-----------
1) SUBSTRİNG() Fonksiyonu
-- Veri kümesi içindeki bilgilerin başlangıç olarak belirtilen pozisyondan başlayarak belirtilen karakter sayısı kadar alınmasını sağlar.
select uno, substring(uadi,2,4) as "yeniad" from uye -->
yeniad sütunu altında
uadi yani üye adının 2. harfinden 4. harfine kadar olan karakterleri listeler.
2 )LOWER(), UPPER() Fonksiyonları
Lower verilerin değeri küçük harfe, upper ise büyük harfe çevirir.
Select UPPER(İlAdi) from İl --> Listelenen il adlarının bütün karakterleri Büyük harf olur.
Select lower(İlAdi) from İl -->Listelenen il adlarının bütün karakterleri küçük harf olur.
3) TRIM() , RTRIM() , LTRIM() Fonksiyonları
-- Boşlukları kaldırır.
select ltrim(uadi) as uadi, usoyadi from uye -->
uye adının solundaki yani başındaki boşlukları siler.
select rtrim(uadi) as uadi, usoyadi from uye -->
uye adının sağındaki yani sonundaki boşlukları siler.
4 LEN() , LENGTH() Fonksiyonları
-- Karakter sayısını bulma
select uadi,len(uadi) as "karakter sayısı" from uye --> Üye adlarının karakter sayılarını
"karakter sayısı" sütunu altında listeler.
5) REPLACE() Fonksiyonları
--Yer değiştirmek için kullanılır.
select uadi,usoyadi, replace(uadi,'sezer','osman') as "lakap" from uye --> sezer ismi osman ismiyle değitirilecek.
6) REVERSE() Fonksiyonları
--Tersten yazdırma işlemleri için kullanılır.
select uadi,reverse(upper (uadi)) from uye -->
uadi' nın karakterlerini büyük harfe çevirip tersten yazdırdık.
7) ABS() Fonksiyonu
Mutlak değer alma işlemi
Select ABS(-1.25) as "mutlak_değer_karşılığı" -->
"mutlak_değer_karşılığı" isimli sütunun altında 1.25 yani sayının mutlak değeri verilecektir.
8) FLOOR(), CEILING(),ROUND() Fonksiyonları
-- Aşağı-Yukarı ve en yakın değere yuvarlama işlemlerini yaparlar.
Select FLOOR(1.256) --> Aşağıya yuvarlar. Yani yeni değer 1 olur.
Select CEILING(1.256)-->Yukarıya yuvarlar. Yani yeni değer 2 olur.
Select round(1.26,1) --> Verilen sayısal ifadeyi yuvarlamak için kullanılır. Yuvarlama işlemi 5 ten küçük değerler için aşağı, 5 ve 5 ten büyük değerler için yukarı yönlü yuvarlama olur. Sonuç olarak yeni değer 1.30 olur.
9) POWER() Fonksiyonu
--Üst alma işlemlerini yapmak için kullanılır.
select power(2,3) --> yani 2 üstü 3 = 8 sonucunu döndürür.
10) SQRT() Fonksiyonu
Karekök almak için kullanılan fonksiyondur.
Select sqrt(4) --> 2 sonucunu döndürür.
11) SUM() Fonksiyonu
Toplam bulma fonksiyonu olarak kullanılır.
select sum(Spuan) from soru -- Soru tablosundaki listelenen
Spuan'ların
toplamını döndürür.
12) AVG() Fonksiyonu
Ortalama değer hesaplama fonksiyonu olarak kullanılır.
Select round(AVG(Spuan),1) from Soru --> ortalaması alınan
Spuan'ların yuvarlama işleminden sonra listele.
13) Max(),Min() Fonksiyonları
Select MAX(Spuan) from Soru -->En yüksek
Spuan listeler.
Select MIN(Spuan) from Soru -->En düşük
Spuan'ı listeler.
14) Count() Fonksiyonu
-- Satır sayısını bulur.
Select count(Spuan) from soru --> Alt alta kaç satır olduğunu dönderir.
15) GETDATE
--Güncel saat bilgisini verir.
select GETDATE()
select DATEPART(YEAR,GETDATE())
select DATEPART(DAY,GETDATE())
select DATEPART(HOUR,GETDATE())
select DATEPART(MINUTE,GETDATE())
1) GROUP BY
GRUPLANDIRMA İŞLEMİNDE KULLANILAN FONKSİYONLAR
a) AVG()
--Ortalamaya göre gruplandırma.
select UNo,AVG(SPuan) as ort from Soru where UNo>=3 Group By UNo order by UNo desc
b) Count()
Grupların içerdikleri satır sayısını verir.
select UNo,Count(SPuan) as satirsayisi from Soru Group By UNo
select UAdi,Count(UAdi) as satirsayisi from Uye Group By UAdi
c) Max()
Gruptaki sütünün maxını bulur.
select UNo ,MAX(SPuan) as maxı from Soru Group By UNo
d) Min()
Gruptaki sütünün minimumunu bulur.
select UNo ,MIN(SPuan) as mini from Soru Group By UNo
---------Birden fazla sütuna göre gruplandırma---------
select SBaslik,UNo ,MAX(SPuan) as maxı,MIN(SPuan) as mini from Soru Group By UNo,SBaslik
Gruplandırma koşul ifadeleri (Having )
HAVING Group By dan sonra order by dan önce kullanılır.
select SBaslik ,(max(SPuan) -min(SPuan)) as Fark from Soru Group By SBaslik Having (max(SPuan) -MIN(SPuan))>5
------------------------Birden Fazla Tablo Üzerinde Sorgulama--------------------
A) Tablolara Takma İsim Kullanma
select distinct uyem.UAdi,sorum.SBaslik from uye uyem,soru sorum where uyem.UNo=sorum.UNo
select distinct Uye.UNo,Soru.SNo,Soru.C1 from Uye ,Soru where Uye.Uno=Soru.UNo and soru.C1='a' and Uye.UNo in (select Uno from Soru)
Trigger Nedir?
Veritabanı tablolarında bazı olaylar meydana geldiğinde çalışan küçük kod parçalarına trigger denir. Bu olaylardan daha önce DML konusunda bahsetmiştik. Listeleyecek olursak bu olaylar aşağıdakilerdir:
Trigger oluşturmak:
CREATE TRIGGER trigger_adi
ON tablo_adi
AFTER veya INSTEAD OF (INSERT veya UPDATE veya DELETE)
AS
Sql ifadeler
Trigger enable/disable yapmak:
disable trigger trigger_adı On tablo_adı
enable trigger trigger_adı On tablo_adı
Delete Trigger
Burada kullanicilar tablosundaki silinen verilerimizi yeni bir tabloya yani silinenkullanicilar tablosuna trigger yardımıyla ekledik.
use deneme
create trigger tr_silinenkullanici on kullanicilar
After delete
As
Begin
Declare @ID int
Declare @kadi nvarchar(50)
Declare @sifre nvarchar(50)
Declare @adi varchar(50)
Declare @soyad nvarchar(50)
Declare @yetki nvarchar(50)
Select @ID=kId from deleted
Select @kadi=k_adi from deleted
Select @sifre=sifre from deleted
Select @Adi=kadi from deleted
Select @soyad=ksoyadi from deleted
Select @yetki=yetki from deleted
Insert into silinenkullanicilar values(@ID,@kadi,@sifre,@adi,@soyad,@yetki)
End
Insert Trigger
Tablo üzerindeki kayıt ya da kayıtlarda güncelleme olduğunda devreye girecek olan triggerdir. INSERT ve DELETE triggerden biraz farklıdır. Farkı ise UPDATE trigger devreye girdiğinde Inserted sahte tablosu asıl tablodaki kayıtlardan, düzenlenmiş kayıtların kopyasını, Deleted sahte tablosu ise kayıtların düzenleme işleminden önceki hallerini tutar.
satisyap tablosuna veri eklediğimiz zaman yani insert ile ürün eklendiği zaman
devreye girecek triggerımız stok tablosundaki verilerimizin sayısını azlatacaktır.
create trigger tr_stokazaltir on satisyap
after insert
as
begin
declare @urunId int
declare @satinaladet int
declare @satiurun int
select @urunId=satisurun, @satinaladet=satisadet from inserted
update urunler set adet=adet-@satinaladet
where urunId=@urunId
end
