SQLite'a Giriş

SQLite Notları

Yakın zamanda başlayacağım android programlama derslerinde çok işimize yarayacağı için bu konuyu paylaşmak istedim.

İç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

  • DEFAULTanahtar 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 NULLgirilir; 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 rowidbirbirinin 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 SELECTkomutları birleştirilebilir:
    INSERT INTO yeni_tablo SELECT * FROM varolan_tablo;
  • Kayıt silmek için DELETEkomutu 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 ASanahtar 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 WHEREsözcüğü kullanılır.
    SELECT * FROM tablo_adı WHERE id = 1;
  • Gösterilen kayıtlarda yineleme varsa DISTINCTsö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 HAVINGanahtar 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 .sqlitercadında bir dosya oluşturulabilir:
    $ cat .sqliterc
    .mode column
    .header on
    .nullvalue NULL
  • sqlitekomutu, 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:

SQLite'a Giriş” için bir yorum

  • Geri bildirim: SQLite Notları | muhendisliksite

  • Nisan 19, 2017 tarihinde, saat 6:52 pm
    Permalink

    merhaba
    sqlite ile oluşturduğum veritabanına veri girişi sınırı nasıl eklenir mesela sadece 10 tane kullanıcı kayıt yapabilir(sadece 10 satir nasıl yapılır)?

    Yanıtla

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir