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 查询？

答：

常用的优化手段：

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

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

2. 避免 SELECT *：只查需要的列，减少数据传输量
3. 用 WHERE 提前过滤：先筛选再 JOIN，减少参与运算的数据量
4. 用 EXPLAIN 分析：EXPLAIN QUERY PLAN SELECT ... 查看执行计划
5. 避免在 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 模块来管理实验数据：

1. 存储样本元数据：将 120 个样本的临床信息（年龄、BMI、HbA1c、分组等）存入 SQLite 数据库，用 SQL 做快速统计和筛选
2. 合并多源数据：用 JOIN 将样本元数据表、物种丰度表、功能注释表关联起来，一条语句就能得到"T2D 组中 Bacteroides 丰度 > 10% 的样本"
3. 结合 pandas 分析：用 pd.read_sql() 把 SQL 查询结果直接转为 DataFrame，再做下游统计和可视化
4. 批量处理注释数据：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、执行顺序这三个高频考点。