Yerel Değişkenler :
@@SERVERNAME
Sunucumuzu adı
@@LANGUAGE
kullanılan dil
@@VERSION
Kullandığımız SQL Server’ın sürüm bilgisi
@@IDENTITY
Kimlik sütunu için veritabanına girilen değer
@@ROWCOUNT
satır sayısı
SQL Server’da değişkenler DECLAERE ifadesi kullanma
” DECLARE @değişken_adı veri_türü ” ifadesi ile tanımlanır. Aralara virgül koyarsak birden fazla değişkeni tek aynı anda tek bir DECLARE ifadesi ile kullanbiliriz.
Bir değişken oluşturulduğunda NULL dur . Değişkenlere değer atamanın SET ifadesi değer atanabilir.
SET ifadesi kullanılarak değişkene değer atama
set @degisken=@deger
şeklinde kullanılabilir.
–değişkenler
—-tek @ işareti bu local variable = local değişken , @@ = global
—- # temp (geçici) = local, ## = global
declare @name varchar(10), @lastname varchar (10)
select @name = firstName , @lastname = lastname
from employees
where employeeID = 1
select @name , @lastname
select *
from employees
where employeeID = 1
set @name ‘hasan’
— =
select @name = ‘hasan’
set @name = (select firstname from employees where employeeID = 1)
set @lastname = (select lastname from employees where employeeID = 1) — set le sadece tek değer atanır
declare @sayı int
select @sayı = 2
select @sayı
declare @sayı int
select @sayı = employeeID from employees where firstname = ‘nancy’
select @sayı
declare @sayi int
set @sayi = (select employeeID from employees where firstname = ‘nancy’)
select @sayi
select top 2 *
from [order details]
declare @productID int
set @productID = 111
select sum (Quantity) as ‘toplam satıs miktarı’
from [order details]
where productID = @productID
declare @productID int
set @productID = 111
if exists (select top 1 productID from [order details] where productID = @productID)
select sum (Quantity) as ‘toplam satıs miktarı’ from [order details] where productID = @productID
else
select ‘productID=’ + cast (@productID as varchar (10)) + ‘ ‘ +’olan ürün yok’
declare @productID int , @total int
set @productID = 11
if exists (select top 1 productID from [order details] where productID = @productID)
begin
select @total =sum (Quantity) from [order details] where productID = @productID
select ‘ürün numarası = ‘ + cast (@productID as varchar (10)) + ‘ satıs adedi = ‘ + cast (@total as varchar (10))
end
else
select ‘productID=’ + cast (@productID as varchar (10)) + ‘ ‘ +’olan ürün yok’
—- cast (value as type)
select cast (10 as varchar (10))
select cast (’10’ as int)
— convert (data type , value)
select convert (varchar(10),10)
— data type larini değiştirir
select top 5 * from employees
select firstname , lastname ,
case title when ‘ Sales Manager’ then ‘satis temsilcisi’
when ‘Sales Representative’ then ‘satıs sorumlusu’
else ‘saticilar’ end as ‘gorev tanimi’
from employees
select top 2 * from [order details]
select top 2 * from orders
select freight ,
case when freight between 0 and 400 then ‘iyi’
when freight between 400 and 800 then ‘cok iyi’
else ‘harika’
end ‘durum’
from orders
order by ‘durum’
select top 2 * from products
select unitsInstock ,
case when unitsInstock >= 50 then ‘depoda cok var’
when unitsInstock between 10 and 50 then ‘az kaldı’
when unitsInstock <= 50 then ‘bitti’ else ‘bitiyor’ end ‘stok durumu’ ,
unitprice,
case when unitPrice >=50 then ‘pahalı’
when unitPrice between 20 and 50 then ‘orta’ else ‘ucuz’ end ‘fiyat durumu’
from products order by ‘stok durumu’
declare @son int , @toplam int
set @toplam = 0
set @son = 100
while @son > 0
begin
set @toplam = @toplam + @son
set @son =@son – 1
end
select
cast (@toplam as varchar(10))
select * from employees
select left(firstname,1) + ‘.’ + lastname as ’employeename’
from employees
left– soldan okuma
right– sağdan okuma
upper–büyük harflere çevirir
lower–küçük harfler
len–uzunluğu verir
substring–bir yerden itibaren okumak için
select left(firstname,1) as ‘left’,right(lastname,3)as ‘right’,
upper(firstname)as’upper’,lower(lastname)as’lower’,len (firstname) as ‘len’,
substring(title,15)as ‘substring’
from employees
206–>kamu telefonu
71–>özel telefon
select homephone from employees
select homephone,
case when left(homephone,5)="(206)’ then’kamu telefonu’
when left(homephone,4)="(71)’ then ‘özel telefon’ else ‘bilinmiyor’
end
from employees
select
print
declare @sayi int
set @sayi=10
print @sayi
–select @sayi
declare @i int
set @i=10248
while @i<5000
begin
select @i,freight from orders
where orderID=@İ +1
set @i=@i+1
end
select*from orders
select top 1 * from orders
select top 1 * from orders order by 1desc
declare @start int,@end int
set @start=(select top 1 orderID from orders)
set @end = (select top 1 orderID from orders order by 1 desc)
while @start < @end
begin
select @start ‘startValue’, orderID , freight
from orders
where orderID = @start
set @start = @start + 3
end
–select @start = ‘start’ , @end ‘end’
continue
break
declare @ID int
–select * from orders where orderID between 10250 and 10255
set (select top 1 orderID from orders)
while (@ID > 0)
begin
set @ID = @ID + 1
if (@ID > 10250 and @ID < 10255)
continue
if (@ID = 10260)
break
select orderID from orders where orderID = @ID
end
select convert (varchar(10),orderDate,105) as ‘NewOrderdate’
from orders
select datepart (day,orderDate) as ‘Day’,datepart (month,orderDate) as ‘month’ ,
datename (month,orderDate) as ‘month Name’,
case datepart (month,orderDate)
when 1 then ‘ocak’
when 2 then ‘subat’
when 3 then ‘mart’
when 4 then ‘nisan’
when 5 then ‘mayıs’
when 6 then ‘haziran’
when 7 then ‘temmuz’
when 8 then’ağustos’
when 9 then ‘eylul’
when 10 then ‘ekim’
when 11 then ‘kasım’
when 12 then ‘aralık’ else ‘bilinmiyor’ end ‘ay adı’,
datepart (year,orderDate) as ‘year’ , datepart (dy ,orderDate) as ‘day of year’ ,
datepart (week ,orderDate) as ‘week’ , datepart (weekday,orderDate) as ‘week day’ ,
datename (weekday,orderDate) as ‘weekdayname’ , case datepart (weekday , orderdate)
when 1 then ‘pazartesi’
when 2then ‘salı’
when 3 then ‘çarşamba’
when 4 then ‘perşembe’
when 5 then ‘cuma’
when 6 then ‘cumartesi’
when 7 then ‘pazar’else ‘bilinmiyor’ end ‘gun adı’
from orders
set datefirst 1
select getdate () –gunun tarihini getirir
select dateadd (month, 3 , getdate()) —tarih eklemek yada çıkarmak
select datename (weekday, dateadd (month, -3 , getdate()))
select datediff(day,’2005-12-03′, ‘2006-10-03′),
datediff(month,’2005-12-03’, ‘2006-10-03′),
datediff(year,’2005-12-03’, ‘2006-10-03′),
datediff(week ,’2005-12-03’, ‘2006-10-03’)
select* from orders
select datediff(day, orderdate,requireddate) as ‘teslimat süresi’
from orders
insert into ogrenciler
(ad, soyad) values (‘aylinn’ , ‘aaydinn’)
insert into ogrenciler
values (‘aaaa’, ‘bbbbb’,null)
update ogrenciler
set ad = ‘AYLIN’
where ad = ‘aylin’
update ogrenciler
set ad =upper(ad)
delete from ogrenciler — kalan ıdentityden dewam eder
select * from ogrenciler
truncate table ogrenciler
sql değişkenler
