sqlite3 学习笔记

前言

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统。

SQLite 非常适合用于嵌入式系统、物联网设备、移动应用程序和客户端应用程序，它不需要服务器，简单易用。

SQL的概念

SQLite3是一个数据库，而SQL是一种编程语言。我们需要使用SQL来操作SQLite3数据库。

SQL语言包括四种主要程序设计语言类别的语句：数据定义语言(DDL)，数据查询语言(DQL)，数据操作语言(DML)，数据控制语言(DCL)。

SQL语言中完成核心功能的9个动词：

SQL功能	动词
数据定义DD	Create、Drop（删除）、Alter（修改）
数据查询DQ	Select
数据操纵DM	Insert（插入）、Update（更新）、Delete（删除）
数据控制DC	Grant（授权）、Revoke（撤销授权）

SQLite3 是一个 轻量级的嵌入式数据库系统，它仅实现了 SQL 标准的一个子集。即SQLite3中只能使用部分SQL语句，而且不支持数据控制语句(DCL)。

• SQLITE3支持的 DML关键字有
  • CREATE:创建一个新的表，一个表的视图，或者数据库中的其他对象
  • ALTER:修改数据库中的某个已有的数据库对象，比如一个表
  • DROP：删除整个表，或者表的视图，或者数据库中的其他对象
• SQLITE3支持的 DDL关键字有
  • INSERT:创建一条记录
  • UPDATE:修改记录
  • DELETE:删除记录
• SQLITE3支持的 DQL关键字有
  • SELECT:从一个或多个表中检索某些记录

与表格相关的操作

i.创建表格（增）

基本语法

CREATE TABLE IF NOT EXISTS table_name (column1_name data_type [constraints],column2_name data_type [constraints],...columnN_name data_type [constraints]
);例子
-- 创建一个名为 "users" 的表格
CREATE TABLE IF NOT EXISTS users (user_id INT PRIMARY KEY,username TEXT NOT NULL,email TEXT UNIQUE,password TEXT NOT NULL
);

1. CREATE TABLE: 创建表格的关键字。
2. IF NOT EXISTS: 可以避免出现添加已存在数据表的SQL错误信息
3. table_name: 要创建的表格的名称。
4. column_name: 列的名称。
5. data_type: 数据类型，例如 INT, TEXT, REAL, BLOB 等。
6. constraints: 约束，例如 PRIMARY KEY, UNIQUE, NOT NULL, FOREIGN KEY 等。

列（字段）相关的配置

每个列（字段）都有对应的配置，如列名称，列数据类型，类约束，列默认值等。列配置的具体内容如下：

1. 列的数据类型：

   • INT: 整数。
   • TEXT: 文本字符串。
   • REAL: 浮点数。
   • BLOB: 二进制数据。专门用于存放图像、视频动画和其他类型的文件等。
   • DATE: 日期。
   • DATETIME: 日期和时间。

2. 列的默认值
   DEFAULT 关键字用于为表格中的列设置默认值。当插入新记录时（注意：这不是创建表时存在的默认值），如果该列的值没有被明确指定，则会自动使用默认值。
   CREATE TABLE table_name ( column_name data_type DEFAULT default_value, ... );
DEFAULT: 设置默认值的关键字。
   default_value: 默认值，可以是常量、表达式、函数等

3. 列（字段）的约束：

   • PRIMARY KEY: 主键，用于唯一标识每条记录。
     在数据表中能够唯一识别记录的字段，都会被人们设置为主键，如“学号”字段。
     当某个字段被设置为主键后，该字段中就不能再有重复值，也不能有空值，数据库管理系统将强制执行这一规则，这就是主键约束。
   • FOREIGN KEY: 外键，用于建立两个表格之间的关系。

