侧边栏壁纸
博主头像
前端学习

行动起来，活在当下

  • 累计撰写 313 篇文章
  • 累计创建 18 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录
python

SQL基础命令与Python调用

Administrator
Administrator
2026-06-06 / 0 评论 / 0 点赞 / 13 阅读 / 0 字

SQL 基础命令与 Python 调用 SQL

SQLite 的特点是：不需要单独安装数据库服务，数据直接保存在一个 .db 文件里，适合学习、脚本、报表 Demo 和小型工具。

一、SQL 是什么

SQL 是用来操作数据库的语言，常见用途包括：

  • 创建表
  • 插入数据
  • 查询数据
  • 修改数据
  • 删除数据
  • 统计汇总
  • 多表关联

最常见的 SQL 可以分成几类：

类型作用常见命令
DDL定义表结构CREATE、ALTER、DROP
DML操作表数据INSERT、UPDATE、DELETE
DQL查询数据SELECT
TCL事务控制COMMIT、ROLLBACK

二、创建表 CREATE TABLE

创建一张订单表：

CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_no TEXT NOT NULL,
    customer_name TEXT,
    amount REAL NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL
);

字段说明：

字段含义
id自增主键
order_no订单编号
customer_name客户名称
amount订单金额
status订单状态，例如 paid、refund、failed
created_at创建日期

常见字段类型：

类型说明
INTEGER整数
REAL小数
TEXT文本
BLOB二进制数据
NULL空值

三、插入数据 INSERT

插入一条数据：

INSERT INTO orders (order_no, customer_name, amount, status, created_at)
VALUES ('ORD001', '张三', 199.90, 'paid', '2026-06-06');

插入多条数据：

INSERT INTO orders (order_no, customer_name, amount, status, created_at)
VALUES
    ('ORD002', '李四', 299.00, 'paid', '2026-06-06'),
    ('ORD003', '王五', 88.50, 'failed', '2026-06-06'),
    ('ORD004', '赵六', 120.00, 'refund', '2026-06-06');

四、查询数据 SELECT

查询全部字段：

SELECT * FROM orders;

查询指定字段：

SELECT order_no, customer_name, amount, status
FROM orders;

给字段起别名：

SELECT
    order_no AS 订单编号,
    amount AS 订单金额,
    status AS 订单状态
FROM orders;

五、条件查询 WHERE

查询已支付订单：

SELECT *
FROM orders
WHERE status = 'paid';

查询金额大于 100 的订单：

SELECT *
FROM orders
WHERE amount > 100;

多个条件：

SELECT *
FROM orders
WHERE status = 'paid'
  AND amount >= 100;

或者条件：

SELECT *
FROM orders
WHERE status = 'failed'
   OR status = 'refund';

范围查询：

SELECT *
FROM orders
WHERE amount BETWEEN 100 AND 300;

模糊查询：

SELECT *
FROM orders
WHERE customer_name LIKE '%张%';

指定多个值：

SELECT *
FROM orders
WHERE status IN ('paid', 'refund');

六、排序 ORDER BY

按金额从小到大：

SELECT *
FROM orders
ORDER BY amount ASC;

按金额从大到小：

SELECT *
FROM orders
ORDER BY amount DESC;

按日期和金额排序：

SELECT *
FROM orders
ORDER BY created_at ASC, amount DESC;

七、限制数量 LIMIT

查询前 10 条：

SELECT *
FROM orders
LIMIT 10;

跳过前 10 条，再查 10 条：

SELECT *
FROM orders
LIMIT 10 OFFSET 10;

八、统计函数

统计订单数量：

SELECT COUNT(*) AS total_orders
FROM orders;

统计订单总金额：

SELECT SUM(amount) AS total_amount
FROM orders;

统计平均金额：

SELECT AVG(amount) AS avg_amount
FROM orders;

查询最大、最小金额：

SELECT
    MAX(amount) AS max_amount,
    MIN(amount) AS min_amount
FROM orders;

九、分组统计 GROUP BY

按订单状态统计数量：

SELECT
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY status;

按日期统计销售额：

SELECT
    created_at,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_amount
FROM orders
GROUP BY created_at
ORDER BY created_at;

只统计已支付订单的每日销售额：

SELECT
    created_at,
    COUNT(*) AS paid_orders,
    SUM(amount) AS paid_amount
FROM orders
WHERE status = 'paid'
GROUP BY created_at
ORDER BY created_at;

十、分组后过滤 HAVING

WHERE 是分组前过滤，HAVING 是分组后过滤。

查询每日订单数大于 5 的日期：

