第2章:数据基石:使用PHP操作MySQL数据库
章节介绍
学习目标
在本章结束时,你将能够:
- 理解数据库和MySQL的基本概念
- 使用SQL语言执行基础的数据库操作
- 使用PHP的PDO扩展安全地连接MySQL数据库
- 使用预处理语句防止SQL注入攻击
- 执行完整的CRUD(增删改查)操作并处理结果
本章的作用与衔接
在第1章中,你学会了PHP的基本语法,如同掌握了建造房屋所需的砖块和工具.本章将带你认识并学习使用一个强大而有序的"仓库"——数据库,来持久化存储和管理你的数据.数据库是现代Web应用的核心,无论是用户信息、文章内容,还是商品数据,都离不开它.掌握PHP操作数据库,是构建动态网站和API接口的绝对基石.通过本章的学习,你将获得让PHP"说话"并操作数据的能力,为后续构建能够与数据库交互的API接口(第4章)打下坚实基础.
主要内容概览
本章将首先带你理解数据库的基础概念和SQL语言,然后重点学习PHP中目前最推荐、最安全的数据库操作方式——PDO扩展.我们将深入探讨一个至关重要的安全话题:SQL注入攻击与防护.最后,通过一个完整的实战项目,将理论知识转化为实际开发技能.
核心概念讲解
2.1 数据库与MySQL基础
什么是数据库？
数据库(Database)是一个有组织的数据集合,它可以被高效地访问、管理和更新.你可以把它想象成一个高度智能化的电子文件柜,能够按照特定规则快速存储和检索信息.
核心概念解析:
- 数据库管理系统(DBMS):管理数据库的软件系统,MySQL就是其中一种.
- 表(Table):数据库中存储特定类型数据的结构化清单,类似于Excel工作表.例如,一个
users表存储所有用户信息. - 字段(Column):表中的列,定义了存储数据的类型和约束.例如
id、username、email. - 记录(Row):表中的一行,代表一条具体的数据.例如一个具体的用户信息.
- 主键(Primary Key):唯一标识表中每条记录的字段,值不能重复且不能为NULL.
MySQL简介:
MySQL是一个开源的关系型数据库管理系统(RDBMS),使用SQL(结构化查询语言)进行数据管理.它因其性能高、成本低、可靠性好、社区活跃而成为Web开发中最流行的数据库之一,常与PHP组成经典的"LAMP"(Linux, Apache, MySQL, PHP)开发栈.
2.2 SQL语言基础
SQL(Structured Query Language)是用于与关系数据库通信的标准语言.以下是必须掌握的五大核心操作:
1. 创建表(CREATE TABLE)
-- 创建一个用户表CREATETABLEusers(idINTAUTO_INCREMENTPRIMARYKEY,usernameVARCHAR(50)NOTNULLUNIQUE,emailVARCHAR(100)NOTNULLUNIQUE,password_hashVARCHAR(255)NOTNULL,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);2. 插入数据(INSERT)
-- 向users表插入一条记录INSERTINTOusers(username,email,password_hash)VALUES('john_doe','john@example.com','hashed_password_123');3. 查询数据(SELECT)
-- 查询所有用户SELECT*FROMusers;-- 查询特定字段SELECTid,username,emailFROMusers;-- 带条件的查询SELECT*FROMusersWHEREusername='john_doe';-- 排序和限制SELECT*FROMusersORDERBYcreated_atDESCLIMIT10;4. 更新数据(UPDATE)
-- 更新特定用户的信息UPDATEusersSETemail='new_email@example.com'WHEREid=1;5. 删除数据(DELETE)
-- 删除特定记录DELETEFROMusersWHEREid=1;-- 清空表(谨慎使用！)-- DELETE FROM users;2.3 PHP数据库扩展:为何选择PDO？
PHP提供了多种方式连接MySQL数据库,主要有三种扩展:
- mysql扩展:PHP早期扩展,已在PHP 7.0中被移除,绝对不要使用.
- mysqli扩展:MySQL改进扩展,支持面向过程和面向对象两种方式.
- PDO(PHP Data Objects):数据访问抽象层,支持多种数据库.
为什么强烈推荐PDO？
| 特性 | PDO优势 |
|---|---|
| 数据库兼容性 | 同一套代码稍作调整即可支持MySQL、PostgreSQL、SQLite等多种数据库 |
| 安全性 | 原生支持预处理语句,有效防止SQL注入 |
| 错误处理 | 提供多种错误处理模式,异常模式特别适合现代应用 |
| 面向对象 | 完全面向对象的接口,代码更清晰 |
| 性能 | 预处理语句可重复使用,提升重复查询性能 |
2.4 预处理语句:安全防线的核心
什么是SQL注入？
SQL注入是OWASP Top 10长期位列前三的严重安全漏洞.攻击者通过构造特殊的输入,改变原有SQL语句的逻辑,从而执行恶意操作.
攻击原理示例:
假设登录SQL语句为:
SELECT*FROMusersWHEREusername='$username'ANDpassword='$password'如果用户输入:username = 'admin'--(注意最后的空格和注释符)
语句变为:
SELECT*FROMusersWHEREusername='admin'-- ' AND password = 'anything'--之后的内容被注释掉,攻击者无需密码即可登录admin账户！
预处理语句的工作原理:
- 准备阶段:SQL语句模板被发送到数据库服务器,参数用占位符(
?或:name)表示 - 编译阶段:数据库分析、编译和优化SQL模板,但不执行
- 绑定阶段:将实际参数值绑定到占位符
- 执行阶段:使用绑定的值执行已编译的语句
关键安全优势:
- 数据和指令分离:用户输入永远被视为数据,不会被解释为SQL代码
- 类型安全:参数会被正确转义和处理
- 性能提升:同一模板多次执行时只需编译一次
代码示例
示例1:建立数据库连接与错误处理
<?php/** * 使用PDO连接MySQL数据库 * 演示连接、错误处理基本配置 */// 数据库配置信息$host='localhost';// 数据库服务器地址$dbname='php_api_demo';// 数据库名称$username='root';// 数据库用户名$password='';// 数据库密码(根据实际情况填写)$charset='utf8mb4';// 字符集,支持最全的UTF-8(包括emoji)// PDO连接选项配置$options=[// 设置错误模式:异常模式,PDO出错时抛出异常PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,// 设置默认获取模式:关联数组,返回的每行数据是字段名=>值的关联数组PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC,// 禁用预处理语句模拟,确保使用真正的预处理(安全重要！)PDO::ATTR_EMULATE_PREPARES=>false,];// 构建DSN(数据源名称)$dsn="mysql:host=$host;dbname=$dbname;charset=$charset";try{// 创建PDO实例(连接数据库)$pdo=newPDO($dsn,$username,$password,$options);echo"数据库连接成功！<br>";// 测试查询:获取当前MySQL版本$stmt=$pdo->query('SELECT VERSION() as version');$result=$stmt->fetch();echo"MySQL版本:".$result['version']."<br>";}catch(PDOException$e){// 捕获连接或查询中的异常// 注意:生产环境中应记录到日志,而不是直接显示给用户die("数据库连接失败: ".$e->getMessage());}// 连接成功后的操作示例// 在实际应用中,$pdo对象会被保存在变量中供后续使用预期输出:
数据库连接成功！ MySQL版本:8.0.xx示例2:创建表与插入数据
<?php/** * 创建用户表并插入初始数据 * 演示CREATE TABLE和INSERT操作 */require_once'config.php';// 假设数据库配置在config.php中try{// 创建PDO连接(使用上例中的配置)$pdo=newPDO($dsn,$username,$password,$options);// 1. 创建users表(如果不存在)$createTableSQL=" CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL UNIQUE, full_name VARCHAR(100), age INT, city VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ";$pdo->exec($createTableSQL);echo"1. users表创建成功<br>";// 2. 使用预处理语句插入单条数据(安全方式)$insertSQL="INSERT INTO users (username, email, full_name, age, city) VALUES (:username, :email, :full_name, :age, :city)";$stmt=$pdo->prepare($insertSQL);// 绑定参数并执行$userData=['username'=>'alice_wonder','email'=>'alice@example.com','full_name'=>'Alice Johnson','age'=>28,'city'=>'New York'];$stmt->execute($userData);echo"2. 用户 Alice 插入成功,ID: ".$pdo->lastInsertId()."<br>";// 3. 插入多条数据$users=[['username'=>'bob_smith','email'=>'bob@example.com','full_name'=>'Bob Smith','age'=>32,'city'=>'London'],['username'=>'carol_davis','email'=>'carol@example.com','full_name'=>'Carol Davis','age'=>25,'city'=>'Tokyo']];foreach($usersas$user){$stmt->execute($user);echo" 用户 ".$user['username']." 插入成功<br>";}echo"<br>数据初始化完成！";}catch(PDOException$e){die("操作失败: ".$e->getMessage());}预期输出:
1. users表创建成功 2. 用户 Alice 插入成功,ID: 1 用户 bob_smith 插入成功 用户 carol_davis 插入成功 数据初始化完成！示例3:查询数据的多种方式
<?php/** * 查询数据的多种方式 * 演示SELECT查询、结果集处理 */require_once'config.php';try{$pdo=newPDO($dsn,$username,$password,$options);echo"<h3>用户数据查询演示</h3>";// 方法1:查询所有用户(简单查询)echo"<h4>1. 查询所有用户:</h4>";$stmt=$pdo->query("SELECT id, username, email, full_name, age, city FROM users");// 获取所有结果作为关联数组$allUsers=$stmt->fetchAll();if(empty($allUsers)){echo"暂无用户数据<br>";}else{echo"共找到 ".count($allUsers)." 个用户:<br>";foreach($allUsersas$user){echo"ID:{$user['id']}, 用户名:{$user['username']}, 姓名:{$user['full_name']}, 城市:{$user['city']}<br>";}}// 方法2:带条件的查询(使用命名占位符)echo"<h4>2. 查询年龄大于25的用户:</h4>";$sql="SELECT id, username, full_name, age, city FROM users WHERE age > :min_age ORDER BY age DESC";$stmt=$pdo->prepare($sql);$stmt->execute(['min_age'=>25]);while($user=$stmt->fetch()){echo"{$user['full_name']}({$user['username']}), 年龄:{$user['age']}, 城市:{$user['city']}<br>";}// 方法3:查询单条记录echo"<h4>3. 查询用户名为'alice_wonder'的用户详情:</h4>";$sql="SELECT * FROM users WHERE username = ?";$stmt=$pdo->prepare($sql);$stmt->execute(['alice_wonder']);$user=$stmt->fetch();if($user){echo"找到用户: <br>";echo"ID:{$user['id']}<br>";echo"用户名:{$user['username']}<br>";echo"邮箱:{$user['email']}<br>";echo"全名:{$user['full_name']}<br>";echo"年龄:{$user['age']}<br>";echo"城市:{$user['city']}<br>";echo"创建时间:{$user['created_at']}<br>";}else{echo"用户不存在<br>";}// 方法4:使用fetchColumn获取单个值echo"<h4>4. 统计用户总数:</h4>";$count=$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();echo"用户总数: ".$count."<br>";// 方法5:使用fetchAll(PDO::FETCH_COLUMN)获取单列echo"<h4>5. 获取所有用户名列表:</h4>";$stmt=$pdo->query("SELECT username FROM users ORDER BY username");$usernames=$stmt->fetchAll(PDO::FETCH_COLUMN,0);echo"用户名列表: ".implode(', ',$usernames)."<br>";}catch(PDOException$e){die("查询失败: ".$e->getMessage());}预期输出:
用户数据查询演示 1. 查询所有用户: 共找到 3 个用户: ID: 1, 用户名: alice_wonder, 姓名: Alice Johnson, 城市: New York ID: 2, 用户名: bob_smith, 姓名: Bob Smith, 城市: London ID: 3, 用户名: carol_davis, 姓名: Carol Davis, 城市: Tokyo 2. 查询年龄大于25的用户: Bob Smith (bob_smith), 年龄: 32, 城市: London Alice Johnson (alice_wonder), 年龄: 28, 城市: New York 3. 查询用户名为'alice_wonder'的用户详情: 找到用户: ID: 1 用户名: alice_wonder 邮箱: alice@example.com 全名: Alice Johnson 年龄: 28 城市: New York 创建时间: 2023-10-01 10:30:00 4. 统计用户总数: 用户总数: 3 5. 获取所有用户名列表: 用户名列表: alice_wonder, bob_smith, carol_davis示例4:更新与删除操作
<?php/** * 更新与删除操作 * 演示UPDATE和DELETE语句的使用 */require_once'config.php';try{$pdo=newPDO($dsn,$username,$password,$options);echo"<h3>更新与删除操作演示</h3>";// 1. 更新操作:修改用户信息echo"<h4>1. 更新用户信息:</h4>";// 先查看原始数据$sql="SELECT id, username, city, age FROM users WHERE username = 'bob_smith'";$user=$pdo->query($sql)->fetch();echo"更新前 - Bob的信息: 城市:{$user['city']}, 年龄:{$user['age']}<br>";// 执行更新(使用预处理语句)$updateSQL="UPDATE users SET city = :city, age = :age WHERE username = :username";$stmt=$pdo->prepare($updateSQL);$updateData=['city'=>'Berlin','age'=>33,'username'=>'bob_smith'];$stmt->execute($updateData);$affectedRows=$stmt->rowCount();echo"更新完成,影响行数:{$affectedRows}<br>";// 验证更新结果$user=$pdo->query($sql)->fetch();echo"更新后 - Bob的信息: 城市:{$user['city']}, 年龄:{$user['age']}<br>";// 2. 删除操作:删除特定用户echo"<h4>2. 删除用户操作:</h4>";// 先插入一个用于测试删除的用户$insertSQL="INSERT INTO users (username, email, full_name, age, city) VALUES ('test_user', 'test@example.com', 'Test User', 20, 'Test City')";$pdo->exec($insertSQL);$testUserId=$pdo->lastInsertId();echo"已创建测试用户,ID:{$testUserId}<br>";// 统计删除前的用户数$countBefore=$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();echo"删除前用户总数:{$countBefore}<br>";// 执行删除(使用预处理语句)$deleteSQL="DELETE FROM users WHERE id = ?";$stmt=$pdo->prepare($deleteSQL);$stmt->execute([$testUserId]);$deletedRows=$stmt->rowCount();echo"删除完成,删除行数:{$deletedRows}<br>";// 验证删除结果$countAfter=$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();echo"删除后用户总数:{$countAfter}<br>";// 3. 事务处理示例:要么全部成功,要么全部回滚echo"<h4>3. 事务处理演示:</h4>";try{// 开始事务$pdo->beginTransaction();echo"事务开始...<br>";// 执行多个操作$stmt1=$pdo->prepare("UPDATE users SET age = age + 1 WHERE city = 'New York'");$stmt1->execute();$updated1=$stmt1->rowCount();echo"操作1: New York用户年龄+1,影响{$updated1}人<br>";// 模拟一个可能失败的操作$stmt2=$pdo->prepare("UPDATE users SET age = age - 1 WHERE city = 'NonExistentCity'");$stmt2->execute();$updated2=$stmt2->rowCount();echo"操作2: 不存在的城市用户年龄-1,影响{$updated2}人<br>";// 这里可以故意制造一个错误来测试回滚// $pdo->exec("INVALID SQL STATEMENT"); // 取消注释测试回滚// 提交事务$pdo->commit();echo"事务提交成功！<br>";}catch(Exception$e){// 回滚事务$pdo->rollBack();echo"事务失败,已回滚.错误: ".$e->getMessage()."<br>";}}catch(PDOException$e){die("操作失败: ".$e->getMessage());}预期输出:
更新与删除操作演示 1. 更新用户信息: 更新前 - Bob的信息: 城市: London, 年龄: 32 更新完成,影响行数: 1 更新后 - Bob的信息: 城市: Berlin, 年龄: 33 2. 删除用户操作: 已创建测试用户,ID: 4 删除前用户总数: 4 删除完成,删除行数: 1 删除后用户总数: 3 3. 事务处理演示: 事务开始... 操作1: New York用户年龄+1,影响1人 操作2: 不存在的城市用户年龄-1,影响0人 事务提交成功！示例5:SQL注入攻击与防护实战演示
<?php/** * SQL注入攻击与防护演示 * 对比不安全写法与安全写法的区别 */require_once'config.php';try{$pdo=newPDO($dsn,$username,$password,$options);echo"<h2>SQL注入攻击与防护实战演示</h2>";// 为演示创建测试表和数据$pdo->exec(" CREATE TABLE IF NOT EXISTS sensitive_data ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT, data_type VARCHAR(50), secret_info TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ");$pdo->exec("DELETE FROM sensitive_data");// 清空旧数据// 插入测试数据$testData=[[1,'email','admin@example.com'],[1,'password_hash','hashed_password_123'],[2,'bank_account','AC123456789'],[3,'personal_note','My secret note...']];$stmt=$pdo->prepare("INSERT INTO sensitive_data (user_id, data_type, secret_info) VALUES (?, ?, ?)");foreach($testDataas$data){$stmt->execute($data);}echo"测试数据已准备完成<br><br>";/场景1:不安全的直接拼接(易受攻击)/echo"<h3>场景1:不安全的SQL拼接(易受SQL注入攻击)</h3>";// 模拟用户输入(攻击者恶意输入)$userInput="1' OR '1'='1";// 典型的SQL注入payloadecho"用户输入: <code>".htmlspecialchars($userInput)."</code><br>";// 危险！直接拼接SQL语句$unsafeSQL="SELECT * FROM sensitive_data WHERE user_id = '".$userInput."'";echo"执行的SQL: <code>".htmlspecialchars($unsafeSQL)."</code><br>";echo"查询结果:<br>";try{$result=$pdo->query($unsafeSQL);$rows=$result->fetchAll();if(count($rows)>0){echo"<span style='color: red; font-weight: bold;'>攻击成功！获取到 ".count($rows)." 条敏感数据:</span><br>";foreach($rowsas$row){echo"用户ID:{$row['user_id']}, 类型:{$row['data_type']}, 信息:{$row['secret_info']}<br>";}echo"<br><strong>漏洞分析:</strong>攻击者输入 <code>1' OR '1'='1</code> 使SQL条件永远为真,从而窃取了所有数据！<br>";}}catch(Exception$e){echo"查询出错: ".$e->getMessage()."<br>";}/场景2:安全的预处理语句(防护成功)/echo"<hr><h3>场景2:使用预处理语句(有效防护SQL注入)</h3>";echo"同样的用户输入: <code>".htmlspecialchars($userInput)."</code><br>";// 安全！使用预处理语句$safeSQL="SELECT * FROM sensitive_data WHERE user_id = ?";echo"执行的SQL模板: <code>".htmlspecialchars($safeSQL)."</code><br>";echo"参数值: <code>".htmlspecialchars($userInput)."</code><br>";$stmt=$pdo->prepare($safeSQL);$stmt->execute([$userInput]);$rows=$stmt->fetchAll();echo"查询结果:<br>";if(count($rows)>0){echo"找到 ".count($rows)." 条记录:<br>";foreach($rowsas$row){echo"用户ID:{$row['user_id']}, 类型:{$row['data_type']}, 信息:{$row['secret_info']}<br>";}}else{echo"<span style='color: green; font-weight: bold;'>防护成功！没有找到匹配的数据.</span><br>";echo"<br><strong>安全原理:</strong>预处理语句将数据和SQL指令分离,用户输入 <code>1' OR '1'='1</code> 被当作一个完整的字符串值处理,而不是SQL代码片段.<br>";}/场景3:更复杂的注入攻击演示/echo"<hr><h3>场景3:更危险的SQL注入(删除数据)</h3>";// 模拟更恶意的输入$maliciousInput="1; DROP TABLE sensitive_data; --";echo"恶意输入: <code>".htmlspecialchars($maliciousInput)."</code><br>";// 不安全的拼接方式$dangerousSQL="DELETE FROM sensitive_data WHERE user_id = ".$maliciousInput;echo"危险SQL: <code>".htmlspecialchars($dangerousSQL)."</code><br>";echo"<strong>后果分析:</strong>如果执行此SQL,将会:<br>";echo"1. 删除user_id=1的数据<br>";echo"2. 执行<code>DROP TABLE sensitive_data</code>删除整个表！<br>";echo"3. <code>--</code>注释掉后面的所有内容,避免语法错误<br>";// 使用预处理语句防护echo"<br><strong>使用预处理语句的防护:</strong><br>";$safeDeleteSQL="DELETE FROM sensitive_data WHERE user_id = ?";$stmt=$pdo->prepare($safeDeleteSQL);$stmt->execute([$maliciousInput]);$affected=$stmt->rowCount();echo"实际删除行数:{$affected}<br>";echo"<span style='color: green;'>防护成功！预处理语句将整个<code>1; DROP TABLE sensitive_data; --</code>当作一个无效的用户ID值,不会执行DROP操作.</span>";// 验证表依然存在$tableExists=$pdo->query("SHOW TABLES LIKE 'sensitive_data'")->fetch();echo"<br>表'sensitive_data'依然存在: ".($tableExists?'是':'否');}catch(PDOException$e){die("操作失败: ".$e->getMessage());}预期输出: SQL注入攻击与防护实战演示 测试数据已准备完成 场景1:不安全的SQL拼接(易受SQL注入攻击) 用户输入: 1' OR '1'='1 执行的SQL: SELECT * FROM sensitive_data WHERE user_id = '1' OR '1'='1' 查询结果: 攻击成功！获取到 4 条敏感数据: 用户ID: 1, 类型: email, 信息: admin@example.com 用户ID: 1, 类型: password_hash, 信息: hashed_password_123 用户ID: 2, 类型: bank_account, 信息: AC123456789 用户ID: 3, 类型: personal_note, 信息: My secret note... 漏洞分析:攻击者输入 1' OR '1'='1 使SQL条件永远为真,从而窃取了所有数据！ 场景2:使用预处理语句(有效防护SQL注入) 同样的用户输入: 1' OR '1'='1 执行的SQL模板: SELECT * FROM sensitive_data WHERE user_id = ? 参数值: 1' OR '1'='1 查询结果: 防护成功！没有找到匹配的数据. 安全原理:预处理语句将数据和SQL指令分离,用户输入 1' OR '1'='1 被当作一个完整的字符串值处理,而不是SQL代码片段. 场景3:更危险的SQL注入(删除数据) 恶意输入: 1; DROP TABLE sensitive_data; -- 危险SQL: DELETE FROM sensitive_data WHERE user_id = 1; DROP TABLE sensitive_data; -- 后果分析:如果执行此SQL,将会: 1. 删除user_id=1的数据 2. 执行DROP TABLE sensitive_data删除整个表！ 3. --注释掉后面的所有内容,避免语法错误 使用预处理语句的防护: 实际删除行数: 0 防护成功！预处理语句将整个1; DROP TABLE sensitive_data; --当作一个无效的用户ID值,不会执行DROP操作. 表'sensitive_data'依然存在: 是实战项目:学生信息管理系统(数据库层)
项目需求分析
我们将构建一个学生信息管理系统的数据库操作层,为后续的API开发做准备.系统需要管理学生的基本信息,包括:
- 学生信息的存储(增)
- 学生信息的查询(查)
- 学生信息的更新(改)
- 学生信息的删除(删)
- 多种查询方式(按ID、按姓名、按成绩范围等)
技术方案
- 数据库:MySQL
- PHP扩展:PDO
- 安全措施:全部使用预处理语句
- 错误处理:异常处理模式
分步骤实现
步骤1:数据库设计
-- 创建学生表CREATETABLEstudents(idINTAUTO_INCREMENTPRIMARYKEY,student_idVARCHAR(20)UNIQUENOTNULLCOMMENT'学号',nameVARCHAR(100)NOTNULLCOMMENT'姓名',genderENUM('男','女','其他')DEFAULT'其他'COMMENT'性别',birth_dateDATECOMMENT'出生日期',emailVARCHAR(100)UNIQUECOMMENT'邮箱',phoneVARCHAR(20)COMMENT'手机号',majorVARCHAR(100)COMMENT'专业',enrollment_yearYEARCOMMENT'入学年份',gpaDECIMAL(3,2)COMMENT'平均绩点',addressTEXTCOMMENT'家庭地址',created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,INDEXidx_name(name),INDEXidx_major(major),INDEXidx_gpa(gpa))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='学生信息表';步骤2:数据库连接与配置类
<?php/** * 学生信息管理系统 - 数据库配置类 * 文件名: Database.php */classDatabase{// 数据库连接实例(单例模式)privatestatic$instance=null;private$pdo;// 私有构造函数,防止外部实例化privatefunction__construct(){$this->connect();}// 获取数据库连接单例publicstaticfunctiongetInstance(){if(self::$instance===null){self::$instance=newself();}returnself::$instance;}// 获取PDO连接对象publicfunctiongetConnection(){return$this->pdo;}// 建立数据库连接privatefunctionconnect(){$host='localhost';$dbname='student_management';$username='root';$password='';$charset='utf8mb4';$options=[PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC,PDO::ATTR_EMULATE_PREPARES=>false,];$dsn="mysql:host=$host;dbname=$dbname;charset=$charset";try{$this->pdo=newPDO($dsn,$username,$password,$options);}catch(PDOException$e){// 生产环境应记录日志而不是直接显示die("数据库连接失败: ".$e->getMessage());}}// 防止克隆privatefunction__clone(){}// 防止反序列化privatefunction__wakeup(){}}步骤3:学生数据操作类
<?php/** * 学生信息管理系统 - 学生数据操作类 * 文件名: StudentRepository.php */require_once'Database.php';classStudentRepository{private$pdo;publicfunction__construct(){$db=Database::getInstance();$this->pdo=$db->getConnection();}/** * 创建学生记录 * @param array $studentData 学生数据数组 * @return int 新创建的学生ID */publicfunctioncreateStudent($studentData){// 定义必填字段$requiredFields=['student_id','name'];// 验证必填字段foreach($requiredFieldsas$field){if(empty($studentData[$field])){thrownewInvalidArgumentException("字段 '$field' 是必填项");}}// 构建SQL语句$sql="INSERT INTO students (";$sql.=implode(', ',array_keys($studentData));$sql.=") VALUES (";$sql.=':'.implode(', :',array_keys($studentData));$sql.=")";// 准备并执行预处理语句$stmt=$this->pdo->prepare($sql);try{$stmt->execute($studentData);return(int)$this->pdo->lastInsertId();}catch(PDOException$e){// 处理唯一约束冲突等错误if($e->getCode()=='23000'){// 唯一约束错误代码if(strpos($e->getMessage(),'student_id')!==false){thrownewException("学号 '{$studentData['student_id']}' 已存在");}elseif(strpos($e->getMessage(),'email')!==false){thrownewException("邮箱 '{$studentData['email']}' 已存在");}}throw$e;// 重新抛出其他异常}}/** * 根据ID获取学生信息 * @param int $id 学生ID * @return array|null 学生信息数组或null */publicfunctiongetStudentById($id){$sql="SELECT * FROM students WHERE id = :id";$stmt=$this->pdo->prepare($sql);$stmt->execute(['id'=>$id]);return$stmt->fetch()?:null;}/** * 根据学号获取学生信息 * @param string $studentId 学号 * @return array|null 学生信息数组或null */publicfunctiongetStudentByStudentId($studentId){$sql="SELECT * FROM students WHERE student_id = :student_id";$stmt=$this->pdo->prepare($sql);$stmt->execute(['student_id'=>$studentId]);return$stmt->fetch()?:null;}/** * 获取所有学生(支持分页和筛选) * @param array $filters 筛选条件 * @param int $page 页码(从1开始) * @param int $perPage 每页数量 * @return array 学生列表和分页信息 */publicfunctiongetAllStudents($filters=[],$page=1,$perPage=20){// 基础查询$sql="SELECT * FROM students WHERE 1=1";$params=[];// 动态添加筛选条件if(!empty($filters['major'])){$sql.=" AND major = :major";$params['major']=$filters['major'];}if(!empty($filters['gender'])){$sql.=" AND gender = :gender";$params['gender']=$filters['gender'];}if(!empty($filters['enrollment_year'])){$sql.=" AND enrollment_year = :enrollment_year";$params['enrollment_year']=$filters['enrollment_year'];}if(isset($filters['min_gpa'])&&is_numeric($filters['min_gpa'])){$sql.=" AND gpa >= :min_gpa";$params['min_gpa']=$filters['min_gpa'];}if(isset($filters['max_gpa'])&&is_numeric($filters['max_gpa'])){$sql.=" AND gpa <= :max_gpa";$params['max_gpa']=$filters['max_gpa'];}if(!empty($filters['name_like'])){$sql.=" AND name LIKE :name_like";$params['name_like']='%'.$filters['name_like'].'%';}// 排序$sql.=" ORDER BY id DESC";// 分页$totalSql="SELECT COUNT(*) as total FROM students WHERE 1=1".substr($sql,strpos($sql,"WHERE 1=1")+13);$totalStmt=$this->pdo->prepare($totalSql);$totalStmt->execute($params);$total=(int)$totalStmt->fetch()['total'];// 计算分页$offset=($page-1)*$perPage;$sql.=" LIMIT :offset, :limit";$params['offset']=$offset;$params['limit']=$perPage;// 执行查询$stmt=$this->pdo->prepare($sql);// 绑定分页参数(需要指定类型)$stmt->bindValue(':offset',$offset,PDO::PARAM_INT);$stmt->bindValue(':limit',$perPage,PDO::PARAM_INT);// 绑定其他参数foreach($paramsas$key=>$value){if($key!=='offset'&&$key!=='limit'){$stmt->bindValue(':'.$key,$value);}}$stmt->execute();$students=$stmt->fetchAll();// 计算总页数$totalPages=ceil($total/$perPage);return['students'=>$students,'pagination'=>['total'=>$total,'page'=>$page,'per_page'=>$perPage,'total_pages'=>$totalPages,'has_prev'=>$page>1,'has_next'=>$page<$totalPages]];}/** * 更新学生信息 * @param int $id 学生ID * @param array $updateData 要更新的数据 * @return int 影响的行数 */publicfunctionupdateStudent($id,$updateData){if(empty($updateData)){thrownewInvalidArgumentException("更新数据不能为空");}// 移除ID字段(防止更新ID)unset($updateData['id']);// 构建SET子句$setParts=[];foreach(array_keys($updateData)as$field){$setParts[]="$field= :$field";}$sql="UPDATE students SET ".implode(', ',$setParts)." WHERE id = :id";// 添加ID到参数$updateData['id']=$id;$stmt=$this->pdo->prepare($sql);$stmt->execute($updateData);return$stmt->rowCount();}/** * 删除学生 * @param int $id 学生ID * @return int 影响的行数 */publicfunctiondeleteStudent($id){$sql="DELETE FROM students WHERE id = :id";$stmt=$this->pdo->prepare($sql);$stmt->execute(['id'=>$id]);return$stmt->rowCount();}/** * 搜索学生(多种条件组合) * @param array $searchCriteria 搜索条件 * @return array 搜索结果 */publicfunctionsearchStudents($searchCriteria){$sql="SELECT * FROM students WHERE 1=1";$params=[];// 动态构建查询条件if(!empty($searchCriteria['keyword'])){$sql.=" AND (name LIKE :keyword OR student_id LIKE :keyword OR email LIKE :keyword)";$params['keyword']='%'.$searchCriteria['keyword'].'%';}if(!empty($searchCriteria['majors'])&&is_array($searchCriteria['majors'])){$placeholders=[];foreach($searchCriteria['majors']as$index=>$major){$paramName=':major_'.$index;$placeholders[]=$paramName;$params[$paramName]=$major;}$sql.=" AND major IN (".implode(', ',$placeholders).")";}if(!empty($searchCriteria['start_year'])&&!empty($searchCriteria['end_year'])){$sql.=" AND enrollment_year BETWEEN :start_year AND :end_year";$params['start_year']=$searchCriteria['start_year'];$params['end_year']=$searchCriteria['end_year'];}// 排序$orderBy=!empty($searchCriteria['order_by'])?$searchCriteria['order_by']:'id';$orderDir=!empty($searchCriteria['order_dir'])&&strtoupper($searchCriteria['order_dir'])==='ASC'?'ASC':'DESC';$sql.=" ORDER BY$orderBy$orderDir";// 限制结果数量$limit=!empty($searchCriteria['limit'])?(int)$searchCriteria['limit']:100;$sql.=" LIMIT :limit";$params['limit']=$limit;$stmt=$this->pdo->prepare($sql);// 绑定参数foreach($paramsas$key=>$value){if($key===':limit'){$stmt->bindValue($key,$value,PDO::PARAM_INT);}else{$stmt->bindValue($key,$value);}}$stmt->execute();return$stmt->fetchAll();}/** * 获取专业统计 * @return array 各专业学生数量统计 */publicfunctiongetMajorStatistics(){$sql="SELECT major, COUNT(*) as student_count FROM students WHERE major IS NOT NULL AND major != '' GROUP BY major ORDER BY student_count DESC";$stmt=$this->pdo->query($sql);return$stmt->fetchAll();}/** * 获取GPA分布统计 * @return array GPA分布 */publicfunctiongetGpaDistribution(){$sql="SELECT CASE WHEN gpa >= 3.5 THEN '优秀 (3.5-4.0)' WHEN gpa >= 3.0 THEN '良好 (3.0-3.49)' WHEN gpa >= 2.0 THEN '中等 (2.0-2.99)' WHEN gpa >= 1.0 THEN '及格 (1.0-1.99)' ELSE '不及格 (<1.0)' END as gpa_range, COUNT(*) as student_count FROM students WHERE gpa IS NOT NULL GROUP BY gpa_range ORDER BY MIN(gpa) DESC";$stmt=$this->pdo->query($sql);return$stmt->fetchAll();}}步骤4:使用示例与测试
<?php/** * 学生信息管理系统 - 使用示例 * 文件名: StudentDemo.php */require_once'Database.php';require_once'StudentRepository.php';// 设置错误显示ini_set('display_errors',1);error_reporting(E_ALL);try{echo"<h2>学生信息管理系统 - 功能演示</h2>";// 创建数据操作实例$studentRepo=newStudentRepository();/1.创建学生/echo"<h3>1. 创建学生记录</h3>";$newStudent=['student_id'=>'20230001','name'=>'张三','gender'=>'男','birth_date'=>'2002-05-15','email'=>'zhangsan@example.com','phone'=>'13800138000','major'=>'计算机科学','enrollment_year'=>2023,'gpa'=>3.75,'address'=>'北京市海淀区'];$studentId=$studentRepo->createStudent($newStudent);echo"学生创建成功,ID:{$studentId}<br>";// 再创建几个测试学生$testStudents=[['student_id'=>'20230002','name'=>'李四','gender'=>'女','major'=>'软件工程','enrollment_year'=>2023,'gpa'=>3.92],['student_id'=>'20230003','name'=>'王五','gender'=>'男','major'=>'计算机科学','enrollment_year'=>2023,'gpa'=>3.45],['student_id'=>'20220001','name'=>'赵六','gender'=>'女','major'=>'数据科学','enrollment_year'=>2022,'gpa'=>3.88]];foreach($testStudentsas$student){$id=$studentRepo->createStudent($student);echo"学生{$student['name']}创建成功,ID:{$id}<br>";}/2.查询学生/echo"<h3>2. 查询学生信息</h3>";// 按ID查询$student=$studentRepo->getStudentById(1);if($student){echo"ID为1的学生:{$student['name']}({$student['student_id']}), GPA:{$student['gpa']}<br>";}// 按学号查询$student=$studentRepo->getStudentByStudentId('20230002');if($student){echo"学号20230002的学生:{$student['name']}, 专业:{$student['major']}<br>";}/3.获取所有学生(分页)/echo"<h3>3. 分页查询所有学生</h3>";$result=$studentRepo->getAllStudents([],1,3);echo"第1页(每页3条),共{$result['pagination']['total']}个学生:<br>";foreach($result['students']as$s){echo"-{$s['name']}({$s['student_id']}),{$s['major']}, GPA:{$s['gpa']}<br>";}echo"分页信息: 当前页{$result['pagination']['page']}/{$result['pagination']['total_pages']}";echo"[上一页: ".($result['pagination']['has_prev']?'有':'无')."] ";echo"[下一页: ".($result['pagination']['has_next']?'有':'无')."]<br>";/4.条件筛选查询/echo"<h3>4. 条件筛选查询</h3>";// 查询计算机科学专业的学生$filters=['major'=>'计算机科学'];$result=$studentRepo->getAllStudents($filters,1,10);echo"计算机科学专业的学生(共{$result['pagination']['total']}人):<br>";foreach($result['students']as$s){echo"-{$s['name']}, GPA:{$s['gpa']}<br>";}// 查询GPA大于3.5的学生$filters=['min_gpa'=>3.5];$result=$studentRepo->getAllStudents($filters,1,10);echo"<br>GPA≥3.5的优秀学生(共{$result['pagination']['total']}人):<br>";foreach($result['students']as$s){echo"-{$s['name']}({$s['major']}), GPA:{$s['gpa']}<br>";}/5.更新学生信息/echo"<h3>5. 更新学生信息</h3>";$updateData=['gpa'=>3.95,'email'=>'zhangsan_new@example.com'];$affected=$studentRepo->updateStudent(1,$updateData);echo"更新ID为1的学生,影响行数:{$affected}<br>";// 验证更新$student=$studentRepo->getStudentById(1);echo"更新后 - 张三的GPA:{$student['gpa']}, 邮箱:{$student['email']}<br>";/6.搜索功能演示/echo"<h3>6. 高级搜索功能</h3>";$searchCriteria=['majors'=>['计算机科学','软件工程'],'start_year'=>2022,'end_year'=>2023,'order_by'=>'gpa','order_dir'=>'DESC','limit'=>5];$results=$studentRepo->searchStudents($searchCriteria);echo"搜索计算机科学或软件工程专业,2022-2023年入学,按GPA降序排列:<br>";foreach($resultsas$s){echo"-{$s['name']}({$s['student_id']}),{$s['major']}, GPA:{$s['gpa']}, 入学:{$s['enrollment_year']}<br>";}/7.统计分析/echo"<h3>7. 数据统计分析</h3>";// 专业统计$majorStats=$studentRepo->getMajorStatistics();echo"各专业学生数量:<br>";foreach($majorStatsas$stat){echo"-{$stat['major']}:{$stat['student_count']}人<br>";}// GPA分布统计$gpaStats=$studentRepo->getGpaDistribution();echo"<br>GPA分布统计:<br>";foreach($gpaStatsas$stat){echo"-{$stat['gpa_range']}:{$stat['student_count']}人<br>";}/8.删除学生/echo"<h3>8. 删除学生记录</h3>";// 先创建一个用于删除的测试学生$testStudent=['student_id'=>'20230099','name'=>'测试删除','major'=>'测试专业'];$testId=$studentRepo->createStudent($testStudent);echo"创建测试学生,ID:{$testId}<br>";// 删除该学生$deleted=$studentRepo->deleteStudent($testId);echo"删除测试学生,影响行数:{$deleted}<br>";// 验证删除$student=$studentRepo->getStudentById($testId);echo"验证删除结果: ".($student?'删除失败':'删除成功')."<br>";/9.错误处理演示/echo"<h3>9. 错误处理演示</h3>";try{// 尝试创建重复学号的学生$duplicateStudent=['student_id'=>'20230001',// 已存在的学号'name'=>'重复学号测试'];$studentRepo->createStudent($duplicateStudent);}catch(Exception$e){echo"预期中的错误: ".$e->getMessage()."<br>";}echo"<hr><h3 style='color: green;'>所有功能演示完成！</h3>";}catch(Exception$e){echo"<h3 style='color: red;'>发生错误:</h3>";echo"<p>".$e->getMessage()."</p>";echo"<pre>".$e->getTraceAsString()."</pre>";}预期输出: 学生信息管理系统 - 功能演示 1. 创建学生记录 学生创建成功,ID: 1 学生 李四 创建成功,ID: 2 学生 王五 创建成功,ID: 3 学生 赵六 创建成功,ID: 4 2. 查询学生信息 ID为1的学生: 张三 (20230001), GPA: 3.75 学号20230002的学生: 李四, 专业: 软件工程 3. 分页查询所有学生 第1页(每页3条),共 4 个学生: - 赵六 (20220001), 数据科学, GPA: 3.88 - 王五 (20230003), 计算机科学, GPA: 3.45 - 李四 (20230002), 软件工程, GPA: 3.92 分页信息: 当前页 1/2 [上一页: 无] [下一页: 有] 4. 条件筛选查询 计算机科学专业的学生(共2人): - 张三, GPA: 3.95 - 王五, GPA: 3.45 GPA≥3.5的优秀学生(共3人): - 李四 (软件工程), GPA: 3.92 - 赵六 (数据科学), GPA: 3.88 - 张三 (计算机科学), GPA: 3.95 5. 更新学生信息 更新ID为1的学生,影响行数: 1 更新后 - 张三的GPA: 3.95, 邮箱: zhangsan_new@example.com 6. 高级搜索功能 搜索计算机科学或软件工程专业,2022-2023年入学,按GPA降序排列: - 张三 (20230001), 计算机科学, GPA: 3.95, 入学: 2023 - 李四 (20230002), 软件工程, GPA: 3.92, 入学: 2023 - 王五 (20230003), 计算机科学, GPA: 3.45, 入学: 2023 7. 数据统计分析 各专业学生数量: - 计算机科学: 2人 - 软件工程: 1人 - 数据科学: 1人 GPA分布统计: - 优秀 (3.5-4.0): 3人 - 良好 (3.0-3.49): 1人 8. 删除学生记录 创建测试学生,ID: 5 删除测试学生,影响行数: 1 验证删除结果: 删除成功 9. 错误处理演示 预期中的错误: 学号 '20230001' 已存在 所有功能演示完成！最佳实践
1. 数据库连接管理
连接池与单例模式:
在生产环境中,应该使用连接池来管理数据库连接.我们示例中的单例模式是一个简化版本,实际项目中可以考虑使用依赖注入容器.
// 连接配置应存储在环境变量或配置文件中,不要硬编码$config=['host'=>getenv('DB_HOST'),'database'=>getenv('DB_NAME'),'username'=>getenv('DB_USER'),'password'=>getenv('DB_PASS'),'charset'=>'utf8mb4','options'=>[PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC,PDO::ATTR_EMULATE_PREPARES=>false,// 持久连接(谨慎使用)// PDO::ATTR_PERSISTENT => true,]];2. 错误处理最佳实践
分层错误处理策略:
try{// 数据库操作$stmt=$pdo->prepare("SELECT * FROM users WHERE id = ?");$stmt->execute([$userId]);$user=$stmt->fetch();if(!$user){// 业务逻辑错误:用户不存在thrownewUserNotFoundException("用户ID{$userId}不存在");}// 更多业务逻辑...}catch(PDOException$e){// 记录到系统日志(生产环境)error_log("数据库错误: ".$e->getMessage());// 返回用户友好的错误信息(不要暴露数据库细节)http_response_code(500);echojson_encode(['error'=>'数据库服务暂时不可用,请稍后重试']);}catch(UserNotFoundException$e){// 业务异常,返回404http_response_code(404);echojson_encode(['error'=>$e->getMessage()]);}catch(Exception$e){// 其他未预料的异常error_log("未预料错误: ".$e->getMessage());http_response_code(500);echojson_encode(['error'=>'服务器内部错误']);}3. SQL性能优化
索引使用原则:
- 为WHERE、JOIN、ORDER BY涉及的字段创建索引
- 避免在索引列上使用函数或计算
- 使用复合索引时注意列的顺序
- 定期分析查询性能
-- 创建合适的索引CREATEINDEXidx_user_emailONusers(email);CREATEINDEXidx_orders_user_dateONorders(user_id,order_dateDESC);-- 使用EXPLAIN分析查询EXPLAINSELECT*FROMusersWHEREemail='test@example.com';查询优化技巧:
// 避免 SELECT *,只选择需要的字段$sql="SELECT id, username, email FROM users";// 好$sql="SELECT * FROM users";// 不好// 使用LIMIT限制结果集$sql="SELECT * FROM large_table LIMIT 100";// 分页查询优化$sql="SELECT * FROM users WHERE id > :last_id ORDER BY id LIMIT :limit";4. 安全规范
必须遵循的安全准则:
- 永远使用预处理语句
// ✅ 正确做法$stmt=$pdo->prepare("SELECT * FROM users WHERE username = ?");$stmt->execute([$username]);// ❌ 绝对禁止的做法$sql="SELECT * FROM users WHERE username = '$username'";$pdo->query($sql);- 最小权限原则
-- 为应用创建专用数据库用户,只授予最小必要权限CREATEUSER'app_user'@'localhost'IDENTIFIEDBY'strong_password';GRANTSELECT,INSERT,UPDATE,DELETEONapp_db.*TO'app_user'@'localhost';-- 不要授予DROP, ALTER, CREATE等权限- 输入验证与过滤
// 验证邮箱格式if(!filter_var($email,FILTER_VALIDATE_EMAIL)){thrownewInvalidArgumentException("邮箱格式不正确");}// 过滤HTML标签$clean_input=filter_var($input,FILTER_SANITIZE_STRING);// 类型验证$id=filter_var($input_id,FILTER_VALIDATE_INT);if($id===false){thrownewInvalidArgumentException("ID必须是整数");}5. 常见错误与避坑指南
错误1:忘记关闭连接或释放资源
// ❌ 错误做法:大量连接不释放for($i=0;$i<1000;$i++){$pdo=newPDO($dsn,$user,$pass);// 操作...// 没有关闭连接}// ✅ 正确做法:让PDO自动管理或手动置null$pdo=newPDO($dsn,$user,$pass);// 多次操作...// 脚本结束后会自动关闭,或手动:$pdo=null;错误2:在循环中执行查询
// ❌ 低效做法:N+1查询问题$users=$pdo->query("SELECT id FROM users")->fetchAll();foreach($usersas$user){$stmt=$pdo->prepare("SELECT * FROM orders WHERE user_id = ?");$stmt->execute([$user['id']]);$orders=$stmt->fetchAll();// 处理订单...}// ✅ 高效做法:使用JOIN一次性获取$sql="SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id ORDER BY u.id";$stmt=$pdo->query($sql);// 一次性获取所有数据错误3:忽略字符集导致乱码
// ❌ 可能乱码$pdo=newPDO("mysql:host=localhost;dbname=test","root","");// ✅ 明确指定字符集$pdo=newPDO("mysql:host=localhost;dbname=test;charset=utf8mb4","root","",[PDO::MYSQL_ATTR_INIT_COMMAND=>"SET NAMES utf8mb4"]);练习题与挑战
基础练习题
题目1:数据库概念理解(难度:★☆☆☆☆)
- 什么是主键(Primary Key)？它有什么作用？
- SQL中的INSERT、SELECT、UPDATE、DELETE语句分别对应什么操作？
- 什么是SQL注入？如何防止SQL注入攻击？
题目2:基础CRUD操作(难度:★★☆☆☆)
创建一个products产品表,包含以下字段:
id(主键,自增)name(产品名称,字符串)price(价格,小数)stock(库存,整数)category(分类,字符串)created_at(创建时间)
编写PHP代码完成以下操作:
- 使用PDO连接数据库
- 插入3个测试产品
- 查询所有产品并按价格降序排列
- 更新某个产品的库存
- 删除价格最高的产品
进阶练习题
题目3:复杂查询与统计(难度:★★★☆☆)
基于上面的products表,编写一个ProductAnalytics类,包含以下方法:
getProductsByPriceRange($minPrice, $maxPrice):获取指定价格区间的产品getCategorySummary():统计每个分类的产品数量和平均价格searchProducts($keyword, $category = null):根据关键词搜索产品,可指定分类updateStock($productId, $quantityChange):更新库存,确保库存不为负数
要求:所有方法必须使用预处理语句,包含适当的错误处理.
题目4:事务处理实践(难度:★★★☆☆)
模拟一个电商订单系统,涉及两个表:
orders:订单表(id, user_id, total_amount, status)order_items:订单项表(id, order_id, product_id, quantity, price)
编写一个placeOrder方法,需要:
- 开启事务
- 在orders表插入订单记录
- 在order_items表插入多个订单项
- 更新products表中对应产品的库存
- 如果任何步骤失败,回滚整个事务
- 所有操作成功,提交事务
综合挑战题
题目5:完整的学生选课系统(难度:★★★★☆)
设计并实现一个学生选课系统的数据库操作层,包含以下表:
students:学生表(id, student_id, name, major)courses:课程表(id, course_code, name, credits, capacity)enrollments:选课表(id, student_id, course_id, enrollment_date, grade)
要求实现的功能:
- 学生可以选课(检查课程容量、避免重复选课)
- 学生可以退课
- 查询某个学生的所有课程
- 查询某门课程的所有学生
- 统计每门课程的选课人数
- 为学生课程打分
- 计算学生的GPA(绩点)
- 使用事务确保数据一致性
题目6:数据库迁移工具(难度:★★★★★)
创建一个简单的数据库迁移工具类,支持: - 记录已执行的迁移脚本
- 按顺序执行迁移文件
- 支持回滚操作
- 迁移文件格式:
YYYYMMDD_HHMMSS_description.sql
示例迁移文件内容:
-- 迁移: 20231001_000001_create_users_table.up.sqlCREATETABLEusers(idINTAUTO_INCREMENTPRIMARYKEY,usernameVARCHAR(50)NOTNULLUNIQUE,emailVARCHAR(100)NOTNULLUNIQUE,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);-- 回滚: 20231001_000001_create_users_table.down.sqlDROPTABLEIFEXISTSusers;章节总结
重点知识回顾
- 数据库基础:理解了数据库、表、字段、记录等核心概念,MySQL作为关系型数据库管理系统的基本特性.
- SQL语言:掌握了CREATE、INSERT、SELECT、UPDATE、DELETE等基本SQL操作,能够设计简单的表结构.
- PDO扩展:学会了使用PHP的PDO扩展连接数据库,理解了PDO相比其他扩展的优势:安全性、兼容性、面向对象.
- 预处理语句:深入理解了预处理语句的工作原理,这是防御SQL注入攻击的核心技术,必须牢固掌握.
- CRUD操作:能够使用PHP执行完整的增删改查操作,包括条件查询、排序、分页等高级功能.
- 错误处理:掌握了PDO异常处理模式,学会了如何优雅地处理数据库操作中的各种错误.
- 安全实践:理解了SQL注入攻击的原理,学会了使用预处理语句、输入验证、最小权限原则等安全措施.
技能掌握要求
完成本章学习后,你应该能够:
- ✓ 独立设计简单的数据库表结构
- ✓ 使用PDO安全地连接MySQL数据库
- ✓ 使用预处理语句执行所有数据库操作
- ✓ 实现基本的CRUD功能并处理结果集
- ✓ 识别和防范SQL注入攻击
- ✓ 处理数据库操作中的异常和错误
- ✓ 实现分页查询和条件筛选
进一步学习建议
- 深入学习SQL:掌握JOIN连接、子查询、事务、存储过程、触发器等高级SQL特性.
- 数据库设计:学习数据库规范化理论(三大范式)、索引优化、查询性能调优.
- PDO高级特性:研究PDO的事务处理、大数据集处理(fetch分批)、自定义数据类型绑定.
- ORM框架:了解Eloquent(Laravel)、Doctrine等ORM框架,理解其优缺点.
- NoSQL数据库:了解MongoDB、Redis等非关系型数据库的使用场景.
- 数据库安全:深入学习数据库安全最佳实践,包括加密存储、审计日志、备份恢复策略.
下一章预告
在掌握了PHP操作数据库的核心技能后,下一章《对话规则:理解HTTP协议与RESTful API设计》将带你进入Web开发的核心通信层.你将学习:
- HTTP协议如何工作:请求、响应、状态码、头部信息
- RESTful API的设计原则和最佳实践
- 如何使用工具测试API接口
- JSON作为数据交换格式的优势和用法
这些知识将为你构建真正的API接口打下理论基础,让你理解前后端是如何通过HTTP协议进行"对话"的.
重要提醒:数据库操作是后端开发的基石,请务必通过练习题巩固本章知识.只有熟练掌握数据库操作,才能在后续的API开发中游刃有余.如果在练习中遇到问题,建议回顾本章的代码示例,特别是安全相关的部分,这是区分初级和中级开发者的关键技能点.
