November 18, 2010

SQLite'a Giriş

[Bu belgenin PDF hali aşağıdadır, Scribd kutusunun sol altındaki Download linkinden indirilebilir.]

Click here to download:

sqlitenotlari.pdf (143 KB)

(download)

SQLite Notları

İçindekiler

SQLite konfigürasyon, kurulum, sunucu program gerektirmeyen, tek bir dosya üzerinde bulunan bir veritabanı türüdür. Birçok dilde kullanılabilmektedir. sqlite3 programı ile programlama dili kullanmadan veritabanları oluşturulabilir.

Kavramlar

relational database: ilişkisel veritabanı
table: Veriler tablo halinde saklanır.
schema: Tablodaki sütunlara ait bilgiler (meta veri)
row: record, kayıt
column: alan adı
field: alan, hücre
primary key: birincil anahtar
foreign key: yabancı anahtar
trigger: tetik, dürtü
view : görünüm
transaction: etkileşim
result set: sonuç kümesi (SELECT işlemi sonucu seçilen)
index

Veritabanı ve tablo oluşturmak

Veritabanı oluşturmak için: sqlite3.exe veritabanı.db
Tablo oluşturmak için: CREATE TABLE tablo_adı(alan_adları...)

Alan adlarına örnek:

Tür belirtmeden:
CREATE TABLE notlar(öğrenci_no, harf_notu);
Tür belirterek:
CREATE TABLE notlar(öğrenci_no integer, harf_notu text);
Veri türleri: null, integer, real, text, blob

