[Ilugc] Sqlite database tutorial part I
Girish Venkatachalam
the simplest
way to get started on using an embedded small database for uses which
are common.
To begin with my understanding of SQL and databases has been very sketchy
and incongruous for a long time.
In recent years however things changed. Today I can claim that I put SQL to
good use.
To put things in perspective, other than crazy fellows like my ilk
most of the world
lives and moves on databases.
All your ticketing, bank transactions blah blah are done using DB.
So learning SQL is something that a lot of average programmers and
grubby folks do
grudgingly.
But properly applied, sqlite and SQL are both very pleasant.
Okay let us start from the start.
I will split this content into two mails. In this mail I will start with sqlite.
What is sqlite?
It is a very small database that supports the SQL standard or is
compliant to a large degree.
There are really small key value pair(table) databases like tdb in
samba, Berkeley DB, gdm etc.
But no SQL.
SQL = Structured Query Language
It is a method to extract data which is basically columns from a set of rows.
Okay, let us look at a trivial example.
Say you have a very simple data set(table).
A table is nothing but a name value pair.
Each row in a database is a separate data point or name value pair .
Usually it is one name and one value or multiple values.
Does not matter much. But each row is always the smallest unit of
independent data.
Let us look at it this way.
I want to store and manipulate the marks of students in a class.
There are 5 subjects, phy, che, math, Tamil and English.
Okay here you go:
Rahim 20 30 40 50 100
Syam 20 40 40 50 100
Priya 30 50 100 60 80
Okay you have 3 students with their credentials.
Now Each of them is represented in an SQL DB like this.
$ sqlite3 marks.db
> create table marks (id integer primary key, name string, phy
integer, che integer, mat integer,
eng integer, tamil integer);
> insert into marks values(NULL, "Rahim", 20, 30, 40, 50, 100);
> insert into marks values(NULL, "Syam", 20, 40, 40, 50, 100);
> insert into marks values(NULL, "Priya", 30, 50, 100, 60, 80);
> quit;
There you go.
You now managed to store them in a nice SQL formatted sqlite DB(table).
Check:
$ echo "select * from marks;" | sqlite3 marks.db
You will see values.
Right. You get it so far?
Now I have only talked about rows.
Each dataset is stored in a row, but the table schema or type is specified by
how many columns each datapoint will contain.
In this case, the columns are name and marks in each subject.
Now you can look for them.
$ echo "select phy from marks;" | sqlite3 marks.db
Or
$ echo "select phy,name from marks;" | sqlite3 marks.db
Good.
You are happy now. Aren't you?
Usually people are discombobulated by various terms that they absolutely
don't need to know when dealing about DB and SQL.
User ID, password, TCP, port and so on.
Mysql, postgres ...eeek.
Instead sqlite is a file db, no username, password nonsense.
Just plain sql. Just copy the stupid file and you get the DB.
Backing up is simple. It supports a huge dataset.
All the common software you can imagine that apparently have nothing to do with
databases use SQLite.
It is called an embeddable DB and it sure it.
But I don't care. It gets my job done, gives me the convenience and superb
power of SQL.
We will see that tomorrow. Okay?
-Girish
--
G3 Tech
Networking appliance company
web: http://g3tech.in mail: gir...@g3tech.in
_______________________________________________
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc