sqlite3

2023-11-12 2690 words 6 minutes

Contents

SQLite

SQLite是一种开源，零配置，独立的，独立的，事务关系数据库引擎，旨在嵌入到应用程序中。

一、安装sqlite

SQLite以其零配置而闻名，所以不需要复杂的设置或管理。

# rpm
yum -y install sqlite3

# apt
apt install sqlite3

二、sqlite命令

进入sqlite

[sugar@MacBook-Pro ~]$ sqlite3
SQLite version 3.32.3 2020-06-18 14:16:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .exit

如需获取可用的点命令的清单，可以在任何时候输入 “.help”

sqlite> .help
.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
.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 ?TABLE?            Render database content as SQL
.echo on|off             Turn command echo on or off

上面的命令会显示各种重要的 SQLite 点命令的列表，如下所示：

命令	描述
.backup ? DB? FILE	备份 DB 数据库（默认是 “main”）到 FILE 文件。
.bail ON\|OFF	发生错误后停止。默认为 OFF。
.databases	列出数据库的名称及其所依附的文件。
.dump ?TABLE?	以 SQL 文本格式转储数据库。如果指定了 TABLE 表，则只转储匹配 LIKE 模式的 TABLE 表。
.echo ON\|OFF	开启或关闭 echo 命令。
.exit	退出 SQLite 提示符。
.explain ON\|OFF	开启或关闭适合于 EXPLAIN 的输出模式。如果没有带参数，则为 EXPLAIN on，即开启 EXPLAIN。
.header(s) ON\|OFF	开启或关闭头部显示。
.help	显示消息。
.import FILE TABLE	导入来自 FILE 文件的数据到 TABLE 表中。
.indices ?TABLE?	显示所有索引的名称。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表的索引。
.load FILE ?ENTRY?	加载一个扩展库。
.log FILE\|off	开启或关闭日志。FILE 文件可以是 stderr（标准错误）/stdout（标准输出）。
.mode MODE	设置输出模式，MODE 可以是下列之一：csv 逗号分隔的值column 左对齐的列html HTML 的代码insert TABLE 表的 SQL 插入（insert）语句line 每行一个值list 由 .separator 字符串分隔的值tabs 由 Tab 分隔的值tcl TCL 列表元素
.nullvalue STRING	在 NULL 值的地方输出 STRING 字符串。
.output FILENAME	发送输出到 FILENAME 文件。
.output stdout	发送输出到屏幕。
.print STRING…	逐字地输出 STRING 字符串。
.prompt MAIN CONTINUE	替换标准提示符。
.quit	退出 SQLite 提示符。
.read FILENAME	执行 FILENAME 文件中的 SQL。
.schema ?TABLE?	显示 CREATE 语句。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表。
.separator STRING	改变输出模式和 .import 所使用的分隔符。
.show	显示各种设置的当前值。
.stats ON\|OFF	开启或关闭统计。
.tables ?PATTERN?	列出匹配 LIKE 模式的表的名称。
.timeout MS	尝试打开锁定的表 MS 毫秒。
.width NUM NUM	为 “column” 模式设置列宽度。
.timer ON\|OFF	开启或关闭 CPU 定时器。

用.show命令来查看 SQLite 命令提示符的默认设置

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

格式化输出

.header on
.mode column
.timer on
.changes on

这样设置只是在当前终端中设置了格式化输出，当再次打开时则需要再次设置，可以把sqlite的配置写入到~/.sqliter

[sugar@localhost domain]$ cat ~/.sqliterc 
.header on
.mode column
.timer on

[sugar@localhost domain]$ sqlite3 domain.db 
-- Loading resources from /Users/sugar/.sqliterc
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> .tables
ssls
sqlite> select * from ssls;
id  created_at                        updated_at                        deleted_at  status  web              
--  --------------------------------  --------------------------------  ----------  ------  -----------------
1   2022-10-14 23:31:24.473488+08:00  2022-10-14 23:31:24.473488+08:00              1       https://gitea.com
Run Time: real 0.001 user 0.000178 sys 0.000250

三、sql操作

创建数据库

