本文详细介绍了SQLite数据库的基础命令操作，包括查看表头、索引、定义，数据导入导出，设置NULL值，以及数据库的创建、备份、删除、建表、删除表、创建视图和索引等操作。此外，还讨论了SQLite的事务处理和连接管理，包括缓冲区大小调整和事务的自动提交模式。
目录
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 test3.查看表或视图的定义（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 stdout3. 数据库基础操作
1. 创建数据库
.open test.db2.备份数据库
数据备份有2种方法：
1》直接在命令行执行如下语句
sqlite3 test.db .dump > test.sql2》在 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)：关闭数据库文件。还要关闭所有附加的数据库文件。
扫一扫
6999
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
