数据库及SQL常用语法

关系型数据库基础与 SQL 常用查询语法精讲

小象实战讲义 · Python数据分析实战

当数据规模从几千行增长到数百万行，或者当数据不再是孤立的单张表格，而是由“用户表”、“订单表”、“商品表”等多张相互关联的实体表格组成时，传统的文件存储（CSV/Excel）在并发读写、数据一致性维护与多维度复杂查询上就会遇到瓶颈。此时，**关系型数据库（RDBMS）与结构化查询语言（SQL）**就成为了企业级数据存储与分析的基石。本节将带你建立关系型数据库的底层模型认知，系统攻克 SQL 的 CRUD 核心语法与常用聚合查询技巧。

💡 核心导读

• 数据库与数据表核心概念：掌握表（Table）、记录（Row）、字段（Column）、主键（Primary Key）与外键（Foreign Key）。
• 主流数据库家族对比：了解 MySQL、PostgreSQL、Oracle 以及轻量嵌入式 SQLite 的特点。
• SQL 核心语法体系：精通增删改查（CRUD）四大核心语句的标准编写规范。
• 高阶查询子句全链路：掌握 WHERE 过滤、ORDER BY 排序、GROUP BY 分组与 HAVING 聚合筛选。
• SQLite 纯 SQL 查询与验证实战：通过 Python 内置数据库引擎实战演练 SQL 核心语法。

1. 关系型数据库全景认知

什么是数据库？

数据库（Database）是按照一定数据模型组织、存储和管理数据的仓库。一个数据库通常由多张**数据表（Table）**组成：

• 行（Row / Record）：代表一条具体的实体数据记录（例如：一个特定用户）；
• 列（Column / Field）：代表实体拥有的某种属性（例如：用户姓名、注册时间、城市）；
• 主键（Primary Key）：表中能够唯一标识一条记录的字段（如 user_id），不可重复且不可为空；
• 外键（Foreign Key）：用于建立多张表之间关联关系的字段（如订单表中的 user_id 指向用户表的主键）。

┌───────────────────────────────────┐       ┌───────────────────────────────────┐
│          用户表 (users)           │       │          订单表 (orders)          │
├─────────┬───────────┬─────────────┤       ├──────────┬─────────┬──────────────┤
│ user_id │ user_name │    city     │<──────┼ order_id │ user_id │ total_amount │
│ (主键 PK)│   (文本)  │    (文本)   │ (外键) │ (主键 PK)│ (外键 FK)│    (数值)    │
└─────────┴───────────┴─────────────┘       └──────────┴─────────┴──────────────┘

主流关系型数据库简介

• MySQL / PostgreSQL：开源界最流行的企业级服务端数据库，支持极高并发与海量数据；
• Oracle / MS SQL Server：大型传统企业、电信与核心业务系统常用的商业级数据库；
• SQLite：轻量级嵌入式数据库，整个数据库就是一个独立的本地磁盘文件，无需安装任何独立服务器软件，零配置即可使用，是移动端与单机数据分析的绝佳选择。

2. SQL 常用语法与 CRUD 核心操作

SQL（Structured Query Language，结构化查询语言）是与所有关系型数据库沟通的通用国际标准语言。其核心操作分为四大类别（CRUD）：

┌─────────────────────────────────────────────────────────────┐
│                       SQL CRUD 核心操作速查                  │
├───────────────┬─────────────────────────────────────────────┤
│ 1. Create (增)│ INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2); │
├───────────────┼─────────────────────────────────────────────┤
│ 2. Read (查)  │ SELECT 列1, 列2 FROM 表名 WHERE 过滤条件;      │
├───────────────┼─────────────────────────────────────────────┤
│ 3. Update (改)│ UPDATE 表名 SET 列1 = 新值 WHERE 过滤条件;     │
├───────────────┼─────────────────────────────────────────────┤
│ 4. Delete (删)│ DELETE FROM 表名 WHERE 过滤条件;              │
└───────────────┴─────────────────────────────────────────────┘

⚠️ 高危操作警示：在执行 UPDATE 和 DELETE 语句时，务必确认携带 WHERE 条件！如果遗漏 WHERE 子句，将会更新或清空整张表的所有记录！

3. SQL 查询进阶：过滤、排序、聚合与分组

在日常数据分析中，90% 以上的工作都在使用 SELECT 查询语句。一个完整的复杂查询通常包含以下关键子句，其书写与执行顺序如下：

SELECT 字段列表, 聚合函数(COUNT/SUM/AVG)
FROM 表名
WHERE 行级过滤条件 (AND/OR/IN/BETWEEN/LIKE)
GROUP BY 分组字段
HAVING 组级过滤条件 (针对聚合结果进行筛选)
ORDER BY 排序字段 ASC/DESC
LIMIT 返回行数;

• 常用聚合函数：
  • COUNT(*)：统计记录总行数；
  • SUM(column)：计算数值列总和；
  • AVG(column)：计算数值列平均值；
  • MAX(column) / MIN(column)：找出列的最大值与最小值。

4. Python 代码实战：使用内存 SQLite 演练核心 SQL 语法

