一. 数据库操作

DDL:

DML: insesrt update,delete

DQL: select

1 更新数据

  1. update tableName set field = newValue; // 更新全表数据
  2. 或
  3. update tableName set field = newValue while condition;  // 更新满足条件的数据

2. 删除数据 delete

  1. delete from tableName;
  2. delete from tableName where condition;

3. 删除数据 truncate

  1. truncate tableName;

4. delete 和 truncate 的区别

1、truncate 命令的执行速度，快于 delete 命令。

2、delete 命令删除记录时，会记录大量的日志信息。

3、truncate 命令删除记录后，会恢复 auto_increment 默认值。

4、通过事务，无法恢复 truncate 命令删除的记录。

5.插入单条完整记录

  1. insert into tableName values(value1, value2,....);

注意:插入数据时,值和表中字段列需要一一对应.

6. 插入单条指定字段数据的记录

  1. insert into tableName(field1, field2,...) values(value1, value2,....);

注意: 查询时指定的字段和值需要一一对应.

7. 插入多条记录

全字段完整记录

  1. insert into tableName values(value1, value2,....),(value1, value2,....),...;

指定字段的记录

  1. insert into tableName(field1, field2,...) values (value1, value2,....),(value1, value2,....),...;

二.PDO操作

PHP 中,所有对数据的增删改操作,都使用 exec()方法执行

  1. int PDO::exec(String sql);

三.案例练习(班级信息增删改)

classList.php

  1. <?php
  2.     header("content-type:text/html;charset=utf8");
  3.     // 连接数据库
  4.     $url = "mysql:host=mysql;dbname=database_lesson_32091_14_11223";
  5.     $user = "lesson_32091_14_11223";
  6.     $passwd = "95a08403e5048dcfc27bfd7425cf55ff";
  7.     $db = new PDO($url,$user,$passwd);
  9.     // 判断请求中是否有参数,如果有就是执行删除操作
  10.     if(isset($_GET['name'])) {
  11.         // 如果存在请求参数,取出并拼接SQL语句
  12.         $sql = "delete from class where name = '{$_GET['name']}'";
  13.         echo $sql;
  14.         // 执行SQL
  15.         $row = $db->exec($sql);
  16.         // 如果执行结果影响行数大于0,说明操作成功
  17.         if($row > 0){
  18.             // 弹出弹框,并重定向到当前页面,目的是去除到页面中的请求参数
  19.           echo "<script type='text/javascript'>alert('删除成功'); window.location='classList.php'</script>";
  20.         }
  21.         else
  22.             echo "<script type='text/javascript'>alert('删除失败');</script>";
  23.     }
  27.     // 查询所有数据,包括在删除操作后的结果查询
  28.     // 所以先判断是否应该删除,然后再查询结果
  29.     $sql = "select * from class";
  30.     $st = $db->query($sql);
  31.     $res = $st->fetchAll(PDO::FETCH_ASSOC);
  32. ?>
  35. <!DOCTYPE html>
  36. <html lang="en">
  38. <head>
  39.   <meta charset="UTF-8">
  40.   <meta http-equiv="X-UA-Compatible" content="IE=edge">
  41.   <meta name="viewport" content="width=device-width, initial-scale=1.0">
  42.   <title>Document</title>
  43.   <style>
  44.        *{
  45.           margin: 0;
  46.       }
  47.       #content{
  48.         width:1200px;
  49.         margin:0 auto;
  50.         background: #f2f2f2;
  51.         text-align: center;
  52.       }
  53.       table{
  54.           border-collapse: collapse;
  55.           text-align: center;
  56.           margin: 0 auto;
  57.       }
  58.       tr,th,td{
  59.             border:1px solid black;
  60.       }
  61.   </style>
  62. </head>
  64. <body>
  65.     <div id='content'>
  66.         <h1>班级信息管理</h1>
  67.         <table width='800px'>
  68.             <tr>
  69.                 <th>班级名称</th>
  70.                 <th>班级人数</th>
  71.                 <th>操作</th>
  72.             </tr>
  73.             <?php foreach ($res as $value) { ?>
  74.                 <tr>
  75.                     <td> <?php echo $value['name']; ?></td>
  76.                     <td><?php echo rand(30,50) . "人"?></td>
  77.                     <td>
  78.                         <span>
  79.                         <a href=<?php echo "upDataClass.php?name={$value['name']}";?> >修改</a>
  80.                         </span>
  82.                          <span>
  83.                             <a href=<?php echo "classList.php?name={$value['name']}";?> >删除</a>
  84.                         </span>
  85.                     </td>
  86.                 </tr>
  87.             <?php } ?>
  88.         </table>
  89.     </div>
  90. </body>
  92. </html>

