一、汇总报表：group by

想知道「谁留了几条、最早最晚分别是哪天」，一条 SQL 就够。 select name, count(*), min(created), max(created) from msg group by name。 group by 是「按这一列分组」，分完组才能对每组算 count、sum、min、max。 不分组直接算，算的是全表。

演示第二段那张表：小王 3 条（11-01 09:10 到 11-02 09:00），王工 1 条，小李 1 条。 写报表最容易犯的错：把没分组的列直接 select 出来。 比如 select name, body, count(*) from msg group by name——body 没参与分组， 一个名字对应三条 body，你说该显示哪条？ 这种写法在 MySQL 的宽松模式下会「随便挑一行」给你，看起来能跑，结果不可信。 在严格模式或 PostgreSQL 上直接报错。别依赖「随便挑一行」这种运气。

要显示某一行的内容，得先用 min()／max() 之类的聚合函数明确说「我要哪条」，或者用子查询。 含糊的写法迟早给你一个含糊的结果。

二、留言板：三件事

整个留言板就三件事：一条 select 拿列表、一个模板显示、一个 insert 存新的。其余都是装饰。 演示第三段跑了四步：GET / 返回 200 且页面里有 5 个 li；POST /add 返回 200 并跳到 /； 再看列表变成 6 个 li，最新一条是「我也来一条」；空名字返回 400。 加完跳回列表页用的是 redirect。这就是 9.3 篇讲的 POST-重定向-GET，避免刷新重复提交。

注意模板里用 {{ }} 输出内容，不是直接拼字符串。 这一点下一节会解释为什么这么重要。 还有一个细节：列表要按时间倒序，SQL 里写 order by id desc。 用 id 不用 created，因为 id 是严格递增的，同一秒内的两条也不会乱。 服务端校验也不能省：名字和内容都不能空。演示里空名字返回 400。 这个 400 是服务端给的，前端那个 required 属性挡不住直接发请求的人。

三、XSS 真演示

有人在留言里写 <script>alert(document.cookie)</script>。 转义之后，页面里的源码是 <script>alert(document.cookie)</script>。 浏览器把它当一行普通文字显示，不会执行。 不转义的话，它会真的执行——每个打开这个页面的人都会中招。 那段脚本能读他们的 Cookie， 然后用他们的身份发请求。

所以有一条硬规矩：凡是从数据库或用户那里拿来的内容，一律用模板变量输出，不要手拼 HTML 字符串。 Jinja2 默认帮你转义（9.2 篇讲过）。 真正危险的是「为了显示富文本而关掉转义」——写 |safe 就等于把这道门打开。 要显示用户写的富文本（比如带格式的评论），正确做法是先过滤再放行： 用一个白名单库（如 bleach）只允许少数几个标签，其余全转义。不要图省事直接 |safe。

四、分页

演示第五段：总条数 6，每页 2 条，三页各 2 条。 select ... limit 2 offset 0 拿第 1 页，offset 2 拿第 2 页，offset 4 拿第 3 页。 分页就是这两个数，没有别的魔法。 offset 的算法是 (页码 - 1) × 每页条数。这一句记住，翻页就不会错。

但 offset 越大越慢——数据库得先把前面那些行数过去，再丢掉。 翻到第 10000 页，它要数 20000 行才开始给你。 真实系统翻到很后面会改另一种做法： 从上一页最后那个 id 往后取（where id < 上一页最后一个 id order by id desc limit 20）。 这种写法不管翻到第几页都一样快，因为 id 上有索引，直接定位。 代价是不能「跳到第 500 页」——只能一页页往下走。

还有一个必须做的： 先 count(*) 拿总数，才能算出有几页、要不要显示「下一页」。 这个查询也要走一次数据库， 别忘了。

五、动手 Demo：从一条 SQL 到一个留言板

先造 5 条留言，跑一条汇总 SQL 看分组报表。 然后把留言板真跑起来：GET 列表、POST 加一条、空名字拿 400。 最后演示转义和不转义的差别，以及分页。

import logging, os, sqlite3, tempfile, threading
from flask import Flask, redirect, render_template_string, request, url_for
from werkzeug.serving import make_server
import requests

logging.getLogger('werkzeug').setLevel(logging.ERROR)
d = tempfile.mkdtemp()
DB = os.path.join(d, 'board.db')
con = sqlite3.connect(DB, check_same_thread=False)
con.execute('''create table msg (
    id integer primary key autoincrement,
    name text not null,
    body text not null,
    created text not null default (datetime('now','localtime')))''')
