19 Şubat 2016 Cuma

sql değişkenler

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

Hiç yorum yok:

Yorum Gönder