Syntaxe SQLite

C

SQLite is followed by a set of unique rules and guidelines known as syntax. This chapter lists all the basic SQLite syntax.

Case sensitivity

One point to note is that SQLite is case-insensitive, that is, the clauses GLOB and glob have the same meaning in SQLite statements.

Comments

SQL comments are additional comments that can be added to SQLite code to improve its readability and can appear anywhere; whitespace can be included, including within expressions and between other SQL statements, but cannot be nested.-Character ” (ASCII 0x2d) starts and extends to the next newline character (ASCII 0x0a) or until input ends, whichever comes first.

You can also use C-style comments, which start with “ / *Starting with ” and extending to the next “ * /Pairs of ” characters including the next “ * /Pairs of ” characters, or until input ends (whichever comes first). C-style comments can span multiple lines.

sqlite> .help　--　This is a single line comment

SQLite statements

All SQLite statements start with keywords such as SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc., and all statements end with a semicolon (;).

SQLite ANALYZE statement

ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;

SQLite AND / OR clause

SELECT　column1,　column2....columnN
FROM nom_de_table;
WHERE CONDITION-1　{AND|OR} CONDITION-2;

SQLite ALTER TABLE statement

ALTER TABLE table_name ADD COLUMN column_def...;

SQLite ALTER TABLE statement (rename)

ALTER TABLE table_name RENAME TO new_table_name;

SQLite ATTACH DATABASE statement

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

SQLite BEGIN TRANSACTION statement

BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;

SQLite BETWEEN clause

SELECT　column1,　column2....columnN
FROM nom_de_table;
WHERE column_name BETWEEN val-1　AND val-2;

SQLite COMMIT statement

COMMIT;

SQLite CREATE INDEX statement

CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );

SQLite CREATE UNIQUE INDEX statement

CREATE UNIQUE INDEX index_name
ON table_name ( column1,　column2,...columnN);

SQLite CREATE TABLE statement

CREATE TABLE table_name(
　　　column1　datatype,
　　　column2　datatype,
　　　column3　datatype,
　　　.....
　　　columnN datatype,
　　　PRIMARY KEY(one or more columns));

SQLite CREATE TRIGGER statement

CREATE TRIGGER database_name.trigger_name　
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN　
　　　stmt1;　
　　　stmt2;
　　　....
END;

Instructions CREATE VIEW SQLite

CREATE VIEW database_name.view_name AS
SELECT statement....;

Instructions CREATE VIRTUAL TABLE SQLite

CREATE VIRTUAL TABLE database_name.table_name USING weblog(access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );

Instructions COMMIT TRANSACTION SQLite

COMMIT;

SQLite COUNT clause

SELECT COUNT(column_name) FROM table_name
CONDITION DE WHERE;

Instructions DELETE SQLite

DELETE FROM table_name
WHERE {CONDITION};

Instructions DETACH DATABASE SQLite

DETACH DATABASE 'Alias-Name';

SQLite DISTINCT clause

SELECT DISTINCT column1,　column2....columnN
FROM table_name;

Instructions DROP INDEX SQLite

DROP INDEX database_name.index_name;

Instructions DROP TABLE SQLite

DROP TABLE database_name.table_name;

Instructions DROP VIEW SQLite

DROP INDEX database_name.view_name;

Instructions DROP TRIGGER SQLite

DROP INDEX database_name.trigger_name;

SQLite EXISTS clause

SELECT　column1,　column2....columnN
FROM nom_de_table;
WHERE column_name EXISTS (SELECT　*　FROM table_name );

Instructions EXPLAIN SQLite

EXPLAIN INSERT statement...;
or　
EXPLAIN QUERY PLAN SELECT statement...;

SQLite GLOB clause

SELECT　column1,　column2....columnN
FROM nom_de_table;
WHERE column_name GLOB { PATTERN };

Préfixe GROUP BY SQLite

SELECT SUM(column_name) FROM table_name
WHERE CONDITION
GROUP BY column_name;

SQLite HAVING clause

SELECT SUM(column_name) FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

SQLite INSERT INTO statement

INSERT INTO table_name( column1,　column2....columnN)
VALUES ( value1, value2....valueN);

SQLite IN clause

SELECT　column1,　column2....columnN
FROM nom_de_table;
WHERE column_name IN (val-1, val-2,...val-N);

SQLite LIKE clause

SELECT　column1,　column2....columnN
FROM nom_de_table;
WHERE column_name LIKE { PATTERN };

SQLite NOT IN clause

SELECT　column1,　column2....columnN
FROM nom_de_table;
WHERE column_name NOT IN (val-1, val-2,...val-N);

Préfixe ORDER BY SQLite

SELECT　column1,　column2....columnN
FROM nom_de_table;
WHERE CONDITION
ORDER BY column_name {ASC|DESC};

SQLite PRAGMA statement

PRAGMA pragma_name;
For example:
PRAGMA page_size;
PRAGMA cache_size =　1024;
PRAGMA table_info(table_name);

SQLite RELEASE SAVEPOINT statement

RELEASE savepoint_name;

SQLite REINDEX statement

REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;

SQLite ROLLBACK statement

ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;

SQLite SAVEPOINT statement

SAVEPOINT savepoint_name;

SQLite SELECT statement

SELECT　column1,　column2....columnN
FROM table_name;

Requête UPDATE SQLite

UPDATE　nom_de_table
SET　column1　=　value1,　column2　=　value2....columnN=valueN
[　WHERE　　CONDITION　];

Requête VACUUM SQLite

VACUUM;

Préfixe WHERE SQLite

SELECT　column1,　column2....columnN
FROM nom_de_table;
CONDITION DE WHERE;