02-SQL核心语法¶

-- 创建数据库
CREATE DATABASE school;
USE school;

-- 创建学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男', '女'),
    age INT,
    major VARCHAR(50),
    enrollment_date DATE
);

-- 创建课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    teacher VARCHAR(50),
    credits INT
);

-- 创建成绩表
CREATE TABLE scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    score DECIMAL(5,2),
    exam_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- 插入示例数据
INSERT INTO students VALUES
(1, '张三', '男', 20, '计算机', '2023-09-01'),
(2, '李四', '女', 21, '数学', '2023-09-01'),
(3, '王五', '男', 19, '计算机', '2023-09-01'),
(4, '赵六', '女', 20, '物理', '2023-09-01'),
(5, '钱七', '男', 22, '计算机', '2022-09-01');

INSERT INTO courses VALUES
(1, '数据库原理', '陈老师', 3),
(2, '机器学习', '刘老师', 4),
(3, 'Python编程', '王老师', 3),
(4, '数据结构', '张老师', 4);

INSERT INTO scores VALUES
(1, 1, 1, 85.5, '2024-01-15'),
(2, 1, 2, 92.0, '2024-01-16'),
(3, 1, 3, 88.5, '2024-01-17'),
(4, 2, 1, 78.0, '2024-01-15'),
(5, 2, 2, 95.5, '2024-01-16'),
(6, 3, 1, 82.0, '2024-01-15'),
(7, 3, 3, 90.0, '2024-01-17'),
(8, 4, 2, 87.5, '2024-01-16'),
(9, 5, 1, 91.0, '2024-01-15'),
(10, 5, 4, 89.5, '2024-01-18');

-- 查询所有列
SELECT * FROM students;

-- 查询指定列
SELECT name, age FROM students;

-- 给列起别名
SELECT name AS 姓名, age AS 年龄 FROM students;

-- 去重查询
SELECT DISTINCT major FROM students;

-- 常量列
SELECT name, '2024' AS 学年 FROM students;

-- 比较运算符：=, !=, <>, >, <, >=, <=
SELECT * FROM students WHERE age > 20;
SELECT * FROM students WHERE major = '计算机';

-- 逻辑运算符：AND, OR, NOT
SELECT * FROM students WHERE age > 20 AND major = '计算机';
SELECT * FROM students WHERE age < 20 OR age > 22;
SELECT * FROM students WHERE NOT major = '计算机';

-- IN：在集合中
SELECT * FROM students WHERE major IN ('计算机', '数学');

-- NOT IN：不在集合中
SELECT * FROM students WHERE major NOT IN ('计算机');

-- BETWEEN：范围查询（包含边界）
SELECT * FROM students WHERE age BETWEEN 20 AND 22;

-- LIKE：模糊查询
-- % 匹配任意多个字符
SELECT * FROM students WHERE name LIKE '张%';  -- 姓张的
SELECT * FROM students WHERE name LIKE '%三';  -- 名字以三结尾的
-- _ 匹配单个字符
SELECT * FROM students WHERE name LIKE '张_';  -- 姓张，名字两个字的

-- IS NULL / IS NOT NULL：空值判断
SELECT * FROM students WHERE major IS NULL;

-- 升序排序（默认）
SELECT * FROM students ORDER BY age;
SELECT * FROM students ORDER BY age ASC;

-- 降序排序
SELECT * FROM students ORDER BY age DESC;

-- 多列排序
SELECT * FROM students ORDER BY major, age DESC;
-- 先按专业升序，同专业按年龄降序

-- 只返回前3条
SELECT * FROM students LIMIT 3;

-- 跳过前2条，返回3条（第3、4、5条）
SELECT * FROM students LIMIT 3 OFFSET 2;

-- MySQL语法简写
SELECT * FROM students LIMIT 2, 3;  -- 跳过2条，取3条

-- 实际应用：分页查询
-- 第1页，每页10条
SELECT * FROM students LIMIT 10 OFFSET 0;
-- 第2页，每页10条
SELECT * FROM students LIMIT 10 OFFSET 10;
-- 第n页，每页pageSize条
SELECT * FROM students LIMIT pageSize OFFSET (n-1) * pageSize;

-- COUNT：计数
SELECT COUNT(*) FROM students;                    -- 总记录数
SELECT COUNT(major) FROM students;                -- major非空的记录数
SELECT COUNT(DISTINCT major) FROM students;       -- 不同专业的数量

-- SUM：求和
SELECT SUM(credits) FROM courses;

