How to Configure and Use SQLite On Ubuntu 20.04 LTS

97

This tip is about the how to Configure and Use SQLite On Ubuntu 20.04 LTS. So read this free guide, How to Configure and Use SQLite On Ubuntu 20.04 LTS step by step. If you have query related to same article you may contact us.

How to Configure and Use SQLite On Ubuntu 20.04 LTS – Guide

How to install SQLite on Ubuntu 20.04 LTS, will be shown in this guide. For those who didn’t know this before, SQLite is a lightweight command-line database application. It is one of the most coherent database engines in the world. In part, thanks to the C language that makes it very methodical in managing the system’s data.

This article assumes you have a lot less knowledge of Linux, you know. how to use the shell and, most importantly, you host your website on your own VPS. Installation is quite easy and assumes you are running under the root account, otherwise you may need to add ‘sudo’ to the commands to gain root privileges. This will show you the methodical installation of SQLite on Ubuntu 20.04 (Focal Fossa). You can follow the same order for ubuntu 18.04, 16.04 and any other Debian based distribution like Linux Mint.

prerequisites

To complete this tutorial, you will need:

A local machine or server running Ubuntu 20.04. Per up a server, including a non-root sudo user and a firewall, you can create a DigitalOcean Droplet running Ubuntu 20.04 and then follow our Initial Server Setup Guide.

Step 1 – Installing SQLite on Ubuntu 20.04

To install the SQLite command-line interface on Ubuntu, first update your package list:

sudo apt update

Now install SQLite:

sudo apt install sqlite3

To verify the installation, check the software version:

sqlite3 – version

You will receive an output like this:

Exit

3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1

With SQLite installed, you are now ready to create a new database.

Step 2 – Creating an SQLite Database

In this step you will create a database containing different sharks and their attributes. To create the database, open your terminal and run this sqlite3 command:

sqlite3 sharks.db

This will create a new database called sharks. If the sharks.db file already exists, SQLite will open a connection to it; if it doesn’t exist, SQLite will create it.

You will receive an output like this:

OutputSQLite version 3.31.1 2020-01-27 19:55:54

Type “.help” for usage tips.

Then your prompt will change. A new prefix, sqlite>, now appears:

If the sharks.db file does not yet exist and if you exit sqlite promote without running any queries, the sharks.db file will not be created. To ensure the file is created, you can run an empty query by typing; and then pressing “Enter”. This way you can be sure that the database file was actually created.

With your Shark database created, you will create a new table and populate it with data.

Step 3 – Creating an SQLite Table

SQLite databases are organized into tables. Tables store information. To better visualize a table, you can imagine rows and columns.

the rest of it tutorial will follow a common convention for entering SQLite commands. SQLite commands are in uppercase and user information in lowercase. Lines must end with a semicolon.

Now let’s create a table and some columns for various data:

an ID

the name of the shark

the type of shark

The average length of the shark (in centimeters)

Use the following command to create the table:

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

Using NOT NULL makes this field mandatory. We will discuss NOT NULL in more detail in the next section.

After creating the table, an empty prompt will return. Now let’s enter some values ​​into it.

Inserting Values ​​into Tables

In SQLite, the command to insert values ​​into a table follows this general form:

INSERT INTO tablename VALUES (values ​​go here);

Where tablename is the name of your table and values ​​are in parentheses.

Now insert three VALUES rows into your shark table:

INSERT VALUES IN Sharks (1, “Sammy”, “Greenland Shark”, 427); INSERT VALUES IN Sharks (2, “Alyoshka”, “Great White Shark”, 600); INSERT VALUES IN SHARKS (3, “Himari”, “Megaladon”, 1800);

Since you previously specified NOT NULL for each of the variables in your table, you must enter a value for each.

For example, try adding another shark without defining its length:

INSERT INTO Sharks VALUES (4, “Faiza”, “Hammerhead Shark”);

You will get this error:

