侧边栏壁纸
博主头像
落叶人生博主等级

走进秋风,寻找秋天的落叶

  • 累计撰写 144678 篇文章
  • 累计创建 28 个标签
  • 累计收到 9 条评论
标签搜索

目 录CONTENT

文章目录

PHP--MySQL简明手册

2024-02-21 星期三 / 0 评论 / 0 点赞 / 2 阅读 / 25166 字

PHP 的MySQL连接当用PHP访问数据库时,除了PHP自带的数据库驱动,我们一般还有两种比较好的选择:PDO和MySQLi。注意:自PHP5.5开始,传统的mysql扩展已经废弃,只能使用PHP提

PHP 的MySQL连接

当用PHP访问数据库时,除了PHP自带的数据库驱动,我们一般还有两种比较好的选择:PDO和MySQLi。

注意:自PHP5.5开始,传统的mysql扩展已经废弃,只能使用PHP提供的MySQLi扩展或PDO扩展

PDO和MySQLi区别

PDOMySQLi
Database support12 different driversMySQL only
APIOOPOOP + procedural
ConnectionEasyEasy
Named parametersYesNo
Object mappingYesYes
Prepared statements (client side)YesNo
PerformanceFastFast
Stored proceduresYesYes

如果你的项目需要在多种数据库中切换,建议使用 PDO;若选用MySQLi,在多种数据库中切换时候,你需要重新编写所有代码。

MySQLi专门针对MySQL设计的,所以MySQLi相对于PDO性能稍微好一些。

PHP 连接MySQL

MySQLi - 面向对象的连接方法

<?php$servername = "localhost";$username = "username";$password = "password";// 创建连接$conn = new mysqli($servername, $username, $password);// 检测连接if ($conn->connect_error) {    die("连接失败: " . $conn->connect_error);}echo "连接成功";?>

MySQLi - 面向过程的连接方法

<?php$servername = "localhost";$username = "username";$password = "password";// 创建连接$conn = mysqli_connect($servername, $username, $password);// 检测连接if (!$conn) {    die("Connection failed: " . mysqli_connect_error());}echo "连接成功";?

PDO 的连接方法

<?php$servername = "localhost";$username = "username";$password = "password";try {    $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);    echo "连接成功";}catch(PDOException $e){    echo $e->getMessage();}?>

关闭连接

连接在脚本执行完后会自动关闭。可以使用以下代码来关闭连接:

MySQLi - 面向对象的关闭连接方法

$conn->close();

MySQLi - 面向过程的关闭连接方法

mysqli_close($conn);

PDO关闭连接的方法

$conn = null;

创建数据库

使用 MySQLi 和 PDO 创建 MySQL 数据库

使用CREATE DATABASE语句创建数据库

MySQLi - 使用面向对象的方式创建数据库

<?php$servername = "localhost";$username = "username";$password = "password";// 创建连接$conn = new mysqli($servername, $username, $password);// 检测连接if ($conn->connect_error) {    die("连接失败: " . $conn->connect_error);}// 创建数据库$sql = "CREATE DATABASE myDB";if ($conn->query($sql) === TRUE) {    echo "数据库创建成功";} else {    echo "Error creating database: " . $conn->error;}$conn->close();?>

**MySQLi-使用面向过程的方式创建数据库 **

<?php$servername = "localhost";$username = "username";$password = "password";// 创建连接$conn = mysqli_connect($servername, $username, $password);// 检测连接if (!$conn) {    die("连接失败: " . mysqli_connect_error());}// 创建数据库$sql = "CREATE DATABASE myDB";if (mysqli_query($conn, $sql)) {    echo "数据库创建成功";} else {    echo "Error creating database: " . mysqli_error($conn);}mysqli_close($conn);?>

PDO 创建数据库

<?php$servername = "localhost";$username = "username";$password = "password";try {    $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);    // 设置 PDO 错误模式为异常    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    $sql = "CREATE DATABASE myDBPDO";    // 使用 exec() ,因为没有结果返回    $conn->exec($sql);    echo "数据库创建成功<br>";}catch(PDOException $e){    echo $sql . "<br>" . $e->getMessage();}$conn = null;?>

创建数据表

使用CREATE TABLE语句创建数据表

我们将创建一个名为 "MyGuests" 的表,有 5 个列: "id", "firstname", "lastname", "email" 和 "reg_date":

CREATE TABLE MyGuests (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,firstname VARCHAR(30) NOT NULL,lastname VARCHAR(30) NOT NULL,email VARCHAR(50),reg_date TIMESTAMP)

MySQLi - 使用面向对象的方式创建数据表

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// 检测连接if ($conn->connect_error) {    die("连接失败: " . $conn->connect_error);}// 使用 sql 创建数据表$sql = "CREATE TABLE MyGuests (					id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 					firstname VARCHAR(30) NOT NULL,					lastname VARCHAR(30) NOT NULL,					email VARCHAR(50),					reg_date TIMESTAMP				)";				if ($conn->query($sql) === TRUE) {    echo "Table MyGuests created successfully";} else {    echo "创建数据表错误: " . $conn->error;}$conn->close();?>

MySQLi - 使用面向过程的方式创建数据表

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = mysqli_connect($servername, $username, $password, $dbname);// 检测连接if (!$conn) {    die("连接失败: " . mysqli_connect_error());}// 使用 sql 创建数据表$sql = "CREATE TABLE MyGuests (					id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 					firstname VARCHAR(30) NOT NULL,					lastname VARCHAR(30) NOT NULL,					email VARCHAR(50),					reg_date TIMESTAMP				)";if (mysqli_query($conn, $sql)) {    echo "数据表 MyGuests 创建成功";} else {    echo "创建数据表错误: " . mysqli_error($conn);}mysqli_close($conn);?>

PDO 创建数据表

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDBPDO";try {    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);    // 设置 PDO 错误模式,用于抛出异常    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    // 使用 sql 创建数据表   $sql = "CREATE TABLE MyGuests (					id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 					firstname VARCHAR(30) NOT NULL,					lastname VARCHAR(30) NOT NULL,					email VARCHAR(50),					reg_date TIMESTAMP				)";    // 使用 exec() ,没有结果返回 $conn->exec($sql);echo "数据表 MyGuests 创建成功";}catch(PDOException $e){    echo $sql . "<br>" . $e->getMessage();}$conn = null;?>

插入数据

插入语句

INSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...)

MySQLi - 使用面向对象方式插入数据

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// 检测连接if ($conn->connect_error) {    die("连接失败: " . $conn->connect_error);}$sql = "INSERT INTO MyGuests (firstname, lastname, email) 					VALUES ('John', 'Doe', '[email protected]')";					if ($conn->query($sql) === TRUE) {    echo "新记录插入成功";} else {    echo "Error: " . $sql . "<br>" . $conn->error;}$conn->close();?>

MySQLi - 使用面向过程方式插入数据

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = mysqli_connect($servername, $username, $password, $dbname);// 检测连接if (!$conn) {    die("Connection failed: " . mysqli_connect_error());}$sql = "INSERT INTO MyGuests (firstname, lastname, email)				VALUES ('John', 'Doe', '[email protected]')";if (mysqli_query($conn, $sql)) {    echo "新记录插入成功";} else {    echo "Error: " . $sql . "<br>" . mysqli_error($conn);}mysqli_close($conn);?>

POD 插入数据

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDBPDO";try {    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);    // 设置 PDO 错误模式,用于抛出异常    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    $sql = "INSERT INTO MyGuests (firstname, lastname, email)   								VALUES ('John', 'Doe', '[email protected]')";    // 使用 exec() ,没有结果返回     $conn->exec($sql);    echo "新记录插入成功";}catch(PDOException $e) {    echo $sql . "<br>" . $e->getMessage();}$conn = null;?>

插入多条数据

MySQLi - 使用面向对象方式插入多条数据

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建链接$conn = new mysqli($servername, $username, $password, $dbname);// 检查链接if ($conn->connect_error) {    die("连接失败: " . $conn->connect_error);}$sql = "INSERT INTO MyGuests (firstname, lastname, email)			VALUES ('John', 'Doe', '[email protected]');";$sql .= "INSERT INTO MyGuests (firstname, lastname, email)			VALUES ('Mary', 'Moe', '[email protected]');";$sql .= "INSERT INTO MyGuests (firstname, lastname, email)			VALUES ('Julie', 'Dooley', '[email protected]')";			if ($conn->multi_query($sql) === TRUE) {    echo "新记录插入成功";} else {    echo "Error: " . $sql . "<br>" . $conn->error;}	$conn->close();?>

MySQLi - 使用面向过程方式插入多条数据

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建链接$conn = mysqli_connect($servername, $username, $password, $dbname);// 检查链接if (!$conn) {    die("连接失败: " . mysqli_connect_error());}$sql = "INSERT INTO MyGuests (firstname, lastname, email)				VALUES ('John', 'Doe', '[email protected]');";$sql .= "INSERT INTO MyGuests (firstname, lastname, email)				VALUES ('Mary', 'Moe', '[email protected]');";$sql .= "INSERT INTO MyGuests (firstname, lastname, email)				VALUES ('Julie', 'Dooley', '[email protected]')";if (mysqli_multi_query($conn, $sql)) {    echo "新记录插入成功";} else {    echo "Error: " . $sql . "<br>" . mysqli_error($conn);}mysqli_close($conn);?>