-- AVG：平均值
SELECT AVG(score) FROM scores;
SELECT AVG(score) AS 平均成绩 FROM scores;

-- MAX / MIN：最大/最小值
SELECT MAX(score) AS 最高分, MIN(score) AS 最低分 FROM scores;

-- 组合使用
SELECT
    COUNT(*) AS 总人数,
    AVG(age) AS 平均年龄,
    MAX(age) AS 最大年龄,
    MIN(age) AS 最小年龄
FROM students;

-- 按专业分组统计
SELECT major, COUNT(*) AS 人数 FROM students GROUP BY major;

-- 按专业分组，统计平均年龄
SELECT major, AVG(age) AS 平均年龄 FROM students GROUP BY major;

-- 多列分组
SELECT major, gender, COUNT(*) AS 人数
FROM students
GROUP BY major, gender;

-- 分组后过滤（HAVING）
-- WHERE：分组前过滤
-- HAVING：分组后过滤

-- 查询人数超过1人的专业
SELECT major, COUNT(*) AS 人数
FROM students
GROUP BY major
HAVING COUNT(*) > 1;

-- WHERE + GROUP BY + HAVING 组合
SELECT major, AVG(score) AS 平均成绩
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
WHERE sc.score IS NOT NULL
GROUP BY major
HAVING AVG(score) > 85;

-- 查询学生及其成绩
SELECT
    s.name,
    c.course_name,
    sc.score
FROM students s
INNER JOIN scores sc ON s.student_id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.course_id;

-- 简写（省略INNER）
SELECT s.name, c.course_name, sc.score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;

-- 加上过滤条件
SELECT s.name, c.course_name, sc.score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE sc.score >= 90;

-- 查询所有学生及其成绩（包括没有成绩的学生）
SELECT
    s.name,
    c.course_name,
    COALESCE(sc.score, 0) AS score  -- 没有成绩显示0
FROM students s
LEFT JOIN scores sc ON s.student_id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.course_id;

-- 查询没有成绩的学生
SELECT s.name
FROM students s
LEFT JOIN scores sc ON s.student_id = sc.student_id
WHERE sc.score_id IS NULL;

-- 查询所有课程及选修的学生（包括没有学生选的课程）
SELECT c.course_name, s.name
FROM students s
RIGHT JOIN scores sc ON s.student_id = sc.student_id
RIGHT JOIN courses c ON sc.course_id = c.course_id;

-- MySQL不支持FULL OUTER JOIN，用UNION模拟
SELECT s.name, c.course_name
FROM students s
LEFT JOIN scores sc ON s.student_id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.course_id

UNION

SELECT s.name, c.course_name
FROM students s
RIGHT JOIN scores sc ON s.student_id = sc.student_id
RIGHT JOIN courses c ON sc.course_id = c.course_id;

-- 查询同一专业的学生对（自连接示例）
SELECT
    s1.name AS 学生1,
    s2.name AS 学生2,
    s1.major AS 专业
FROM students s1
JOIN students s2 ON s1.major = s2.major AND s1.student_id < s2.student_id;

-- 查询成绩高于平均分的学生
SELECT name FROM students
WHERE student_id IN (
    SELECT student_id FROM scores
    WHERE score > (SELECT AVG(score) FROM scores)  -- 子查询：嵌套在另一个查询中
);

-- 查询选了"机器学习"课程的学生
SELECT name FROM students
WHERE student_id IN (
    SELECT student_id FROM scores
    WHERE course_id = (
        SELECT course_id FROM courses
        WHERE course_name = '机器学习'
    )
);

-- 查询每个学生成绩高于自己平均成绩的课程
SELECT s.name, c.course_name, sc.score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE sc.score > (
    SELECT AVG(score) FROM scores
    WHERE student_id = s.student_id  -- 相关子查询
);

-- 查询有成绩记录的学生
SELECT name FROM students s
WHERE EXISTS (
    SELECT 1 FROM scores sc
    WHERE sc.student_id = s.student_id
);

-- 查询没有成绩的学生
SELECT name FROM students s
WHERE NOT EXISTS (
    SELECT 1 FROM scores sc
    WHERE sc.student_id = s.student_id
);

-- 插入单条记录
INSERT INTO students (name, gender, age, major, enrollment_date)
VALUES ('孙八', '女', 21, '化学', '2023-09-01');

