PHP学习笔记 1.数据库的基本操作

1、连接数据库

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

//创建连接
$conn = new mysqli($servername, $username, $password);

//检测连接
if ($conn->connect_error) {
    die("连接失败：".$conn->connect_error);
}
echo "连接成功"
?>

2、创建数据库

<?php

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

//连接数据库
$conn = new mysqli($servername, $username, $password);
if ($conn->error) {
    die("连接失败".$conn->error);
}

//创建数据库
$sql = "CREATE DATABASE myDB";
if (mysqli_query($conn, $sql)) {
    echo ("数据库创建成功");
} else {
    echo "数据库创建失败".mysqli_error($conn);
}

//关闭数据库
mysqli_close($conn);

?>

3、创建数据表

<?PHP

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

//创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("连接数据库失败");
}

//创建数据库表
$sql = "CREATE TABLE MyGesture (
        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
        firstname VARCHAR (30) NOT NULL ,
        lastname VARCHAR (30) NOT NULL ,
        email VARCHAR (30) NOT NULL ,
        reg_date TIMESTAMP 
        )";

if ($conn->query($sql) == TRUE) {
    echo "数据表创建成功";
} else {
    echo "数据库创建失败".$conn->error;
}

$conn->close();

?>

4、插入单条数据到数据库

<?PHP

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

//创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("连接数据库失败");
}

//插入数据
$sql = "INSERT INTO MyGesture (firstname, lastname, email)
        VALUES ('Yuan', 'Can', '498441403@qq.com')";

if ($conn->query($sql) == TURE) {
    echo "数据插入成功";
} else {
    echo "数据插入失败".$conn->error;
}

//关闭数据库
$conn->close();

?>

5、插入多条数据

<?PHP

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

//创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("连接数据库失败");
}

//插入数据
$sql = "INSERT INTO MyGesture (firstname, lastname, email)
        VALUES ('Yuan1', 'Can1', '498441403@qq.com');";

$sql .= "INSERT INTO MyGesture (firstname, lastname, email)
        VALUES ('Yuan2', 'Can2', '498441403@qq.com');";

$sql .= "INSERT INTO MyGesture (firstname, lastname, email)
        VALUES ('Yuan3', 'Can3', '498441403@qq.com');";

$sql .= "INSERT INTO MyGesture (firstname, lastname, email)
        VALUES ('Yuan4', 'Can4', '498441403@qq.com')";

if ($conn->multi_query($sql) == TURE) {
    echo "数据插入成功";
} else {
    echo "数据插入失败".$conn->error;
}

//关闭数据库
$conn->close();

?>

6、从数据库读取数据

<?PHP

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

//创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("连接数据库失败");
}

$sql = "SELECT id, firstname ,lastname FROM MyGesture";
$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();

?>

7、查询数据

<?PHP

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

//创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("连接数据库失败");
}

$result = mysqli_query($conn,"SELECT * FROM MyGesture WHERE firstname = 'yuan'");

while ($row = mysqli_fetch_array($result))
{
    echo $row['firstname']. " " .$row['lastname'];
    echo "<br>";
}

//关闭数据库
$conn->close();

?>

8、修改数据

<?PHP

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

//创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("连接数据库失败");
}

mysqli_query($conn, "UPDATE MyGesture SET firstname = 'HuoShan'WHERE lastname = 'can' ");

//关闭数据库
$conn->close();

?>

9、删除数据

<?PHP

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

//创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("连接数据库失败");
}

mysqli_query($conn, "DELETE FROM MyGesture WHERE lastname = 'can' ");

//关闭数据库
$conn->close();

?>