for n, b, t in (('小王', '这批螺栓的规格确认一下', '2026-11-01 09:10'),
                ('小李', '图纸已更新，见附件', '2026-11-01 10:20'),
                ('小王', '收到，我看看', '2026-11-01 11:00'),
                ('王工', '明天上午现场碰一下', '2026-11-02 08:30'),
                ('小王', '现场见', '2026-11-02 09:00')):
    con.execute('insert into msg (name, body, created) values (?,?,?)', (n, b, t))
con.commit()

print('① 先造一批数据')
print('  %d 条留言，%d 个人' % (con.execute('select count(*) from msg').fetchone()[0],
                            con.execute('select count(distinct name) from msg').fetchone()[0]))

print('② 汇总：谁留了几条')
print('| 谁 | 几条 | 最早 | 最晚 |')
print('|---|---:|---:|---:|')
for n, c, a, b in con.execute('select name, count(*), min(created), max(created)'
                             ' from msg group by name order by count(*) desc'):
    print('| %s | %d | %s | %s |' % (n, c, a[5:16], b[5:16]))
print('  group by 是「按这一列分组」，分完组才能算 count、sum、min、max。')

PAGE = '''<ul>
{% for r in rows %}
<li>{{ r['name'] }}：{{ r['body'] }}</li>
{% endfor %}
</ul>'''

app = Flask(__name__)
app.secret_key = 'demo-only'

@app.route('/')
def index():
    rows = con.execute('select id, name, body from msg order by id desc').fetchall()
    return render_template_string(PAGE, rows=rows)

@app.route('/add', methods=['POST'])
def add():
    name = (request.form.get('name') or '').strip()
    body = (request.form.get('body') or '').strip()
    if not name or not body:
        return '名字和内容都不能空', 400
    con.execute('insert into msg (name, body) values (?,?)', (name, body))
    con.commit()
    return redirect(url_for('index'))

srv = make_server('127.0.0.1', 0, app)
B = 'http://127.0.0.1:%d' % srv.server_port
threading.Thread(target=srv.serve_forever, daemon=True).start()

print('③ 一个真能用的留言板')
r = requests.get(B + '/', timeout=5)
print('  GET / → %d，页面里有 %d 个 <li>' % (r.status_code, r.text.count('<li>')))
r = requests.post(B + '/add', data={'name': '我', 'body': '我也来一条'}, timeout=5)
print('  POST /add → %d，跳到 /' % r.status_code)
r = requests.get(B + '/', timeout=5)
print('  再看列表 → %d 个 <li>，最新一条是「%s」'
      % (r.text.count('<li>'), r.text.split('<li>')[1].split('：')[1].split('</li>')[0]))
r = requests.post(B + '/add', data={'name': '', 'body': 'x'}, timeout=5)
print('  空名字 → %d %s' % (r.status_code, r.text))
print('  整个留言板就三件事：一条 select 拿列表，一个模板显示，一个 insert 存新的。')

print('④ XSS 真演示：如果不转义会怎样')
evil = '<script>alert(document.cookie)</script>'
requests.post(B + '/add', data={'name': '坏人', 'body': evil}, timeout=5)
r = requests.get(B + '/', timeout=5)
line = [x for x in r.text.splitlines() if 'script' in x][0]
print('  有人在留言里写：%s' % evil)
print('  转义后页面里是：%s' % line.strip())
print('  转义之后，这段东西在浏览器眼里就是一行普通文字。')

print('⑤ 分页')
total = con.execute('select count(*) from msg').fetchone()[0]
for p in (1, 2, 3):
    rows = con.execute('select id, name, body from msg order by id desc limit 2 offset ?',
                       ((p - 1) * 2,)).fetchall()
    print('    第 %d 页（limit 2 offset %d）：%d 条' % (p, (p - 1) * 2, len(rows)))
