SQLite数据库创建表实例教程从基础语法到实际应用快速掌握SQLite表设计技巧与最佳实践助你轻松入门数据库开发提高工作效率

威震华夏关云长 · 发表于 2025-9-13 16:10:00

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？立即注册

x

1. SQLite简介

SQLite是一种轻量级的、基于文件的 relational database management system (RDBMS)，它不需要独立的服务器进程，允许直接读写普通的磁盘文件。SQLite具有以下特点：

• 零配置：无需安装或管理
• 服务器less：不需要单独的服务器进程
• 单个数据库文件：所有数据存储在一个文件中
• 跨平台：可在Windows、Linux、Mac OS等多种操作系统上运行
• 小巧：完全配置时小于400KB，省略可选功能时小于250KB
• 可靠：ACID事务支持
• 语言绑定：支持多种编程语言

SQLite广泛应用于移动应用、桌面软件、嵌入式系统以及小型网站等场景。它的简单性和高效性使其成为开发者的理想选择。

2. SQLite创建表的基础语法

在SQLite中，创建表使用CREATE TABLE语句。基本语法如下：

CREATE TABLE table_name (
column1 datatype [constraint],
column2 datatype [constraint],
column3 datatype [constraint],
...
table_constraint
);

复制代码

其中：

• table_name：要创建的表的名称
• column1, column2, column3：表中的列名
• datatype：列的数据类型
• constraint：列的约束条件
• table_constraint：表的约束条件

下面是一个简单的创建表示例：

CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER
);

复制代码

这个示例创建了一个名为users的表，包含四个列：id、name、email和age。

3. 数据类型详解

SQLite使用动态类型系统，这意味着数据类型是值的属性，而不是列本身的属性。但是，在创建表时，仍然可以指定列的类型亲和性(type affinity)。SQLite支持以下五种数据类型：

3.1 NULL

NULL值表示缺失的数据或未知值。任何列都可以包含NULL值，除非该列被定义为NOT NULL约束。

3.2 INTEGER

INTEGER用于存储整数值，可以是1、2、3、4、6或8字节的有符号整数。

CREATE TABLE products (
id INTEGER PRIMARY KEY,
quantity INTEGER DEFAULT 0
);

复制代码

3.3 REAL

REAL用于存储浮点数值，是8字节的IEEE浮点数。

CREATE TABLE measurements (
id INTEGER PRIMARY KEY,
temperature REAL,
humidity REAL
);

复制代码

3.4 TEXT

TEXT用于存储文本字符串，可以使用数据库编码（UTF-8、UTF-16BE或UTF-16LE）存储。

CREATE TABLE articles (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
content TEXT
);

复制代码

3.5 BLOB

BLOB用于存储二进制数据，完全按照输入存储。

CREATE TABLE files (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
data BLOB
);

复制代码

3.6 类型亲和性

SQLite还支持一些额外的数据类型声明，它们会被映射到上述五种基本类型：

NUMERIC类型亲和性比较特殊，它可以根据存储的值自动转换为INTEGER或REAL。

CREATE TABLE sample (
id INTEGER PRIMARY KEY,
int_col INTEGER,
text_col TEXT,
real_col REAL,
numeric_col NUMERIC,
blob_col BLOB
);

复制代码

4. 约束条件

约束条件用于限制表中数据的类型，确保数据的准确性和可靠性。SQLite支持以下约束：

4.1 NOT NULL 约束

NOT NULL约束确保列不能有NULL值。

CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department TEXT NOT NULL
);

复制代码

4.2 DEFAULT 约束

DEFAULT约束为列提供默认值，当INSERT语句没有为该列提供值时使用。

CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL,
stock INTEGER DEFAULT 0,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

复制代码

4.3 UNIQUE 约束

UNIQUE约束确保列中的所有值都是唯一的。

CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
email TEXT UNIQUE
);

复制代码

4.4 PRIMARY KEY 约束

PRIMARY KEY约束唯一标识表中的每条记录。主键必须包含唯一的值，且不能包含NULL值。在SQLite中，可以将主键定义为INTEGER PRIMARY KEY，这将使该列成为别名，用于rowid。

CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
location TEXT
);

复制代码

4.5 CHECK 约束

CHECK约束确保列中的值满足特定条件。

CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER CHECK(age >= 18),
salary REAL CHECK(salary > 0)
);

复制代码

4.6 FOREIGN KEY 约束

FOREIGN KEY约束用于建立两个表之间的链接，确保引用完整性。

CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department_id INTEGER,
FOREIGN KEY (department_id) REFERENCES departments(id)
);

复制代码

5. 实际应用案例

5.1 创建博客数据库

让我们创建一个简单的博客数据库，包含用户、文章和评论三个表。

-- 创建用户表
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
full_name TEXT,
bio TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);
-- 创建文章表
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
content TEXT NOT NULL,
author_id INTEGER NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
published_at TEXT,
status TEXT DEFAULT 'draft' CHECK(status IN ('draft', 'published', 'archived')),
FOREIGN KEY (author_id) REFERENCES users(id)
);
-- 创建评论表
CREATE TABLE comments (
id INTEGER PRIMARY KEY,
post_id INTEGER NOT NULL,
author_id INTEGER NOT NULL,
content TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (post_id) REFERENCES posts(id),
FOREIGN KEY (author_id) REFERENCES users(id)
);
-- 创建标签表
CREATE TABLE tags (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
-- 创建文章-标签关联表（多对多关系）
CREATE TABLE post_tags (
post_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
PRIMARY KEY (post_id, tag_id),
FOREIGN KEY (post_id) REFERENCES posts(id),
FOREIGN KEY (tag_id) REFERENCES tags(id)
);

复制代码

5.2 创建电子商务数据库

下面是一个电子商务数据库的示例，包含产品、客户、订单和订单项等表。

-- 创建客户表
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
phone TEXT,
password_hash TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);
-- 创建地址表
CREATE TABLE addresses (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
street TEXT NOT NULL,
city TEXT NOT NULL,
state TEXT NOT NULL,
postal_code TEXT NOT NULL,
country TEXT NOT NULL,
is_default INTEGER DEFAULT 0 CHECK(is_default IN (0, 1)),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
-- 创建产品分类表
CREATE TABLE categories (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
parent_id INTEGER,
FOREIGN KEY (parent_id) REFERENCES categories(id)
);
-- 创建产品表
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
price REAL NOT NULL CHECK(price > 0),
sku TEXT NOT NULL UNIQUE,
quantity INTEGER NOT NULL DEFAULT 0 CHECK(quantity >= 0),
category_id INTEGER,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(id)
);
-- 创建产品图片表
CREATE TABLE product_images (
id INTEGER PRIMARY KEY,
product_id INTEGER NOT NULL,
image_url TEXT NOT NULL,
is_primary INTEGER DEFAULT 0 CHECK(is_primary IN (0, 1)),
FOREIGN KEY (product_id) REFERENCES products(id)
);
-- 创建订单表
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date TEXT DEFAULT CURRENT_TIMESTAMP,
total_amount REAL NOT NULL CHECK(total_amount > 0),
status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
shipping_address_id INTEGER NOT NULL,
billing_address_id INTEGER NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id),
FOREIGN KEY (shipping_address_id) REFERENCES addresses(id),
FOREIGN KEY (billing_address_id) REFERENCES addresses(id)
);
-- 创建订单项表
CREATE TABLE order_items (
id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL CHECK(quantity > 0),
unit_price REAL NOT NULL CHECK(unit_price > 0),
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
-- 创建支付表
CREATE TABLE payments (
id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL,
amount REAL NOT NULL CHECK(amount > 0),
payment_method TEXT NOT NULL,
transaction_id TEXT,
status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'completed', 'failed', 'refunded')),
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES orders(id)
);

复制代码

5.3 使用Python创建SQLite表

下面是一个使用Python创建SQLite表并插入数据的完整示例：

