This tutorial is a small introduction to databases, SQL language and 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: [email protected] _______________________________________________ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