SELECT
    created_at,
    COUNT(*) AS total_orders
FROM orders
GROUP BY created_at
HAVING COUNT(*) > 5;

十一、条件统计 CASE WHEN

统计每日订单、已支付订单、退款订单、失败订单：

SELECT
    created_at AS report_date,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = 'refund' THEN 1 ELSE 0 END) AS refund_orders,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed_orders
FROM orders
GROUP BY created_at
ORDER BY created_at;

统计每日已支付销售额：

SELECT
    created_at AS report_date,
    ROUND(SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END), 2) AS paid_amount
FROM orders
GROUP BY created_at
ORDER BY created_at;

十二、修改数据 UPDATE

修改单条订单状态：

UPDATE orders
SET status = 'paid'
WHERE order_no = 'ORD003';

同时修改多个字段：

UPDATE orders
SET
    status = 'refund',
    amount = 0
WHERE order_no = 'ORD004';

注意：写 UPDATE 时一定要加 WHERE，否则会修改整张表。

十三、删除数据 DELETE

删除一条订单：

DELETE FROM orders
WHERE order_no = 'ORD004';

删除失败订单：

DELETE FROM orders
WHERE status = 'failed';

注意：写 DELETE 时一定要加 WHERE，否则会删除整张表的数据。

十四、删除表 DROP TABLE

删除订单表：

DROP TABLE IF EXISTS orders;

这个命令会直接删除表结构和表数据，练习时要谨慎使用。

十五、多表关联 JOIN

假设有客户表：

CREATE TABLE IF NOT EXISTS customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    phone TEXT
);

订单表里保存客户 ID：

CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    amount REAL NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL
);

关联查询订单和客户：

SELECT
    orders.id AS order_id,
    customers.name AS customer_name,
    customers.phone,
    orders.amount,
    orders.status,
    orders.created_at
FROM orders
LEFT JOIN customers
    ON orders.customer_id = customers.id;

常见 JOIN：

JOIN 类型含义
INNER JOIN两张表都匹配才返回
LEFT JOIN左表全部返回，右表匹配不到则为 NULL
RIGHT JOIN右表全部返回，SQLite 不直接支持

SQLite 最常用的是 LEFT JOIN。

十六、Python 调用 SQLite

Python 内置了 sqlite3 模块，不需要额外安装。

1. 连接数据库

import sqlite3
from config import DB_PATH

conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()

2. 执行查询

import sqlite3
from config import DB_PATH

conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()

cursor.execute("""
    SELECT id, order_no, customer_name, amount, status, created_at
    FROM orders
    ORDER BY created_at DESC
    LIMIT 10
""")

rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

3. 查询一条数据

import sqlite3
from config import DB_PATH

conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()

cursor.execute("""
    SELECT id, order_no, amount, status
    FROM orders
    WHERE order_no = ?
""", ("ORD001",))

row = cursor.fetchone()
print(row)

conn.close()

这里的 ? 是参数占位符，可以防止 SQL 注入。

4. 插入数据

import sqlite3
from config import DB_PATH

conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()

cursor.execute("""
    INSERT INTO orders (order_no, customer_name, amount, status, created_at)
    VALUES (?, ?, ?, ?, ?)
""", ("ORD100", "测试客户", 168.80, "paid", "2026-06-06"))

conn.commit()
conn.close()

5. 批量插入数据

import sqlite3
from config import DB_PATH

orders = [
    ("ORD101", "客户A", 100.00, "paid", "2026-06-06"),
    ("ORD102", "客户B", 200.00, "failed", "2026-06-06"),
    ("ORD103", "客户C", 300.00, "refund", "2026-06-06"),
]

conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()

cursor.executemany("""
    INSERT INTO orders (order_no, customer_name, amount, status, created_at)
    VALUES (?, ?, ?, ?, ?)
""", orders)

conn.commit()
conn.close()

6. 修改数据

import sqlite3
from config import DB_PATH

conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()

cursor.execute("""
    UPDATE orders
    SET status = ?
    WHERE order_no = ?
""", ("paid", "ORD102"))

conn.commit()
conn.close()

7. 删除数据

import sqlite3
from config import DB_PATH

conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()

cursor.execute("""
    DELETE FROM orders
    WHERE order_no = ?
""", ("ORD103",))

conn.commit()
conn.close()

8. 推荐写法：使用 with 自动关闭连接

import sqlite3
from config import DB_PATH

with sqlite3.connect(DB_PATH) as conn:
    cursor = conn.cursor()

    cursor.execute("""
        SELECT created_at, COUNT(*) AS total_orders
        FROM orders
        GROUP BY created_at
        ORDER BY created_at
    """)

    rows = cursor.fetchall()