$ sqlite3 cccc.db
SQLite version 3.32.3 2020-06-18 14:16:19
Enter ".help" for usage hints.
sqlite>

上面的命令将在当前目录下创建一个文件 cccc.db。该文件将被 SQLite 引擎用作数据库。如果您已经注意到 sqlite3 命令在成功创建数据库文件之后，将提供一个 sqlite> 提示符。

一旦数据库被创建，您就可以使用 SQLite 的 .databases 命令来检查它是否在数据库列表中，如下所示：

sqlite> .database
main: /Users/sugar/Desktop/workspace/cccc.db
sqlite>

数据库备份

sqlite3 cccc.db .dump > cccc.sql

恢复数据库

sqlite3 cccc.db < cccc.sql

查看表详细信息

.schema

非交互式执行sql

[sugar@Sugar sqlite]$ sqlite3 cccc.db "select * from msg;"
-- Loading resources from /Users/sugar/.sqliterc
age  id  
---  ----
19   1001

四、sql语法

大小写

SQLite不区分大小写。但是，有一些区分大小写的命令。例如：GLOB和glob在SQLite语句中有不同的含义。

注释：

注释用于在SQLite代码中增加代码的可读性。
注释不能嵌套。
注释以两个连续的“ - ”字符。
也可使用“/*”字符开始，并延伸至下一个“*/”字符对所包括的内容视为注释。

1、表操作

列的数据类型

NULL —该值为 NULL 值
INTEGER —有符号整数
REAL —浮点值
TEXT-文本字符串
BLOB —数据块

-- 创建表
sqlite> CREATE TABLE Testing(Id INTEGER);
Run Time: real 0.002 user 0.000269 sys 0.001006
changes: 0   total_changes: 0
sqlite> 

-- 查看表结构
sqlite> .schema
CREATE TABLE Testing(Id INTEGER);
sqlite> .schema Testing
CREATE TABLE Testing(Id INTEGER);
sqlite>

-- 如果存在则不创建
CREATE TABLE IF NOT EXISTS Testing(Id INTEGER);

-- 复制表
CREATE TABLE Cars2 AS SELECT * FROM Cars;

-- 查看所有表
sqlite> .tables
Testing

-- 通用语法
sqlite> select * from sqlite_master;
type   name     tbl_name  rootpage  sql                             
-----  -------  --------  --------  --------------------------------
table  Testing  Testing   2         CREATE TABLE Testing(Id INTEGER)
Run Time: real 0.001 user 0.000111 sys 0.000102
changes: 0   total_changes: 0
sqlite> 

-- 删除表
sqlite> DROP TABLE Testing;

sqlite> DROP TABLE IF EXISTS Testing;


-- 重命名数表
sqlite> CREATE TABLE Names(Id INTEGER, Name TEXT);
sqlite> ALTER TABLE Names RENAME TO NamesOfFriends;
sqlite> .schema NamesOfFriends

2、数据操作

sqlite> CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT,Price INTEGER DEFAULT 'Not available');

-- 插入数据
sqlite> INSERT INTO Cars(Id, Name, Price) VALUES(1, 'Audi', 52642);

-- 删除数据
sqlite> DELETE FROM Cars2 WHERE Id=1;

-- 删除表所有数据
sqlite> DELETE FROM Cars2;

-- 更新数据
sqlite> UPDATE Cars SET Name='Skoda Octavia' WHERE Id=3;

3、约束

# 非空
NOT NULL

# 唯一
UNIQUE

# 主键
PRIMARY KEY

# 外键
FOREIGN KEY

# 默认值
sqlite> CREATE TABLE Hotels(Id INTEGER PRIMARY KEY, Name TEXT,City TEXT DEFAULT 'not available');

所有的SQLite语句都是以关键字(如：SELECT，INSERT，UPDATE，DELETE，ALTER，DROP等)开始的。所有语句都以分号(;)结尾。

ANALYZE语句的语法：

ANALYZE;  
-- or  
ANALYZE database_name;  
-- or  
ANALYZE database_name.table_name;

AND/OR子句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  CONDITION-1 {AND|OR} CONDITION-2;

ALTER TABLE语句的语法

