美文网首页笔记整理
PHP基础回顾之My SQL(七)

PHP基础回顾之My SQL(七)

作者: 桜花約束 | 来源:发表于2018-09-20 21:45 被阅读0次

    连接 MySQL

    <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    
    // 创建连接
    $conn = new mysqli($servername, $username, $password);
    
    // 检测连接
    if ($conn->connect_error) {
        die("连接失败: " . $conn->connect_error);
    } 
    echo "连接成功";
    ?>
    

    关闭连接

    $conn->close(); 
    

    创建 MySQL 数据库
    CREATE DATABASE 语句用于在 MySQL 中创建数据库。在下面的实例中,创建了一个名为 "myDB" 的数据库:

    <?php
    $servername = "localhost";
    $username = "username";
    $password = "password"; 
    // 创建连接
    $conn = new mysqli($servername, $username, $password);
    // 检测连接
    if ($conn->connect_error) {
        die("连接失败: " . $conn->connect_error);
    }  
    // 创建数据库
    $sql = "CREATE DATABASE myDB";
    if ($conn->query($sql) === TRUE) {
        echo "数据库创建成功";
    } else {
        echo "Error creating database: " . $conn->error;
    } 
    $conn->close();
    ?>
    

    创建数据表

    <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";
    
    // 创建连接
    $conn = new mysqli($servername, $username, $password,   $dbname);
    // 检测连接
    if ($conn->connect_error) {
        die("连接失败: " . $conn->connect_error);
    }
    
    // 使用 sql 创建数据表
    $sql = "CREATE TABLE MyGuests (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
    )";
    
    if ($conn->query($sql) === TRUE) {
        echo "Table MyGuests created successfully";
    } else {
        echo "创建数据表错误: " . $conn->error;
    }
    
    $conn->close();
    ?> 
    

    插入数据

     <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";
    
    // 创建连接
    $conn = new mysqli($servername, $username, $password,     $dbname);
    // 检测连接
    if ($conn->connect_error) {
        die("连接失败: " . $conn->connect_error);
    }
    
    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', 'john@example.com')";
    
    if ($conn->query($sql) === TRUE) {
        echo "新记录插入成功";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
    
    $conn->close();
    ?>
    

    插入多条数据

    <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";
    
    // 创建链接
    $conn = new mysqli($servername, $username, $password,     $dbname);
    // 检查链接
    if ($conn->connect_error) {
        die("连接失败: " . $conn->connect_error);
    } 
    
    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', 'john@example.com');";
    $sql .= "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('Mary', 'Moe', 'mary@example.com');";
    $sql .= "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('Julie', 'Dooley', 'julie@example.com')";
    
    if ($conn->multi_query($sql) === TRUE) {
        echo "新记录插入成功";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
    
    $conn->close();
    ?>
    

    MySQLi 预处理语句
    以下实例在 MySQLi 中使用了预处理语句,并绑定了相应的参数:
    实例 (MySQLi 使用预处理语句)

     <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";
    
    // 创建连接
    $conn = new mysqli($servername, $username, $password, $dbname);
    
    // 检测连接
    if ($conn->connect_error) {
        die("连接失败: " . $conn->connect_error);
    }
    
    // 预处理及绑定
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
    $stmt->bind_param("sss", $firstname, $lastname, $email);
    
    // 设置参数并执行
    $firstname = "John";
    $lastname = "Doe";
    $email = "john@example.com";
    $stmt->execute();
    
    $firstname = "Mary";
    $lastname = "Moe";
    $email = "mary@example.com";
    $stmt->execute();
    
    $firstname = "Julie";
    $lastname = "Dooley";
    $email = "julie@example.com";
    $stmt->execute();
    
        echo "新记录插入成功";
    
        $stmt->close();
        $conn->close();
        ?>
    

    从 MySQL 数据库读取数据

    <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";
    
    // 创建连接
    $conn = new mysqli($servername, $username, $password,     $dbname);
    // Check connection
    if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
    } 
    
    $sql = "SELECT id, firstname, lastname FROM MyGuests";
    $result = $conn->query($sql);
    
    if ($result->num_rows > 0) {
    // 输出数据
        while($row = $result->fetch_assoc()) {
              echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " .       $row["lastname"]. "<br>";
        }
    } else {
        echo "0 结果";
    }
    $conn->close();
    ?>
    

    where语句

    <?php
    $con=mysqli_connect("localhost","username","password","database"    );
    // 检测连接
    if (mysqli_connect_errno())
    {
        echo "连接失败: " . mysqli_connect_error();
    }
    
    $result = mysqli_query($con,"SELECT * FROM Persons
    WHERE FirstName='Peter'");
    
    while($row = mysqli_fetch_array($result))
    {
        echo $row['FirstName'] . " " . $row['LastName'];
        echo "<br>";
    }
    ?>
    

    ORDER BY 关键词

    • ORDER BY 关键词用于对记录集中的数据进行排序。
    • ORDER BY 关键词默认对记录进行升序排序。
      -用 DESC 关键字降序排序
      语法
      SELECT column_name(s)
      FROM table_name
      ORDER BY column_name(s) ASC|DESC

    更新数据库中的数据update

    • UPDATE 语句用于更新数据库表中已存在的记录。
      语法
      UPDATE table_name
      SET column1=value, column2=value2,...
      WHERE some_column=some_value

    删除数据库中的数据delete

    • DELETE FROM 语句用于从数据库表中删除记录。
      语法
      DELETE FROM table_name
      WHERE some_column = some_value

    相关文章

      网友评论

        本文标题:PHP基础回顾之My SQL(七)

        本文链接:https://www.haomeiwen.com/subject/waqonftx.html