1.SQLite环境配置
下载SQLite(http://www.sqlite.org/download.html)
解压出来后,得到文件sqlite3.def、sqlite3.dll 和 sqlite3.exe
将存放SQLite文件目录添加到环境变量PATH路径下
2.测试环境
C:\Users\Administrator>cd C:\sqlite
C:\sqlite>sqlite3
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .show
echo: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
stats: off
width:
sqlite> .schema sqlite_master
CREATE TABLE sqlite_master (
type text,
name text,
tbl_name text,
rootpage integer,
sql text
);
3.创建数据库
sqlite> test.db
4.创建表
sqlite> create table table_name(id integer primary key,value text);
CPU Time: user 0.000000 sys 0.000000
5.插入数据
sqlite> insert into table_name(id,value)values(XX,XXX);
6.查询数据
sqlite> select * from table_name where xx = xxx;
7.修改数据
sqlite>update table_name set xx = xxx where xx = xxx;
8.删除数据
sqlite>delete from table_name where xx = xxx;
9.修改表结构
sqlite>alter table table_name add column xxx;
10.创建视图
sqlite>create view view_name as select * from table_name;
11.创建索引
sqlite>create index idx_xxx on xxx(xxx);
12.显示表结构
sqlite> .schema [table_name];
13.显示所有的表和视图
sqlite > .tables;
14.显示指定表的索引列表
sqlite > .indices [table_name];
15.导出数据库到 SQL 文件:
sqlite > .output [file_name];
sqlite > .dump;
sqlite > .output file_name;
16.从 SQL 文件导入数据库:
sqlite > .read [file_name];
17.格式化输出数据到 CSV 格式:
sqlite >.output [file_name.csv];
sqlite >.separator;
sqlite > select * from table_name;
sqlite >.output file_name;
18.从 CSV 文件导入数据到表中:
sqlite >create table new_table(xx,xx);
sqlite >.import [file_name.csv ] new_table;
19.备份数据库:
/* usage: sqlite3 [database] .dump > [filename] */
sqlite3 mytable.db .dump > file_name.sql
20.恢复数据库:
/* usage: sqlite3 [database ] < [filename ] */
sqlite3 mytable.db < file_name.sql
下载SQLite(http://www.sqlite.org/download.html)
解压出来后,得到文件sqlite3.def、sqlite3.dll 和 sqlite3.exe
将存放SQLite文件目录添加到环境变量PATH路径下
2.测试环境
C:\Users\Administrator>cd C:\sqlite
C:\sqlite>sqlite3
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .show
echo: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
stats: off
width:
sqlite> .schema sqlite_master
CREATE TABLE sqlite_master (
type text,
name text,
tbl_name text,
rootpage integer,
sql text
);
3.创建数据库
sqlite> test.db
4.创建表
sqlite> create table table_name(id integer primary key,value text);
CPU Time: user 0.000000 sys 0.000000
5.插入数据
sqlite> insert into table_name(id,value)values(XX,XXX);
6.查询数据
sqlite> select * from table_name where xx = xxx;
7.修改数据
sqlite>update table_name set xx = xxx where xx = xxx;
8.删除数据
sqlite>delete from table_name where xx = xxx;
9.修改表结构
sqlite>alter table table_name add column xxx;
10.创建视图
sqlite>create view view_name as select * from table_name;
11.创建索引
sqlite>create index idx_xxx on xxx(xxx);
12.显示表结构
sqlite> .schema [table_name];
13.显示所有的表和视图
sqlite > .tables;
14.显示指定表的索引列表
sqlite > .indices [table_name];
15.导出数据库到 SQL 文件:
sqlite > .output [file_name];
sqlite > .dump;
sqlite > .output file_name;
16.从 SQL 文件导入数据库:
sqlite > .read [file_name];
17.格式化输出数据到 CSV 格式:
sqlite >.output [file_name.csv];
sqlite >.separator;
sqlite > select * from table_name;
sqlite >.output file_name;
18.从 CSV 文件导入数据到表中:
sqlite >create table new_table(xx,xx);
sqlite >.import [file_name.csv ] new_table;
19.备份数据库:
/* usage: sqlite3 [database] .dump > [filename] */
sqlite3 mytable.db .dump > file_name.sql
20.恢复数据库:
/* usage: sqlite3 [database ] < [filename ] */
sqlite3 mytable.db < file_name.sql