09-03-2024, 02:33 AM
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.
Example: To open or create a database called `example.db`:
2. Creating a Table
Description: Creates a new table in the SQLite database with specified columns and data types.
Example: To create a table named `users` with `id`, `name`, and `email` columns:
3. Inserting Data into a Table
Description: Inserts a new row of data into a specified table.
Example: To insert a new user into the `users` table:
4. Querying Data from a Table
Description: Selects and retrieves data from a table based on specified conditions.
Example: To retrieve all users from the `users` table:
5. Updating Data in a Table
Description: Updates existing data in a table based on specified conditions.
Example: To update the email address of the user with `id = 1`:
6. Deleting Data from a Table
Description: Deletes rows from a table based on specified conditions.
Example: To delete the user with `id = 1` from the `users` table:
7. Dropping a Table
Description: Removes a table and all its data from the database.
Example: To drop the `users` table:
8. Importing Data from a CSV File
Description: Imports data from a CSV file into a specified table.
Example: To import data from `users.csv` into the `users` table:
9. Exporting Data to a CSV File
Description: Exports data from a table to a CSV file.
Example: To export all data from the `users` table to `output.csv`:
10. Showing All Tables in the Database
Description: Displays a list of all tables in the current SQLite database.
Example: To show all tables:
11. Describing a Table Structure
Description: Shows the schema (structure) of a specified table, including its columns and data types.
Example: To show the structure of the `users` table:
12. Exiting the SQLite3 Shell
Description: Exits the SQLite3 command-line interface.
Example: To exit the SQLite3 shell:
13. Executing SQL from a File
Description: Executes SQL commands from a specified file.
Example: To execute SQL commands from `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.
Example: To back up the database to `backup.db`:
15. Restoring a Database from a Backup
Description: Restores the database from a backup file.
Example: To restore the database from `backup.db`:
16. Displaying Help Information
Description: Displays help information for the SQLite3 command, listing all available commands and options.
Example: To display help information in SQLite3:
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!
**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]Code:
sqlite3 example.db2. 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], ...);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], ...);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];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];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];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];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]Code:
.mode csv
.import users.csv users9. Exporting Data to a CSV File
Description: Exports data from a table to a CSV file.
Code:
.mode csv
.output [CSVFilePath]
SELECT * FROM [TableName];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:
.tablesCode:
.tables11. Describing a Table Structure
Description: Shows the schema (structure) of a specified table, including its columns and data types.
Code:
.schema [TableName]Code:
.schema users12. Exiting the SQLite3 Shell
Description: Exits the SQLite3 command-line interface.
Code:
.quitCode:
.quit13. Executing SQL from a File
Description: Executes SQL commands from a specified file.
Code:
.read [SQLFilePath]Code:
.read script.sql14. 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]Code:
.backup backup.db15. Restoring a Database from a Backup
Description: Restores the database from a backup file.
Code:
.restore [BackupFilePath]Code:
.restore backup.db16. Displaying Help Information
Description: Displays help information for the SQLite3 command, listing all available commands and options.
Code:
.helpCode:
.helpConclusion
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!
