Sqlite基础语法

1. Sqlite数据库命令

1.1. SQLite 点命令的列表

命令 描述
.backup ?DB? FILE 备份 DB 数据库(默认是 “main”)到 FILE 文件。
.bail ON OFF
.connect ?DB? FILE 连接到数据库。如果指定了 DB,则连接到 DB 数据库。如果指定了 FILE,则连接到 FILE 文件。
.changes 显示自上次重置后,数据库的更改数。
.close 关闭数据库。
.commit 提交当前事务。
.configure ?OPTIONS? 配置 SQLite。OPTIONS 为逗号分隔的选项列表。
.create ?DB? TABLE 创建数据库。如果指定了 DB,则创建 DB 数据库。如果指定了 TABLE 表,则创建 TABLE 表。
.dbstat(s) ?DB? TABLE? 显示数据库的统计信息。如果指定了 DB,则显示 DB 数据库的统计信息。如果指定了 TABLE 表,则只显示匹配 LIKE 模式的 TABLE 表的统计信息。
.dbinfo ?DB? 显示数据库的描述信息。如果指定了 DB,则显示 DB 数据库的描述信息。
.databases 列出数据库的名称及其所依附的文件
.dump ?TABLE? 以 SQL 文本格式转储数据库。如果指定了 TABLE 表,则只转储,匹配 LIKE 模式的 TABLE 表。
.echo ON OFF
.exit 退出 SQLite 提示符。
.explain ON OFF
.header(s) ON OFF
.help 显示消息。
.import FILE TABLE 导入来自 FILE 文件的数据到 TABLE 表中。
.indices ?TABLE? 显示所有索引的名称。如果指定了 TABLE 表,则只显示匹配 LIKE 模式的 TABLE 表的索引。
.load FILE ?ENTRY? 加载一个扩展库。
.log FILE off
.profile ON OFF
.profile FILE 将性能分析器输出到 FILE 文件。
.profile FILE ?LINE? 将性能分析器输出到 FILE 文件的 LINE 行。
.mode MODE 设置输出模式,MODE 可以是下列之一:csv 逗号分隔的值。column 左对齐的列。html HTML 的 <\table> 代码。insert TABLE 表的 SQL 插入(insert)语句。line 每行一个值。list 由 .separator 字符串分隔的值。tabs 由 Tab 分隔的值。tcl TCL 列表元素
.nullvalue STRING 在 NULL 值的地方输出 STRING 字符串。
.read FILENAME 执行 FILENAME 文件中的 SQL。
.output FILENAME 发送输出到 FILENAME 文件。
.output stdout 发送输出到屏幕。
.profile FILENAME 将性能分析器输出到 FILENAME 文件。
.print STRING… 逐字地输出 STRING 字符串。
.prompt MAIN CONTINUE 替换标准提示符。
.quit 退出 SQLite 提示符。
.schema ?TABLE? 显示 CREATE 语句。如果指定了 TABLE 表,则只显示匹配 LIKE 模式的 TABLE 表。
.separator STRING 改变输出模式和 .import 所使用的分隔符。
.show 显示各种设置的当前值。
.stats ON OFF
.tables ?PATTERN? 列出匹配 LIKE 模式的表的名称。
.test FILENAME 执行 FILENAME 文件中的测试。
.testctrl ?KEY? VALUE? 设置或显示测试控制变量。KEY 可以是下列之一: abort ON
.timeout MS 尝试打开锁定的表 MS 毫秒。
.trace FILENAME 将 SQLite 的调试输出发送到 FILENAME 文件。
.version 显示 SQLite 版本。
.vacuum ?DB? 为 DB 数据库执行 VACUUM。如果指定了 DB,则执行 DB 数据库的 VACUUM。
.vfsname 显示当前 VFS 的名称。
.width NUM NUM 为 “column” 模式设置列宽度。
.timer ON OFF

1.2. SQLite 语法

1.2.1. 大小写敏感性

SQLite 是不区分大小写的。
但也有一些命令是大小写敏感的,比如 GLOBglob 在 SQLite 的语句中有不同的含义。

1.2.2. 注释

SQLite 注释是附加的注释,可以在 SQLite 代码中添加注释以增加其可读性,他们可以出现在任何空白处,包括在表达式内和其他 SQL 语句的中间,但它们不能嵌套。

SQL 注释以两个连续的 “-“ 字符(ASCII 0x2d)开始,并扩展至下一个换行符(ASCII 0x0a)或直到输入结束,以先到者为准。

