SQLite操作语句详解：CRUD操作的基本语法

# 1. 简介

## 1.1 什么是SQLite

SQLite是一种轻量级的嵌入式关系型数据库管理系统，它可以在各种操作系统上运行，并且不需要服务器进程。它被广泛用作移动设备、嵌入式系统和小型应用程序的数据库存储解决方案。

## 1.2 SQLite的优势和应用领域

SQLite具有以下优势：
- 零配置：无需服务器或者配置文件。
- 事务性：完整支持事务属性，允许多个并发用户访问同一个数据库而不会影响用户数据完整性。
- 高效性：SQLite的读取速度非常快，特别适用于嵌入式设备。
- 跨平台：可以在Windows、MacOS、Linux等多个平台上运行。

SQLite的应用领域包括但不限于：
- 移动应用程序的本地存储。
- 小型服务器。
- 替代文件格式的本地存储解决方案。

接下来我们将介绍如何使用SQLite进行数据库和表的创建。

# 2. 创建数据库和表

### 2.1 创建数据库

SQLite使用`.open`命令来打开一个数据库文件，如果文件不存在会自动创建该文件。在SQLite中，每个数据库是一个文件，因此可以通过文件系统来管理数据库文件。

.open database_name.db

### 2.2 创建表

要在SQLite中创建表，可以使用`CREATE TABLE`语句，并指定表名以及各个列的名称和数据类型。

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

### 2.3 数据类型

SQLite支持以下几种数据类型：

- `INTEGER`：整数类型
- `REAL`：浮点类型
- `TEXT`：文本类型
- `BLOB`：二进制类型

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    salary REAL
);

在这个例子中，我们创建了一个名为`employees`的表，其中包含ID（整数类型）、姓名（文本类型）、年龄（整数类型）和工资（浮点类型）列。

通过以上示例，我们学习了如何在SQLite中创建数据库和表，以及SQLite所支持的数据类型。接下来，我们将深入学习如何插入数据到表中。

# 3. 插入数据

在SQLite中，可以使用INSERT语句向表中插入新的数据。本章将介绍如何插入单行数据、插入多行数据以及使用INSERT INTO SELECT语句插入数据。

#### 3.1 插入单行数据

插入单行数据可以使用INSERT INTO语句，指定表名和要插入的数据。下面是一个示例：

import sqlite3

# 连接到数据库
conn = sqlite3.connect('test.db')

# 创建游标对象
cursor = conn.cursor()

# 定义要执行的SQL语句
sql = "INSERT INTO student (id, name, age, grade) VALUES (1, 'Tom', 18, 'A')"

# 执行SQL语句
cursor.execute(sql)

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

上述代码通过插入一行数据到名为student的表中。需要注意的是，表名和字段名需要与数据库中的实际表和字段对应，插入的数据需要与表的字段类型相匹配。

#### 3.2 插入多行数据

如果需要插入多行数据，可以使用executemany()方法。下面是一个示例：

import sqlite3

# 连接到数据库
conn = sqlite3.connect('test.db')

# 创建游标对象
cursor = conn.cursor()

# 定义要执行的SQL语句
sql = "INSERT INTO student (id, name, age, grade) VALUES (?, ?, ?, ?)"

# 定义要插入的数据
data = [
    (2, 'Alice', 17, 'B'),
    (3, 'Bob', 19, 'A'),
    (4, 'Charlie', 20, 'B')
]

# 执行SQL语句
cursor.executemany(sql, data)

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

上述代码使用executemany()方法将多行数据插入到表中。需要注意的是，占位符`?`用来替代要插入的具体值，然后通过执行executemany()方法和传入数据的列表来一次性插入多行数据。

#### 3.3 使用INSERT INTO SELECT语句插入数据

除了上述的插入方式，还可以使用INSERT INTO SELECT语句来插入数据。该语句可以将查询结果直接插入到目标表中。下面是一个示例：

import sqlite3

# 连接到数据库
conn = sqlite3.connect('test.db')

# 创建游标对象
cursor = conn.cursor()

# 定义要执行的SQL语句
sql = "INSERT INTO student_new (id, name, age, grade) SELECT id, name, age, grade FROM student WHERE grade = 'A'"

# 执行SQL语句
cursor.execute(sql)

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

上述代码将student表中grade为'A'的记录插入到student_new表中。可以根据实际需求编写不同的SELECT语句来获取要插入的数据。

# 4. 查询数据

在SQLite中，查询数据是非常常见和重要的操作。我们可以使用SELECT语句来获取数据库中的数据。下面将介绍SELECT语句的基本使用方法和常见的查询操作。

#### 4.1 SELECT基本语句

SELECT语句用于从数据库中检索数据。其基本语法如下：

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

其中，列名是要查询的字段名称，可以是单个字段，也可以是多个字段。使用星号（*）代表查询所有字段。

例如，我们有一个名为`users`的表，其中包含了`id`、`name`和`age`等字段，我们可以使用以下语句查询所有用户的姓名和年龄：

SELECT name, age FROM users;

#### 4.2 条件查询

除了查询所有数据，我们还经常需要根据某些条件查询特定的数据。在SELECT语句中，我们可以使用WHERE子句来指定查询条件。

WHERE子句的语法如下：

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

其中，条件是一个逻辑表达式，可以使用比较运算符（如等于、大于、小于）、逻辑运算符（如AND、OR）和通配符（如%、_）等进行条件筛选。

例如，我们可以使用以下语句查询年龄大于等于18岁的用户：

SELECT name, age FROM users WHERE age >= 18;

#### 4.3 使用ORDER BY排序数据

在查询数据时，我们通常希望按照某个字段的值进行排序，以便更好地查看和分析数据。在SQLite中，我们可以使用ORDER BY子句来对结果进行排序。

ORDER BY子句的使用方法如下：

SELECT 列名1, 列名2, ... FROM 表名 ORDER BY 排序字段 [ASC|DESC];

其中，排序字段是要排序的字段名，ASC代表升序（默认），DESC代表降序。

例如，我们可以使用以下语句按照年龄从小到大的顺序查询用户信息：

SELECT name, age FROM users ORDER BY age ASC;

#### 4.4 使用LIMIT限制结果集

在查询数据时，有时我们只需要获取部分数据，而不是全部结果。在SQLite中，我们可以使用LIMIT子句来限制结果集的数量。

LIMIT子句的使用方法如下：

SELECT 列名1, 列名2, ... FROM 表名 LIMIT 数量 OFFSET 偏移量;

其中，数量表示要获取的记录数量，偏移量表示跳过的记录数量（可选）。

例如，我们可以使用以下语句查询前5个用户的信息：

SELECT name, age FROM users LIMIT 5;

以上就是查询数据的基本用法和常见操作。通过灵活运用SELECT语句和相关子句，我们可以根据需要来获取数据库中的数据。

# 5. 更新数据

在SQLite中，更新数据使用`UPDATE`语句，可以更新现有的记录。

#### 5.1 使用UPDATE语句更新单行数据

下面是一个示例，假设我们有一个名称为`students`的表格，其中包含`id`、`name`和`age`字段。现在我们要更新id为1的学生的姓名：

UPDATE students
SET name = 'NewName'
WHERE id = 1;

**注释：** 这个SQL语句将会把`students`表中`id`为1的学生的姓名改为`NewName`。

#### 5.2 使用UPDATE语句批量更新数据

如果我们需要批量更新数据，也可以使用`UPDATE`语句。以下是一个示例，假设我们要将所有学生的年龄增加2岁：

UPDATE students
SET age = age + 2;

**注释：** 这个SQL语句将会把`students`表中所有学生的年龄都增加2岁。

#### 5.3 使用UPDATE语句更新指定条件的数据

有时候我们只想更新符合特定条件的数据，下面是一个例子，假设我们要把所有名字为`Tom`的学生年龄都修改为25岁：

UPDATE students
SET age = 25
WHERE name = 'Tom';

**注释：** 这个SQL语句将会把`students`表中所有名字为`Tom`的学生的年龄都修改为25岁。

在本章中，我们学习了如何使用`UPDATE`语句来更新SQLite中的数据。

# 6. 删除数据

在SQLite中，我们可以使用DELETE语句来删除数据。删除操作可以删除单行数据，也可以批量删除数据，还可以根据条件删除指定的数据。

#### 6.1 使用DELETE语句删除单行数据

要删除单行数据，我们可以使用DELETE语句结合WHERE子句来限定删除的条件。下面是一个示例代码，演示了如何删除表中的一行数据：

import sqlite3

# 连接到数据库
conn = sqlite3.connect('example.db')

# 创建游标对象
cursor = conn.cursor()

# 删除一行数据
cursor.execute("DELETE FROM students WHERE id = 1")

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

以上代码中，我们首先通过`connect()`方法连接到数据库，然后创建一个游标对象。接着，我们使用`execute()`方法执行DELETE语句，指定要删除的条件。在本例中，我们删除了表中`id`为1的行数据。最后，通过`commit()`方法提交事务，将修改保存到数据库中。最后，关闭游标和连接。

#### 6.2 使用DELETE语句批量删除数据

如果我们需要批量删除数据，可以使用DELETE语句结合IN子句来指定多个删除条件。下面是一个示例代码，演示了如何删除表中的多行数据：

import sqlite3

# 连接到数据库
conn = sqlite3.connect('example.db')

# 创建游标对象
cursor = conn.cursor()

# 删除多行数据
ids = [2, 3, 4]
cursor.execute("DELETE FROM students WHERE id IN (?)", (",".join(map(str, ids)),))

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

在以上代码中，我们首先定义了一个包含要删除行的id的列表`ids`。然后，我们使用`JOIN()`方法将`ids`列表转换为字符串形式，并将其作为参数传递给DELETE语句的IN子句。通过在执行DELETE语句之前，将参数传递给`execute()`方法作为元组的方式，我们可以确保参数值被正确转义和引用。最后，我们提交事务并关闭游标和连接。

#### 6.3 使用DELETE语句删除指定条件的数据

如果需要删除满足特定条件的数据，可以在DELETE语句的WHERE子句中指定条件。下面是一个示例代码，演示了如何删除表中满足特定条件的数据：

import sqlite3

# 连接到数据库
conn = sqlite3.connect('example.db')

# 创建游标对象
cursor = conn.cursor()

# 删除满足特定条件的数据
cursor.execute("DELETE FROM students WHERE GPA < 3.0")

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

在以上代码中，我们使用DELETE语句的WHERE子句指定了满足`GPA < 3.0`条件的数据将被删除。执行DELETE语句后，我们通过`commit()`方法提交事务，并通过关闭游标和连接来完成删除操作。

在本章中，我们学习了如何使用DELETE语句删除数据。我们可以通过指定单个删除条件、多个删除条件或满足特定条件来删除数据。删除数据是维护数据库中数据的重要操作，在实际应用中经常会遇到。通过掌握删除数据的操作，我们可以更好地利用SQLite数据库来管理数据。