PDO 插入多条数据

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDBPDO";try {    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);    	// set the PDO error mode to exception    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    // 开始事务    $conn->beginTransaction();    //  SQL 语句    	$conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 							VALUES ('John', 'Doe', '[email protected]')");    	$conn->exec("INSERT INTO MyGuests (firstname, lastname, email)     						VALUES ('Mary', 'Moe', '[email protected]')");    	$conn->exec("INSERT INTO MyGuests (firstname, lastname, email)     						VALUES ('Julie', 'Dooley', '[email protected]')");    	// 提交事务    $conn->commit();	echo "新记录插入成功"; }catch(PDOException $e){    	// 如果执行失败回滚	$conn->rollback();    echo $sql . "<br>" . $e->getMessage();}$conn = null;?>

使用预处理语句插入多条数据

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// 检测连接if ($conn->connect_error) {    die("连接失败: " . $conn->connect_error);} else {    $sql = "INSERT INTO MyGuests VALUES(?, ?, ?)";    // 为  mysqli_stmt_prepare() 初始化statement 对象    $stmt = mysqli_stmt_init($conn);    //预处理语句	if (mysqli_stmt_prepare($stmt, $sql)) {	// 绑定参数        mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);        	// 设置参数并执行    	$firstname = 'John';    $lastname = 'Doe';    $email = '[email protected]';    mysqli_stmt_execute($stmt);            $firstname = 'Mary';    $lastname = 'Moe';    $email = '[email protected]';    mysqli_stmt_execute($stmt);         	$firstname = 'Julie';    	$lastname = 'Dooley';    $email = '[email protected]';    	mysqli_stmt_execute($stmt);    }}?>

参数绑定

mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);

该函数绑定参数查询并将参数传递给数据库。第二个参数是 "sss" 。以下列表展示了参数的类型。

  • i - 整数
  • d - 双精度浮点数
  • s - 字符串
  • b - 布尔值

预处理语句

预处理:创建 SQL 语句模板并发送到数据库。预留的值使用参数 '?' 标记 。数据库解析,编译,对SQL语句模板执行查询优化,并存储结果不输出。最后,将应用绑定的值传递给参数("?" 标记),数据库执行语句

相对直接执行SQL语句,预处理的优点:

  • 预处理语句大大减少了分析时间,只做了一次查询(虽然语句多次执行)。
  • 绑定参数减少了服务器带宽,你只需要发送查询的参数,而不是整个语句。
  • 预处理语句针对SQL注入是非常有用的,因为参数值发送后使用不同的协议,保证了数据的合法性。

MySQLi 使用预处理语句

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// 检测连接if ($conn->connect_error) {    die("连接失败: " . $conn->connect_error);}// 预处理及绑定$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) 														VALUES(?, ?, ?)");$stmt->bind_param("sss", $firstname, $lastname, $email);// 设置参数并执行$firstname = "John";$lastname = "Doe";$email = "[email protected]";$stmt->execute();$firstname = "Mary";$lastname = "Moe";$email = "[email protected]";$stmt->execute();$firstname = "Julie";$lastname = "Dooley";$email = "[email protected]";$stmt->execute();echo "新记录插入成功";$stmt->close();$conn->close();?>

PDO 使用预处理语句

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDBPDO";try {    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);    	// 设置 PDO 错误模式为异常    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    // 预处理 SQL 并绑定参数    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email)    									VALUES (:firstname, :lastname, :email)");    	$stmt->bindParam(':firstname', $firstname);    $stmt->bindParam(':lastname', $lastname);    $stmt->bindParam(':email', $email);    // 插入行    $firstname = "John";    $lastname = "Doe";	$email = "[email protected]";    $stmt->execute();    // 插入其他行    $firstname = "Mary";    $lastname = "Moe";    $email = "[email protected]";    $stmt->execute();    // 插入其他行    $firstname = "Julie";    $lastname = "Dooley";    $email = "[email protected]";    $stmt->execute();    echo "新记录插入成功";    }catch(PDOException $e) {    echo $sql . "<br>" . $e->getMessage();}$conn = null;?>

查询数据

