PHP学习笔记 20 - MySQL

PHP 中的 MySQL

  • PHP 通过 mysqli 操作 MySQL,需要在 php.ini 中打开 extension=php_mysqli.dll
  • PHP 通过 PDO(PHP Data Object)操作数据,需要在 php.ini 中打开 extension=php_pdo_mysql.dll

数据库操作

PHP 对数据库操作有三种方式:面向对象方式、面向过程方式、PDO方式

面向对象方式

<?php
$servername = "localhost";
$username = "root";
$password = "???";

$conn = new mysqli($servername, $username, $password);
if ($conn->connect_error) {
    die("连接失败,Error: " . $conn->connect_error . "<br>");
} else {
    echo "连接成功!<br>";

    // 创建数据库
    $sql = "CREATE DATABASE test";
    if ($conn->query($sql) === TRUE) {
        echo "创建数据库成功!<br>";
    } else {
        echo "创建数据库失败:" . $conn->error . "<br>";
    }

    // 进入数据库
    $conn->select_db("test");
    //$sql = "USE test";
    //$conn->query($sql);

    // 创建表
    $sql = "CREATE TABLE User (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name TEXT,
        email TEXT
        )";
    if ($conn->query($sql) === TRUE) {
        echo "创建表成功!<br>";
    } else {
        echo "创建表失败!" . $conn->error . "<br>";
    }

    // 插入数据1
    $sql = "INSERT INTO User(name, email) VALUES ('Cynhard', 'cynhard85@126.com')";
    if ($conn->query($sql) === TRUE) {
        echo "插入数据成功!<br>";
    } else {
        echo "插入数据失败!" . $conn->error . "<br>";
    }
    // 插入数据2
    $stmt = $conn->prepare("INSERT INTO User(name, email) VALUES (?,?)");
    // i - integer
    // d - double
    // s - string
    // b - BLOB
    $stmt->bind_param("ss", $name, $email);
    $name = "Gopher";
    $email = "gopher@ggg.com";
    if ($stmt->execute() === TRUE) {
        echo "插入数据成功!<br>";
    } else {
        echo "插入数据失败!" . $conn->error . "<br>";
    }
    $name = "PHPer";
    $email = "php@ppp.com";
    if ($stmt->execute() === TRUE) {
        echo "插入数据成功!<br>";
    } else {
        echo "插入数据失败!" . $conn->error . "<br>";
    }
    $stmt->close();

    // 获取最后一次插入的ID
    $lastId = $conn->insert_id;

    // 删除数据
    $sql = "DELETE FROM User WHERE id=$lastId";
    if ($conn->query($sql) === TRUE) {
        echo "删除数据成功!<br>";
    } else {
        echo "删除数据失败!" . $conn->error . "<br>";
    }

    // 修改数据
    $sql = "UPDATE User SET name='Comaxy' WHERE id=1";
    if ($conn->query($sql) === TRUE) {
        echo "修改数据成功!<br>";
    } else {
        echo "修改数据失败!" . $conn->error . "<br>";
    }

    // 查找数据
    $sql = "SELECT * FROM User";
    $result = $conn->query($sql);
    if ($result === FALSE) {
        echo "查找失败!Error:" . $conn->error . "<br>";
    } else {
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                echo "id: " . $row["id"] . ", name: " . $row["name"] . ", email: " . $row["email"] . "<br>";
            }
        }
    }

    // 删除表
    $sql = "DROP TABLE User";
    if ($conn->query($sql) === TRUE) {
        echo "删除表成功!<br>";
    } else {
        echo "删除表失败!" . $conn->error . "<br>";
    }

    // 删除数据库
    $sql = "DROP DATABASE test";
    if ($conn->query($sql) === TRUE) {
        echo "删除数据库成功!<br>";
    } else {
        echo "删除数据库失败!" . $conn->error . "<br>";
    }

    // 关闭数据库
    $conn->close();
    echo "数据库已关闭!<br>";
}

?>

面向过程方式

<?php
$servername = "localhost";
$username = "root";
$password = "???";

