SQLite Statements and Syntax

SQLite is a self-contained, file-based SQL database that follows a specific syntax. Statements in SQLite are used to query the database and are essential for database management. Every statement follows a fixed syntax in SQLite.

In this guide, we will look at some important SQLite statements and syntaxes. We have divided the statements into several categories, covering basic to advanced statements for every user.

SQLite Statement Rules

• SQLite is case-insensitive, and insensitive, meaning that keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc. are treated the same regardless of capitalization.
• All the SQLite statements end with a semicolon (;).
• SQLite uses single-line comments starting with -- and C-style comments /* */ for multi-line comments.

List all the basic SQLite Syntaxes

Here is a list of basic SQLite syntaxes for statements:

Basic SQLite Statements

These are some of the basic statements in SQLite and their syntaxes.

SELECT Statement

SELECT col1, col2
FROM tablename;

INSERT INTO Statement

INSERT INTO tablename( col1, col2)
VALUES ( val1, val2);

UPDATE Statement

UPDATE tablename
SET col1 = val1, col2 = val2
[ WHERE  CONDITION ];

DELETE Statement

DELETE FROM tablename
WHERE {CONDITION};

Data Definition Statements in SQLite

Here are some the data definition statements in SQLite, used to create table, index and view.

CREATE TABLE Statement

CREATE TABLE tablename(
  col1 datatype,
  col2 datatype,
  PRIMARY KEY( one or more columns )
);

CREATE INDEX Statement

CREATE INDEX indexname
ON tablename ( colname COLLATE NOCASE );
UNIQUE INDEX 
CREATE UNIQUE INDEX indexname
ON tablename ( col1, col2, ...coln);

CREATE VIEW Statement

CREATE VIEW databasename.viewname AS
SELECT statements;

Data Manipulation Statements in SQLite

Here are some data manipulation statements in SQLite, used to modify data of table or database.

ALTER TABLE Statement

ALTER TABLE tablename ADD COLUMN coldef;
OR (Rename)
ALTER TABLE tablename RENAME TO newtablename;

DROP Statement

INDEX
DROP INDEX database_name.indexname;
TABLE 
DROP TABLE database_name.tablename;
VIEW 
DROP INDEX database_name.viewname;
TRIGGER 
DROP INDEX database_name.triggername;

Transaction Control Statements in SQLite

Here are some transaction control statements in SQLite used to control the processing and exposure of changes.

BEGIN TRANSACTION Statement

BEGIN;
OR
BEGIN EXCLUSIVE TRANSACTION;

COMMIT TRANSACTION Statement

COMMIT;

ROLLBACK Statement

ROLLBACK;
OR
ROLLBACK TO SAVEPOINT savepointname;

Clauses in SQLite

Here are some basic syntax of clauses in SQLite

AND/OR Clause

SELECT col1, col2
FROM tablename
WHERE CONDITION-1 {AND|OR} CONDITION-2;

BETWEEN Clause

SELECT col1, col2
FROM table_name
WHERE colname BETWEEN val1 AND val2;

EXPLAIN Statement

EXPLAIN INSERT statements;
or  
EXPLAIN QUERY PLAN SELECT statements;

Advanced SQLite Statements

Here are some Advanced SQLite Staments:

CREATE TRIGGER Statement

CREATE TRIGGER databasename.triggername  
BEFORE INSERT ON tablename FOR EACH ROW
BEGIN  
  stmt1;  
  stmt2;
END;

CREATE VIRTUAL TABLE Statement

CREATE VIRTUAL TABLE dbname.tablename USING weblog( access.log );
OR
CREATE VIRTUAL TABLE dbname.tablename USING fts3( );

PRAGMA Statement

PRAGMA pragmaname;

Miscellaneous Statements

ANALYZE Statement

ANALYZE;
OR
ANALYZE dbname;
OR
ANALYZE dbname.tablename;

ATTACH DATABASE Statement

ATTACH DATABASE 'DbName' As 'AliasName';

DETACH DATABASE Statement

DETACH DATABASE 'AliasName';

REINDEX Statement

REINDEX collationname;
REINDEX dbname.indexname;

VACUUM Statement

VACUUM;

SAVEPOINT Statement

SAVEPOINT savepointname

RELEASE SAVEPOINT Statement

RELEASE savepointname;

Conclusion

SQLite statements and syntax are an essential part of working with SQLite. From basic statements to advanced statements, SQLite provides a set of queries for managing and manipulating data. Whether you are a beginner or an advanced user, SQLite statements and syntax are important to fully utilize SQLite.