upDataClass.php

  1. <?php
  2.     // 获取参数中的旧班级名
  3.     $name = $_GET['name'];
  4.     echo "oldName:", $name;
  5.     // 
  6.     if(isset($_GET['newname'])){
  7.         $newname = $_GET['newname'];
  8.         $num = $_GET['num'];
  9.         // 判断输入的人数是否是数字
  10.         if(strlen($newname) > 0 && is_numeric($num)){
  11.             header("content-type:text/html;charset=utf8");
  12.             // 连接数据库
  13.             $url = "mysql:host=mysql;dbname=database_lesson_32091_14_11223";
  14.             $user = "lesson_32091_14_11223";
  15.             $passwd = "95a08403e5048dcfc27bfd7425cf55ff";
  16.             $db = new PDO($url,$user,$passwd);
  18.             // 修改数据
  19.             $sql = "update class set name='{$newname}' where name = '{$name}'";
  20.             echo $sql;
  21.             $row = $db->exec($sql);
  22.             // 判断是否修改成功
  23.             if($row > 0){
  24.                 echo "<script>alert('修改成功'); window.location='classList.php';</script>";
  25.             }else{
  26.                echo "<script>alert('修改失败'); '</script>"; 
  27.             }
  28.         }else{
  29.             echo "<script>alert('输入数据不合法'); </script>";
  30.         }
  31.     }
  34. ?>
  38. <!DOCTYPE html>
  39. <html lang="en">
  41.     <head>
  42.         <meta charset="UTF-8">
  43.         <meta http-equiv="X-UA-Compatible" content="IE=edge">
  44.         <meta name="viewport" content="width=device-width, initial-scale=1.0">
  45.         <title>Document</title>
  46.         <style>
  47.            *{
  48.               margin: 0;
  49.           }
  50.           body{
  51.               background: #f2f2f2;
  52.           }
  53.           #content{
  54.             width:500px;
  55.             margin:0 auto;
  56.             background: #f2f2f2;
  57.             text-align: center;
  58.             border: 1px solid orange;
  59.             margin-top: 100px;
  60.             padding: 30px;
  61.           }
  62.           h1{
  63.               margin-bottom: 20px;
  64.           }
  65.           span{
  66.               display: inline-block;
  67.               width:100px;
  68.               height: 30px;
  69.               text-align: left;
  70.           }
  71.           .inp{
  72.               width:200px;
  73.               height: 30px;
  74.           }
  75.           .btn{
  76.               width:100px;
  77.               height: 30px;
  78.               background: blue;
  79.               color:white;
  80.               border-radius:5px;
  81.           }
  82.           p{
  83.               height: 60px;
  84.           }
  86.         </style>
  87.     </head>
  89.     <body>
  90.         <div id="content">
  91.             <h1>修改班级信息</h1>
  92.             <form action="upDataClass.php">
  93.                 <!--因为使用get方式传参数时,指定地址后不能写参数字符串
  94.                 所以解决办法,要么将提交方式改成post,要么使用隐藏域的方式 -->
  95.                 <!--下面添加一个隐藏域标签,在页面渲染时不显示,但是提交时会将数据一起提交上去-->
  96.                 <input type='hidden' name='name' value="<?php echo $name;?>" >
  97.                 <p>
  98.                     <span class='title'>班级名称:</span>
  99.                     <input class='inp' type="text" placeholder="请输入班级名称" name='newname'>
  100.                 </p>
  102.                 <p>
  103.                      <span class='title'>人数:</span>
  104.                     <input class='inp' type="text" placeholder="请输入班级人数" name='num'>
  105.                 </p>
  107.                 <input class='btn' type='submit' value="修改">
  108.             </form>
  109.         </div>
  110.     </body>
  111. </html>

