ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQLite 命令行客户端 sqlite3 使用指南

SQLite 命令行客户端 sqlite3 使用指南

Linux操作系统 作者:qwer1030274531 时间:2020-10-21 11:24:03 0 删除 编辑

下载 sqlite3

很多 Linux 操作系统默认会安装一个 SQLite 软件,或者打开 SQLite 官方 下载页面,找到“ Precompiled Binaries for XXX”,根据不同平台点击下载相应的“sqlite-tools-xxx.zip”文件。

下载
下载之后直接解压 zip 文件,其中包含 3 个文件:

  • sqldiff 或者 sqldiff.exe,SQLite 数据库比较工具;
  • sqlite3 或者 sqlite3.exe,SQLite 命令行客户端;
  • sqlite3_analyzer 或者 sqlite3_analyzer.exe,SQLite 数据表和索引的统计分析工具。

本文介绍如何 sqlite3 命令行客户端的使用,如何管理和操作数据库。

连接数据库

在操作系统命令行中直接输入 sqlite3 或者双击 sqlit3.exe 运行客户端工具:

sqlite3.exe
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>1234567

默认情况下,SQLite 使用内存作为数据库的存储,意味着退出之后所有的内容都会丢失。我们可以使用 .open命令打开一个新的数据库文件,例如:

sqlite> .open hr.db1

如果指定的数据库文件不存在,sqlite3 会创建一个新的文件。

另外,我们也可以在运行客户端工具的时候直接打开一个数据库文件。例如:

D:\Software\sqlite-tools-win32-x86-3330000\sqlite3.exe D:\Software\sqlite-tools-win32-x86-3330000\hr.db
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite>12345

查看帮助

输入 .help命令查看命令帮助信息:

sqlite> .help.archive ...             Manage SQL archives.auth ON|OFF             Show authorizer callbacks.backup ?DB? FILE        Backup DB (default "main") to FILE....vfslist                 List all available VFSes.vfsname ?AUX?           Print the name of the VFS stack.width NUM1 NUM2 ...     Set minimum column widths for columnar output12345678

sqlite3 工具支持一系列以点号(.)开始的特殊命令,通常用于设置客户端的显示格式,或者执行一些预定义的查询语句。

也可以使用 .help TOPIC查看某个具体命令的帮助,例如:

sqlite> .help open.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
     Options:        --append        Use appendvfs to append database to the end of FILE
        --new           Initialize FILE to an empty database
        --nofollow      Do not follow symbolic links
        --readonly      Open FILE readonly
        --zip           FILE is a ZIP archive12345678

列出数据库

输入.databases命令列出当前连接中打开的所有数据库和对应的文件。例如:

sqlite> .databasesmain: D:\Software\sqlite-tools-win32-x86-3330000\hr.db12

其中,main 是默认打开的数据库名。如果使用 ATTACH语句打开了其他数据库文件,还会显示更多的数据库。例如:

sqlite> attach database "D:\Software\sqlite-tools-win32-x86-3330000\new.db" as newdb;sqlite> .databasesmain: D:\Software\sqlite-tools-win32-x86-3330000\hr.db
newdb: D:\Software\sqlite-tools-win32-x86-3330000\new.db12345

查看数据库信息

输入 .dbinfo命令查看指定数据库的状态信息,默认为 main 数据库:

sqlite> .dbinfodatabase page size:  4096write format:        1read format:         1reserved bytes:      0file change counter: 161database page count: 13freelist page count: 0schema cookie:       8schema format:       4default cache size:  0autovacuum top root: 0incremental vacuum:  0text encoding:       1 (utf8)user version:        0application id:      0software version:    3033000number of tables:    3number of indexes:   7number of triggers:  0number of views:     0schema size:         1852data version         11234567891011121314151617181920212223

备份数据库

输入 .backup命令备份指定的数据库,默认为 main 数据库:

sqlite> .backup backup.hr.db1

另外, .save命令可以将当前内存数据库保存为指定数据库文件,实现类似于备份的效果。例如:

sqlite> .save backup.hr.db1

也可以使用 .clone命令将当前数据库复制到指定数据库文件,例如:

sqlite> .clone hr2.db
departments... done
jobs... done
employees... done
sqlite_autoindex_jobs_1... done
sqlite_autoindex_employees_1... done
emp_department_ix... done
emp_job_ix... done
emp_manager_ix... done
emp_name_ix... done
dept_location_ix... done1234567891011

以上命令在当前工作目录中生成了一个新的数据库文件 hr2.db。

还原数据库

输入 .restore命令从备份文件中还原到指定的数据库,默认为 main 数据库:

sqlite> .restore newdb backup.hr.db1

注意,还原操作会删除 newdb 中原有的所有对象。

列出所有表

使用 .tables命令查看所有数据库中的表,例如:

sqlite> .tablesdepartments  employees    jobs         newdb.t12

sqlite3 工具支持表名的模糊查找,类似于 LIKE 运算符。例如:

sqlite> .tables 'emp%'employees12

查看表定义

输入 .schema命令查看数据库对象的定义,例如:

sqlite> .schema --indent employeesCREATE TABLE employees(
  employee_id INTEGER NOT NULL ,
  first_name CHARACTER VARYING(20) ,
  last_name CHARACTER VARYING(25) NOT NULL ,
  email CHARACTER VARYING(25) NOT NULL ,
  phone_number CHARACTER VARYING(20) ,
  hire_date DATE NOT NULL ,
  job_id CHARACTER VARYING(10) NOT NULL ,
  salary NUMERIC(8,2) ,
  commission_pct NUMERIC(2,2) ,
  manager_id INTEGER ,
  department_id INTEGER,
  CONSTRAINT emp_emp_id_pk PRIMARY KEY(employee_id) ,
  CONSTRAINT emp_salary_min CHECK(salary > 0) ,
  CONSTRAINT emp_email_uk UNIQUE(email),
  CONSTRAINT emp_dept_fk FOREIGN KEY(department_id) REFERENCES departments(department_id) ,
  CONSTRAINT emp_job_fk FOREIGN KEY(job_id) REFERENCES jobs(job_id) ,
  CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id));CREATE INDEX emp_department_ix ON employees(department_id);CREATE INDEX emp_job_ix ON employees(job_id);CREATE INDEX emp_manager_ix ON employees(manager_id);CREATE INDEX emp_name_ix ON employees(last_name, first_name);123456789101112131415161718192021222324

默认情况下, .schema命令显示所有对象的定义。另外, .fullschema命令可以显示额外的 sqlite_stat 统计表信息。

查看索引信息

输入 .indexes命令可以列出数据库中的所有索引:

sqlite> .indexes
dept_location_ix              emp_name_ix
emp_department_ix             sqlite_autoindex_employees_1
emp_job_ix                    sqlite_autoindex_jobs_1
emp_manager_ix12345

想要查看指定表上的索引,可以在该命令后增加一个表名。例如:

sqlite> .indexes jobs
sqlite_autoindex_jobs_112

上面的 .schema命令也可以用于查看索引的定义。

数据库的模式对象信息存储在  sqlite_schema 系统表中,因此也可以查询该表获取相关信息。例如:

sqlite> select * from newdb.sqlite_schema;table|t|t|2|CREATE TABLE t(id int)12

每个数据库都有一个 sqlite_schema,以上语句返回了 newdb 中的模式对象。

显式/修改当前设置

输入.show命令可以查看 sqlite3 中的各种设置,例如:

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"rowseparator: "\n"
       stats: off
       width:
    filename: hr.db12345678910111213

这些设置都提供了对应的修改命令,可以使用 .help 命令查看帮助信息,例如:

sqlite> .help echo.help echo.echo on|off             Turn command echo on or off123

.echo命令用于设置命令的回显,例如: http://dxb.myzx.cn/medicine/

sqlite> .echo on.echo onsqlite> select 1 as id;select 1 as id;112345

设置输出格式

sqlite3 提供了多种不同的结果输出格式,可以使用 .mode命令进行设置:

sqlite> .help mode.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii     Columns/rows delimited by 0x1F and 0x1E
     box       Tables using unicode box-drawing characters
     csv       Comma-separated values
     column    Output in columns.  (See .width)
     html      HTML <table> code     insert    SQL insert statements for TABLE
     json      Results in a JSON array
     line      One value per line
     list      Values delimited by "|"
     markdown  Markdown table format
     quote     Escape answers as for SQL
     table     ASCII-art table
     tabs      Tab-separated values
     tcl       TCL list elements1234567891011121314151617

默认的输出格式为 list,使用 | 作为字段的分隔符,使用发送给其他程序(例如 AWK)做进一步的处理。例如:

sqlite> select 1 as id, "apple" as name;id|name1|apple123

使用 .separator命令可以设置字段和数据行的分隔符,例如:

sqlite> .separator ", "sqlite> select 1 as id, "apple" as name;id, name1, apple1234

以上输出格式类似于 csv 模式。

box 模式可以为输出结果增加一个字符绘制的外框,例如:

sqlite> .mode box
sqlite> select 1 as id, "apple" as name;┌────┬───────┐
│ id │ name  │
├────┼───────┤
│ 1  │ apple │
└────┴───────┘1234567

json 模式可以用于输出一个 JSON 数组,例如:

sqlite> .mode json
sqlite> select 1 as id, "apple" as name;[{"id":1,"name":"apple"}]123

markdown 模式可以用于输出一个 Markdown 表格,例如:

sqlite> .mode markdown
sqlite> select 1 as id, "apple" as name;| id | name  ||----|-------|| 1  | apple |12345

insert 模式可以生成一个插入数据的语句,例如:

sqlite> .mode insert product
sqlite> select 1 as id, "apple" as name;INSERT INTO product(id,name) VALUES(1,'apple');123

其中,product 是插入语句的目标表。

对于 column、box、table 以及 markdown 模式,可以使用 .width命令设置每个字段的最小宽度。例如:

sqlite> .mode markdown
sqlite> .width 10 20sqlite> select 1 as id, "apple" as name;|     id     |         name         ||------------|----------------------|| 1          | apple                |123456

其他的输出格式可以自行进行尝试。

设置 NULL 显示

默认情况下,NULL 值显示为空,和空白字符很难区分。sqlite3 提供了 .nullvalue命令,可以设置 NULL 值的显示内容。例如: http://zzdxb.baikezh.com/

sqlite> select "" as id, null as name;id|name|sqlite> .nullvalue '[NULL]'sqlite> select "" as id, null as name;id|name|[NULL]12345678

显式执行时间

输入 .timer on命令可以自动显式查询语句消耗的时间,例如:

sqlite> select department_id,count(*) from employees group by 1;department_id|count(*)|110|120|230|640|150|4560|570|180|3490|3100|6110|2Run Time: real 0.014 user 0.000000 sys 0.000000123456789101112131415

输入 .timer off命令可以关闭执行时间的显式。

显示执行计划

使用 .eqp命令可以打开或者关闭执行计划的自动显示。例如:

