Sqlite基础语法
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 是不区分大小写的。
但也有一些命令是大小写敏感的,比如 GLOB 和 glob 在 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 数据类型可以使用 BLOB、BLOB(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 FULL和VACUUM 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;