addClass.php

  1. <?php
  3.     if(isset($_GET['newname'])){
  4.         $newname = $_GET['newname'];
  5.         $num = $_GET['num'];
  6.         // 判断输入的人数是否是数字
  7.         if(strlen($newname) > 0 && is_numeric($num)){
  8.             header("content-type:text/html;charset=utf8");
  9.             // 连接数据库
  10.             $url = "mysql:host=mysql;dbname=database_lesson_32091_14_11223";
  11.             $user = "lesson_32091_14_11223";
  12.             $passwd = "95a08403e5048dcfc27bfd7425cf55ff";
  13.             $db = new PDO($url,$user,$passwd);
  15.             // 修改数据
  16.             $sql = "insert into class(name) values('{$newname}')";
  17.             echo $sql;
  18.             $row = $db->exec($sql);
  19.             // 判断是否修改成功
  20.             if($row > 0){
  21.                 echo "<script>alert('添加成功'); window.location='classList.php';</script>";
  22.             }else{
  23.                echo "<script>alert('添加操作失败'); '</script>"; 
  24.             }
  25.         }else{
  26.             echo "<script>alert('输入数据不合法'); </script>";
  27.         }
  28.     }   
  29. ?>
  33. <!DOCTYPE html>
  34. <html lang="en">
  36.     <head>
  37.         <meta charset="UTF-8">
  38.         <meta http-equiv="X-UA-Compatible" content="IE=edge">
  39.         <meta name="viewport" content="width=device-width, initial-scale=1.0">
  40.         <title>Document</title>
  41.         <style>
  42.            *{
  43.               margin: 0;
  44.           }
  45.           body{
  46.               background: #f2f2f2;
  47.           }
  48.           #content{
  49.             width:500px;
  50.             margin:0 auto;
  51.             background: #f2f2f2;
  52.             text-align: center;
  53.             border: 1px solid orange;
  54.             margin-top: 100px;
  55.             padding: 30px;
  56.           }
  57.           h1{
  58.               margin-bottom: 20px;
  59.           }
  60.           span{
  61.               display: inline-block;
  62.               width:100px;
  63.               height: 30px;
  64.               text-align: left;
  65.           }
  66.           .inp{
  67.               width:200px;
  68.               height: 30px;
  69.           }
  70.           .btn{
  71.               width:100px;
  72.               height: 30px;
  73.               background: blue;
  74.               color:white;
  75.               border-radius:5px;
  76.           }
  77.           p{
  78.               height: 60px;
  79.           }
  81.         </style>
  82.     </head>
  84.     <body>
  85.         <div id="content">
  86.             <h1>修改班级信息</h1>
  87.             <form action="addClass.php">
  88.                 <p>
  89.                     <span class='title'>班级名称:</span>
  90.                     <input class='inp' type="text" placeholder="请输入班级名称" name='newname'>
  91.                 </p>
  93.                 <p>
  94.                      <span class='title'>人数:</span>
  95.                     <input class='inp' type="text" placeholder="请输入班级人数" name='num'>
  96.                 </p>
  98.                 <input class='btn' type='submit' value="修改">
  99.             </form>
  100.         </div>
  101.     </body>
  102. </html>