SQL 与数据库：
从SELECT 查询到综合项目实战

-- 创建数据库（指定字符集）
CREATE DATABASE school DEFAULT CHARACTER SET utf8mb4;

-- 查看所有数据库
SHOW DATABASES;

-- 切换到目标数据库
USE school;

-- 删除数据库（⚠️ 高危操作）
DROP DATABASE school;

CREATE TABLE student (
    id          INT          PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(50)  NOT NULL,
    age         INT          CHECK (age >= 0 AND age <= 150),
    gender      VARCHAR(10)  DEFAULT '未知',
    email       VARCHAR(100) UNIQUE,
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
DESC student;

-- 查看建表 SQL
SHOW CREATE TABLE student;

-- 添加列
ALTER TABLE student ADD phone VARCHAR(20);

-- 修改列类型
ALTER TABLE student MODIFY name VARCHAR(100);

-- 重命名列
ALTER TABLE student CHANGE phone mobile VARCHAR(20);

-- 删除列
ALTER TABLE student DROP COLUMN mobile;

-- 重命名表
ALTER TABLE student RENAME TO students;

-- 删除表
DROP TABLE student;

-- 单条插入（推荐：指定字段名）
INSERT INTO student (name, age, gender)
VALUES ('张三', 18, '男');

-- 多条插入
INSERT INTO student (name, age, gender)
VALUES
    ('李四', 19, '女'),
    ('王五', 20, '男'),
    ('赵六', 21, '女');

-- 从另一张表复制
INSERT INTO student_backup
SELECT * FROM student WHERE age >= 18;

-- ✔ 正确：带 WHERE 条件
UPDATE student
SET age = 19
WHERE id = 1;

-- 多字段更新
UPDATE student
SET age = 20, gender = '女'
WHERE name = '李四';

-- ❌ 灾难性错误：忘记 WHERE
-- UPDATE student SET age = 20;
-- → 会把所有学生的年龄都改成 20！

-- ✔ 删除指定行
DELETE FROM student WHERE id = 1;

-- 慎用：清空整张表
TRUNCATE TABLE student;

-- 慎用：删除整张表
DROP TABLE student;

SELECT    [DISTINCT]  列1, 列2 AS 别名, 聚合函数(列)
FROM      表名
WHERE     行过滤条件
GROUP BY  分组列
HAVING    分组后过滤
ORDER BY  排序列 [ASC | DESC]
LIMIT     [偏移,] 行数;

-- 查询所有列（生产环境慎用 *）
SELECT * FROM student;

-- 查询指定列（推荐：按需取列）
SELECT name, age FROM student;

-- AS 别名（可省略 AS）
SELECT name AS 姓名, age AS 年龄
FROM student;

-- DISTINCT 去重
SELECT DISTINCT gender FROM student;

-- 比较
WHERE age >= 18

-- AND / OR / NOT
WHERE age >= 18 AND age <= 22
WHERE gender = '男' OR gender = '女'
WHERE NOT (age = 18)

-- BETWEEN：闭区间 [18, 22]
WHERE age BETWEEN 18 AND 22

-- IN：等于列表中任一值
WHERE id IN (1, 2, 3)

-- IS NULL / IS NOT NULL
WHERE email IS NULL
WHERE email IS NOT NULL

-- 两种通配符：
--   %   匹配任意数量字符（含 0 个）
--   _   匹配恰好 1 个字符

SELECT * FROM student
WHERE name LIKE '张%';           -- 张三、张三丰、张飞(非姓张的不行)

SELECT * FROM student
WHERE name LIKE '张_';           -- 张三、张四（恰好 2 字）

SELECT * FROM student
WHERE email LIKE '%@gmail.com';   -- 以 @gmail.com 结尾

-- 包含"小"的：%小%
-- 第 2 个字是"小"的：_小%

-- 转义特殊字符（MySQL 默认 \）
WHERE name LIKE '50\%' ESCAPE '\';     -- 匹配 "50%"

-- 总人数
SELECT COUNT(*) AS total FROM student;

-- 平均年龄
SELECT AVG(age) FROM student;

-- 按性别分组
SELECT gender, COUNT(*) AS cnt, AVG(age) AS avg_age
FROM student
GROUP BY gender;

-- 加上 WHERE 过滤（先过滤再分组）
SELECT class_id, AVG(score) AS avg_score
FROM score
WHERE score >= 60          -- 只看及格的
GROUP BY class_id;

-- 各班平均分超过 80 分的班级
SELECT class_id, AVG(score) AS avg_score
FROM score
GROUP BY class_id
HAVING AVG(score) > 80;   -- 分组后的过滤

-- 单字段排序
SELECT * FROM student ORDER BY age ASC;     -- 升序（默认）
SELECT * FROM student ORDER BY age DESC;    -- 降序

-- 多字段排序：先按 age 降序，再按 id 升序
SELECT * FROM student ORDER BY age DESC, id ASC;

-- LIMIT：取前 N 条
SELECT * FROM student LIMIT 10;

-- LIMIT offset, count：分页（offset 从 0 开始）
-- 跳过前 10 条，取接下来的 10 条（第 2 页）
SELECT * FROM student ORDER BY id LIMIT 10, 10;

-- 分页公式：LIMIT (页码 - 1) * 每页, 每页

-- INNER JOIN：只返回两表都匹配的行
SELECT s.id, s.name, c.class_name
FROM student s
INNER JOIN class c ON s.class_id = c.id;

-- LEFT JOIN：保留左表全部
SELECT s.id, s.name, c.class_name
FROM student s
LEFT JOIN class c ON s.class_id = c.id;

-- 三表 JOIN：查张三的课程成绩
SELECT s.name, co.course_name, sc.score
FROM student s
JOIN score sc ON s.id = sc.student_id
JOIN course co ON sc.course_id = co.id
WHERE s.name = '张三';

-- 1) WHERE 子查询：年龄大于平均年龄
SELECT * FROM student
WHERE age > (SELECT AVG(age) FROM student);

