43-44学时:编程实践数据库

MySQL数据库入门：用Navicat管理健康养老数据

在这4个学时中，我们将学习 MySQL 数据库基础，使用 Navicat for MySQL 可视化客户端管理数据库，结合健康养老数据的前后端项目（Python Flask API + HTML前端）。你将掌握数据库理论，操作MySQL表，并将数据库部署到阿里云ECS（通过宝塔面板管理）。课程适合初学者，逐步讲解：

MySQL核心概念和SQL语法。
用Navicat创建、查询、修改数据库。
集成健康数据API，管理姓名、血压、心率、血糖、体重、步数、时间。
连接阿里云MySQL。

课程安排

学时1：理论课 - 了解MySQL、关系型数据库、SQL。
学时2：演示课 - 老师用Navicat搭建健康数据数据库，连接Flask API。
学时3-4：实践课 - 动手操作数据库，优化表结构，连接阿里云MySQL。

开发环境

IDE：PyCharm Community Edition（用于API集成）。
数据库：MySQL（本地和阿里云）。
客户端：Navicat for MySQL（下载Navicat，免费试用版）。
浏览器：Chrome，测试API。
本地环境设置：
1. 安装 MySQL，宝塔面板上点一下就可以安装了：
  - Windows：MySQL Community Server (mysql.com)。
  - Mac：brew install mysql。
  - Linux：sudo apt install mysql-server。
  - 启动：mysql -u root -p，创建数据库：CREATE DATABASE health_db;
2. 安装 Navicat：
  - 下载安装Navicat，启动后连接本地MySQL（host: localhost, user: root, password: 你的密码）。
  - 测试连接，创建 health_db 数据库。
3. PyCharm 项目：
  - 使用现有“HealthAPI”项目（含 app.py, config.py）。
  - 确保依赖：pip install flask flask-cors mysql-connector-python。
4. 前端：
  - 使用现有 index.html（健康数据网页）。
阿里云环境：
- ECS（Ubuntu 20.04）已安装宝塔面板。
- 宝塔管理MySQL、Nginx、Python项目。
- 登录宝塔：http://<公网IP>:8888。

学时1：理论课 - 认识MySQL和Navicat

1.1 什么是MySQL？

MySQL是开源关系型数据库，存储和管理结构化数据：

用途：保存健康数据（如姓名、血压、心率）。
特点：高效、支持多用户、适合Web应用。
与API集成：Flask通过 mysql-connector-python 连接MySQL，执行查询。

1.2 关系型数据库

表：类似Excel，存储数据（如 health_data 表）。
字段：表中的列（如 name, blood_pressure）。
记录：表中的行（如“张三, 120/80, 70”）。
主键：唯一标识记录的字段（如 id）。
外键：连接多表（本课程暂不涉及）。

例子：health_data 表结构：

id	name	blood_pressure	heart_rate	blood_sugar	weight	steps	timestamp
1	张三	120/80	70	5.5	70.5	10000	2025-05-10 10:00:00

1.3 SQL语法

SQL（结构化查询语言）操作MySQL：

创建表：

CREATE TABLE health_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    blood_pressure VARCHAR(20),
    heart_rate INT,
    blood_sugar FLOAT,
    weight FLOAT,
    steps INT,
    timestamp VARCHAR(20)
);

插入数据：

INSERT INTO health_data (name, blood_pressure, heart_rate, blood_sugar, weight, steps, timestamp)
VALUES ('张三', '120/80', 70, 5.5, 70.5, 10000, '2025-05-10 10:00:00');

查询数据：
```
SELECT * FROM health_data;
SELECT name, heart_rate FROM health_data WHERE heart_rate > 80;
```
更新数据：
```
UPDATE health_data SET heart_rate = 75 WHERE id = 1;
```
删除数据：
```
DELETE FROM health_data WHERE id = 1;
DELETE FROM health_data; -- 清空表
```

1.4 Navicat

Navicat是可视化MySQL客户端：

功能：创建数据库/表，执行SQL，导入/导出数据。
优势：图形界面，适合初学者，调试方便。
与宝塔集成：Navicat可连接阿里云MySQL。

1.5 健康数据数据库目标

创建 health_db 数据库，包含 health_data 表。
支持API操作：插入（POST）、查询（GET）、清空（DELETE）。
用Navicat管理本地和阿里云数据库。

学时2：演示课 - 用Navicat搭建数据库并集成API

2.1 目标

老师展示：

用Navicat创建 health_db 和 health_data 表。
连接Flask API，测试插入、查询、清空。
验证前端交互（index.html）。

2.2 代码和操作

现有代码（参考后端教材）

PyCharm项目：HealthAPI（app.py, config.py）。

config.py：

MYSQL_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': 'your_mysql_password',  # 替换为本地MySQL密码
    'database': 'health_db'
}

app.py：保持不变（见后端教材）。
index.html：保持不变（前端通过 http://localhost:5000/api/health 交互）。

Navicat操作

安装Navicat：
- 下载Navicat for MySQL，安装并启动。
连接本地MySQL：
- 打开Navicat，点击“连接” > “MySQL”。
- 配置：
  - 连接名：LocalMySQL
  - 主机：localhost
  - 用户名：root
  - 密码：你的MySQL密码
- 测试连接，成功后保存。
创建数据库：
- 右键“连接” > “新建数据库”，名称：health_db。
创建表：
- 打开 health_db，点击“表” > “新建表”。
- 添加字段（图形界面）：
  - id: INT, 主键，自动递增
  - name: VARCHAR(50)
  - blood_pressure: VARCHAR(20)
  - heart_rate: INT
  - blood_sugar: FLOAT
  - weight: FLOAT
  - steps: INT
  - timestamp: VARCHAR(20)