import sqlite3
from datetime import datetime
# 连接到SQLite数据库（如果不存在，则会创建）
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 创建用户表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
full_name TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
''')
# 创建任务表
cursor.execute('''
CREATE TABLE IF NOT EXISTS tasks (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
description TEXT,
status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'in_progress', 'completed')),
priority TEXT DEFAULT 'medium' CHECK(priority IN ('low', 'medium', 'high')),
due_date TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
user_id INTEGER NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
)
''')
# 插入示例用户
users = [
('john_doe', 'john@example.com', 'hashed_password_1', 'John Doe'),
('jane_smith', 'jane@example.com', 'hashed_password_2', 'Jane Smith'),
('bob_johnson', 'bob@example.com', 'hashed_password_3', 'Bob Johnson')
]
cursor.executemany('''
INSERT INTO users (username, email, password_hash, full_name)
VALUES (?, ?, ?, ?)
''', users)
# 插入示例任务
tasks = [
('Complete project proposal', 'Write and submit the project proposal document', 'pending', 'high', '2023-12-15', 1),
('Team meeting', 'Weekly team sync meeting', 'pending', 'medium', '2023-12-10', 1),
('Code review', 'Review pull requests from team members', 'in_progress', 'medium', '2023-12-08', 2),
('Update documentation', 'Update API documentation for new features', 'pending', 'low', '2023-12-20', 2),
('Fix bugs', 'Fix reported bugs in the issue tracker', 'in_progress', 'high', '2023-12-05', 3)
]
cursor.executemany('''
INSERT INTO tasks (title, description, status, priority, due_date, user_id)
VALUES (?, ?, ?, ?, ?, ?)
''', tasks)
# 提交更改
conn.commit()
# 查询并打印所有用户及其任务
print("Users and their tasks:")
cursor.execute('''
SELECT u.username, u.full_name, t.title, t.status, t.priority, t.due_date
FROM users u
LEFT JOIN tasks t ON u.id = t.user_id
ORDER BY u.username, t.due_date
''')
results = cursor.fetchall()
for row in results:
print(f"User: {row[0]} ({row[1]}), Task: {row[2]}, Status: {row[3]}, Priority: {row[4]}, Due: {row[5]}")
# 关闭连接
conn.close()

复制代码

5.4 使用Java创建SQLite表

下面是一个使用Java创建SQLite表并插入数据的示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
public class SQLiteExample {
private static final String DB_URL = "jdbc:sqlite:library.db";
public static void main(String[] args) {
// 创建数据库连接
try (Connection conn = DriverManager.getConnection(DB_URL)) {
System.out.println("Connection to SQLite has been established.");
// 创建表
createTables(conn);
// 插入示例数据
insertSampleData(conn);
// 查询并显示数据
queryAndDisplayData(conn);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void createTables(Connection conn) throws SQLException {
// 创建作者表
String createAuthorsTable = """
CREATE TABLE IF NOT EXISTS authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
birth_date TEXT,
nationality TEXT
)""";
// 创建书籍表
String createBooksTable = """
CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
isbn TEXT NOT NULL UNIQUE,
publication_year INTEGER,
author_id INTEGER,
genre TEXT,
available INTEGER DEFAULT 1 CHECK(available IN (0, 1)),
FOREIGN KEY (author_id) REFERENCES authors(id)
)""";
// 创建借阅记录表
String createBorrowingsTable = """
CREATE TABLE IF NOT EXISTS borrowings (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL,
borrower_name TEXT NOT NULL,
borrow_date TEXT NOT NULL,
due_date TEXT NOT NULL,
return_date TEXT,
FOREIGN KEY (book_id) REFERENCES books(id)
)""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createAuthorsTable);
stmt.execute(createBooksTable);
stmt.execute(createBorrowingsTable);
System.out.println("Tables created successfully.");
}
}
private static void insertSampleData(Connection conn) throws SQLException {
// 插入作者数据
String insertAuthor = "INSERT INTO authors (name, birth_date, nationality) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertAuthor)) {
pstmt.setString(1, "J.K. Rowling");
pstmt.setString(2, "1965-07-31");
pstmt.setString(3, "British");
pstmt.executeUpdate();
pstmt.setString(1, "George Orwell");
pstmt.setString(2, "1903-06-25");
pstmt.setString(3, "British");
pstmt.executeUpdate();
pstmt.setString(1, "Harper Lee");
pstmt.setString(2, "1926-04-28");
pstmt.setString(3, "American");
pstmt.executeUpdate();
}
// 插入书籍数据
String insertBook = "INSERT INTO books (title, isbn, publication_year, author_id, genre) VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertBook)) {
pstmt.setString(1, "Harry Potter and the Philosopher's Stone");
pstmt.setString(2, "978-0-7475-3269-9");
pstmt.setInt(3, 1997);
pstmt.setInt(4, 1);
pstmt.setString(5, "Fantasy");
pstmt.executeUpdate();
pstmt.setString(1, "Harry Potter and the Chamber of Secrets");
pstmt.setString(2, "978-0-7475-3849-3");
pstmt.setInt(3, 1998);
pstmt.setInt(4, 1);
pstmt.setString(5, "Fantasy");
pstmt.executeUpdate();
pstmt.setString(1, "1984");
pstmt.setString(2, "978-0-452-28423-4");
pstmt.setInt(3, 1949);
pstmt.setInt(4, 2);
pstmt.setString(5, "Dystopian");
pstmt.executeUpdate();
pstmt.setString(1, "Animal Farm");
pstmt.setString(2, "978-0-452-28424-1");
pstmt.setInt(3, 1945);
pstmt.setInt(4, 2);
pstmt.setString(5, "Political Satire");
pstmt.executeUpdate();
pstmt.setString(1, "To Kill a Mockingbird");
pstmt.setString(2, "978-0-06-112008-4");
pstmt.setInt(3, 1960);
pstmt.setInt(4, 3);
pstmt.setString(5, "Fiction");
pstmt.executeUpdate();
}
// 插入借阅记录数据
String insertBorrowing = "INSERT INTO borrowings (book_id, borrower_name, borrow_date, due_date) VALUES (?, ?, ?, ?)";
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime now = LocalDateTime.now();
try (PreparedStatement pstmt = conn.prepareStatement(insertBorrowing)) {
pstmt.setInt(1, 1);
pstmt.setString(2, "Alice Johnson");
pstmt.setString(3, now.minusDays(5).format(formatter));
pstmt.setString(4, now.plusDays(10).format(formatter));
pstmt.executeUpdate();
pstmt.setInt(1, 3);
pstmt.setString(2, "Bob Smith");
pstmt.setString(3, now.minusDays(3).format(formatter));
pstmt.setString(4, now.plusDays(7).format(formatter));
pstmt.executeUpdate();
pstmt.setInt(1, 5);
pstmt.setString(2, "Charlie Brown");
pstmt.setString(3, now.minusDays(1).format(formatter));
pstmt.setString(4, now.plusDays(14).format(formatter));
pstmt.executeUpdate();
}
System.out.println("Sample data inserted successfully.");
}
private static void queryAndDisplayData(Connection conn) throws SQLException {
// 查询所有书籍及其作者
String queryBooks = """
SELECT b.title, b.isbn, b.publication_year, a.name AS author_name, b.genre, b.available
FROM books b
JOIN authors a ON b.author_id = a.id
ORDER BY a.name, b.publication_year
""";
System.out.println("\nBooks in the library:");
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(queryBooks)) {
System.out.println("Title | ISBN | Year | Author | Genre | Available");
System.out.println("--------------------------------------------------------");
while (rs.next()) {
System.out.printf("%s | %s | %d | %s | %s | %s%n",
rs.getString("title"),
rs.getString("isbn"),
rs.getInt("publication_year"),
rs.getString("author_name"),
rs.getString("genre"),
rs.getInt("available") == 1 ? "Yes" : "No");
}
}
// 查询当前借阅记录
String queryBorrowings = """
SELECT br.id, b.title, br.borrower_name, br.borrow_date, br.due_date
FROM borrowings br
JOIN books b ON br.book_id = b.id
WHERE br.return_date IS NULL
ORDER BY br.due_date
""";
System.out.println("\nCurrent borrowings:");
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(queryBorrowings)) {
System.out.println("ID | Title | Borrower | Borrow Date | Due Date");
System.out.println("--------------------------------------------------------");
while (rs.next()) {
System.out.printf("%d | %s | %s | %s | %s%n",
rs.getInt("id"),
rs.getString("title"),
rs.getString("borrower_name"),
rs.getString("borrow_date"),
rs.getString("due_date"));
}
}
}
}

复制代码

6. 表设计技巧

6.1 命名约定

良好的命名约定可以提高数据库的可读性和可维护性：

• 使用小写字母和下划线分隔单词（snake_case）
• 表名使用复数形式（如users而不是user）
• 列名使用单数形式（如first_name而不是first_names）
• 主键通常命名为id
• 外键命名为referenced_table_id（如user_id）
• 避免使用SQL保留字作为表名或列名

-- 好的命名示例
CREATE TABLE users (
id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
content TEXT NOT NULL,
author_id INTEGER NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (author_id) REFERENCES users(id)
);

复制代码

6.2 主键设计

在SQLite中，主键设计有以下几种常见方式：

1. 自增整数主键：最常用的主键类型，使用INTEGER PRIMARY KEY，SQLite会自动使其自增。

CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL
);

复制代码

1. 复合主键：当单个列不能唯一标识记录时，可以使用多个列作为主键。

CREATE TABLE order_items (
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
unit_price REAL NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);

复制代码

1. 自然主键：使用具有业务意义的列作为主键，如ISBN、电子邮件等。

CREATE TABLE books (
isbn TEXT PRIMARY KEY,
title TEXT NOT NULL,
author TEXT NOT NULL,
publication_year INTEGER
);

复制代码

6.3 索引设计

索引可以显著提高查询性能，但会增加插入、更新和删除操作的开销。以下是一些索引设计的技巧：

1. 为主键自动创建索引：SQLite会自动为主键创建索引。
2. 为外键创建索引：外键列经常用于JOIN操作，应该创建索引。

为主键自动创建索引：SQLite会自动为主键创建索引。

为外键创建索引：外键列经常用于JOIN操作，应该创建索引。

CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date TEXT DEFAULT CURRENT_TIMESTAMP,
total_amount REAL NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
-- 为外键创建索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

复制代码

1. 为经常用于查询条件的列创建索引：

CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT NOT NULL,
price REAL NOT NULL,
description TEXT
);
-- 为经常用于查询条件的列创建索引
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_price ON products(price);

复制代码

1. 为复合查询条件创建复合索引：

-- 如果经常同时按category和price查询
CREATE INDEX idx_products_category_price ON products(category, price);

复制代码

1. 避免过度索引：只为经常用于查询条件、排序或JOIN的列创建索引。

6.4 规范化设计

数据库规范化是组织表和列以减少数据冗余和提高数据完整性的过程。常见的规范化形式包括：

确保每个列只包含原子值，每个记录是唯一的。

-- 不符合1NF的设计
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
phone_numbers TEXT -- 存储多个电话号码，如"123-456-7890, 987-654-3210"
);
-- 符合1NF的设计
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE customer_phones (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
phone_number TEXT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);

复制代码

在满足1NF的基础上，消除部分依赖，即非主键列必须完全依赖于整个主键。

-- 不符合2NF的设计
CREATE TABLE order_items (
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
unit_price REAL NOT NULL,
product_name TEXT NOT NULL, -- 依赖于product_id，而不是整个主键
PRIMARY KEY (order_id, product_id)
);
-- 符合2NF的设计
CREATE TABLE order_items (
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
unit_price REAL NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL
);

复制代码

在满足2NF的基础上，消除传递依赖，即非主键列不能依赖于其他非主键列。

-- 不符合3NF的设计
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department_id INTEGER NOT NULL,
department_name TEXT NOT NULL, -- 依赖于department_id，而不是主键
location TEXT
);
-- 符合3NF的设计
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department_id INTEGER NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
location TEXT
);

复制代码

6.5 反规范化

在某些情况下，为了提高查询性能，可以有意地引入冗余数据，这称为反规范化。常见的反规范化技术包括：

1. 添加冗余列：

CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date TEXT DEFAULT CURRENT_TIMESTAMP,
total_amount REAL NOT NULL,
customer_name TEXT, -- 冗余列，避免JOIN查询
FOREIGN KEY (customer_id) REFERENCES customers(id)
);

复制代码

1. 预计算汇总值：

CREATE TABLE categories (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
product_count INTEGER DEFAULT 0 -- 冗余列，存储产品数量
);

复制代码

1. 使用汇总表：

CREATE TABLE monthly_sales (
year INTEGER NOT NULL,
month INTEGER NOT NULL,
total_amount REAL NOT NULL,
total_orders INTEGER NOT NULL,
PRIMARY KEY (year, month)
);

复制代码

反规范化可以提高查询性能，但会增加数据维护的复杂性，需要在性能和数据一致性之间做出权衡。

7. 最佳实践

7.1 使用事务

事务是一组原子性的SQL操作，要么全部执行成功，要么全部失败回滚。使用事务可以确保数据的一致性。

import sqlite3
conn = sqlite3.connect('banking.db')
cursor = conn.cursor()
try:
# 开始事务
conn.execute("BEGIN TRANSACTION")
# 从账户1扣除100
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
# 向账户2增加100
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
# 记录交易
cursor.execute("INSERT INTO transactions (from_account, to_account, amount) VALUES (1, 2, 100)")
# 提交事务
conn.commit()
print("Transaction completed successfully.")
except Exception as e:
# 发生错误，回滚事务
conn.rollback()
print(f"Transaction failed: {e}")
finally:
conn.close()

复制代码

7.2 使用参数化查询

参数化查询可以防止SQL注入攻击，并提高查询性能。

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 不安全的查询方式（容易受到SQL注入攻击）
username = "admin' --"
query = f"SELECT * FROM users WHERE username = '{username}'"
cursor.execute(query)
# 安全的参数化查询方式
username = "admin' --"
cursor.execute("SELECT * FROM users WHERE username = ?", (username,))
# 插入数据也使用参数化查询
user_data = ("john_doe", "john@example.com", "hashed_password")
cursor.execute("INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)", user_data)
conn.commit()
conn.close()

复制代码

7.3 定期备份

定期备份数据库是防止数据丢失的重要措施。

import sqlite3
import shutil
from datetime import datetime
def backup_database(db_path, backup_dir):
# 创建备份文件名（包含时间戳）
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
backup_path = f"{backup_dir}/backup_{timestamp}.db"
try:
# 连接到数据库
conn = sqlite3.connect(db_path)
# 确保所有数据都已写入磁盘
conn.execute("BEGIN IMMEDIATE")
conn.commit()
# 关闭连接
conn.close()
# 复制数据库文件
shutil.copy2(db_path, backup_path)
print(f"Database backed up successfully to {backup_path}")
except Exception as e:
print(f"Backup failed: {e}")
# 使用示例
backup_database("example.db", "backups")

复制代码

7.4 使用视图简化复杂查询

视图是虚拟表，基于SQL语句的结果集。使用视图可以简化复杂查询，提高代码的可读性和可维护性。

-- 创建视图
CREATE VIEW customer_order_summary AS
SELECT
c.id AS customer_id,
c.first_name,
c.last_name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM
customers c
LEFT JOIN
orders o ON c.id = o.customer_id
GROUP BY
c.id, c.first_name, c.last_name;
-- 使用视图
SELECT * FROM customer_order_summary WHERE order_count > 5 ORDER BY total_spent DESC;

复制代码

7.5 使用触发器自动化任务

触发器是在特定数据库事件（如INSERT、UPDATE或DELETE）发生时自动执行的特殊程序。

-- 创建触发器，在插入新订单时更新客户的总消费金额
CREATE TRIGGER update_customer_total_spent
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE customers
SET total_spent = (
SELECT COALESCE(SUM(total_amount), 0)
FROM orders
WHERE customer_id = NEW.customer_id
)
WHERE id = NEW.customer_id;
END;
-- 创建触发器，在更新订单时更新客户的总消费金额
CREATE TRIGGER update_customer_total_spent_on_update
AFTER UPDATE ON orders
FOR EACH ROW
WHEN NEW.total_amount <> OLD.total_amount
BEGIN
UPDATE customers
SET total_spent = (
SELECT COALESCE(SUM(total_amount), 0)
FROM orders
WHERE customer_id = NEW.customer_id
)
WHERE id = NEW.customer_id;
END;

复制代码

7.6 优化查询性能

以下是一些优化SQLite查询性能的技巧：

1. 使用EXPLAIN QUERY PLAN分析查询：

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 5;

复制代码

1. *避免使用SELECT **：

-- 不好的做法
SELECT * FROM customers;
-- 好的做法
SELECT id, first_name, last_name, email FROM customers;

复制代码

1. 使用LIMIT限制结果集大小：

SELECT id, title, content FROM posts ORDER BY created_at DESC LIMIT 10;

复制代码

1. 避免在WHERE子句中使用函数：

-- 不好的做法
SELECT * FROM orders WHERE DATE(order_date) = '2023-12-01';
-- 好的做法
SELECT * FROM orders WHERE order_date >= '2023-12-01' AND order_date < '2023-12-02';

复制代码

1. 使用适当的JOIN类型：

-- INNER JOIN只返回匹配的行
SELECT o.id, o.order_date, c.first_name, c.last_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
-- LEFT JOIN返回左表的所有行，即使右表没有匹配
SELECT c.id, c.first_name, c.last_name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name;

复制代码

8. 常见问题与解决方案

8.1 数据库锁定问题

SQLite在写入操作时会锁定整个数据库文件，这可能导致并发访问问题。

解决方案：

1. 使用WAL模式：WAL（Write-Ahead Logging）模式允许更多的并发读取和写入。

import sqlite3
conn = sqlite3.connect('example.db')
conn.execute("PRAGMA journal_mode=WAL")
conn.close()

复制代码

1. 尽量缩短事务时间：减少事务中包含的操作数量，尽快提交事务。

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 不好的做法：长事务
conn.execute("BEGIN TRANSACTION")
for i in range(1000):
cursor.execute("INSERT INTO logs (message) VALUES (?)", (f"Log entry {i}",))
conn.commit()
# 好的做法：短事务或批量操作
for i in range(0, 1000, 100):
conn.execute("BEGIN TRANSACTION")
for j in range(i, i + 100):
cursor.execute("INSERT INTO logs (message) VALUES (?)", (f"Log entry {j}",))
conn.commit()
conn.close()

复制代码

8.2 数据库文件过大

随着数据量的增加，数据库文件可能会变得过大，影响性能。

解决方案：

1. 定期清理不必要的数据：

-- 删除过期的日志
DELETE FROM logs WHERE created_at < date('now', '-30 days');
-- 删除未使用的临时数据
DELETE FROM temp_data WHERE created_at < date('now', '-7 days');

复制代码

1. 使用VACUUM命令重建数据库：

import sqlite3
conn = sqlite3.connect('example.db')
conn.execute("VACUUM")
conn.close()

复制代码

1. 考虑分表或分库：对于非常大的数据集，可以考虑将数据分散到多个表或数据库中。

8.3 数据类型不匹配

SQLite的动态类型系统可能导致数据类型不匹配的问题。

解决方案：

1. 使用严格类型检查：在应用程序中添加类型检查。

import sqlite3
def insert_user(conn, username, email, age):
# 类型检查
if not isinstance(username, str) or not username:
raise ValueError("Username must be a non-empty string")
if not isinstance(email, str) or '@' not in email:
raise ValueError("Email must be a valid email address")
if not isinstance(age, int) or age <= 0:
raise ValueError("Age must be a positive integer")
cursor = conn.cursor()
cursor.execute("INSERT INTO users (username, email, age) VALUES (?, ?, ?)", (username, email, age))
conn.commit()
# 使用示例
conn = sqlite3.connect('example.db')
try:
insert_user(conn, "john_doe", "john@example.com", 30)
except ValueError as e:
print(f"Error: {e}")
finally:
conn.close()

复制代码

1. 使用CHECK约束：在表定义中添加CHECK约束。

CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL CHECK(price > 0),
quantity INTEGER NOT NULL CHECK(quantity >= 0),
category TEXT NOT NULL
);

复制代码

8.4 外键约束不生效

默认情况下，SQLite不会强制执行外键约束。

解决方案：

1. 启用外键约束：

import sqlite3
conn = sqlite3.connect('example.db')
conn.execute("PRAGMA foreign_keys = ON")
conn.close()

复制代码

1. 在每次连接时启用外键约束：

import sqlite3
def get_connection():
conn = sqlite3.connect('example.db')
conn.execute("PRAGMA foreign_keys = ON")
return conn
# 使用示例
conn = get_connection()
cursor = conn.cursor()
# 执行数据库操作
conn.close()

复制代码

8.5 查询性能问题

随着数据量的增加，查询性能可能会下降。

解决方案：

1. 添加适当的索引：

-- 为经常用于查询条件的列创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);

复制代码

1. 优化查询语句：

-- 不好的做法：使用子查询
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');
-- 好的做法：使用JOIN
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.status = 'active';

复制代码

1. 使用ANALYZE命令更新统计信息：

ANALYZE;

复制代码

1. 考虑使用内存数据库：对于临时操作或性能要求高的场景，可以使用内存数据库。

import sqlite3
# 创建内存数据库
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
# 创建表并插入数据
cursor.execute("CREATE TABLE temp_data (id INTEGER PRIMARY KEY, value TEXT)")
cursor.executemany("INSERT INTO temp_data (value) VALUES (?)", [('A',), ('B',), ('C',)])
# 执行查询
cursor.execute("SELECT * FROM temp_data")
print(cursor.fetchall())
conn.close()

复制代码

9. 总结

SQLite是一个强大而灵活的嵌入式数据库引擎，适用于各种应用程序，从移动应用到桌面软件再到网站。通过掌握SQLite创建表的基础语法、数据类型、约束条件以及表设计技巧，你可以构建高效、可靠的数据库结构。

在实际应用中，良好的表设计应该考虑以下几点：

1. 选择合适的数据类型：根据存储的数据选择最合适的数据类型，避免浪费存储空间。
2. 使用适当的约束：使用NOT NULL、UNIQUE、CHECK等约束确保数据完整性。
3. 设计合理的主键：选择能够唯一标识记录的列作为主键，通常是自增整数。
4. 规范化设计：遵循数据库规范化原则，减少数据冗余，提高数据一致性。
5. 合理使用索引：为经常用于查询条件、排序或JOIN的列创建索引，提高查询性能。
6. 考虑性能与一致性的平衡：在必要时使用反规范化技术，但要注意数据一致性问题。

通过遵循最佳实践，如使用事务、参数化查询、定期备份、使用视图和触发器等，你可以确保数据库的安全性、可靠性和性能。

最后，了解并解决常见问题，如数据库锁定、文件过大、数据类型不匹配、外键约束不生效和查询性能问题，可以帮助你更好地维护和优化SQLite数据库。

希望这篇教程能帮助你快速掌握SQLite表设计技巧与最佳实践，轻松入门数据库开发，提高工作效率。

	通知：关于部分勋章领取条件及购买价格调整的通知	05-18 21:22
	通知：本站资源由网友上传分享，如有违规等问题请到版务模块进行投诉，资源失效请在帖子内回复要求补档，会尽快处理！	10-23 09:31

活动公告

SQLite数据库创建表实例教程从基础语法到实际应用快速掌握SQLite表设计技巧与最佳实践助你轻松入门数据库开发提高工作效率

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

浏览过的版块

塔罗

立华奏

站长推荐 /1

友情链接

Tencent QQ

活动公告

SQLite数据库创建表实例教程 从基础语法到实际应用快速掌握SQLite表设计技巧与最佳实践助你轻松入门数据库开发提高工作效率

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

浏览过的版块

塔罗

立华奏

站长推荐 /1

友情链接

Tencent QQ

SQLite数据库创建表实例教程从基础语法到实际应用快速掌握SQLite表设计技巧与最佳实践助你轻松入门数据库开发提高工作效率