1
-- 投影与别名
2
SELECT username AS name, age FROM users;
3
SELECT * FROM users;                       -- 查所有列（生产慎用）
4

5
-- 筛选 WHERE
6
SELECT * FROM users WHERE age > 18;
7
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
8
SELECT * FROM users WHERE username IN ('tom', 'amy');
9
SELECT * FROM users WHERE username LIKE 't%';     -- 模糊（% 任意多，_ 单个）
10
SELECT * FROM users WHERE email IS NULL;          -- 空值判断用 IS NULL
11
SELECT * FROM users WHERE age > 18 AND balance > 100;
12

13
-- 去重
14
SELECT DISTINCT city FROM users;
15

16
-- 排序（ASC 升序默认，DESC 降序）
17
SELECT * FROM users ORDER BY age DESC, username ASC;
18

19
-- 分页（LIMIT 偏移量, 条数）
20
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;   -- 跳过20取10条
21
SELECT * FROM users LIMIT 20, 10;                     -- 等价写法

1
-- 聚合函数
2
SELECT COUNT(*) FROM users;                    -- 计数
3
SELECT AVG(age), MAX(balance), MIN(balance), SUM(balance) FROM users;
4

5
-- 分组 GROUP BY
6
SELECT city, COUNT(*) AS num, AVG(age) AS avg_age
7
FROM users
8
GROUP BY city;
9

10
-- 分组后筛选 HAVING（WHERE 不能用聚合函数，HAVING 可以）
11
SELECT city, COUNT(*) AS num
12
FROM users
13
GROUP BY city
14
HAVING COUNT(*) > 10;

1
-- 假设有 orders 表：orders(id, user_id, amount)
2

3
-- 内连接：只返回两表都匹配的行
4
SELECT u.username, o.amount
5
FROM users u
6
INNER JOIN orders o ON u.id = o.user_id;
7

8
-- 左连接：返回左表全部 + 右表匹配（无匹配为 NULL）
9
SELECT u.username, o.amount
10
FROM users u
11
LEFT JOIN orders o ON u.id = o.user_id;
12

13
-- 右连接：返回右表全部
14
SELECT u.username, o.amount
15
FROM users u
16
RIGHT JOIN orders o ON u.id = o.user_id;
17

18
-- 自连接（表和自己连）
19
SELECT e.name AS 员工, m.name AS 上级
20
FROM employees e
21
LEFT JOIN employees m ON e.manager_id = m.id;

1
连接类型示意：
2
内连接 INNER JOIN     A ∩ B          （交集）
3
左连接 LEFT JOIN      A 全部 + B匹配  （A 为主）
4
右连接 RIGHT JOIN     B 全部 + A匹配  （B 为主）
5
全连接 FULL JOIN      A ∪ B          （并集，MySQL 不支持，用 UNION 模拟）

1
-- 子查询：嵌套在另一个查询里
2
SELECT username FROM users
3
WHERE age > (SELECT AVG(age) FROM users);      -- 大于平均年龄
4

5
SELECT * FROM users
6
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
7

8
-- 窗口函数（MySQL 8.0+）：不合并行，做组内计算
9
-- 每个城市内按余额排名
10
SELECT username, city, balance,
11
    RANK() OVER (PARTITION BY city ORDER BY balance DESC) AS rk,
12
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY balance DESC) AS rn,
13
    SUM(balance) OVER (PARTITION BY city) AS city_total
14
FROM users;

1
-- 创建索引
2
CREATE INDEX idx_username ON users(username);         -- 普通索引
3
CREATE UNIQUE INDEX idx_email ON users(email);        -- 唯一索引
4
CREATE INDEX idx_city_age ON users(city, age);        -- 复合索引
5
CREATE INDEX idx_name ON users(username(10));         -- 前缀索引（长字符串）
6

7
-- 查看 / 删除
8
SHOW INDEX FROM users;
9
DROP INDEX idx_username ON users;

1
-- 分析查询是否用到索引
2
EXPLAIN SELECT * FROM users WHERE username = 'tom';

1
✅ 该建索引的场景：
2
- WHERE、JOIN、ORDER BY、GROUP BY 频繁用到的列
3
- 区分度高的列（如用户名、订单号）
4
- 复合索引遵循"最左前缀"原则
5

6
❌ 索引失效的场景：
7
- 对索引列做运算/函数：WHERE YEAR(created_at)=2026（改用范围）
8
- 隐式类型转换：WHERE phone = 138xxx（phone 是字符串却传数字）
9
- LIKE '%xxx' 以通配符开头
10
- 复合索引不满足最左前缀
11
- OR 连接非索引列

1
# ETL：Extract（提取）→ Transform（转换）→ Load（加载）
2
def etl_example():
3
    # Extract：从源库读取
4
    with conn.cursor() as cursor:
5
        cursor.execute("SELECT id, username, balance FROM users")
6
        rows = cursor.fetchall()
7

8
    # Transform：清洗转换
9
    cleaned = [
10
        {"id": r["id"], "name": r["username"].strip().upper(),
11
         "level": "VIP" if r["balance"] > 1000 else "普通"}
12
        for r in rows if r["balance"] is not None
13
    ]
14

15
    # Load：写入目标表
16
    with conn.cursor() as cursor:
17
        cursor.executemany(
18
            "INSERT INTO user_summary (id, name, level) VALUES (%s,%s,%s)",
19
            [(c["id"], c["name"], c["level"]) for c in cleaned],
20
        )
21
        conn.commit()