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


SQL基本操作语句
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数据库来管理数据。
相关推荐