4. 列的索引（略读）

   • 索引 (INDEX)：定义索引，用于提高查询效率。除表之外，索引是大型数据库系统中最重要的对象之一！索引是一种树形结构，如果使用正确的话，可以减少定位和查询数据所需的IO操作。

   • 索引的用途
     索引类似于书籍的目录，它让数据库系统不必扫描整个表就能快速定位到需要的数据行。 如果一个查询的 WHERE 子句中包含了索引列，数据库系统可以快速地找到匹配的行，从而提高查询速度。 索引的创建需要消耗磁盘空间和一些额外的维护成本，但是对查询速度的提升通常是值得的。

   • 如何创建索引
     使用 CREATE INDEX 语句来创建索引。 以下是 SQLite 中创建索引的语法：
     CREATE INDEX 索引名 ON 表名 (列名1, 列名2, ...);
索引名: 为索引取一个名称。
     表名: 要为其创建索引的表。
     列名1, 列名2, ...: 要索引的列。 多个列可以同时索引（复合索引），提高多条件查询的效率。

     • 示例：
       假设有一个名为 products 的表，包含 id、name 和 price 列。 你想在 name 列上创建索引：
       CREATE INDEX idx_product_name ON products (name);
       或者，你想在 name 和 price 列上创建复合索引：
       CREATE INDEX idx_product_name_price ON products (name, price);

ii 删除表格（删）

使用drop删除表，会将表彻底的删除掉，包括表结构和表数据。

DROP TABLE table_name;例子
-- 删除一个名为 "users" 的表格
DROP TABLE users;

用户若只是想删除表的数据
可以通过 删除记录的SQL语句 DELETE FROM 表名; 来实现相同的相关

iii 更改表格（改）

SQLite只支持部分的SQL更改表格的语句。支持的操作有：

• 修改表名
• 添加列

SQLite 不直接支持的修改表结构的操作有：

• 删除列： SQLite 不直接支持删除列。 要删除列，你需要创建一个新的表，只包含你想要保留的列，然后将数据从旧表复制到新表，最后删除旧表，并将新表改名为旧表的名称。
• 修改主键: SQLite 不直接支持修改主键。 通常需要创建新的表，添加新的主键，并复制数据。

---

修改表名：（SQLite支持）

ALTER TABLE old_table_nameRENAME TO new_table_name;例子：
-- 名为 "users_new " 的表格，修改名字为“users”
ALTER TABLE users_new RENAME TO users;

• old_table_name：要修改的表格的旧名称。
• new_table_name：要修改的表格的新名称。

---

添加列：（SQLite支持）

ALTER TABLE table_name ADD column_name data_type [column_attributes];例子：
-- 名为 "users" 的表格，增加一列，列名称为job ，列数据类型为TEXT，约束为NOT NULL，默认值为 default_value
ALTER TABLE users ADD job TEXT NOT NULL DEFAULT default_value;

• table_name：要修改的表格名。
• column_name：要添加的列名。
• data_type：新列的数据类型。
• column_attributes：可选的列属性，例如 NOT NULL、DEFAULT 等。

---

删除列：（SQLite间接支持）

SQLite虽然不可以直接通过DROP来删除列，但是却可通过间接的方法实现，如下。

假设你有一个名为 users 的表，包含 id (INTEGER) 和 name (TEXT) 列。

ALTER TABLE users ADD job TEXT NOT NULL DEFAULT default_value;

删除 job 列 (间接方法):

-- 创建一个新的表，不包含 email 列
CREATE TABLE users_new (id INTEGER, name TEXT);-- 将数据从旧表复制到新表
INSERT INTO users_new SELECT id, name FROM users;-- 删除旧表
DROP TABLE users;-- 将新表改名为旧表的名称
ALTER TABLE users_new RENAME TO users;

iv 查询表格（查）

在linux系统中，使用sqlite3命令进入数据库后，可以通过.schema来查看所有的表格信息

sqlite>.schema 
CREATE TABLE IF NOT EXISTS users (user_id INT PRIMARY KEY,username TEXT NOT NULL,email TEXT UNIQUE,password TEXT NOT NULL
);

