导入数据源
为了将这些数据导入到MySQL数据库中，您需要遵循以下步骤。首先，确保您的系统中已经安装了MySQL。然后，您可以使用Python和pandas库结合sqlalchemy来导入数据。这里是一个详细的指导步骤：
第一步：安装必要的库
如果您还没有安装pandas和sqlalchemy，可以通过以下命令安装：
pip install pandas sqlalchemy
第二步：创建数据库
在MySQL中创建一个新的数据库，例如命名为school_management：
CREATE DATABASE school_management;
第三步：使用python生成数据
import pandas as pd
import numpy as np
# 设置随机种子
np.random.seed(0)
# 生成学生表数据
students_data = {
"student_id": np.arange(1, 2001),
"name": [f"Student_{i}" for i in range(1, 2001)],
"age": np.random.randint(18, 25, size=2000),
"gender": np.random.choice(['Male', 'Female'], size=2000)
}
students_df = pd.DataFrame(students_data)
# 生成教师表数据
teachers_data = {
"teacher_id": np.arange(1, 101),
"name": [f"Teacher_{i}" for i in range(1, 101)],
"subject": np.random.choice(['Mathematics', 'Physics', 'Chemistry', 'Biology', 'Literature'], size=100),
"salary": np.random.randint(30000, 80000, size=100)
}
teachers_df = pd.DataFrame(teachers_data)
# 生成课程表数据
courses_data = {
"course_id": np.arange(1, 501),
"course_name": [f"Course_{i}" for i in range(1, 501)],
"teacher_id": np.random.choice(teachers_df['teacher_id'], size=500)
}
courses_df = pd.DataFrame(courses_data)
# 生成成绩表数据
grades_data = {
"grade_id": np.arange(1, 10001),
"student_id": np.random.choice(students_df['student_id'], size=10000),
"course_id": np.random.choice(courses_df['course_id'], size=10000),
"score": np.random.randint(50, 100, size=10000)
}
grades_df = pd.DataFrame(grades_data)
# 生成活动表数据
activities_data = {
"activity_id": np.arange(1, 201),
"activity_name": [f"Activity_{i}" for i in range(1, 201)],
"organizer": np.random.choice(['Student Council', 'Science Club', 'Math Club', 'Literature Society'], size=200)
}
activities_df = pd.DataFrame(activities_data)
# 显示生成的表数据信息
students_df.info(), teachers_df.info(), courses_df.info(), grades_df.info(), activities_df.info()
-
学生表（Students） - 包含2000名学生的信息：
- student_id: 学生ID（1到2000）
- name: 学生姓名（Student_1到Student_2000）
- age: 年龄（18到24岁之间）
- gender: 性别（Male或Female）
-
教师表（Teachers） - 包含100名教师的信息：
- teacher_id: 教师ID（1到100）
- name: 教师姓名（Teacher_1到Teacher_100）
- subject: 所教科目（Mathematics, Physics, Chemistry, Biology, Literature）
- salary: 工资（30000到80000之间）
-
课程表（Courses） - 包含500个课程的信息：
- course_id: 课程ID（1到500）
- course_name: 课程名称（Course_1到Course_500）
- teacher_id: 教师ID，表示哪位教师教授此课程
-
成绩表（Grades） - 包含10000条成绩记录：
- grade_id: 成绩ID（1到10000）
- student_id: 学生ID，表示此成绩属于哪位学生
- course_id: 课程ID，表示此成绩属于哪门课程
- score: 分数（50到99分之间）
-
活动表（Activities） - 包含200项活动的信息：
- activity_id: 活动ID（1到200）
- activity_name: 活动名称（Activity_1到Activity_200）
- organizer: 组织者（Student Council, Science Club, Math Club, Literature Society）
第四步：编写Python脚本来导入数据
import pandas as pd
from sqlalchemy import create_engine
# 连接到MySQL数据库（替换username和password为您的用户名和密码）
engine = create_engine('mysql+pymysql://username:password@localhost/school_management')
# 假设已经有DataFrame：students_df, teachers_df, courses_df, grades_df, activities_df
# 将DataFrame导入到MySQL中
students_df.to_sql('students', con=engine, index=False, if_exists='replace')
teachers_df.to_sql('teachers', con=engine, index=False, if_exists='replace')
courses_df.to_sql('courses', con=engine, index=False, if_exists='replace')
grades_df.to_sql('grades', con=engine, index=False, if_exists='replace')
activities_df.to_sql('activities', con=engine, index=False, if_exists='replace')
print("数据已成功导入MySQL数据库!")
注意事项
- 确保在运行导入脚本之前，您已经在MySQL中创建了相应的数据库（例如
school_management）。 - 在
create_engine函数中，需要替换username和password为您的MySQL用户名和密码。 - 根据您的MySQL服务器配置，可能需要修改连接字符串中的其他参数，如主机名和端口。
数据库表字段说明
-
学生表（Students）
- student_id (主键)
- name
- age
- gender
-
教师表（Teachers）
- teacher_id (主键)
- name
- subject
- salary
-
课程表（Courses）
- course_id (主键)
- course_name
- teacher_id (外键)
-
成绩表（Grades）
- grade_id (主键)
- student_id (外键)
- course_id (外键)
- score
-
活动表（Activities）
- activity_id (主键)
- activity_name
- organizer
练习题
为了帮助你通过具体的查询强化和巩固MySQL的学习，以下是基于你提供的数据结构，涵盖不同MySQL知识点的50个练习题目。这些题目将帮助你实践各种查询技巧，包括基本查询、排序、分组、连接、子查询等。
基本查询 (SELECT)
- 查询所有学生的姓名和年龄。
- 查询教师表中所有教师的姓名和薪资。
- 选出所有课程的课程名称和对应的教师ID。
- 从成绩表中查询所有学生ID和其对应的分数。
- 显示活动表中所有活动的名称。
条件查询 (WHERE)
- 查询18岁学生的姓名和学生ID。
- 查找薪资高于50000的教师的姓名和科目。
- 查询成绩在90分以上的学生ID和课程ID。
- 找出所有“女性”学生的姓名和年龄。
- 选出所有由“Science Club”组织的活动。
排序查询 (ORDER BY)
- 按年龄从小到大排序学生的姓名和年龄。
- 按薪资从高到低显示教师的姓名和薪资。
- 查询所有课程，结果按课程ID降序排序。
- 查询所有成绩，按分数降序排序。
- 按活动ID排序所有活动的名称。
分组查询 (GROUP BY)
- 按性别分组，计算每组学生的平均年龄。
- 按教师科目分组，显示每个科目的教师数量。
- 按课程ID分组，计算每门课程的平均分数。
- 根据活动的组织者分组，统计每个组织者组织的活动数。
- 按学生年龄分组，统计每个年龄段的学生人数。
连接查询 (JOIN)
- 使用内连接查询每个学生的姓名及其所有成绩。
- 使用左外连接显示所有教师及其教授的课程名。
- 使用右外连接找出所有课程及其可能的教师姓名。
- 使用全外连接列出所有学生和他们可能参加的活动名称。
- 内连接查询教师的最高薪资和对应的教师姓名。
子查询 (Subquery)
- 查询成绩高于学生平均成绩的所有成绩记录。
- 找出教师薪资高于教师平均薪资的教师姓名和薪资。
- 查询没有成绩记录的学生的姓名。
- 找出有超过5门课程的教师的姓名。
- 查询参加活动次数最多的学生姓名。
范围查询 (BETWEEN, IN)
- 查询年龄在20到22岁之间的学生姓名和年龄。
- 找出学号在100到200之间的学生的姓名。
- 查询在物理或数学科目教学的教师姓名。
- 查找分数在60至80分之间的学生的成绩。
- 查询参与了学生会或数学俱乐部活动的所有活动名称。
聚合查询 (AGGREGATE)
- 计算学生的总人数。
- 求出教师的平均薪资。
- 找出最高和最低的学生成绩。
- 统计每门课程的学生人数。
- 计算参加活动最多的组织者的活动次数。
联合查询 (
UNION, UNION ALL)
- 列出所有学生和教师的姓名（使用UNION）。
- 列出所有课程和活动的名称（使用UNION ALL）。
- 合并不同性别学生的平均年龄列表。
- 合并查询教师薪资高于平均值和低于平均值的教师信息。
- 显示有成绩记录和无成绩记录学生的姓名。
递归查询 (WITH RECURSIVE)
- （如果适用于你的MySQL版本）使用递归查询模拟一个简单的组织层级。
空间数据查询 (Spatial Data)
- （如果适用于你的数据）查询特定地理位置附近的学校活动。
事务查询 (Transactions)
- 编写一个事务处理例程，用于同时更新学生的成绩和课程的平均分。
存储过程和函数
- 创建一个存储过程，用于计算并返回任意学生的平均成绩。
- 创建一个函数，用于检查教师是否符合特定的薪资标准。
1111
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
