SQLite -语法
SQLite是紧随其后的是独特的组称为语法的规则和指导方针。本教程为您提供了快速启动和SQLite的清单的所有基本SQLite语法。
大小写敏感性
注意重要的一点是,SQLite是大小写不敏感,但也有一些命令,这是区分大小写的GLOB和glob在SQLite报表有不同的意义
注解
SQLite评论是额外的笔记,你可以添加在你的SQLite代码来增加其可读性和他们可以出现在任何地方,空白可以发生,包括在中间的表情和其他SQL语句,但他们不能嵌套。
SQL注释开始连续两个"-"字符(ASCII 0 x2d)和扩展包括下一个换行符(ASCII 0 x0a)或在年底前输入,哪个是第一位的。
您还可以使用c风格的评论,首先"/ *"和扩展包括下一个"* /"字符对或在年底前输入,哪个是第一位的。c风格的评论可以跨越多个行。
sqlite >。帮助——这是一行评论
sqlite>.help -- This is a single line comment
SQLite语句
所有SQLite语句开始等任何关键词的选择、插入、更新、删除、涂改、下降,等等,所有的语句以分号(;)结束。
SQLite分析声明:
ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;
SQLite和/或条款:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
SQLite ALTER TABLE语句:
ALTER TABLE table_name ADD COLUMN column_def...;
SQLite ALTER TABLE语句(重命名):
ALTER TABLE table_name RENAME TO new_table_name;
SQLite数据库附加声明:
ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
SQLite开始事务声明:
BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;
SQLite之间的条款:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;
SQLite COMMIT语句:
COMMIT;
SQLite创建索引语句
CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );
SQLite创建TABLE语句:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
SQLite创建触发器语句:
CREATE TRIGGER database_name.trigger_name
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN
stmt1;
stmt2;
....
END;
SQLite创建视图语句:
CREATE VIEW database_name.view_name AS
SELECT statement....;
SQLite创建虚拟表声明:
CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3(
);
SQLite提交事务声明:
COMMIT;
SQLite数条款:
SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;
SQLite DELETE语句:
DELETE FROM table_name
WHERE {CONDITION};
SQLite DETACH DATABASE语句:
DETACH DATABASE 'Alias-Name';
SQLite DISTINCT语句:
SELECT DISTINCT column1, column2....columnN
FROM table_name;
SQLite DROP INDEX语句:
DROP INDEX database_name.index_name;
SQLite DROP TABLE 语句:
DROP TABLE database_name.table_name;
SQLite DROP VIEW 语句 :
DROP INDEX database_name.view_name;
SQLite DROP TRIGGER 语句 :
DROP INDEX database_name.trigger_name;
SQLite EXISTS Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name EXISTS (SELECT * FROM table_name );
SQLite EXPLAIN 语句 :
EXPLAIN INSERT statement...;
or
EXPLAIN QUERY PLAN SELECT statement...;
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 语句
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 语句
PRAGMA pragma_name;
For example:
PRAGMA page_size;
PRAGMA cache_size =
1024;
PRAGMA table_info(table_name);
SQLite RELEASE SAVEPOINT 语句:
RELEASE savepoint_name;
SQLite REINDEX 语句:
REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;
SQLite ROLLBACK 语句:
ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;
SQLite SAVEPOINT语句:
SAVEPOINT savepoint_name;
SQLite SELECT 语句:
SELECT column1, column2....columnN
FROM table_name;
SQLite UPDATE 语句:
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];
SQLite VACUUM 语句:
VACUUM;
SQLite WHERE Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;