MySQLi -使用面向对象方式查询数据

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// 检测连接if ($conn->connect_error) {    die("连接失败: " . $conn->connect_error);} $sql = "SELECT id, firstname, lastname FROM MyGuests";$result = $conn->query($sql);if ($result->num_rows > 0) {    // 输出每行数据    while($row = $result->fetch_assoc()) 	{        echo "<br> id: ". $row["id"]. " - Name: ". $row["firstname"]. " " . $row["lastname"];    }} else {    echo "0 个结果";}$conn->close();?>

PDO 查询数据

<?phpecho "<table style='border: solid 1px black;'>";echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th><th>Email</th><th>Reg date</th>			</tr>";class TableRows extends RecursiveIteratorIterator {    function __construct($it) {         parent::__construct($it, self::LEAVES_ONLY);     }    function current() {        return "<td style='width: 150px; border: 1px solid black;'>" . 		parent::current(). "</td>";    }    	function beginChildren() {     		echo "<tr>"; 	}     function endChildren() {         echo "</tr>" . "/n";    } }$servername = "localhost";$username = "username";$password = "password";$dbname = "myDBPDO";try {    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    $stmt = $conn->prepare("SELECT * FROM MyGuests");     $stmt->execute();    // 设置结果集为关联数组  	$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);     	foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {         echo $v;    	}    $dsn = null; }catch(PDOException $e){    echo "Error: " . $e->getMessage();}$conn = null;echo "</table>";?>

使用 WHERE 子句

WHERE 子句用于提取满足指定标准的的记录。

语法:

SELECT column_name(s)FROM table_nameWHERE column_name operator value

例子:

<?php$con=mysqli_connect("localhost","username","password","database");// 检测连接if (mysqli_connect_errno()){	echo "连接失败: " . mysqli_connect_error();}$sql = "SELECT * FROM Persons WHERE FirstName='Peter'";$result = mysqli_query($con,$sql);while($row = mysqli_fetch_array($result))   {	echo $row['FirstName'] . " " . $row['LastName'];	echo "<br>";}?>

ORDER BY 关键词

ORDER BY 关键词用于对记录集中的数据进行排序,默认为升序。

语法:

SELECT column_name(s)FROM table_nameORDER BY column_name(s) ASC|DESC

例子:

<?php$con=mysqli_connect("localhost","username","password","database");// 检测连接if (mysqli_connect_errno()){	    echo "连接失败: " . mysqli_connect_error();}$sql = "SELECT * FROM Persons ORDER BY age";$result = mysqli_query($con,$sql);while($row = mysqli_fetch_array($result)){	echo $row['FirstName'];	echo " " . $row['LastName'];	echo " " . $row['Age'];	echo "<br>";}mysqli_close($con);?>

根据两列进行排序

可以根据多个列进行排序.

当按照多个列进行排序时,只有第一列的值相同时才使用第二列:

SELECT column_name(s)FROM table_nameORDER BY column1, column2

Update 更新数据

UPDATE 语句用于更新数据库表中已存在的记录。

语法:

UPDATE table_nameSET column1=value, column2=value2,...WHERE some_column=some_value

注意:如果省去 WHERE 子句,所有的记录都会被更新!

假设已经创建了“Person表”:

FirstNameLastNameAge
PeterGriffin35
GlennQuagmire33

对Person表更新

<?php$con=mysqli_connect("localhost","username","password","database");// 检测连接if (mysqli_connect_errno()){	echo "连接失败: " . mysqli_connect_error();}$sql = "UPDATE Persons SET Age=36 WHERE FirstName='Peter' AND LastName='Griffin'";mysqli_query($con,$sql);mysqli_close($con);?>

更新后:

FirstNameLastNameAge
PeterGriffin36
GlennQuagmire33

Delete 删除数据

DELETE 语句用于从数据库表中删除行。

语法:

DELETE FROM table_nameWHERE some_column = some_value

注意:如果省去 WHERE 子句,所有的记录都会被删除!

假设有表Person

FirstNameLastNameAge
PeterGriffin35
GlennQuagmire33

下面的例子删除 "Persons" 表中所有 LastName='Griffin' 的记录:

<?php$con=mysqli_connect("localhost","username","password","database");// 检测连接if (mysqli_connect_errno()){	echo "连接失败: " . mysqli_connect_error();}$sql = "DELETE FROM Persons WHERE LastName='Griffin'";mysqli_query($con,$sql);mysqli_close($con);?>

删除数据后:

FirstNameLastNameAge
GlennQuagmire33

参考

  • 数据库扩展
  • MySQL Drivers and Plugins
  • PDO和MySQLi区别与选择?
  • PHP数据库

广告 广告

评论区