关于PHP数据库mysql的一些案例

案例1：查询select

使用php连接数据库class9,

获取数据库的表student中的信息，

然后输出到页面上（用表格套住）

<?php
header("Content-type:text/html; charset=utf-8");
// 建立mysql的链接
// 参数：主机名，用户，密码，需要的数据库
/*
在PHP函数方法前面加@符号，表示忽略警告
*/
$conn =@mysqli_connect("localhost","root","","lanou0322");
// 判断数据库链接是否成功
/*if($conn){
echo "成功了!";
}else{
echo "失败了!";
}
*/
if(!$conn){
echo "失败";
// 终止
exit;
}
$conn->query('set names utf8');
$sql ="SELECT * FROM student";
$result = $conn->query($sql);
// 5.判断
// mysqli_num_rows 返回的条数
// echo mysqli_num_rows($result);
if(mysqli_num_rows($result)>0){
echo "<table border=1>";
echo "<tr>
<th>id</th>
<th>name</th>
<th>sex</th>
<th>age</th>
</tr>";
while($row = $result -> fetch_assoc()){
echo "<tr>
<td style='20'>{$row['id']}</td>
<td style='80; text-align:center;'>{$row['name']}</td>
<td style='20'>{$row['sex']}</td>
<td style='20'>{$row['age']}</td>
</tr>";
}
echo "</table>";
//如果想在一个页面输出同样的两个数据库表格那么可以在执行一次$conn->query($sql);然后打印
echo "<hr/>";
$result = $conn->query($sql);
echo "<table border=1>";
echo "<tr>
<th>id</th>
<th>name</th>
<th>sex</th>
<th>age</th>
</tr>";
while($row = $result -> fetch_assoc()){
echo "<tr>
<td style='20'>{$row['id']}</td>
<td style='80; text-align:center;'>{$row['name']}</td>
<td style='20'>{$row['sex']}</td>
<td style='20'>{$row['age']}</td>
</tr>";
}
echo "</table>";
}
// 关闭数据库
$conn->close();//关闭数据库

?>

效果：

案例2：插入insert

使用php连接数据库

1. 向数据表student,插入一条信息："小明", 28, "男"   添加成功输出提示"添加成功"

 

2. 插入多条信息

<?php
header("Content-type:text/html;charset=utf-8");
//链接数据库
$conn =@mysqli_connect("localhost","root","","lanou0322");
if(!$conn){
echo "失败";
// 终止
exit;
}
$conn->query('set names utf8');
//向数据库添加数据INSERT INTO name(数据表名) VALUE （添加的数据信息）;
$sql ="INSERT INTO student(name,sex,age) VALUES ('小黄','女','17')";
$sql ="SELECT * FROM student";
$result = $conn->query($sql);
// 判断插入是否成功
// 使用 mysqli_affected_rows($conn);
if(mysqli_affected_rows($conn)>0){
echo "成功";
}else{
echo "失败";
}
echo mysqli_num_rows($result);
if(mysqli_num_rows($result)>0){
echo "<table border=1>";
echo "<tr>
<th>id</th>
<th>name</th>
<th>sex</th>
<th>age</th>
</tr>";
while($row = $result -> fetch_assoc()){
echo "<tr>
<td style='20'>{$row['id']}</td>
<td style='80; text-align:center;'>{$row['name']}</td>
<td style='20'>{$row['sex']}</td>
<td style='20'>{$row['age']}</td>
</tr>";
}
echo "</table>";
}
    $conn->close();//关闭数据库
?>

效果：

案例3：更新update

使用php连接数据库

更新(修改)数据表student中，年龄age=28的改为10

<?php
header("Content-type:text/html;charset=utf-8");
$conn =@mysqli_connect("localhost","root","","lanou0322");
$conn->query('set names utf8');
// mysqli_query($conn,"UPDATA name SET... WHERE ...")
$sql = mysqli_query($conn,"UPDATE student SET name = '我傻逼',age = '10' WHERE id = 1");
$sql ="SELECT * FROM student";
$result = $conn->query($sql);
echo mysqli_affected_rows($conn);
// 判断修改是否成功
if(mysqli_affected_rows($conn)>0){
echo "成功";
}else{
echo "失败";
}
echo mysqli_num_rows($result);
if(mysqli_num_rows($result)>0){
echo "<table border=1>";
echo "<tr>
<th>id</th>
<th>name</th>
<th>sex</th>
<th>age</th>
</tr>";
while($row = $result -> fetch_assoc()){
echo "<tr>
<td style='20'>{$row['id']}</td>
<td style='80; text-align:center;'>{$row['name']}</td>
<td style='20'>{$row['sex']}</td>
<td style='20'>{$row['age']}</td>
</tr>";
}
echo "</table>";
}
$conn->close();
?>

效果：

案例4：删除delete

使用php连接数据库class9,

删除数据库的表student中，id号为1的数据

<?php
header("Content-type:text/html;charset=utf-8");
$conn =@mysqli_connect("localhost","root","","lanou0322");
$conn->query('set names utf8');
$sql = mysqli_query($conn,"DELETE FROM student WHERE id = 1");
$sql ="SELECT * FROM student";
$result = $conn->query($sql);
// echo mysqli_affected_rows($conn);
// 判断修改是否成功
if(mysqli_affected_rows($conn)>0){
echo "成功";
}else{
echo "失败";
}
// echo mysqli_num_rows($result);
if(mysqli_num_rows($result)>0){
echo "<table border=1>";
echo "<tr>
<th>id</th>
<th>name</th>
<th>sex</th>
<th>age</th>
</tr>";
while($row = $result -> fetch_assoc()){
echo "<tr>
<td style='20'>{$row['id']}</td>
<td style='80; text-align:center;'>{$row['name']}</td>
<td style='20'>{$row['sex']}</td>
<td style='20'>{$row['age']}</td>
</tr>";
}
echo "</table>";
}
$conn->close();
?>

效果：