Transcript Database systems design - Canakkale Onsekiz Mart University

SQL
5/8/2003
TCSS445A Isabelle Bichindaritz
1
Ne öğrenilecek
•
•
•
•
•
SQL’in amacı ve önemi
SQL cümlesi
SQL kelimeleri
SQL sorgularının oluşturulması
SQL’le veri tabanının güncellenmesi
5/8/2003
TCSS445A Isabelle Bichindaritz
2
Veri Tabanı Dili
• İdeal halde, veri tabanı dili kullanıcıya aşağıdaki
imkanları sağlamalıdır:
– Veri tabanının ve ilişki yapılarının oluşturulması;
– İlişkideki veriler üzerinde ekleme, değiştirme ve silme
işlemlerinin yerine getirilmesi;
– Basit ve karmaşık sorguların yerine getirilmesi
• Bu işlemler kullanıcının en az çabası ile yerine
getirilmeli ve komut yapısı/sözdüzeni kolay
öğrenilebilen olmalıdır
• Taşınabilir
(farklı
ortamlarda
çalışabilir)
olmalıdır
5/8/2003
TCSS445A Isabelle Bichindaritz
3
SQL –genel bilgiler
• SQL iki başlıca bileşenden oluşmaktadır:
– Veri tabanının yapısını tanımlamak için Veri
Tanımlama Dili (DDL)
– Verileri çıkarmak ve güncellemek için veri
işlem dili (DML)
• SQL2 komut denetimi akışlarını içermiyor.
Bu
işlemleri
programlama
dilleri
kullanmakla gerçekleştirmek mümkündür
5/8/2003
TCSS445A Isabelle Bichindaritz
4
SQL- genel bilgiler (devamı)
• SQL öğrenmek kolaydır:
– Yordamsal olmayan dildir – hangi bilginin
gerektiğinin belirlenmesi yeterlidir, bu bilginin
nasıl alındığının gösterilmesine ihtiyaç yoktur;
– Serbest biçimlidir (free-format)
5/8/2003
TCSS445A Isabelle Bichindaritz
5
SQL-genel bilgiler (devamı)
• İngiliz dilinin kelimelerini içerir:
1)
CREATE
VARCHAR(5),
TABLE
Staff(staffNo
lName VARCHAR(15),
salary DECIMAL(7,2));
2) INSERT INTO Staff VALUES (‘SG16’,
‘Brown’, 8300);
3) SELECT staffNo, lName, salary
FROM Staff
WHERE salary > 10000;
5/8/2003
TCSS445A Isabelle Bichindaritz
6
SQL-genel bilgiler (devamı)
• Veri ve Veri Tabanı yöneticileri, şirket
yöneticileri, uygulama geliştiricileri, ve
diğer kullanıcılar yararlana bilir
• SQL için ISO standardı mevcuttur. Bu,
ilişkisel veritabanları için resmi ve de facto
(faktık) standart dildir
5/8/2003
TCSS445A Isabelle Bichindaritz
7
SQL’in tarihçesi
• 1974’de, D. Chamberlin (IBM San Jose
Laboratory) ‘Structured English Query
Language’ (SEQUEL) adlandırılan dil
geliştirdi.
• Bu dilin sonraki sürümü, SEQUEL/2 1976
‘da geliştirildi, fakat yasal nedenlerden
dolayı sonradan SQL olarak tanımlandı
5/8/2003
TCSS445A Isabelle Bichindaritz
8
SQL’in tarihçesi
• IBM, sonradan System R adlı SEQUEL/2
tabanlı VTYS geliştirdi.
• Ama SQL’in kökü, System R’den önce
geliştirilmiş SQUARE (Specifying Queries as
Relational Expressions) projesine dayanıyor
5/8/2003
TCSS445A Isabelle Bichindaritz
9
SQL’in tarihçesi
• 70’lerin sonlarında ORACLE meydana çıktı ve
SQL tabanlı ilk ticari İVTYS (RDBMS) oldu
• 1987’de ANSI ve ISO, SQL için başlangıç
standardı yayınladılar
• 1989’da ISO, ‘Integrity Enhancement Feature’
(bütünlüğün sağlanması özellikleri) tanımını ekledi
• 1992’de ISO standardına tam cevap veren SQL2
veya SQL/92 sürümü geliştirildi
• 1999’de nesneye yönelik veri yönetimini
destekleyen SQL3 sunuldu
5/8/2003
TCSS445A Isabelle Bichindaritz
10
SQL komutlarının yazılışı
• SQL cümlesi anahtar kelimelerden (reserved
words) ve kullanıcı tanımlı kelimelerden
oluşmaktadır (User-defined words).
– Anahtar kelimeler tanımlandığı gibi
yazılmalıdır ve satırlara bölünemez
– Kullanıcı tanımlı kelimeler veri tabanındaki
ilişki, sütun, görünüm gibi değişkenleri
tanımlamak içindir
5/8/2003
TCSS445A Isabelle Bichindaritz
11
SQL komutlarının yazılışı
• SQL cümlesinin bileşenleri, sabit karakter
türündekiler dışında büyük-küçük harflere
duyarsızdır
• SQL cümlesinin okunaklı olması için tavsiyeler:
– Her yantümce ( clause) yeni satırdan başlamalıdır.
– Tüm yantümceler alt-alta aynı hizada yazılmalıdır
– Eğer yantümce birkaç kısımdan oluşuyorsa her bir
kısım ayrıca satırda ve yantümcenin başladığı yerden
bir az sağdan yazılmalıdır
5/8/2003
TCSS445A Isabelle Bichindaritz
12
SQL cümlesinin sabitleri
• Tüm sayısal olmayan sabitler tek tırnak
arasında yazılmalıdır (örn., ‘London’).
• Sayısal sabitler
(örn., 650.00).
5/8/2003
tırnak içinde yazılmaz
TCSS445A Isabelle Bichindaritz
13
SELECT cümlesi
SELECT [DISTINCT | ALL]
{* | [satır ifadesi [AS yeni ad] [,...] }
FROM
Tablo Adı [alias] [, ...]
[WHERE
koşul]
[GROUP BY sütun listesi] [HAVINGkoşul]
[ORDER BY sütun listesi]
SELECT cümlesinin ifadesinde Backus–Naur yazılım biçimi (BNF)
kullanılmıştır. Bu yazılıma göre «I» simgesi «veya» anlamındadır. [ ] –
ifadenin zorunlu olmadığını, ... Kelimenin tekrarlandığını göstermek içindir
5/8/2003
TCSS445A Isabelle Bichindaritz
14
SELECT cümlesi
FROM
kullanılacak tablo(lar)
WHERE
satırları seçme koşulu
GROUP BY belirlenmiş sütundaki değerleri aynı olan
satırların bir grupta toplanması
HAVING
grupların belirli koşul üzere seçilmesi
SELECT
sonuç ilişkide hangi sütunların olacağının
belirlenmesi
ORDER BY sütun(sütunlar) değerlerine göre sonuç
ilişkinin satırlarının sıralanması
5/8/2003
TCSS445A Isabelle Bichindaritz
15
SELECT cümlesi
• Yantümcelerin ardışıklığı değiştirilemez
• Yalnız SELECT ve FROM zorunludur
5/8/2003
TCSS445A Isabelle Bichindaritz
16
Tüm sütunlar,tüm satırlar
Tüm personellerin ayrıntılı bilgileri.
SELECT staffNo, fName, lName, address, position,
sex, DOB, salary, branchNo
FROM Staff;
5/8/2003
TCSS445A Isabelle Bichindaritz
17
Tüm sütunlar, tüm satırlar (devamı)
* simgesi ‘tüm sütunlar’ anlamında kullanılır
SELECT *
FROM Staff;
5/8/2003
TCSS445A Isabelle Bichindaritz
18
Bazı sütunlar, tüm satırlar
Yalnız numarasını, ad ve soyadını, maaşını göstermekle
tüm personellerin bilgileri (No, ad,soyad ve maaşları)
SELECT staffNo, fName, lName, salary
FROM Staff;
5/8/2003
TCSS445A Isabelle Bichindaritz
19
DISTINCT kullanımı
Bakılmış tüm evlerin numaraları
SELECT propertyNo
FROM Viewing;
5/8/2003
TCSS445A Isabelle Bichindaritz
20
DISTINCT kullanımı
•
DISTINCT , tekrarlanan satırları elemek içindir
Bakılmış farklı evlerin numaraları
SELECT DISTINCT propertyNo
FROM Viewing;
Rno
Pno
Date
Comment
CR56
PA14
24may98
Çok küçük l
CR76
PG4
20-apr- Çok uzak
98
CR56
PG4
26may98
CR62
PA14
14may98
CR56
PG36
28-apr98
5/8/2003
Yemek odası
yok
TCSS445A Isabelle Bichindaritz
21
Hesaplanan alanlar (Calculated Fields)
Personellerin numaralar, adları, soyadları, ve aylık
maaşları
SELECT staffNo, fName, lName, salary/12
FROM Staff;
5/8/2003
TCSS445A Isabelle Bichindaritz
22
Hesaplanan Alanlar (devamı)
• Sütunu adlandırmak için AS kelimesi
kullanılır
SELECT staffNo, fName, lName, salary/12
AS monthlySalary
FROM Staff;
«AS» sütunu yeniden adlandırmak için de kullanıla bilir.
Hesaplanan alanlarda birden fazla tablonun sütunlarının kullanılması
mümkündür
5/8/2003
TCSS445A Isabelle Bichindaritz
23
Koşullu arama
Maaşı 10,000’den yüksek olan personellerin bilgileri
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > 10000;
5/8/2003
TCSS445A Isabelle Bichindaritz
24
Karmaşık koşul
London veya Glasgow kentlerindeki tüm şubelerin
adres bilgileri (No,cadde,kent ve posta kodları)
SELECT *
FROM Branch
WHERE city = ‘London’ OR city = ‘Glasgow’;
5/8/2003
TCSS445A Isabelle Bichindaritz
25
Değer aralıklarının kullanılması
maaşları 20,000 ve 30,000 arasında olan personellerin bilgileri
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary BETWEEN 20000 AND 30000;
• BETWEEN aralığın uç değerlerini de içeriyor
5/8/2003
TCSS445A Isabelle Bichindaritz
26
Değer aralıklarının kullanılması (devamı)
• Eksi anlamda NOT BETWEEN kullanılır
• BETWEEN, SQL’e ilave güç katmaz. Onun
görevini diğer işlemlerle de gerçekleştirmek
mümkündür:
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary>=20000 AND salary <= 30000;
5/8/2003
TCSS445A Isabelle Bichindaritz
27
Küme üyeliği
Tüm yöneticilerin ve denetleyicilerin listesi
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position IN (‘Manager’, ‘Supervisor’);
5/8/2003
TCSS445A Isabelle Bichindaritz
28
Küme üyeliği (devamı)
işlem yapan sürümü de mevcuttur: (NOT IN).
• IN, SQL’e ilave güç katmaz
• Aynı sonucu aşağıdaki ifadeden de almak mümkündür:
• Eksi
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position=‘Manager’ OR
position=‘Supervisor’;
• IN’in kullanımı, kümede çok sayıda kesintili değer olduğu
zaman etkilidir
5/8/2003
TCSS445A Isabelle Bichindaritz
29
Örüntülerin (Pattern) eşleştirilmesi
Adreslerinde ‘Glasgow’ altsatırı bulunan tüm mülk
sahipleri
SELECT clientNo, fName, lName, address, telNo
FROM PrivateOwner
WHERE address LIKE ‘%Glasgow%’;
5/8/2003
TCSS445A Isabelle Bichindaritz
30
Örüntülerin karşılaştırılması
• SQL’de örüntüleri karşılaştırmak için 2 özel simge
kullanılıyor:
– %: sıfır veya daha fazla karakter;
– _ (alt çizgi): her hangi sayıda karakter
• LIKE ‘%Glasgow%’- ‘Glasgow’ altsatırını içeren,
herhangi uzunlukta karakterler ardışıklığını ifade
ediyor
5/8/2003
TCSS445A Isabelle Bichindaritz
31
NULL arama koşulu
PG4 evi üzere görüş bildirmemiş müşterinin numarası
ve eve bakış tarihi
• PG4’e iki bakış yapılmış, birisinde görüş (açıklama)
bildirilmemiştir.
• IS NULL sözcüğü ile sorgunun cevabını bula bileriz:
SELECT clientNo, viewDate
FROM Viewing
WHERE propertyNo = ‘PG4’ AND
comment IS NULL;
5/8/2003
TCSS445A Isabelle Bichindaritz
32
NULL arama koşulu
cientNo
Pno
viewDat
e
Comment
CR56
PA14
24-may98
Çok küçük l
CR76
PG4
20-apr98
Çok uzak
CR56
PG4
26-may98
CR62
PA14
14-may98
CR56
PG36
28-apr98
Yemek odası yok
• Null olmayan değerleri (IS NOT NULL) ile
aramak mümkündür.
5/8/2003
TCSS445A Isabelle Bichindaritz
33
Tek sütun üzere sıralama
Maaşların azalma ardışıklığı ile personellerin bilgileri
listesi
SELECT staffNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC;
5/8/2003
TCSS445A Isabelle Bichindaritz
34
Tek sütun üzere sıralama (devamı)
Evlerin türüne göre sıralama yapmakla tüm kiralık
evlerin alfabetik sıra ile listesi
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type;
5/8/2003
TCSS445A Isabelle Bichindaritz
35
Çok sütun üzere sıralama
• Bu listede 4 daire (“flat”) bulunmaktadır. İkinci sıralama kıstası
bulunmadığından bu listenin satırları tabloda rast geldikleri
ardışıklık ile sıralanmışlar
• Kiranın (rent) azalma sırası tabloyu yeniden
düzenleye bileriz:
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type, rent DESC;
5/8/2003
TCSS445A Isabelle Bichindaritz
36
SELECT
yantümce
(Aggregate)
–
Kümeleme
• ISO standardı 5 kümeleme fonksiyonunu tanımlar:
COUNT belirtilmiş sütundaki değerler sayısı
SUM belirtilmiş sütundaki değerlerin toplamı
AVG belirtilmiş sütundaki değerlerin ortalaması
MIN belirtilmiş sütundaki en küçük değer
MAX belirtilmiş sütundaki en büyük değer
5/8/2003
TCSS445A Isabelle Bichindaritz
37
SELECT yantümcesi - Kümeleme
• Tablonun tek sütunu üzere işlem yapılıyor. Sonuç
tek bir değerdir
• COUNT, MIN, ve MAX hem sayısal, hem de
sayısal olmayan alanlar için uygulana bilir. SUM
ve AVG yalnız sayısal alanlar için kullanıla bilir.
• COUNT(*) dışında tüm fonksiyonlar önce null
değerleri eler ve işlemi, null olmayan değerler
üzerinde yaparlar
5/8/2003
TCSS445A Isabelle Bichindaritz
38
SELECT yantümcesi-kümeleme
• COUNT(*), null veya tekrarlanan değerlere
bakmaksızın, tablonun tüm satırlarını sayar.
• Tekrarları elemek için sütun adından önce
DISTINCT kullanıla bilir
• DISTINCT’in MIN/MAX fonksiyonlarında
etkisi yoktur, ama SUM/AVG kullanımında
etkilidir
5/8/2003
TCSS445A Isabelle Bichindaritz
39
SELECT yantümcesi-kümeleme
• Kümeleme fonksiyonları yalnız
SELECT
listesinde ve HAVING sözcüğünde kullanıla bilir
• Eğer SELECT listesinde kümeleme fonksiyonu
varsa ve sorguda GROUP BY kullanılmamışsa, o
zaman kümeleme fonksiyonun dışında, SELECT
listesinde hiçbir sütun adı olamaz. Örneğin,
aşağıdaki yazılış yanlıştır:
SELECT staffNo, COUNT(salary)
FROM Staff;
5/8/2003
TCSS445A Isabelle Bichindaritz
40
COUNT(*) kullanımı
aylık kira hakkı $350 üzerinde olan kaç ev vardır:
SELECT COUNT(*) AS count
FROM PropertyForRent
WHERE rent > 350;
5/8/2003
TCSS445A Isabelle Bichindaritz
41
COUNT(DISTINCT) kullanımı
Mayis ayında kaç farklı eve bakılmıştır
SELECT COUNT(DISTINCT propertyNo) AS count
FROM Viewing
WHERE viewDate BETWEEN ‘1-May-11’ AND ‘31-May-11’;
cientNo
Pno
viewDat
e
Comment
CR56
PA14
24-may11
Çok küçük l
CR76
PG4
20-apr11
Çok uzak
CR56
PG4
26-may11
CR62
PA14
14-may11
CR56
PG36
28-apr11
5/8/2003
Yemek odası yok
TCSS445A Isabelle Bichindaritz
42
COUNT ve SUM kullanımı
Yöneticilerin
maaşları
sayıları
ve
onların
toplam
SELECT COUNT(staffNo) AS sayı, SUM(salary) AS
toplam
FROM Staff
WHERE position = ‘Manager’;
5/8/2003
TCSS445A Isabelle Bichindaritz
43
MIN, MAX, AVG kullanımı
Personellerin en düşük, en yüksek, ve ortalama
maaşlarını bulmalı
SELECT MIN(salary) AS min,
MAX(salary) AS max,
AVG(salary) AS avg
FROM Staff;
5/8/2003
TCSS445A Isabelle Bichindaritz
44
SELECT cümlesi - Gruplaştırma
• Grup içinde genelleştirme yapmak için GROUP
BY sözcüğü kullanılmaktadır
• SELECT ve GROUP BY bütünleşiktir: SELECT
listesinde bulunan her kelime, grup için tek bir
değeri ifade eder. SELECT sözcüğü yalnız:
– Sütun adlarını
– Kümeleme fonksiyonlarını,
– Bunların kombinasyonundan oluşan ifadeleri içere bilir
5/8/2003
TCSS445A Isabelle Bichindaritz
45
SELECT cümlesi - Gruplaştırma
• SELECT listesindeki tüm sütun adları, eğer
kümeleme fonksiyonunda kullanılmamışsa
GROUP BY sözcüğünde bulunmalıdır
• Eğer WHERE, GROUP BY ile birlikte
kullanılıyorsa, WHERE önce gelmelidir.
Gruplar, koşulu sağlayan satırlar üzere
oluşturuluyor.
5/8/2003
TCSS445A Isabelle Bichindaritz
46
Örnek 5.17 GROUP BY
kullanımı
Her şubedeki personel sayısı ve toplam maaşları
SELECT
branchNo,
COUNT(staffNo) AS count,
SUM(salary) AS sum
FROM Staff
GROUP BY branchNo
ORDER BY branchNo;
5/8/2003
TCSS445A Isabelle Bichindaritz
47
GROUP BY kullanımı
5/8/2003
TCSS445A Isabelle Bichindaritz
48
Sınırlı gruplaştırma – HAVING
sözcüğü
• HAVING sözcüğü, verilmiş koşulları sağlayan
grupları tanımlamak için kullanılıyor
• WHERE’e benzerdir. Ama WHERE satırların
elenmesinde, HAVING grupların elenmesinde
kullanılar
• HAVING sözcüğündeki sütun adları GROUP BY
listesinde de bulunmalı veya kümeleme
fonksiyonunun argümanı olmalıdırlar
5/8/2003
TCSS445A Isabelle Bichindaritz
49
HAVING kullanımı
1’den fazla üyesi olan her bir şubenin personel sayısı
ve toplam maaşları
SELECT branchNo,
COUNT(staffNo) AS count,
SUM(salary) AS sum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;
5/8/2003
TCSS445A Isabelle Bichindaritz
50
Altsorgular
• SQL cümleleri kendileri de SQL cümleleri içere biliyorlar
• Bu tür sorgular dış SELECT cümlesinin SELECT, FROM,
WHERE ve HAVING yantümcelerinde kullanıla bilir.
Onlara altsorgu veya iç sorgu denir
• Altsorguların
INSERT,
UPDATE,
ve
cümlelerinde de kullanılması mümkündür
• Altsorgu,
karşılaştırma
gösterilmelidir
5/8/2003
işleminde
TCSS445A Isabelle Bichindaritz
sağ
DELETE
tarafta
51
Altsorgular (devamı)
• ORDER BY sözcüğü altsorgularda kullanılamaz
(dış SELECT’te kullanıla bilir).
• Altsorgunun SELECT listesi yalnız tek bir sütun
adı veya ifade içermelidir (EXISTS kullanan
altsorgular istisnadır).
• Varsayım
olarak,
altsorguda
FROM
sözcüğündeki tablo adı kullanılıyor. alias
kullanılması mümkündür
5/8/2003
TCSS445A Isabelle Bichindaritz
52
«Eşitlik» kullanılan arltsorgu
‘163 Main St’. Adresindeki
personellerin listesi
şubede
çalışan
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’);
5/8/2003
TCSS445A Isabelle Bichindaritz
53
Eşitlik kullanılan altsorgu (devamı)
• İç SELECT ‘163 Main St’ adresindeki
şubenin numarasını (‘B003’) buluyor
• Dış
SELECT,
bu
şubede
çalışan
personellerin ayrıntılı bilgilerini veriyor
• Dış SELECT böyle olacak:
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo = ‘B003’;
5/8/2003
TCSS445A Isabelle Bichindaritz
54
Eşitlik kullanılan altsorgu (devamı)
5/8/2003
TCSS445A Isabelle Bichindaritz
55
Küme kullanılan altsorgu
Maaşları, ortalama maaştan çok olanların listesi
(ortalama maaşla farkı göstermekle)
SELECT staffNo, fName, lName, position,
salary – (SELECT AVG(salary) FROM Staff) As SalDiff
FROM Staff
WHERE salary >
(SELECT AVG(salary)
FROM Staff);
5/8/2003
TCSS445A Isabelle Bichindaritz
56
Küme kullanılan altsorgu (devamı)
• ‘WHERE salary > AVG(salary)’ yazmak olmaz
• Ortalama maaş bulunmalı (17000), sonra,
ortalama maaştan yüksek olanların listesini dış
SELECT ile bulmalı
SELECT staffNo, fName, lName, position,
salary – 17000 As salDiff
FROM Staff
WHERE salary > 17000;
5/8/2003
TCSS445A Isabelle Bichindaritz
57
Küme kullanılan altsorgu (devamı)
5/8/2003
TCSS445A Isabelle Bichindaritz
58
İç içe altsorgu: IN kullanımı
‘163 Main St’ adresindeki şubenin personellerinin
kontrolünde olan evler
SELECT propertyNo, street, city, postcode, type, rooms, rent
FROM PropertyForRent
WHERE staffNo IN
(SELECT staffNo
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’));
5/8/2003
TCSS445A Isabelle Bichindaritz
59
İçiçe altsorgu: IN kullanımı (devamı)
5/8/2003
TCSS445A Isabelle Bichindaritz
60
ANY ve ALL
• ANY ve ALL tek sütun üreten altsorgularda kullanıla
bilir
• ALL kullanıldığı zaman, eğer altsorgunun ürettiği tüm
değerler koşulu sağlarsa sonuç “doğru” değerini alacak,
• ANY kullanıldığı zaman, eğer altsorgunun ürettiği her
hangi değer koşulu sağlarsa sonuç “doğru” değerini
alacak
• Eğer altsorgu boş ise, ALL doğru değer, ANY yanlış
değer döndürecek.
• SOME kelimesi ANY yerine kullanıla bilir.
5/8/2003
TCSS445A Isabelle Bichindaritz
61
ANY/SOME kullanımı
Maaşları B003 şubesinde çalışanların en azından
birisinin maaşından yüksek olan personellerin
listesi
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > SOME
(SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);
5/8/2003
TCSS445A Isabelle Bichindaritz
62
ANY/SOME kullanımı
• İç sorgu {12000, 18000, 24000} kümesini
üretiyor ve dış sorgu maaşı bu kümedeki
her hangi bir değerden yüksek olan
personeli seçiyor
5/8/2003
TCSS445A Isabelle Bichindaritz
63
Örnek 5.23 ALL kulanımı
Maaşları, B003 şubesindeki personellerin
her birisinin maaşından yüksek olanların
listesi
SELECT staffNo, fName, lName, position,
salary
FROM Staff
WHERE salary > ALL
(SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);
5/8/2003
TCSS445A Isabelle Bichindaritz
64
ALL kullanımı
5/8/2003
TCSS445A Isabelle Bichindaritz
65
Çok Tablolu sorgular
• Aynı tablodan gelen sonuçları işlemek için altsorgular
kullanıla biliyor
• Eğer sonuç farklı tablolardan veriler gerektiriyorsa
bitiştirme (join) işlemi kullanılmalıdır
• Join çalıştırmak için FROM sözcüğünde birden fazla tablo
ismi gösterilmelidir
• Tablo isimleri virgülle ayrılıyor
• FROM sözcüğünde alias tanımlamak ve sorgu ifadesinde
kullanmak mümkündür
• Alias tablo adından boşlukla ayrılıyor
5/8/2003
TCSS445A Isabelle Bichindaritz
66
Basit Join
Kiralık evlere bakmış
adları
tüm müşterilerin
SELECT c.clientNo, fName, lName,
propertyNo, comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo;
5/8/2003
TCSS445A Isabelle Bichindaritz
67
Basit Join
• Sonuç tabloda (c.clientNo = v.clientNo)
koşulunu sağlayan satırlar gösterilmiştir
• İlişkisel
cebirde
eşdeğerdir
5/8/2003
equi-join
TCSS445A Isabelle Bichindaritz
işlemine
68
Diğer JOIN yapıları
• SQL alternatif join işlemlerini de sağlıyor
FROM Client c JOIN Viewing v ON c.clientNo =
v.clientNo
FROM Client JOIN Viewing USING clientNo
FROM Client NATURAL JOIN Viewing
5/8/2003
TCSS445A Isabelle Bichindaritz
69
bitiştirme işlemi ve sıralama
Her bir şube için, evlerin kontrolünü
yapan personellerin numaraları, adları ve
kontrollerinde olan evlerin numaraları
•
SELECT s.branchNo, s.staffNo, fName,
lName, propertyNo
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
ORDER BY s.branchNo, s.staffNo, propertyNo;
5/8/2003
TCSS445A Isabelle Bichindaritz
70
bitiştirme işlemi ve sıralama
5/8/2003
TCSS445A Isabelle Bichindaritz
71
Üç tablolu Join
Her bir şube için evleri kontrol edenlerin listesi
(şubenin yerleştiği kendi ve kontrolünde olan
evleri göstermekle)
SELECT b.branchNo, b.city, s.staffNo,
fName, lName,
propertyNo
FROM Branch b, Staff s, PropertyForRent p
WHERE b.branchNo = s.branchNo AND
s.staffNo = p.staffNo
ORDER BY b.branchNo, s.staffNo, propertyNo;
5/8/2003
TCSS445A Isabelle Bichindaritz
72
Üç tablolu Join
Alternatif yazılış:
FROM (Branch b JOIN Staff s USING branchNo) AS
bs JOIN PropertyForRent p USING staffNo
5/8/2003
TCSS445A Isabelle Bichindaritz
73
Çoklu Gruplaştırma
Şubeler
üzere
her
bir
personelin
kontrolünde bulunan evlerin sayısı
SELECT s.branchNo, s.staffNo, COUNT(*)
AS count
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
GROUP BY s.branchNo, s.staffNo
ORDER BY s.branchNo, s.staffNo;
5/8/2003
TCSS445A Isabelle Bichindaritz
74
Çoklu Gruplaştırma
5/8/2003
TCSS445A Isabelle Bichindaritz
75
Join işleminin yürütülmesi
bitiştirme işlemi sonuçu aşağıdaki ardışıklıkla alınıyor:
1. FROM yantümcesinde gösterilmiş tabloların
kartezyen çarpımı alınıyor
2.WHERE yantümcesi varsa üretişmiş tabloda
koşula uygun olarak satırlar üzere eleme yapılıyor
3. Yerde kalan her bir satır için SELECT
listesindeki özelliklerin değerleri belirleniyor
5/8/2003
TCSS445A Isabelle Bichindaritz
76
Join işleminin yürütülmesi
4. Eğer DISTİNCT varsa sonuç tablodan tekrarlanan
satırlar eleniyor
5. ORDER BY yantümcesi varsa sonuç tabloda
sıralama yapılıyor
• SQL’de kartezyen çarpma için özel ifade biçimi
vardır:
SELECT [DISTINCT | ALL]
{* | columnList}
FROM Table1 CROSS JOIN Table2
5/8/2003
TCSS445A Isabelle Bichindaritz
77
İç bitiştirme
• İç Bitiştirme (inner join veya sadece join)
işleminde bitiştirilen tablolarda her hangi
satır eşleşmemiş ise, bu satır sonuç tabloda
bulunmaz
5/8/2003
TCSS445A Isabelle Bichindaritz
78
İç bitiştirme (devamı)
Bu iki tablonun (iç)
bitiştirmesi
SELECT b.*,p.*
FROM branch1 b,
property_for_rent1 p
WHERE b.bcity=p.pcity;
BRANCH1
Property_for_rent
Bno
bcity
pno
B3
B4
B2
Glasgow
Bristol
London
pcity
PA14 Aberdeen
PL94 London
PG4 Glasgow
İç bitiştirme-Dış Bitiştirme
• Sonuç tabloda aynı kentler bulunan iki satır
var
• Bristol ve Aberdeen şubelerine uygun
satırlar yoktur
• Eşleşmeyen satırların da sonuç tabloda
olması için Outer Join kullanılmalıdır
5/8/2003
TCSS445A Isabelle Bichindaritz
80
Sol dış Bitiştirme
Tüm eşleşmeyen şubeleri göstermekle hem
şube, hem de mülk bulunan kentler
SELECT b.*, p.*
FROM Branch1 b LEFT JOIN
PropertyForRent1 p ON b.bCity = p.pCity;
5/8/2003
TCSS445A Isabelle Bichindaritz
81
sol dış bitiştirme
• Sol (birinci) tablonun sağdaki tablonun
satırları ile eşleşmeyen satırları da sonuç
tabloda bulunuyor
• İkinci tablonun uygun sütun değerleri
NULL değer alıyor
5/8/2003
TCSS445A Isabelle Bichindaritz
82
Sağ Dış Bitiştirme- Right Outer Join
Eşleşmeyen mülkleri göstermekle hem şube,
hem de mülk bulunan kentler
SELECT b.*, p.*
FROM Branch1 b RIGHT JOIN
PropertyForRent1 p ON b.bCity = p.pCity;
5/8/2003
TCSS445A Isabelle Bichindaritz
83
Sağ Dış Bitiştirme
• Sağ dış bitiştirme işleminin sonucu ikinci
tablonun (sağdaki), soldaki (birinci)
tablonun satırları ile eşleşmeyen satırlarını
da içeriyor
• Birinci tablonun uygun satırları Null
değerler alıyor.
5/8/2003
TCSS445A Isabelle Bichindaritz
84
Tam Dış Bitiştirme- Full Outer Join
Eşleşmeyen şube ve mülkleri de göstermekle
hem şube, hem de mülk bulunan kentler
SELECT b.*, p.*
FROM Branch1 b FULL JOIN
PropertyForRent1 p ON b.bCity = p.pCity;
5/8/2003
TCSS445A Isabelle Bichindaritz
85
Tam Dış Bitiştirme-örnek
• Her iki tabloda eşleşmeyen satırları da
içerir
• Eşleşmeyen sütunlar Null değerler alıyor
5/8/2003
TCSS445A Isabelle Bichindaritz
86
EXISTS ve NOT EXISTS
• EXISTS ve NOT EXISTS yalnız altsorgularda
kullanılır
• Tek bir doğru/yanlış değerini döndürür
• Altsorgu tarafından dönen sonuç, tabloda
azından bir satır varsa doğrudur
en
• Altsorgudan dönen tablo boş ise yanlıştır
• NOT EXISTS , EXISTS işleminin eksini yapar.
5/8/2003
TCSS445A Isabelle Bichindaritz
87
EXISTS ve NOT EXISTS
• (NOT) EXISTS, altsorgu sonuç tablosunda
satırların var olduğunu (olmadığını)
denetler. Altsorgu tablosu birkaç sütun içere
bilir
• Genelde (NOT)
böyledir:
EXISTS
için
yazılış
(SELECT * ...)
5/8/2003
TCSS445A Isabelle Bichindaritz
88
EXISTS kullanımı
London şubesinde çalışanların listesi
SELECT staffNo, fName, lName, position
FROM Staff s
WHERE EXISTS
(SELECT *
FROM Branch b
WHERE s.branchNo = b.branchNo AND
city = ‘London’);
5/8/2003
TCSS445A Isabelle Bichindaritz
89
Örnek-EXISTS kullanılan sorgular
• s.branchNo = b.branchNo koşulu, her bir personel
için doğru şubeyi bulmak açısından gereklidir
• Bu koşulu kullanmasaydık, tüm personel
kayıtlarını alacaktık. Çünkü
SELECT * FROM Branch WHERE city=‘London’
her zaman doğrudur ve sorgu böyle olacaktı:
SELECT staffNo, fName, lName, position FROM Staff
WHERE true;
5/8/2003
TCSS445A Isabelle Bichindaritz
90
EXISTS kullanılan sorgular
• Sorgu böyle de yazıla bilir:
SELECT staffNo, fName, lName, position
FROM Staff s, Branch b
WHERE s.branchNo = b.branchNo AND
city = ‘London’;
5/8/2003
TCSS445A Isabelle Bichindaritz
91
Union, Intersect, ve Difference
(Except)
• Union, Intersection, ve Difference küme işlemleri,
birkaç sorgunun sonuçlarını tek bir tabloda
göstermek için kullanıla bilir
• A ve B tablolarının birleşimi,
A ve B
tablolarından en azından birinde bulunan satırları
veriyor
• İki tablonun kesişimi, her iki tabloda da bulunan
satırları veriyor
• Fark, A tablosunda bulunan, B tablosunda
bulunmayan satırları veriyor
• Tablolar birleşebilen olmalıdır
5/8/2003
TCSS445A Isabelle Bichindaritz
92
Union, Intersect, ve Difference
(Except)
• Kümeleme ifadesi tüm işlemler için aynıdır:
op [ALL] [CORRESPONDING [BY {column1 [, ...]}]]
• Eğer
CORRESPONDING
BY
yazılmışsa,
kümeleme işlemi gösterilen sütun (sütunlar) üzere
yapılacak
• Eğer CORRESPONDING yazılmışsa ama BY
tümcesi yoksa işlem ortak sütunlar üzere
yapılacak.
• Eğer ALL yazılmışsa , sonuç tekrarlanan satırlar
içere bilecek
5/8/2003
TCSS445A Isabelle Bichindaritz
93
Union, Intersect, ve Difference
(Except)
5/8/2003
TCSS445A Isabelle Bichindaritz
94
UNION kullanımı
Sorgu: ya şube , ya kiralık evler bulunan kentlerin
listesi
(SELECT city
FROM Branch
WHERE city IS NOT NULL) UNION
(SELECT city
FROM PropertyForRent
WHERE city IS NOT NULL);
5/8/2003
TCSS445A Isabelle Bichindaritz
95
UNION kullanımı
veya
(SELECT *
FROM Branch
WHERE city IS NOT NULL)
UNION CORRESPONDING BY city
(SELECT *
FROM PropertyForRent
WHERE city IS NOT NULL);
5/8/2003
TCSS445A Isabelle Bichindaritz
96
UNION kullanımı-devamı
• Her iki sorgudan sonuç tablolar alınarak
birleştirilir
5/8/2003
TCSS445A Isabelle Bichindaritz
97
INTERSECT kullanımı
Şube ve kiralık evlerin bulunduğu tüm
kentler
(SELECT city FROM Branch)
INTERSECT
(SELECT city FROM PropertyForRent);
5/8/2003
TCSS445A Isabelle Bichindaritz
98
INTERSECT kullanımı-devamı
Veya
(SELECT * FROM Branch)
INTERSECT CORRESPONDING BY city
(SELECT * FROM PropertyForRent);
5/8/2003
TCSS445A Isabelle Bichindaritz
99
INTERSECT kullanımı-devamı
Aynı sorgu böyle de yazıla bilir:
SELECT b.city
FROM Branch b PropertyForRent p
WHERE b.city = p.city;
veya
SELECT DISTINCT city FROM Branch b
WHERE EXISTS
(SELECT * FROM PropertyForRent p
WHERE p.city = b.city);
5/8/2003
TCSS445A Isabelle Bichindaritz
100
EXCEPT kullanımı
Şube bulunan, ama mülk bulunmayan kentlerin
listesi
(SELECT city FROM Branch)
EXCEPT
(SELECT city FROM PropertyForRent);
veya
(SELECT * FROM Branch)
EXCEPT CORRESPONDING BY city
(SELECT * FROM PropertyForRent);
5/8/2003
TCSS445A Isabelle Bichindaritz
101
EXCEPT kullanımı
Aynı sorgu böyle de yazıla bilir:
SELECT DISTINCT city FROM Branch
WHERE city NOT IN
(SELECT city FROM PropertyForRent);
veya
SELECT DISTINCT city FROM Branch b
WHERE NOT EXISTS
(SELECT * FROM PropertyForRent p
WHERE p.city = b.city);
5/8/2003
TCSS445A Isabelle Bichindaritz
102
INSERT
INSERT INTO TabloAdı [ (sütunlarListesi) ]
VALUES (verideğerleri listesi)
• columnList zorunlu değil. Bırakılmışsa SQL, sütunlar
listesini kaynak tabloda olduğu sıra ile kabul eder
• eğer sütunun veri değeri varsayılan (DEFAULT)
olarak tanımlanmamış ise uygun
sütun adı
gösterilmezse,
onun
değeri
NULL
olarak
belirlenmelidir.
5/8/2003
TCSS445A Isabelle Bichindaritz
103
INSERT
• VerideğerleriListesi aşağıdaki
uyarak oluşturulmalıdır:
kurallara
– Listedeki birimler sayısı sütunlar sayısına eşit
olmalıdır;
– Verinin yeri sütun listesindeki sütunun yerine
uygun olmalıdır;
– Verinin türü, sütun listesindeki uygun
sütunun türü ile uyumlu olmalıdır
5/8/2003
TCSS445A Isabelle Bichindaritz
104
INSERT … VALUES
Staff tablosuna tüm sütun değerleri
girilmekle yeni satır ilave edilmesi
INSERT INTO Staff
VALUES (‘SG16’, ‘Alan’, ‘Brown’, ‘Assistant’,
‘M’, Date‘1957-05-25’, 8300, ‘B003’);
5/8/2003
TCSS445A Isabelle Bichindaritz
105
Varsayım (Defaults) kullanmakla INSERT
Staff tablosuna yalnız belirlenmiş
değerlerini girmekle yeni satır eklenmesi
sütun
INSERT INTO Staff (staffNo, fName, lName,
position, salary, branchNo)
VALUES (‘SG44’, ‘Anne’, ‘Jones’,
‘Assistant’, 8100, ‘B003’);
Veya
INSERT INTO Staff
VALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, NULL,
NULL, 8100, ‘B003’);
5/8/2003
TCSS445A Isabelle Bichindaritz
106
INSERT … SELECT
• Bu biçim satırların bir tablodan diğerine
kopyalanmasına imkan sağlıyor
INSERT INTO TableName [ (columnList) ]
SELECT ...
5/8/2003
TCSS445A Isabelle Bichindaritz
107
INSERT … SELECT
StaffPropCount tablomuz olduğunu varsayalım.
Bu tablo tüm personellerin adlarını ve onların
kontrolündeki evlerin sayısını içeriyor:
StaffPropCount(staffNo, fName, lName, propCnt)
Staff ve PropertyForRent tablolarını kullanmakla
StaffPropCount tablosuna veriler eklene bilir.
5/8/2003
TCSS445A Isabelle Bichindaritz
108
INSERT … SELECT
INSERT INTO StaffPropCount
(SELECT s.staffNo, fName, lName, COUNT(*)
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
GROUP BY s.staffNo, fName, lName)
UNION
(SELECT staffNo, fName, lName, 0
FROM Staff
WHERE staffNo NOT IN
(SELECT DISTINCT staffNo
FROM PropertyForRent));
5/8/2003
TCSS445A Isabelle Bichindaritz
109
INSERT … SELECT
5/8/2003
TCSS445A Isabelle Bichindaritz
110
UPDATE
UPDATE TabloAdı
SET sütunAdı1 = veriDeğeri11
[, sütunadı2 = veriDeğeri2...]
[WHERE aramaKoşulu]
• TabloAdı taban tablonun adı veya görünüş
adı ola bilir
• SET tümcesi güncellenmesi gereken sütun
adlarını gösteriyor
5/8/2003
TCSS445A Isabelle Bichindaritz
111
UPDATE
• WHERE kelimesi zorunlu değildir:
– Eğer
yazılmamışsa,
gösterilen
sütunlar
tablonun tüm satırları üzere güncellenecek;
– Eğer yazılmışsa, yalnız aramaKoşulunu
sağlayan satırlar güncellenecek.
• Yeni veriDeğeri(Değerleri) uygun sütunların
türleri ile uyumlu olmalıdır
5/8/2003
TCSS445A Isabelle Bichindaritz
112
Tüm
satırların
güncellenmesi
(UPDATE All Rows)
Tüm personelin maaşlarına %3 zam yapılmıştır
UPDATE Staff
SET salary = salary*1.03;
Tüm yöneticilerin maaşlarına %5 zam yapmalı.
UPDATE Staff
SET salary = salary*1.05
WHERE position = ‘Manager’;
5/8/2003
TCSS445A Isabelle Bichindaritz
113
Örnek 5.40 Çok sütunun güncellenmesi (UPDATE
Multiple Columns)
David Ford (staffNo=‘SG14’) yöneticiliğe
yükselmiş ve maaşı £18,000 olmuştur
UPDATE Staff
SET position = ‘Manager’, salary = 18000
WHERE staffNo = ‘SG14’;
5/8/2003
TCSS445A Isabelle Bichindaritz
114
DELETE
DELETE FROM TabloAdı
[WHERE aramaKoşulu]
• TabloAdı taban tablonun veya görünüşün adı ola
bilir
• aramaKoşulu
zorunlu
değil.
Bırakılmışsa,
tablodaki tüm satırlar silinecek. Bu tablonun
silinmesi anlamına gelmez. Eğer aramaKoşulu
gösterilmişse , yalnız bu koşulu sağlayan satırlar
silinecek
5/8/2003
TCSS445A Isabelle Bichindaritz
115
satırların silinmesi
PG4 evine ait tüm görüş verilerini silmeli
DELETE FROM Viewing
WHERE propertyNo = ‘PG4’;
Viewing tablosundan tüm kayıtları silmeli
DELETE FROM Viewing;
5/8/2003
TCSS445A Isabelle Bichindaritz
116