创建数据库和数据表 – Qgenius® 官方学习平台

数据库是数据的家。了解数据库开发知识，有助于AI产品经理更好地理解数据存储、处理及安全性问题，从而能更精准地规划产品功能、有效与开发团队沟通需求，并推动数据驱动决策。

在数据库中，最常用的是关系型数据库。关系型数据库通过表（二维表格）来存储数据，各个表之间可以通过“关系”进行关联。它的特点是：数据结构化、数据之间有明确关联、支持 SQL 语言进行操作。我们知道， Excel 文件和 JSON 文件数据格式虽然灵活、但缺乏严格的数据约束。而关系型数据库则更适合数据量大、需要复杂查询和数据完整性保障的场景。

在这里我们将以MySQL数据库为例，学习如何运用Python来帮助我们更高效地操作关系型数据库。

什么是 MySQL 数据库？

MySQL 是一种常用的开源关系型数据库管理系统，广泛应用于各种企业级项目中。

我们经常会在Linux服务器上安装MySQL。数据库工程师通常需要登录服务器，并在终端里使用命令安装MySQL数据库。这就是数据库工程师常用的安装命令：

sudo apt update
sudo apt install mysql-server

注：本示例仅供参考，无须实际操作。如果您有条件，也可以尝试在本地安装 MySQL 数据库。

使用 SQL 命令创建数据库和数据表

在这里我们首先要了解数据库和数据表这两个不同的概念。

数据库（Database）：可以看作是一个容器，用来存放多个数据表。每个数据库针对一个特定的业务场景，例如用户信息、订单记录或员工管理等。

一个新项目开始时，我们往往先要创建数据库。这样的目的是将相关数据集中管理，可以提高数据的组织性和查询效率，也方便对数据进行权限管理和备份。

我们可以在终端中直接使用MySQL内部的SQL命令来创建数据库：

CREATE DATABASE my_database;

这里假设你要建立的数据库名字是：my_database。建立数据库后，如果我们想选择这个数据库进行后续操作，还需要使用下面的SQL命令：

USE my_database;

接下来我们要在这个数据库里创建数据表。数据表（Table）同数据库不同，如果数据库是数据的家，那数据表就是具体的房间，他们往往有自己专门的用处。比如说，卧室用来睡觉，存放床和衣柜；客厅用来会客，存放沙发和电视。在一个数据库中，每个数据表用于存储某一类具体的数据。例如，在“用户管理”数据库中，可以有“用户信息”、“登录记录”等数据表。

数据表也是真正存储数据的地方。它类似于 Excel 中的工作表，由行和列组成。会定义数据的结构和类型。每一列代表一种数据类型（例如姓名、年龄、邮箱等），而每一行代表一条记录。从而帮助我们组织数据。

我们也可以用 SQL 命令创建数据表。例如，下面这个示例中，创建了一个存放学生信息的数据表。

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100)
);

这里你可能注意到了，每个字段（列）有明确的数据类型，如：VARCHAR(50) 表示最大长度为 50 个字符的字符串，可以用来存储姓名、地址等文本信息。INT 表示整数，可以用来存储年龄、ID 等数值信息。DATE 表示日期，可以用来存储生日、注册时间等日期信息。

而主键是用来唯一标识一条记录的字段。例如，在学生信息表中，每个学生都有一个唯一的学号，可以作为主键。主键可以帮助我们快速找到特定的记录，也可以保证数据的唯一性。

还有数据约束： “NOT NULL 表示该字段不能为空，UNIQUE 表示该字段的值不能重复。例如，学生的姓名不能为空，学号不能重复。”

每个数据表的字段都有自己非常明确的格式，这是和JSON数据不同的地方。如果程序没有很好的处理好数据格式，就可能引发错误，这也是初学时要注意的地方。

附： Python 数据类型和 MySQL 数据表数据类型的常见异同：

整型（Integer）
- MySQL：常用类型为 INT、SMALLINT、BIGINT 等，用于存储整数。
- Python：使用 int 类型存储整数。
- 不同点：
  - MySQL 中不同的整型有不同的取值范围（例如 SMALLINT 范围比 INT 小）。
  - 在 MySQL 中还可以设置 UNSIGNED（无符号），这在 Python 中没有直接对应。
浮点型（Floating Point）
- MySQL：常用类型为 FLOAT 和 DOUBLE，用于存储浮点数。
- Python：使用 float 类型表示浮点数（默认是双精度，即相当于 MySQL 的 DOUBLE）。
- 不同点：
  - MySQL 提供了不同精度的浮点类型，开发者需要根据精度需求进行选择，而 Python 的 float 通常采用双精度。
