My personal blog around GNU/Linux

Posts tagged ‘sqlite3 basics’

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.


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.