您也可以使用 C 风格的注释,以 “/*“ 开始,并扩展至下一个 “/*“ 字符对或直到输入结束,以先到者为准。SQLite的注释可以跨越多行。

1.2.3. SQLite 语言

1.2.3.1. ANALYZE 语句

分析指定数据库的统计信息。

  • 该语句会更新数据库中所有表的统计信息,包括列、索引等,以优化查询执行计划。
  • @param database_name 要分析的数据库的名称。
  • @return 不返回结果集,仅在执行过程中显示进度信息。
ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;
1.2.3.2. ATTACH 语句
ATTACH database_name AS alias_name;
or
ATTACH database_name AS alias_name KEY 'key';
or
ATTACH 'database_name' AS alias_name;
or
ATTACH 'database_name' AS alias_name KEY 'key';
1.2.3.3. BEGIN 语句

这个命令用于在数据库中开始一个新的事务。事务是一系列数据库操作的逻辑单元,

  • 它们要么全部成功执行,要么在发生错误时全部回滚(撤销)。
  • 使用 BEGIN 命令可以确保数据库操作的原子性、一致性和隔离性。
    BEGIN;
    or
    BEGIN DEFERRED;
    or
    BEGIN IMMEDIATE;
    or
    BEGIN EXCLUSIVE;
    or
    BEGIN TRANSACTION;
    or
    BEGIN DEFERRED TRANSACTION;
    or
    BEGIN IMMEDIATE TRANSACTION;
    or
    BEGIN EXCLUSIVE TRANSACTION;
    or
    BEGIN TRANSACTION DEFERRED;
    or
    BEGIN TRANSACTION IMMEDIATE;
    or
    BEGIN TRANSACTION EXCLUSIVE;
    or
    BEGIN TRANSACTION;
1.2.3.4 AND/OR 子句
SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION-1 {AND|OR} CONDITION-2;
1.2.3.5. ALTER TABLE 语句
ALTER TABLE table_name ADD COLUMN column_def...;
or
LTER TABLE table_name RENAME TO new_table_name; # 改名
1.2.3.6. BETWEEN 子句
SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name BETWEEN val-1 AND val-2;
1.2.3.7. BLOB 数据类型

BLOB 是二进制大型对象(Binary Large Object)的缩写,它表示的是一个二进制数据,而不是字符串。
BLOB 数据类型可以用来存储图片、声音、视频等二进制数据。
BLOB 数据类型可以使用 BLOBBLOB(x)BLOB(x,y)BLOB(x,y,z)BLOB(x,y,z,w)BLOB(x,y,z,w,v)BLOB(x,y,z,w,v,u)BLOB(x,y,z,w,v,u,t)BLOB(x,y,z,w,v,u,t,s)

1.2.3.8. COMMIT 语句
COMMIT;
or
COMMIT TRANSACTION;
or
COMMIT TRANSACTION transaction_name;
1.2.3.9. CREATE INDEX 语句
CREATE INDEX index_name ON table_name (column_name);
CREATE INDEX index_name ON table_name (column_name ASC);
CREATE INDEX index_name ON table_name (column_name DESC);
CREATE INDEX index_name ON table_name (column_name ASC, column_name2 DESC);
CREATE INDEX index_name ON table_name (column_name ASC, column_name2 DESC, column_name3 ASC);
1.2.3.10. CREATE UNIQUE INDEX 语句
CREATE UNIQUE INDEX index_name ON table_name (column_name,...);
1.2.3.11. CREATE TABLE 语句
CREATE TABLE table_name (
    column_name column_type, 
    column_name column_type,
    ....,
    PRIMARY KEY (column_name, column_name2, ...),
    );

1.2.3.12. CREATE TRIGGER 语句

/**
 * 创建一个触发器,名为trigger_name,作用于database_name数据库中的table_name表
 * 该触发器在每次插入新行之前触发
 * 
 * @param database_name 数据库名称
 * @param trigger_name 触发器名称
 * @param table_name 触发器作用的表名
 * @param stmt1, stmt2, ... 在触发器触发时执行的SQL语句序列
 * @return 无返回值
 */
CREATE TRIGGER database_name.trigger_name 
    BEFORE INSERT ON table_name FOR EACH ROW
        BEGIN 
            stmt1; -- 执行第一条SQL语句
            stmt2; -- 执行第二条SQL语句
            .... -- 执行更多的SQL语句
        END;