$conn = mysqli_connect($servername, $username, $password);
if (!$conn) {
    die("连接失败,Error: " . mysqli_connect_error() . "<br>");
} else {
    echo "连接成功!<br>";

    // 创建数据库
    $sql = "CREATE DATABASE test";
    if (mysqli_query($conn, $sql) === TRUE) {
        echo "创建数据库成功!<br>";
    } else {
        echo "创建数据库失败:" . mysqli_error($conn) . "<br>";
    }

    // 进入数据库
    mysqli_select_db($conn, "test");
    //$sql = "USE test";
    //mysqli_query($conn, $sql);

    // 创建表
    $sql = "CREATE TABLE User (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name TEXT,
        email TEXT
        )";
    if (mysqli_query($conn, $sql) === TRUE) {
        echo "创建表成功!<br>";
    } else {
        echo "创建表失败!" . mysqli_error($conn) . "<br>";
    }

    // 插入数据
    $sql = "INSERT INTO User(name, email) VALUES ('Cynhard', 'cynhard85@126.com')";
    if (mysqli_query($conn, $sql) === TRUE) {
        echo "插入数据成功!<br>";
    } else {
        echo "插入数据失败!" . msqli_error($conn) . "<br>";
    }

    // 获取最后一次插入的ID
    $lastId = mysqli_insert_id($conn);

    // 修改数据
    $sql = "UPDATE User SET name='Comaxy' WHERE id=1";
    if (mysqli_query($conn, $sql) === TRUE) {
        echo "修改数据成功!<br>";
    } else {
        echo "修改数据失败!" . msqli_error($conn) . "<br>";
    }

    // 查找数据
    $sql = "SELECT * FROM User";
    $result = mysqli_query($conn, $sql);
    if ($result === FALSE) {
        echo "查找失败!Error:" . msqli_error($conn) . "<br>";
    } else {
        if (mysqli_num_rows($result) > 0) {
            while ($row = mysqli_fetch_assoc($result)) {
                echo "id: " . $row["id"] . ", name: " . $row["name"] . ", email: " . $row["email"] . "<br>";
            }
        }
    }

    // 删除数据
    $sql = "DELETE FROM User WHERE id=$lastId";
    if (mysqli_query($conn, $sql)) {
        echo "删除数据成功!<br>";
    } else {
        echo "删除数据失败!" . msqli_error($conn) . "<br>";
    }

    // 删除表
    $sql = "DROP TABLE User";
    if (mysqli_query($conn, $sql)) {
        echo "删除表成功!<br>";
    } else {
        echo "删除表失败!" . msqli_error($conn) . "<br>";
    }

    // 删除数据库
    $sql = "DROP DATABASE test";
    if (mysqli_query($conn, $sql)) {
        echo "删除数据库成功!<br>";
    } else {
        echo "删除数据库失败!" . msqli_error($conn) . "<br>";
    }

    // 关闭数据库
    mysqli_close($conn);
    echo "数据库已关闭!<br>";
}

?>

PDO方式

<?php

$servername = "localhost";
$username = "root";
$password = "33857431lxy4978";

try {
    $conn = new PDO("mysql:host=$servername;dbname=test", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "连接成功!<br>";

    // 创建表
    $sql = "CREATE TABLE User (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name TEXT,
    email TEXT
    )";
    $conn->exec($sql);
    echo "创建表成功!<br>";

    // 插入数据1
    $sql = "INSERT INTO User(name, email) VALUES ('Cynhard', 'cynhard85@126.com')";
    $conn->exec($sql);
    echo "插入数据成功!<br>";
    // 插入数据2
    $stmt = $conn->prepare("INSERT INTO User(name, email) VALUES (:name, :email)");
    $stmt->bindParam(":name", $name);
    $stmt->bindParam(":email", $email);
    $name = "Gopher";
    $email = "gopher@ggg.com";
    $stmt->execute();
    echo "插入数据成功!<br>";
    $name = "PHPer";
    $email = "php@ppp.com";
    $stmt->execute();
    echo "插入数据成功!<br>";

    // 获取最后一次插入的ID
    $lastId = $conn->lastInsertId();

    // 删除数据
    $sql = "DELETE FROM User WHERE id=$lastId";
    $conn->exec($sql);
    echo "删除数据成功!<br>";

    // 修改数据
    $sql = "UPDATE User SET name='Comaxy' WHERE id=1";
    $conn->exec($sql);
    echo "修改数据成功!<br>";

    // 查找数据
    $sql = "SELECT * FROM User";
    $stmt = $conn->prepare($sql);
    $stmt->execute();
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $result = $stmt->fetchAll();
    foreach ($result as $i => $row) {
        echo "id: " . $row["id"] . ", name: " . $row["name"] . ", email: " . $row["email"] . "<br>";
    }

    // 删除表
    $sql = "DROP TABLE User";
    $conn->exec($sql);
    echo "删除表成功!<br>";

    // 删除数据库
    $sql = "DROP DATABASE test";
    $conn->exec($sql);
    echo "删除数据库成功!<br>";

    // 关闭数据库
    $conn = null;
    echo "数据库已关闭!<br>";
} catch (PDOException $e) {
    echo "连接失败,Error:" . $e->getMessage() . "<br>";
}
?>
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值