-- 插入多条记录
INSERT INTO students (name, gender, age, major, enrollment_date)
VALUES
    ('周九', '男', 20, '生物', '2023-09-01'),
    ('吴十', '女', 19, '化学', '2023-09-01');

-- 插入查询结果
INSERT INTO honor_students (student_id, name, avg_score)
SELECT s.student_id, s.name, AVG(sc.score)
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING AVG(sc.score) >= 90;

-- 更新单条记录
UPDATE students SET age = 21 WHERE student_id = 1;

-- 更新多条记录
UPDATE scores SET score = score + 5 WHERE score < 60;

-- 多列更新
UPDATE students
SET age = age + 1, major = '软件工程'
WHERE student_id = 1;

-- 使用JOIN更新
UPDATE scores sc
JOIN students s ON sc.student_id = s.student_id
SET sc.score = sc.score + 10
WHERE s.major = '计算机';

-- ⚠️ 危险操作：没有WHERE会更新所有记录！
-- UPDATE students SET age = 20;  -- 所有学生年龄都变成20

-- 删除单条记录
DELETE FROM scores WHERE score_id = 1;

-- 删除多条记录
DELETE FROM scores WHERE score < 60;

-- 使用JOIN删除
DELETE sc FROM scores sc
JOIN students s ON sc.student_id = s.student_id
WHERE s.major = '化学';

-- ⚠️ 危险操作：没有WHERE会删除所有记录！
-- DELETE FROM students;  -- 所有学生都被删除！

-- 清空表（更快，不记录日志）
TRUNCATE TABLE temp_logs;

-- 基础建表
CREATE TABLE teachers (
    teacher_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE DEFAULT CURRENT_DATE,
    is_active BOOLEAN DEFAULT TRUE
);

