Home A - Z Changelog
Page Index

Blame

82d1c4 Qwas 2024-03-31 14:07:47 1 
# SQLite学习
2
3
为了简化操作，直接在 Ubuntu 上演示。
4
5
## 安装 SQLite
6
7
WSL 版本的 Ubuntu 默认没有安装 SQLite。
8
9
```sh
10
sudo apt install sqlite3
11
```
12
13
![sqlite3 version](https://static.qwas.fun/notes/2024/01/sqlite3-version.png)
14
15
## SQLite 基本语法
16
17
创建数据库，若已存在，则打开改数据库
18
19
```sh
20
sqlite3 qwas.db
21
```
22
23
创建表
24
25
```sql
26
CREATE TABLE
27
```
28
29
```sql
30
CREATE TABLE BOOK(
31
ID INTEGER PRIMARY KEY,
32
SensorID INTEGER,
33
SiteNum INTEGER,
34
PublicTime VARCHAR(12),
35
SensorParameter REAL
36
);
37
```
38
39
![sqlite3 create table](https://static.qwas.fun/notes/2024/01/sqlite3-create-table.png)
40
41
插入语句
42
43
```sql
44
INSERT
45
```
46
47
```sql
48
INSERT INTO "BOOK" VALUES(NULL, 1, 0, '20240101123', 18.9);
49
INSERT INTO "BOOK" VALUES(2, 1, 0, '20240101123', 18.9);
50
```
51
52
`.schema`查看数据库种所有表的结构
53
54
`.schema 表名`查看数据库种指定表的结构
55
56
`.table`查看当前数据库的所有表
57
58
`.exit`退出数据库
59
60
`.help`查看帮助
61
62
```text
63
sqlite> .help
64
.archive ... Manage SQL archives
65
.auth ON|OFF Show authorizer callbacks
66
.backup ?DB? FILE Backup DB (default "main") to FILE
67
.bail on|off Stop after hitting an error. Default OFF
68
.binary on|off Turn binary output on or off. Default OFF
69
.cd DIRECTORY Change the working directory to DIRECTORY
70
.changes on|off Show number of rows changed by SQL
71
.check GLOB Fail if output since .testcase does not match
72
.clone NEWDB Clone data into NEWDB from the existing database
73
.connection [close] [#] Open or close an auxiliary database connection
74
.databases List names and files of attached databases
75
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
76
.dbinfo ?DB? Show status information about the database
77
.dump ?OBJECTS? Render database content as SQL
78
.echo on|off Turn command echo on or off
79
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
80
.excel Display the output of next command in spreadsheet
81
.exit ?CODE? Exit this program with return-code CODE
82
.expert EXPERIMENTAL. Suggest indexes for queries
83
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
84
.filectrl CMD ... Run various sqlite3_file_control() operations
85
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
86
.headers on|off Turn display of headers on or off
87
.help ?-all? ?PATTERN? Show help text for PATTERN
88
.import FILE TABLE Import data from FILE into TABLE
89
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
90
.indexes ?TABLE? Show names of indexes
91
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
92
.lint OPTIONS Report potential schema issues.
93
.load FILE ?ENTRY? Load an extension library
94
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
95
.mode MODE ?TABLE? Set output mode
96
.nonce STRING Disable safe mode for one command if the nonce matches
97
.nullvalue STRING Use STRING in place of NULL values
98
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
99
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
100
.output ?FILE? Send output to FILE or stdout if FILE is omitted
101
.parameter CMD ... Manage SQL parameter bindings
102
.print STRING... Print literal STRING
103
.progress N Invoke progress handler after every N opcodes
104
.prompt MAIN CONTINUE Replace the standard prompts
105
.quit Exit this program
106
.read FILE Read input from FILE
107
.recover Recover as much data as possible from corrupt db.
108
.restore ?DB? FILE Restore content of DB (default "main") from FILE
109
.save FILE Write in-memory database into FILE
110
.scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off
111
.schema ?PATTERN? Show the CREATE statements matching PATTERN
112
.selftest ?OPTIONS? Run tests defined in the SELFTEST table
113
.separator COL ?ROW? Change the column and row separators
114
.session ?NAME? CMD ... Create or control sessions
115
.sha3sum ... Compute a SHA3 hash of database content
116
.shell CMD ARGS... Run CMD ARGS... in a system shell
117
.show Show the current values for various settings
118
.stats ?ARG? Show stats or turn stats on or off
119
.system CMD ARGS... Run CMD ARGS... in a system shell
120
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
121
.testcase NAME Begin redirecting output to 'testcase-out.txt'
122
.testctrl CMD ... Run various sqlite3_test_control() operations
123
.timeout MS Try opening locked tables for MS milliseconds
124
.timer on|off Turn SQL timer on or off
125
.trace ?OPTIONS? Output each SQL statement as it is run
126
.vfsinfo ?AUX? Information about the top-level VFS
127
.vfslist List all available VFSes
128
.vfsname ?AUX? Print the name of the VFS stack
129
.width NUM1 NUM2 ... Set minimum column widths for columnar output
130
```