SQLite3 SQL Command Uses with Examples

SQLite is a lightweight SQL database that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

Except some of the commands, SQLite uses similar command syntax as used by MySQL, Oracle. This article will help to How to use SQLite database using the command line.

Advertisement

1. Create SQLite Database

SQLite provides a simple command to create database. Use following command to create an sqlite database.

sqlite3 tecadmin.db

Basically SQLite database is a file that gets created in your currect working directory.

ls -l tecadmin.db

-rw-r--r--. 1 root root 3072 May 11 14:32 tecadmin.db

2. Create Tables in SQLite Database

After creating database lets create tables. Use following queries to create two tables ( users, posts ) in database tecadmin.db.

sqlite3 tecadmin.db

sqlite> create table users(uid integer,uname varchar(60),category varchar(50));
sqlite> create table posts(postid integer,postname varchar(50),content varchar(1000));
sqlite> create table tmp(id integer,tname varchar(50);
sqlite> .quit

3. List or Drop Tables in SQLite

To list the table names only in SQLite database simply use following command.

sqlite> .tables
posts  tmp    users

Some times if you need to drop any table, use the following commands to do it like below.
drop table <tablename>;
drop table if exists <tablename>;

drop table tmp;
drop table if exists tmp;

4. Insert Data in Tables

Following commands are used for insert data in SQLite databases through SQLite prompt.

sqlite> INSERT INTO posts VALUES(1, 'Post 1','this is demo post 1');
sqlite> INSERT INTO posts VALUES(2, 'Post 2','this is demo post 2');
sqlite> INSERT INTO users VALUES(1,'Harry','staff');
sqlite> INSERT INTO users VALUES(2,'Rahul','Admin');

You can also execute a bundle of commands contains in a file.

vi data.sql

INSERT INTO posts VALUES(10, 'Sample Post 10','this is sample post 10');
INSERT INTO posts VALUES(11, 'Sample Post 11','this is sample post 11');
INSERT INTO users VALUES(10,'Sarah','Support');
INSERT INTO users VALUES(11,'Nick','Sales');

Following command will execute all the commands from the data.sql in the tecadmin.db database.

sqlite3 tecadmin.db < data.sql

5. Fetch Data from Tables

Use SELECT command for viewing data from tables in SQLite database like examples below.

sqlite> SELECT * FROM users;
1|Harry|staff
2|Rahul|Admin
10|Sarah|Support
11|Nick|Sales

sqlite> SELECT * FROM posts;
1|Post 1|this is demo post 1
2|Post 2|this is demo post 2
10|Sample Post 10|this is sample post 10
11|Sample Post 11|this is sample post 11

sqlite> SELECT * FROM posts WHERE postid = 1;
1|Post 1|this is demo post 1

6. Changing Output Formats

The SQLite3 shows the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs", and "tcl". Using ".mode" command you can change output format. The default output mode is "list".

sqlite> .mode line
sqlite> select * from users;
     uid = 1
   uname = Harry
category = staff

     uid = 2
   uname = Rahul
category = Admin

sqlite> .mode column
sqlite> select * from users;
1           Harry       staff
2           Rahul       Admin
10          Sarah       Support
11          Nick        Sales

7. Convert SQLite Database to ASCII Text File

You can simply covert SQLite database to a plain text file using '.dump' command. Use the following command to do.

sqlite3 tecadmin.db '.dump' > backup.dump

To reconstruct the SQLite database from ASCII file backup.dump just type:

cat backup.dump | sqlite3 tecadmin-1.db

References:
http://www.sqlite.org/sqlite.html