SQLite is a lightweight, widely used SQL database engine implemented in C. It’s known for its efficiency and its ability to interface with many programming languages. Most Linux distributions can install SQLite3 from their repositories, but these versions may be outdated or lack newer features. This tutorial will guide you through installing SQLite3 from the official source code, creating a simple database, and performing basic operations such as reading, inserting, and deleting data.
Requirements
- Ubuntu 20.04 operating system.
Installation
Before installing SQLite3, update your server and install the build-essential package:
apt-get update
apt-get install build-essential
Then, proceed with configuring SQLite3.
1. Downloading SQLite3
First, download the SQLite3 package. You can find the latest version on the SQLite download page. Copy the .tar.gz file link from the Source Code section, then use it in the command below:
cd ~
mkdir sqlite3 && cd sqlite3
wget http://www.sqlite.org/sqlite-autoconf-version.tar.gz
tar xvfz sqlite-autoconf-version.tar.gz
2. Installing and Configuring SQLite3
Navigate to the extracted directory and compile SQLite3:
cd sqlite-autoconf-version
./configure
make
sudo make install
To verify the installation, check the version with:
sqlite3 --version
SQLite can also be installed directly via the package manager with:
sudo apt update
sudo apt install sqlite3
Working with SQLite3
1. Creating a Database
Create a new SQLite database with the sqlite3 command. For instance, to create a database named VPS Sell:
sqlite3 vpssell.db
If the file vpssell.db already exists, SQLite will open a connection to it. Otherwise, it will create the file.
2. Creating a Table
SQLite databases are organized into tables. You can create a table with columns for various data types. For example, to create a table with columns for plan number, server name, server type, and price:
CREATE TABLE vpssell(
number INTEGER NOT NULL,
name TEXT NOT NULL,
vpsselltype TEXT NOT NULL,
price INTEGER NOT NULL
);
The NOT NULL constraint ensures that each field is required.
Add some sample data to the table:
INSERT INTO vpssell VALUES (2, 'Linux', 'KVM', 3.99);
INSERT INTO vpssell VALUES (3, 'Container', 'OpenVZ', 2.99);
INSERT INTO vpssell VALUES (4, 'Windows', 'KVM', 3.99);
To view the table’s contents:
SELECT * FROM vpssell;
To query entries based on a specific condition, such as price:
SELECT * FROM vpssell WHERE price = 2.99;
3. Updating Tables
To add a new column, use the ALTER TABLE command. For example, to add a column for CPU cores:
ALTER TABLE vpssell ADD COLUMN CPU INTEGER;
Update the newly added column with:
UPDATE vpssell SET CPU = 1 WHERE number = 2;
4. Deleting Entries
To delete entries based on a condition, use the DELETE command. For example, to delete all entries with a price less than or equal to 3.00:
DELETE FROM vpssell WHERE price <= 3.00;
This command will remove all servers from the vpssell table with a price of 3.00 or less.
For more detailed information on SQLite syntax, refer to the official SQLite documentation.
