Category: SQlite

Added: 19th of November 2020

Viewed: 1,573 times

Install SQlite3 in Ubuntu and Ubuntu based distributions, create table, insert data



SQLite is a self contained database, and unlike MySQL / MariaDB does not require the same amount of configuration or a server to run. Once installed your ready to go.

SQLite also works with many different programming too, and if you understand how to query a MySQL / MariaDB database it's quite easy to pick up SQLite.

SQLite works best with standalone applications. Firefox, Thunderbird, Bleachbit are just a few applications that use SQlite.

This tutorial will just go over the basics of installing and using SQLite3 on Ubuntu and Ubuntu based distributions. Once installed we will then create a new database, table and insert some basic data. We will then query the database using some simple SQlite statements.


Step 1: Install Sqlite3


To install Sqlite3 on Ubuntu and Ubuntu based distributions enter the following command
sudo apt-get install sqlite3


Once installed you create a database using the following command
sqlite3 mycomputertips.db


I created a database file named mycomputertips with extension .db, but you can create a database file with any extension you like.

On Ubuntu and Ubuntu based distributions the database file will be saved under your home directory, /home/username




Step 2: Access The Database


To access the database file and create a new table, we need to access SQLlite.
In the terminal enter the folllowing command below. This will launch SQLlite and bring you to the SQlite prompt
sqlite3


To work with the database file, for example create new tables, insert data, we need to open it.
Opening the database file is just a matter of using the .open command and specifying the location under your home directory
.open /home/username/mycomputertips.db


To display the full path to your database file enter we use the following command
.databases


Output
main: /home/username/mycomputertips.db



Step 3: Create A Table And Insert Some Data


Next lets create a simple table called users, and create three fields, id, name, age
CREATE TABLE users (
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
'name' TEXT,
'age' INTEGER
);


Then insert some data in to the users table
INSERT INTO users (name,age)
VALUES
('John','30'),
('Paul','43'),
('Mary','36');


To display the tables in your database file enter the following command
.tables


Output
users



Step 4: Run Simple Queries Against The Database File


Select all users from the database
select * from users;


Output
1|John|30
2|Paul|43
3|Mary|36


Display users by id
select * from users where id=1;


Output
1|John|30


Display users by name
select name from users;


Output
John
Paul
Mary


Display users by age order
select * from users order by age;


Output
1|John|30
3|Mary|36
2|Paul|43


Display users by age order, oldest first
select * from users order by age desc;


Output
2|Paul|43
3|Mary|36
1|John|30


Display user by name
select * from users where name='John';


Output
1|John|30


When outputting results to the terminal, you will notice there is no spacing or headers identifying each column.
To solve this issue the following commands at the sqlite prompt
.mode columns
.headers on


Then run a query again
select * from users;


Output
id  name  age
--   ----   ---
1    John    30
2    Paul    43
3    Mary   36



Some Other Commands


To delete a table
delete table {table_name}


To delete a database file
There is no command for deleting a database, you just need to delete the .db file under your home directory

To exit SQlite either enter the following comand
.exit


or alternatively press CTRL + Z

SQlite Data Types
If you have used MySQL or MariaDB you will know there are a lot of data types when setting up your fields, but in SQlite there are only 5, these are:

NULL
INTEGER
REAL
TEXT
BLOB