Master Prompt Engineering: From Basics to Advanced SQL Generation

After a brief recap of setting up a local large model, the article introduces prompt engineering as the practice of crafting short texts that guide an AI model's output, citing OpenAI’s definition.

Prompt Components

A well‑structured prompt consists of five key parts:

Role : Define the persona the model should adopt (e.g., a nutrition professor or a cartoon rabbit) to steer tone and expertise.

Context : Provide background information or conversation history so the model understands the task’s setting.

Instruction/Task : State the concrete action the model must perform, using verb‑first, result‑oriented phrasing.

Example : Supply a concrete sample that serves as a visual anchor for the desired output format.

Output Format : Specify the structure (list, table, code block, dialogue) to ensure the response is immediately usable.

Role Design

The article uses analogies (director assigning actors) and shows three role styles with concrete before/after prompts:

Professional role : "Imagine you are a nutrition professor…" vs a vague request.

Creative role : "Write a poem in Li Bai’s style about AI and the moon" vs a generic poem request.

Mediator role : "You are a tidy cartoon rabbit teaching a 3‑year‑old to brush teeth" vs a plain instruction.

Advanced role usage includes multi‑role collaboration (e.g., "You are both a screenwriter and a critic…") and reverse role training (e.g., "You are a primary‑school student…").

Context Usage

Through dialogue analogies, the article demonstrates how missing context leads to confusion, while providing context yields precise answers. It outlines three ways to embed context:

Single‑turn queries with background (e.g., translating a medical device manual).

Multi‑turn conversations that retain prior information.

Implicit context where the model infers meaning from the query (e.g., "Who is Li Bai?" vs "How to use Li Bai’s skill in a game").

Common pitfalls such as information overload and abrupt topic jumps are highlighted with corrective tips.

Instruction Design

Instructions are compared to employee tasks: vague vs clear. The article presents a verb‑first, outcome‑driven pattern and distinguishes between simple operations (e.g., translate a paragraph) and complex analyses (e.g., critique sociological field reports). It also shows how to bind constraints (e.g., word limits, style requirements).

A table of typical errors (missing main command, mixed commands, overly generic or overly detailed instructions) is summarized as bullet points with before/after examples.

Example and Output Formatting

Examples act as visual anchors; the article contrasts pure text descriptions with image‑plus‑text references, emphasizing that concrete samples reduce ambiguity. It lists three scenarios for using examples: style transfer, complex format specifications, and semantic calibration.

Output format guidelines cover hierarchy, whitespace control, emphasis (bold), color cues, and platform adaptation. The article warns against over‑formatting simple tasks and suggests validation commands (e.g., "ensure JSON is valid").

Practical Prompt: SQL Generation

The second part applies the five components to build a prompt that generates SQL statements from user keywords.

Role Positioning

SQL 语句生成器

Or more descriptively:

您是具有以下能力的专业数据库工程师：
- 准确解析用户业务场景关键词
- 掌握 ANSI SQL 标准及主流数据库方言
- 熟悉数据库设计范式与性能优化原则
- 具备多表关联查询设计能力

Context

Explain typical user challenges (complex joins, function usage, multi‑condition logic, performance concerns).

Instruction/Task

按以下步骤处理用户输入的关键词：
1. 语义解析：识别操作类型、目标表/字段、过滤条件、排序/分组、分页参数。
2. 结构映射：自动关联 JOIN、为 VARCHAR 加引号、保持数值原样、处理日期函数。
3. 逻辑校验：对危险操作给出警示、对超过三表关联的查询建议索引、提示模糊查询的性能影响。

Examples

[输入关键词]
"需要最近三个月上海地区单价超过5000元的电子产品订单，按金额降序排"

[生成SQL]
SELECT
    o.order_id,
    u.user_name,
    p.product_name,
    o.order_amount,
    o.create_time
FROM
    orders o
    JOIN users u ON o.user_id = u.user_id
    JOIN products p ON o.product_id = p.product_id
WHERE
    o.region = '上海'
    AND p.category = '电子产品'
    AND o.order_amount > 5000
    AND o.create_time >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
ORDER BY
    o.order_amount DESC
LIMIT 100;

[说明]
1. 自动关联三表 JOIN
2. 数值条件未加引号
3. 添加 LIMIT 防止结果集过大
4. 时间条件使用函数动态计算

Output Format

[仅返回SQL语句，无需任何额外说明]

The full prompt combines all sections and ends with a confirmation request (“如果你已理解上述要求，请回答是的。”). An example response from DeepSeek is shown via an image:

The article notes that real‑world prompts can be far more complex, involving classification, multi‑answer generation, confidence scoring, and even model fine‑tuning.

References

Prompt Engineering Guide – https://www.promptingguide.ai/zh

Prompt‑Engineering‑Guide (GitHub) – https://github.com/dair-ai/Prompt-Engineering-Guide

OpenAI Prompt Engineering – https://platform.openai.com/docs/guides/prompt-engineering

OpenAI Prompt Generator – https://platform.openai.com/docs/guides/prompt-generation

DeepSeek Model – https://chat.deepseek.com/