Transcript Access 2013

Access 2013
Sorgu Oluşturma ve SQL
Temel Bilgi Teknolojileri 2
Sorgu Oluşturma
• OLUŞTUR sekmesinin Sorgular grubunda iki seçenek
vardır:
• Sorgu Sihirbazı: Kullanıcının seçtiği tablolardaki verileri
istediği türde (basit, çapraz, yinelenenleri bulma,
eşleşmeyenleri bulma) ve istediği şekilde görebilmesi
için kolaylık sağlayan bir sihirbazı başlatır.
• Sorgu Tasarımı: Kullanıcının önce ilgili tabloları seçip,
sonrasında o tablolardan hangi alanlarının
görüntüleneceğini, tablonun hangi alanına göre sıralı
olarak görüntüleneceğini ve verilen ölçüte göre hangi
satırların görüntüleneceğini seçebildiği görünümdür.
Sorgu Sihirbazı Kullanımı
• Sihirbazda ilk seçenek (Basit Sorgu Sihirbazı) seçili
durumda iken Tamam tıklayarak devam edelim:
• Sonraki adımda bir yada daha çok tablodan sorguda
görmek istediğimiz alanları Seçili Alanlar listesine
ekleyerek İleri tıklayarak devam edelim:
• Örneğimizde Öğrenciler tablosundan Adı ve Soyadı
alanlarını, Dersler tablosundan Ders Adı alanını ve
Notlar tablosundan da Final alanını seçtik.
• Bu adımda Ayrıntılı seçili iken İleri tıklarsak sorgunun
ismini belirleyebileceğimiz en son adıma geçilir, Son
tıklarsak sorguya otomatik olarak bir isim verilir.
• Önceki adımda aktif halde olan Son düğmesi tıklansaydı Ayrıntılı
seçeneği seçilmiş gibi sihirbaz tamamlanırdı. Bu ekranda aktif
halde olan Geri düğmesi ile de önceki ekrana dönülebilir.
Öğrenciler Tablosu
Dersler Tablosu
Notlar Tablosu
Sorgu Sonucu
• Eğer sadece Dersler tablosundan Ders Adı alanını ve
Notlar tablosundan Final alanını seçip, sonraki
adımda Özet seçeneğini seçince aktif hale gelen
Özet Seçenekleri düğmesini tıklasaydık:
• Açılan pencerede her ders için notların toplamı,
ortalaması, en küçük ve en büyük değerlerden hangisini
görmek istiyorsak seçebiliriz:
• Eğer her dersten tüm öğrencilerin aldığı Final
notlarının ortalamasını değil de, her öğrencinin
aldığı tüm derslerden final notlarının ortalamasını
görmek (öğrenci bazında gruplandırma) isteseydik:
• Öğrenciler tablosundan Adı ve Soyadı alanlarını ve
Notlar tablosundan da Final alanını seçerek Özet
Seçenekleri içinden yine Ort seçimi ile aşağıdaki
sonucu elde ederdik:
Çapraz Sorgu
• Sihirbazda ikinci seçenek (Çapraz Sorgu Sihirbazı)
seçerek devam edelim:
• Notlar tablosunu seçerek İleri ile devam edelim:
• Satır başlığı olarak sadece ÖğrenciNo alanını
seçerek İleri ile devam edelim:
• Sütun başlığı olarak sadece DersKodu alanını
seçerek İleri ile devam edelim:
• Hesaplanacak alan için Final ve fonksiyon olarak ta
Ort seçerek İleri ile devam edelim:
Bu seçimi
kaldıralım
Notlar Tablosu
Çapraz Sorgunun Sonucu
İlk derste yaptığımız gibi
Notlar tablosunda DersYılı
adında bir alan olsaydı,
bir dersten kalan
öğrencinin hem şimdiki
hem de önceki yıl için
final notları saklanabilirdi.
Çapraz sorgumuzda Ort
fonksiyonunu seçtiğimiz
için öğrencinin o dersten
aldığı iki final notunun
ortalaması hesaplanıp
görüntülenirdi (Şu anda
tek bir notun ortalaması
hesaplandığı için o notun
aynısı görüntülenmiş
oluyor).
Sorgu Tasarımı Kullanımı
• Sorgu Tasarımı simgesi
tıklandığında İlişkiler
tıkladığımızda karşımıza gelen
Tabloyu Göster penceresi gelir
• İlişkilerdeki gösterimden farklı
olarak tablolarda * alanı da
vardır. Bu aslında tüm alanların
seçimi için kullanılan simgedir.
Eğer 3 tablo da
eklendikten sonra Alan
olarak Ders Adı, sıralama
olarak Artan seçilip Veri
Sayfası Görünümü veya
Çalıştır tıklanırsa, ders
isimlerinin sıralı olarak
listelendiği fakat bazı
derslerin tekrar ettiği
görülebilir.
Ara tablo olan Notlar
tablosunda hangi ders kaç
defa tekrar ediyorsa bu
sorgunun sonucunda da o
sayıda tekrar eder (Türk
Dili 1 dersi ile ilgili Notlar
tablosunda tek kayıt yer
aldığı için tekrar etmiyor).
Eğer sadece Dersler tablosu
sorgu tasarımına eklenmiş
olsaydı Notlar sorguya dahil
olmadığı için herhangi bir
dersin tekrar ettiğini
görmeyecektik.
Bu defa Notlar tablosunda
yer almayan (o ders ile ilgili
hiç not girişi yapılmamış)
dersler de görüntülenecekti.
Ölçüt kısmı tüm satırlar
yerine sadece verilen
ölçüte uyan satırların
görüntülenmesi için
kullanılır.
Örnek:
Yandaki
sorgu
tasarımının
çıktısı ne
olur?
SQL Görünümü
• GİRİŞ sekmesindeki Görünümlerde
Sorgular için SQL Görünümü adında
farklı bir görünüm de yer almaktadır.
• SQL (Structured Query Language) İlişkisel Model
kullanan veritabanı yönetim sistemlerinde
kullanılan sorgulama dilidir.
• Sorgu Sihirbazı veya Sorgu Tasarımı ile yarattığımız
sorgular aslında arkaplanda SQL dilindeki karşılıkları
oluşturularak saklanır. SQL Görünümü ile istediğimiz
zaman bu sorguların kodunu görebiliriz ve bu dili
biliyorsak kod üzerinden değişiklik yapabiliriz.
SQL
• SQL dilini öğrenmek bir programlama dili öğrenmeye
göre çok daha kolaydır. Çünkü programlama dillerindeki
gibi işlemin “nasıl yapılacağı” değil, işlemde “ne
yapılacağı” yazılır.
• Bu dili öğrenmenin avantajı günümüzde en çok
kullanılan VTYS türü olan İVTYS (İlişkisel Veri Tabanı
Yönetim Sistemi) üzerinde standart bir dil olmasıdır.
• MS SQL Server, Oracle, IBM DB2 gibi birçok İVTYS aslında
Access'e göre çok daha gelişmiş özelliklere sahip olsalar da,
Access'teki gibi bir Sorgu Tasarımı veya Sorgu Sihirbazı
içermezler. Ama hepsi SQL dili ile sorgu yazılmasına izin verir.
• Bu dil birçok komut içerse de bu derste sadece seçme
sorgularında kullanılan SELECT komutu öğretilecektir.
Örneklerde kullanılan
“Personel Bilgileri” Veri Tabanı
• PERSONEL Tablosu:
•
•
•
•
•
•
•
•
PERSONEL_NO
AD
SOYAD
GOREV
MAAS
BOLUM
YONETICISI
GIRIS_TARIHI
• BOLUMLER Tablosu:
• BOLUM_NO
• BOLUM_ADI
• BOLUM_YER
Örneklerde kullanılan
“Öğrenci İşleri” Veri Tabanı
• OGRENCILER Tablosu:
•
•
•
•
•
•
OGR_NO
AD
SOYAD
DOGUM_TARIHI
DOGUM_YERI
ADRES
• DERSLER Tablosu:
•
•
•
•
DERS_KODU
DERS_ADI
DERS_KREDISI
HOCASI
• NOTLAR Tablosu:
•
•
•
•
•
•
OGRENCI
DERS
DERS_YILI
VIZE1
VIZE2
FINAL
• HOCALAR Tablosu:
•
•
•
•
HOCA_NO
AD
SOYAD
UNVAN
SELECT komutu
Bu işlemlerden bahsedilmeyecek
sadece seçme sorgusu olan SELECT
anlatılacak
SELECT sütun [yada sütunlar]
FROM tablo [yada tablolar]
WHERE seçim kriteri
• SELECT ifadesinden sonra * kullanılırsa tüm
nitelikler (sütunlar) seçilir.
• Kriter verilmezse “WHERE” sözcüğü de yazılmaz. Bu
durumda tüm kayıtlar (satırlar) seçilir.
• Personel tablosundaki tüm kayıtların tüm nitelikleri
aşağıdaki SQL cümlesi ile gösterilir:
SELECT * FROM PERSONEL
WHERE ile kriter verme
• Eğer tüm kayıtların değil de sadece belirli
kayıtların görüntülenmesi istenirse WHERE ile
kriter verilir.
• Aşağıdaki SQL cümlesi maaşı 1800 TL’nin
üzerinde olan personelin adı ve soyadını
ekranda gösterir:
SELECT AD, SOYAD
FROM PERSONEL
WHERE MAAS > 1800
Kriterlerde kullanılan işleçler
• Programlama dillerinde kullanılan aritmetiksel
karşılaştırma işleçleri (<, <=, >, >=, =, <>) ve
mantıksal işleçler (AND, OR, NOT) SQL dilinde
de kriter verirken kullanılır.
• Aşağıdaki SQL cümlesi görevi müdür olan ve
maaşı 5000 TL’den fazla olan personeli
gösterir:
SELECT * FROM PERSONEL
WHERE GOREV = ‘Müdür’ AND MAAS > 5000
Programlama dillerinde olduğu gibi SQL’de de karakter türü veriler ile işlem yapılacaksa
tek tırnak yada çift tırnak kullanılır.
Karakter türü verilerin karşılaştırılması
• Karakter türü veriler ile de büyüklük-küçüklük
kıyaslamaları yapılabilir.
• Aşağıdaki SQL cümlesi adı N-Z arasında bir harf ile
başlayan personeli gösterir:
SELECT * FROM PERSONEL WHERE AD > ‘N’
• Aşağıdaki SQL cümlesi adı E harfi ile başlayan
personeli gösterir:
SELECT * FROM PERSONEL
WHERE AD > ‘E’ AND AD < ‘F’
LIKE işleci
• Belirli bir karakter katarını barındıran verileri
aramak için LIKE kullanılır.
• Önceki slaytta yer alan, adı E harfi ile başlayan
personeli gösteren sorgu LIKE ile de yazılabilir:
SELECT * FROM PERSONEL WHERE AD LIKE ‘E*’
• Adresler şehir adı ile bitiyorsa, Edirne ilinde
ikamet eden öğrencileri görmek için aşağıdaki
sorgu kullanılabilir:
SELECT * FROM OGRENCILER
WHERE ADRES LIKE ‘*Edirne’
BETWEEN … AND … işleci
• İki değer arasında karşılaştırma yapmak için Between …
And … işleci (… ile … arasında) kullanılabilir.
• Aşağıdaki SQL cümlesi maaşı 1000 ile 2000 TL arasında
olan işçileri görüntüler:
SELECT * FROM PERSONEL
WHERE MAAS BETWEEN 1000 AND 2000
AND GOREVI = ‘İşçi’
• Bu sorgu Between işleci kullanılmadan da yazılabilirdi:
SELECT * FROM PERSONEL
WHERE MAAS >= 1000 AND MAAS <= 2000
AND GOREVI = ‘İşçi’
IN işleci
• Bir listedeki değerler ile karşılaştırma yapmak
için IN işleci kullanılır.
• 1000 ile 2000 TL arasında değil de sadece
1000, 1500 ve 2000 TL maaş alanları
listelemek için aşağıdaki SQL cümlesi
kullanılabilir:
SELECT * FROM PERSONEL WHERE MAAS IN
(1000, 1500, 2000)
Tarihsel türü verilerin karşılaştırılması
• Belirli bir tarihe eşit olan veya o tarihten
büyük yada küçük olan verilerin aranması
istenirse, tarih ay/gün/yıl biçiminde ve #
karakterleri arasında yazılmalıdır.
• 1989 doğumlu öğrenciler:
SELECT * FROM OGRENCILER
WHERE DOGUM_TARIHI BETWEEN
#1/1/1989# AND #12/31/1989#
DISTINCT ifadesi
• Eğer tablonun bir alanında yer alan veriler
içinde aynı olan veriler varsa SELECT
ifadesinden sonra kullanılan DISTINCT ile bu
tekrar eden verilerin sadece 1 defa
görüntülenmesi sağlanabilir.
• Aşağıdaki SQL cümlesi farklı kayıtlardaki aynı
adları her kayıt için tekrar göstermek yerine 1
defa gösterilmesini sağlar:
SELECT DISTINCT AD FROM PERSONEL
AS ifadesi ve sütun içeriklerini
birleştirme
• Sütunların kendi ismi yerine AS ifadesi ile takma
isim almaları sağlanabilir.
• İki yada daha fazla sayıda sütunun içeriğini
birleştirmek için sütun isimleri arasında Access’te
&, Oracle’da ise || işleçleri kullanılır.
• Aşağıdaki SQL cümlesi AD ve SOYAD sütunlarının
içeriklerini araya bir boşluk karakteri ekleyerek
birleştirir ve ISIM adlı bir sütun şeklinde gösterir.
SELECT AD & ' ' & SOYAD AS ISIM FROM OGRENCILER
Matematiksel İşlemler
• SELECT ifadesinden sonra bir sütunun bir
matematiksel işleme tabi tutulması ve bu işlemin
sonucunun gösterilmesi sağlanabilir.
• Personelin yıllık maaşlarının görüntülenmesi:
SELECT AD, SOYAD, MAAS * 12 AS YILLIK_MAAS
FROM PERSONEL
• Öğrencilerin başarı notunun görüntülenmesi:
SELECT VIZE1 * 0.3 + VIZE2 * 0.3 + FINAL * 0.4 AS
BASARI_NOTU FROM OGRENCILER
NULL (boş) değerler ile ilgili işlemler
• Eğer bir kayıt, bazı alanları boş bırakılarak
eklendiyse, matematiksel işlemlerde sorun
çıkabilir (NULL, sıfır değeri ile aynı değildir).
1500 * 12 + 0 işleminin sonucu 1800 iken,
1500 * 12 + NULL işleminin sonucu NULL olacaktır.
• Karşılaştırma işlemlerinde de NULL ile = işleci
kullanılmaz, IS kullanılır. Final notu girilmemiş
öğrenciler:
SELECT * FROM NOTLAR WHERE FINAL IS NULL
FINAL IS NOT NULL kullanılsaydı notu girilen öğrenciler görüntülenirdi.
Büyük/Küçük Harf Duyarlılığı
• SQL dili büyük/küçük harf ayrımı yapmaz
• Aşağıdaki yazımların hepsi aynı işi yapar
(Personel tablosundaki tüm personelin sadece
adı ve soyadı görüntülenir):
– SELECT AD, SOYAD FROM PERSONEL
– SELECT Ad, Soyad FROM Personel
– Select Ad, Soyad From Personel
– select ad, soyad from personel
Türkçe karakter kullanma
• Birçok VTYS, tablo ve nitelik isimlerinde Türkçe
karakter kullanımına izin verir. Fakat
sorgularda bazı sıkıntılara neden olabileceği
için kullanılması tavsiye edilmez.
– Örneğin PERSONEL tablosundaki nitelik isimleri
ADI ve SOYADI şeklinde büyük harfler ile verildiyse,
bazı VTYS’ler “SELECT Adı, Soyadı FROM Personel”
ifadesini, bazıları ise “SELECT Adi, Soyadi FROM
Personel” ifadesini (İngilizcede 'I' harfinin küçük
hali 'i' olduğu için) doğru kabul eder.
Alan ismi belirlerken dikkat edilmesi
gereken noktalar:
• Öğrencilerin not bilgilerini saklamak için “NOT” isminde bir
alan yaratılmamalıdır. İngilizcede “değil” anlamına geldiği ve
SQL'de kullanılan bir ifade olduğu için “NOT” yerine
“NOTU”, “VIZE”, “FINAL” gibi ifadeler tercih edilmelidir:
• Benzer şekilde SQL’de kullanılan AND, GROUP, ORDER, …
gibi ifadeler de alan ismi olarak kullanılmamalıdır.
• Alan isimlerinde boşluk karakteri kullanılırsa sorgularda o
alan ismini köşeli parantez içinde yazmak gerekeceği için alt
çizgi (_) kullanmak yada boşluksuz yazmak daha kullanışlı
olacaktır.
SELECT DersKodu FROM DERSLER;
SELECT Ders_Kodu FROM DERSLER;
SELECT [Ders Kodu] FROM DERSLER;
ORDER BY ile sıralama
• Eğer görüntülenecek olan kayıtların belirli bir
sütuna göre sıralı olarak görüntülenmesi
isteniyorsa ORDER BY kullanılır.
• Sıralama yukarıdan aşağıya doğru artan sırada
olacaksa ASC, azalan sırada olacaksa DESC
kullanılır. Varsayılan sıralama şekli artan olduğu
için ASC yazılmasa bile artan sıralama kullanılmış
olur.
• Aşağıdaki SQL cümlesi PERSONEL tablosundaki
kayıtları maaşa göre azalan sırada gösterir:
SELECT * FROM PERSONEL ORDER BY MAAS DESC
SQL Fonksiyonları
• Programlama dillerinde olduğu gibi, SQL’de de
bazı aritmetik işlemler için yada tip dönüşümü
yapmak için hazır olarak sunulan fonksiyonlar
mevcuttur.
• Bu fonksiyonların bazıları (SUM, AVG, MIN,
MAX, …) birçok kayıt üzerinde işlem yapıp tek
bir sonuç üretirken, bazıları ise (Örneğin bir
tarihsel değerinin yılını veren YEAR) üzerinde
işlem yaptığı her kayıt için ayrı sonuç üretir.
1989 doğumlu öğrencileri YEAR fonksiyonu ile bulma:
SELECT * FROM OGRENCILER WHERE YEAR(DOGUM_TARIHI) = 1989
SUM ve AVG
• Belirli bir sütundaki sayısal verilerin toplanarak
sonucun gösterilmesi istenirse SUM, aritmetik
ortalamasının gösterilmesi istenirse AVG kullanılır.
• Aşağıdaki SQL cümlelerinden ilki tüm personelin
maaşlarının toplamını, ikincisi ise maaşların
aritmetik ortalamasını gösterir:
1. SELECT SUM(MAAS) AS TOPLAM_MAAS FROM
PERSONEL
2. SELECT AVG(MAAS) AS [MAASLARIN ORTALAMASI]
FROM PERSONEL
NOT: _ karakteri yerine boşluk karakteri
kullanılması istenirse [] içinde yazılmalıdır.
MIN ve MAX
• Belirli bir sütundaki en büyük veriyi görüntülemek
için MAX, en küçük veriyi görüntülemek için ise
MIN fonksiyonları kullanılır.
• Aşağıdaki SQL cümlesi, “2013-2014” öğretim
yılında “BİL118” dersinden en yüksek başarı
notunu gösterir:
SELECT MAX(VIZE1 * 0.3 + VIZE2 * 0.3 + FINAL * 0.4)
FROM NOTLAR
WHERE DERS = "BİL118" AND DERS_YILI = "2013-2014"
COUNT
• Sorgunun ürettiği satır sayısını döndürür.
• Aşağıdaki SQL cümlesi PERSONEL tablosundaki
toplam kayıt sayısını döndürür:
SELECT COUNT(*) FROM PERSONEL
• Eğer COUNT içinde * yerine belirli bir sütun
ismi verilirse o sütundaki NULL olmayan değer
sayısını döndürür:
SELECT COUNT(ADRES) FROM OGRENCILER
GROUP BY
• SUM, AVG gibi bazı fonksiyonların tablonun
tamamı için değil de, belirli bir alana (yada
alanlara) göre gruplandırılarak çalıştırılması
GROUP BY deyimi ile sağlanabilir.
• Aşağıdaki SQL cümlesi personelin ortalama
maaşlarını her bölüm için ayrı ayrı listeler:
SELECT BOLUM, AVG(MAAS) FROM PERSONEL
GROUP BY BOLUM