您可以使用 SQLIte 命令中的 .tables 命令来验证表是否已成功创建，该命令用于列出附加数据库中的所有表。

sqlite>.tables
users 

与记录相关的操作

i 插入记录

在 SQLite 数据库中，用户可以使用 INSERT 语句来添加新的记录到表中。 INSERT 语句有多种形式，取决于你如何指定要插入的值。

1. 指定所有列的值:

这是最常用的方法，你需要指定表中的每一列的值，并按照列的顺序排列。

INSERT INTO 表名 (列名1, 列名2, 列名3, ...) VALUES (值1, 值2, 值3, ...);例子
假设你有一个名为 `users` 的表，包含 `id` (INTEGER), `name` (TEXT), `age` (INTEGER) 列。 要添加一个新的用户记录：
INSERT INTO users (id, name, age) VALUES (1, 'John Doe', 30);

• 表名: 要插入记录的表名。
• 列名1, 列名2, 列名3, …: 表中的列名，必须与 VALUES 子句中的值一一对应。
• 值1, 值2, 值3, …: 要插入的值，数据类型必须与相应的列匹配。

2. 省略列名 (自动推断):

如果你要为表中的所有列都赋值，你可以省略列名，只需要提供值即可。 但是，值的顺序必须与表中列的顺序一致。

INSERT INTO 表名 VALUES (值1, 值2, 值3, ...);示例 (延续上例):
INSERT INTO users VALUES (2, 'Jane Doe', 25);

3. 使用默认值:

如果你想使用列的默认值（如果列定义了默认值），你可以省略该列的值。 但是，你必须至少提供一个列的值。

示例 (假设 age 列有默认值 20):

INSERT INTO users (id, name) VALUES (3, 'Peter Pan');

4. 插入多行记录:

你可以使用单一 INSERT 语句插入多行记录：

INSERT INTO 表名 (列名1, 列名2, ...)
VALUES (值1, 值2, ...),(值3, 值4, ...),(值5, 值6, ...);

省略列名也可以。

INSERT INTO 表名
VALUES (值1, 值2, ...),(值3, 值4, ...),(值5, 值6, ...);

示例：

INSERT INTO users (id, name, age)
VALUES (4, 'Alice', 28),(5, 'Bob', 35);

注意事项：

• 数据类型匹配:
  确保你提供的值与表中列的数据类型匹配。 否则，插入操作可能会失败。
• 主键冲突:
  如果你有主键约束，确保插入的主键值是唯一的。 否则，插入操作会失败，并返回错误。
• 自动递增:
  如果你的表中有一列定义为 AUTOINCREMENT，则该列的值会自动生成。 你不需要在 INSERT 语句中提供该列的值。
• 事务处理:
  如果你要插入多行记录，或者需要将插入操作与其他数据库操作组合在一起，建议使用事务处理 (BEGIN TRANSACTION, COMMIT, ROLLBACK) 来确保数据的一致性。

ii 删除记录

在 SQLite 数据库中，用户可以使用 DELETE 语句来删除表中的记录。 DELETE 语句的基本语法如下：

DELETE FROM 表名
WHERE 条件;

• DELETE FROM 表名: 指定要删除记录的表名。

• WHERE 条件: 指定删除哪些记录。 这部分是必须的，否则会删除表中的所有记录！ 条件可以使用各种比较运算符（=, !=, >, <, >=, <=），AND 和 OR 运算符组合条件，以及其他 SQL 函数，如LIKE，BETWEEN xx AND xx。
  具体的运用请看示例。

示例：

假设你有一个名为 users 的表，包含 id、name 和 email 列。

• 删除 id 为 1 的记录:

DELETE FROM users
WHERE id = 1;

• 删除 name 为 ‘John Doe’ 的记录:

DELETE FROM users
WHERE name = 'John Doe';

