sqlite3 基本操作

 sqlite3 基本操作
表查找
.tables patter
查看索引
.indices table name
查看表结构视图
.schema table name
查询sqlite_master表
.mode col
.headers on
select * from table

数据导出
.output file.sql
.dump
.output stdout

数据导入
.show
drop table test;
drop view schema;
.read file.sql

CLP 提供了几个格式化选项命令。最简单的是.echo, 如果设置.echo on，则新输入的命令在
执行前都会回显，默认值是off。.headers 设置为on 时，查询结果显示时带有字段名。当遇
到NULL值时，如果需要以一个字符串来显示，使用.nullvalue命令设置，如：
sqlite> .nullvalue NULL
默认情况下使用空串。如果要改变CLP 的shell提示符，使用.prompt [value]，如：
sqlite> .prompt 'SQlite3>'
SQlite3>
.mode 命令可以设置结果数据的几种输出格式。可选的格式为csv、column、html、insert、
line、list、tabs 和tcl。默认值是list，在此模式下显示结果时列间以默认的分隔符分隔。如
果你想以CSV 格式输出一个表的数据，可如下操作：
sqlite3> .output file.csv
sqlite3> .separator ,
sqlite3> select * from test;
sqlite3> .output stdout

创建数据库
test.sql 为表创建sql语句
sqlite3 test.db < test.sql
sqlite3 test.db VACUUM 也就是释放数据库文件中未使用的空间，以得到一个更小的数据库文件。
cp test.db test.backup

。在SQLite中有5 种本地类型：
INTEGER、REAL、TEXT、BLOB 和NULL

修改表结构
ALTER TABLE table { RENAME TO name |ADD COLUMN column_def }
sqlite> ALTER TABLE contacts ADD COLUMN email TEXT NOT NULL DEFAULT '' COLLATE NOCASE;

SELECT中使用3大类13种关系操作：
. 基本的操作
. Restriction(限制)
. Projection
. Cartesian Product(笛卡尔积)
. Union(联合)
. Difference(差)
. Rename(重命名)
. 附加的操作
. Intersection(交叉)
. Natural Join(自然连接)
.Assign(指派OR 赋值)
. 扩展的操作
. Generalized Projection
. Left Outer Join
. Right Outer Join
. Full Outer Join


LIKE操作符

sqlite> SELECT id, name FROM foodsWHERE name LIKE 'J%';

函数
ABS()绝对值
upper()大写
lower()小写
sum()累加
avg()
count()
min()
max()

分组
sqlite> SELECT type_id FROM foods GROUP BY type_id;
去重复
distinct
sqlite> SELECT DISTINCT type_id FROM foods;


多表连接
连接(join)是SELECT 命令的第一个操作，它产生初始的信息，供语句的其它部分过滤和处
理。连接的结果是一个合成的关系(或表)，它是SELECT 后继操作的输入。
也许从一个例子开始是最简单的。
sqlite> SELECT foods.name, food_types.name
FROM foods, food_types
WHERE foods.type_id=food_types.id LIMIT 10;

名称和别名
当把多个表连接在一起时，字段可能重名。
SELECT B.name FROMA JOIN B USING (a);

插入记录
sqlite> INSERT INTO foods (name, type_id) VALUES ('Cinnamon Bobka', 1);
修改记录
UPDATE 命令用于修改一个表中的记录。UPDATE 命令可以修改一个表中一行或多行中的
一个或多个字段。UPDATE 语句的一般格式为：
UPDATE table SET update_listWHERE predicate;
UPDATE foods SET name='CHOCOLATE BOBKA' WHERE name='Chocolate Bobka';
删除
DELETE FROM tableWHERE predicate;

事务
事务的范围
事务由3 个命令控制：BEGIN、COMMIT 和ROLLBACK。BEGIN 开始一个事务，之后的
所有操作都可以取消。COMMIT 使BEGIN 后的所有命令得到确认；而ROLLBACK 还原
BEGIN 之后的所有操作。如：
sqlite> BEGIN;
sqlite> DELETE FROM foods;
sqlite> ROLLBACK;
sqlite> SELECT COUNT(*) FROM foods;
COUNT(*)
412
上面开始了一个事务，先删除了foods表的所有行，但是又用ROLLBACK进行了回卷。再
执行SELECT时发现表中没发生任何改变。

SQLite 提供5 种冲突解决方案：REPLACE、IGNORE、FAIL、ABORT 和ROLLBACK。

触发器
当特定的表上发生特定的数据库事件时，触发器会执行特定的SQL 命令。创建触发器的一
般语法如下：
CREATE [TEMP|TEMPORARY] TRIGGER name [BEFORE|AFTER]
[INSERT|DELETE|UPDATE|UPDATE OF columns] ON table
action


清洁数据库
SQLite 有两个命令用于数据库清洁——REINDEX和VACUUM。REINDEX用于重建索引，
有两种形式：
REINDEX collation_name;
REINDEX table_name|index_name;
第一种形式利用给定的排序法名称重新建立所有的索引。
VACUUM通过重建数据库文件来清除数据库内所有的未用空间。

连接缓冲区大小
缓冲区尺寸pragma 控制一个连接可以在内存中使用多少个数据库页。要查看当前缓冲区大
小的默认值，执行：
sqlite> PRAGMA cache_size;
41
cache_size
2000
要改变缓冲区大小，执行：
sqlite> PRAGMA cache_size=10000;
sqlite> PRAGMA cache_size;
cache_size
10000


系统表
sqlite_master表是一个系统表，它包含数据库中所有表、视图、索引和触发器的信息。例如，
foods的当前内容如下：
sqlite> SELECT type, name, rootpage FROM sqlite_master;
type name

内容来自
SQLite 权威指南
The Definitive Guide to SQLite