目录
1.简介
SQLite 使用 B-tree 处理索引,使用 B+tree 处理表数据。
2.基础命令操作
1.查看表头命令
.headers on 或 .h on 或 .head on
查询时显示表头: .headers on / .h on / .head on
.headers 设置为 on 时,查询结果显示时带有字段名,默认值是 off。
2.查看表的索引
格式:.indices 表名
例如:
.indices test
3.查看表或视图的定义(DDL)语句
格式: .schema [table name]
例如:
.schema test
更详细的 schema 信息可以通过 SQLite 唯一的一个系统视图 sqlite_master 得到。
4. 格式化输出sql语句内容
.mode column 或者 .m col
5.数据导出
可以使用.dump 命令将数据库导出为 SQL 格式的文件。
不使用任何参数,.dump 将导出整个数据库。如果提供参数,CLP 把参数理解为表名或视图名
例如:
.output file.sql
.dump
.output stdout
使用上面3个命令可以导出该数据库对应的sql语句,生成到file.sql文件中。
6.数据导入
有两种方法可以导入数据,用哪种方法决定于要导入的文件的格式。如果文件由 SQL 语句 构成,可以使用.read 命令导入(执行)文件。如果文件是由逗号或其它定界符分隔的值 (comma-separated values, CSV)组成,可使用.import [file][table]命令,此命令将解析指定的文件并尝试将数据插入到指定的表中。
.read 命令用来导入由.dump 命令创建的文件。如果要使用前面作为备份文件所导出的file.sql,需要先移除已经存在的数据库对象(表和视图),然后用下面方法导入:
drop table 表名; #先删除该数据库中的所有表
.read file.sql
格式化:
CLP 提供了几个格式化选项命令。
7.命名回显
.echo on命令设置为新输入的命令在执行前都会回显,默认值是 off。
例如:
.echo on 在执行 select * from test; 时会在执行结果前显示该执行语句。
8.设置NULL值为指定值
格式:.nullvalue 指定值
例如:
.nullvalue 命令设置NULL值时,默认情况下使用空串,若显示为NULL使用.nullvalue NULL设置。
9.设置CLP的shell提示符
格式:.prompt [value]
例如:
.prompt 'sqlite3> '
10. 设置结果数据的输出格式
.mode 命令可以设置结果数据的几种输出格式。可选的格式为 csv、column、html、insert、line、list、tabs 和 tcl。默认值是 list,在此模式下显示结果时列间以默认的分隔符分隔。如果你想以 CSV 格式输出一个表的数据,可如下操作:
sqlite3>.output file.csv
sqlite3>.separator ,
sqlite3>select * from test;
sqlite3>.output stdout
因为有一个 CSV 模式,所以下面的命令会得到相似的结果:
sqlite3> .output file.csv
sqlite3> .mode csv
sqlite3> select * from test;
sqlite3> .output stdout
3. 数据库基础操作
1. 创建数据库
.open test.db
2.备份数据库
数据备份有2种方法:
1》直接在命令行执行如下语句
sqlite3 test.db .dump > test.sql
2》在 CLP 中执行:
sqlite> .output test.sql
sqlite> .dump
sqlite> .exit
导入一个 SQL 格式备份的数据库可如下做:
sqlite3 test.db < test.sql
此处假设 test.db 不存在。如果它存在,则或许会因为数据库中有同名的对象而出错。
可以用复制的方法得到一个二进制的数据库文件拷贝。但也许在复制之前你想先抽空 (vacuum)它,也就是释放数据库文件中未使用的空间,以得到一个更小的数据库文件。
可操作如下:
sqlite3 test.db VACUUM
cp test.db test.backup
一般情况下,二进制的备份不如SQL备份兼容性好。尽管 SQLite 有很好的向上兼容性和各 操作系统间文件格式的一致性,但如果想要将备份文件保留很长时间,还是 SQL 格式保险 一些。
3.删除数据库
当一个数据库你不想再用时,简单地从操作系统中将其文件删除就行了。
4.建表
和mysql建表差不多
修改表:SQLite 版的 ALTER TABLE 命令既可以改变 表名,也可以增加字段。
alter table foods add column email text not null default '' collate nocase;
alter table foods drop column email ;
支持修改表添加/删除列,但无法修改表中字段。
5. 删除表
drop table 表名;
6. 创建视图
create view 视图名 as select * from 表名;
例如:
create view schema as select * from sqlite_master;
select type,name,tbl_name,sql from sqlite_master order by type;
7. 创建索引
create index 索引名 on 表名 (字段名);
8.限定和排序
可以用 LIMIT 和 OFFSET 保留字限定结果集的大小和范围。LIMIT 指定返回记录的最大数 量。OFFSET 指定偏移的记录数。
例如,下面的命令返回 food_types 表中 id 排第 2 的记录:
SELECT * FROM food_types LIMIT 1 OFFSET 1 ORDER BY id; 保留字 OFFSET 在结果集中跳过一行(Bakery),保留字 LIMIT 限制最多返回一行(Cereal)。
9.函数和聚合
函数的种类包括:数学函数,如ABS()计算绝对值;字符串格式函数,如UPPER()和 LOWER(),它们将字符串的值 转化为大写或小写。
标准的聚合函数包括 SUM()、AVG()、 COUNT()、MIN()和 MAX()。
10.NULL 和 UNIQUE
问题:如果一个字段已经声明为 UNIQUE,可以向这个字段插入多少个 NULL 值? 回答:与数据库的种类有关。PostgreSQL 和 Oracle 可以插入多个。Informix 和 Microsoft SQLServer只能一个。DB2、SQLAnywhere和Borland Inter-Base不能。SQLite采用了与PostgreSQL和 Oracle 相同的解决方案。
另一个困扰大家的关于 NULL 的经典问题是:两个 NULL 值是否相等?你没有足够的信息 来证明它们相等,但也没有足够的信息证明它们不等。SQLite 的观点是假设所有的 NULL都是不同的。所以你可以向唯一字段中插入任意多个 NULL 值。
不同存储类的值可以通过它们各自类的“类值”进行 排序,定义如下:
1. NULL 存储类具有最低的类值。
2. INTEGER 或 REAL 存储类值高于 NULL,它们的类值相等。
3. TEXT 存储类的值比 INTEGER 和 REAL 高。数值永远比字符串的值低。
4. BLOB 存储类具有最高的类值。具有 BLOB 类的值大于其它所有类的值。
SQLite默认的亲和性是NUMERIC。如果你暂时还不确定要往一个字段里放什么内容,或准备将来修改,用 NONE 亲和性是一个好的选择。
11.查询字段类型sql
select typeof(x),typeof(y),typeof(z) from 表名;
12. SQLite 会显示编译后的 VDBE 汇编程序
例如:
explain select * from meta_layer;
4. 连接与事务
SQLite 默认情况下,每条 SQL 语句自成事务(自动提交模式)。
连接缓冲区大小
缓冲区尺寸 pragma 控制一个连接可以在内存中使用多少个数据库页。
要查看当前缓冲区大 小的默认值,执行:
sqlite> PRAGMA cache_size;
要改变缓冲区大小,执行:
sqlite> PRAGMA cache_size=10000;
sqlite> PRAGMA cache_size;
sqlite> pragma database_list;
sqlite> CREATE INDEX foods_name_type_idx ON foods(name,type_id);
sqlite> pragma index_info(foods_name_type_idx);
sqlite> pragma index_list(foods)
sqlite> pragma table_info(foods);
sqlite_master 表是一个系统表,它包含数据库中所有表、视图、索引和触发器的信息。
关于连接和事务,你必须知道两件事:
(1)对数据库的任何操作,一个连接存在于一个事务之下。
(2)一个连接绝不会同时存在于多个事务之下。无论何时,一个连接在对数据
库做任何操作时,都总是在恰好一个事务之下,不会多,也不会少。
连接的生命周期(The Connection Lifecycle)
和大多数据库连接相同,其生命周期由三个阶段构成:
1. 连接数据库(Connect to the database)。
2. 处理事务(Perform transactions):如你所知,任何命令都在事务下执行。默认情况下,事 务自动提交,也就是每一个 SQL 语句都在一个独立的事务下运行。当然也可以通过使用
BEGIN..COMMIT 手动提交事务。
3. 断开连接(Disconnect from the database):关闭数据库文件。还要关闭所有附加的数据库文件。