使用 PDO 关联查询 MySQL 数据

使用pdo关联查询mysql数据

try {
    $pdo = new PDO('mysql:host=localhost;dbname=test;', 'root', '123456');

    // 0.等值联结
    $sql = 'SELECT c.name, o.id, o.customer_id, o.price FROM orders o, customer c WHERE o.customer_id = c.id AND c.name = :name';

    // 1.内联结(与上面等值联结返回的查询结果相同)
    // $sql = 'SELECT c.name, o.id, o.customer_id, o.price FROM orders o INNER JOIN customer c ON o.customer_id = c.id AND c.name=:name';

    // (与内联结相比，外联结还包括没有相关联的行)
    // 2.左外联结(这里联结orders，包括orders没有关联的行 o.price)
    // $sql = 'SELECT c.id, o.price FROM orders o LEFT OUTER JOIN customer c ON o.customer_id = c.id AND c.name=:name';

    // 3.右外联结(这里联结customer，包括customer没有关联的行 c.id)
    // $sql = 'SELECT c.id, o.price FROM orders o RIGHT OUTER JOIN customer c ON o.customer_id = c.id AND c.name=:name';

    // 4.另外还有自联结(self-join，自联结和子查询返回结果一样，但是一般比子查询快)

    // 5.自然联结(natural-join)

    $stmt = $pdo->prepare($sql);

    //$stmt->bindValue(1, 'zhangsan');
    //$stmt->bindParam(1, $name);
    $name = 'zhangsan';
    $stmt->execute(array(':name' => 'zhangsan'));

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        var_dump($row);
    }

} catch (PDOException $e) {
    echo $e->getMessage();
}

附上几个操作图

1.customer和orders表.png 2.等值联结查询.png 3.内联结查询.png 4.左外联结查询.png 5.右外联结查询.png