Community Learn Tools Library Leisure
search
English
Home > List of blog posts > 数据库操作总结
Blogger Information
Blog 55
fans 3
comment 0
visits 54719
Special Recommendation
More>
Related recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
数据库操作总结
王佳祥
Original
900 people have browsed it

数据库操作总结

一、MYSQLi

1.把数据库连接配置参数单独放到一个文件,用的时候直接引用这个文件

  1. <?php
  2. //连接参数
  3. return [
  4.     //类型
  5.     'type' => $type ?? 'mysql',
  7.     //默认数据库主机名(IP)
  8.     'host' => $host ?? 'localhost',
  10.     //默认数据库名
  11.     'dbname' => $type ?? 'tp5',
  13.     //默认字符编码集
  14.     'charset' => $type ?? 'utf8',
  16.     //默认端口号
  17.     'port' => $username ?? '3306',
  19.     //默认用户名
  20.     'username' => $username ?? 'root',
  22.     //默认用户的密码
  23.     'password' => $password ?? 'wang1111'
  24.  ];

2.把数据库连接过程单独放到一个文件，用的时候直接引用

  1. <?php
  2. //.连接数据库
  3. //导入配置参数。就是一个数组
  4. $config = require __DIR__ . '/../config.php';
  5. //关联数组拆分为变量
  6. extract($config);
  7. //连接数据库
  8. $mysqli = new mysqli($host,$username,$password,$dbname);
  9. //检测错误
  10. if($mysqli->connect_errno) die('Connect Error: ' . $mysqli->connect_error);
  11. //字符编码
  12. $mysqli->set_charset($charset);

3.数据库的新增操作

  1. <?php
  2. //新增操作
  3. //1.连接数据库
  4. require "connect.php";
  6. //2.操作
  7. //sql语句，用预处理语句
  8. $sql = 'INSERT `user` SET `username`=?,`password`=?,`age`=?;';
  9. //然后把sql语句转为stmt对象
  10. $stmt = $mysqli->prepare($sql);
  11. //给sql语句中的占位符绑定变量
  12. $stmt->bind_param('ssi',$username,$password,$age);
  13. //给变量赋值
  14. $username = 'tangsan';
  15. $password = md5('123456');
  16. $age = 12;
  17. $stmt->execute() or die($stmt->error);
  18. printf('成功新增了%S条记录，新增主键ID = %d<br>',$stmt->affected_rows,$stmt->insert_id);
  20. //通过遍历添加大量数据
  21. $users = [
  22.     ['username'=>'Yname','password'=>md5('12346'),'age'=>21],
  23.     ['username'=>'Cname','password'=>md5('12346'),'age'=>19],
  24.     ['username'=>'Qname','password'=>md5('12346'),'age'=>28],
  25.     ['username'=>'Wname','password'=>md5('12346'),'age'=>24],
  26.     ['username'=>'Ename','password'=>md5('12346'),'age'=>26],
  27.     ['username'=>'Rname','password'=>md5('12346'),'age'=>23]
  28. ];
  29. foreach($users as $user){
  30. extract($user);
  31. $stmt->execute() or die($stmt->error);
  32. printf('成功新增了%S条记录，新增主键ID = %d<br>',$stmt->affected_rows,$stmt->insert_id);
  33. }
  34. //3.关闭数据库连接
  35. $mysqli->close();

4.数据库的更新操作

  1. <?php
  2. //更新操作
  3. //1.连接数据库
  4. require 'connect.php';
  5. //2.操作语句
  6. $sql = 'UPDATE `user` SET `username`=?,`password`=?,`age`=? WHERE `id`=?;';
  7. //把sql语句转为stmt对象
  8. $stmt = $mysqli->prepare($sql);
  9. //给stmt对象绑定变量
  10. $stmt->bind_param('ssii',$username,$password,$age,$id);
  11. //变量赋值
  12. $username = 'PQfl1';
  13. $password = md5('444421');
  14. $age = 88;
  15. $id = 3;
  16. //执行sql语句
  17. $stmt->execute() or die($stmt->error);
  18. printf('成功更新了%s条语句',$stmt->affected_rows);
  19. //3.关闭数据库
  20. $mysqli->close();

