数据库操作总结
一、MYSQLi
1.把数据库连接配置参数单独放到一个文件,用的时候直接引用这个文件
<?php//连接参数return [ //类型 'type' => $type ?? 'mysql', //默认数据库主机名(IP) 'host' => $host ?? 'localhost', //默认数据库名 'dbname' => $type ?? 'tp5', //默认字符编码集 'charset' => $type ?? 'utf8', //默认端口号 'port' => $username ?? '3306', //默认用户名 'username' => $username ?? 'root', //默认用户的密码 'password' => $password ?? 'wang1111' ];
2.把数据库连接过程单独放到一个文件，用的时候直接引用
<?php//.连接数据库//导入配置参数。就是一个数组$config = require __DIR__ . '/../config.php';//关联数组拆分为变量extract($config);//连接数据库$mysqli = new mysqli($host,$username,$password,$dbname);//检测错误if($mysqli->connect_errno) die('Connect Error: ' . $mysqli->connect_error);//字符编码$mysqli->set_charset($charset);
3.数据库的新增操作
<?php//新增操作//1.连接数据库require "connect.php";//2.操作//sql语句，用预处理语句$sql = 'INSERT `user` SET `username`=?,`password`=?,`age`=?;';//然后把sql语句转为stmt对象$stmt = $mysqli->prepare($sql);//给sql语句中的占位符绑定变量$stmt->bind_param('ssi',$username,$password,$age);//给变量赋值$username = 'tangsan';$password = md5('123456');$age = 12;$stmt->execute() or die($stmt->error);printf('成功新增了%S条记录，新增主键ID = %d<br>',$stmt->affected_rows,$stmt->insert_id);//通过遍历添加大量数据$users = [ ['username'=>'Yname','password'=>md5('12346'),'age'=>21], ['username'=>'Cname','password'=>md5('12346'),'age'=>19], ['username'=>'Qname','password'=>md5('12346'),'age'=>28], ['username'=>'Wname','password'=>md5('12346'),'age'=>24], ['username'=>'Ename','password'=>md5('12346'),'age'=>26], ['username'=>'Rname','password'=>md5('12346'),'age'=>23]];foreach($users as $user){extract($user);$stmt->execute() or die($stmt->error);printf('成功新增了%S条记录，新增主键ID = %d<br>',$stmt->affected_rows,$stmt->insert_id);}//3.关闭数据库连接$mysqli->close();
4.数据库的更新操作
<?php//更新操作//1.连接数据库require 'connect.php';//2.操作语句$sql = 'UPDATE `user` SET `username`=?,`password`=?,`age`=? WHERE `id`=?;';//把sql语句转为stmt对象$stmt = $mysqli->prepare($sql);//给stmt对象绑定变量$stmt->bind_param('ssii',$username,$password,$age,$id);//变量赋值$username = 'PQfl1';$password = md5('444421');$age = 88;$id = 3;//执行sql语句$stmt->execute() or die($stmt->error);printf('成功更新了%s条语句',$stmt->affected_rows);//3.关闭数据库$mysqli->close();
5.数据库的删除操作
<?php//删除操作//1.连接数据库require 'connect.php';//2.操作语句$sql = 'DELETE FROM `user` WHERE `id`=?;';//把sql语句转为stmt对象$stmt = $mysqli->prepare($sql);//给stmt对象绑定变量$stmt->bind_param('i',$id);//变量赋值$id = 3;//执行sql语句$stmt->execute() or die($stmt->error);printf('成功删除了%s条语句',$stmt->affected_rows);//3.关闭数据库$mysqli->close();
6.数据库的查询操作
<?php//查询操作//1.连接数据库require 'connect.php';//2.操作语句//$sql = 'SELECT * FROM `user` WHERE `id`>?;';$sql = 'SELECT `id`,`username`,`password`,`age` FROM `user` WHERE `id`>?;';//把sql语句转为stmt对象$stmt = $mysqli->prepare($sql);//给stmt对象绑定变量$stmt->bind_param('i',$id);//变量赋值$id = 3;//执行sql语句$stmt->execute() or die($stmt->error);//获取结果集/* $res = $stmt->get_result();if($res->num_rows === 0) exit('结果为空'); *///print_r($res->fetch_assoc());//1.while遍历数据库/* while ($user = $res->fetch_assoc()){vprintf('%d: %s |%s %d <br> ',$user);} *///2.foreach遍历/* $users = $res->fetch_all(MYSQLI_ASSOC);foreach($users as $user){vprintf('%d:%s*****%s |%d<br>',$user);} *///3.对字段进行绑定$stmt->bind_result($id,$username,$password,$age);while($stmt->fetch()){printf('%d: %s -----%s***%d<br>',$id,$username,$password,$age);}//3.释放结果集//$res->free();//4.关闭数据库$mysqli->close();
二、PDO
1.把数据库连接配置参数单独放到一个文件,用的时候直接引用这个文件
<?php//连接参数return [ //类型 'type' => $type ?? 'mysql', //默认数据库主机名(IP) 'host' => $host ?? 'localhost', //默认数据库名 'dbname' => $type ?? 'tp5', //默认字符编码集 'charset' => $type ?? 'utf8', //默认端口号 'port' => $username ?? '3306', //默认用户名 'username' => $username ?? 'root', //默认用户的密码 'password' => $password ?? 'wang1111' ];
2.把数据库连接过程单独放到一个文件，用的时候直接引用
<?php//pdo连接数据库//导入配置参数，就是一个数组$config = require __DIR__ . '/../config.php';//把数组拆分成变量extract($config);//创建连接对象：连接数据库try{//sprintf()返回格式化字符串，不输出//设置dsn数据库类型：主机名；数据库名；字符集；端口号；$dsn = sprintf('%s:host=%s;dbname=%s;charset=%s;port=%s',$type,$host,$dbname,$charset,$port);var_dump($dsn);//连接数据库$pdo = new PDO($dsn,$username,$password);//设置结果集的默认获取模式，关联数组$pdo ->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);//var_dump($pdo);}catch(PDOException $e){echo $e->getMessage();}catch(Throwable $e){echo $e->getMessage();}
3.数据库新增操作
<?php//pdo新增操作//1.连接require "connect.php";//2.sql语句$sql = 'INSERT `user` SET `username`=?,`password`=?,`age`=?';//把sql语句转换为stmt对象$stmt = $pdo->prepare($sql);//变量绑定//bindparam(参数标识符, 变量,指定参数的类型,类型的长度)$stmt->bindParam(1,$username,PDO::PARAM_STR,30);$stmt->bindParam(2,$password,PDO::PARAM_STR,100);$stmt->bindParam(3,$age,PDO::PARAM_INT,3);//变量赋值$username = 't3kl';$password = md5('8521');$age = 25;//执行stmt语句$stmt->execute();if($stmt->rowCount()>0) echo '新增成功'.$stmt->rowCount.'主键ID='.$pdo->lastInsertId();//3.关闭数据库连接$pdo = null;
4.数据库更新操作
<?php//数据库的更新操作//1.连接require "connect.php";//2.sql语句$sql = 'UPDATE `user` SET `username`=?,`password`=?,`age`=? WHERE `id`=?';//把sql语句转换为stmt对象$stmt = $pdo->prepare($sql);//将值直接绑定到匿名占位符上$stmt -> execute(['wang',md5('4444'),24,57]);if($stmt->rowCount()>0) echo '更新成功'.$stmt->rowCount() .'条记录';//3.关闭数据库连接$pdo = null;
5.数据库删除操作
<?php//数据库的删除操作//1.连接require "connect.php";//2.sql语句$sql = 'DELETE FROM `user` WHERE `id`=?';//把sql语句转换为stmt对象$stmt = $pdo->prepare($sql);//将值直接绑定到匿名占位符上$stmt -> execute([57]);if($stmt->rowCount()>0) echo '删除成功'.$stmt->rowCount() .'条记录';//3.关闭数据库连接$pdo = null;
6.数据库查询操作
<?php//数据库的查询操作//1.连接require "connect.php";//2.sql语句$sql = 'SELECT `id`,`username`,`password` FROM `user` WHERE `id`>?';//把sql语句转换为stmt对象$stmt = $pdo->prepare($sql);//将值直接绑定到匿名占位符上$stmt -> execute([30]);//fetch() + while()遍历结果集while($user = $stmt->fetch()){vprintf('<li>%d:%s|%s</li>',$user);}//3.关闭数据库连接$pdo = null;
- fetchAll() + foreach()遍历结果集
//fetchAll() + foreach()$users = $stmt->fetchAll();foreach ($users as $user){vprintf('<li>%d: %s****%s</li>',$user);}
//将三个字段与三个变量进行绑定$stmt->bindColumn('id',$id);$stmt->bindColumn('username',$username);$stmt->bindColumn('password',$password);while($stmt->fetch(PDO::FETCH_BOUND)){printf('<li>%d: %s | %s</li>',$id,$username,$password);}
三、学习总结
- mysqli和pdo的用法大致都是一样的，首先设置好数据库连接参数，然后连接数据库，执行sql操作语句，把sql语句通过prepare()都转换为stmt对象,然后给stmt对象绑定变量，用bindParam()绑定变量或者bindValue()直接绑定参数，最后用execute()执行预处理语句。
