SQL数据库基础（SQL Database Fundamentals）¶

一句话说明¶

SQL是管理和查询结构化数据的标准语言，在生信中用于存储样本元数据、查询基因注释数据库、以及用数据库思维高效分析物种丰度表。

核心概念（白话版）¶

1. 数据库是什么¶

定义：数据库（Database）就是一个有组织的数据仓库，用来存储、管理和检索大量数据。
白话比方：你可以把数据库想象成一个超级 Excel 文件——但它比 Excel 强太多了：
Excel 打开几万行就卡死，数据库能轻松处理几百万、几千万行
Excel 用鼠标点来点去，数据库用 SQL 语句精确查询
Excel 只能一个人用，数据库支持多人同时读写
Excel 数据容易搞乱，数据库有严格的格式约束（每列只能放规定的数据类型）

2. SQL是什么¶

定义：SQL（Structured Query Language，结构化查询语言）是和数据库"对话"的语言。
白话比方：你去图书馆借书，SQL 就是你跟图书管理员说的那些话——"帮我找所有 2024 年出版的生物学书"，"把这本书的作者改成张三"。SQL 就是这种"精确的请求语句"。
发音：可以读作 "S-Q-L"（三个字母分开读），也可以读 "sequel"。
特点：
不区分大小写（SELECT 和 select 一样），但约定俗成关键字大写，表名/列名小写
每条语句以分号 ; 结尾
非常接近英语自然语言，比编程语言好学

3. 为什么生信要学SQL¶

场景	没有SQL	有SQL
从10万条基因注释中找目标基因	写 Python 脚本一行行扫描，慢	一条 SELECT 语句，秒出结果
合并样本元数据和丰度表	pandas merge + 各种对齐	一条 JOIN 语句搞定
统计每个门的物种数量	groupby + count + 排序	`GROUP BY phylum` 一句话
NCBI / Ensembl / UniProt 查询	网页手动点	直接 SQL 查询本地镜像
存储几十万条代谢通路注释	CSV 文件，打开就卡	数据库秒查

总结：数据量小的时候用 pandas 够了；数据量大、要频繁查询、要多人共享时，SQL 是标配。

4. 关系型 vs 非关系型数据库¶

对比项	关系型数据库（RDBMS）	非关系型数据库（NoSQL）
数据结构	表格（行+列），像 Excel	文档/键值对/图，格式灵活
查询语言	SQL	各家不同（MongoDB 用 JSON 查询）
代表产品	MySQL, PostgreSQL, SQLite	MongoDB, Redis, Elasticsearch
数据关系	用外键关联多张表	通常把相关数据塞到一个文档里
适用场景	结构化数据、需要复杂查询	半结构化/非结构化数据、高并发
生信用途	样本元数据、基因注释、实验记录	序列搜索引擎、日志存储

面试结论：生信工程师主要用关系型数据库，学好 SQL 就够用了。SQLite 是最轻量的选择——不需要安装服务器，Python 自带支持，非常适合学习和小型项目。

SQL 基础语法¶

以下所有示例都用生信场景，数据用 T2D 肠道菌群研究举例。

1. CREATE TABLE / INSERT —— 建表和插入数据¶

CREATE TABLE（创建表）¶

白话：相当于在 Excel 里新建一个工作表，然后定义每一列叫什么名字、能放什么类型的数据。

-- 创建样本元数据表
-- 白话：建一张"样本信息登记表"，规定好每列放什么
CREATE TABLE samples (
    sample_id   TEXT PRIMARY KEY,   -- 样本编号，主键（唯一标识，不能重复）
    patient_id  TEXT NOT NULL,      -- 患者编号，不能为空
    group_name  TEXT NOT NULL,      -- 分组：'T2D' 或 'Control'
    age         INTEGER,            -- 年龄，整数
    bmi         REAL,               -- BMI指数，小数
    gender      TEXT,               -- 性别：'M' 或 'F'
    collect_date TEXT               -- 采样日期
);

-- 创建物种丰度表
CREATE TABLE abundance (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,  -- 自增主键
    sample_id   TEXT NOT NULL,      -- 样本编号（关联 samples 表）
    taxonomy    TEXT NOT NULL,      -- 物种分类名（如 "Bacteroides"）
    tax_level   TEXT NOT NULL,      -- 分类层级：'phylum'/'genus'/'species'
    abundance   REAL NOT NULL,      -- 相对丰度值（0~1）
    read_count  INTEGER             -- 原始reads计数
);

常用数据类型：

SQL类型	说明	举例
`TEXT`	文本字符串	样本ID、物种名、基因名
`INTEGER`	整数	reads计数、年龄、染色体编号
`REAL`	小数（浮点数）	丰度值、p值、BMI
`BLOB`	二进制大数据	序列文件（很少用）

关键约束：

