Sqlite 简明教程
SQLite - Syntax
SQLite 是遵循称为语法的一组独特规则和指南的。本章列出了所有基本的 SQLite 语法。
Comments
SQLite 注释是额外的注释,可以将其添加到 SQLite 代码中以提高其可读性,它们可以出现在任何地方;空格可能会出现,包括在表达式内部和其它 SQL 语句的中部,但不能嵌套。
SQL 注释以两个连续的 "-" 字符(ASCII 0x2d)开头,并扩展到下一个换行符(ASCII 0x0a)或输入结束,以先到者为准。
你还可以使用 C 风格注释,它以字符对 "/ " and extend up to and including the next " /" 开头,或以输入结束,以先到者为准。C 风格注释可以跨越多行。
sqlite> .help -- This is a single line comment
SQLite Statements
所有 SQLite 语句都以任意关键字开头,如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等,所有语句都以分号 (;) 结尾。
SQLite AND/OR Clause
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
SQLite BETWEEN Clause
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;
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;
SQLite CREATE VIRTUAL TABLE Statement
CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );
SQLite EXISTS Clause
SELECT column1, column2....columnN
FROM table_name
WHERE column_name EXISTS (SELECT * FROM table_name );
SQLite GLOB Clause
SELECT column1, column2....columnN
FROM table_name
WHERE column_name GLOB { PATTERN };
SQLite GROUP BY Clause
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 table_name
WHERE column_name IN (val-1, val-2,...val-N);
SQLite Like Clause
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
SQLite NOT IN Clause
SELECT column1, column2....columnN
FROM table_name
WHERE column_name NOT IN (val-1, val-2,...val-N);
SQLite ORDER BY Clause
SELECT column1, column2....columnN
FROM table_name
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 REINDEX Statement
REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;