🗄️ 数据库原理与应用在线学习

# 模拟数据库表结构 # 学生表 students_table = { "表名": "students", "字段": ["学号", "姓名", "年龄", "专业"], "记录": [ ["001", "张三", 20, "计算机"], ["002", "李四", 21, "数学"], ["003", "王五", 19, "物理"] ] } print(f"表名：{students_table['表名']}") print(f"字段：{', '.join(students_table['字段'])}") print(f"记录数：{len(students_table['记录'])}") print("\n表数据：") print(-"*50) for record in students_table["记录"]: print(f" {record[0]} | {record[1]} | {record[2]}岁 | {record[3]}")

# 模拟SQL查询操作 # 产品表数据 products = [ {"id": 1, "name": "笔记本", "price": 5000, "category": "电子产品"}, {"id": 2, "name": "手机", "price": 3000, "category": "电子产品"}, {"id": 3, "name": "桌子", "price": 800, "category": "家具"} ] # SELECT * FROM products print("查询所有产品：") print(="*50) for p in products: print(f" {p['id']} | {p['name']} | ¥{p['price']} | {p['category']}") # SELECT name, price FROM products print("\n只查询名称和价格：") print(="*50) for p in products: print(f" {p['name']}: ¥{p['price']}") # SELECT DISTINCT category FROM products categories = list(set(p["category"] for p in products)) print(f"\n产品类别：{', '.join(categories)}")

# 员工表数据 employees = [ {"id": 1, "name": "张三", "salary": 8000, "dept": "技术部"}, {"id": 2, "name": "李四", "salary": 12000, "dept": "技术部"}, {"id": 3, "name": "王五", "salary": 6000, "dept": "销售部"}, {"id": 4, "name": "赵六", "salary": 15000, "dept": "技术部"} ] # WHERE salary > 8000 print("工资大于8000的员工：") high_salary = [e for e in employees if e["salary"] > 8000] for e in high_salary: print(f" {e['name']}: ¥{e['salary']}") # WHERE dept = '技术部' print("\n技术部员工：") tech_dept = [e for e in employees if e["dept"] == "技术部"] for e in tech_dept: print(f" {e['name']}: ¥{e['salary']}") # ORDER BY salary DESC print("\n按工资降序排列：") sorted_emp = sorted(employees, key=lambda x: x["salary"], reverse=True) for e in sorted_emp: print(f" {e['name']}: ¥{e['salary']}")

# 订单表 orders = [ {"order_id": 1, "customer_id": 101, "amount": 500}, {"order_id": 2, "customer_id": 102, "amount": 800}, {"order_id": 3, "customer_id": 101, "amount": 300} ] # 客户表 customers = [ {"customer_id": 101, "name": "张三"}, {"customer_id": 102, "name": "李四"}, {"customer_id": 103, "name": "王五"} ] # INNER JOIN print("订单与客户信息（INNER JOIN）：") print(="*50) for order in orders: for customer in customers: if order["customer_id"] == customer["customer_id"]: print(f" 订单{order['order_id']} | {customer['name']} | ¥{order['amount']}") # LEFT JOIN（所有客户，包括没有订单的） print("\n所有客户及其订单（LEFT JOIN）：") print(="*50) for customer in customers: customer_orders = [o for o in orders if o["customer_id"] == customer["customer_id"]] if customer_orders: for o in customer_orders: print(f" {customer['name']} | 订单{o['order_id']} | ¥{o['amount']}") else: print(f" {customer['name']} | 无订单")

# 销售数据 sales = [ {"product": "A", "region": "北区", "amount": 1000}, {"product": "B", "region": "北区", "amount": 1500}, {"product": "A", "region": "南区", "amount": 800}, {"product": "B", "region": "南区", "amount": 1200}, {"product": "A", "region": "北区", "amount": 900} ] # 聚合函数 amounts = [s["amount"] for s in sales] print("销售数据统计：") print(="*40) print(f"记录数(COUNT)：{len(sales)}") print(f"总销售额(SUM)：¥{sum(amounts)}") print(f"平均销售额(AVG)：¥{sum(amounts)/len(amounts):.0f}") print(f"最高销售额(MAX)：¥{max(amounts)}") print(f"最低销售额(MIN)：¥{min(amounts)}") # GROUP BY region print("\n按地区分组统计：") print(="*40) region_stats = {} for s in sales: region = s["region"] if region not in region_stats: region_stats[region] = [] region_stats[region].append(s["amount"]) for region, amounts in region_stats.items(): print(f"{region}: 订单数{len(amounts)}, 总销售额¥{sum(amounts)}, 平均¥{sum(amounts)/len(amounts):.0f}")

# 初始数据 students = [ {"id": 1, "name": "张三", "score": 85}, {"id": 2, "name": "李四", "score": 90} ] print("初始学生数据：") for s in students: print(f" {s['id']} | {s['name']} | {s['score']}分") # INSERT - 插入新记录 new_student = {"id": 3, "name": "王五", "score": 88} students.append(new_student) print(f"\n插入新学生：{new_student['name']}") # UPDATE - 修改记录 for s in students: if s["id"] == 1: s["score"] = 87 print(f"更新学生{s['name']}的成绩为{s['score']}分") # DELETE - 删除记录 students = [s for s in students if s["id"] != 2] print("删除学生李四") # 最终结果 print("\n最终学生数据：") for s in students: print(f" {s['id']} | {s['name']} | {s['score']}分")

# 数据库设计示例 - 学生选课系统 # 遵循第三范式的设计 # 学生表 students = [ {"student_id": "S001", "name": "张三", "major": "计算机"}, {"student_id": "S002", "name": "李四", "major": "数学"} ] # 课程表 courses = [ {"course_id": "C001", "name": "数据库", "credit": 3}, {"course_id": "C002", "name": "数据结构", "credit": 4} ] # 选课表（关联表） enrollments = [ {"student_id": "S001", "course_id": "C001", "grade": 85}, {"student_id": "S001", "course_id": "C002", "grade": 90}, {"student_id": "S002", "course_id": "C001", "grade": 88} ] print("学生选课系统 - 数据库设计示例") print(="*50) # 查询学生选课情况 print("\n学生选课详情：") for e in enrollments: student = next(s for s in students if s["student_id"] == e["student_id"]) course = next(c for c in courses if c["course_id"] == e["course_id"]) print(f" {student['name']} 选修 {course['name']} 成绩：{e['grade']}分") # 统计每个学生的平均成绩 print("\n学生平均成绩：") for student in students: grades = [e["grade"] for e in enrollments if e["student_id"] == student["student_id"]] avg_grade = sum(grades) / len(grades) print(f" {student['name']}: {avg_grade:.1f}分")