SQLite
SQLite Index, Trigger & View with Example
In the daily use of SQLite, you will need some administrative tools over your database. You can...
SQLite databases are very lightweight. Unlike other database systems, there is no configuration, installation required to start working on an SQLite Open database.
What you need is the SQLite library which is less than 500KB size. We will jump start working on SQLite databases and tables directly.
In this SQLite tutorial, you will learn how to access SQLite database and use it-
Unlike other database management systems, there is no CREATE DATABASE command in SQLite. In this SQLite tutorial, here is how you can create a new database:
sqlite3 SchoolDB.db
.databases
This will give you the list of databases created, and you should see the new database "SchoolDB.db" listed there:
If you want to learn how to open SQLite file and create the database file in a specific location rather than in the same location where the sqlite3.exe is located, here is how to view SQLite database:
.open c:/users/mga/desktop/SchoolDB.db
Note that, the same command will be used to open the database file if the database file is already created. So if you write the same exact command again you will open the database itself:
.open c:/users/mga/desktop/SchoolDB.db
SQLite will check the file name "SchoolDB.db" whether it is found in the same location or not. If the file exists, it will open it. Otherwise, a new database will be created with the same file name specified in the specified location.
If you have a .SQL file that contains the tables schema and you want to create a new database with the same tables from that file, in the following example, we will explain how to do this.
Example:
In the following example, we will create the sample database. We will use this sample database throughout the SQLite tutorial, with the name "SQLiteTutorialsDB" and populate it with the tables. As following:
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 );
The code above will create four tables as following:
sqlite3 SQLiteTutorialsDB.db < SQLiteTutorialsDB.sql
.open SQLiteTutorialsDB.db
.tables
To back up a database, you have to open that database first as follows:
.open c:/sqlite/sample/SchoolDB.db
this command will open a database that is located on the following directory "c:/sqlite/sample/"
.open SchoolDB.db
.backup SchoolDB.db
Unlike other Database management systems, there is no DROP DATABASE SQLite command. If you want to drop database SQLite, all you have to do is to delete the database file.
Notes:
In the daily use of SQLite, you will need some administrative tools over your database. You can...
The data modification clauses in SQLite are INSERT, UPDATE, and DELETE statements. It is used for...
$20.20 $9.99 for today 4.6 (119 ratings) Key Highlights of SQLite PDF 159+ pages eBook Designed for...
In this article, we will see how to create tables, modify tables and dropping tables in SQLite3...
To write SQL queries in an SQLite database, you have to know how the SELECT, FROM, WHERE, GROUP...
Data types in SQLite are different compared to other database management system. In SQLite, you...