![]() The dot commands provide useful functionality beyond plain SQL. There are many more handy dot commands and. backup: Creates a backup copy of the opened database. open FILE: Closes current database and opens specified file facilitates database switching. output, but applies only to one query before reverting to stdout. output FILE: Redirects output to a specified file. dump: Converts the database or specific tables to an SQL text file useful for backups. indexes: Lists names of indexes for connected databases optional filtering available. ![]() schema: Outputs the CREATE statements for database schemas accepts regex patterns. tables: Displays names of tables in the connected database useful for exploring schemas. headers: Toggles column headers on or off for query output. mode: Sets output modes for displaying query results (e.g., list, column, csv). databases: Lists names and files of attached databases allows viewing of database connections. Some important sqlite3 dot commands include: The sqlite3 command line shell provides special “dot commands” that facilitate common tasks. Useful for tasks like archiving transaction logs. BACKUP: Makes a backup copy of an SQLite database.VACUUM: The VACUUM command rebuilds and cleans the database file, reclaiming unused space.For example, PRAGMA journal_mode sets the journaling mode. PRAGMA: The PRAGMA command controls various configuration settings of the SQLite library.ALTER USER: Modifies properties of an existing user, such as changing their password.Users can be granted privileges and roles. CREATE USER: Creates a new user account for authentication.SQLite Administration CommandsĪdministrative commands handle tasks like user management, backups, and database maintenance. They are crucial for the correct operation of SQLite databases in concurrent environments. Transactions provide consistency, atomicity, isolation, and durability. All changes within the block are discarded. This rolls back (undoes) the current transaction. All changes within the block become permanent. This commits (saves) the current transaction. All subsequent DML commands execute within the context of the transaction. Transactions allow you to bundle multiple related operations into an atomic unit. Familiarity with SELECT is a must for working effectively with SQLite.Īlso read: SQLite Data Types: An In-Depth Guide SQLite Transaction Control Commands SQLite supports nearly all standard SQL SELECT syntax like subqueries, UNIONs, GROUP BY, ORDER BY, LIMIT and much more. SELECT supports powerful filtering capabilities using WHERE clauses: SELECT * FROM users WHERE age >= 18 AND city = 'Boston' Īnd advanced JOIN operations allow selecting data across multiple related tables. This query selects the id and name columns from the users table. ![]() SELECT retrieves data from one or more tables. This provides UPSERT functionality in a single statement.Īlso read: SQLite Operators: A Comprehensive Guide SQLite Data Query Language (DQL) Commandsĭata Query Language (DQL) commands allow you to query, search, and filter data stored in the database. From the SQLite documentation:įor each row proposed for insertion, if a row with the same primary key already exists in the database, delete it first and then insert the new row. The REPLACE command does an INSERT or UPDATE depending on whether the row already exists. Similar to UPDATE, DELETE can remove multiple rows in one statement. For example: DELETE FROM users WHERE id = 123 Updates can affect multiple table rows at once. For example: UPDATE users SET name = 'John Taylor' WHERE id = 123 UPDATE modifies existing data in a table. For example: INSERT INTO users (name, email) VALUES ('John Doe', ' ') Īnd you can insert multiple rows at once: INSERT INTO users (name, email) VALUES ('Mary', ' '), ('Lee', ' ') UPDATE INSERT adds new rows of data into a table. SQLite Data Manipulation Language (DML) Commandsĭata Manipulation Language (DML) commands allow inserting, modifying, and deleting data within schema objects like tables. You can also DROP indexes, views, and triggers. For example, to remove a table: DROP TABLE users The DROP command removes objects from the database. Other usages include renaming or dropping columns, adding table constraints, and renaming tables. For example, to add a new column to a table: ALTER TABLE users ADD COLUMN phone TEXT The ALTER command modifies the structure of existing database objects. You can also use CREATE to define indexes, views, and triggers. The CREATE command creates new database objects like tables, indexes, views, and triggers.įor example, to create a new table: CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE ) The key DDL commands in SQLite are: CREATE SQLite Data Definition Language (DDL) Commandsĭata Definition Language (DDL) commands allow you to create and modify database structures.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |