數(shù)據(jù)庫操作總結(jié)

一、MYSQLi

1.把數(shù)據(jù)庫連接配置參數(shù)單獨(dú)放到一個(gè)文件,用的時(shí)候直接引用這個(gè)文件

<?php
//連接參數(shù)
return [
    //類型
    'type' => $type ?? 'mysql',
    //默認(rèn)數(shù)據(jù)庫主機(jī)名(IP)
    'host' => $host ?? 'localhost',
    //默認(rèn)數(shù)據(jù)庫名
    'dbname' => $type ?? 'tp5',
    //默認(rèn)字符編碼集
    'charset' => $type ?? 'utf8',
    //默認(rèn)端口號
    'port' => $username ?? '3306',
    //默認(rèn)用戶名
    'username' => $username ?? 'root',
    //默認(rèn)用戶的密碼
    'password' => $password ?? 'wang1111'
 ];

2.把數(shù)據(jù)庫連接過程單獨(dú)放到一個(gè)文件，用的時(shí)候直接引用

<?php
//.連接數(shù)據(jù)庫
//導(dǎo)入配置參數(shù)。就是一個(gè)數(shù)組
$config = require __DIR__ . '/../config.php';
//關(guān)聯(lián)數(shù)組拆分為變量
extract($config);
//連接數(shù)據(jù)庫
$mysqli = new mysqli($host,$username,$password,$dbname);
//檢測錯(cuò)誤
if($mysqli->connect_errno) die('Connect Error: ' . $mysqli->connect_error);
//字符編碼
$mysqli->set_charset($charset);

3.數(shù)據(jù)庫的新增操作

<?php
//新增操作
//1.連接數(shù)據(jù)庫
require "connect.php";
//2.操作
//sql語句，用預(yù)處理語句
$sql = 'INSERT `user` SET `username`=?,`password`=?,`age`=?;';
//然后把sql語句轉(zhuǎn)為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);
//通過遍歷添加大量數(shù)據(jù)
$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.關(guān)閉數(shù)據(jù)庫連接
$mysqli->close();

4.數(shù)據(jù)庫的更新操作

<?php
//更新操作
//1.連接數(shù)據(jù)庫
require 'connect.php';
//2.操作語句
$sql = 'UPDATE `user` SET `username`=?,`password`=?,`age`=? WHERE `id`=?;';
//把sql語句轉(zhuǎn)為stmt對象
$stmt = $mysqli->prepare($sql);
//給stmt對象綁定變量
$stmt->bind_param('ssii',$username,$password,$age,$id);
//變量賦值
$username = 'PQfl1';
$password = md5('444421');
$age = 88;
$id = 3;
//執(zhí)行sql語句
$stmt->execute() or die($stmt->error);
printf('成功更新了%s條語句',$stmt->affected_rows);
//3.關(guān)閉數(shù)據(jù)庫
$mysqli->close();

5.數(shù)據(jù)庫的刪除操作

<?php
//刪除操作
//1.連接數(shù)據(jù)庫
require 'connect.php';
//2.操作語句
$sql = 'DELETE  FROM  `user` WHERE `id`=?;';
//把sql語句轉(zhuǎn)為stmt對象
$stmt = $mysqli->prepare($sql);
//給stmt對象綁定變量
$stmt->bind_param('i',$id);
//變量賦值
$id = 3;
//執(zhí)行sql語句
$stmt->execute() or die($stmt->error);
printf('成功刪除了%s條語句',$stmt->affected_rows);
//3.關(guān)閉數(shù)據(jù)庫
$mysqli->close();

6.數(shù)據(jù)庫的查詢操作