or
CREATE TRIGGER trigger_name trigger_type 
    ON table_name FOR EACH ROW BEGIN
1.2.3.13. CREATE VIEW 语句
CREATE VIEW database_name.view_name AS 
    SELECT column_name, column_name2, ... FROM table_name;
1.2.3.14. CREATE VIRTUAL TABLE 语句
CREATE VIRTUAL TABLE database_name.table_name 
    USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name 
    USING fts3( );
1.2.3.15. COUNT 子句
SELECT COUNT(column_name)
    FROM   table_name
        WHERE  CONDITION;
1.2.3.16. DELETE 语句
DELETE FROM table_name
    WHERE  CONDITION;
or
DELETE FROM table_name
    WHERE  CONDITION
        LIMIT 10;
or
DELETE FROM table_name
    WHERE  CONDITION
        LIMIT 10 OFFSET 10;
1.2.3.17. DETACH DATABASE 语句

用于从当前连接中分离一个数据库。该命令将取消指定数据库的连接,使其成为离线模式,以便可以在另一个会话中以只读方式访问它。注意,该命令不会删除数据库文件,只是断开了当前连接。

DETACH DATABASE 'database_name';
1.2.3.18. DISTINCT 子句

用于从一个或多个表中选择唯一的、不重复的行,返回结果集中唯一的列值
例如,从单个表students中选择不重复的姓名和年龄:

SELECT DISTINCT column_name1, column_name2, ...
FROM   table_name1, table_name2, ...;
1.2.3.19. DROP TABLE 语句
DROP TABLE table_name;
or
DROP TABLE IF EXISTS table_name;
or
DROP TABLE IF EXISTS database_name.table_name;
or
DROP TABLE IF EXISTS database_name.table_name;
1.2.3.20. DROP VIEW 语句
DROP VIEW view_name;
or
DROP VIEW IF EXISTS view_name;
or
DROP VIEW IF EXISTS database_name.view_name;
1.2.3.21. DROP TRIGGER 语句
DROP TRIGGER trigger_name;
or
DROP TRIGGER IF EXISTS trigger_name;
or
DROP TRIGGER IF EXISTS database_name.trigger_name;
1.2.3.22. EXISTS 子句
SELECT column1, column2....columnN
    FROM   table_name
        WHERE  column_name EXISTS (SELECT * FROM   table_name );
1.2.3.23. EXPLAIN 语句
EXPLAIN INSERT statement...;
or
EXPLAIN QUERY PLAN SELECT * FROM   table_name;
or
EXPLAIN QUERY PLAN SELECT * FROM   table_name 
    WHERE  column_name = 'value' AND column_name2 = 'value2';
1.2.3.24. GLOB 子句

该函数适用于在数据库中进行数据查询和过滤操作。
SELECT:指定要查询的列,可以是表中的所有列或部分列。
FROM:指定要查询的表。
WHERE:指定查询条件,只有满足条件的行才会被返回。
GLOB:用于匹配列值的模式匹配运算符,类似于通配符匹配。其中{ PATTERN }是用户指定的匹配模式。

  • { PATTERN }可以是:
    • ‘abc*’:查找以”abc”开头的所有字符串。
    • ‘123?’:查找长度为三个且以”123”开头、第三个字符任意的字符串。
  • 注意:
    • SQLite中的GLOB是大小写敏感的。
    • 不支持正则表达式的高级模式匹配功能,如果需要更复杂的正则匹配,则需使用REGEXP函数(但请注意,SQLite本身不直接支持REGEXP,可能需要加载一个扩展来提供此功能)。
      SELECT column1, column2....columnN
          FROM   table_name
              WHERE  column_name GLOB { PATTERN };
1.2.3.25. GROUP BY 子句

该函数是一个SQL查询语句,用于统计满足特定条件的某列数据的数量。具体功能如下:
SELECT column_name, COUNT(column_name):选择需要展示的列,其中COUNT(column_name)用于计算某列数据的数量。
FROM table_name:指定查询的数据表。
WHERE CONDITION:设定查询条件,只有满足条件的数据才会被统计。
GROUP BY column_name:按照某列对结果进行分组,确保每个唯一值都有一个计数。

整体而言,该函数的作用是对指定数据表中满足特定条件的数据进行计数,并按照某列进行分组展示。

SELECT column_name, COUNT(column_name)
    FROM   table_name
        WHERE  CONDITION
            GROUP BY column_name;