• 删除 email 包含 ‘@example.com’ 的记录:

DELETE FROM users
WHERE email LIKE '%@example.com%';

• 删除 id 在 1 到 5 之间的记录（注意，这是闭区间，包含了id=1和id=5的记录）:

DELETE FROM users
WHERE id BETWEEN 1 AND 5;

• 删除多条件: 删除 id 为 1 并且 name 为 ‘Alice’ 的记录:

DELETE FROM users
WHERE id = 1 AND name = 'Alice';

• 删除全部记录

DELETE FROM users;

重要提示：

• 谨慎使用 DELETE 语句！ 没有 WHERE 子句的 DELETE 语句会删除表中的所有记录。 在执行 DELETE 语句之前，务必仔细检查你的 WHERE 子句，确保它只删除你想要删除的记录。

• 备份数据库: 在执行重要的 DELETE 操作之前，最好先备份你的数据库，以防万一发生错误。

• 事务处理: 对于多个 DELETE 操作或与其他操作一起进行 DELETE 操作，建议使用事务处理 (BEGIN TRANSACTION, COMMIT, ROLLBACK) 来确保数据的一致性。 例如：

BEGIN TRANSACTION;
DELETE FROM users WHERE id = 1;
DELETE FROM orders WHERE user_id = 1;
COMMIT;  -- or ROLLBACK;

iii 查询记录

在 SQLite 数据库中，用户可以使用 SELECT 语句来查询表中的记录。 SELECT 语句有很多种形式，可以满足各种查询需求。

1. 查询所有列的所有记录:

这是最简单的查询方式，它会返回表中所有列的所有记录。

SELECT * FROM 表名;

例如，如果你的表名为 users，则查询语句为：

SELECT * FROM users;

2. 查询指定的列:

你可以选择只查询表中的某些列，而不是所有列。

SELECT 列名1, 列名2, ... FROM 表名;

例如，要查询 users 表中的 name 和 email 列：

SELECT name, email FROM users;

3. 使用 WHERE 子句过滤记录:

你可以使用 WHERE 子句来过滤记录，只返回满足特定条件的记录。

SELECT * FROM 表名 WHERE 条件;

例如，要查询 users 表中 id 为 1 的记录：

SELECT * FROM users WHERE id = 1;

用户可以使用各种比较运算符（=, !=, >, <, >=, <=），AND 和 OR 运算符组合条件，以及 LIKE 运算符进行模式匹配。
查询 age 大于 25 的用户：SELECT * FROM users WHERE age > 25;
查询 name 包含 “John” 的用户：SELECT * FROM users WHERE name LIKE '%John%';
查询 age 大于 25 且 email 包含 “@example.com” 的用户：SELECT * FROM users WHERE age > 25 AND email LIKE '%@example.com%';

4. 使用 ORDER BY 子句排序记录:

用户可以使用 ORDER BY 子句来对查询结果进行排序。

SELECT * FROM 表名 ORDER BY 列名 [ASC|DESC];

ASC 表示升序排序（默认），DESC 表示降序排序。

例如，要按 age 升序排序查询结果：

SELECT * FROM users ORDER BY age ASC;

要按 name 降序排序：

SELECT * FROM users ORDER BY name DESC;

5. 使用 LIMIT 子句限制返回的记录数:
   你可以使用 LIMIT 子句来限制返回的记录数。

SELECT * FROM 表名 LIMIT n;

n 表示要返回的记录数。

例如，要只返回前 10 条记录：

SELECT * FROM users LIMIT 10;

你还可以指定起始位置和返回的记录数：

SELECT * FROM 表名 LIMIT m, n;

m 表示起始位置（从 0 开始），n 表示返回的记录数。 例如，返回从第 5 条记录开始的 10 条记录：

SELECT * FROM users LIMIT 4, 10;

6. 使用 OFFSET 子句 (与LIMIT配合使用):
   OFFSET 用于跳过指定数量的行数，之后才开始返回结果，常与 LIMIT 子句一起使用。

