SQLite is followed by set of rules and guidelines called Syntax.
Note : SQLite is case insensitive, i.e. the word SYNTAX and syntax have the same meaning in SQLite statements.
SQLite Statements :
All the SQLite statements ends with a semicolon (;). Below is the list all the basic SQLite Syntax.
1. ANALYZE Statement :
ANALYZE; OR ANALYZE dbname; OR ANALYZE dbname.tablename;
2. AND/OR Clause :
SELECT col1, col2 FROM tablename WHERE CONDITION-1 {AND|OR} CONDITION-2;
3. ALTER TABLE Statement :
ALTER TABLE tablename ADD COLUMN coldef; OR (Rename) ALTER TABLE tablename RENAME TO newtablename;
4. ATTACH DATABASE Statement :
ATTACH DATABASE 'DbName' As 'AliasName';
5. BEGIN TRANSACTION Statement :
BEGIN; OR BEGIN EXCLUSIVE TRANSACTION;
6. BETWEEN Clause :
SELECT col1, col2 FROM table_name WHERE colname BETWEEN val1 AND val2;
7. COMMIT TRANSACTION Statement :
COMMIT;
8. CREATE INDEX Statement :
CREATE INDEX indexname ON tablename ( colname COLLATE NOCASE ); UNIQUE INDEX CREATE UNIQUE INDEX indexname ON tablename ( col1, col2, ...coln);
9. CREATE TABLE Statement :
CREATE TABLE tablename( col1 datatype, col2 datatype, PRIMARY KEY( one or more columns ) );
10. CREATE TRIGGER Statement :
CREATE TRIGGER databasename.triggername BEFORE INSERT ON tablename FOR EACH ROW BEGIN stmt1; stmt2; END;
11. CREATE VIEW Statement :
CREATE VIEW databasename.viewname AS SELECT statements;
12. CREATE VIRTUAL TABLE Statement :
CREATE VIRTUAL TABLE dbname.tablename USING weblog( access.log ); OR CREATE VIRTUAL TABLE dbname.tablename USING fts3( );
13. DELETE Statement :
DELETE FROM tablename WHERE {CONDITION};
14. DETACH DATABASE Statement :
DETACH DATABASE 'AliasName';
15. 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;
16. EXPLAIN Statement :
EXPLAIN INSERT statements; or EXPLAIN QUERY PLAN SELECT statements;
17. INSERT INTO Statement :
INSERT INTO tablename( col1, col2) VALUES ( val1, val2);
18. PRAGMA Statement :
PRAGMA pragmaname;
19. REINDEX Statement :
REINDEX collationname; REINDEX dbname.indexname;
20. ROLLBACK Statement :
ROLLBACK; OR ROLLBACK TO SAVEPOINT savepointname;
21. SAVEPOINT Statement :
SAVEPOINT savepointname;
22. RELEASE SAVEPOINT Statement :
RELEASE savepointname;
23. SELECT Statement :
SELECT col1, col2 FROM tablename;
24. UPDATE Statement :
UPDATE tablename SET col1 = val1, col2 = val2 [ WHERE CONDITION ];
25. VACUUM Statement :
VACUUM;