-- 假设有一个名为"Students"的表,其中包含"StudentID"和"CourseID"两列

SELECT CourseID, COUNT(StudentID)
    FROM Students
        WHERE CourseID IS NOT NULL -- 可选条件,若只统计非NULL的StudentID
            GROUP BY CourseID;
/** 上述SQL语句的功能是:
*   从"Students"表中选择每一门课程(CourseID)以及对应课程的学生数量。
*   结果将展示每门课程及其对应的注册学生数。
*   如果WHERE子句未指定,则会计算所有行中"StudentID"列非NULL值的总数。
*   在GROUP BY之后,对于每个不同的CourseID值,
*   COUNT函数都会返回该课程下StudentID不为NULL的数量。
*/
1.2.3.26. HAVING 子句

该函数是SQL查询语句,用于统计满足特定条件的某列数据的数量.

SELECT column_name, COUNT(column_name)
    FROM   table_name
        WHERE  CONDITION
            GROUP BY column_name    
                HAVING COUNT(column_name) > 10;
1.2.3.27. INSERT INTO 语句
INSERT INTO table_name( column1, column2....columnN)
    VALUES ( value1, value2....valueN);
1.2.3.28. IN 语句
SELECT column1, column2....columnN
    FROM   table_name
        WHERE  column_name IN (value1, value2....valueN);
1.2.3.29. LIKE 语句
SELECT column1, column2....columnN
    FROM   table_name
        WHERE  column_name LIKE 'pattern';
1.2.3.30. NOT IN 子句
SELECT column1, column2....columnN
    FROM   table_name
         WHERE  column_name NOT IN (value1, value2....valueN);
1.2.3.31. ORDER BY 子句
SELECT column1, column2....columnN
    FROM   table_name
        WHERE  CONDITION
            ORDER BY column_name1, column_name2....column_nameN  {Asc|Desc};
1.2.3.32. PRAGMA 语句
PRAGMA database_name.table_name.column_name;

For example:
    PRAGMA page_size; -- 获取数据库的页大小
    PRAGMA cache_size = 1024;   -- 设置数据库的缓存大小,决定了SQLite可以存储在内存中的页面数量,以提高数据访问速度。
    PRAGMA table_info(table_name);  -- 获取表的列信息
1.2.3.33. RELEASE SAVEPOINT 语句

用于释放(删除)一个保存点(savepoint)。

  • 该命令只能在事务中使用,用于释放之前在当前事务中设置的保存点。
  • 释放保存点后,不能再回滚到该保存点,但之前已经回滚到该保存点的操作不会被撤销。
    语法:RELEASE SAVEPOINT savepoint_name;
    其中 savepoint_name 是要释放的保存点的名称。
    RELEASE SAVEPOINT savepoint_name;
1.2.3.34. REINDEX 语句

用于重建索引。它可用于修复损坏的索引或优化数据库性能。参数collation_name指定要重建的索引的名称。

使用REINDEX命令可以完成以下操作:

  • 重建指定的索引:REINDEX INDEX collation_name;
  • 重建指定表的所有索引:REINDEX TABLE table_name;
  • 重建数据库中的所有索引(包括系统目录的索引):REINDEX DATABASE database_name;

REINDEX 命令在重建索引过程中会创建一个新的索引,然后将旧索引替换为新索引。在重建索引期间,无法访问被重建索引的表。

需要注意的是,REINDEX命令需要超级用户权限才能执行。

REINDEX table_name;
REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;
1.2.3.35. ROLLBACK 语句
ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;
1.2.3.36. SAVEPOINT 语句
SAVEPOINT savepoint_name;
1.2.3.37. SELECT 语句
SELECT column1, column2....columnN
    FROM   table_name
1.2.3.38. UPDATE 语句
UPDATE table_name
    SET column1 = value1, column2 = value2....columnN = valueN
        [WHERE  CONDITION ];
1.2.3.39. VACUUM 语句

用于回收数据库表中的空间,删除死行并重新组织表的数据。
它分为 VACUUM FULLVACUUM ANALYZE两种形式,
分别代表 完全清理更新统计信息
VACUUM函数可以优化数据库性能,但需要在低负载时期运行,因为它会锁定表

VACUUM;
VACUUM table_name;
VACUUM database_name;
VACUUM database_name.table_name;
VACUUM database_name.index_name;
VACUUM database_name.table_name.index_name;