约束	白话解释
`PRIMARY KEY`	主键——这列的值必须唯一，用来标识每一行，像身份证号
`NOT NULL`	不能为空——这列必须填值
`UNIQUE`	唯一——这列不能有重复值
`DEFAULT`	默认值——不填就自动用这个值
`AUTOINCREMENT`	自增——每插入一行自动加1

INSERT（插入数据）¶

白话：相当于往 Excel 表里新增一行数据。

-- 插入单条样本数据
INSERT INTO samples (sample_id, patient_id, group_name, age, bmi, gender, collect_date)
VALUES ('S001', 'P001', 'T2D', 55, 28.3, 'M', '2024-01-15');

-- 插入多条数据（一次插入多行）
INSERT INTO samples (sample_id, patient_id, group_name, age, bmi, gender, collect_date)
VALUES
    ('S002', 'P002', 'Control', 48, 22.1, 'F', '2024-01-16'),
    ('S003', 'P003', 'T2D', 62, 30.5, 'M', '2024-01-17'),
    ('S004', 'P004', 'Control', 45, 21.8, 'F', '2024-01-18');

-- 插入丰度数据
INSERT INTO abundance (sample_id, taxonomy, tax_level, abundance, read_count)
VALUES
    ('S001', 'Bacteroides', 'genus', 0.25, 15000),
    ('S001', 'Prevotella',  'genus', 0.08, 4800),
    ('S001', 'Faecalibacterium', 'genus', 0.12, 7200),
    ('S002', 'Bacteroides', 'genus', 0.30, 18000),
    ('S002', 'Prevotella',  'genus', 0.15, 9000);

2. SELECT / WHERE / ORDER BY / LIMIT —— 查询数据¶

白话：SELECT 就是"查"——从表里挑出你想看的数据。

-- ============================================================
-- 基础查询：查看所有样本
-- 白话：把 samples 表里的所有数据都拿出来看看
-- ============================================================
SELECT * FROM samples;
-- * 表示"所有列"

-- 只看特定列
SELECT sample_id, group_name, age FROM samples;

-- ============================================================
-- WHERE：加条件筛选
-- 白话：只拿满足条件的行，相当于 Excel 的筛选功能
-- ============================================================

-- 只看 T2D 组的样本
SELECT * FROM samples
WHERE group_name = 'T2D';

-- 找 BMI > 25 的样本（超重）
SELECT sample_id, bmi, group_name FROM samples
WHERE bmi > 25;

-- 多条件：T2D 组 且 年龄 > 50
SELECT * FROM samples
WHERE group_name = 'T2D' AND age > 50;

-- 多条件：T2D 组 或 BMI > 30
SELECT * FROM samples
WHERE group_name = 'T2D' OR bmi > 30;

-- 模糊查询：找名字包含 "Bacteroid" 的物种
-- LIKE 是模糊匹配，% 表示"任意字符"
SELECT * FROM abundance
WHERE taxonomy LIKE '%Bacteroid%';

-- IN 查询：找指定的几个物种
SELECT * FROM abundance
WHERE taxonomy IN ('Bacteroides', 'Prevotella', 'Faecalibacterium');

-- BETWEEN：找丰度在 0.1 到 0.3 之间的
SELECT * FROM abundance
WHERE abundance BETWEEN 0.1 AND 0.3;

-- IS NULL / IS NOT NULL：找缺失值
SELECT * FROM samples
WHERE bmi IS NULL;         -- 找BMI没填的样本
SELECT * FROM samples
WHERE bmi IS NOT NULL;     -- 找BMI有值的样本

-- ============================================================
-- ORDER BY：排序
-- 白话：按某列排队，ASC = 从小到大（默认），DESC = 从大到小
-- ============================================================

-- 按丰度从高到低排序
SELECT * FROM abundance
ORDER BY abundance DESC;

-- 先按分组排，同组内按年龄排
SELECT * FROM samples
ORDER BY group_name ASC, age DESC;

-- ============================================================
-- LIMIT：只看前N条
-- 白话：只拿前几行，相当于 pandas 的 head()
-- ============================================================

-- 看丰度最高的前10个记录
SELECT * FROM abundance
ORDER BY abundance DESC
LIMIT 10;

-- 跳过前5条，取接下来的10条（分页查询）
SELECT * FROM abundance
ORDER BY abundance DESC
LIMIT 10 OFFSET 5;

3. GROUP BY / HAVING / 聚合函数 —— 分组统计¶

白话：GROUP BY 就是"按某列分组"，然后对每组做统计（数个数、求平均、求最大值等）。相当于 pandas 的 groupby().agg()。

五大聚合函数¶

函数	白话	生信例子
`COUNT(*)`	数一数有多少行	每组有多少个样本
`SUM(列)`	求和	每个样本的总 reads 数
`AVG(列)`	求平均值	每组的平均 BMI
`MAX(列)`	求最大值	某物种的最高丰度
`MIN(列)`	求最小值	某物种的最低丰度

-- 统计每组有多少个样本
SELECT group_name, COUNT(*) AS sample_count
FROM samples
GROUP BY group_name;
-- AS 是给结果列起个别名，不影响原表