SELECT * FROM 表名 LIMIT n OFFSET m;

这些是 SQLite 中 SELECT 语句的一些基本用法，你可以根据实际需要组合使用这些功能来实现更复杂的查询。 记住在编写复杂查询时，使用括号来明确操作的优先级。

7. 使用DISTINCT去除重复的信息
   DISTINCT 关键字用于在 SQL 查询结果中去除重复的行。 它只返回唯一的值。 DISTINCT 放在要返回的列的列表的前面。

SELECT DISTINCT 列名1, 列名2, ...
FROM 表名
[WHERE 条件];

• DISTINCT: 关键字，表示只返回唯一的值。
• 列名1, 列名2, ...: 要选择的列。 如果省略，则表示对所有列进行去重。
• FROM 表名: 指定要查询的表。
• WHERE 条件 (可选): 用于过滤记录，在去除重复行之前进行筛选。

前面介绍了SELECT语句最基本的语法格式,实际上SELECT语句的完整语法格式要比其复杂得多。

下面将经常用到的带有主要子句的语法格式归纳如下

SELECT 	[DISTINCTIALL] 	select list
FROM					table source
[WHERE					search condition]
[GROUPBY				group_by_expression]
[HAVING					searching_condition]
[ORDER BY				order_expression[ASC|DESC]]

假设SELECT语句带有所有的子句，则其执行顺序如下：
（1）FROM 子句。
（2）WHERE 子句。
（3）GROUP BY 子句。
（4）HAVING 子句。
（5）SELECT 子句。
（6）ORDER BY 子句

iv 修改记录

在 SQLite 数据库中，用户可以使用 UPDATE 语句来修改表中的记录。 UPDATE 语句的基本语法如下：

UPDATE 表名
SET 列名1 = 新值1, 列名2 = 新值2, ...
WHERE 条件;

• UPDATE 表名: 指定要修改记录所在的表名。
• SET 列名1 = 新值1, 列名2 = 新值2, ...: 指定要修改的列以及它们的新值。 可以用逗号分隔多个列和值。
• WHERE 条件: 指定要修改哪些记录。 这部分是必须的，否则会更新表中的所有记录！ 条件可以使用各种比较运算符（=, !=, >, <, >=, <=），AND 和 OR 运算符组合条件，以及其他 SQL 函数。

示例：

假设你有一个名为 users 的表，包含 id、name、email 和 age 列。

1. 修改 id 为 1 的用户的 name 和 age:

UPDATE users
SET name = 'John Doe Updated', age = 35
WHERE id = 1;

2. 修改 email 包含 ‘@example.com’ 的用户的 age:

UPDATE users
SET age = 40
WHERE email LIKE '%@example.com%';

修改多个列，多条件更新: 修改 id 为 2 并且 name 为 ‘Jane Doe’ 的用户的 email 和 age：

UPDATE users
SET email = 'jane.doe.updated@example.com', age = 30
WHERE id = 2 AND name = 'Jane Doe';

注意点：

• 谨慎使用 UPDATE 语句！ 没有 WHERE 子句的 UPDATE 语句会更新表中的所有记录。 在执行 UPDATE 语句之前，务必仔细检查你的 WHERE 子句，确保它只更新你想要更新的记录。

• 备份数据库: 在执行重要的 UPDATE 操作之前，最好先备份你的数据库，以防万一发生错误。

• 事务处理: 对于多个 UPDATE 操作或与其他操作一起进行 UPDATE 操作，建议使用事务处理 (BEGIN TRANSACTION, COMMIT, ROLLBACK) 来确保数据的一致性。

• 数据类型: 确保新值的数据类型与要更新的列的数据类型匹配。 如果不匹配，更新操作可能会失败或导致数据错误。

Linux中使用sqlite3

待续

参考内容：
菜鸟教程-sqlite3