数据库查询语言：数据库查询语言：SQLite：创建与管理：创建与管理SQLite
数据库数据库
数据库查询语言：数据库查询语言：SQLite：创建与管理：创建与管理SQLite
数据库数据库
1. 介绍介绍SQLite基础基础
1.1 SQLite概述概述
SQLite是一个开源的嵌入式关系型数据库引擎，它以C语言编写，不需要单独的服务器进程，而
是直接在应用程序中运行。SQLite的设计目标是简单、高效、可靠，适用于各种环境，从移动设
备到大型服务器。它支持SQL查询语言，提供了事务处理、用户自定义函数、触发器等高级功
能。
1.2 安装与配置安装与配置SQLite环境环境
在在Windows上安装上安装SQLite
1. 访问SQLite官方网站下载最新版本的SQLite预编译二进制文件。
2. 下载完成后，将sqlite3.exe文件放置在你的项目目录中，或者将其添加到系统路径
中，以便在任何位置运行。
在在Linux上安装上安装SQLite
在大多数Linux发行版中，SQLite可以通过包管理器轻松安装：
sudo apt-get install sqlite3
或在Fedora和CentOS上：
sudo yum install sqlite
在在MacOS上安装上安装SQLite
使用Homebrew安装SQLite：
brew install sqlite
1.3 理解理解SQLite数据类型数据类型
SQLite支持五种标准数据类型：
• NULL：值为NULL。
• INTEGER：整数，根据值的大小自动选择最合适的存储类型。
• REAL：浮点数，存储为8字节的IEEE浮点数。
• TEXT：文本字符串，存储为UTF-8、UTF-16或UTF-16le编码。
• BLOB：二进制大对象，存储为原样。
此外，SQLite还支持类型亲和性，即列可以被声明为特定的类型，但实际存储的数据类型可以更
灵活。
示例：创建一个包含多种数据类型的表示例：创建一个包含多种数据类型的表
--
创建一个包含多种数据类型的表
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
salary REAL,
photo BLOB
);
在这个例子中，我们创建了一个名为employees的表，其中包含以下列： - id：整数类型，作
为主键。 - name：文本类型，不能为空。 - age：整数类型。 - salary：浮点数类型。 -
photo：二进制大对象类型，用于存储图像数据。
插入数据插入数据
```sql – 插入数据到employees表 INSERT INTO employees (id, name, age, salary, photo) VALUES (1,
‘张三’, 30, 5000.0,
x’FFD8FFE000104A46494600010100000100010000FFDB0043000505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050505050
创建与操作数据库创建与操作数据库
2. 创建创建SQLite数据库数据库
SQLite是一个轻量级的数据库，它不需要一个单独的服务器进程，而是作为一个库嵌入到应用程
序中。创建一个SQLite数据库非常简单，只需要使用CREATE DATABASE语句的替代形式，即连
接到一个数据库文件，如果该文件不存在，SQLite会自动创建它。
2.1 示例代码示例代码
--
创建或连接到名为
example.db
的
SQLite
数据库
ATTACH DATABASE 'example.db' AS example;
在Python中使用SQLite，可以使用sqlite3模块，代码如下：
import sqlite3
#
连接到
SQLite
数据库，如果不存在则创建
conn = sqlite3.connect('example.db')
#
创建一个游标对象
cursor = conn.cursor()
#
使用游标执行
SQL
语句
cursor.execute('CREATE TABLE IF NOT EXISTS users
(id INTEGER PRIMARY KEY, name TEXT, email TEXT)')
#
提交事务
conn.commit()
#
关闭连接
conn.close()
2.2 解释解释
在上述代码中，我们首先导入了sqlite3模块，然后使用connect函数连接到example.db数
据库。如果数据库文件不存在，connect函数会自动创建它。接下来，我们创建了一个游标对
象，使用游标执行SQL语句来创建一个名为users的表，该表包含id、name和email三个字
段。CREATE TABLE IF NOT EXISTS语句确保只有在表不存在时才创建它，避免了重复创建
的错误。最后，我们提交了事务并关闭了数据库连接。
3. 表的创建与管理表的创建与管理
在SQLite中，表是数据的主要存储单元。创建表时，需要定义表的结构，包括字段名和数据类
型。管理表包括修改表结构、添加或删除字段、重命名表等操作。
3.1 创建表创建表
--
创建一个名为
books
的表，包含
id
、
title
和
author
字段
CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author TEXT
);
3.2 修改表结构修改表结构
添加字段添加字段
--
向
books
表添加一个新字段：
year
ALTER TABLE books ADD COLUMN year INTEGER;
删除字段删除字段
--
删除
books
表中的
year
字段
--
注意：
SQLite
不直接支持删除字段，需要创建一个新表，然后将数据迁移过去
CREATE TABLE books_new (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author TEXT
);
INSERT INTO books_new (id, title, author) SELECT id, title, author
FROM books;
DROP TABLE books;
ALTER TABLE books_new RENAME TO books;
重命名表重命名表
--
将
books
表重命名为
library
ALTER TABLE books RENAME TO library;
4. 数据的插入与查询数据的插入与查询
4.1 插入数据插入数据
--
向
books
表插入一条数据
INSERT INTO books (title, author, year) VALUES ('The Great Gatsby',
'F. Scott Fitzgerald', 1925);
在Python中插入数据：
#
插入数据
cursor.execute('INSERT INTO books (title, author, year)
VALUES (?, ?, ?)', ('The Great Gatsby', 'F. Scott Fitzgerald',
1925))
conn.commit()
4.2 查询数据查询数据
--
查询
books
表中所有数据
SELECT * FROM books;
--
查询
books
表中作者为
F. Scott Fitzgerald
的书籍
SELECT * FROM books WHERE author = 'F. Scott Fitzgerald';
在Python中查询数据：
#
查询所有数据
cursor.execute('SELECT * FROM books')
rows = cursor.fetchall()
for row in rows:
print(row)
#
查询特定条件的数据
cursor.execute('SELECT * FROM books WHERE author =
?', ('F. Scott Fitzgerald',))
rows = cursor.fetchall()
for row in rows:
print(row)
4.3 更新数据更新数据
--
更新
books
表中
id
为
1
的书籍的
year
字段
UPDATE books SET year = 1926 WHERE id = 1;
在Python中更新数据：
#
更新数据
cursor.execute('UPDATE books SET year = ? WHERE id = ?',
(1926, 1))
conn.commit()
4.4 删除数据删除数据
--
删除
books
表中
id
为
1
的书籍
DELETE FROM books WHERE id = 1;
在Python中删除数据：
#
删除数据
cursor.execute('DELETE FROM books WHERE id = ?', (1,))
conn.commit()
通过以上示例，我们可以看到如何在SQLite中创建数据库、表，以及如何插入、查询、更新和删
除数据。SQLite的简单性和易用性使其成为许多应用程序和系统中首选的数据库解决方案。