-- 每组的平均年龄和平均BMI
SELECT group_name,
       AVG(age) AS avg_age,
       AVG(bmi) AS avg_bmi
FROM samples
GROUP BY group_name;

-- 每个样本中有多少个物种（genus 层级）
SELECT sample_id, COUNT(*) AS genus_count
FROM abundance
WHERE tax_level = 'genus'
GROUP BY sample_id;

-- 每个物种在所有样本中的平均丰度
SELECT taxonomy,
       AVG(abundance) AS mean_abundance,
       MAX(abundance) AS max_abundance,
       MIN(abundance) AS min_abundance
FROM abundance
WHERE tax_level = 'genus'
GROUP BY taxonomy
ORDER BY mean_abundance DESC;

-- ============================================================
-- HAVING：对分组结果再筛选
-- 白话：WHERE 是筛选"原始行"，HAVING 是筛选"统计后的结果"
-- 记忆口诀：WHERE 先筛行，GROUP BY 再分组，HAVING 后筛组
-- ============================================================

-- 找出平均丰度 > 0.1 的物种（优势菌）
SELECT taxonomy, AVG(abundance) AS mean_abundance
FROM abundance
WHERE tax_level = 'genus'
GROUP BY taxonomy
HAVING AVG(abundance) > 0.1
ORDER BY mean_abundance DESC;

-- 找出在 >= 3 个样本中出现的物种（核心菌群）
SELECT taxonomy, COUNT(DISTINCT sample_id) AS sample_count
FROM abundance
WHERE tax_level = 'genus' AND abundance > 0
GROUP BY taxonomy
HAVING COUNT(DISTINCT sample_id) >= 3;

SQL 执行顺序（面试常考）：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
  1       2        3          4        5         6         7

白话：先找到表 → 筛选行 → 分组 → 筛选组 → 选列 → 排序 → 取前N条

4. JOIN —— 多表关联查询¶

白话：JOIN 就是把两张表"拼"在一起。就像你有一张"样本信息表"和一张"物种丰度表"，想把它们合并成一张大表来分析。相当于 pandas 的 pd.merge()。

JOIN 类型图解¶

表A（samples）          表B（abundance）
┌──────────┐           ┌──────────┐
│ S001     │           │ S001     │
│ S002     │           │ S002     │
│ S003     │           │ S003     │
│ S004     │           │ S005     │  ← 只在B表中
└──────────┘           └──────────┘
     ↑ S004只在A表中

INNER JOIN（内连接）：只取两边都有的 → S001, S002, S003
LEFT JOIN（左连接）：A表全保留，B表没有的填NULL → S001, S002, S003, S004(NULL)
RIGHT JOIN（右连接）：B表全保留，A表没有的填NULL → S001, S002, S003, S005(NULL)
FULL OUTER JOIN（全连接）：两边都保留 → S001, S002, S003, S004(NULL), S005(NULL)

注意：SQLite 不支持 RIGHT JOIN 和 FULL OUTER JOIN，但 MySQL / PostgreSQL 支持。面试时知道概念就行。

-- ============================================================
-- INNER JOIN：只取两张表都有的样本
-- 白话：只合并两边都有对应数据的行
-- ============================================================
SELECT s.sample_id, s.group_name, s.age,
       a.taxonomy, a.abundance
FROM samples s
INNER JOIN abundance a ON s.sample_id = a.sample_id;
-- s 和 a 是表的别名（alias），写起来短
-- ON 后面写关联条件：两张表通过 sample_id 对应

-- ============================================================
-- LEFT JOIN：保留左表所有行
-- 白话：samples 表的所有样本都要显示，即使没有丰度数据也保留
-- ============================================================
SELECT s.sample_id, s.group_name,
       a.taxonomy, a.abundance
FROM samples s
LEFT JOIN abundance a ON s.sample_id = a.sample_id;
-- 如果某样本在 abundance 表中没有数据，taxonomy 和 abundance 显示 NULL

-- ============================================================
-- 实际生信例子：合并元数据和丰度，做分组比较
-- ============================================================

-- 比较 T2D 组和 Control 组的 Bacteroides 平均丰度
SELECT s.group_name,
       AVG(a.abundance) AS avg_bacteroides
FROM samples s
INNER JOIN abundance a ON s.sample_id = a.sample_id
WHERE a.taxonomy = 'Bacteroides'
GROUP BY s.group_name;

-- 找出每个样本的优势菌（丰度最高的物种）
SELECT s.sample_id, s.group_name,
       a.taxonomy, a.abundance
FROM samples s
INNER JOIN abundance a ON s.sample_id = a.sample_id
WHERE a.tax_level = 'genus'
  AND a.abundance = (
      SELECT MAX(a2.abundance)
      FROM abundance a2
      WHERE a2.sample_id = s.sample_id
        AND a2.tax_level = 'genus'
  );

5. UPDATE / DELETE —— 修改和删除数据¶

