从自然语言生成 SQL 查询
学习如何通过提供模式上下文、示例和安全防护措施，提示 LLM 将通俗的英语问题转换为正确的 SQL。
从自然语言生成 SQL 查询 是 CoddyKit 上的免费 Prompt Engineering & LLM Optimization for Developers 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Prompt Engineering & LLM Optimization for Developers 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Prompt Engineering & LLM Optimization for Developers 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
Text-to-SQL Overview
One of the most practical developer uses of LLMs is turning a plain question like How many orders shipped last week? into a runnable SQL query.
Done well, it lets non-experts query data and speeds up everyday analysis.
The Model Needs the Schema
An LLM cannot guess your table and column names. Always include the relevant schema in the prompt so it references real fields.
Schema:
orders(id, customer_id, status, total, created_at)
customers(id, name, country)A Basic Text-to-SQL Prompt
Combine schema, the question, and an instruction to return only SQL.
Given the schema above, write a single
Postgres query. Return only SQL.
Question: total revenue per country last month.Specify the Dialect
SQL dialects differ (Postgres, MySQL, SQLite, BigQuery). State which one you use, or the model may emit functions your database does not support.
Few-Shot Examples Help
Showing one or two question-to-SQL pairs teaches the model your conventions, like how you format dates or name aliases.
Q: customers from Japan
SQL: SELECT * FROM customers WHERE country = 'Japan';
Q: orders over 100
SQL: SELECT * FROM orders WHERE total > 100;Handling Joins
For questions spanning tables, hint at the relationships in the schema (foreign keys) so the model joins correctly instead of guessing.
-- orders.customer_id references customers.id
SELECT c.country, SUM(o.total)
FROM orders o JOIN customers c
ON o.customer_id = c.id
GROUP BY c.country;Guardrail: Read-Only
Never run model-generated SQL with write access. Restrict the connection to SELECT only and reject any query containing DELETE, UPDATE, or DROP.
if (/\b(delete|update|drop|insert)\b/i.test(sql)) {
throw new Error('Only read queries allowed');
}Validate Before Executing
Parse or EXPLAIN the query before running it. This catches syntax errors and lets you reject queries touching tables outside an allowlist.
EXPLAIN SELECT ...; -- check plan, no data fetchedSelf-Correction Loop
If the query errors, feed the database error message back to the model and ask it to fix the SQL. Two or three attempts usually resolve most mistakes.
The query failed with: column "revenue"
does not exist. Fix the SQL.Explaining Results
After running the query, you can ask the model to summarize the rows in plain language, closing the loop from question to readable answer.
Limits to Remember
- Ambiguous questions yield ambiguous SQL.
- Large schemas may not fit in context, retrieve the relevant tables.
- Always verify aggregates on critical reports.
Quick Check
Test your understanding of text-to-SQL.
Recap
Text-to-SQL works by giving the model the schema, dialect, and few-shot examples, then validating output. Always run generated SQL read-only, validate before executing, and use a self-correction loop on errors.
常见问题解答
「从自然语言生成 SQL 查询」课时是免费的吗？
是的 — 「从自然语言生成 SQL 查询」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Prompt Engineering & LLM Optimization for Developers 课程的其余内容，请升级到 CoddyKit PRO。 Prompt Engineering & LLM Optimization for Developers 课程共包含 4 节课。
「从自然语言生成 SQL 查询」这节课中我会学到什么？
学习如何通过提供模式上下文、示例和安全防护措施，提示 LLM 将通俗的英语问题转换为正确的 SQL。 你通过在浏览器中直接运行的动手代码来练习 Prompt Engineering & LLM Optimization for Developers，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Prompt Engineering & LLM Optimization for Developers 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Prompt Engineering & LLM Optimization for Developers 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。
「从自然语言生成 SQL 查询」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Prompt Engineering & LLM Optimization for Developers 课中编写并运行代码吗？
能。每节 Prompt Engineering & LLM Optimization for Developers 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。