Introduction to SQlite3 Shell Commands
SQLite3 is a lightweight and easy-to-learn database management system, making it a popular choice for beginners learning about databases. It is highly compatible with various programming languages, including Python, PHP, C, and C++. SQLite3 shell commands are terminal-based commands that are easy to understand and use.
Ready to dive into SQLite3? We’ve got a list of 20 essential sqlite3 shell commands with examples. The best way to learn is by doing, so roll up your sleeves and start practicing!
Preparation
First, we will start sqlite3. So, type ‘sqlite3’ in the terminal and it will start. If sqlite3 isn’t installed in your system, you can follow these steps to install sqlite3 easily.
Attach to a database
We will have to attach our database to sqlite3 first but for the first time, this command will create a new database automatically.
sqlite3>attach 'database_name.db' as 'alias';
#example
sqlite3>attach 'my_database.db' as 'my';
Detaching Databases
When we wanted to go to our another database, we will first have to disconnect the current database. Hence, we will disconnect from the current database by using the following command.
sqlite3>DETACH DATABASE 'currentDB';
#example
sqlite3>DETACH DATABASE 'my';
Show all the tables inside the attached database
After connecting to the database, we would want to know the names of all the tables that exists in it. Therefore, we will use this command them.
sqlite3>.tables
View Table Structure
We know which table we need, but we also need to know its structure. Specifically, we want to know the names of the columns in the table and what kind of data each column stores.
sqlite3>.schema table_name
#example
sqlite3>.schema symbol
Enabling header/to view header row
If the header row does not show while printing all the data of a table, we can enable it using this command.
sqlite3>.header on
Showing all Databases
We can get the names of all the available databases by using this command.
sqlite3>.databases
Creating a Database/first quit from sqlite3
When we want to create a new database, we will first have to exit from sqlite3. Hence, to exit, we can use .quit command. After exiting, we can use this command to create a new database.
Syntax: sqlite3 database_name.db
sqlite3>.quit
sqlite3 test1.db
Creating Dump
To backup our database, we can use this command. It will backup all the data in the database.
Syntax: database_name.db .dump > database_name.sql
sqlite3>testDB.db .dump > testDB.sql
Restoring from Dump
To restore our data from the backup, we can use this command. It will restore our data like before.
Syntax: database_name.db < database_name.sql
sqlite3>testDB.db < testDB.sql
Exit the sqlite3
When we want to exit from sqlite3, we use .quit command to exit.
sqlite3>.quit
Select all the data in a table
To print all the data in any table, we can use this command. The * here means that we would like to select all the data.
sqlite3>select * from table_name;
#example
sqlite3>select * from symbol;
Selecting specific columns in a table
Unlike before, we will select specific columns here. This will print all the rows but from only selected columns.
sqlite3>select column_1,column_2 from table_name;
#example
sqlite3>select data,high from symbol;
Show output in ascending order
To print our data in ascending order, we will use asc at the end of the command.
sqlite3>select * from table_name order by column_1 asc;
#example
sqlite3>select * from symbol order by high asc;
Show output in descending order
In like manner, we will use desc if we would like to print our data in descending order.
sqlite3>select * from table_name order by column_1 desc;
#example
sqlite3>select * from symbol order by high desc;
To get last 10 data
We might now know which data is stored in a table by its name alone. In such cases, we can print only 10 rows of any table to see the stored data and get to our desired table.
sqlite3>SELECT * FROM my_table ORDER BY rowid desc limit 10;
sqlite3>SELECT * FROM my_table ORDER BY column_1 desc limit 10;
#example
sqlite3>SELECT * FROM symbol ORDER BY high desc limit 10;
To get first 10 data
In like manner, we can see first 10 rows of any table by using the following command.
sqlite3>SELECT * FROM my_table ORDER BY rowid asc limit 10;
sqlite3>SELECT * FROM my_table ORDER BY column_1 asc limit 10;
#example
sqlite3>SELECT * FROM symbol ORDER BY high asc limit 10;
Create a table
For creating a table in the attached database, we will write a command like this.
We have described our column names and the types of data it is going to store. We have also mentioned that the data cannot be Null which means it cannot be empty. In the Address row, we have defined a character limit to 50, therefore, it will not accept an address with 51 or more characters.
sqlite3>CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
Inserting data into the table
At the same time, we will insert data into our table. To insert data into the table, we can use a command like this where we mention the columns of the table.
sqlite3>INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
sqlite3>INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
sqlite3>INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
Inserting data directly into the table
Or, we can directly insert into table while knowing the types of values which each column accepts.
sqlite3>INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
Making commit to database
After inserting our data, we will have to commit our changes to the database, consequently, we will use this command for it.
sqlite3>conn.commit()
More information?, we have created sqlite3 database using Python and have performed various operations on it. Like storing, retrieving and querying the database as per our requirements. You can check it out by clicking here.
Leave a Reply