白话：UPDATE 是改数据，DELETE 是删数据。操作前一定要加 WHERE 条件，否则会改/删整张表！

-- ============================================================
-- UPDATE：修改数据
-- 白话：把某些行的某列值改掉
-- ============================================================

-- 修改某个样本的BMI值（发现录入错了）
UPDATE samples
SET bmi = 27.5
WHERE sample_id = 'S001';

-- 同时修改多列
UPDATE samples
SET age = 56, bmi = 28.0
WHERE sample_id = 'S001';

-- 批量修改：把所有没填性别的样本标记为 'Unknown'
UPDATE samples
SET gender = 'Unknown'
WHERE gender IS NULL;

-- ============================================================
-- DELETE：删除数据
-- 白话：删掉满足条件的行
-- ⚠️ 一定要加 WHERE！不加就删全表！
-- ============================================================

-- 删除某个样本的丰度数据
DELETE FROM abundance
WHERE sample_id = 'S004';

-- 删除丰度为0的记录（清理无效数据）
DELETE FROM abundance
WHERE abundance = 0;

-- ⚠️ 危险操作：不加 WHERE = 删除所有数据！
-- DELETE FROM abundance;  -- 千万别这么干！

6. 子查询（Subquery）¶

白话：子查询就是"查询里面嵌套查询"——先用内层查询查出一个中间结果，再让外层查询用这个结果。像俄罗斯套娃。

-- 找出丰度高于全体平均丰度的物种
SELECT taxonomy, abundance
FROM abundance
WHERE abundance > (
    SELECT AVG(abundance) FROM abundance
);
-- 内层：先算出全体平均丰度
-- 外层：再筛选出超过平均值的记录

-- 找出 T2D 组中有哪些样本
-- 然后查这些样本的丰度数据
SELECT * FROM abundance
WHERE sample_id IN (
    SELECT sample_id FROM samples
    WHERE group_name = 'T2D'
);

-- 找出每个物种在 T2D 和 Control 组的平均丰度差异
SELECT taxonomy,
       t2d_avg,
       ctrl_avg,
       (t2d_avg - ctrl_avg) AS diff
FROM (
    SELECT a.taxonomy,
           AVG(CASE WHEN s.group_name = 'T2D' THEN a.abundance END) AS t2d_avg,
           AVG(CASE WHEN s.group_name = 'Control' THEN a.abundance END) AS ctrl_avg
    FROM abundance a
    INNER JOIN samples s ON a.sample_id = s.sample_id
    WHERE a.tax_level = 'genus'
    GROUP BY a.taxonomy
) sub
ORDER BY ABS(diff) DESC;
-- CASE WHEN 是条件判断，相当于 Python 的 if-else
-- ABS() 是求绝对值

生信中的数据库应用¶

1. 存储样本元数据¶

在宏基因组研究中，每个样本都有很多元信息需要管理：

-- 完整的样本元数据表设计
CREATE TABLE sample_metadata (
    sample_id       TEXT PRIMARY KEY,
    project_id      TEXT NOT NULL,       -- 项目编号
    patient_id      TEXT NOT NULL,       -- 患者编号
    group_name      TEXT NOT NULL,       -- 疾病组/对照组
    age             INTEGER,
    gender          TEXT,
    bmi             REAL,
    hba1c           REAL,               -- 糖化血红蛋白（T2D关键指标）
    fasting_glucose REAL,               -- 空腹血糖
    medication      TEXT,               -- 用药情况
    sequencing_platform TEXT,           -- 测序平台（如 Illumina NovaSeq）
    read_length     INTEGER,            -- 测序读长
    total_reads     INTEGER,            -- 总reads数
    collect_date    TEXT,
    notes           TEXT                -- 备注
);

-- 实际应用：快速统计项目基本信息
SELECT group_name,
       COUNT(*) AS n,
       AVG(age) AS mean_age,
       AVG(bmi) AS mean_bmi,
       AVG(hba1c) AS mean_hba1c,
       AVG(total_reads) AS mean_reads
FROM sample_metadata
GROUP BY group_name;

2. 基因注释数据库查询¶

-- KEGG 通路注释表
CREATE TABLE kegg_annotation (
    gene_id     TEXT NOT NULL,
    ko_id       TEXT,                   -- KEGG Orthology 编号（如 K00001）
    pathway_id  TEXT,                   -- 通路编号（如 ko00010）
    pathway_name TEXT,                  -- 通路名称
    description TEXT                    -- 基因功能描述
);

-- 查询碳水化合物代谢相关的基因
SELECT gene_id, ko_id, pathway_name, description
FROM kegg_annotation
WHERE pathway_name LIKE '%Carbohydrate%'
ORDER BY ko_id;

-- 统计每条通路有多少个基因
SELECT pathway_id, pathway_name, COUNT(DISTINCT gene_id) AS gene_count
FROM kegg_annotation
WHERE pathway_id IS NOT NULL
GROUP BY pathway_id, pathway_name
ORDER BY gene_count DESC
LIMIT 20;