Tabloyu silmek: DROP TABLE notlar;
ALTER TABLE: Tablo adını değiştirmek veya sütun eklemek için kullanılır. Sütun adını değiştirmek, sütunu silmek ya da sütuna sınırlama (constraint) getirmek için kullanılamaz. [http://www.sqlite.org/lang_alterTABLE.html]

SQLite İfadeleri

Veritabanındaki tüm bilgiyi seçmek (yazdırmak)

Veritabanındaki her şeyi seçmek:
```
SELECT * FROM tablo_adı;
```
Veritabanındaki bazı alan adlarını (sütunları) seçmek:
```
SELECT alan_adı FROM tablo_adı;
```
Veritabanındaki bazı kayıtları (satırları) seçmek:
```
SELECT * FROM tablo_adı WHERE alan_adı=KOŞUL;
```
Yukarıdaki iki koşul birleştirilip veritabanındaki bazı hücreler seçilebilir.
```
SELECT bir_alan_adı FROM tablo WHERE başka_bir_alan_adı=KOŞUL;
```
Seçim koşulları operatörlerle zenginleştirilebilir.

Önemli operatörler:

Eşitlik işlemi yerine kullanılabilecekler:

IN : Kümede olup olmadığına bakar.
LIKE: Wildcard eşleştirme için (% * gibi, _ ? gibi.)
GLOB: LIKE gibi; ancak UNIX'teki shell globbing'e benzer, büyük/küçük harf duyarlı
BETWEEN: Aralık belirtmek için kullanılır.

Ör:

SELECT bir_alan_adı FROM tablo
       WHERE başka_bir_alan_adı LIKE KOŞUL BETWEEN 200 AND 400;

Veri eklemek, güncellemek ve silmek

DEFAULT anahtar sözcüğü: Veri girilmezse alacağı değer . Ör:

CREATE TABLE kitaplar(id integer primary key, kitap_adı text,
                      yayım_tarihi text default 'belli değil');

Veri ekleme:

INSERT INTO kitaplar(id,kitap_adı, yayım_tarihi)
     VALUES(12,"Puslu Kıtalar Atlası","1999");

Tüm alanlara ekleme yapılacaksa tablo_adından sonraki kısım gerekli değildir:
```
INSERT INTO kitaplar
     VALUES(12,"Puslu Kıtalar Atlası","1999");
```

MySQL'dekinin aksine toplu veri girişi yoktur.

INSERT INTO kitaplar
     VALUES(12,"Puslu Kıtalar Atlası","1999"),
     VALUES(13,"Karanlığa Okunan Ezanlar","2005");

Veri eklerken tüm alanların girilmesi zorunlu değildir. Default değer verilmişse o değer kullanılır, yoksa NULL girilir; ancak bu durumda tablo adından sonra hangi alanlara giriş yapıldığı belirtilmelidir.
```
INSERT INTO kitaplar(id,kitap_adı) VALUES(12,"Puslu Kıtalar Atlası");
INSERT INTO kitaplar(kitap_adı) VALUES("Divan);
```
Var olan veriyi değiştirmek için INSERT INTO yerine REPLACE INTO kullanılır.
Primary key: Her tabloda bir primary key vardır, bu açıkça belirtilmezse rowid adında otomatik bir alan oluşturulur. (Belirtilirse primary key ile rowid birbirinin kopyası olur.)

Aynı ana anahtarı içeren bir kayıt girilmek istenirse buna izin verilmez. Bu kayıt önce silinmeli, sonra ekleme yapılmalıdır ya da doğrudan REPLACE INTO komutu ile değiştirilmelidir.
INSERT OR REPLACE INTO komutu ile kayıt varsa güncellenir, yoksa oluşturulur.

INSERT INTO ve SELECT komutları birleştirilebilir:

INSERT INTO yeni_tablo SELECT * FROM varolan_tablo;

Kayıt silmek için DELETE komutu kullanılır. Koşul belirtilmezse tüm tablo silinir.
```
DELETE FROM tablo; -- tüm tabloyu sıfırlar.
```
Belli satırlardaki verilerin bazı alanlarını değiştirmek için UPDATE komutu kullanılır. UPDATE tablo_adı SET alan_adı='yeni değer' WHERE id=1;

Bu işlemin REPLACE'den farkı tüm kaydı girmenin gerekmemesi, sadece değişmesi gereken alanları güncellemesidir. Bunun dışında REPLACE aslında INSERT OR REPLACE ifadesine denk bir kısayol olduğundan halihazırda kayıt yoksa oluşturulur.

UPDATE komutunda ise yeni kayıt oluşturulmaz; kayıt yoksa hata mesajı verilmez, varsa güncellenir.

Verileri sorgulamak

SELECT * FROM tablo_adı;
SELECT alan_adı1, alan_adı2 FROM tablo_adı;
Gösterim esnasında sütunları yeniden adlandırmak için AS anahtar sözcüğü kullanılır.
```
SELECT alan_adı1 AS YeniSütunAdı FROM tablo_adı;
```
Gösterilen kayıt sayısını limitlemek için LIMIT sözcüğü kullanılır. SELECT * FROM tablo_adı limit 2; ilk iki kaydı gösterecektir. Offset sözcüğü ile başlangıç satırı belirtilebilir. SELECT * FROM tablo_adı limit 2 offset 1;
Gösterilen veriyi sıralamak için ORDER BY alan_adı ASC/DESC; kalıbı kullanılır. Birden fazla alan adı kullanılabilir.
```
ORDER BY alan_adı1 desc, alan_adı2 ASC;
```
Sadece belli satırları göstermek için WHERE sözcüğü kullanılır.
```
SELECT * FROM tablo_adı WHERE id = 1;
```
Gösterilen kayıtlarda yineleme varsa DISTINCT sözcüğü ile yinelenen kayıtlar filtrelenebilir.
```
SELECT DISTINCT yazar_adı FROM kitaplar WHERE fiyat < 20;
```
Group by kalıbı ile yinelenen kayıtlara ait veriler gruplanabilir. Daha sonra sum() gibi toplam fonksiyonları ile bu gruplar hakkında özet bilgiye ulaşılabilir.
```
SELECT sum(orderPrice) AS Total, Customer FROM orders
    GROUP BY customer;
```

Toplam (aggregate) fonksiyonları kullanılırsa WHERE yerine HAVING anahtar sözcüğü kullanılmalıdır.

SELECT sum(OrderPrice) AS Total, Customer FROM Orders
    GROUP BY Customer HAVING sum(OrderPrice)>1000;

(Bkz: http://zetcode.com/databases/sqlitetutorial/SELECT/ )

Kısıtlamalar

Bazı alan adlarına kısıtlama koyabiliriz, bu şekilde anlamsız veri girişi önlenir.

NOT NULL: Verinin girilmesini zorunlu yapar.
UNIQUE: Girilen veri eşsiz olmalıdır.
PRIMARY KEY: Birincil anahtar
FOREIGN KEY: Yabancı anahtar
CHECK: Kabul edilebilir veri girişi aralığı.
DEFAULT: Varsayılan değer.
NOT NULL: Alan adını zorunlu yapar. CREATE TABLE öğrenciler(ad text, soyad text NOT NULL);
UNIQUE: Sütundaki her değer farklı olmak zorundadır. CREATE TABLE markalar(Id integer, markaadi text unique);
PRIMARY KEY: Tablodaki her kaydın en az bir eşsiz sütunu olmalıdır. Bunlardan en onemlisi PRIMARY KEY'dir. birden fazla UNIQUE key olabilir, ancak PRIMARY KEY bir tanedir. PRIMARY KEY otomatik olarak UNIQUE'tır.

Başka tablolarla bağlantı kurulurken PRIMARY KEY'ler diğer tablolarda FOREIGN KEY olarak bağlantıyı sağlar. Birincil anahtar türü tamsayı (integer) ise auto increment özelliği de eklenir.

Varsayılan olarak rowid adında bir birincil anahtar vardır. Birincil anahtar ayrıca verilse bile, rowid birincil anahtar olarak kullanılabilir.)
FOREIGN KEY: Bir tablodaki bir yabancı anahtar başka bir tablodaki birincil anahtardir ve iki tablo arasındaki bağlantıyı sağlar.
```
CREATE TABLE yazarlar(yazarid integer primary key, ad text);
CREATE TABLE kitaplar(kitapid integer primary key, başlık text,
     yazarid integer, foreign key(yazarid) references yazarlar (yazaradı));
```
Bu şekilde yazarlar tablosunda bulunmayan bir yazara ait bir kitabın veritabanına eklenmesi engellenmiş olur.

CHECK: Not null'a benzer bir şekilde, belli bir koşulu sağlamayan verilerin veritabanına girilmesini engeller.

CREATE TABLE Orders(Id integer PRIMARY KEY, OrderPrice integer
     CHECK(OrderPrice>0), Customer text);

sqlite> INSERT INTO Orders(OrderPrice, Customer) VALUES(-10, 'Johnson');
SQL error: constraint failed

DEFAULT kısıtlaması: Bir alana veri girilmezse varsayılan değer girilir.

Tabloları birleştirmek

İlişkisel (relational) veritabanlarının en önemli özelliği tabloları birleştirebilmeleridir. JOIN operasyonu ile iki ya da daha fazla tablo birleştirilir. Bu işlem, INNER JOIN ve OUTER JOIN olarak iki çeşittir; aksi belirtilmedikçe INNER JOIN yapılır.

Inner Join

İki tablodan eşleşen değerlere sahip kayıtları gösterir. Üç çeşittir: Inner join, natural inner join ve cross inner join.

Inner Join

SELECT Name, Day FROM Customers, Reservations
       WHERE Customers.CustomerId = Reservations.CustomerID;

Natural Inner Join: Otomatik olarak ortak sütunlar üzerinden eşleştirme yapar.
Cross Inner Join: Bir tablodaki tüm kayıtlarla diğer tablodaki tüm kayıtları birleştirir. (Kartezyen çarpım gibi)

Outer Join

Outer join işleminde, birleştirilen tabloların eşleşen kaydı olması gerekemez. Left outer join, right outer join, full outer join olarak üç çeşit olsa da SQLite sadece left outer join destekler. Bu nedenle işlem left join olarak da yazılabilir. Eşleşmeyen kayıtlara ait değerler NULL olarak gösterilir.

Outer Join:

SELECT Name, Day FROM Customers LEFT JOIN Reservations
                 ON Customers.CustomerId=Reservations.CustomerId;

ya da eşleştirilen alan adı ortaksa USING anahtar sözcüğü ile

SELECT Name, Day FROM Customers LEFT JOIN Reservations USING (CustomerId);

Natural Left Outer Join: Ortak sütun adlarıyla otomatik outer join yapar.
```
SELECT Name, Day FROM Customers NATURAL LEFT OUTER JOIN Reservations;
```

SQLite Fonksiyonları

Temel fonksiyonlar

http://www.sqlite.org/lang_corefunc.html

SELECT komutu ile fonksiyonlar ekrana yazdırılabilir.
SELECT fonksiyon_adı(); gibi
sqlite_version()
random()
upper()/lower()/length()

Aggregate fonksiyonlar

http://www.sqlite.org/lang_aggfunc.html

max()/min()/sum()
count()/count(*)
avg()
sum()/total()

Tarih ve zaman fonksiyonları

http://www.sqlite.org/lang_datefunc.html

date();
date('now');
datetime();
datetime('now');
strftime('%d-%m-$Y');
date('now','start of year','10 months','weekday 4');

Görünümler (views), tetikler (trigger) ve transactions

Görünümler

Görünümler bir ya da birden fazla tablodaki verilerin yeni bir tablo olmadan istenen şekilde gösterilmesidir. Sanal bir tablo olarak düşünülebilir.

CREATE VIEW UcuzKitaplar AS Select KitapAdı From Kitaplar WHERE Fiyat < 20;

SELECT * FROM UcuzKitaplar;

Yaratılan görünüm, DROP VIEW kalıbı ile silinebilir.

Triggers (Tetikler)

Veritabanında belli bir olay olduğunda otomatik olarak gerçekleştirilen işlemlere trigger(tetik) denir.

CREATE TABLE Log(Id integer PRIMARY KEY, EskiAd text, YeniAd  text, Tarih text)

CREATE TRIGGER mytrigger UPDATE OF Ad ON Arkadaşlar
BEGIN
 INSERT INTO Log(EskiAd,YeniAd, Tarih) VALUES(eski.Ad, new.Name, datetime('now'));
END;

Böylece Arkadaşlar adlı tabloda bir Ad güncellenirse Log adlı tabloya güncellemeyle ilgili bir kayıt otomatik olarak girilir.

Transactions

Transaction, veritabanında yapılan işlemlerin en küçük parçasını gösterir. SELECT dışındaki komutlarda içkin (implicit) bir transaction vardır.

Elle transaction başlatmak için Begin Transaction ifadesi kullanılır. Commit ya da Rollback ifadelerine kadar olan tüm ifadeler tek bir transaction'da gerçekleştirilir. Bu şekilde veri tabanında yapılan işlem hızlanır ve kilitlenmeler (lock) azaltılır. (Birden fazla kişi transaction yaparken her biri transaction sırasında veritabanını kilitler, transactionlar gruplandıklarında bu işlem seyrekleşir.) (Bkz: [http://stackoverflow.com/questions/54998/how-scalable-is-sqlite] )

Rollback tüm değişiklikleri geri alır (Niye?)

Diğer

||: String birleştirme operatörü. Ör: SELECT "ABC" | | "DEF"
CREATE TABLE IF NOT EXISTS Testing(id integer primary key); : Tablo yoksa oluşturulur, varsa hiçbir şey yapılmaz.
Home dizininde .sqliterc adında bir dosya oluşturulabilir:
```
$ cat .sqliterc
.mode column
.header on
.nullvalue NULL
```

sqlite komutu, komut satırından seçenek ve emir alabilir.

sqlite3 -help/
sqlite3 -html deneme.db
sqlite3 deneme.db ".dump" > output.txt

Nokta ile başlayan komutlar (sqlite3 programına özel komutlar)

.help: Yardım
.databases: Bağlı bulunan veritabanlarını görmek için
.tables: Tüm tabloları görmek için
.schema: Tüm tabloların schemalarını görmek için
.schema tablo_adı: Bir tabloya ait schema'yı görmek için
.output filename: Ekran yerine dosyaya yazdırmak için
.output stdout: Dosya yerine ekrana yazdırmak için
.headers on/off: Başlık satırlarını yazdırmak icin
.nullvalue STRING: Nullvalue yerine STRING yazdırmak için
.mode {csv, column, html, INSERT, line, list, tabs}: Çıktı türleri
.log FILE/stderr: Log yazdırmak için
.dump: Veritabanını SQL ifadeleri olarak yazdırmak için
.dump tablo_adı: Tabloyu SQL olarak yazdırmak için
.separator STRING: Ayırıcı olarak STRING sözcüğünü kullanmak için
.width sayı1, sayı2...: Sütun genişliklerini ayarlamak için

Bkz: