基本数据操作 – Qgenius® 官方学习平台

CRUD 操作是数据库编程中最基本、最重要的操作，它代表着对数据表进行 Create（创建）、Read（读取）、Update（更新）和 Delete（删除）四种操作，具体来说：

Create（创建）： 向数据表中插入新的数据记录，例如添加一个新用户、一篇新文章等。
Read（读取）： 从数据表中查询数据记录，例如获取用户信息、文章列表等。
Update（更新）： 修改数据表中已有的数据记录，例如修改用户信息、文章内容等。
Delete（删除）： 从数据表中删除数据记录，例如删除一个用户、一篇文章等。

CRUD 操作是所有数据管理活动的基础，无论是简单的信息查询还是复杂的业务逻辑，都离不开 CRUD 操作。几乎所有的应用程序都需要与数据库进行交互，而 CRUD 操作正是应用程序与数据库交互的核心。同时， CRUD 操作是实现各种业务逻辑的基础，例如用户注册、商品购买、订单管理等。此外，通过事务处理等机制，CRUD 操作可以保证数据的一致性和完整性。

现在，假设您正在负责管理一个用户行为日志系统，需要记录用户在产品中的点击、浏览和操作信息。每条记录包含：

用户ID
操作类型（如点击、浏览等）
操作时间
设备信息

首先，我们先来看如何插入数据：

原理：插入操作是将新数据写入数据库。通常使用 SQL 的 INSERT 语句来完成。
注意事项：确保数据格式和字段类型匹配；如果涉及用户输入，一定要注意防止SQL注入。

我们来看一个示例代码和它的提示词。

提示词示例：

提示词：

请生成一段Python代码，使用PyMySQL库连接到MySQL数据库。要求代码中包含数据库连接参数（如服务器地址、端口、用户名、密码、数据库名称和字符编码），并执行一条INSERT语句，将用户行为日志（包括用户ID、操作类型、操作时间和设备信息）插入到表user_logs中。请加入异常处理，并在出现错误时输出具体的错误信息。

示例代码：

import pymysql
from datetime import datetime

def insert_user_log(user_id, action, device_info):
    # 数据库连接参数（根据实际情况修改）
    host = '127.0.0.1'
    port = 3306
    user = 'your_username'
    password = 'your_password'
    db = 'your_database'
    charset = 'utf8mb4'

    try:
        # 建立数据库连接
        connection = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            db=db,
            charset=charset,
            cursorclass=pymysql.cursors.DictCursor
        )
        print("数据库连接成功！")
        
        with connection.cursor() as cursor:
            # 定义插入数据的SQL语句
            sql = """
            INSERT INTO user_logs (user_id, action, action_time, device_info)
            VALUES (%s, %s, %s, %s);
            """
            # 当前时间作为操作时间
            action_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            cursor.execute(sql, (user_id, action, action_time, device_info))
            connection.commit()  # 提交事务
            print("数据插入成功！")
            
    except pymysql.MySQLError as e:
        print("数据库操作出错：", e)
    finally:
        if 'connection' in locals() and connection:
            connection.close()
            print("数据库连接已关闭。")

# 示例调用
if __name__ == "__main__":
    insert_user_log(1001, "点击", "Windows-PC")

接下来，我们来学习如何查询数据（Read）。常见的数据查询有下面几种情况：

单表查询：单表查询指的是从数据库中的一个数据表中检索数据。这种查询可以一次性获取表中的所有记录，也可以只选取某些字段（列）。例如，如果有一个记录用户行为日志的表，你可以查询该表中的所有数据，或者只查询其中的用户ID、操作类型等部分字段。单表查询的重点是数据来源仅限于一个表，而不涉及多个表之间的连接操作。
条件筛选：条件筛选是通过 SQL 语句中的 WHERE 子句来限定查询返回的记录。这样可以使查询结果更加精准，只返回满足特定条件的数据。
- 例如：如果你只关心某个特定用户的操作记录，你可以在查询中加入 WHERE user_id = 101；
- 又如：如果你只想查看最近一段时间内的记录，可以在 WHERE 子句中指定一个时间范围（如 action_time BETWEEN '2025-01-14 00:00:00' AND '2025-01-15 23:59:59'）。
聚合查询：聚合查询使用SQL提供的聚合函数（例如 COUNT、SUM、AVG 等）来对数据进行统计和计算。
- COUNT 用于统计记录的数量；
- SUM 用于计算数值型字段的总和；
- AVG 用于求取数值型字段的平均值。
  通过聚合查询，我们可以直接从数据库中得到统计数据，而不必在程序中手动对查询结果进行遍历和计算。

现在，假设我们要查询用户行为日志表中最近一天内所有点击操作的记录，并统计点击次数。

我们来看一下示例代码及提示词。

提示词示例：

提示词：

请生成一段Python代码，使用PyMySQL库连接到MySQL数据库。代码中需要执行两条SQL语句：

1、查询表user_logs中操作类型为‘点击’且在最近一天内的所有记录。

2、统计这些记录的总数。

请使用异常处理，并输出查询结果和统计信息。

示例代码：

import pymysql
from datetime import datetime, timedelta

