Sunday, July 17, 2011

SQLite - Some basic commands

I have introduced you the SQLite database in my previous posts. I also showed a python program to work with the SQLite. Now I want you to familiar with the basic commands that are used in the SQLite3.

Open your terminal and type, sqlite3 followed by a database name and press enter. Look at the code given below,

ajay@AJAY:~$ sqlite3 ajay.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Now we can type the SQLite commands here. We can create a table in database, here ajay.db.

sqlite> create table t1(one varchar(10), two smallint);

We just created a table with name t1 and two variables one and two. We can see its structute by typing the code below,

sqlite> select * from sqlite_master;

Now we get the structure of the table.

table|t1|t1|2|CREATE TABLE t1(one varchar(10), two smallint)

This type of displaying the structure seems little bit strange. We can change the mode of display.

sqlite> .mode line

Now see the structure of the table. It will be like,

sqlite> select * from sqlite_master;
    type = table
    name = t1
tbl_name = t1
rootpage = 2
     sql = CREATE TABLE t1(one varchar(10), two smallint)

We can now add some values to the table by insert into command.

sqlite> insert into t1 values('hello!',10);
sqlite> insert into t1 values('goodbye', 20);

Now we can view the contents of the table t1.

sqlite> select * from t1;
  one = hello!
  two = 10

  one = goodbye
  two = 20

We can also change this mode of display. In other databases we see a user friendly table structured display. We can also set that in SQLite.

sqlite> .mode column
sqlite> select * from t1;
hello!      10        
goodbye     20        

But still the field names are not there. We need to set the header.

sqlite> .header on
sqlite> select * from t1;
one         two       
----------  ----------
hello!      10        
goodbye     20        

Now add two more set of values.

sqlite> insert into t1 values('welcome', 30);
sqlite> insert into t1 values('come in', 40);

Now the table have 4 set of values.

sqlite> select * from t1;
one         two       
----------  ----------
hello!      10        
goodbye     20        
welcome     30        
come in     40        

It also supports common commands that are used in other databases.

sqlite> select * from t1 order by one;
one         two       
----------  ----------
come in     40        
goodbye     20        
hello!      10        
welcome     30        

sqlite> select * from t1 order by one desc;
one         two       
----------  ----------
welcome     30        
hello!      10         
goodbye     20        
come in     40        

You can experiment on the other codes also. Later I will add more posts regarding the SQLite.

Thanks

AJAY

No comments:

Post a Comment