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

案例1：查询select

使用php连接数据库class9,

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

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

 1 <?php
 2 header("Content-type:text/html; charset=utf-8");
 3 // 建立mysql的链接
 4 // 参数：主机名，用户，密码，需要的数据库
 5 /*
 6 在PHP函数方法前面加@符号，表示忽略警告
 7 */
 8 $conn =@mysqli_connect("localhost","root","","lanou0322");
 9 // 判断数据库链接是否成功
10 /*if($conn){
11 echo "成功了!";
12 }else{
13 echo "失败了!";
14 }
15 */
16 if(!$conn){
17 echo "失败";
18 // 终止
19 exit;
20 }
21 $conn->query('set names utf8');
22 $sql ="SELECT * FROM student";
23 $result = $conn->query($sql);
24 // 5.判断
25 // mysqli_num_rows 返回的条数
26 // echo mysqli_num_rows($result);
27 if(mysqli_num_rows($result)>0){
28 echo "<table border=1>";
29 echo "<tr>
30 <th>id</th>
31 <th>name</th>
32 <th>sex</th>
33 <th>age</th>
34 </tr>";
35 while($row = $result -> fetch_assoc()){
36 echo "<tr>
37 <td style='20'>{$row['id']}</td>
38 <td style='80; text-align:center;'>{$row['name']}</td>
39 <td style='20'>{$row['sex']}</td>
40 <td style='20'>{$row['age']}</td>
41 </tr>";
42 }
43 echo "</table>";
44 //如果想在一个页面输出同样的两个数据库表格那么可以在执行一次$conn->query($sql);然后打印
45 echo "<hr/>";
46 $result = $conn->query($sql);
47 echo "<table border=1>";
48 echo "<tr>
49 <th>id</th>
50 <th>name</th>
51 <th>sex</th>
52 <th>age</th>
53 </tr>";
54 while($row = $result -> fetch_assoc()){
55 echo "<tr>
56 <td style='20'>{$row['id']}</td>
57 <td style='80; text-align:center;'>{$row['name']}</td>
58 <td style='20'>{$row['sex']}</td>
59 <td style='20'>{$row['age']}</td>
60 </tr>";
61 }
62 echo "</table>";
63 }
64 // 关闭数据库
65 $conn->close();//关闭数据库
66 
67 ?>

View Code

效果：

案例2：插入insert

使用php连接数据库

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

2. 插入多条信息

 1 <?php
 2 header("Content-type:text/html;charset=utf-8");
 3 //链接数据库
 4 $conn =@mysqli_connect("localhost","root","","lanou0322");
 5 if(!$conn){
 6 echo "失败";
 7 // 终止
 8 exit;
 9 }
10 $conn->query('set names utf8');
11 //向数据库添加数据INSERT INTO name(数据表名) VALUE （添加的数据信息）;
12 $sql ="INSERT INTO student(name,sex,age) VALUES ('小黄','女','17')";
13 $sql ="SELECT * FROM student";
14 $result = $conn->query($sql);
15 // 判断插入是否成功
16 // 使用 mysqli_affected_rows($conn);
17 if(mysqli_affected_rows($conn)>0){
18 echo "成功";
19 }else{
20 echo "失败";
21 }
22 echo mysqli_num_rows($result);
23 if(mysqli_num_rows($result)>0){
24 echo "<table border=1>";
25 echo "<tr>
26 <th>id</th>
27 <th>name</th>
28 <th>sex</th>
29 <th>age</th>
30 </tr>";
31 while($row = $result -> fetch_assoc()){
32 echo "<tr>
33 <td style='20'>{$row['id']}</td>
34 <td style='80; text-align:center;'>{$row['name']}</td>
35 <td style='20'>{$row['sex']}</td>
36 <td style='20'>{$row['age']}</td>
37 </tr>";
38 }
39 echo "</table>";
40 }
41     $conn->close();//关闭数据库
42 ?>

View Code

效果：

案例3：更新update

使用php连接数据库

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

 1 <?php
 2 header("Content-type:text/html;charset=utf-8");
 3 $conn =@mysqli_connect("localhost","root","","lanou0322");
 4 $conn->query('set names utf8');
 5 // mysqli_query($conn,"UPDATA name SET... WHERE ...")
 6 $sql = mysqli_query($conn,"UPDATE student SET name = '我傻逼',age = '10' WHERE id = 1");
 7 $sql ="SELECT * FROM student";
 8 $result = $conn->query($sql);
 9 echo mysqli_affected_rows($conn);
10 // 判断修改是否成功
11 if(mysqli_affected_rows($conn)>0){
12 echo "成功";
13 }else{
14 echo "失败";
15 }
16 echo mysqli_num_rows($result);
17 if(mysqli_num_rows($result)>0){
18 echo "<table border=1>";
19 echo "<tr>
20 <th>id</th>
21 <th>name</th>
22 <th>sex</th>
23 <th>age</th>
24 </tr>";
25 while($row = $result -> fetch_assoc()){
26 echo "<tr>
27 <td style='20'>{$row['id']}</td>
28 <td style='80; text-align:center;'>{$row['name']}</td>
29 <td style='20'>{$row['sex']}</td>
30 <td style='20'>{$row['age']}</td>
31 </tr>";
32 }
33 echo "</table>";
34 }
35 $conn->close();
36 ?>

View Code

效果：

案例4：删除delete

使用php连接数据库class9,

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

 1 <?php
 2 header("Content-type:text/html;charset=utf-8");
 3 $conn =@mysqli_connect("localhost","root","","lanou0322");
 4 $conn->query('set names utf8');
 5 $sql = mysqli_query($conn,"DELETE FROM student WHERE id = 1");
 6 $sql ="SELECT * FROM student";
 7 $result = $conn->query($sql);
 8 // echo mysqli_affected_rows($conn);
 9 // 判断修改是否成功
10 if(mysqli_affected_rows($conn)>0){
11 echo "成功";
12 }else{
13 echo "失败";
14 }
15 // echo mysqli_num_rows($result);
16 if(mysqli_num_rows($result)>0){
17 echo "<table border=1>";
18 echo "<tr>
19 <th>id</th>
20 <th>name</th>
21 <th>sex</th>
22 <th>age</th>
23 </tr>";
24 while($row = $result -> fetch_assoc()){
25 echo "<tr>
26 <td style='20'>{$row['id']}</td>
27 <td style='80; text-align:center;'>{$row['name']}</td>
28 <td style='20'>{$row['sex']}</td>
29 <td style='20'>{$row['age']}</td>
30 </tr>";
31 }
32 echo "</table>";
33 }
34 $conn->close();
35 ?>

View Code

效果：