sql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

SQL

Overview

概述

This skill provides guidance for working with SQLite databases. It covers query writing, schema design, and SQLite-specific best practices.

本技能提供SQLite数据库的使用指导，涵盖查询编写、架构设计以及SQLite特有的最佳实践。

When to Use This Skill

适用场景

Use this skill when:

Writing SQL queries for SQLite databases
Analyzing or optimizing existing queries
Designing database schemas
Creating database migrations
Working with Go code that interacts with SQLite

在以下场景中使用本技能：

为SQLite数据库编写SQL查询
分析或优化现有查询
设计数据库架构
创建数据库迁移
编写与SQLite交互的Go代码

SQLite Best Practices

SQLite最佳实践

Query Writing

查询编写

ALWAYS write lowercase queries. Uppercase queries make me sad.
Prefer
```
select *
```
over explicit column names
Prefer CTEs over long nested subqueries

始终使用小写编写查询。大写查询会让我不悦。
优先使用
```
select *
```
而非显式列名
优先使用CTE而非长嵌套子查询

Schema Design

架构设计

ALWAYS use
```
strict
```
tables
ALWAYS write timestamps like this:
```
strftime('%Y-%m-%dT%H:%M:%fZ')
```
Time modifications should also use
```
strftime
```
Usually start with the primary key, which is usually defined like this:
```
id text primary key default ('p_' || lower(hex(randomblob(16))))
```
(where the
```
p_
```
is a prefix depending on the table name; two-letter prefixes are okay too, so the prefix is unique among tables)

After the primary key come

created

updated

columns like this:

created text not null default (strftime('%Y-%m-%dT%H:%M:%fZ'))

Updated timestamps are automatically updated with a trigger like this:

sql

create trigger table_name_updated_timestamp after update on table_name begin
update table_name set updated = strftime('%Y-%m-%dT%H:%M:%fZ') where id = old.id;
end;

始终使用
```
strict
```
表
始终按如下格式编写时间戳：
```
strftime('%Y-%m-%dT%H:%M:%fZ')
```
时间修改也应使用
```
strftime
```
通常从主键开始定义，主键的常见写法如下：
```
id text primary key default ('p_' || lower(hex(randomblob(16))))
```
（其中
```
p_
```
是取决于表名的前缀；也可以使用双字母前缀，确保前缀在各表中唯一）

主键之后是

created

updated

列，写法如下：

created text not null default (strftime('%Y-%m-%dT%H:%M:%fZ'))

更新时间戳可通过如下触发器自动更新：

sql

create trigger table_name_updated_timestamp after update on table_name begin
update table_name set updated = strftime('%Y-%m-%dT%H:%M:%fZ') where id = old.id;
end;