PHP 中的 MySQL
- PHP 通过 mysqli 操作 MySQL,需要在 php.ini 中打开
extension=php_mysqli.dll
- PHP 通过 PDO(PHP Data Object)操作数据,需要在 php.ini 中打开
extension=php_pdo_mysql.dll
数据库操作
PHP 对数据库操作有三种方式:面向对象方式、面向过程方式、PDO方式
面向对象方式
<?php
$servername = "localhost";
$username = "root";
$password = "???";
$conn = new mysqli($servername, $username, $password);
if ($conn->connect_error) {
die("连接失败,Error: " . $conn->connect_error . "<br>");
} else {
echo "连接成功!<br>";
// 创建数据库
$sql = "CREATE DATABASE test";
if ($conn->query($sql) === TRUE) {
echo "创建数据库成功!<br>";
} else {
echo "创建数据库失败:" . $conn->error . "<br>";
}
// 进入数据库
$conn->select_db("test");
//$sql = "USE test";
//$conn->query($sql);
// 创建表
$sql = "CREATE TABLE User (
id INT AUTO_INCREMENT PRIMARY KEY,
name TEXT,
email TEXT
)";
if ($conn->query($sql) === TRUE) {
echo "创建表成功!<br>";
} else {
echo "创建表失败!" . $conn->error . "<br>";
}
// 插入数据1
$sql = "INSERT INTO User(name, email) VALUES ('Cynhard', 'cynhard85@126.com')";
if ($conn->query($sql) === TRUE) {
echo "插入数据成功!<br>";
} else {
echo "插入数据失败!" . $conn->error . "<br>";
}
// 插入数据2
$stmt = $conn->prepare("INSERT INTO User(name, email) VALUES (?,?)");
// i - integer
// d - double
// s - string
// b - BLOB
$stmt->bind_param("ss", $name, $email);
$name = "Gopher";
$email = "gopher@ggg.com";
if ($stmt->execute() === TRUE) {
echo "插入数据成功!<br>";
} else {
echo "插入数据失败!" . $conn->error . "<br>";
}
$name = "PHPer";
$email = "php@ppp.com";
if ($stmt->execute() === TRUE) {
echo "插入数据成功!<br>";
} else {
echo "插入数据失败!" . $conn->error . "<br>";
}
$stmt->close();
// 获取最后一次插入的ID
$lastId = $conn->insert_id;
// 删除数据
$sql = "DELETE FROM User WHERE id=$lastId";
if ($conn->query($sql) === TRUE) {
echo "删除数据成功!<br>";
} else {
echo "删除数据失败!" . $conn->error . "<br>";
}
// 修改数据
$sql = "UPDATE User SET name='Comaxy' WHERE id=1";
if ($conn->query($sql) === TRUE) {
echo "修改数据成功!<br>";
} else {
echo "修改数据失败!" . $conn->error . "<br>";
}
// 查找数据
$sql = "SELECT * FROM User";
$result = $conn->query($sql);
if ($result === FALSE) {
echo "查找失败!Error:" . $conn->error . "<br>";
} else {
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "id: " . $row["id"] . ", name: " . $row["name"] . ", email: " . $row["email"] . "<br>";
}
}
}
// 删除表
$sql = "DROP TABLE User";
if ($conn->query($sql) === TRUE) {
echo "删除表成功!<br>";
} else {
echo "删除表失败!" . $conn->error . "<br>";
}
// 删除数据库
$sql = "DROP DATABASE test";
if ($conn->query($sql) === TRUE) {
echo "删除数据库成功!<br>";
} else {
echo "删除数据库失败!" . $conn->error . "<br>";
}
// 关闭数据库
$conn->close();
echo "数据库已关闭!<br>";
}
?>
面向过程方式
<?php
$servername = "localhost";
$username = "root";
$password = "???";
$conn = mysqli_connect($servername, $username, $password);
if (!$conn) {
die("连接失败,Error: " . mysqli_connect_error() . "<br>");
} else {
echo "连接成功!<br>";
// 创建数据库
$sql = "CREATE DATABASE test";
if (mysqli_query($conn, $sql) === TRUE) {
echo "创建数据库成功!<br>";
} else {
echo "创建数据库失败:" . mysqli_error($conn) . "<br>";
}
// 进入数据库
mysqli_select_db($conn, "test");
//$sql = "USE test";
//mysqli_query($conn, $sql);
// 创建表
$sql = "CREATE TABLE User (
id INT AUTO_INCREMENT PRIMARY KEY,
name TEXT,
email TEXT
)";
if (mysqli_query($conn, $sql) === TRUE) {
echo "创建表成功!<br>";
} else {
echo "创建表失败!" . mysqli_error($conn) . "<br>";
}
// 插入数据
$sql = "INSERT INTO User(name, email) VALUES ('Cynhard', 'cynhard85@126.com')";
if (mysqli_query($conn, $sql) === TRUE) {
echo "插入数据成功!<br>";
} else {
echo "插入数据失败!" . msqli_error($conn) . "<br>";
}
// 获取最后一次插入的ID
$lastId = mysqli_insert_id($conn);
// 修改数据
$sql = "UPDATE User SET name='Comaxy' WHERE id=1";
if (mysqli_query($conn, $sql) === TRUE) {
echo "修改数据成功!<br>";
} else {
echo "修改数据失败!" . msqli_error($conn) . "<br>";
}
// 查找数据
$sql = "SELECT * FROM User";
$result = mysqli_query($conn, $sql);
if ($result === FALSE) {
echo "查找失败!Error:" . msqli_error($conn) . "<br>";
} else {
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"] . ", name: " . $row["name"] . ", email: " . $row["email"] . "<br>";
}
}
}
// 删除数据
$sql = "DELETE FROM User WHERE id=$lastId";
if (mysqli_query($conn, $sql)) {
echo "删除数据成功!<br>";
} else {
echo "删除数据失败!" . msqli_error($conn) . "<br>";
}
// 删除表
$sql = "DROP TABLE User";
if (mysqli_query($conn, $sql)) {
echo "删除表成功!<br>";
} else {
echo "删除表失败!" . msqli_error($conn) . "<br>";
}
// 删除数据库
$sql = "DROP DATABASE test";
if (mysqli_query($conn, $sql)) {
echo "删除数据库成功!<br>";
} else {
echo "删除数据库失败!" . msqli_error($conn) . "<br>";
}
// 关闭数据库
mysqli_close($conn);
echo "数据库已关闭!<br>";
}
?>
PDO方式
<?php
$servername = "localhost";
$username = "root";
$password = "33857431lxy4978";
try {
$conn = new PDO("mysql:host=$servername;dbname=test", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "连接成功!<br>";
// 创建表
$sql = "CREATE TABLE User (
id INT AUTO_INCREMENT PRIMARY KEY,
name TEXT,
email TEXT
)";
$conn->exec($sql);
echo "创建表成功!<br>";
// 插入数据1
$sql = "INSERT INTO User(name, email) VALUES ('Cynhard', 'cynhard85@126.com')";
$conn->exec($sql);
echo "插入数据成功!<br>";
// 插入数据2
$stmt = $conn->prepare("INSERT INTO User(name, email) VALUES (:name, :email)");
$stmt->bindParam(":name", $name);
$stmt->bindParam(":email", $email);
$name = "Gopher";
$email = "gopher@ggg.com";
$stmt->execute();
echo "插入数据成功!<br>";
$name = "PHPer";
$email = "php@ppp.com";
$stmt->execute();
echo "插入数据成功!<br>";
// 获取最后一次插入的ID
$lastId = $conn->lastInsertId();
// 删除数据
$sql = "DELETE FROM User WHERE id=$lastId";
$conn->exec($sql);
echo "删除数据成功!<br>";
// 修改数据
$sql = "UPDATE User SET name='Comaxy' WHERE id=1";
$conn->exec($sql);
echo "修改数据成功!<br>";
// 查找数据
$sql = "SELECT * FROM User";
$stmt = $conn->prepare($sql);
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$result = $stmt->fetchAll();
foreach ($result as $i => $row) {
echo "id: " . $row["id"] . ", name: " . $row["name"] . ", email: " . $row["email"] . "<br>";
}
// 删除表
$sql = "DROP TABLE User";
$conn->exec($sql);
echo "删除表成功!<br>";
// 删除数据库
$sql = "DROP DATABASE test";
$conn->exec($sql);
echo "删除数据库成功!<br>";
// 关闭数据库
$conn = null;
echo "数据库已关闭!<br>";
} catch (PDOException $e) {
echo "连接失败,Error:" . $e->getMessage() . "<br>";
}
?>