3. NCBI / Ensembl / UniProt 数据库结构概览¶

这些大型生物数据库底层都用关系型数据库存储：

数据库	底层存储	核心表	你可能用到的场景
NCBI Taxonomy	关系型	nodes（节点层级）, names（物种名）	物种分类查询、构建分类树
Ensembl	MySQL	gene, transcript, exon, seq_region	基因注释、转录本查询
UniProt	Oracle	entry, feature, dbxref	蛋白质功能注释、结构信息
GO (Gene Ontology)	关系型	term, graph_path, association	基因GO功能富集分析

-- 模拟 NCBI Taxonomy 查询
-- 查找 Bacteroides 属下的所有物种
CREATE TABLE taxonomy (
    tax_id      INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    rank        TEXT,                   -- kingdom/phylum/class/order/family/genus/species
    parent_id   INTEGER                -- 上一级分类的 tax_id
);

SELECT tax_id, name, rank
FROM taxonomy
WHERE parent_id = (SELECT tax_id FROM taxonomy WHERE name = 'Bacteroides' AND rank = 'genus')
  AND rank = 'species';

4. 用 SQL 思维分析物种丰度表¶

很多生信分析的逻辑，其实就是 SQL 的 GROUP BY + JOIN：

-- ============================================================
-- 场景1：找核心菌群（core microbiome）
-- 定义：在 >= 80% 的样本中都出现的属
-- ============================================================
SELECT taxonomy, 
       COUNT(DISTINCT sample_id) AS present_in,
       (SELECT COUNT(DISTINCT sample_id) FROM abundance) AS total_samples,
       ROUND(COUNT(DISTINCT sample_id) * 100.0 / 
             (SELECT COUNT(DISTINCT sample_id) FROM abundance), 1) AS prevalence_pct
FROM abundance
WHERE tax_level = 'genus' AND abundance > 0
GROUP BY taxonomy
HAVING ROUND(COUNT(DISTINCT sample_id) * 100.0 / 
             (SELECT COUNT(DISTINCT sample_id) FROM abundance), 1) >= 80
ORDER BY prevalence_pct DESC;

-- ============================================================
-- 场景2：Alpha多样性简易计算（物种丰富度 = Richness）
-- ============================================================
SELECT sample_id,
       COUNT(DISTINCT taxonomy) AS richness
FROM abundance
WHERE tax_level = 'genus' AND abundance > 0
GROUP BY sample_id
ORDER BY richness DESC;

-- ============================================================
-- 场景3：差异物种初筛
-- 比较T2D vs Control 每个物种的平均丰度
-- ============================================================
SELECT a.taxonomy,
       AVG(CASE WHEN s.group_name = 'T2D' THEN a.abundance END) AS t2d_mean,
       AVG(CASE WHEN s.group_name = 'Control' THEN a.abundance END) AS ctrl_mean,
       AVG(CASE WHEN s.group_name = 'T2D' THEN a.abundance END) -
       AVG(CASE WHEN s.group_name = 'Control' THEN a.abundance END) AS diff
FROM abundance a
JOIN samples s ON a.sample_id = s.sample_id
WHERE a.tax_level = 'genus'
GROUP BY a.taxonomy
ORDER BY ABS(diff) DESC
LIMIT 20;

Python + SQLite 实操¶

SQLite 是 Python 自带的轻量级数据库，不需要额外安装。以下代码可以直接运行。

1. 创建数据库和表¶

import sqlite3

# ============================================================
# 连接数据库（如果不存在会自动创建）
# ":memory:" 表示在内存中创建，程序结束就消失
# 也可以写文件路径如 "my_project.db"
# ============================================================
conn = sqlite3.connect("t2d_metagenome.db")

# 获取游标（cursor）—— 用来执行 SQL 语句的"执行器"
cur = conn.cursor()

# 创建样本元数据表
cur.execute("""
    CREATE TABLE IF NOT EXISTS samples (
        sample_id   TEXT PRIMARY KEY,
        patient_id  TEXT NOT NULL,
        group_name  TEXT NOT NULL,
        age         INTEGER,
        bmi         REAL,
        gender      TEXT
    )
""")
# IF NOT EXISTS：如果表已存在就跳过，不会报错

# 创建物种丰度表
cur.execute("""
    CREATE TABLE IF NOT EXISTS abundance (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        sample_id   TEXT NOT NULL,
        taxonomy    TEXT NOT NULL,
        tax_level   TEXT NOT NULL,
        abundance   REAL NOT NULL,
        read_count  INTEGER
    )
""")

# 提交更改（重要！不提交的话修改不会保存）
conn.commit()
print("数据库和表创建成功！")

2. 插入数据¶

# ============================================================
# 插入样本数据
# 用 ? 占位符防止 SQL 注入攻击（安全写法）
# ============================================================

