SQLite - Syntaxe

SQLite est suivi d'un ensemble unique de règles et de directives appelées Syntaxe. Ce chapitre répertorie toute la syntaxe de base de SQLite.

Sensibilité à la casse

Le point important à noter est que SQLite est case insensitive, ie les clauses GLOB et glob ont la même signification dans les instructions SQLite.

commentaires

Les commentaires SQLite sont des notes supplémentaires que vous pouvez ajouter dans votre code SQLite pour augmenter sa lisibilité et ils peuvent apparaître n'importe où; des espaces peuvent apparaître, y compris à l'intérieur d'expressions et au milieu d'autres instructions SQL, mais ils ne peuvent pas être imbriqués.

Les commentaires SQL commencent par deux caractères "-" consécutifs (ASCII 0x2d) et s'étendent jusqu'au prochain caractère de nouvelle ligne (ASCII 0x0a) ou jusqu'à la fin de l'entrée, selon la première éventualité.

Vous pouvez également utiliser des commentaires de style C, qui commencent par "/ *" et s'étendent jusqu'à et y compris la prochaine paire de caractères "* /" ou jusqu'à la fin de l'entrée, selon la première éventualité. Les commentaires de style C peuvent s'étendre sur plusieurs lignes.

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

Instructions SQLite

Toutes les instructions SQLite commencent par l'un des mots-clés tels que SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc., et toutes les instructions se terminent par un point-virgule (;).

Instruction SQLite ANALYZE

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

Clause SQLite AND / OR

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

Instruction SQLite ALTER TABLE

ALTER TABLE table_name ADD COLUMN column_def...;

Instruction SQLite ALTER TABLE (Renommer)

ALTER TABLE table_name RENAME TO new_table_name;

Instruction SQLite ATTACH DATABASE

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

Instruction SQLite BEGIN TRANSACTION

BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;

Clause SQLite BETWEEN

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

Instruction SQLite COMMIT

COMMIT;

Instruction SQLite CREATE INDEX

CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );

Instruction SQLite CREATE UNIQUE INDEX

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

Instruction SQLite CREATE TABLE

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

Instruction SQLite CREATE TRIGGER

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

Instruction SQLite CREATE VIEW

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

Instruction SQLite CREATE VIRTUAL TABLE

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

Instruction SQLite COMMIT TRANSACTION

COMMIT;

Clause SQLite COUNT

SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;

Instruction SQLite DELETE

DELETE FROM table_name
WHERE {CONDITION};

Instruction SQLite DETACH DATABASE

DETACH DATABASE 'Alias-Name';

Clause SQLite DISTINCT

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

Instruction SQLite DROP INDEX

DROP INDEX database_name.index_name;

Instruction SQLite DROP TABLE

DROP TABLE database_name.table_name;

Instruction SQLite DROP VIEW

DROP INDEX database_name.view_name;

Instruction SQLite DROP TRIGGER

DROP INDEX database_name.trigger_name;

Clause SQLite EXISTS

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

Instruction SQLite EXPLAIN

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

Clause SQLite GLOB

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

Clause SQLite GROUP BY

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

Clause SQLite HAVING

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

Instruction SQLite INSERT INTO

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

Clause SQLite IN

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

Clause similaire à SQLite

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

Clause SQLite NOT IN

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

Clause ORDER BY SQLite

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

Instruction SQLite PRAGMA

PRAGMA pragma_name;

For example:

PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);

Instruction SQLite RELEASE SAVEPOINT

RELEASE savepoint_name;

Instruction SQLite REINDEX

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

Instruction SQLite ROLLBACK

ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;

Instruction SQLite SAVEPOINT

SAVEPOINT savepoint_name;

Instruction SQLite SELECT

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

Instruction SQLite UPDATE

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

Instruction SQLite VACUUM

VACUUM;

Clause WHERE SQLite

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

↰ Previous page Next page ↱