下面我们通过 Python 内置的 sqlite3 模块，在内存中创建一张模拟员工表，演示标准 SQL 的建表、插入、更新、条件过滤与分组聚合全流程。

# 示例 1：在 SQLite 中执行 DDL 建表与 DML 数据插入

import sqlite3

# 连接到内存数据库（":memory:" 模式，测试完毕自动销毁）
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

# 1. 创建员工信息表 (employees)
create_table_sql = """
CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT,
    salary REAL,
    city TEXT
);
"""
cursor.execute(create_table_sql)

# 2. 批量插入初始数据
mock_employees = [
    (101, "张伟", "技术部", 15000.0, "北京"),
    (102, "李娜", "市场部", 12000.0, "上海"),
    (103, "王强", "技术部", 18000.0, "北京"),
    (104, "刘洋", "运营部", 9500.0, "广州"),
    (105, "陈晨", "技术部", 16000.0, "上海"),
    (106, "赵敏", "市场部", 13500.0, "北京")
]

insert_sql = "INSERT INTO employees (emp_id, name, department, salary, city) VALUES (?, ?, ?, ?, ?);"
cursor.executemany(insert_sql, mock_employees)
conn.commit()

print(f"✅ 成功创建数据表并插入 {len(mock_employees)} 条员工记录！")

接下来，我们执行包含条件过滤、排序、分组聚合与 HAVING 筛选的 SQL 复杂查询：

# 示例 2：执行复杂 SQL 查询与聚合统计

# 1. 基础条件查询：查找北京地区薪资 > 13000 的员工
query_1 = """
SELECT name, department, salary 
FROM employees 
WHERE city = '北京' AND salary > 13000.0
ORDER BY salary DESC;
"""
cursor.execute(query_1)
print("\n=== 北京高薪员工查询结果 (ORDER BY salary DESC) ===")
for name, dept, sal in cursor.fetchall():
    print(f"• 姓名: {name:<4} | 部门: {dept:<6} | 薪资: {sal:.2f} 元")

# 2. 分组聚合查询：统计各部门的人数与平均薪资，并筛选平均薪资 > 12000 的部门
query_2 = """
SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary, MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 12000.0
ORDER BY avg_salary DESC;
"""
cursor.execute(query_2)
print("\n=== 部门薪酬统计 (GROUP BY + HAVING 筛选) ===")
for dept, count, avg_sal, max_sal in cursor.fetchall():
    print(f"• 部门: {dept:<6} | 人数: {count} 人 | 平均薪资: {avg_sal:.2f} 元 | 最高薪资: {max_sal:.2f} 元")

# 3. 更新操作演示：为市场部员工全员上调 1000 元
update_sql = "UPDATE employees SET salary = salary + 1000 WHERE department = '市场部';"
cursor.execute(update_sql)
conn.commit()

cursor.execute("SELECT name, salary FROM employees WHERE department = '市场部';")
print("\n=== 调薪后市场部薪资明细 ===")
for name, sal in cursor.fetchall():
    print(f"• {name}: {sal:.2f} 元")

conn.close()

📝 动手练一练

1. 语法纠错题：一位初学 SQL 的同学写了下面这条查询语句，想要统计订单金额总和大于 10000 元的城市：

   SELECT city, SUM(amount) FROM orders WHERE SUM(amount) > 10000 GROUP BY city;

   请问这条 SQL 会报错吗？为什么？应该如何修改？

   👉 点击查看参考答案

   参考答案： 会报错。 因为 WHERE 子句是在数据分组之前对单行原始记录进行过滤的，不能在 WHERE 中直接使用聚合函数（如 SUM(amount)）。 对聚合后的结果进行条件过滤，必须使用 HAVING 子句。 正确 SQL：

   SELECT city, SUM(amount) 
   FROM orders 
   GROUP BY city 
   HAVING SUM(amount) > 10000;

2. SQL 编写练习：假设有一张图书表 books (book_id, title, author, price, stock)。请编写一条 SQL 语句，查询库存量（stock）大于 0 且价格在 30 到 80 元之间的图书，按照价格从高到低排序，只返回前 5 本图书的 title 和 price。

   👉 点击查看参考答案

   参考答案：

   SELECT title, price 
   FROM books 
   WHERE stock > 0 AND price BETWEEN 30 AND 80
   ORDER BY price DESC 
   LIMIT 5;

本章小结

在本节中，我们建立了关系型数据库与 SQL 查询的系统认知：

• 关系型数据库通过主键与外键维护多表实体之间的结构化关联；
• 牢记 CRUD 基础语法，执行修改与删除时切记加上 WHERE 条件；
• 熟练掌握 WHERE（行过滤）、GROUP BY（分组）、HAVING（聚合过滤）与 ORDER BY（排序）的执行顺序。

📋 行动清单

• 记忆常用的 5 个 SQL 聚合函数：COUNT、SUM、AVG、MAX、MIN。
• 理解 WHERE 与 HAVING 的本质区别，准备进入下一节 Python 操作本地 SQLite 实战。

—— 小象教研组