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