5.数据库的删除操作

  1. <?php
  2. //删除操作
  3. //1.连接数据库
  4. require 'connect.php';
  5. //2.操作语句
  6. $sql = 'DELETE  FROM  `user` WHERE `id`=?;';
  7. //把sql语句转为stmt对象
  8. $stmt = $mysqli->prepare($sql);
  9. //给stmt对象绑定变量
  10. $stmt->bind_param('i',$id);
  11. //变量赋值
  12. $id = 3;
  13. //执行sql语句
  14. $stmt->execute() or die($stmt->error);
  15. printf('成功删除了%s条语句',$stmt->affected_rows);
  16. //3.关闭数据库
  17. $mysqli->close();

6.数据库的查询操作

  1. <?php
  2. //查询操作
  3. //1.连接数据库
  4. require 'connect.php';
  5. //2.操作语句
  6. //$sql = 'SELECT *  FROM  `user` WHERE `id`>?;';
  7. $sql = 'SELECT `id`,`username`,`password`,`age`  FROM  `user` WHERE `id`>?;';
  8. //把sql语句转为stmt对象
  9. $stmt = $mysqli->prepare($sql);
  10. //给stmt对象绑定变量
  11. $stmt->bind_param('i',$id);
  12. //变量赋值
  13. $id = 3;
  14. //执行sql语句
  15. $stmt->execute() or die($stmt->error);
  16. //获取结果集
  17. /* $res = $stmt->get_result();
  18. if($res->num_rows === 0) exit('结果为空'); */
  19. //print_r($res->fetch_assoc());
  20. //1.while遍历数据库
  21. /* while ($user = $res->fetch_assoc()){
  22. vprintf('%d: %s |%s %d <br> ',$user);
  23. } */
  24. //2.foreach遍历
  25. /* $users = $res->fetch_all(MYSQLI_ASSOC);
  26. foreach($users as $user){
  27. vprintf('%d:%s*****%s |%d<br>',$user);
  28. } */
  30. //3.对字段进行绑定
  31. $stmt->bind_result($id,$username,$password,$age);
  32. while($stmt->fetch()){
  33. printf('%d: %s -----%s***%d<br>',$id,$username,$password,$age);
  34. }
  35. //3.释放结果集
  36. //$res->free();
  37. //4.关闭数据库
  38. $mysqli->close();

二、PDO

1.把数据库连接配置参数单独放到一个文件,用的时候直接引用这个文件

  1. <?php
  2. //连接参数
  3. return [
  4.     //类型
  5.     'type' => $type ?? 'mysql',
  7.     //默认数据库主机名(IP)
  8.     'host' => $host ?? 'localhost',
  10.     //默认数据库名
  11.     'dbname' => $type ?? 'tp5',
  13.     //默认字符编码集
  14.     'charset' => $type ?? 'utf8',
  16.     //默认端口号
  17.     'port' => $username ?? '3306',
  19.     //默认用户名
  20.     'username' => $username ?? 'root',
  22.     //默认用户的密码
  23.     'password' => $password ?? 'wang1111'
  24.  ];

2.把数据库连接过程单独放到一个文件，用的时候直接引用

  1. <?php
  2. //pdo连接数据库
  3. //导入配置参数，就是一个数组
  4. $config = require __DIR__ . '/../config.php';
  5. //把数组拆分成变量
  6. extract($config);
  7. //创建连接对象：连接数据库
  8. try{
  9. //sprintf()返回格式化字符串，不输出
  10. //设置dsn数据库类型：主机名；数据库名；字符集；端口号；
  11. $dsn = sprintf('%s:host=%s;dbname=%s;charset=%s;port=%s',$type,$host,$dbname,$charset,$port);
  12. var_dump($dsn);
  13. //连接数据库
  14. $pdo = new PDO($dsn,$username,$password);
  15. //设置结果集的默认获取模式，关联数组
  16. $pdo ->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
  17. //var_dump($pdo);
  18. }catch(PDOException $e){
  19. echo $e->getMessage();
  20. }catch(Throwable $e){
  21. echo $e->getMessage();
  22. }