# 插入单条
cur.execute(
    "INSERT INTO samples VALUES (?, ?, ?, ?, ?, ?)",
    ("S001", "P001", "T2D", 55, 28.3, "M")
)

# 批量插入（推荐，效率高）
sample_data = [
    ("S002", "P002", "Control", 48, 22.1, "F"),
    ("S003", "P003", "T2D", 62, 30.5, "M"),
    ("S004", "P004", "Control", 45, 21.8, "F"),
    ("S005", "P005", "T2D", 58, 27.9, "M"),
    ("S006", "P006", "Control", 50, 23.5, "F"),
]
cur.executemany(
    "INSERT INTO samples VALUES (?, ?, ?, ?, ?, ?)",
    sample_data
)

# 插入丰度数据
abundance_data = [
    ("S001", "Bacteroides",       "genus", 0.25, 15000),
    ("S001", "Prevotella",        "genus", 0.08,  4800),
    ("S001", "Faecalibacterium",  "genus", 0.12,  7200),
    ("S001", "Roseburia",         "genus", 0.06,  3600),
    ("S002", "Bacteroides",       "genus", 0.30, 18000),
    ("S002", "Prevotella",        "genus", 0.15,  9000),
    ("S002", "Faecalibacterium",  "genus", 0.18, 10800),
    ("S002", "Roseburia",         "genus", 0.10,  6000),
    ("S003", "Bacteroides",       "genus", 0.20, 12000),
    ("S003", "Prevotella",        "genus", 0.05,  3000),
    ("S003", "Faecalibacterium",  "genus", 0.08,  4800),
    ("S004", "Bacteroides",       "genus", 0.28, 16800),
    ("S004", "Prevotella",        "genus", 0.18, 10800),
    ("S004", "Faecalibacterium",  "genus", 0.20, 12000),
    ("S005", "Bacteroides",       "genus", 0.22, 13200),
    ("S005", "Prevotella",        "genus", 0.06,  3600),
    ("S006", "Bacteroides",       "genus", 0.32, 19200),
    ("S006", "Prevotella",        "genus", 0.12,  7200),
]
cur.executemany(
    "INSERT INTO abundance (sample_id, taxonomy, tax_level, abundance, read_count) VALUES (?, ?, ?, ?, ?)",
    abundance_data
)

conn.commit()
print(f"插入了 {len(sample_data) + 1} 条样本数据")
print(f"插入了 {len(abundance_data)} 条丰度数据")

3. 查询数据¶

# ============================================================
# 基础查询
# ============================================================

# 查询所有样本
print("=== 所有样本 ===")
cur.execute("SELECT * FROM samples")
rows = cur.fetchall()  # 获取所有结果
for row in rows:
    print(row)
# 输出：('S001', 'P001', 'T2D', 55, 28.3, 'M') ...

# 只获取一条
cur.execute("SELECT * FROM samples WHERE sample_id = ?", ("S001",))
one_row = cur.fetchone()  # 获取第一条
print(f"\nS001样本: {one_row}")

# ============================================================
# 分组统计
# ============================================================
print("\n=== 分组统计 ===")
cur.execute("""
    SELECT group_name,
           COUNT(*) AS n,
           ROUND(AVG(age), 1) AS mean_age,
           ROUND(AVG(bmi), 1) AS mean_bmi
    FROM samples
    GROUP BY group_name
""")
for row in cur.fetchall():
    print(f"组: {row[0]}, 样本数: {row[1]}, 平均年龄: {row[2]}, 平均BMI: {row[3]}")

# ============================================================
# JOIN 查询：合并样本信息和丰度数据
# ============================================================
print("\n=== T2D vs Control 的 Bacteroides 丰度 ===")
cur.execute("""
    SELECT s.group_name,
           ROUND(AVG(a.abundance), 4) AS avg_abundance
    FROM samples s
    INNER JOIN abundance a ON s.sample_id = a.sample_id
    WHERE a.taxonomy = 'Bacteroides'
    GROUP BY s.group_name
""")
for row in cur.fetchall():
    print(f"{row[0]}: 平均丰度 = {row[1]}")

4. pandas 读取 SQL¶

import pandas as pd
import sqlite3

# ============================================================
# pandas 可以直接用 SQL 查询数据库，返回 DataFrame
# 这是生信最常用的方式：SQL 查 + pandas 分析
# ============================================================

conn = sqlite3.connect("t2d_metagenome.db")

# 方法一：读取整张表
df_samples = pd.read_sql("SELECT * FROM samples", conn)
print(df_samples)
print(f"形状: {df_samples.shape}")

# 方法二：用 SQL 做复杂查询，结果直接变 DataFrame
df_abundance = pd.read_sql("""
    SELECT s.sample_id, s.group_name, s.age, s.bmi,
           a.taxonomy, a.abundance
    FROM samples s
    INNER JOIN abundance a ON s.sample_id = a.sample_id
    WHERE a.tax_level = 'genus'
""", conn)

print(f"\n合并后的数据:\n{df_abundance.head(10)}")