OutputError: table shark has 4 columns, but 3 values ​​were provided

In this step, you created a table and entered values ​​into it. In the next step you will read the table from the database.

Step 4 – Reading tables in SQLite

In this step, we’ll focus on the most basic methods of reading data from a table. Recognize that SQLite provides more specific methods for viewing data in tables.

To view your table with all values ​​entered, use SELECT:

SELECT * FROM sharks;

You will see the previously entered entries:

Output1 | Sammy | Greenland Shark | 4272 | Alyoshka | Great White Shark | 6003 | Himari | Megaladon | 1800

To view an entry based on its id (the values ​​we set manually), add the WHERE command to your query:

SELECT * FROM sharks WHERE id IS 1;

This will return the shark whose id is 1:

Output1 | Sammy | Greenland Shark | 427

Let’s take a look at this command.

1. First, we SELECT all
2. values ​​from our database, sharks.
3. Next, we look at all id values.

We then return all entries from the table where id equals 1.

So far, you’ve created a table, entered data into it, and looked at the saved data. You will now update the existing table.

Step 5 – Updating tables in SQLite

In the following two sections, you will first add a new column to the existing table and then update the existing values ​​in the table.

Adding Columns to SQLite Tables

SQLite allows you to alter your table using the ALTER TABLE command. This means you can create new rows and columns or modify existing rows and columns.

Use ALTER TABLE to create a new column. This new column will track the age of each shark in years:

ALTER TABLE sharks ADD COLUMN whole age;

You now have a fifth column, age.

Updating values ​​in SQLite tables

Using the UPDATE command, add new age values ​​for each of your sharks:

UPDATE sharks SET age = 272 WHERE id = 1; UPDATE SET sharks age = 70 WHERE id = 2; UPDATE SET sharks age = 40 WHERE id = 3;

Output1 | Sammy | Greenland Shark | 427 | 2722 | Alyoshka | Great White Shark | 600 | 703 | Himari | Megaladon | 1800 | 40

In this step, you changed the composition of your table and then updated the values ​​within it. In the next step, you will delete information from a table.

Step 6 – Deleting information in SQLite

In this step, you will delete entries from your table based on an argument’s evaluation.

In the following command, you are querying your database and asking it to delete all sharks in your shark table under the age of 200:

EXCLUDE FROM sharks WHERE age

Typing SELECT * FROM sharks; will check whether Alyoshka and Himari, who were less than 200 years old each, were excluded. Only Sammy the Greenland Shark remains:

Output1 | Sammy | Greenland Shark | 427 | 272

Step 7 – Gathering information in SQLite

Let’s imagine that we have two tables: our current shark table and an endangered table. Now what if the endangered table had an id value mapped to the ids in its shark table and also had a status value that indicated each shark’s conservation status?

1. If you want to query data from both tables, you can use one of the four SQLite join commands:
2. INTERNAL JOINT
3. OUTER JOIN
4. LEFT ASSOCIATION

CROSS JOIN

Let’s create this second table and use INNER JOIN to join some data.

First, create your endangered table:

CREATE TABLE in danger (integer id NOT NULL, status text NOT NULL); INSERT INTO endangered VALUES (1, “almost threatened”);

Now join their tables:

SELECT * FROM endangered INNER JOIN sharks in sharks.id = endangered.id;

Your output will look like this:

Output1 | Sammy | Greenland Shark | 427 | 272 | 1 | almost threatened

Note that the output also includes the Threatened id value. You can specify the desired output with a more explicit command:

SELECT sharks.id, sharks.name, sharks.sharktype, sharks.length, sharks.age, endangered.status FROM sharks INNER JOIN endangered in sharks.id = endangered.id;

This time the output excludes the second id value:

Output1 | Sammy | Greenland Shark | 427 | 272 | almost threatened

You have now successfully joined information from multiple tables.

Final note

I hope you like the guide How to Configure and Use SQLite On Ubuntu 20.04 LTS. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.