SQLite教程 #
安装SQLite #
SQLite官网在此 https://sqlite.org/ 下载页在此 https://sqlite.org/download.html
Windows下载Precompiled Binaries for Windows下的sqlite-tools-win32-x86-3390200.zip
下载后解压即可使用。
D:\apps\sqlite>sqlite3.exe learnsql.db ###建立数据库文件learnsql.db
SQLite version 3.39.2 2022-07-21 15:24:47
Enter ".help" for usage hints.
sqlite> .databases ###查看数据库
main: D:\apps\sqlite\learnsql.db r/w
sqlite> .quit ###退出sqlite
Debian下直接使用sudo apt install sqlite3命令来安装。
alair@hpzb14:~$ sqlite3 learnsql
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite> .database
main: /home/alair/learnsql r/w
sqlite> .quit
SQLite常用操作 #
连接SQLite数据库 #
直接输入sqlite3即可开始使用slqite，默认session为内存临时会话，关闭数据就会清理。
C:\Users\alair\Documents>sqlite3
SQLite version 3.39.2 2022-07-21 15:24:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
使用自定义数据库文件有以下两个方法：
- 直接打开自定义数据库文件。
C:\Users\alair\Documents>sqlite3 learnsql.db
SQLite version 3.39.2 2022-07-21 15:24:47
Enter ".help" for usage hints.
sqlite>
- 进入sqlite后用
.open命令打开具体文件。
C:\Users\alair\Documents>sqlite3
SQLite version 3.39.2 2022-07-21 15:24:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open learnsql.db
sqlite> .databases
main: C:\Users\alair\Documents\learnsql.db r/w
sqlite>
显示当前连接下的数据库信息 #
sqlite> .databases
main: C:\Users\alair\Documents\learnsql.db r/w
还可以使用ATTACH DATABASE来添加第二个数据库文件。
sqlite> attach database "logs.db" as logs;
sqlite> .databases
main: C:\Users\alair\Documents\learnsql.db r/w
logs: C:\Users\alair\Documents\logs.db r/w
sqlite>
显示数据库中的表 #
sqlite> .tables
domains; logs;
还可以使用%通配符来查询
sqlite> .table '%o%'
domains; logs;
sqlite> .table '%l%'
logs;
sqlite>
显示表结构 #
显示某一个表结构
sqlite> .schema domains
CREATE TABLE domains (ID, ICP, DomainName, RegDate, EndDate);
显示所有表结构
sqlite> .fullschema
CREATE TABLE domains (ID, ICP, DomainName, RegDate, EndDate);
CREATE TABLE logs (ID, google, baidu, sogou);
/* No STAT tables available */
保存查询结果为文件 #
sqlite> .output r.txt
sqlite> select * from domains;
从.sql文件执行查询命令
#
z.sql文件内容如下
select * from domains where icp='namesilo';
如下执行该z.sql文件
sqlite> .mode column
sqlite> .header on
sqlite> .read z.sql
ID ICP DomainName RegDate EndDate
-- -------- ------------- ---------- ----------
4 namesilo getos.org 2016/10/9 2022/10/9
8 namesilo les.moe 2015/12/31 2022/12/31
9 namesilo regex.in 2020/1/3 2023/1/3
12 namesilo blbl.dev 2020/1/17 2023/1/17
13 namesilo raspi.in 2014/2/27 2023/2/27
14 namesilo pornie.in 2016/3/10 2023/3/10
15 namesilo aosp.me 2016/3/17 2023/3/17
16 namesilo bailuyuan.org 2017/3/23 2023/3/23
18 namesilo ustv.xyz 2018/3/24 2023/3/25
20 namesilo ciux.org 2010/4/26 2023/4/26
21 namesilo avlist.top 2021/6/5 2023/6/5
24 namesilo ltecn.com 2014/7/26 2025/7/26
25 namesilo pornie.top 2016/3/10 2027/3/10
退出sqlite #
退出sqlite可用命令为.quit和.exit，区别是.exit可附带退出码。
显示所有sqlite命令及解释 #
sqlite> .help
.archive ... Manage SQL archives
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.cd DIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.connection [close] [#] Open or close an auxiliary database connection
.databases List names and files of attached databases
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
.dbinfo ?DB? Show status information about the database
.dump ?OBJECTS? Render database content as SQL
.echo on|off Turn command echo on or off
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
.excel Display the output of next command in spreadsheet
.exit ?CODE? Exit this program with return-code CODE
.expert EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
.filectrl CMD ... Run various sqlite3_file_control() operations
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off Turn display of headers on or off
.help ?-all? ?PATTERN? Show help text for PATTERN
.import FILE TABLE Import data from FILE into TABLE
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
.indexes ?TABLE? Show names of indexes
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
.lint OPTIONS Report potential schema issues.
.load FILE ?ENTRY? Load an extension library
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?OPTIONS? Set output mode
.nonce STRING Suspend safe mode for one command if nonce matches
.nullvalue STRING Use STRING in place of NULL values
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
.output ?FILE? Send output to FILE or stdout if FILE is omitted
.parameter CMD ... Manage SQL parameter bindings
.print STRING... Print literal STRING
.progress N Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILE Read input from FILE or command output
.recover Recover as much data as possible from corrupt db.
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...)
.scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN? Show the CREATE statements matching PATTERN
.selftest ?OPTIONS? Run tests defined in the SELFTEST table
.separator COL ?ROW? Change the column and row separators
.session ?NAME? CMD ... Create or control sessions
.sha3sum ... Compute a SHA3 hash of database content
.shell CMD ARGS... Run CMD ARGS... in a system shell
.show Show the current values for various settings
.stats ?ARG? Show stats or turn stats on or off
.system CMD ARGS... Run CMD ARGS... in a system shell
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
.testcase NAME Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ... Run various sqlite3_test_control() operations
.timeout MS Try opening locked tables for MS milliseconds
.timer on|off Turn SQL timer on or off
.trace ?OPTIONS? Output each SQL statement as it is run
.vfsinfo ?AUX? Information about the top-level VFS
.vfslist List all available VFSes
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set minimum column widths for columnar output
sqlite>