Veri Tabanları
SQL nedir?
SQL normalizasyonu
SQL veritabanı programları
MySQL
Sqlite3
Sqlite3 Kullanımı
SQLite veritabanı ve tablo oluşturma
Öncelikle SQLite ile bir veritabanı oluşturulması öğrenilmelidir. Bu veritabanı birkaç tablodan, yerleştirilmiş bazı verilerden oluşur ve bu kayıtların görüntülenmesini sağlar.
Bir veritabanı dosyası oluşturmak için ilk olarak sqlite3 komutu ve ardından oluşturulmak istenen veritabanı ismi yazılır. Aşağıda company.db isimli bir veritabanı oluşturulmuştur. Bu veritabanında 3 kolonlu bir employee tablosu (id, name, title) ve bir department tablosu bulunmaktadır.
$ sqlite3 company.db
SQLite version 3.8.10.1 2015-05-09 12:14:55
Enter ".help" for usage hints.
sqlite> create table employee(empid integer,name varchar(20),title varchar(10));
sqlite> create table department(deptid integer,name varchar(20),location varchar(10));
sqlite> .quit
SQLite komut satırından ayrılmak için
".quit"komutu kullanılmıştır. Bu programdan çıkmak için üç güvenli yol bulunmaktadır;.q,.quit,.exitDaha önceden aynı isimle bir veritabanı oluşturulmamış olması gerekmektedir.
SQLite veritabanında istenilen dosya aşağıdaki hedef dizinde oluşturulmuş olur.
$ ls -l company.db
-rw-r--r-- 1 irembuyuk staff 3072 19 Ağu 14:13 company.db
Veri girme
Aşağıdaki örnekte hem employee hem de department tabloları için veriler girilmiştir.
Girilmek istenilen tüm veriler sqlite komut satırında yazılabilir ya da bu komutlar bir dosyaya eklenerek veriler dosyadan çekilebilir.
Öncelikle insert-data.sql adlı dosya nano komutuyla oluşturulur.
$ nano insert-data.sql
insert into employee values(101,'John Smith','CEO');
insert into employee values(102,'Raj Reddy','Sysadmin');
insert into employee values(103,'Jason Bourne','Developer');
insert into employee values(104,'Jane Smith','Sale Manager');
insert into employee values(105,'Rita Patel','DBA');
insert into department values(1,'Sales','Los Angeles');
insert into department values(2,'Technology','San Jose');
insert into department values(3,'Marketing','Los Angeles');
Aşağıda girilen komutla insert-data.sql içindeki tüm veriler company.db veritabanına aktarılır.
$ sqlite3 company.db < insert-data.sql
Verilerin görüntülenmesi
Veriler girildikten sonra select komutu ile tablolardaki veriler görüntülenebilir. Aşağıda oluşturulmuş olan employee ve department tabloları ve içerdikleri veriler gösterilmiştir.
$ sqlite3 company.db
SQLite version 3.8.10.1 2015-05-09 12:14:55
Enter ".help" for usage hints.
sqlite> select * from employee;
101|John Smith|CEO
102|Raj Reddy|Sysadmin
103|Jason Bourne|Developer
104|Jane Smith|Sale Manager
105|Rita Patel|DBA
sqlite> select * from department;
1|Sales|Los Angeles
2|Technology|San Jose
3|Marketing|Los Angeles
limit komutu girilerek tablodan alınan data limitlendirilebilir. Aşağıda girilen komutla employee tablosundan yalnızca iki sıra görüntülenir.
$ sqlite3 company.db
SQLite version 3.8.10.1 2015-05-09 12:14:55
Enter ".help" for usage hints.
sqlite> select * from employee limit 2;
101|John Smith|CEO|3|
102|Raj Reddy|Sysadmin|2|
limit komutunun bir de offset opsiyonu vardır. Örneğin tablodaki yalnızca üçüncü satır isteniyorsa "limit 1 offset 2" yazılmalıdır.
sqlite> select * from employee limit 1 offset 2;
103|Jason Bourne|Developer|2|
Tabloya yeni bir isim verme
Aşağıdaki örnekte department isimli tablonun ismi dept olarak değiştirilmiştir.
sqlite> alter table department rename to dept;
Varolan tabloya kolon ekleme
Employee tablosuna departman id numaralarını içeren kolon eklemek için;
sqlite> alter table employee add column deptid integer;
Veriler update komutuyla kolona yerleştirilir.
sqlite> update employee set deptid=3 where empid=101;
sqlite> update employee set deptid=2 where empid=102;
sqlite> update employee set deptid=2 where empid=103;
sqlite> update employee set deptid=1 where empid=104;
sqlite> update employee set deptid=2 where empid=105;
Employee tablosu görüntülenerek verilerin yerleşimi gözlemlenebilir.
sqlite> select * from employee;
101|John Smith|CEO|3
102|Raj Reddy|Sysadmin|2
103|Jason Bourne|Developer|2
104|Jane Smith|Sale Manager|1
105|Rita Patel|DBA|2
Veritabanındaki tüm tabloları görüntüleme
.tables komutu girilerek içinde bulunulan veritabanındaki tüm tablolar görülebilir. company veritabanında bulunan iki tablo aşağıda görülmektedir. Daha kapsamlı bir liste isteniyorsa sqlite_master komutu ile sorgu yapılabilir.
sqlite> .tables
dept employee
sqlite> select * from sqlite_master;
table|employee|employee|2|CREATE TABLE employee(empid integer,name varchar(20),title varchar(10), deptid integer, updatedon date)
table|dept|dept|3|CREATE TABLE "dept"(deptid integer,name varchar(20),location varchar(10))
.dump komutu ile veritabanı içerisindeki bütün tabloların oluşturulması için gerekli olan SQL komutları (create ve insert) ekranda görüntülenir.
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE employee(empid integer,name varchar(20),title varchar(10), deptid integer, updatedon date);
INSERT INTO "employee" VALUES(101,'John Smith','CEO',3,NULL);
INSERT INTO "employee" VALUES(102,'Raj Reddy','Sysadmin',2,NULL);
INSERT INTO "employee" VALUES(103,'Jason Bourne','Developer',2,NULL);
INSERT INTO "employee" VALUES(104,'Jane Smith','Sales Manager',1,'2015-08-20 16:28:33');
INSERT INTO "employee" VALUES(105,'Rita Patel','DBA',2,NULL);
CREATE TABLE "dept"(deptid integer,name varchar(20),location varchar(10));
INSERT INTO "dept" VALUES(1,'Sales','Los Angeles');
INSERT INTO "dept" VALUES(2,'Technology','San Jose');
INSERT INTO "dept" VALUES(3,'Marketing','Los Angeles');
COMMIT;
".dump" içindekiler filtrelenebilir ve başka bir veritabanına aktarılabilir. Aşağıda employee tablosu, sed komutuyla employee2 olarak değiştirilip bu dosya company2.db veritabanına aktarılmıştır.
$ sqlite3 company.db ".dump" | sed -e s/employee/employee2/ | sqlite3 company2.db
Yukarıdaki örnekte girilen komutlar sqlite3 interaktif modunda yazılmamıştır. Terminal ekranında girilmiştir.
~/.bash_historydosyasında tüm terminal komutlarının saklandığı gibi, SQLite programında çalıştırılan tüm komutlar da~/.sqlite_historyadlı dosyada bulunabilir.
Index oluşturma
Aşağıdaki örnekte employee tablosunda (employee id alanında) empidx adında özel bir index oluşturulması gösterilmiştir.
sqlite> create unique index empidx on employee(empid);
Index bir kez oluşturulduğunda tabloya aynı id numaralı farklı bir veri eklenmeye çalışıldığında error mesajıyla karşılaşılacaktır.
sqlite> insert into employee values (101,'James Bond','Secret Agent',1);
Error: UNIQUE constraint failed: employee.empid
Trigger oluşturma
Yapılacak olan örnek için ilk olarak employee tablosuna ismi 'updatedon' olan date kolonu eklenir.
sqlite> alter table employee add column updatedon date;
Ardından sql penceresinden '.q' komutuyla çıkılır. Terminalde trigger tanımlayan dosya oluşturulur. Bu trigger tabloda herhangi bir değişiklik gerçekleştirildiğinde "updatedon" kolonundaki zaman bilgisi güncellenir.
$ nano employee_update_trg.sql
create trigger employee_update_trg after update on employee
begin
update employee set updatedon = datetime('NOW') where rowid = new.rowid;
end;
Trigger dosyası company.db veritabanına kaydedilir.
$ sqlite3 company.db < employee_update_trg.sql
Employee tablosuna yeni bir veri eklendiğinde ya da veriler değiştirildiğinde "updatedon" kolonunda değişimin yapıldığı güncel zaman bilgisi görüntülenecektir.
$ sqlite3 company.db
sqlite> update employee set title='Sales Manager' where empid=104;
sqlite> select * from employee;
101|John Smith|CEO|3|
102|Raj Reddy|Sysadmin|2|
103|Jason Bourne|Developer|2|
104|Jane Smith|Sales Manager|1|2015-08-20 16:28:33
105|Rita Patel|DBA|2|
View oluşturma
Aşağıdaki örnekte employee ve dept tabloları kullanılarak "empdept" adında bir view oluşturulmuştur.
sqlite> create view empdept as select empid, e.name, title, d.name, location from employee e, dept d where e.deptid = d.deptid;
Oluşturulmuş olan bu view tıpkı normal bir tablo görüntüleme gibi select komutuyla görüntülenebilir.
sqlite> select * from empdept;
101|John Smith|CEO|Marketing|Los Angeles
102|Raj Reddy|Sysadmin|Technology|San Jose
103|Jason Bourne|Developer|Technology|San Jose
104|Jane Smith|Sales Manager|Sales|Los Angeles
105|Rita Patel|DBA|Technology|San Jose
View oluşturulduktan sonra .tables komutuyla company.db veritabanındaki tablolara bakıldığında empdept view de eklenmiş olduğu görülür.
sqlite> .tables
dept empdept employee
SQLite Savepoint, Rollback, Commit
Oluşturulmuş olan dept tablosu 3 veri içermektedir.
sqlite> select * from dept;
1|Sales|Los Angeles
2|Technology|San Jose
3|Marketing|Los Angeles
dept tablosunda "major" adı verilen bir savepoint oluşturularak tabloda bazı işlemler gerçekleştirilebilir. Aşağıdaki örnekte görüldüğü üzere tabloya iki adet veri eklenmiş, bir veri silinmiş ve "major" adı verilen bir savepoint oluşturulmuştur.
sqlite> savepoint major;
sqlite> insert into dept values(4,'HR','Los Angeles');
sqlite> insert into dept values(5,'Finance','San Jose');
sqlite> delete from dept where deptid=1;
sqlite> select * from dept;
2|Technology|San Jose
3|Marketing|Los Angeles
4|HR|Los Angeles
5|Finance|San Jose
Eğer üstte yapılan işlemler geri alınmak isteniyorsa savepointten önceki haline rollback yapılabilir. Aşağıda "major" savepoint olarak belirlenmiş noktadan sonra yapılan tüm işlemler rollback yapılmıştır.
sqlite> rollback to savepoint major;
sqlite> select * from dept;
1|Sales|Los Angeles
2|Technology|San Jose
3|Marketing|Los Angeles
Oluşturulmuş olan savepointlere ihtiyaç duyulmadığında release komutuyla ortadan kaldırılabilir.
sqlite> release savepoint major;
Zaman fonksiyonları
sqlite3 komutu girilerek entera basılır. Bu şekilde sqlite interaktif modda kullanıma başlanabilir. select DATETIME('NOW') komutu zamanı UTCye ya da Eşgüdümlü Evrensel Zamana dönüştürür. Komut select DATETIME('now','localtime') şeklinde girildiğinde ise güncel saat görülür.
sqlite> select datetime('now');
2015-08-18 09:28:40
sqlite> select datetime('now','localtime');
2015-08-18 12:28:43
Eğer sqlite3'ün "./src/date.c" kaynak dosyasına bakılırsa burada tarih ve saat için farklı opsiyonların olduğu görülür. Örneğin; yerel saati 10 dakika ve 3,5 saniye ileri ayarlamak için aşağıdaki komut girilir;
sqlite> select datetime('now','localtime','+3.5 seconds','+10 minutes');
2015-08-18 12:38:50
İstenilen tarihi ayarlamakta mümkündür. weekday 0=pazar, 1=pazartesi, 2=salı ... 6=cumartesi gününe değiştirir. Örneğin; aşağıdaki komutta tarih perşembe günü olarak ayarlanmıştır.
sqlite> select datetime('now','localtime','+3.5 seconds','weekday 4');
2015-08-20 12:28:53
Standart olarak zaman kolonları UTC zaman birimine göre ayarlanmıştır. employee tablosunda yerel saat ayarlanmak isteniyorsa aşağıdaki şekilde datetime komutu girilmelidir.
sqlite> select empid,datetime(updatedon,'localtime') from employee where empid=104;
104|2015-08-20 19:28:33
Belirtilen opsiyonlara ek olarak birde "strftime" fonksiyonu vardır. İstenildiğinde strftime fonksiyonuda kullanılabilir.
sqlite> select empid,strftime('%d-%m-%Y %w %W',updatedon) from employee where empid=104;
104|20-08-2015 4 33
Aşağıda strftime fonksiyonunun opsiyonları sıralanmıştır;
- %d day of month: 00
- %f fractional seconds: SS.SSS
- %H hour: 00-24
- %j day of year: 001-366
- %J Julian day number
- %m month: 01-12
- %M minute: 00-59
- %s seconds since 1970-01-01
- %S seconds: 00-59
- %w day of week 0-6 with Sunday==0
- %W week of year: 00-53
- %Y year: 0000-9999
Objeleri atma (silme)
Başlangıçtan itibaren oluşturulmuş olan tüm tablolar drop komutuyla ortadan kaldırılabilir.
dropkomutunun işlevini gösterirken oluşturulmuş olan tabloların kaybolmaması için öncelikle company.db veritabanı study.db olarak kopyalanmıştır ve işlemler bu kopyalanmış veritabanı üzerinden yapılmıştır.
$ cp company.db study.db
$ sqlite3 study.db
sqlite> .tables
dept empdept employee
sqlite> drop index empidx;
sqlite> drop trigger employee_update_trg;
sqlite> drop view empdept;
sqlite> drop table employee;
sqlite> drop table dept;
study.db veritabanındaki tüm tablolar ve görüntüler silinmiş olur. Kontrol etmek için;
sqlite> .tables
sqlite>
Bir tablo silindiğinde içeriğindeki tüm indexler ve triggerlarda silinmiş olur.
Operatörler
Aşağıda SQL için kullanılan ifadeler verilmiştir.
- ||
- / %
- –
- << >> & |
- < >=
- = == != <> IS IS NOT IN LIKE BETWEEN GLOB MATCH REGEXP
- AND OR
sqlite> select * from employee where empid >= 102 and empid;
102|Raj Reddy|Sysadmin|2|
103|Jason Bourne|Developer|2|
104|Jane Smith|Sales Manager|1|2015-08-20 16:28:33
105|Rita Patel|DBA|2|
sqlite> select * from dept where location like 'Los%';
1|Sales|Los Angeles
3|Marketing|Los Angeles
Attach and Detach Database
Birden fazla veritabanına sahip olunduğunda attach komutu ile veriler birbirlerine bağlanabilir. Eğer iki adet veritabanı aynı isimli fakat farklı verilere sahip birer tablo içeriyorsa, veritabanlarındaki veriler ortak bir yerde birleştirilip görüntülenebilir.
Bir örnek üzerinden açıklamak için company1.db ve company2.db isimli iki veritabanı oluşturulur. company1.db veritabanı company.db veritabanının kopyalanması ile elde edilir. Ardından company2.db veritabanı ilk adımdan itibaren açıklananlar doğrultusunda oluşturulur.
$ cp company.db company1.db
$ sqlite3 company2.db
SQLite version 3.8.10.1 2015-05-09 12:14:55
Enter ".help" for usage hints.
sqlite> create table employee(empid integer,name varchar(20),title varchar(10));
sqlite> .quit
$ nano insert-data2.sql
insert into employee values(201,'James Bond','Secret Agent');
insert into employee values(202,'Spider Man','Action Hero');
$ sqlite3 company2.db < insert-data2.sql
echo ".schema" |sqlite3 company1.db | sqlite3 company2.dbkomutu girilerek de veritabanı oluşturulabilir.
Aşağıda hazırlanmış olan iki adet veritabanı mevcuttur (company1.db ve company2.db). Sqlite ile bu iki veritabanına c1 ve c2 adı verilir.
$ sqlite3
sqlite> attach database 'company1.db' as c1;
sqlite> attach database 'company2.db' as c2;
.database komutuyla tüm birleştirilmiş veritabanları görüntülenir.
sqlite> .database
seq name file
--- --------------- ----------------------------
0 main
2 c1 /Users/irembuyuk/company1.db
3 c2 /Users/irembuyuk/company2.db
Bu veritabanlarındaki veriler ortak bir alanda birleştirilip görüntülenebilir.
sqlite> select empid, name, title from c1.employee union select empid, name,title from c2.employee;
101|John Smith|CEO
102|Raj Reddy|Sysadmin
103|Jason Bourne|Developer
104|Jane Smith|Sales Manager
105|Rita Patel|DBA
201|James Bond|Secret Agent
202|Spider Man|Action Hero
Veritabanları birleştirildikten sonra yine aynı sqlite bölümünden çıkmadan istenirse birbirinden ayrıştırılabilir. Bunun için detach komutu kullanılır.
sqlite> detach c1;
sqlite> .database
seq name file
--- --------------- ----------------------------
0 main
2 c2 /Users/irembuyuk/company2.db
Sorgular
SQLde arama yapılırken girilen komutlar sayesinde sorgu özelleştirilebilir.
Daha önce gösterildiği gibi görüntülenmek istenilen tablo için girilmesi gereken komut SELECT komutudur. Örnekte oluşturulmuş olan veritabanı movies.db ismindedir. 5 kolondan oluşan movies isimli bir tablo içermektedir. İlk kolonda id numarası, ikincisinde filmin ismi, üçüncüsünde türü, dördüncüsünde yılı ve sonuncu kolonda imdb puanı görülmektedir.
$ sqlite3 movies.db
SQLite version 3.8.10.1 2015-05-09 12:14:55
Enter ".help" for usage hints.
sqlite> select * from movies;
1|Avatar|action|2009|7.9
2|Jurassic World|action|2015|7.3
3|The Avengers|action|2012|8.1
4|The Dark Knight|action|2008|9.0
5|Star Wars: Episode I - The Phantom Menace|action|1999|6.6
6|Star Wars|action|1977|8.7
7|Avengers: Age of Ultron|action|2015|7.9
8|The Dark Knight Rises|action|2012|8.5
9|Pirates of the Caribbean: Dead Mans Chest|action|2006|7.3
10|Iron Man 3|action|2013|7.3
distinct komutu kolondaki aynı olan verileri eleyerek yalnızca farklı olanların görüntülenmesini sağlar. Örnekte kullanılan tablodaki film türlerine bu şekilde ulaşmak mümkündür.
sqlite> select distinct genre from movies;
action
comedy
horror
romance
drama
kolondan istenilen verileri çıktısını elde edilebilir. Bunun için where komutu kullanılabilir. imdb puanlarının istenilen değerin üzerinde ya da altında kalan filmler görüntülenebilir. where komutuyla birlikte Operatörler bölümünde anlatılan işaretler kullanılabilir.
sqlite> select * from movies where imdb_rating > 8.5;
4|The Dark Knight|action|2008|9.0
6|Star Wars|action|1977|8.7
30|Inception|action|2010|8.8
32|Star Wars: Episode V - The Empire Strikes Back|action|1980|8.8
56|Inside Out|comedy|2015|8.6
148|Forrest Gump|romance|1994|8.8
199|Saving Private Ryan|drama|1998|8.6
201|Interstellar|drama|2014|8.7
219|Se7en|drama|1995|8.6
where komutuyla birlikte like komutu kullanılabilir. Bu komut benzer değerleri karşılaştırırken oldukça elverişlidir. Örnekte aynı adlı fakat yazılışları farklı olan iki film aratılmıştır. Bunun için movies tablosundaki film isimlerinin olduğu kolonda filmin bilinen harfleri girilmiştir. '_' işareti yerine herhangi bir karakter gelebileceği anlamına gelir. '%' işaretide kullanılabilen bir diğer özel karakterdir. d% d harfiyle başlayan tüm filmleri listeler. %d şeklinde yazıldığındaysa d harfiyle sonlanan tüm filmler listelenir. Eğer paternin hem önünde hem de arkasınde yer alırsa %man% filmin içerisinde patern aratılır.
sqlite> select * from movies where name like 'Se_en';
219|Se7en|drama|1995|8.6
220|Seven|drama|1979|6.1
sqlite> select * from movies where name like 'd%';
55|Despicable Me 2|comedy|2013|7.5
68|Despicable Me|comedy|2010|7.7
122|Dracula|horror|1992|7.5
128|Dark Shadows|horror|2012|6.2
200|Dawn of the Planet of the Apes|drama|2014|7.7
203|Dances with Wolves|drama|1990|8.0
sqlite> select * from movies where name like '%man%';
9|Pirates of the Caribbean: Dead Mans Chest|action|2006|7.3
10|Iron Man 3|action|2013|7.3
11|Spider-Man|action|2002|7.3
14|Spider-Man 2|action|2004|7.3
18|Spider-Man 3|action|2007|6.2
21|Iron Man|action|2008|7.9
23|Iron Man 2|action|2010|7.1
31|Man of Steel|action|2013|7.2
34|The Amazing Spider-Man|action|2012|7.1
39|Batman|action|1989|7.6
164|Pretty Woman|romance|1990|6.9
208|Rain Man|drama|1988|8.0
LIKEopsiyonu spesifik bir patern arama yöntemi değildir.%man%aratıldığında dikkat edilirse çıktılarda hem "Batman" hem de "Man of Steel" olduğu görülecektir.
Kolonlarda arama yapılırken kullanılabilecek bir diğer komut between komutudur. Bu komutla girilen harfler ya da rakamlar aralığında kalan satırlar ekrana basılır. Komuttan sonra girilen ilk harfle ikinci harf aralığındaki(ikinci girilen harf hariç) tüm harflerle başlayan satırlar ekranda görüntülenir. Örnekte 'D' ve 'F' aralığındaki harflerle (d,e) başlayan filmler görülmektedir. Örneğin devamında ise kolon olarak yıl seçilerek 1900 ile 1980 yılları(1980 dahil) arasında yapılmış olan filmler görülmektedir.
sqlite> select * from movies where name between 'D' AND 'F';
55|Despicable Me 2|comedy|2013|7.5
68|Despicable Me|comedy|2010|7.7
122|Dracula|horror|1992|7.5
128|Dark Shadows|horror|2012|6.2
139|End of Days|horror|1999|5.7
168|Elf|romance|2003|6.9
200|Dawn of the Planet of the Apes|drama|2014|7.7
203|Dances with Wolves|drama|1990|8.0
sqlite> select * from movies where year between 1900 AND 1980;
6|Star Wars|action|1977|8.7
32|Star Wars: Episode V - The Empire Strikes Back|action|1980|8.8
109|Jaws 2|horror|1978|5.7
116|The Amityville Horror|horror|1979|6.2
157|Gone with the Wind|romance|1939|8.2
161|Snow White and the Seven Dwarfs|romance|1937|7.7
174|The Sound of Music|romance|1965|8.0
194|Jaws|drama|1975|8.1
220|Seven|drama|1979|6.1
Çıktıları order by komutuyla sıralamak mümkündür. desc opsiyonu azalan(descending) sıralama ile, asc opsiyonu artan(ascending) sıralama ile veriyi görüntüler. Bu komutlar istenildiğinde limit ile sınırlandırılabilir. Örneğin en düşük imdb puanına sahip 5 film örnekteki gibi görüntülenebilir.
sqlite> select * from movies order by imdb_rating asc limit 5;
171|Fifty Shades of Grey|romance|2015|4.2
79|Alvin and the Chipmunks: The Squeakquel|comedy|2009|4.4
143|Anaconda|horror|1997|4.6
150|The Twilight Saga: New Moon|romance|2009|4.6
113|The Haunting|horror|1999|4.9
Gruplama fonksiyonları
Gruplama fonksiyonları (aggregate functions) sayesinde birden çok sayıdaki girdiden tek bir sonuç elde etmek mümkün hale gelir. Örneğin istenilen bir kolona ait verilerin toplanması ya da kolondaki verilerinin ortalaması bu komutlarla hesaplanabilir.
Bir tablodaki satır sayısını saymanın en hızlı yolu count komutudur. Bu komut boş(NULL) olmayan tüm satırları sayar. Örnekte fakeapps adlı bir tablo kullanılmıştır. Bu tablo 5 kolondan oluşmaktadır. İlk kolonda id numarası, ikincisinde uygulama isimleri, üçüncüsünde kategorisi, dördüncüsünde indirilme sayısı ve sonuncu kolonda ücreti görülmektedir.
$ sqlite3 fakeapps.db
SQLite version 3.8.10.1 2015-05-09 12:14:55
Enter ".help" for usage hints.
sqlite> select count(*) from fakeapps;
201
Gruplama fonksiyonlarıyla yapılan işlemler veriler gruplandırıldığında daha anlamlı hale gelir. select komutuyla birlikte group by kullanılabilir. Bu komut sayesinde istenilen veriler biraraya getirilir.
selectkomutuyla seçilen kolonungroup bykomutunun kullanıldığı kolonla aynı olmasına dikkat ediniz.
Örnekte 20.000den fazla indirilmiş olan uygulamalar, fiyatlarına göre gruplandırılıp sayılmıştır. İlk kolon ücreti, ikinci kolon o ücretteki uygulama sayısını göstermektedir.
sqlite> select price, count(*) from fakeapps where downloads >20000 group by price;
0.0 26
0.99 17
1.99 18
2.99 7
3.99 5
14.99 5
sum() komutuyla kolonda girilen tüm sayıların toplamı bulunur. Örnekte her bir kategoriden indirilen uygulamaların toplam sayısı hesaplanmıştır.
sqlite> select sum(downloads) from fakeapps;
3339953
sqlite> select category, sum(downloads) from fakeapps group by category;
Books 160864
Business 178726
Catalogs 186158
Education 184724
Entertainment 95168
Finance 178163
Food & Drink 90950
Games 256083
Health & Fitness 165555
Lifestyle 166832
Medical 77191
Music 59367
Navigation 152114
News 103259
Photo & Video 184848
Productivity 135004
Reference 162032
Social Networking 126549
Sports 176988
Travel 242116
Utilities 96099
Weather 161163
max() ve min()komutları kolondaki en büyük ya da en küçük değeri bulmak için kullanılabilir. avg() komutuyla ise kolondaki sayıların aritmetik ortalama değerini hesaplanır. round() fonksiyonu ile ortalaması alınan değerin virgülden sonraki kısımları ayarlanabilir.
sqlite> select min(downloads) from fakeapps;
1387
sqlite> select max(downloads) from fakeapps;
31090
sqlite> select avg(downloads) from fakeapps;
16616.6815920398
sqlite> select round(avg(downloads)) from fakeapps;
16617.0
Her bir ücret için indirilme sayılarının ortalamasını hesaplarken girilmesi gereken komut;
sqlite> select price, avg(downloads) from fakeapps group by price;
0.0|15762.2602739726
0.99|15971.511627907
1.99|16952.5952380952
2.99|17725.380952381
3.99|18741.6666666667
14.99|19368.5833333333
0.0
6|17193.0
Yukarıdaki örnekte indirilme sayılarının ortalamasında virgülden sonraki rakam sayısı 2'ye düşürülerek daha rahat okunabilir bir hale getirilebilir.
sqlite> select price, round(avg(downloads),2) from fakeapps group by price;
0.0|15762.26
0.99|15971.51
1.99|16952.6
2.99|17725.38
3.99|18741.67
14.99|19368.58
0.0
6|17193.0
Multiple tables
Aynı veritabanında birden fazla tablo bulunabilir. Bu tablolardan istenilen kolonlar aynı çıktıda görüntülenebilir.
Örnekte oluşturulmuş olan veritabanında iki adet tablo bulunmaktadır. İlk tablo id numarasıyla artistlerin adlarını içeren artists tablosu, ikincisi ise id numarası, albüm ismi, artist id numarası ve albüm yılını içeren albums tablosudur. İki ayrı tablodan albüm bilgilerini, yıllarını ve sanatçı adlarını birlikte görüntülemek mümkündür. Bunun için select komutundan sonra ilk olarak tablonun ismi ardından .kolon şeklinde istenilen kolonun ismi yazılmalıdır. Her iki tabloda kullanıldığı için ikisininde adı belirtildikten sonra çıktı listelenir.
sqlite> select albums.name, albums.year, artists.name from albums, artists;
A Hard Days Night 1964 The Beatles
A Hard Days Night 1964 Elvis Presley
A Hard Days Night 1964 Michael Jackson
A Hard Days Night 1964 Madonna
A Hard Days Night 1964 Elton John
A Hard Days Night 1964 Led Zeppelin
A Hard Days Night 1964 Pink Floyd
Elvis Presley 1956 The Beatles
Elvis Presley 1956 Elvis Presley
Elvis Presley 1956 Michael Jackson
Elvis Presley 1956 Madonna
Elvis Presley 1956 Elton John
Elvis Presley 1956 Led Zeppelin
Elvis Presley 1956 Pink Floyd
Bu şekilde yapılan tablo birleştirme ile kullanılabilir sonuç elde etmek zordur. Görülen çıktıda artists tablosundan gelen her bir satırla albums tablosundan gelen tüm satırlar eşleştirilmiştir (kartezyen çarpımı). Yalnızca sanatçı tarafından çıkarılmış olan albümleri gösterebilmek için join komutu kullanılmalıdır. Bu komut sayesinde iki ya da daha fazla tablo birleştirilebilir. SQLde kullanılan join tiplerinden en yaygını inner joindir.
Aşağıdaki örnekte ilk olarak albums tablosundan tüm kolonlar seçilmiştir. join artists on komutuyla ikinci tablo seçilerek bir inner join komutu girilmiştir. Son olarak girilen albums.artist_id = artists.id girdileriyle join komutu için koşul belirlenmiş olur. Burada iki tablonun birbirleriyle hangi kolonlar kullanılarak eşleneceği seçilmiş olur. SQL albums tablosundaki artist_id kolonunu alarak artists tablosundaki id kolonuyla birebir eşler ve ekrana basar.
sqlite> select * from albums join artists on albums.artist_id = artists.id;
1 A Hard Days Night 1 1964 1 The Beatles
2 Elvis Presley 2 1956 2 Elvis Presley
4 Yellow Submarine 1 1968 1 The Beatles
5 Hey Jude 1 1970 1 The Beatles
6 Like a Virgin 4 1984 4 Madonna
7 From Elvis in Memphis 2 1969 2 Elvis Presley
8 Bad 3 1987 3 Michael Jackson
9 Elton John 5 1970 5 Elton John
10 Like a Prayer 4 1989 4 Madonna
11 The Dark Side of the Moon 7 1973 7 Pink Floyd
12 Thriller 3 1982 3 Michael Jackson
14 The Wall 7 1979 7 Pink Floyd
Yukarıdaki tablonun daha düzgün görüntülenmesi isteniyorsa, sqlite çıktısı terminalde oluşturulup Linux Komutları bölümünde gösterilen
column -tkomutu ile birleştirilebilir:
sqlite3 -separator $'\t' artistalbum.db "select * from albums join artists on albums.artist_id = artists.id" | column -t -s $'\t'
SQLde kullanılabilen bir diğer join outer joindir. Outer joinlerde iki ya da daha fazla tabloyu birleştirebilir fakat inner joinden farklı olarak join komutu için girilen koşulun birbiriyle tamamen örtüşmesi gerekmez. İlk tabloda seçilen kolondaki verilerin karşılığı ikinci tabloda yoksa yani o kolon boş olsa bile outer join sayesinde ekrana basılabilir.
Albümün isminin tabloda bulunduğu fakat sanatçının isminin diğer tabloda bulunmadığı sonuçlarıda yukarıdaki örneğe eklemek istersek left join komutu kullanılabilir.
sqlite> select * from albums left join artists on albums.artist_id = artists.id;
1 A Hard Days Night 1 1964 1 The Beatles
2 Elvis Presley 2 1956 2 Elvis Presley
3 1989 2014
4 Yellow Submarine 1 1968 1 The Beatles
5 Hey Jude 1 1970 1 The Beatles
6 Like a Virgin 4 1984 4 Madonna
7 From Elvis in Memphis 2 1969 2 Elvis Presley
8 Bad 3 1987 3 Michael Jackson
9 Elton John 5 1970 5 Elton John
10 Like a Prayer 4 1989 4 Madonna
11 The Dark Side of the Moon 7 1973 7 Pink Floyd
12 Thriller 3 1982 3 Michael Jackson
13 Unorthodox Jukebox 2012
14 The Wall 7 1979 7 Pink Floyd
Aşağıdaki şekide SQLde kullanılabilen join çıktı sonuçları gösterilmiştir (kaynak).
Sorular
- 2010 yılından sonra çevrilmiş, imdb puanı 5'ten küçük olan filmlerin isimlerini bulunuz.
select name from movies where year >2010 and imdb_rating<5; The Twilight Saga: Breaking Dawn - Part 1| Fifty Shades of Grey
- Aksiyon türünde 1990-2000 yılları arasında çevrilmiş filmleri imdb puanına göre (artan sırada) sıralayınız.
sqlite> select * from movies where genre like 'action' and year between 1990 and 2000 order by imdb_rating asc; 42|Twister|action|1996|6.3 49|The Lost World: Jurassic Park|action|1997|6.5 5|Star Wars: Episode I - The Phantom Menace|action|1999|6.6 27|Independence Day|action|1996|6.9
- Fakeapps veritabanındaki uygulamalardan en çok uygulama indirilen ilk 3 kategoriyi bulunuz.
sqlite> select category, sum(downloads) from fakeapps group by category order by sum(downloads) desc limit 3; Games|256083 Travel|242116 Catalogs|186158
Sqlite3 ile terminal etkileşimi
Veri tabanı komutlarının terminal karşılıkları
Yukarıdaki resimde (kaynak) terminal komutlarının gerçekleştirdiği işlemleri veritabanlarında yapabilen SQL komutları özetlenmiştir.
NoSQL veritabanı programları
SQL ve NoSQL karşılaştırması
NoSQL kategorileri