-- 2) IN 子查询：选修了"数据库"的学生
SELECT * FROM student
WHERE id IN (
    SELECT student_id FROM score
    WHERE course_id = (SELECT id FROM course WHERE name = '数据库')
);

-- 3) FROM 子查询：当作临时表
SELECT class_id, avg_score
FROM (SELECT class_id, AVG(score) AS avg_score FROM score GROUP BY class_id) AS t
WHERE avg_score > 80;

-- 4) EXISTS：是否存在
SELECT * FROM course c
WHERE EXISTS (SELECT 1 FROM score WHERE course_id = c.id);

CREATE TABLE student (
    id     INT          PRIMARY KEY AUTO_INCREMENT,
    name   VARCHAR(50)  NOT NULL,
    email  VARCHAR(100) UNIQUE,
    age    INT          CHECK (age >= 0 AND age <= 150),
    gender VARCHAR(10)  DEFAULT '未知',
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES class(id)
        ON DELETE CASCADE
);

-- 外键级联：删班级时自动删其学生（慎用）
-- ON DELETE CASCADE / SET NULL / RESTRICT / NO ACTION

# macOS 安装
brew install mysql

# 启动服务
brew services start mysql

# Linux
sudo systemctl start mysql

# Windows：从官网下载 MSI 安装包

# 登录
mysql -u root -p

# 查看数据库
mysql> SHOW DATABASES;

# 退出
mysql> exit;

-- 创建索引（CREATE 时）
CREATE TABLE student (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(50),
    email VARCHAR(100),
    UNIQUE KEY uk_email (email),      -- 唯一索引
    KEY idx_name (name)               -- 普通索引
);

-- 事后添加 / 删除索引
CREATE INDEX idx_age ON student(age);
ALTER TABLE student ADD INDEX idx_class_name(class_name);
DROP INDEX idx_age ON student;

-- 联合索引（多字段索引，列顺序很重要！）
CREATE INDEX idx_name_age ON student(name, age);

-- 查看索引
SHOW INDEX FROM student;

-- EXPLAIN：分析查询是否用上了索引
EXPLAIN SELECT * FROM student WHERE name = '张三';

START TRANSACTION;   -- 或 BEGIN

UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 假设这里程序崩溃 / 网络中断……
UPDATE account SET balance = balance + 100 WHERE id = 2;

-- 两步都成功
COMMIT;

-- 任一步失败：
-- ROLLBACK;   -- 撤销所有改动

-- 创建视图
CREATE VIEW v_student_score AS
SELECT s.id, s.name, co.course_name, sc.score
FROM student s
JOIN score sc ON s.id = sc.student_id
JOIN course co ON sc.course_id = co.id;

-- 查询视图（和查询表一样）
SELECT * FROM v_student_score WHERE score >= 60;

-- 修改视图
CREATE OR REPLACE VIEW v_student_score AS ...;

-- 删除视图
DROP VIEW v_student_score;

-- 查每个班成绩前 3 名
SELECT * FROM (
    SELECT
        s.class_id,
        s.name,
        sc.score,
        ROW_NUMBER() OVER (PARTITION BY s.class_id ORDER BY sc.score DESC) AS rk
    FROM student s
    JOIN score sc ON s.id = sc.student_id
) t
WHERE rk <= 3;

-- ROW_NUMBER()：连续编号（1, 2, 3, 4）
-- RANK()：       同分同名，下一空位（1, 2, 2, 4）
-- DENSE_RANK()： 同分同名，下一连续（1, 2, 2, 3）

-- 每行的累计求和
SELECT name, score,
       SUM(score) OVER (ORDER BY id) AS cumulative
FROM score;

import pymysql

# 1) 建立连接
conn = pymysql.connect(
    host="localhost",
    user="root",
    password="123456",
    database="school",
    charset="utf8mb4",
)

# 2) 获取游标
with conn.cursor() as cur:
    # 3) 执行 SQL（参数化防注入！）
    cur.execute(
        "SELECT * FROM student WHERE name = %s",
        ("张三",)
    )
    rows = cur.fetchall()
    for row in rows:
        print(row)

    # 4) 插入 + 提交事务
    cur.execute(
        "INSERT INTO student(name, age) VALUES(%s, %s)",
        ("李四", 20)
    )
    conn.commit()

conn.close()

CREATE DATABASE db_name;

CREATE TABLE t (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(50) NOT NULL DEFAULT '',
    age   INT CHECK (age >= 0),
    email VARCHAR(100) UNIQUE,
    FOREIGN KEY (dept_id) REFERENCES dept(id)
);

ALTER TABLE t
    ADD COLUMN phone VARCHAR(20),
    DROP COLUMN phone,
    MODIFY name VARCHAR(100),
    RENAME TO new_t;

DROP TABLE t;
TRUNCATE TABLE t;

INSERT INTO t (a, b) VALUES (1, 'x');

UPDATE t SET a = 2 WHERE id = 1;

DELETE FROM t WHERE a > 0;

SELECT col1, col2, COUNT(*), SUM(col3)
FROM t
WHERE col1 LIKE 'a%' AND col2 BETWEEN 1 AND 10
GROUP BY col1
HAVING COUNT(*) > 3
ORDER BY col2 DESC
LIMIT 10;