🐍 Python Day45：SQL 基础 — 数据库入门

🕐 预计用时：2-3 小时 ｜ 🎯 目标：掌握数据库概念、SQL 语法、CRUD 操作

📖 今日目录

1. 什么是数据库？
2. 关系型数据库 vs 非关系型数据库
3. SQL 基础概念
4. 创建数据库和表 (CREATE)
5. 插入数据 (INSERT)
6. 查询数据 (SELECT)
7. 条件查询 (WHERE)
8. 排序与分页 (ORDER BY / LIMIT)
9. 聚合函数 (COUNT/SUM/AVG/MAX/MIN)
10. 分组查询 (GROUP BY / HAVING)
11. 更新与删除 (UPDATE / DELETE)
12. 表关联 (JOIN)
13. 今日小结

1. 什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。Excel 能存数据，但当数据量大到百万、千万行时，就需要数据库了。

常见数据库

2. 关系型 vs 非关系型

# 关系型数据库（SQL）：数据以"表"的形式存储

# 表1: students（学生表）
# +----+--------+-----+--------+
# | id | name   | age | class  |
# +----+--------+-----+--------+
# | 1  | 张三   | 20  | 一班   |
# | 2  | 李四   | 22  | 二班   |
# | 3  | 王五   | 21  | 一班   |
# +----+--------+-----+--------+

# 表2: scores（成绩表）
# +----+------------+---------+-------+
# | id | student_id | subject | score |
# +----+------------+---------+-------+
# | 1  | 1          | 数学    | 95    |
# | 2  | 1          | 英语    | 88    |
# | 3  | 2          | 数学    | 80    |
# +----+------------+---------+-------+

# 两个表通过 student_id 关联 → "关系型"数据库

3. SQL 基础概念

-- SQL（Structured Query Language）是操作数据库的语言
-- 不区分大小写，但习惯上关键字用大写

-- 常用数据类型
-- INTEGER / INT    整数
-- REAL / FLOAT     浮点数
-- TEXT / VARCHAR    字符串
-- DATE / DATETIME  日期时间
-- BOOLEAN          布尔值

-- 常用约束
-- PRIMARY KEY      主键（唯一标识，不重复不为空）
-- NOT NULL         不允许为空
-- UNIQUE           唯一值
-- DEFAULT          默认值
-- FOREIGN KEY      外键（关联其他表）

4. 创建数据库和表 (CREATE)

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

-- 创建学生表
CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,  -- 自增主键
    name TEXT NOT NULL,                      -- 姓名（不能为空）
    age INTEGER CHECK(age > 0),             -- 年龄（必须大于0）
    gender TEXT DEFAULT '未知',              -- 性别（默认值）
    class TEXT NOT NULL,                     -- 班级
    email TEXT UNIQUE,                       -- 邮箱（唯一）
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP  -- 创建时间
);

-- 创建成绩表
CREATE TABLE scores (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,             -- 学生ID（外键）
    subject TEXT NOT NULL,                   -- 科目
    score REAL CHECK(score >= 0 AND score <= 100),  -- 分数范围
    FOREIGN KEY (student_id) REFERENCES students(id) -- 外键关联
);

-- SQLite 中创建表（Python 内置）
import sqlite3

