Updated On: Tutorial updated and rechecked on 25th of November 2023
Views: 1,565
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.
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.
