SQL 入门必修课：从零搭建数据查询能力

-- 语法：SELECT 你要查的字段1, 字段2 FROM 表名;
-- 注释规则：-- 后面的内容是注释，不会被执行（用于写说明）

-- 1. 先创建模拟学生表（新手直接复制运行）
CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    age INT,
    class VARCHAR(10)
);
-- 2. 插入模拟数据
INSERT INTO student VALUES 
(1, '张三', 18, '高三1班'),
(2, '李四', 17, '高三2班'),
(3, '王五', 18, '高三1班');
-- 3. 查询「所有学生的姓名+年龄」
SELECT name, age FROM student;

SELECT * FROM student;

-- 语法：CREATE TABLE 表名 (
--         字段1 数据类型 约束条件,
--         字段2 数据类型 约束条件
--       );
-- 约束=数据规则（如「姓名不能为空」「学号唯一」）

-- 创建【通用版】大模型对话历史表（支持MySQL/PostgreSQL/SQLite）
CREATE TABLE llm_chat_history (
    chat_id INT PRIMARY KEY AUTOINCREMENT, -- 对话唯一ID（自动增长，避免重复）
    user_id VARCHAR(30) NOT NULL,          -- 用户ID（不能为空）
    prompt TEXT NOT NULL,                  -- 用户提问（长文本用TEXT，支持大段内容）
    answer TEXT NOT NULL,                  -- 大模型回答（长文本用TEXT）
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP -- 创建时间（自动记录当前时间）
);

ALTER TABLE student ADD COLUMN gender VARCHAR(2) DEFAULT '未知';

DROP TABLE IF EXISTS student; -- IF EXISTS：避免表不存在时报错

-- 向大模型对话表插入2条用户提问
INSERT INTO llm_chat_history (user_id, prompt, answer) VALUES
('user_001', '什么是SQL？', 'SQL是数据库查询语言'),
('user_002', '什么是大模型？', '大模型是参数超百亿的AI模型');

-- 把user_001的提问改成「SQL适合做什么？」
UPDATE llm_chat_history 
SET prompt = 'SQL适合做什么？' 
WHERE user_id = 'user_001'; -- 必须加WHERE！

-- 删除user_002的对话记录
DELETE FROM llm_chat_history 
WHERE user_id = 'user_002'; -- 必须加WHERE！

-- 1. 增：插入2条对话
INSERT INTO llm_chat_history (user_id, prompt, answer) VALUES
('user_003', 'Python是什么？', 'Python是编程语言'),
('user_004', 'FastAPI是什么？', 'FastAPI是Python后端框架');
-- 2. 改：把user_003的提问改成「Python适合做AI吗？」
UPDATE llm_chat_history SET prompt = 'Python适合做AI吗？' WHERE user_id = 'user_003';
-- 3. 查：查询所有对话
SELECT user_id, prompt FROM llm_chat_history;
-- 4. 删：删除user_004的对话
DELETE FROM llm_chat_history WHERE user_id = 'user_004';

SELECT name, age FROM student WHERE class = '高三1班' AND age >= 18;

SELECT name, age FROM student ORDER BY age DESC;

SELECT name, class, age FROM student ORDER BY class ASC, age DESC;

SELECT COUNT(chat_id) AS chat_total FROM llm_chat_history;

SELECT user_id, COUNT(chat_id) AS chat_count 
FROM llm_chat_history 
GROUP BY user_id; -- 按user_id分组

-- 1. 给大模型对话表补几条数据
INSERT INTO llm_chat_history (user_id, prompt, answer) VALUES
('user_001', 'SQL难吗？', '0基础2小时就能上手'),
('user_001', '如何学SQL？', '多练在线环境的例子'),
('user_003', '大模型能写SQL吗？', '可以，比如GPT-4');
-- 2. 按用户分组，统计对话次数≥2的用户
SELECT user_id, COUNT(chat_id) AS chat_count
FROM llm_chat_history
GROUP BY user_id
HAVING chat_count >= 2; -- 分组后过滤，必须用HAVING！

-- 1. 给大模型对话表加回答长度字段
ALTER TABLE llm_chat_history ADD COLUMN answer_length INT;
-- 2. 更新回答长度（SQLite用LENGTH()函数计算文本长度）
UPDATE llm_chat_history SET answer_length = LENGTH(answer);
-- 3. 统计每个用户的平均回答长度（保留1位小数）
SELECT user_id, ROUND(AVG(answer_length), 1) AS avg_answer_len
FROM llm_chat_history
GROUP BY user_id;

-- 1. 创建学生成绩表（关联学生表的id字段）
CREATE TABLE student_score (
    score_id INT PRIMARY KEY AUTOINCREMENT,
    student_id INT NOT NULL, -- 关联学生表的id
    subject VARCHAR(20) NOT NULL,
    score INT NOT NULL
);
-- 2. 插入成绩数据（student_id对应学生表的id）
INSERT INTO student_score VALUES
(1, 1, '数学', 95),
(2, 2, '数学', 88),
(3, 3, '数学', 92),
(4, 1, '英语', 85);

SELECT 表1.字段, 表2.字段
FROM 表1
INNER JOIN 表2 ON 表1.关联字段 = 表2.关联字段;
-- 关键：ON后面是「两张表的关联条件」

SELECT student.name, student.class, student_score.score
FROM student
INNER JOIN student_score ON student.id = student_score.student_id
WHERE student_score.subject = '数学'; -- 可叠加WHERE过滤科目

SELECT student.name, student_score.subject, student_score.score
FROM student -- 左表：所有学生
LEFT JOIN student_score ON student.id = student_score.student_id
WHERE student_score.subject = '英语'; -- 过滤科目

-- ❌ 错误示例：忘记写ON条件
SELECT student.name, student_score.score FROM student JOIN student_score;

-- 1. 先创建用户表
CREATE TABLE user (
    user_id VARCHAR(30) PRIMARY KEY,
    age INT NOT NULL
);
-- 2. 插入用户数据
INSERT INTO user VALUES ('user_001', 20), ('user_003', 17);
-- 3. 连接用户表和对话表，查询18岁以上用户的对话
SELECT user.user_id, user.age, llm_chat_history.prompt
FROM user
INNER JOIN llm_chat_history ON user.user_id = llm_chat_history.user_id
WHERE user.age >= 18;