玩轉(zhuǎn)SQLite2：SQLite命令行基本操作

本篇介紹SQLite的命令行基本操作

1 SQLite 點(diǎn)命令

SQLite 的點(diǎn)命令，是一些以點(diǎn)為開(kāi)頭的命令：

完整的點(diǎn)指令如下：

.archive ... Manage SQL archives

.auth ON|OFF Show authorizer callbacks

.backup ?DB? FILE 備份DB數(shù)據(jù)庫(kù)（默認(rèn)是 "main"）到 FILE 文件

.bail on|off 發(fā)生錯(cuò)誤后停止，默認(rèn)為 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 列出數(shù)據(jù)庫(kù)的名稱及其所依附的文件

.dbconfig ?op? ?val? List or change sqlite3_db_config() options

.dbinfo ?DB? Show status information about the database

.dump ?OBJECTS? 以 SQL 文本格式轉(zhuǎn)儲(chǔ)數(shù)據(jù)庫(kù)

.echo on|off 開(kāi)啟或關(guān)閉 echo 命令

.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN

.excel Display the output of next command in spreadsheet

.exit ?CODE? 以CODE碼退出SQLite提示符

.expert EXPERIMENTAL. Suggest indexes for queries

.explain ?on|off|auto? 開(kāi)啟或關(guān)閉適合于 EXPLAIN 的輸出模式，默認(rèn)是:auto

.filectrl CMD ... Run various sqlite3_file_control() operations

.fullschema ?--indent? Show schema and the content of sqlite_stat tables

.headers on|off 開(kāi)啟或關(guān)閉頭部顯示

.help ?-all? ?PATTERN? 顯示幫助

.import FILE TABLE 導(dǎo)入來(lái)自 FILE 文件的數(shù)據(jù)到 TABLE 表中

.imposter INDEX TABLE Create imposter table TABLE on index INDEX

.indexes ?TABLE? 顯示所有索引的名稱

.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT

.lint OPTIONS Report potential schema issues.

.load FILE ?ENTRY? 加載一個(gè)擴(kuò)展庫(kù)

.log FILE|off 開(kāi)啟或關(guān)閉日志，可以是stderr或stdout

.mode MODE ?TABLE? 設(shè)置輸出模式

.nonce STRING Disable safe mode for one command if the nonce matches

.nullvalue STRING 在 NULL 值的地方輸出 STRING 字符串

.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE

.open ?OPTIONS? ?FILE? 關(guān)閉存在的數(shù)據(jù)庫(kù)或重新打開(kāi)文件

.output ?FILE? Send output to FILE or stdout if FILE is omitted

.parameter CMD ... Manage SQL parameter bindings

.print STRING... 逐字地輸出 STRING 字符串

.progress N Invoke progress handler after every N opcodes

.prompt MAIN CONTINUE 替換標(biāo)準(zhǔn)提示符

.quit 退出 SQLite 提示符

.read FILE Read input from FILE

.recover Recover as much data as possible from corrupt db.

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

.save FILE Write in-memory database into FILE

.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 顯示各種設(shè)置的當(dāng)前值

.stats ?ARG? 開(kāi)啟或關(guān)閉統(tǒng)計(jì)

.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 嘗試打開(kāi)鎖定的表 MS 毫秒

.timer on|off 開(kāi)啟或關(guān)閉SQL定時(shí)器

.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

例如，使用.show指令可以查看當(dāng)前的各種設(shè)置：

2 SQLite 創(chuàng)建數(shù)據(jù)庫(kù)

使用sqlite3 命令來(lái)創(chuàng)建數(shù)據(jù)庫(kù)有兩種方式

2.1 方式1：sqlite3+數(shù)據(jù)庫(kù)名

例如，使用sqlite3 test1.db創(chuàng)建test1數(shù)據(jù)庫(kù)，然后使用.databases查看數(shù)據(jù)庫(kù)

.

2.2 方式2：使用.open命令

例如，使用.open test2.db創(chuàng)建test2數(shù)據(jù)庫(kù)

2.3 將數(shù)據(jù)庫(kù)導(dǎo)出到文件

使用 .dump 點(diǎn)命令導(dǎo)出數(shù)據(jù)庫(kù)到文本文件中

sqlite3 test1.db .dump > test1.sql

也可以從生成的 testDB.sql 恢復(fù)：

sqlite3 test1.db < test1.sql

3 SQLite 創(chuàng)建表

可以通過(guò)CREATE TABLE語(yǔ)句來(lái)創(chuàng)建表，其基本語(yǔ)法為：

CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

例如，創(chuàng)建一個(gè) COMPANY 表，ID 作為主鍵，NOT NULL 的約束表示在表中創(chuàng)建紀(jì)錄時(shí)這些字段不能為 NULL：

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

然后可以使用.tables命令來(lái)驗(yàn)證表是否已成功創(chuàng)建

sqlite>.tables
COMPANY 

也可以使用.schema命令得到表的完整信息

sqlite>.schema COMPANY
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

最后將數(shù)據(jù)庫(kù)導(dǎo)出到.sql文件查看：

end

審核編輯：湯梓紅