# 方法三：拿到 DataFrame 后用 pandas 继续分析
# 例如：做透视表（样本×物种的丰度矩阵）
pivot = df_abundance.pivot_table(
    values="abundance",
    index="sample_id",
    columns="taxonomy",
    fill_value=0
)
print(f"\n丰度矩阵:\n{pivot}")

# 方法四：pandas DataFrame 写回数据库
# 把分析结果存回数据库
result_df = df_abundance.groupby(["group_name", "taxonomy"])["abundance"].mean().reset_index()
result_df.columns = ["group_name", "taxonomy", "mean_abundance"]
result_df.to_sql("group_mean_abundance", conn, if_exists="replace", index=False)
print("\n分组平均丰度已保存到数据库表 group_mean_abundance")

# 关闭连接
conn.close()

常用速查表¶

SQL 关键字速查¶

功能	SQL语法	pandas 等价
查所有列	`SELECT * FROM table`	`df`
查指定列	`SELECT col1, col2 FROM table`	`df[["col1", "col2"]]`
条件筛选	`WHERE col > 10`	`df[df["col"] > 10]`
模糊匹配	`WHERE col LIKE '%abc%'`	`df[df["col"].str.contains("abc")]`
排序	`ORDER BY col DESC`	`df.sort_values("col", ascending=False)`
取前N行	`LIMIT 10`	`df.head(10)`
分组统计	`GROUP BY col`	`df.groupby("col")`
计数	`COUNT(*)`	`.count()` 或 `len()`
求平均	`AVG(col)`	`.mean()`
求和	`SUM(col)`	`.sum()`
最大/最小	`MAX(col)` / `MIN(col)`	`.max()` / `.min()`
去重	`SELECT DISTINCT col`	`df["col"].unique()`
去重计数	`COUNT(DISTINCT col)`	`df["col"].nunique()`
表连接	`JOIN ... ON ...`	`pd.merge(df1, df2, on=...)`
分组后筛选	`HAVING`	`.filter()` 或手动条件
插入数据	`INSERT INTO`	`df.to_sql()`
更新数据	`UPDATE ... SET ...`	`df.loc[条件, "col"] = 值`
删除行	`DELETE FROM ... WHERE ...`	`df.drop()` 或 `df[~条件]`
新建表	`CREATE TABLE`	无直接等价
空值判断	`IS NULL` / `IS NOT NULL`	`.isna()` / `.notna()`

SQL 执行顺序（面试必背）¶

书写顺序：SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

常见错误排查¶

错误	原因	修复
`no such table: xxx`	表不存在	检查表名拼写，先 CREATE TABLE
`UNIQUE constraint failed`	主键重复	检查插入的数据是否有重复ID
`near "xxx": syntax error`	SQL 语法错	检查关键字拼写、引号匹配、逗号
`no such column: xxx`	列名不存在	检查列名拼写
`database is locked`	其他进程占用了数据库	关闭其他连接，或加 timeout

面试怎么答¶

Q1: SQL 的 WHERE 和 HAVING 有什么区别？¶

答：

WHERE 和 HAVING 都是筛选条件，但作用阶段不同：

WHERE 在 GROUP BY 之前执行，筛选的是原始行，不能使用聚合函数
HAVING 在 GROUP BY 之后执行，筛选的是分组后的结果，可以使用聚合函数

举个生信例子：

-- WHERE：先筛选 genus 层级的数据
-- HAVING：再从分组结果中筛选平均丰度 > 0.1 的物种
SELECT taxonomy, AVG(abundance) AS mean_abd
FROM abundance
WHERE tax_level = 'genus'          -- WHERE：筛选原始行
GROUP BY taxonomy
HAVING AVG(abundance) > 0.1;      -- HAVING：筛选分组后的结果

执行顺序口诀：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Q2: INNER JOIN、LEFT JOIN、RIGHT JOIN 有什么区别？¶

答：

三种 JOIN 的区别在于"保留哪边的数据"：

JOIN 类型	保留规则	白话
INNER JOIN	只保留两边都有匹配的行	两张表都有的才要
LEFT JOIN	左表全保留，右表没匹配的填 NULL	以左表为主
RIGHT JOIN	右表全保留，左表没匹配的填 NULL	以右表为主

生信场景举例：我有一张样本元数据表（samples）和一张丰度表（abundance）。如果某些样本没有丰度数据（测序失败了），用 INNER JOIN 会丢掉这些样本，用 LEFT JOIN 会保留这些样本但丰度列显示 NULL。

实际工作中 LEFT JOIN 最常用，因为通常希望保留主表（样本表）的完整信息。

Q3: 如何优化一条慢的 SQL 查询？¶

答：

常用的优化手段：

加索引（INDEX）：对频繁用于 WHERE、JOIN、ORDER BY 的列建索引

CREATE INDEX idx_sample_id ON abundance(sample_id);
CREATE INDEX idx_taxonomy ON abundance(taxonomy);

