Category: Ubuntu
Added: 9th of December 2022
Updated On: 25th of November 2023
Viewed: 1,470 times
This article was updated and rechecked on 25th of November 2023
Install SQLite DB Browser, Create a new table, Insert and query data in Ubuntu and Ubuntu based distributions
Unlike conventional databases such as MySQL / MariaDB, SQLite is small self contained database that can be installed and manipulated locally.
To get started with SQLite we first need to install the SQLite Browser in your Debian based Linux distribution.
Open the terminal and enter the following command
sudo apt-get install sqlitebrowser
Once installed SQLite Browser can be launched from the main menu in your distribution.
Create a new database, table and fields
Next we are going to create a small database named
websites, and a table named
website_list
The small database will store website names and addresses
Click the New Database button, for the database name enter
websites, then click Save.
After this SQLite browser will ask you to enter the table name.
Enter
website_list for the table name.
Next we need to set up the fields for our database.
Click the Add button and set up three fields with the following parameters
Name: website_id
Type: INTEGER
Select AI to select AUTO INCREMENT, PK will automatically be checked.
Name: website_name
Type: TEXT
Name: website_address
Type: TEXT
Once the fields have been set up press OK, then click the Write Changes to save the database and structure.
CREATE TABLE "website_list" (
"website_id" INTEGER,
"website_name" TEXT,
"website_address" TEXT,
PRIMARY KEY("website_id" AUTOINCREMENT)
);
If you look under your home directory you will see the websites.db file.
INSERT data to the website_list table
Next we are going to add some records to the
website_list table using the INSERT command
Click the Execute SQL button, and copy and paste the SQL query below.
Click the small arrow to execute the query. If successful a message will be displayed in the pane below.
INSERT INTO website_list (website_name,website_address)
VALUES('My Computer Tips','https://www.mycomputertips.co.uk');
Select the
website_list table and browse data, you can see the first record that we added.
Let's add another record. Select Execute SQL button again, copy and paste the query below.
Press the blue arrow to execute the query
INSERT INTO website_list (website_name,website_address)
VALUES('Gaming On Linux','https://www.gamingonlinux.com/');
Select table and browse data, you can now see our second record.
Querying the website_list database
Now we have some data in the
website_list table, we are now going to query it.
We want to find out what record is displayed at
website_id=1
Select the Execute SQL button and copy and paste the SQL code below
SELECT * FROM website_list WHERE website_id=1;
We want to find out what record is displayed at
website_id=2
Select the Execute SQL button and copy and paste the SQL code below
SELECT * FROM website_list WHERE website_id=2;
We want to find the record where
website_name=My Computer Tips;
SELECT * FROM website_list WHERE website_name='My Computer Tips';
In this tutorial we installed SQLite Browser. We then created a new database and table. We inserted some data, and then ran an SQL Query against it.