Comprehensive List of SQLite3 Commands with Descriptions

[Sneakyone]

Comprehensive List of SQLite3 Commands with Descriptions

**SQLite3** is a command-line utility that allows you to interact with SQLite databases. It is widely used for creating, querying, and managing SQLite database files. Below is a detailed list of SQLite3 commands, along with descriptions and examples.

---

1. Opening an SQLite Database
Description: Opens an existing SQLite database file or creates a new one if it does not exist.

Code:

sqlite3 [DatabaseFile]

Example: To open or create a database called `example.db`:

Code:

sqlite3 example.db

2. Creating a Table
Description: Creates a new table in the SQLite database with specified columns and data types.

Code:

CREATE TABLE [TableName] ([Column1] [DataType], [Column2] [DataType], ...);

Example: To create a table named `users` with `id`, `name`, and `email` columns:

Code:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);

3. Inserting Data into a Table
Description: Inserts a new row of data into a specified table.

Code:

INSERT INTO [TableName] ([Column1], [Column2], ...) VALUES ([Value1], [Value2], ...);

Example: To insert a new user into the `users` table:

Code:

INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

4. Querying Data from a Table
Description: Selects and retrieves data from a table based on specified conditions.

Code:

SELECT [Columns] FROM [TableName] WHERE [Condition];

Example: To retrieve all users from the `users` table:

Code:

SELECT * FROM users;

5. Updating Data in a Table
Description: Updates existing data in a table based on specified conditions.

Code:

UPDATE [TableName] SET [Column1] = [Value1], [Column2] = [Value2] WHERE [Condition];

Example: To update the email address of the user with `id = 1`:

Code:

UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

6. Deleting Data from a Table
Description: Deletes rows from a table based on specified conditions.

Code:

DELETE FROM [TableName] WHERE [Condition];

Example: To delete the user with `id = 1` from the `users` table:

Code:

DELETE FROM users WHERE id = 1;

7. Dropping a Table
Description: Removes a table and all its data from the database.

Code:

DROP TABLE [TableName];

Example: To drop the `users` table:

Code:

DROP TABLE users;

8. Importing Data from a CSV File
Description: Imports data from a CSV file into a specified table.

Code:

.mode csv
.import [CSVFilePath] [TableName]

Example: To import data from `users.csv` into the `users` table:

Code:

.mode csv
.import users.csv users

9. Exporting Data to a CSV File
Description: Exports data from a table to a CSV file.

Code:

.mode csv
.output [CSVFilePath]
SELECT * FROM [TableName];

Example: To export all data from the `users` table to `output.csv`:

Code:

.mode csv
.output output.csv
SELECT * FROM users;

10. Showing All Tables in the Database
Description: Displays a list of all tables in the current SQLite database.

Code:

.tables

Example: To show all tables:

Code:

.tables

11. Describing a Table Structure
Description: Shows the schema (structure) of a specified table, including its columns and data types.

Code:

.schema [TableName]

Example: To show the structure of the `users` table:

Code:

.schema users

12. Exiting the SQLite3 Shell
Description: Exits the SQLite3 command-line interface.

Code:

.quit

Example: To exit the SQLite3 shell:

Code:

.quit

13. Executing SQL from a File
Description: Executes SQL commands from a specified file.

Code:

.read [SQLFilePath]

Example: To execute SQL commands from `script.sql`:

Code:

.read script.sql

14. Backing Up the Database to a File
Description: Creates a backup of the current database by exporting it to a new file.

Code:

.backup [BackupFilePath]

Example: To back up the database to `backup.db`:

Code:

.backup backup.db

15. Restoring a Database from a Backup
Description: Restores the database from a backup file.

Code:

.restore [BackupFilePath]

Example: To restore the database from `backup.db`:

Code:

.restore backup.db

16. Displaying Help Information
Description: Displays help information for the SQLite3 command, listing all available commands and options.

Code:

.help

Example: To display help information in SQLite3:

Code:

.help

---

Conclusion

The **SQLite3** command-line utility is a powerful tool for managing SQLite databases, making it an essential utility for developers, database administrators, and anyone working with SQLite. By mastering these commands, you can efficiently create, query, and manage your databases, ensuring effective data management and retrieval.

Happy Database Management!