定点数（Decimal）
- MySQL：DECIMAL 类型用于存储精度要求高的数值（如财务数据）。
- Python：可以使用 decimal.Decimal 来处理精确小数运算。
- 不同点：
  - MySQL 的 DECIMAL 是以字符串形式存储精确数值，确保计算精度；Python 的 Decimal 需要通过模块导入，使用时需注意类型转换。
字符串类型（String）
- MySQL：主要有 CHAR（定长字符）和 VARCHAR（变长字符），以及 TEXT 类型用于存储大段文本。
- Python：使用 str 类型表示字符串。
- 不同点：
  - MySQL 中需要提前设定字符长度（例如 VARCHAR(50)），而 Python 的字符串没有长度限制。
  - 数据库存储时可能涉及字符集（如 UTF-8），而 Python 字符串通常以 Unicode 表示。
布尔类型（Boolean）
- MySQL：没有独立的布尔类型，通常使用 TINYINT(1) 表示，0 代表 False，1 代表 True；从 MySQL 8.0 起，也可用 BOOLEAN（但底层仍为 TINYINT）。
- Python：使用 bool 类型，有 True 和 False 两个值。
- 不同点：
  - 在 MySQL 中，布尔类型本质上是整数类型，需要在代码中进行转换处理。
日期和时间（Date and Time）
- MySQL：常用类型包括 DATE、TIME、DATETIME、TIMESTAMP。
- Python：使用 datetime.date、datetime.time 和 datetime.datetime 来表示日期和时间。
- 不同点：
  - MySQL 的 TIMESTAMP 类型会根据时区进行转换，而 DATETIME 不会；Python 中需要借助第三方库（如 pytz）或 datetime 的时区支持来处理时区问题。
二进制数据（Binary Data）
- MySQL：使用 BLOB 或 BINARY 类型存储二进制数据（如图片、文件）。
- Python：使用 bytes 类型表示二进制数据。
- 不同点：
  - 存储二进制数据时，MySQL 中会有大小限制（例如 TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB），开发时需要根据数据大小选择合适的类型。
JSON 数据类型
- MySQL：从 5.7 版本开始支持 JSON 类型，用于存储 JSON 格式的数据，支持部分 JSON 函数。
- Python：通常使用字典（dict）或列表（list）来表示 JSON 数据，通过 json 模块进行序列化和反序列化。
- 不同点：
  - MySQL 的 JSON 类型在存储时会自动验证数据格式，且可以直接在数据库层面进行 JSON 数据查询；而 Python 中的 JSON 数据需要先转换为字符串才能存入数据库。

直接使用 SQL 命令虽然简单，但在很多场景下可能没有权限直接登录服务器，且存在安全风险，因此我们可以用 Python 脚本来自动化并安全地完成这些操作。

用 Python 创建数据库

Python 中有一些常用的 MySQL 库，如：

PyMySQL：一个纯 Python 实现的 MySQL 客户端，安装简单，适合初学者使用。
SQLAlchemy：功能强大的 ORM（对象关系映射）库，适合需要更高抽象层次开发的场景。

这里我们将以更简单直接的 PyMySQL 为例，帮助大家理解数据库连接和操作的基本流程。

首先我们先要安装 PyMySQL ：

pip install pymysql

注：为了保证安全，敏感信息（如数据库密码）可以存放在环境变量或配置文件中，避免直接硬编码在代码里。

现在我们来学习：

如何用 Python 创建数据库。

假设每位同学用自己的学号作为数据库名称（例如学号为 2025001，则数据库名为 db_2025001），下面提供一个通用的示例：

import pymysql
import os

# 配置连接参数（注意：敏感信息建议使用环境变量或配置文件管理）
db_host = os.getenv("DB_HOST", "localhost")       # 主机地址
db_port = int(os.getenv("DB_PORT", 3306))           # 端口
db_user = os.getenv("DB_USER", "root")              # 用户名
db_password = os.getenv("DB_PASSWORD", "your_password")  # 密码

# 数据库名称（这里建议学生替换为自己的学号，例如 'db_2025001'）
db_name = "db_example"

try:
    # 建立与 MySQL 服务器的连接（注意：初次创建数据库时不需要指定数据库名）
    connection = pymysql.connect(
        host=db_host,
        port=db_port,
        user=db_user,
        password=db_password,
        charset='utf8mb4'
    )
    cursor = connection.cursor()
    
    # 使用 SQL 命令创建数据库
    create_db_query = f"CREATE DATABASE IF NOT EXISTS {db_name};"
    cursor.execute(create_db_query)
    print(f"数据库 {db_name} 创建成功或已存在。")
    
except Exception as e:
    print("连接或创建数据库时出现错误：", e)