conn = sqlite3.connect("school.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    class TEXT NOT NULL
)
""")

conn.commit()
conn.close()

5. 插入数据 (INSERT)

-- 插入单条数据
INSERT INTO students (name, age, gender, class)
VALUES ('张三', 20, '男', '一班');

-- 插入多条数据
INSERT INTO students (name, age, gender, class) VALUES
('李四', 22, '女', '二班'),
('王五', 21, '男', '一班'),
('赵六', 23, '男', '二班'),
('钱七', 20, '女', '一班');

-- 插入成绩数据
INSERT INTO scores (student_id, subject, score) VALUES
(1, '数学', 95), (1, '英语', 88), (1, '语文', 90),
(2, '数学', 80), (2, '英语', 92), (2, '语文', 85),
(3, '数学', 78), (3, '英语', 76), (3, '语文', 82);

6. 查询数据 (SELECT)

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

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

-- 去重
SELECT DISTINCT class FROM students;

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

-- 计算列
SELECT name, age, age + 5 AS "五年后年龄" FROM students;

-- 限制返回行数
SELECT * FROM students LIMIT 3;

7. 条件查询 (WHERE)

-- 比较运算
SELECT * FROM students WHERE age > 20;
SELECT * FROM students WHERE class = '一班';
SELECT * FROM students WHERE age != 22;

-- 逻辑运算
SELECT * FROM students WHERE age > 20 AND gender = '男';
SELECT * FROM students WHERE class = '一班' OR class = '二班';
SELECT * FROM students WHERE NOT class = '二班';

-- 范围
SELECT * FROM students WHERE age BETWEEN 20 AND 22;

-- 集合
SELECT * FROM students WHERE class IN ('一班', '二班');

-- 模糊匹配
SELECT * FROM students WHERE name LIKE '张%';     -- 以"张"开头
SELECT * FROM students WHERE name LIKE '%三%';    -- 包含"三"
SELECT * FROM students WHERE name LIKE '___';     -- 三个字的名字

-- 空值判断
SELECT * FROM students WHERE email IS NULL;
SELECT * FROM students WHERE email IS NOT NULL;

LIKE 通配符

8. 排序与分页

-- 排序
SELECT * FROM students ORDER BY age ASC;           -- 升序
SELECT * FROM students ORDER BY age DESC;          -- 降序
SELECT * FROM students ORDER BY class ASC, age DESC;  -- 先按班级升序，再按年龄降序

-- 分页（第2页，每页3条）
SELECT * FROM students LIMIT 3 OFFSET 3;  -- 跳过前3条，取3条
-- 或
SELECT * FROM students LIMIT 3, 3;        -- 跳过3条，取3条

-- 分页公式：LIMIT 每页数量 OFFSET (页码-1) * 每页数量

9. 聚合函数

-- COUNT: 计数
SELECT COUNT(*) AS 总人数 FROM students;
SELECT COUNT(DISTINCT class) AS 班级数 FROM students;

-- SUM: 求和
SELECT SUM(score) AS 总分 FROM scores WHERE student_id = 1;

-- AVG: 平均值
SELECT AVG(score) AS 平均分 FROM scores WHERE subject = '数学';

-- 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;

10. 分组查询 (GROUP BY / HAVING)

-- GROUP BY: 按某列分组
SELECT subject, COUNT(*) AS 人数, AVG(score) AS 平均分
FROM scores
GROUP BY subject;

-- 结果:
-- 数学 | 3 | 84.33
-- 英语 | 3 | 85.33
-- 语文 | 3 | 85.67

-- 按学生分组，计算每人总分
SELECT student_id, SUM(score) AS 总分, AVG(score) AS 平均分
FROM scores
GROUP BY student_id
ORDER BY 总分 DESC;

-- HAVING: 对分组结果过滤（WHERE 过滤行，HAVING 过滤组）
SELECT student_id, AVG(score) AS 平均分
FROM scores
GROUP BY student_id
HAVING 平均分 > 85;

-- 完整查询顺序:
-- SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

💡 WHERE vs HAVING：
• WHERE：在分组前过滤行
• HAVING：在分组后过滤组
• WHERE 不能用聚合函数，HAVING 可以

11. 更新与删除

-- 更新数据
UPDATE students SET age = 21 WHERE name = '张三';
UPDATE students SET class = '三班', age = 24 WHERE id = 4;

-- ⚠️ 不加 WHERE 会更新所有行！
-- UPDATE students SET age = 20;  -- 所有人的年龄都变成 20！

-- 删除数据
DELETE FROM students WHERE name = '赵六';

-- ⚠️ 不加 WHERE 会删除所有行！
-- DELETE FROM students;  -- 清空整个表！

-- 删除表
DROP TABLE IF EXISTS scores;

-- 清空表（保留结构）
DELETE FROM students;
-- 或
-- TRUNCATE TABLE students;  -- SQLite 不支持

⚠️ UPDATE 和 DELETE 必须加 WHERE！不加 WHERE 的后果是灾难性的——会修改/删除整个表的数据。操作前先用 SELECT 确认 WHERE 条件正确。

12. 表关联 (JOIN)

-- INNER JOIN: 内连接（只返回两个表都匹配的数据）
SELECT students.name, scores.subject, scores.score
FROM students
INNER JOIN scores ON students.id = scores.student_id;

-- 结果:
-- 张三 | 数学 | 95
-- 张三 | 英语 | 88
-- 张三 | 语文 | 90
-- 李四 | 数学 | 80
-- ...

-- LEFT JOIN: 左连接（返回左表所有数据，右表不匹配的为 NULL）
SELECT students.name, scores.subject, scores.score
FROM students
LEFT JOIN scores ON students.id = scores.student_id;

-- 即使某学生没有成绩，也会显示（score 为 NULL）

-- 使用别名简化
SELECT s.name, sc.subject, sc.score
FROM students s
JOIN scores sc ON s.id = sc.student_id
WHERE sc.score >= 90
ORDER BY sc.score DESC;

-- 多表关联
-- SELECT ... FROM A JOIN B ON ... JOIN C ON ...

JOIN 类型图解

-- INNER JOIN: 只取交集
--   A表         B表
--   [1,2,3] ∩ [2,3,4] = [2,3]

-- LEFT JOIN: A 全取，B 匹配不上的填 NULL
--   A表         B表
--   [1,2,3] → [1(NULL), 2, 3]

-- RIGHT JOIN: B 全取（SQLite 不支持，可用 LEFT JOIN 交换表顺序）
-- FULL OUTER JOIN: 两个表全取（SQLite 不支持）

13. 今日小结

SQL 语句速查

核心要点

• ✅ 关系型数据库用表存储数据，通过外键关联
• ✅ SQL 是操作数据库的标准语言
• ✅ CRUD = Create + Read + Update + Delete
• ✅ WHERE 过滤行，HAVING 过滤组
• ✅ UPDATE/DELETE 必须加 WHERE！
• ✅ JOIN 用于关联多个表的数据
• ✅ SQLite 是 Python 内置的轻量数据库

🎯 练习建议：
1. 创建一个"图书管理"数据库（books + authors 表），练习所有 SQL 操作
2. 用 SELECT 写出"每个班级成绩最好的学生"的查询
3. 用 Python 的 sqlite3 模块连接数据库，执行今天的 SQL（Day46 预习）

📚 Day45 完成！明天用 Python 操作 SQLite 数据库