Install and Use SQLite on Ubuntu 20.04 - Andy's Blog

Steps

Install SQLite

1. (Optional) Update your package list

sudo apt update

2. Install SQLite

sudo apt install sqlite3

3. Verify installation

sqlite3 --version

Use SQLite

• Create database named sharks, if file sharks.db exists, SQLite open a connection to it; if not, create a new database

sqlite3 sharks.db

• Create SQLite table

CREATE TABLE sharks(id integer NOT NULL, name text NOT NULL, sharktype text NOT NULL, length integer NOT NULL);

• Inserting values

INSERT INTO sharks VALUES (1, "Sammy", "Greenland Shark", 427);
INSERT INTO sharks VALUES (2, "Alyoshka", "Great White Shark", 600);
INSERT INTO sharks VALUES (3, "Himari", "Megaladon", 1800);

• Reading tables

SELECT * FROM sharks;
-- View entry based on id we choose
SELECT * FROM sharks WHERE id IS 1;

• Comment in SQLite

-- This is a comment

• Update tables in SQLite

-- Add columns
ALTER TABLE sharks ADD COLUMN age integer;
-- Update values
UPDATE sharks SET age = 272 WHERE id=1;
UPDATE sharks SET age = 70 WHERE id=2;
UPDATE sharks SET age = 40 WHERE id=3;

• Deleting information in SQLite

DELETE FROM sharks WHERE age <= 200;

• Joining information in SQLite

-- Create table and insert value
CREATE TABLE endangered (id integer NOT NULL, status text NOT NULL);
INSERT INTO endangered VALUES (1,  "near threatened");
-- Join tables
SELECT * FROM sharks INNER JOIN endangered on sharks.id = endangered.id;
-- Output
1|Sammy|Greenland Shark|427|272|1|near threatened

-- Specify desired output
SELECT sharks.id, sharks.name, sharks.sharktype, sharks.length, sharks.age, endangered.status FROM sharks INNER JOIN endangered on sharks.id = endangered.id;
-- Output
1|Sammy|Greenland Shark|427|272|near threatened

• Terminate sqlite3 program

Typing your system End-Of-File character (usually Control-D). Use the interrupt character (usually a Control-C) to stop a long-running SQL statement.

Reference

• How To Install and Use SQLite on Ubuntu 20.04
• SQLite - Syntax
• Command Line Shell For SQLite