基本操作

1、创建或者打开数据库 sqlite3 xxx.db

如果xxx.db存在则打开如果没有则新建此时执行创建表的命令后才会出现xxx.db文件。

创建xxx.db数据库

sqlite3 xxx.db

CREATE TABLE record(id integer primary key autoincrement,time varchar(20),name varchar(30),count int);

2、查看表结构

sqlite> .schema record

查看表record结构

3、查看所有表

sqlite> .tables

4、查看数据库

sqlite> .databases

5、执行sql脚本

sqlite> .read insert.sql

6、查看帮助

sqlite> .help

7、备份数据库

sqlite> .backup C:/users/ghost/desktop/lemo/xx.sql

数据的增删改查

1、创建表

CREATE TABLE record(id integer primary key autoincrement,time varchar(20),name varchar(30),count int);

2、修改表

3、插入数据

insert into record(time,name) values('20160124','徐凤年');

4、查询数据

select * from record;

5、改数据

update record set name='g' where id=1;

6、删

delete from record where id=1

.backup ?DB? FILE           Backup DB (default "main") to FILE

.bail ON|OFF                  Stop after hitting an error.  Default OFF

.databases                     List names and files of attached databases

.dump ?TABLE? ...          Dump the database in an SQL text format

                                 If TABLE specified, only dump tables matching

                                 LIKE pattern TABLE.

.echo ON|OFF               Turn command echo on or off

.exit                          Exit this program

.explain ?ON|OFF?          Turn output mode suitable for EXPLAIN on or off.

                                 With no args, it turns EXPLAIN on.

.header(s) ON|OFF          Turn display of headers on or off

.help                          Show this message

.import FILE TABLE        Import data from FILE into TABLE

.indices ?TABLE?           Show names of all indices

                                If TABLE specified, only show indices for tables

                                 matching LIKE pattern TABLE.

.log FILE|off                  Turn logging on or off.  FILE can be stderr/stdout

.mode MODE ?TABLE?         Set output mode where MODE is one of:

                                 csv      Comma-separated values

                                 column   Left-aligned columns.  (See .width)

                                 html     HTML <table> code

                                 insert   SQL insert statements for TABLE

                                 line     One value per line

                                 list     Values delimited by .separator string

                                 tabs     Tab-separated values

                                 tcl      TCL list elements

.nullvalue STRING          Print STRING in place of NULL values

.output FILENAME           Send output to FILENAME

.output stdout                 Send output to the screen

.prompt MAIN CONTINUE     Replace the standard prompts

.quit                          Exit this program

.read FILENAME             Execute SQL in FILENAME

.restore ?DB? FILE         Restore content of DB (default "main") from FILE

.schema ?TABLE?           Show the CREATE statements

                                 If TABLE specified, only show tables matching

                                 LIKE pattern TABLE.

.separator STRING          Change separator used by output mode and .import

.show                          Show the current values for various settings

.stats ON|OFF              Turn stats on or off

.tables ?TABLE?           List names of tables

                         If TABLE specified, only list tables matching

                         LIKE pattern TABLE.

.timeout MS            Try opening locked tables for MS milliseconds

.vfsname ?AUX?         Print the name of the VFS stack

.width NUM1 NUM2 ...   Set column widths for "column" mode

.timer ON|OFF          Turn the CPU timer measurement on or off

over

巴特西

【Android】Sqlite3命令详解