finally:
    if connection:
        connection.close()

说明：我们看到程序里有一段是专门配置连接参数的，这里面包括安装MySQL的服务器主机地址、端口、用户名、密码，我们需要确保这些信息正确无误。

同样，我们可以使用 AI 来帮助我们生成这段代码。您可以尝试输入类似下面的提示词：

提示词：

请帮我以 Python 初学者的角度，编写一个程序，用 PyMySQL 连接 MySQL，创建一个名为 db_example 的数据库，并确保代码中包含注释说明每一步操作，同时处理可能的异常情况。

用 Python 创建数据表 在成功创建数据库后，接下来我们将学习如何用 Python 来创建数据表。这里要注意，当设计新的数据表时，我们要遵循下面这些原则：

明确字段：每个字段的数据类型和长度应与实际业务数据相匹配。
主键设置：通常需要设置一个主键（如自增 ID）来唯一标识每条记录。
数据约束：可设置 NOT NULL、UNIQUE 等约束保证数据的完整性。

好，现在我们来看一个用 Python 创建数据表的示例。注意：在创建数据表前，我们需要重新连接时指定数据库名称。这个程序的示例如下：

import pymysql
import os

# 连接参数
db_host = os.getenv("DB_HOST", "localhost")
db_port = int(os.getenv("DB_PORT", 3306))
db_user = os.getenv("DB_USER", "root")
db_password = os.getenv("DB_PASSWORD", "your_password")
db_name = "db_example"  # 学生可替换为自己的数据库名，如 'db_2025001'

try:
    # 连接到指定的数据库
    connection = pymysql.connect(
        host=db_host,
        port=db_port,
        user=db_user,
        password=db_password,
        database=db_name,  # 这里指定了数据库名称
        charset='utf8mb4'
    )
    cursor = connection.cursor()
    
    # 使用 SQL 命令创建数据表（例如：创建一个学生信息表）
    create_table_query = """
    CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        age INT,
        email VARCHAR(100)
    );
    """
    cursor.execute(create_table_query)
    connection.commit()  # 提交更改
    print("数据表 students 创建成功或已存在。")
    
except Exception as e:
    print("连接或创建数据表时出现错误：", e)
finally:
    if connection:
        connection.close()

在上述代码中，我们建立连接时，先要指定数据库名：确保在操作数据表时，连接到正确的数据库。

在这个程序中，我们通过 cursor.execute() 执行 SQL 命令，并使用 connection.commit() 确保更改得到了保存。

同样，我们也可以用提示词来撰写这段代码：

提示词：

请帮我生成一段 Python 代码，使用 PyMySQL 连接到名为 db_example 的 MySQL 数据库，然后创建一个数据表 students，其中包括自增主键 id、name（50 字符以内）、age 和 email 字段。请在代码中添加必要的注释并处理异常情况。

安全性与环境变量配置

直接在代码中硬编码数据库密码等敏感信息存在安全风险。使用环境变量或配置文件可以将敏感信息与代码分离，便于在项目上线时进行管理。

这里提供一个在 Linux 下设置环境变量示例：

export DB_PASSWORD='your_secure_password'

在 Python 中，我们也可以使用 os.getenv() 获取环境变量的值，如下面示例所示。

import os

# 获取环境变量 DB_HOST 的值，如果不存在，则使用 "localhost" 作为默认值
db_host = os.getenv("DB_HOST", "localhost")
print("数据库主机地址:", db_host)

# 同样可以获取其他环境变量，如数据库端口
db_port = os.getenv("DB_PORT", "3306")  # 注意：此处获取的值是字符串，如果需要数字可进行转换
print("数据库端口:", db_port)

练习：

假设您正在负责管理一个产品反馈系统，需要收集用户对产品的使用体验和改进建议。

这个系统应能记录用户提交的反馈，包括用户信息、反馈内容、评分、提交时间、产品编号（product_id）以及反馈类别（例如：功能建议、Bug报告、性能问题等）。

例如，这是一条可能的数据：

用户ID：102
用户姓名：Bob
用户反馈内容：“产品界面很直观，但加载速度有点慢。”
产品编号：250802
用户评级：3颗星
反馈提交时间：2025-02-20 10:45:00
反馈信息类别：性能问题

根据上述业务需求，请编写提示词，要求 AI 完成以下任务：

设计一份数据表字段列表。注意：除了基本信息外，如果需要，也可以让 AI 建议其他有助于数据分析的字段。
生成一段 Python 代码，使用 PyMySQL 连接 MySQL 数据库，并自动创建一个数据库（如 feedback_db）和上述数据表。

你也可以自己尝试撰写代码。试着比较你们写的内容有什么不同。

AI 助教

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