sqlite学习

---

• sqlite的点命令不以;结尾
• sqlite3, .help

1. 命令

---

命令	描述
.backup ?DB? FILE	备份DB数据库（默认main）到FILE文件
.bail ON/OFF	发生错误后停止，默认为OFF
.databases	列出数据库的名称和文件
.dump ?TABLE?	以SQL文本格式转储数据库，如果制定了TABLE表，则之转出匹配LIKE模式的TABLE表
.echo ON/OFF	开启或关闭echo 命令
.exit	退出
.explain ON/OFF	开启或关闭适合EXPLAIN的输出模式，如果没有带参数，则为EXPLAIN on,及开启
.header(s) ON/OFF	开启或关闭头部显示
.help	显示消息
.import FILE TABLE	导入来自FILE文件的数据到TABLE表中
.indices ? TABLE?	显示所有索引的名称，如果制定了TABLE表，则只显示匹配LIKE模式的TABLE表的索引
.load FILE ?ENTRY?	加载一个扩展库
.log FILE/off	开启或关闭日志。FILE文件可以是stderr，stdout
.mode MODE	设置输出模式，1.csv逗号分隔的值 2.column左对齐的列 3.html HTML的代码 4.insert TABLE 表的SQL插入语句 5.line 每行的一个值 6.list 由.sepqrator字符串分隔的值 7.tabs 由Tab分隔的值 8.tcl TCL列表元素
.nullvalue STRING	在NULL指的地方输出STRING字符串
.output FILENAME	发送输出到FILENAME文件
.output stdout	发送输出到屏幕
.print STRING …	逐字的输出STRING字符串
.prompt MAIN CONTINUE	替换标准提示符
.quit	退出
.read FILENAME	执行FILENAME文件中的SQL
.schema ?TABLE?	显示CREATE语句，如果制定了TABLE表，则只显示匹配LIKE模式的TABLE表
.separator STRING	改变输出模式和.import所使用的分隔符
.show	显示各种设置的当前值
.stats ON/OFF	开启或者关闭统计
.tables ?PATTERN?	列出匹配LIKE模式的表的名称
.timeout MS	尝试打开锁定的表MS微秒
.width NUM NUM	为column模式设置宽度
.timer ON/OFF	开启或者关闭CPU定时器测量

* 确保sqlite>提示符与点名令见没有空格否则无法正常工作。
* 主表中保存了数据库表的关键信息，并把它命名为sqlite_master。如果要查看表概要，可执行.schema sqlite_master
* 虽然sqlite不区分大小写，但是在一些命令中大小写是区分的，比如GLOB和glob
* 两个连续的-是注释。注释一行。或者使用/* */
* sqlite中所有语句都是以;结束

2.语法

ANALYZE;or
ANALYZE database_name;or
ANALYZE database_name.table_name;
///
SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION-1 {AND|OR} CONDITION-2;
///
ALTER TABLE table_name ADD COLUMN column_def...;
///
ALTER TABLE table_name RENAME TO new_table_name;
///
ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
///
BEGIN;orBEGIN EXCLUSIVE TRANSACTION;
///
SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name BETWEEN val-1 AND val-2;
//
COMMIT;
///
CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );
///
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);
///
CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,.....
   columnN datatype,
   PRIMARY KEY( one or more columns ));
///
CREATE TRIGGER database_name.trigger_name 
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN 
   stmt1; 
   stmt2;....END;
///
CREATE VIEW database_name.view_name  AS
SELECT statement....;
///
CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );
///
SELECT COUNT(column_name)
FROM   table_name
WHERE  CONDITION;
///
DELETE FROM table_name
WHERE  {CONDITION};
///
DETACH DATABASE 'Alias-Name';
///
SELECT DISTINCT column1, column2....columnN
FROM   table_name;
///
DROP INDEX database_name.index_name;
///
DROP TABLE database_name.table_name;
///
DROP INDEX database_name.view_name;
///
DROP INDEX database_name.trigger_name;
///
SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name EXISTS (SELECT * FROM   table_name );
///
EXPLAIN INSERT statement...;or 
EXPLAIN QUERY PLAN SELECT statement...;
///
SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name GLOB { PATTERN };
///
SELECT SUM(column_name)
FROM   table_name
WHERE  CONDITION
GROUP BY column_name;
///
SELECT SUM(column_name)
FROM   table_name
WHERE  CONDITION
GROUP BY column_name
HAVING (arithematic function condition);
///
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);
///
SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name IN (val-1, val-2,...val-N);
///
SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name LIKE { PATTERN };
///
SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name NOT IN (val-1, val-2,...val-N);
///
SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION
ORDER BY column_name {ASC|DESC};
///
PRAGMA pragma_name;For example:

PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);
///
RELEASE savepoint_name;

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

ROLLBACK;or
ROLLBACK TO SAVEPOINT savepoint_name;

SAVEPOINT savepoint_name;
SELECT column1, column2....columnN
FROM   table_name;
/
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE  CONDITION ];
VACUUM;

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

3. 数据类型

---

存储类	描述
NULL	值是NULL
INTEGER	值是带符号整数存储在1~8字节中
REAL	浮点值，存储为8字节的IEEE浮点数字
TEXT	文本字符串，使用数据库编码存储
BLOB	值是一个blob数据，完全根据他的输入存储