php操作数据库

w3c地址：https://www.w3cschool.cn/php/php-mysql-connect.html

1. 连接数据库

<?php
$servername = "localhost";      
$username = "username";     //数据库用户名
$password = "password";     //数据库密码

// 创建连接
$conn = new mysqli($servername,$username,$password);
if($conn->connect_errno){
    echo "mysql_error".$conn->connect_error;
}else{
    echo "mysql_connect successful";
};
?>

2. 创建数据库

$sql = "CREATE DATABASE myDB";  
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}

3. 创建表

创建表名为 users
有 id username password email 4个字段

$servername = "localhost";
$username = "root";
$password = "123456";
$dbname = "myDB";   //数据库的名字

//连接的数据库
$conn = new mysqli($servername,$username,$password,$dbname);
if($conn->connect_errno){
    echo "mysql_error".$conn->connect_error;
}else{
    echo "mysql_connect successful</br>";
};
//创建表
$sql = "CREATE Table users(
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    password VARCHAR(30) NOT NULL,
    email VARCHAR(30)
);";
if ($conn->query($sql) === TRUE) {
    echo "Table created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}

4.向表中插入数据

$sql = "INSERT INTO users (username, password, email) VALUES ('John', '123456', '[email protected]')";

if ($conn->query($sql) === TRUE) {
    echo "INSERT  successfully";
} else {
    echo "INSERT error";
}

5.查询表

fetch_assoc():mysqli_fetch_assoc() 函数从结果集中取得一行作为关联数组。

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

//连接的数据库
$conn = new mysqli($servername,$username,$password,$dbname);
if($conn->connect_errno){
    echo "mysql_error".$conn->connect_error;
}else{
    echo "mysql_connect successful</br>";
};

//查询表内容
$sql = "select * from users";
// 执行sql语句
$result = $conn->query($sql);
if($result->num_rows>0){
    while($row = $result->fetch_assoc()) {
        echo "id: ". $row["id"]. " - Name: ". $row["username"]. " " . $row["password"]. " ".$row["email"]. "<br>";
    }
}else{
    echo "查询失败";
}

以get方法查询某条数据

$sql = "select * from users where id=".$_GET['id'];

6.删除

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

//连接的数据库
$conn = new mysqli($servername,$username,$password,$dbname);
if($conn->connect_errno){
    echo "mysql_error".$conn->connect_error;
}else{
    echo "mysql_connect successful</br>";
};

//删除表中某条内容
$sql = "DELETE FROM users where id=".$_GET['id'];
// 执行sql语句
$result = $conn->query($sql);
if($conn->query($sql)==true){
    echo "删除成功";
}else{
    echo "删除失败";
}

7.更新

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

//连接的数据库
$conn = new mysqli($servername,$username,$password,$dbname);
if($conn->connect_errno){
    echo "mysql_error".$conn->connect_error;
}else{
    echo "mysql_connect successful</br>";
};

//删除表
$sql = "update users set username='moon' where id=2";

//query()执行sql语句
if($conn->query($sql)==true){
    echo "更新成功";
}else{
    echo "更新失败";
}