def query_click_logs():
    host = '127.0.0.1'
    port = 3306
    user = 'your_username'
    password = 'your_password'
    db = 'your_database'
    charset = 'utf8mb4'

    try:
        connection = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            db=db,
            charset=charset,
            cursorclass=pymysql.cursors.DictCursor
        )
        print("数据库连接成功！")
        
        with connection.cursor() as cursor:
            # 计算时间范围（最近一天）
            now = datetime.now()
            yesterday = now - timedelta(days=1)
            now_str = now.strftime("%Y-%m-%d %H:%M:%S")
            yesterday_str = yesterday.strftime("%Y-%m-%d %H:%M:%S")
            
            # 查询点击操作的记录
            sql_query = """
            SELECT * FROM user_logs 
            WHERE action = '点击' 
            AND action_time BETWEEN %s AND %s;
            """
            cursor.execute(sql_query, (yesterday_str, now_str))
            records = cursor.fetchall()
            print("查询结果：", records)
            
            # 聚合查询，统计点击次数
            sql_count = """
            SELECT COUNT(*) AS click_count FROM user_logs 
            WHERE action = '点击' 
            AND action_time BETWEEN %s AND %s;
            """
            cursor.execute(sql_count, (yesterday_str, now_str))
            count_result = cursor.fetchone()
            print("点击次数：", count_result['click_count'])
            
    except pymysql.MySQLError as e:
        print("数据库查询出错：", e)
    finally:
        if 'connection' in locals() and connection:
            connection.close()
            print("数据库连接已关闭。")

if __name__ == "__main__":
    query_click_logs()

最后，我们来学习一下如何更新与删除数据（Update/Delete）。

更新操作的原理：
使用 SQL 的 UPDATE 语句，通过条件（WHERE）定位要更新的记录，并修改指定字段。

删除操作的原理：
使用 SQL 的 DELETE 语句，通过条件删除数据库中的指定记录。实际操作中，也可以采用逻辑删除（例如设置状态字段）以保留历史记录。

现在，假设我们要更新用户行为日志表中用户ID为1001的记录，将其邮箱更新为新的邮箱地址的一条记录。

我们来看一下示例代码及提示词。

提示词示例：

提示词：

请生成一段Python代码，使用PyMySQL库连接到MySQL数据库。代码中需要更新表user_info中用户ID为1001的记录，将其邮箱更新为新的邮箱地址。请加入异常处理，并在出现错误时输出具体错误信息。

示例代码：

import pymysql

def update_user_email(user_id, new_email):
    host = '127.0.0.1'
    port = 3306
    user = 'your_username'
    password = 'your_password'
    db = 'your_database'
    charset = 'utf8mb4'

    try:
        connection = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            db=db,
            charset=charset,
            cursorclass=pymysql.cursors.DictCursor
        )
        print("数据库连接成功！")
        
        with connection.cursor() as cursor:
            sql_update = """
            UPDATE user_info 
            SET email = %s 
            WHERE user_id = %s;
            """
            cursor.execute(sql_update, (new_email, user_id))
            connection.commit()
            print("用户邮箱更新成功！")
            
    except pymysql.MySQLError as e:
        print("更新操作出错：", e)
    finally:
        if 'connection' in locals() and connection:
            connection.close()
            print("数据库连接已关闭。")

if __name__ == "__main__":
    update_user_email(1001, "new_email@example.com")

接下来，我们看一下怎么做删除。

现在，假设我们要删除用户行为日志表中产品ID为2002的记录，模拟产品下架操作。

我们来看一下示例代码及提示词。

提示词示例：

提示词：

请生成一段Python代码，使用PyMySQL库连接到MySQL数据库。代码中需要删除表products中产品ID为2002的记录，模拟产品下架操作。请加入异常处理，并在出现错误时输出具体错误信息。

示例代码：

import pymysql

def delete_product(product_id):
    host = '127.0.0.1'
    port = 3306
    user = 'your_username'
    password = 'your_password'
    db = 'your_database'
    charset = 'utf8mb4'

    try:
        connection = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            db=db,
            charset=charset,
            cursorclass=pymysql.cursors.DictCursor
        )
        print("数据库连接成功！")
        
        with connection.cursor() as cursor:
            sql_delete = "DELETE FROM products WHERE product_id = %s;"
            cursor.execute(sql_delete, (product_id,))
            connection.commit()
            print("产品记录删除成功！")
            
    except pymysql.MySQLError as e:
        print("删除操作出错：", e)
    finally:
        if 'connection' in locals() and connection:
            connection.close()
            print("数据库连接已关闭。")

if __name__ == "__main__":
    delete_product(2002)

调试技巧与常见错误处理

调试技巧：
- 仔细阅读错误信息，根据错误提示检查SQL语法或参数问题。
- 在提示词中明确要求加入异常处理和输出错误信息，便于定位问题。
- 可以先在数据库管理工具中测试SQL语句，再将其嵌入代码中使用。
常见错误处理：
- 连接错误：检查服务器地址、端口、用户名和密码是否正确。
- SQL语法错误：确认SQL语句在数据库中能正常执行。
- 事务提交失败：确保调用了 connection.commit() 提交事务；若执行多条语句失败，则可回滚（使用 connection.rollback()）。

练习：

在之前课程中，你使用Python创建了一个产品反馈系统的数据库和数据表。

现在，假设您获得了一条新的产品反馈信息，内容如下：

用户ID：101；用户姓名：A1ice；用户反馈内容：“产品体验很好，但希望增加更多功能。”；产品编号：250801；用户评级：4颗星；反馈提交时间：2025-01-15 14:30:00；反馈信息类别：功能建议。

1、请设计一个提示词，完成下列操作：

在数据表中加入上述记录。
按用户ID查询这条记录是否存在。

2、请设计一个提示词，完成下列操作：

在数据表中修改上述记录，将用户姓名更改为：Alice。将用户反馈内容修改为“产品体验非常好！”。
按用户姓名查询这条记录是否存在。

3、请设计一个提示词，完成下列操作：

在数据表中删除上述记录。
按通过查询确认记录已经不存在。

注意：请加入异常处理，并在操作成功或失败时输出相应的提示信息。

AI 助教

提示：您可在此提出学习中遇到的问题。回答由 AI 生成，可能存在错误，请注意甄别。