for row in rows:
    print(row)

with sqlite3.connect(...) as conn 的好处是：

  • 执行成功时自动提交
  • 出错时自动回滚
  • 代码更简洁

十七、Python 查询结果转成字典

默认查询结果是元组，例如：

(1, 'ORD001', 199.9, 'paid')

如果想用字段名读取，可以这样写：

import sqlite3
from config import DB_PATH

conn = sqlite3.connect(DB_PATH)
conn.row_factory = sqlite3.Row

cursor = conn.cursor()
cursor.execute("""
    SELECT id, order_no, amount, status
    FROM orders
    LIMIT 5
""")

rows = cursor.fetchall()

for row in rows:
    print(row["order_no"], row["amount"], row["status"])

conn.close()

十八、Python 使用 pandas 读取 SQL

当前项目已经安装了 pandas，可以直接把 SQL 查询结果变成 DataFrame。

import sqlite3
import pandas as pd
from config import DB_PATH

with sqlite3.connect(DB_PATH) as conn:
    df = pd.read_sql_query("""
        SELECT
            created_at AS report_date,
            COUNT(*) AS total_orders,
            SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
            ROUND(SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END), 2) AS paid_amount
        FROM orders
        GROUP BY created_at
        ORDER BY created_at
    """, conn)

print(df)

保存成 Excel：

df.to_excel("reports/sql_report.xlsx", index=False)

保存成 CSV：

df.to_csv("reports/sql_report.csv", index=False, encoding="utf-8-sig")

十九、参数化查询，防止 SQL 注入

不要这样拼接 SQL：

status = "paid"
sql = f"SELECT * FROM orders WHERE status = '{status}'"

推荐这样写：

status = "paid"

cursor.execute("""
    SELECT *
    FROM orders
    WHERE status = ?
""", (status,))

多个参数：

status = "paid"
min_amount = 100

cursor.execute("""
    SELECT *
    FROM orders
    WHERE status = ?
      AND amount >= ?
""", (status, min_amount))

二十、一个完整的 Python 示例

下面这个脚本会连接当前项目数据库，查询每日订单统计，并打印结果。

import sqlite3
from config import DB_PATH


def query_daily_report():
    sql = """
        SELECT
            created_at AS report_date,
            COUNT(*) AS total_orders,
            SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
            SUM(CASE WHEN status = 'refund' THEN 1 ELSE 0 END) AS refund_orders,
            SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed_orders,
            ROUND(SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END), 2) AS paid_amount
        FROM orders
        GROUP BY created_at
        ORDER BY created_at;
    """

    with sqlite3.connect(DB_PATH) as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute(sql)
        return cursor.fetchall()


def main():
    rows = query_daily_report()

    for row in rows:
        print(
            row["report_date"],
            row["total_orders"],
            row["paid_orders"],
            row["refund_orders"],
            row["failed_orders"],
            row["paid_amount"],
        )


if __name__ == "__main__":
    main()

二十一、如果以后换成 MySQL 或 PostgreSQL

SQLite 的连接方式：

import sqlite3

conn = sqlite3.connect("data/monitor_demo.db")

MySQL 通常使用 pymysql 或 mysql-connector-python：

import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="你的密码",
    database="你的数据库名",
    charset="utf8mb4",
)

PostgreSQL 通常使用 psycopg：

import psycopg

conn = psycopg.connect(
    host="127.0.0.1",
    port=5432,
    user="postgres",
    password="你的密码",
    dbname="你的数据库名",
)

不管换成哪种数据库，基本流程都差不多：

连接数据库 -> 创建游标 -> 执行 SQL -> 读取结果 -> 提交事务 -> 关闭连接

二十二、学习顺序建议

建议按这个顺序练习：

  1. SELECT * FROM orders;
  2. WHERE 条件查询
  3. ORDER BY 排序
  4. COUNT、SUM、AVG 统计
  5. GROUP BY 分组统计
  6. CASE WHEN 条件统计
  7. Python sqlite3 查询
  8. Python 插入、修改、删除
  9. pandas 读取 SQL 生成报表
  10. 多表 JOIN

当前项目最重要的两个入口文件：

  • init_db.py：初始化数据库和模拟数据
  • report.py：读取数据库，生成报表

可以先运行：

python init_db.py
python report.py

然后再尝试修改 SQL，观察报表结果怎么变化。

0
版权归属： Administrator
本文链接： http://localhost:8090/archives/8895336b-94fb-4165-a165-5b5203ace99e
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区