You are reading the article Sqlite Database: How To Create, Open, Backup &Amp; Drop Files updated in September 2023 on the website Lifecanntwaitvn.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Sqlite Database: How To Create, Open, Backup &Amp; Drop Files
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-
SQLite CREATE DatabaseUnlike 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:
Open the Windows Command Line tool (cmd.exe) from the start, type “cmd” and open it.
The “cmd” will open in the default user folder, on my machine, it is “C:UsersMGA”:
From the Installation and packages tutorial, you should now have created an SQLite folder in the “C” directory and copied the chúng tôi on it.
After that we should select SQLite file and navigate where the chúng tôi is located by the following SQLite command line:
Following is the basic syntax of sqlite3 command to create a database sqlite3 SchoolDB.db
This will create a new database with the name “SchoolDB.db”in the same directory where you have copied your .exe file.
If you select SQLite file and navigate to the directory: “c:sqlite”, you will find the file “SchoolDB.db”is created as the following screen shot:
You can ensure that the database is created by writing following SQLite commands: .databases
This will give you the list of databases created, and you should see the new database “SchoolDB.db” listed there:
SQLite CREATE Database in a Specific Location using OpenIf 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 chúng tôi is located, here is how to view SQLite database:
Navigate manually to the folder where chúng tôi is located “C:sqlite”.
The Command to open a database file is: .open c:/users/mga/desktop/SchoolDB.db
This will create a new database with the name “SchoolDB.db” and store the database file in the location chúng tôi 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.
SQLite create a database and populate it with tables from a fileIf 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:
Open a text file and paste the following SQLite commands on it: 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:
“Departments” table with the following columns:
“DepartmentId” an integer number indicates the department id, and it is declared as a PRIMARY KEY (explained later in Column Constraints Section).
“DepartmentName” – a string name for the department, and it doesn’t allow null values using NOT NULL constraint.
“Students” table with the following columns:
“StudentId” an integer number, and it is declared as a PRIMARY KEY.
“StudentName” – the name of the student and it doesn’t allow a null value using NOT NULL constraint.
“DepartmentId” Integer number that refers to the department Id to the department id column in the departments table.
“DateOfBirth” The date of birth of the student.
“Subjects” table with the following columns:
“SubjectId” an integer number and it is declared as a PRIMARY KEY.
“SubjectName” a string value and it doesn’t allow null values.
“Marks” table with the following columns:
“StudentId” integer indicates a student id.
“SubjectId” integer indicates a subject Id.
“Mark” the mark a student gets in a specific subject it is also integer and it does allow null values.
Save the file to SQLite as “SQLiteTutorialsDB.sql” in the same location where chúng tôi is located.
Open chúng tôi and navigate to the directory where chúng tôi is located.
Write the following command: sqlite3 chúng tôi < SQLiteTutorialsDB.sql
Then, a new database “SQLiteTutorialsDB” should be created, and the file “SQLiteTutorialsDB.db” will be located in the same directory. As following:
You can ensure that this table is created by opening the database we just created like this: .open SQLiteTutorialsDB.db
Then write the following command: .tables
This command will give you the list of tables in the “SQLiteTutorialsDB“, and you should see the four tables we had just created:
SQLite Backup & DatabaseTo back up a database, you have to open that database first as follows:
Open the database using the following query .open c:/sqlite/sample/SchoolDB.db
this command will open a database that is located on the following directory “c:/sqlite/sample/”
If it is in the same directory where chúng tôi is located, then you don’t need to specify a location, like this: .open SchoolDB.db
Then to back up a database write the following command:
This will back up the whole database into a new file “SchoolDB.db” in the same directory: .backup SchoolDB.db
If you don’t see any errors after executing that command, this means that the backup is created successfully.
SQLite Drop DatabaseUnlike 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:
You can’t create two databases in the same location with the same name, the database name is unique in the same directory.
Database names are case insensitive.
There are no privileges required to create the databases.
You're reading Sqlite Database: How To Create, Open, Backup &Amp; Drop Files
Update the detailed information about Sqlite Database: How To Create, Open, Backup &Amp; Drop Files on the Lifecanntwaitvn.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!