<?php
//查詢操作
//1.連接數(shù)據(jù)庫
require 'connect.php';
//2.操作語句
//$sql = 'SELECT *  FROM  `user` WHERE `id`>?;';
$sql = 'SELECT `id`,`username`,`password`,`age`  FROM  `user` WHERE `id`>?;';
//把sql語句轉(zhuǎn)為stmt對象
$stmt = $mysqli->prepare($sql);
//給stmt對象綁定變量
$stmt->bind_param('i',$id);
//變量賦值
$id = 3;
//執(zhí)行sql語句
$stmt->execute() or die($stmt->error);
//獲取結(jié)果集
/* $res = $stmt->get_result();
if($res->num_rows === 0) exit('結(jié)果為空'); */
//print_r($res->fetch_assoc());
//1.while遍歷數(shù)據(jù)庫
/* 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.對字段進(jìn)行綁定
$stmt->bind_result($id,$username,$password,$age);
while($stmt->fetch()){
printf('%d: %s -----%s***%d<br>',$id,$username,$password,$age);
}
//3.釋放結(jié)果集
//$res->free();
//4.關(guān)閉數(shù)據(jù)庫
$mysqli->close();

二、PDO

1.把數(shù)據(jù)庫連接配置參數(shù)單獨(dú)放到一個(gè)文件,用的時(shí)候直接引用這個(gè)文件

<?php
//連接參數(shù)
return [
    //類型
    'type' => $type ?? 'mysql',
    //默認(rèn)數(shù)據(jù)庫主機(jī)名(IP)
    'host' => $host ?? 'localhost',
    //默認(rèn)數(shù)據(jù)庫名
    'dbname' => $type ?? 'tp5',
    //默認(rèn)字符編碼集
    'charset' => $type ?? 'utf8',
    //默認(rèn)端口號
    'port' => $username ?? '3306',
    //默認(rèn)用戶名
    'username' => $username ?? 'root',
    //默認(rèn)用戶的密碼
    'password' => $password ?? 'wang1111'
 ];

2.把數(shù)據(jù)庫連接過程單獨(dú)放到一個(gè)文件，用的時(shí)候直接引用

<?php
//pdo連接數(shù)據(jù)庫
//導(dǎo)入配置參數(shù)，就是一個(gè)數(shù)組
$config = require __DIR__ . '/../config.php';
//把數(shù)組拆分成變量
extract($config);
//創(chuàng)建連接對象：連接數(shù)據(jù)庫
try{
//sprintf()返回格式化字符串，不輸出
//設(shè)置dsn數(shù)據(jù)庫類型：主機(jī)名；數(shù)據(jù)庫名；字符集；端口號；
$dsn = sprintf('%s:host=%s;dbname=%s;charset=%s;port=%s',$type,$host,$dbname,$charset,$port);
var_dump($dsn);
//連接數(shù)據(jù)庫
$pdo = new PDO($dsn,$username,$password);
//設(shè)置結(jié)果集的默認(rèn)獲取模式，關(guān)聯(lián)數(shù)組
$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.數(shù)據(jù)庫新增操作

<?php
//pdo新增操作
//1.連接
require "connect.php";
//2.sql語句
$sql = 'INSERT `user` SET `username`=?,`password`=?,`age`=?';
//把sql語句轉(zhuǎn)換為stmt對象
$stmt = $pdo->prepare($sql);
//變量綁定
//bindparam(參數(shù)標(biāo)識符, 變量,指定參數(shù)的類型,類型的長度)
$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;
//執(zhí)行stmt語句
$stmt->execute();
if($stmt->rowCount()>0) echo '新增成功'.$stmt->rowCount.'主鍵ID='.$pdo->lastInsertId();
//3.關(guān)閉數(shù)據(jù)庫連接
$pdo = null;

4.數(shù)據(jù)庫更新操作

<?php
//數(shù)據(jù)庫的更新操作
//1.連接
require "connect.php";
//2.sql語句
$sql = 'UPDATE `user` SET `username`=?,`password`=?,`age`=? WHERE `id`=?';
//把sql語句轉(zhuǎn)換為stmt對象
$stmt = $pdo->prepare($sql);
//將值直接綁定到匿名占位符上
$stmt -> execute(['wang',md5('4444'),24,57]);
if($stmt->rowCount()>0) echo '更新成功'.$stmt->rowCount() .'條記錄';
//3.關(guān)閉數(shù)據(jù)庫連接
$pdo = null;

5.數(shù)據(jù)庫刪除操作

<?php
//數(shù)據(jù)庫的刪除操作
//1.連接
require "connect.php";
//2.sql語句
$sql = 'DELETE  FROM `user` WHERE `id`=?';
//把sql語句轉(zhuǎn)換為stmt對象
$stmt = $pdo->prepare($sql);
//將值直接綁定到匿名占位符上
$stmt -> execute([57]);
if($stmt->rowCount()>0) echo '刪除成功'.$stmt->rowCount() .'條記錄';
//3.關(guān)閉數(shù)據(jù)庫連接
$pdo = null;

6.數(shù)據(jù)庫查詢操作

• fetch() + while()遍歷結(jié)果集

<?php
//數(shù)據(jù)庫的查詢操作
//1.連接
require "connect.php";
//2.sql語句
$sql = 'SELECT `id`,`username`,`password`  FROM `user` WHERE `id`>?';
//把sql語句轉(zhuǎn)換為stmt對象
$stmt = $pdo->prepare($sql);
//將值直接綁定到匿名占位符上
$stmt -> execute([30]);
//fetch() + while()遍歷結(jié)果集
while($user = $stmt->fetch()){
vprintf('<li>%d:%s|%s</li>',$user);
}
//3.關(guān)閉數(shù)據(jù)庫連接
$pdo = null;

• fetchAll() + foreach()遍歷結(jié)果集

//fetchAll() + foreach()
$users = $stmt->fetchAll();
foreach ($users as $user){
vprintf('<li>%d: %s****%s</li>',$user);
}

• //將三個(gè)字段與三個(gè)變量進(jìn)行綁定
  $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);
  }

三、學(xué)習(xí)總結(jié)

• mysqli和pdo的用法大致都是一樣的，首先設(shè)置好數(shù)據(jù)庫連接參數(shù)，然后連接數(shù)據(jù)庫，執(zhí)行sql操作語句，把sql語句通過prepare()都轉(zhuǎn)換為stmt對象,然后給stmt對象綁定變量，用bindParam()綁定變量或者bindValue()直接綁定參數(shù)，最后用execute()執(zhí)行預(yù)處理語句。

批改老師：天蓬老師

批改狀態(tài)：合格

老師批語：這些內(nèi)容很重要