SQLite Create Database Tutorial using SQLite3 Command
SQLite Create Database Tutorial using SQLite3 Command. In this post, we will introduce SQLite, its features and advantages, then move on to the SQLite3 command line.
To start SQLite is released in 2000 for the very first time. It means atomicity, consistency, isolation, durability (ACID) compliance. The SQLite is a technology, developed to manage databases and the data stored in them. It is a language or a medium of interaction used while working with a particular database called “relational database”.
Let’s proceed with this article SQLite Create Database Tutorial using SQLite3 Command.
What is SQLite?
Hence, SQLite is the medium through which all the communication with the Database Management System takes place. While processing any SQL instruction for any Database Management System, the interpretation of the task is figured out by SQLite. This tools do not deal with logical files and only deal with SQL queries.
Features of SQLite
There are certain important features of SQLite that define its functioning and the way it carries out tasks. Following is a list of all the major features of SQLite that make it very useful for Database Management Systems.
Data Manipulation Language – DML contains the instructions for data manipulation. This includes insertion, deletion and updating of the data.
Authentication and security – SQLite server provides a mechanism that ensures that only the relevant and required details are shown to the user and the actual database is secure in the Database Management System.
Transaction Control Language – TCL comprises of instructions like roll back, commit etc. Transactions form a very vital part of any Database Management Systems.
Advanced SQL – This feature works towards the queries in relation to spatial data and data mining.
Embedded SQL – This feature provides for easy processing and embedding of languages like Java, C++ etc. in case the query is in their language.
Stored Procedures – SQLite supports many features of SQL and has high performance but does not support stored procedures.
Declarative language – It is a declarative language which means that it declares and specifies the result of the command.
Use of Operators – SQLite uses several operators such as like, between, in, not in, and conditional to fetch the data efficiently and easily. These also help in applying the rules to a table.
Benefits of SQLite
The benefits of SQLite are what makes it popular and so much in demand. There are various advantages of SQLite:
Usability across all operating systems – SQLite is also use in different PCs, operating systems or laptops and even with different servers. It also allows the user to embed it onto other programmes according to the need.
Widely used/standardised – Due to its existence since quite a few years now, it has become a standard and default language which is used all across the globe allowing all the users to use it uniformly.
Ease of use and user friendly language – SQLite is very easy and interesting to learn and can retrieved the most complicated data within no time with utter ease.
Ability to integrate – Another big advantage is that you can integrate and use SQLite with other programming languages such as Python, Java or Ruby. This makes it extremely compatible and allows itself to use globally.
Can create, delete or modify – Allows you to create, delete and modify database easily using the SQLite command line.
Basic Syntax of SQLite3
The basic syntax of the SQLite3 command is shown below:
sqlite3 [options] [databasefile] [SQL]
You can connect to the SQLite3 shell using the following command:
sqlite3
You will get in to the SQLite shell as shown below:
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
To display the list of all options, run the following command:
sqlite>.help
You should see all available options in the following output:
.archive ... Manage SQL archives: ".archive --help" for details
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.cd DIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dbinfo ?DB? Show status information about the database
.dump ?TABLE? ... Dump the database in an SQL text format
If TABLE specified, only dump tables matching
LIKE pattern TABLE.
.echo on|off Turn command echo on or off
.eqp on|off|full Enable or disable automatic EXPLAIN QUERY PLAN
.excel Display the output of next command in a spreadsheet
.exit Exit this program
.expert EXPERIMENTAL. Suggest indexes for specified queries
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
.indexes ?TABLE? Show names of all indexes
If TABLE specified, only show indexes for tables
matching LIKE pattern TABLE.
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
.lint OPTIONS Report potential schema issues. Options:
fkey-indexes Find missing foreign key indexes
.load FILE ?ENTRY? Load an extension library
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE? Set output mode where MODE is one of:
ascii Columns/rows delimited by 0x1F and 0x1E
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by "|"
quote Escape answers as for SQL
tabs Tab-separated values
tcl TCL list elements
.nullvalue STRING Use STRING in place of NULL values
.once (-e|-x|FILE) Output for the next SQL command only to FILE
Create a Database in SQLite
SQLite is not using a “CREATE DATABASE” command to create a database. To create database data.db, run the following command:
sqlite3 data.db
This command will create a database with the name data.db in the same directory. You can verify your database using the following command:
sqlite> .databases
You should see the following output:
main: /home/vyom/data.db
Create a Database in a Specific Location
To create a database in a specific location, use the below syntax:
sqlite3 /directory-path/dbname
For example, to create a database data.db in the download directory, run the following command:
sqlite3 Downloads/data.db
You should see the following output:
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Next, verify the database location using the following command:
sqlite> .databases
You should see the following output:
main: /home/vyom/Downloads/data.db
You can also use the .open command to create a database in a specific location:
sqlite> .open /home/vyom/Music/data.db
Create a Database and Insert Records From a File
Additionally SQLite allows you to create a database with the same table name from the file. In this example, we will create a SQL file with all SQLite commands to create tables.
Let’s create a file tutorial.sql with the following command:
nano tutorial.sql
Add the following lines:
CREATE TABLE [Departments] (
[DepartmentId] INTEGER NOT NULL PRIMARY KEY,
[DepartmentName] NVARCHAR(50) NOT NULL
);
CREATE TABLE [Students] (
[StudentId] INTEGER PRIMARY KEY NOT NULL,
[StudentName] NVARCHAR(50) NOT NULL,
[DepartmentId] INTEGER NULL,
[DateOfBirth] DATE NULL
);
CREATE TABLE [Subjects] (
[SubjectId] INTEGER NOT NULL PRIMARY KEY,
[SubjectName] NVARCHAR(50) NOT NULL
);
CREATE TABLE [Marks] (
[StudentId] INTEGER NOT NULL,
[SubjectId] INTEGER NOT NULL,
[Mark] INTEGER NULL
);
Save and close the file, then run the following command to populate all tables from a file:
sqlite3 tutorial.db < tutorial.sql
To verify the table, run the following command to open the database:
sqlite3 tutorial.db
Next, list all tables using the following command:
sqlite> .tables
You will get all tables in the following output:
Departments Marks Students Subjects
Create a Table in SQLite
To create a table named student with some columns, run the following command:
CREATE TABLE student(id integer NOT NULL, name text NOT NULL, persontype text NOT NULL, length integer NOT NULL);
You can verify the created table using following command:
.tables
You should see the following output:
student
Insert some data into the table using the following command:
INSERT INTO student VALUES (1, "Shikha", "Patel", 100);
INSERT INTO student VALUES (2, "Richa", "Maheta", 101);
INSERT INTO student VALUES (3, "Meena", "Jethva", 102);
Verify the inserted data using the following command:
SELECT * FROM student;
You should see the content of a table in the following output:
1|Shikha|Patel|100
2|Richa|maheta|101
3|Meena|Jethva|102
To view the data based on ID, use the following command:
SELECT * FROM student WHERE id IS 2;
You should see the following output:
2|Richa|Maheta|101
Backup and Restore SQLite Database
To backup a database in SQLite, you will need to open it first. Run the following command to open a database tutorial.db:
sqlite3 /home/vyom/Music/tutorial.db
Run the following command to backup a database to the same directory:
.backup tutorial.db
If backup is successfull, you should not see any error.
To backup a database into text file, run the following command:
sqlite3 tutorial.db .dump > tutorial.dump.sql
If you want to create a backup in sqlite format, run the following command:
sqlite3 tutorial.db ".backup tutorial.backup.sqlite"
To restore a backup from the SQL formatted dump file, run the following command:
sqlite3 tutorial.db < tutorial.dump.sql
To restore a backup from the SQLite formated dump file, run the following command:
sqlite3 tutorial.db ".restore tutorial.backup.sqlite"
Thank you for reading SQLite Create Database Tutorial using SQLite3 Command.
SQLite Create Database Tutorial using SQLite3 Command Conclusion
In this SQLite Create Database Tutorial using SQLite3 Command post, we introduced SQLite and then showed you how to create a database in different ways. Also keep in mind that you can not create two databases in the same location with the same name. You don’t require any privileges to create a database in SQLite. Also there is not any method to delete a database using the DROP DATABASE statement. You will need to delete a database file to remove the SQLite database.
Why don’t you check out our SQLite content here.