ALTER TABLE table_name ADD COLUMN column_def...;

ALTER TABLE语句(Rename)语句的语法

ALTER TABLE table_name RENAME TO new_table_name;

ATTACH DATABASE语句的语法：

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

BEGIN TRANSACTION语句的语法：

BEGIN;  
-- or  
BEGIN EXCLUSIVE TRANSACTION;

BETWEEN语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name BETWEEN val-1 AND val-2;  
SQLite COMMIT Statement:  
COMMIT;

CREATE INDEX语句的语法：

CREATE INDEX index_name  
ON table_name ( column_name COLLATE NOCASE );

CREATE UNIQUE INDEX语句的语法：

CREATE UNIQUE INDEX index_name  
ON table_name ( column1, column2,...columnN);

CREATE TABLE语句的语法：

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

CREATE TRIGGER语句的语法：

CREATE TRIGGER database_name.trigger_name   
BEFORE INSERT ON table_name FOR EACH ROW  
BEGIN   
   stmt1;   
   stmt2;  
   ....  
END;

CREATE VIEW语句的语法：

CREATE VIEW database_name.view_name  AS  
SELECT statement....;

CREATE VIRTUAL TABLE语句的语法：

CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );  
-- or  
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );

COMMIT TRANSACTION语句的语法：

COMMIT;

COUNT语句的语法：

SELECT COUNT(column_name)  
FROM   table_name  
WHERE  CONDITION;

DELETE语句的语法：

DELETE FROM table_name  
WHERE  {CONDITION};

DETACH DATABASE语句的语法：

DETACH DATABASE 'Alias-Name';

DISTINCT语句的语法：

SELECT DISTINCT column1, column2....columnN  
FROM   table_name;

DROP INDEX语句的语法：

DROP INDEX database_name.index_name;

DROP TABLE语句的语法：

DROP TABLE database_name.table_name;

DROP VIEW语句的语法：

DROP INDEX database_name.view_name;

SQLite DROP TRIGGER 语句的语法：

DROP INDEX database_name.trigger_name;

SQLite EXISTS语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name EXISTS (SELECT * FROM   table_name );

EXPLAIN语句的语法：

EXPLAIN INSERT statement...;  
-- or   
EXPLAIN QUERY PLAN SELECT statement...;

GLOB语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name GLOB { PATTERN };

GROUP BY语句的语法：

SELECT SUM(column_name)  
FROM   table_name  
WHERE  CONDITION  
GROUP BY column_name;

HAVING语句的语法：

SELECT SUM(column_name)  
FROM   table_name  
WHERE  CONDITION  
GROUP BY column_name  
HAVING (arithematic function condition);

INSERT INTO语句的语法：

INSERT INTO table_name( column1, column2....columnN)  
VALUES ( value1, value2....valueN);

IN语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name IN (val-1, val-2,...val-N);

Like语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name LIKE { PATTERN };

NOT IN语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name NOT IN (val-1, val-2,...val-N);

ORDER BY语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  CONDITION  
ORDER BY column_name {ASC|DESC};

PRAGMA语句的语法：

PRAGMA pragma_name;

有关pragma的几个示例：

PRAGMA page_size;  
PRAGMA cache_size = 1024;  
PRAGMA table_info(table_name);

RELEASE SAVEPOINT语句的语法：

RELEASE savepoint_name;

REINDEX语句的语法：

REINDEX collation_name;  
REINDEX database_name.index_name;  
REINDEX database_name.table_name;

ROLLBACK语句的语法：

ROLLBACK;  
-- or  
ROLLBACK TO SAVEPOINT savepoint_name;

SAVEPOINT语句的语法：

SAVEPOINT savepoint_name;

SELECT语句的语法：

SELECT column1, column2....columnN  
FROM   table_name;

UPDATE语句的语法：

UPDATE table_name  
SET column1 = value1, column2 = value2....columnN=valueN  
[ WHERE  CONDITION ];

VACUUM语句的语法：

VACUUM;  
SQLite WHERE Clause:  
SELECT column1, column2....columnN  
FROM   table_name  
WHERE  CONDITION;