sqlite> .eqp onsqlite> select count(*) from employees;QUERY PLAN`--SCAN TABLE employees USING COVERING INDEX emp_manager_ixcount(*)107123456

打开该设置相当于执行了一次 EXPLAIN QUERY PLAN query 语句。

执行脚本文件

输入 .read命令读取并执行 SQL 文件中的语句。例如:

sqlite> .read get_employees.sqlfirst_name|last_name
Ellen|Abel
Sundar|Ande
Mozhe|Atkinson
David|Austin
Hermann|Baer1234567

其中,get_employees.sql 文件中的内容如下:

select first_name, last_namefrom employeeslimit 5;123

保存查询结果 http://dxb.myzx.cn/shijiazhuang/

输入 .output命令将查询结果输出到指定文件,例如:

sqlite> .output result.txt
sqlite> select * from jobs;...sqlite> .output
sqlite> select 1 as id;id11234567

执行 .output 命令之后的查询结果都会写入 result.txt 文件,直接输入 .output 命令表示将结果打印到标准错误输出。

另外, .once命令也可以将查询结果输出到文件,但是它只对随后的一次 SQL 命令有效。

导出 SQL 文件 http://ask.baikezh.com/jilin/

.dump命令可以将当前数据库中的所有内容导出为 SQL 语句,例如:

sqlite3.exe hr.db .dump > hr.sql1

以上命令将 hr.db 中的所有对象和数据导出到 hr.sql 文件中。

恢复损坏的数据库 http://ask.baikezh.com/hubei/

.recover命令和 .dump 命令类似,也可以用于将整个数据库的内容导出为 SQL 语句;但是它不是通过 SQL 接口导出数据,而是直接扫描物理数据页获取所有内容。对于损坏的数据库文件,.recover 命令可以尝试恢复尽可能多的数据。例如:

sqlite3.exe hr.db .recover > hr.sql1

导入/导出 CSV

输入 .import命令从 CSV 文件中导入数据到 SQLite 表中,在此之前需要将 mode 变量设置为 csv。例如:

sqlite> .mode csv
sqlite> .import product.csv product
sqlite> select * from product;id,name1,apple2,banana3,orange1234567

其中,product 是数据库中的表名。如果该表不存在,使用 CSV 文件中的第一行内容作为字段创建表;如果该表已经存在,CSV 文件中的所有内容都被看做数据;如果第一行是标题,可以使用 --skip 1 选项跳过一行数据。

如果想要将查询结果导出到 CSV 文件,可以先将 mode 变量设置为 csv,然后使用 .once 命令导出结果:

sqlite> .headers onsqlite> .mode csv
sqlite> .once employees.csv
sqlite> SELECT * FROM employees;sqlite> .system employees.csv12345

最后的 .system 命令用于执行操作系统命令,在 Windows 中相当于双击打开 employees.csv 文件。

除此之外,也可以使用 .excel命令将下一次查询结果导出到系统默认的电子表格程序(例如 Excel 或者 LibreOffice):

sqlite> .headers onsqlite> .excel
sqlite> select * from employees;123

在 Windows 中,以上命名最终会打开一个 Excel 文件,其中包含了查询结果。该命令相当于上面的 .csv、.once 以及.system 命令组合,或者 .once -x 命令。

SQLite 归档功能 http://ask.baikezh.com/lanzhou/

SQLite 支持类似于 zip 归档或者 tar 归档的功能,通过 .archive命令或者 -A 命令行参数实现。.archive 支持以下选项之一:

选项 长选项 描述
-c --create 创建一个新的归档。
-x --extract 从归档中提取文件。
-i --insert 增加文件到归档。
-t --list 列出归档中的文件。
-u --update 更新归档中的文件。

归档命令还支持以下参数选项,用于指定其他信息:

选项 长选项 描述
-v --verbose 显示详细的处理过程。
-f FILE --file FILE 指定生成的归档文件,默认使用 main 数据库的文件。
-a FILE --append FILE 与 --file 参数类似,但是以追加方式打开归档。
-C DIR --directory DIR 指定相对路径所在的目录,默认为当前工作目录。
-n --dryrun 显示归档操作对应的 SQL 语句,不会实际执行操作。
-- -- 表示随后的内容都是命令行参数,而不是选项。

例如,以下语句都可以将 3 个文件归档为 new_archive.db:

sqlite3 new_archive.db -Acv file1.txt file2.txt file3.txt
sqlite> .ar -cv -f new_archive.db file1.txt file2.txt file3.txt
file1.txt
file2.txt
file3.txt123456

以下语句用于列出 new_archive.db 中的文件: http://ask.baikezh.com/

sqlite> .ar --list -f new_archive.dbfile1.txt
file2.txt
file3.txt1234

以下命令从归档中提取 file1.txt 文件到目录 dir1 中:

sqlite> .ar -x -f new_archive.db -C dir1 file1.txt1

sqlite3 通过 zipfile 扩展支持 zip 文件的压缩和解压,例如:

sqlite> .ar -c -f archive.zip file1.txt file2.txt file3.txt1

读写二进制文件 http://dxb.myzx.cn/guangxi/

sqlite3 提供了两个应用程序定义的 SQL 函数,可以用于读取二进制文件到表中,或者将表中的内容写入二进制文件。

readfile(X)函数可以将整个文件内容读取为 BLOB 数据,然后存入表中。例如:

sqlite> CREATE TABLE images(name TEXT, type TEXT, img BLOB);sqlite> INSERT INTO images(name,type,img)
   ...>   VALUES('icon','jpeg',readfile('icon.jpg'));123

writefile(X,Y)函数可以将二进制内容 Y 写入文件 X,并且返回写入的字节数。例如:

sqlite> SELECT writefile('icon.jpg',img) FROM images WHERE name='icon';writefile('icon.jpg',img)343618123

这两个函数没有包含在 SQLite 核心代码库中,而是通过可加载的扩展 ext/misc/fileio.c 文件提供。

sqlite3 还提供了一个内置的函数 edit(),可以通过调用操作系统中的软件编辑字段内容。例如:

sqlite> UPDATE docs SET body=edit(body, 'WINWORD.EXE') WHERE name='report-15';1

以上命令调用 Word 处理 body 字段中的内容,编辑完成后保存退出,SQLite 自动更新相应的字段内容。

执行系统命令 http://zzdxb.baikezh.com/xinxiang/

输入 .shell或者 .system命令执行操作系统的命令并返回 sqlite3,例如:

sqlite> .shell cd
D:\Software\sqlite-tools-win32-x86-3330000sqlite> .system dir /B
get_employees.sqlhr.db
new.db
result.txt
sqldiff.exe
sqlite3.exe
sqlite3_analyzer.exe1234567891011

退出客户端 http://ask.baikezh.com/liaoning/

输入 .exit或者 .quit命令退出 sqlite3 命令行:

sqlite> .exit1

.exit code命令可以返回一个退出码,通常用于编写脚本程序。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30239065/viewspace-2728370/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-05-08

  • 博文量
    150
  • 访问量
    62574