3.数据库新增操作

  1. <?php
  2. //pdo新增操作
  3. //1.连接
  4. require "connect.php";
  5. //2.sql语句
  6. $sql = 'INSERT `user` SET `username`=?,`password`=?,`age`=?';
  7. //把sql语句转换为stmt对象
  8. $stmt = $pdo->prepare($sql);
  9. //变量绑定
  10. //bindparam(参数标识符, 变量,指定参数的类型,类型的长度)
  11. $stmt->bindParam(1,$username,PDO::PARAM_STR,30);
  12. $stmt->bindParam(2,$password,PDO::PARAM_STR,100);
  13. $stmt->bindParam(3,$age,PDO::PARAM_INT,3);
  14. //变量赋值
  15. $username = 't3kl';
  16. $password = md5('8521');
  17. $age = 25;
  18. //执行stmt语句
  19. $stmt->execute();
  20. if($stmt->rowCount()>0) echo '新增成功'.$stmt->rowCount.'主键ID='.$pdo->lastInsertId();
  22. //3.关闭数据库连接
  23. $pdo = null;

4.数据库更新操作

  1. <?php
  2. //数据库的更新操作
  3. //1.连接
  4. require "connect.php";
  5. //2.sql语句
  6. $sql = 'UPDATE `user` SET `username`=?,`password`=?,`age`=? WHERE `id`=?';
  7. //把sql语句转换为stmt对象
  8. $stmt = $pdo->prepare($sql);
  9. //将值直接绑定到匿名占位符上
  10. $stmt -> execute(['wang',md5('4444'),24,57]);
  11. if($stmt->rowCount()>0) echo '更新成功'.$stmt->rowCount() .'条记录';
  12. //3.关闭数据库连接
  13. $pdo = null;

5.数据库删除操作

  1. <?php
  2. //数据库的删除操作
  3. //1.连接
  4. require "connect.php";
  5. //2.sql语句
  6. $sql = 'DELETE  FROM `user` WHERE `id`=?';
  7. //把sql语句转换为stmt对象
  8. $stmt = $pdo->prepare($sql);
  9. //将值直接绑定到匿名占位符上
  10. $stmt -> execute([57]);
  11. if($stmt->rowCount()>0) echo '删除成功'.$stmt->rowCount() .'条记录';
  12. //3.关闭数据库连接
  13. $pdo = null;

6.数据库查询操作

  • fetch() + while()遍历结果集
  1. <?php
  2. //数据库的查询操作
  3. //1.连接
  4. require "connect.php";
  5. //2.sql语句
  6. $sql = 'SELECT `id`,`username`,`password`  FROM `user` WHERE `id`>?';
  7. //把sql语句转换为stmt对象
  8. $stmt = $pdo->prepare($sql);
  9. //将值直接绑定到匿名占位符上
  10. $stmt -> execute([30]);
  11. //fetch() + while()遍历结果集
  12. while($user = $stmt->fetch()){
  13. vprintf('<li>%d:%s|%s</li>',$user);
  14. }
  15. //3.关闭数据库连接
  16. $pdo = null;

  • fetchAll() + foreach()遍历结果集
  1. //fetchAll() + foreach()
  2. $users = $stmt->fetchAll();
  3. foreach ($users as $user){
  4. vprintf('<li>%d: %s****%s</li>',$user);
  5. }

    1. //将三个字段与三个变量进行绑定
    2. $stmt->bindColumn('id',$id);
    3. $stmt->bindColumn('username',$username);
    4. $stmt->bindColumn('password',$password);
    5. while($stmt->fetch(PDO::FETCH_BOUND)){
    6. printf('<li>%d: %s | %s</li>',$id,$username,$password);
    7. }

三、学习总结

  • mysqli和pdo的用法大致都是一样的，首先设置好数据库连接参数，然后连接数据库，执行sql操作语句，把sql语句通过prepare()都转换为stmt对象,然后给stmt对象绑定变量，用bindParam()绑定变量或者bindValue()直接绑定参数，最后用execute()执行预处理语句。
Correcting teacher：天蓬老师天蓬老师

Correction status：qualified

Teacher's comments：这些内容很重要
Statement of this Website
The copyright of this blog article belongs to the blogger. Please specify the address when reprinting! If there is any infringement or violation of the law, please contact admin@php.cn Report processing!
All comments Speak rationally on civilized internet, please comply with News Comment Service Agreement
0 comments
Author's latest blog post