避免 SELECT *：只查需要的列，减少数据传输量
用 WHERE 提前过滤：先筛选再 JOIN，减少参与运算的数据量
用 EXPLAIN 分析：EXPLAIN QUERY PLAN SELECT ... 查看执行计划
避免在 WHERE 中对列做函数运算：比如 WHERE UPPER(name) = 'ABC' 会导致索引失效

在生信中，最常见的优化是对 sample_id、taxonomy 等高频查询字段建索引。

Q4: 什么是 SQL 注入？如何防止？¶

答：

SQL 注入是一种安全攻击——恶意用户通过拼接 SQL 代码来破坏或窃取数据。

危险写法（字符串拼接）：

# ❌ 不安全：用户输入可能包含恶意SQL
user_input = "'; DROP TABLE samples; --"
sql = f"SELECT * FROM samples WHERE sample_id = '{user_input}'"

安全写法（参数化查询）：

# ✅ 安全：用 ? 占位符，数据库会自动转义
cur.execute("SELECT * FROM samples WHERE sample_id = ?", (user_input,))

面试点：生信虽然不做 Web 开发，但养成参数化查询的习惯很重要——防止数据污染和意外删除。

Q5: 你在项目中用过 SQL 吗？怎么用的？¶

参考答案（结合简历项目）：

在该 T2D 肠道菌群宏基因组项目中，我用 Python 的 sqlite3 模块来管理实验数据：

存储样本元数据：将 120 个样本的临床信息（年龄、BMI、HbA1c、分组等）存入 SQLite 数据库，用 SQL 做快速统计和筛选
合并多源数据：用 JOIN 将样本元数据表、物种丰度表、功能注释表关联起来，一条语句就能得到"T2D 组中 Bacteroides 丰度 > 10% 的样本"
结合 pandas 分析：用 pd.read_sql() 把 SQL 查询结果直接转为 DataFrame，再做下游统计和可视化
批量处理注释数据：KEGG 通路注释有上万条记录，存在 CSV 里打开很慢，导入数据库后用 GROUP BY 快速统计每条通路的基因数量

Q6: 说一下 SQL 的执行顺序¶

答：

SQL 的书写顺序和执行顺序是不同的：

书写：SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
执行：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

白话解释： 1. FROM：先确定要查哪张表 2. WHERE：从表中筛选出符合条件的行 3. GROUP BY：把筛选后的行分组 4. HAVING：从分组结果中再筛选 5. SELECT：选出要显示的列（这时候才处理别名 AS） 6. ORDER BY：对结果排序 7. LIMIT：取前N条

这就是为什么 WHERE 里不能用 SELECT 中的别名——因为 WHERE 执行的时候 SELECT 还没执行。

Q7: 数据库索引是什么？什么时候该建索引？¶

答：

索引（INDEX）就像书的目录——没有目录你要翻遍整本书找内容，有了目录直接跳到对应页码。

-- 建索引
CREATE INDEX idx_taxonomy ON abundance(taxonomy);
-- 有了这个索引后，WHERE taxonomy = 'Bacteroides' 会快很多

该建索引的场景： - 频繁出现在 WHERE 条件中的列 - 用于 JOIN 的关联列（如 sample_id） - 用于 ORDER BY 排序的列

不该建索引的场景： - 数据量很小的表（几百行不需要索引） - 频繁 INSERT/UPDATE 的表（索引会拖慢写入速度） - 值种类很少的列（如 gender 只有 M/F 两种值，索引效果差）

延伸阅读¶

入门教程¶

资源	说明	链接
W3Schools SQL Tutorial	最基础的 SQL 入门，有在线练习	w3schools.com/sql
SQLite 官方文档	SQLite 的权威参考	sqlite.org/docs.html
Python sqlite3 模块文档	Python 自带的 SQLite 接口文档	docs.python.org/3/library/sqlite3.html
SQLBolt	交互式 SQL 练习网站	sqlbolt.com

生信相关¶

资源	说明
Ensembl Public MySQL	Ensembl 提供免费的 MySQL 公共服务器，可以直接 SQL 查询基因注释
UCSC Genome Browser MySQL	UCSC 也提供公共 MySQL 查询接口
BioPython + SQLite	BioPython 的 BioSQL 模块支持将序列数据存入 SQL 数据库

进阶学习方向¶

方向	说明	适用场景
MySQL / PostgreSQL	生产环境的关系型数据库	多人协作项目、大型数据集
SQLAlchemy (Python ORM)	用 Python 对象操作数据库，不用写原始 SQL	大型 Python 项目
数据库设计范式（1NF/2NF/3NF）	如何设计不冗余的表结构	需要自己设计数据库时
窗口函数（WINDOW FUNCTION）	SQL 高级分析功能	复杂排名、移动平均等分析

学习建议：先把上面的 Python + SQLite 实操代码跑一遍，亲手操作过一次比看十遍文档都管用。面试时重点记住 JOIN 的区别、WHERE vs HAVING、执行顺序这三个高频考点。