mysql database method

MySQL is an open source relational database management system that is widely used in web development, enterprise applications, e-commerce and other fields. MySQL provides a rich set of methods that allow developers to access and operate the database conveniently and efficiently. This article will introduce common methods of MySQL database and how to use them.

1. Connecting to MySQL database

Connecting to MySQL database is a necessary step before performing any operation. In MySQL, we can use mysqli and PDO to connect to the database. Among them, mysqli is an object-oriented connection method, and PDO is the data object of PHP.

Use mysqli to connect to MySQL:

a1cbec5c147fb03fe279710dae0486d8connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Use PDO to connect to MySQL:

3fce78ffdc131bddf46042d05bbb26faFailed to connect to database39528cedfa926ea0c01e69ef5b2ea9b0";
}else{
echo "684271ed9684bde649abda8831d4d355Connected successfully39528cedfa926ea0c01e69ef5b2ea9b0";
}
?>

2. Execute SQL statements

In MySQL, executing SQL statements is the core method of operating the database. In PHP, we can use mysqli and PDO to execute SQL statements.

Use mysqli to execute SQL statements:

965315da454505c9e39000b862367cb0query($sql );

if ($result->num_rows > 0) {
// Output data
while($row = $result->fetch_assoc()) {

echo "id: " . $row["id"]. " - 姓名: " . $row["firstname"]. " " . $row["lastname"]. "<br>";

}
} else {
echo "0 result";
}
$conn->close();
?>

Use PDO to execute SQL statement:

40deb34e02f95a2f551557d81f5aa22equery($sql);

foreach ($result as $row){

 echo "id: ".$row['id']." - 姓名: ".$row['firstname']." ".$row['lastname']."<br/>";

}
?>

3. Insert data

In MySQL, inserting data is our Frequently used method. We can use the INSERT INTO statement to insert a piece of data.

Use mysqli to insert data:

d30245a51850fe1a798e6e64b676edaaquery($sql) === TRUE) {
echo "New record inserted successfully";
} else {
echo "Error: " . $sql . "0c6dc11e160d3b678d68754cc175188a" . $conn->error;
}

$conn->close();
?>

Insert data using PDO:

eddbcee409ab47a5596d68f7287c862bprepare($sql);
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);

$firstname = "John";
$lastname = "Doe";
$email = "john @example.com";
$stmt->execute();
echo "New record inserted successfully";
?>

4. Update data

Use the UPDATE statement to update data in MySQL. When updating data, we need to use WHERE to limit records.

Use mysqli to update data:

2c35ecc70906888598688d1fc8c0630aquery($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

Use PDO to update data:

5b15772f15a8320c8ffb17af54a8403eprepare($sql);
$stmt->bindParam(':lastname',$lastname);
$stmt->bindParam(':id',$id );

$lastname="Doe";
$id=2;
$stmt->execute();
echo "Record updated successfully";
?> ;

5. Delete data

Use the DELETE statement to delete data in MySQL. When deleting data, you also need to use WHERE to limit records.

Use mysqli to delete data:

d727664797a8c854b045378d79dd785c query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>

Use PDO to delete data:

64e67896f846eb9f0d4b2fa69e26abb6prepare($sql);
$stmt->bindParam(':id',$id);

$id=3;
$stmt->execute();
echo "Record deleted successfully";
?>

6. Query data

In MySQL, we can use the SELECT statement to query data. It should be noted that the query result is a data set, and we need to use a loop to traverse each record.

Use mysqli to query data:

965315da454505c9e39000b862367cb0query($sql) ;

if ($result->num_rows > 0) {
// 输出数据
while($row = $result->fetch_assoc()) {

echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";

}
} else {
echo "0 结果";
}
$conn->close();
?>

使用PDO查询数据：

9e139cda3c44597bcb17fb0c791f23cequery($sql);

foreach ($stmt as $row) {

echo "id: ".$row["id"]." - Name: ".$row["firstname"]." ".$row["lastname"]."<br/>";

}
?>

总结

本文介绍了使用mysqli和PDO两种方式来连接MySQL数据库，并演示了常见的数据操作方法，如插入数据、更新数据、删除数据和查询数据等。熟练掌握这些方法，将有助于提高MySQL数据库的访问和操作效率，使开发工作更加顺利。

The above is the detailed content of mysql database method. For more information, please follow other related articles on the PHP Chinese website!