-- 带外键的建表
CREATE TABLE classrooms (
    room_id INT PRIMARY KEY AUTO_INCREMENT,
    room_number VARCHAR(20) NOT NULL,
    capacity INT CHECK (capacity > 0),
    building VARCHAR(50),
    teacher_id INT,
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- 常见数据类型
/*
INT / INTEGER     - 整数
BIGINT            - 大整数
DECIMAL(M,D)      - 精确小数（M总位数，D小数位）
FLOAT / DOUBLE    - 浮点数
VARCHAR(N)        - 变长字符串（最大N字符）
CHAR(N)           - 定长字符串
TEXT              - 长文本
DATE              - 日期（YYYY-MM-DD）
DATETIME          - 日期时间（YYYY-MM-DD HH:MM:SS）
TIMESTAMP         - 时间戳
BOOLEAN / BOOL    - 布尔值
ENUM              - 枚举类型
JSON              - JSON数据（MySQL 5.7+）
*/

-- 添加列
ALTER TABLE students ADD COLUMN phone VARCHAR(20);
ALTER TABLE students ADD COLUMN gpa DECIMAL(3,2) AFTER age;

-- 修改列
ALTER TABLE students MODIFY COLUMN phone VARCHAR(30);
ALTER TABLE students CHANGE COLUMN phone mobile VARCHAR(30);

-- 删除列
ALTER TABLE students DROP COLUMN phone;

-- 添加约束
ALTER TABLE students ADD CONSTRAINT fk_major
    FOREIGN KEY (major_id) REFERENCES majors(id);

-- 添加索引
ALTER TABLE students ADD INDEX idx_name (name);  -- INDEX索引加速查询
CREATE INDEX idx_age ON students(age);

-- 删除索引
ALTER TABLE students DROP INDEX idx_name;
DROP INDEX idx_age ON students;

-- 删除表（不可恢复！）
DROP TABLE temp_data;

-- 如果存在则删除
DROP TABLE IF EXISTS temp_data;

-- 删除数据库
DROP DATABASE test_db;

-- 行号
SELECT
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM scores;

-- 分组行号
SELECT
    s.name,
    c.course_name,
    sc.score,
    ROW_NUMBER() OVER (PARTITION BY c.course_id ORDER BY sc.score DESC) AS course_rank
FROM scores sc
JOIN students s ON sc.student_id = s.student_id
JOIN courses c ON sc.course_id = c.course_id;

-- 累计求和
SELECT
    name,
    score,
    SUM(score) OVER (ORDER BY score) AS cumulative_sum
FROM scores;

-- 移动平均
SELECT
    date,
    score,
    AVG(score) OVER (ORDER BY date ROWS 2 PRECEDING) AS moving_avg
FROM daily_scores;

-- 基础CTE
WITH computer_students AS (
    SELECT * FROM students WHERE major = '计算机'
)
SELECT * FROM computer_students WHERE age > 20;

-- 递归CTE（查询层级数据）
WITH RECURSIVE subordinates AS (
    -- 基础查询：找到直接下属
    SELECT employee_id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id = 1  -- CEO的ID

    UNION ALL

    -- 递归查询：找到下属的下属
    SELECT e.employee_id, e.name, e.manager_id, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.employee_id  -- JOIN连接多个表
)
SELECT * FROM subordinates;

-- 简单CASE
SELECT
    name,
    score,
    CASE
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 70 THEN '中等'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade_level
FROM scores;

-- 在聚合中使用CASE
SELECT
    major,
    COUNT(*) AS total,
    SUM(CASE WHEN gender = '男' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = '女' THEN 1 ELSE 0 END) AS female_count
FROM students
GROUP BY major;

-- 创建实验记录表
CREATE TABLE ml_experiments (
    exp_id INT PRIMARY KEY AUTO_INCREMENT,
    model_name VARCHAR(50),
    dataset VARCHAR(50),
    hyperparams JSON,
    metrics JSON,
    accuracy DECIMAL(5,4),
    loss DECIMAL(10,6),
    training_time INT,  -- 秒
    model_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入实验记录
INSERT INTO ml_experiments (model_name, dataset, hyperparams, metrics, accuracy, loss)
VALUES (
    'ResNet50',
    'ImageNet',
    '{"lr": 0.001, "batch_size": 32, "epochs": 100}',
    '{"train_acc": 0.95, "val_acc": 0.92, "test_acc": 0.91}',
    0.91,
    0.2345
);

-- 查询最佳实验
SELECT * FROM ml_experiments
WHERE model_name = 'ResNet50'
ORDER BY accuracy DESC
LIMIT 5;

-- 按超参数分组统计
SELECT
    JSON_UNQUOTE(JSON_EXTRACT(hyperparams, '$.lr')) AS learning_rate,
    AVG(accuracy) AS avg_accuracy,
    COUNT(*) AS experiment_count
FROM ml_experiments
GROUP BY JSON_EXTRACT(hyperparams, '$.lr')
ORDER BY avg_accuracy DESC;

-- 创建数据集元数据表
CREATE TABLE dataset_samples (
    sample_id INT PRIMARY KEY AUTO_INCREMENT,
    file_path VARCHAR(255) NOT NULL,
    label VARCHAR(50),
    split ENUM('train', 'val', 'test') DEFAULT 'train',
    quality_score DECIMAL(3,2),
    metadata JSON,
    augmented BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查询高质量训练样本
SELECT file_path, label, metadata
FROM dataset_samples
WHERE split = 'train'
  AND quality_score > 0.8
  AND augmented = FALSE
ORDER BY quality_score DESC
LIMIT 1000;

-- 统计各类别样本数
SELECT
    label,
    split,
    COUNT(*) AS sample_count,
    AVG(quality_score) AS avg_quality
FROM dataset_samples
GROUP BY label, split
ORDER BY label, split;

-- 创建预测日志表
CREATE TABLE prediction_logs (
    log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    model_version VARCHAR(20),
    input_hash VARCHAR(64),
    input_preview JSON,
    prediction JSON,
    confidence DECIMAL(5,4),
    latency_ms INT,
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_model_version (model_version),
    INDEX idx_created_at (created_at),
    INDEX idx_user_id (user_id)
);

-- 查询模型性能统计（MySQL 8.0+ 使用窗口函数计算P95延迟）
WITH latency_ranked AS (  -- CTE公共表表达式：临时命名结果集
    SELECT
        model_version,
        latency_ms,
        confidence,
        PERCENT_RANK() OVER (  -- 窗口函数：在结果集上滑动计算
            PARTITION BY model_version ORDER BY latency_ms
        ) AS pct
    FROM prediction_logs
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
)
SELECT
    model_version,
    COUNT(*) AS total_predictions,
    AVG(confidence) AS avg_confidence,
    ROUND(AVG(latency_ms), 1) AS avg_latency,
    MAX(latency_ms) AS max_latency,
    MAX(CASE WHEN pct <= 0.95 THEN latency_ms END) AS p95_latency
FROM latency_ranked
GROUP BY model_version;  -- GROUP BY分组；HAVING过滤分组

-- 查询慢预测
SELECT * FROM prediction_logs
WHERE latency_ms > 1000
ORDER BY latency_ms DESC
LIMIT 100;