数据库基础与常用操作

数据库配置

//数据库连接方式
return[
//    数据库连接类型
    'type'=>$type ??'mysql',
//    数据库默认主机
    'host'=>$host ??'localhost',
//    数据库名字
    'dbname'=>$dbname ??'phpedu',
//    数据库字符集
    'charset'=>$charset ??'utf8',
//    数据库端口号
    'port'=>$port ??'3306',
//    数据库账号
    'username'=>$username ??'root',
//    数据库密码
    'password'=>$password ??'root',
];

连接数据库

namespace pdo_edu;
use PDO;
use Exception;
$config  = require 'config/databases.php';
$type=$config['type'];
$host=$config['host'];
$dbname=$config['dbname'];
$username=$config['username'];
$password=$config['password'];
//创建DSN
$dsn=sprintf('%s:host=%s;dbname=%s',$type,$host,$dbname);
//echo $dsn;
try{
    $pdo=new PDO($dsn,$username,$password);
//    var_dump($pdo);
}catch ( Exception $e){
    die($e->getMessage());
}

增删改查

增加

namespace pdo_edu;
use PDO;
require 'pdo.php';
$sql="INSERT `staff` SET   `name` =?";
$stmt=$pdo->prepare($sql);
$data=['王壮'];
$stmt->execute($data);
//判断是否执行成功
if ($stmt->rowCount()===1){
    echo '新增主键成功，';
}else{
    echo '新增失败';
    print_r($stmt->errorInfo());
}
unset($pdo);

删除

namespace pdo_edu;
require 'pdo.php';
$sql = "DELETE FROM `staff` WHERE `id`=:id";
$stmt=$pdo->prepare($sql);
$id=filter_input(INPUT_GET,'id',FILTER_VALIDATE_INT);
$stmt->execute(['id'=>$_GET['id']]);
if ($stmt->rowCount()===1){
    echo '删除成功';
}

修改

namespace pdo_edu;
require 'pdo.php';
$sql = "UPDATE `staff` SET `name` = ? WHERE `id`=?";
$stmt=$pdo->prepare($sql);
$stmt->execute(['王壮好样的',3]);
if ($stmt->rowCount()===1){
    echo '更新成功';
}else{
    echo '没有更新成功';
    print_r($stmt->errorInfo());
}
unset($pdo);

查询

namespace pdo_edu;
require 'pdo.php';
use PDO;
$sql = 'SELECT * FROM `staff`';
//预处理对象$stmt防止 SQl注入
$stmt=$pdo->prepare($sql);
//使用预处理对象调用 execute()执行这条语句
$stmt->execute();
//echo $staff=$stmt->fetch(PDO::FETCH_ASSOC);
//while ($staff = $stmt->fetch(PDO::FETCH_ASSOC)) {
//    printf('<pre>%s</pre>',print_r($staff,true));
//}
$staff=$stmt->fetchall(PDO::FETCH_ASSOC);
foreach ($staff as $staf){
    printf('id=%s:姓名=%s<br>', $staf['id'], $staf['name']);
}
unset($pdo);