- 保存，表名：health_data。
插入测试数据：
- 打开 health_data 表，点击“+”添加记录：
  - name: 张三
  - blood_pressure: 120/80
  - heart_rate: 70
  - blood_sugar: 5.5
  - weight: 70.5
  - steps: 10000
  - timestamp: 2025-05-10 10:00:00
运行API：
- 在PyCharm，运行 app.py（右键 > Run）。
- Chrome访问 http://localhost:5000/api/health，应看到：
```
[{"id": 1, "name": "张三", "blood_pressure": "120/80", ...}]
```
测试前端：
- 打开 index.html（Live Server）。
- 查看表格（显示张三数据）。
- 添加新数据（李四，120/80, 75, 6.0, 65.0, 8000），点击“添加”。
- 在Navicat刷新 health_data，确认新记录。
- 点击“清空表格”，Navicat确认表为空。

调试提示:

Navicat连接失败：检查MySQL服务（mysql -u root -p），密码正确。
API无数据：确认 config.py 密码，Navicat检查 health_db 是否有表。
前端错误：Chrome F12“Network”检查API状态。

学时3-4：实践课 - 优化数据库并连接阿里云

3.1 目标

用Navicat执行高级查询和表优化。
添加索引和触发器。
连接阿里云MySQL（宝塔面板），集成API。

3.2 准备工作

本地环境：
- PyCharm“HealthAPI”项目（app.py, config.py）。
- Navicat连接本地MySQL，health_db 可用。
- 前端 index.html 配置正确。
阿里云：
- 登录宝塔：http://<公网IP>:8888。
- 确认MySQL、Nginx、Python项目管理器已安装。
起点：
- 复制 app.py 为 my_app.py, config.py 为 my_config.py。
- Navicat备份 health_db（右键 > 导出）。

3.3 任务分解

任务1：高级查询

目标：用Navicat查询高心率（>80）和最近数据。
操作：
- 打开Navicat，连接 LocalMySQL，进入 health_db。
- 点击“查询” > “新建查询”，输入：
```
SELECT * FROM health_data WHERE heart_rate > 80;
```
  - 运行，查看结果。
- 新查询：
```
SELECT * FROM health_data ORDER BY timestamp DESC LIMIT 5;
```
  - 运行，查看最近5条记录。
- 在PyCharm，修改 my_app.py，添加端点：
```
@app.route('/api/health/high_heart_rate', methods=['GET'])
def get_high_heart_rate():
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': '数据库连接失败'}), 500
    cursor = conn.cursor(dictionary=True)
    cursor.execute('SELECT * FROM health_data WHERE heart_rate > 80')
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    return jsonify(rows)
```
- 测试：Chrome访问 http://localhost:5000/api/health/high_heart_rate。

任务2：优化表结构

目标：添加索引，创建触发器记录插入时间。
操作：
- 索引：
  - 在Navicat，打开 health_data 表设计。
  - 点击“索引”，添加索引：
    - 名称：idx_name
    - 字段：name
  - 保存，提升查询效率。
- 触发器：
  - 点击“触发器” > “新建触发器”。
  - 名称：set_timestamp
  - 时机：Before Insert
  - 语句：
```
SET NEW.timestamp = NOW();
```
  - 保存，自动设置插入时间。
- 更新 my_app.py，移除手动设置 timestamp：
```
# 在 add_health_data() 中
# 删除 timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
cursor.execute('''INSERT INTO health_data (name, blood_pressure, heart_rate, blood_sugar, weight, steps)
                 VALUES (%s, %s, %s, %s, %s, %s)''',
              (name, blood_pressure, heart_rate, blood_sugar, weight, steps))
```
- 测试：用前端添加数据，Navicat检查 timestamp 自动生成。

任务3：连接阿里云MySQL

目标：用Navicat连接宝塔MySQL，部署API和前端。
操作：
1. 宝塔配置MySQL：
  - 登录宝塔，导航“数据库” > “添加数据库”。
  - 名称：health_db，用户：health_user，密码：Health123!。
  - 记录凭据。
2. Navicat连接阿里云：
  - 新建连接：
    - 连接名：AliyunMySQL
    - 主机：<公网IP>
    - 用户名：health_user
    - 密码：Health123!
  - 测试连接（确保宝塔“安全”开放3306端口）。
  - 创建 health_data 表（同本地，复制SQL或用Navicat设计）。
3. 部署API：
  - 更新 my_config.py：
```
MYSQL_CONFIG = {
    'host': '<公网IP>',
    'user': 'health_user',
    'password': 'Health123!',
    'database': 'health_db'
}
```
  - 上传 my_app.py, my_config.py, requirements.txt 到宝塔 /www/wwwroot/health_api（PyCharm SFTP）。
  - 宝塔“Python项目管理器”：
    - 添加项目：路径 /www/wwwroot/health_api，启动文件 my_app.py，端口5000。
    - 安装依赖，启动项目。
4. 部署前端：
  - 上传 index.html 到 /www/wwwroot/health_api。
  - 宝塔“网站” > “添加站点”：
    - 根目录：/www/wwwroot/health_api
    - 端口：80
    - 首页：index.html
  - 更新 index.html，API地址为 http://<公网IP>:5000。
5. 测试：
  - 访问 http://<公网IP>，测试前端。
  - 添加数据，Navicat（AliyunMySQL）检查 health_data。
  - 访问 http://<公网IP>:5000/api/health/high_heart_rate。