Bash – Sqlite3

Wanted to use a very light weight file based Database Engine and Sqlite3 was my 1st choice. I am making this post on basic commands on Sqlite3 for my anytime reference.

Installing

sudo apt-get install sqlite3

Starting and using Basic commands

1. $sqlite3 db_name.db – creates a new db with the give name
2. sqlite3>.databases – list the available databases
3. sqlite3>.tables – list the existing tables inside the selected database
4. sqlite3>.schema tablename – displays the structure of the table.
5. sqlite3>create table tablename (field1 datatype, field2 datatype, etc…);
   Eg:- create table test(id INTEGER PRIMARY KEY AUTOINCREMENT, name CHAR(40));
6. sqlite3>insert into tablename(fields) values(values);
   Eg:- insert into test(NULL,”Pras”); or
   insert into test(id,name) values(NULL,”Pras”);
7. sqlite3>update tablename set fieldname=value where fieldname=value;
   Eg:- update test set name=”Prasanna” where name=”Pras”;
8. sqlite3>select * from tablename;
   Eg:- select * from test; or
   select * from test where name=”Prasanna”;
9. sqlite3>drop table test; – deletes my table even from the entry
10. sqlite3>delete from tablename – deletes the values and schema of the table.
11. sqlite3>.quit – Get out of sqlite shell prompt and return to Bash.