print('  总条数 %d，每页 2 条，一共 %d 页。分页就是 limit 加 offset 这两个数，没有别的魔法。'
      % (total, (total + 1) // 2))
srv.shutdown()
con.close()

实跑输出：

--------------------------------------------------------------------
从一条 SQL 到一个留言板
--------------------------------------------------------------------

  ① 先造一批数据
  --------------------------------------------------------------------
    5 条留言，3 个人

  ② 汇总：谁留了几条
  --------------------------------------------------------------------

| 谁 | 几条 | 最早 | 最晚 |
|---|---:|---:|---:|
| 小王 | 3 | 11-01 09:10 | 11-02 09:00 |
| 王工 | 1 | 11-02 08:30 | 11-02 08:30 |
| 小李 | 1 | 11-01 10:20 | 11-01 10:20 |

  group by 是「按这一列分组」，分完组才能算 count、sum、min、max。这张表就是典型的汇总报表。
  写报表最容易犯的错：把没分组的列直接 select 出来。
  那样的结果在 MySQL 里可能给你随便挑一行，在严格的库里直接报错。

  ③ 一个真能用的留言板
  --------------------------------------------------------------------
    GET / → 200，页面里有 5 个 <li>
    POST /add → 200，跳到 /
    再看列表 → 6 个 <li>，最新一条是「我也来一条」
    空名字 → 400 名字和内容都不能空
  整个留言板就三件事：一条 select 拿列表，一个模板显示，一个 insert 存新的。
  加完跳回列表页（redirect），避免刷新重复提交。
  注意模板里用 {{ }} 输出内容，不是直接拼字符串。
  前面试过，Jinja2 会自动转义，所以别人在留言里写 <script> 也伤不到人。

  ④ XSS 真演示：如果不转义会怎样
  --------------------------------------------------------------------
    有人在留言里写：<script>alert(document.cookie)</script>
    转义后页面里是：&lt;script&gt;alert(document.cookie)&lt;/script&gt;
  转义之后，这段东西在浏览器眼里就是一行普通文字。
  不转义的话，它会真的执行——每个打开这个页面的人都会中招。
  所以有一条硬规矩：凡是从数据库或用户那里拿来的内容，一律用模板变量输出，不要手拼 HTML 字符串。

  ⑤ 分页
  --------------------------------------------------------------------
    第 1 页（limit 2 offset 0）：2 条
    第 2 页（limit 2 offset 2）：2 条
    第 3 页（limit 2 offset 4）：2 条
  总条数 6，每页 2 条，一共 3 页。分页就是 limit 加 offset 这两个数，没有别的魔法。
  offset 越大越慢——数据库得先数过前面那些行。
  真实系统翻到几万页之后会改用「从上一页最后那个 id 往后取」，这样不管翻到第几页都一样快。

第二段那张汇总表是 group by 的典型用法。注意 order by count(*) desc，按条数从多到少排。 第三段那个 check_same_thread=False 是必须写的。 Flask 处理请求用的线程跟你建连接的不是同一个，不加这个参数会报 ProgrammingError。 第三段四步连起来看：5 个 li → POST 加一条 → 6 个 li → 空名字 400。整个留言板就这么点东西。 第四段最有说服力：同一个字符串，转义之后在页面源码里是 <script>，浏览器把它当文字显示。 不转义就会真执行。 第五段那个 offset 是 (页码 - 1) × 每页条数。记住这个算式，翻页就不会错。

30 秒实验

1. 把演示第二段的 order by count(*) desc 改成 order by name，看表的顺序怎么变。
2. 在演示第三段的 PAGE 模板里用 {{ r['body']|safe }} 替换 {{ r['body'] }}， 再跑一次 XSS 那段，看输出变成什么——看完记得改回来。
3. 把演示第五段的每页 2 条改成 4 条，重新算需要几页。

常见误区

1. 「group by 之后可以随便 select 别的列。 」那些列没参与分组，一个组里对应多个值，数据库只能随便挑一个给你。 结果不可信。
2. 「模板里 {{ }} 和手拼 HTML 一样。」不一样。 {{ }} 会转义，手拼不会。 手拼等于把 XSS 的门打开。
3. 「分页写在 SQL 里就行了，翻到第几页都一样。 」offset 越大越慢， 数据库得先数过前面那些行。 翻到很后面要换成「从上一页最后那个 id 往后取」。

下一篇加最后一层：怎么让网站记住「你是谁」，以及登录这件事的完整形状。

• ☐ （本篇完，下一篇 12.3 Cookie、Session 与管理员登录）

相关笔记

• 数据库与建表
• Cookie、Session 与管理员登录
• 数据库基础 · 索引
• 🐍 超图解 Python 程序设计入门 · 索引