【php基础】php连接mysql数据库及基本操作02

一、php连接mysql数据库

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

?>

二、创建数据库

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


// 要创建的数据库名称
$db_name = "php";
// 检查数据库是否已经存在
$sql = "SHOW DATABASES LIKE '$db_name'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // 如果数据库存在，则先删除
    $conn->query("DROP DATABASE $db_name");
    echo "数据库删除成功！";
}
// 如果没有指定的数据库，则直接创建
if ($conn->query("CREATE DATABASE $db_name")) {
    echo "数据库创建成功！";
} else {
    echo "创建数据库时出错：". $conn->error;
}
// 关闭连接
$conn->close();
?>

三、创建表

<?php
// 创建连接
$conn =new mysqli("localhost",'root','root','php');
// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
echo "连接成功";
echo "<br>";
//创建数据表语句
$sql="CREATE TABLE test1 (  
 id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,  
 firstname VARCHAR(30) NOT NULL,  
 lastname VARCHAR(30) NOT NULL,  
 email VARCHAR(50),  
 reg_date TIMESTAMP  
)";
//向数据库执⾏sql语句
if(mysqli_query($conn,$sql)){
    echo "创建数据表成功";
    echo "<br>";
}
else{
    echo mysqli_error($conn);
}
//关闭连接
mysqli_close($conn);

四、插入数据

<?php
// 创建连接
$conn =new mysqli("localhost",'root','root','php');
// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
echo "连接成功";
echo "<br>";
#创建插入语句
$sql="insert into test1 (firstname,lastname,email) values('john','Doe','john@example.com')";
#执行语句
if(mysqli_query($conn,$sql)){
    echo "数据插入成功";
}
else {
    echo mysqli_error($conn);
    //关闭连接
    mysqli_close($conn);
}

五、插入多条数据

<?php
// 创建连接
$conn =new mysqli("localhost",'root','root','php');
// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
echo "连接成功";
echo "<br>";
#创建插入语句
// 创建插⼊语句
$sql = "INSERT INTO test1 (firstname, lastname, email) VALUES ('pual', 'bei',
  'pual@example.com');";
$sql .= "INSERT INTO test1 (firstname, lastname, email) VALUES ('Mary', 'Moe',
  'mary@example.com');";
$sql .= "INSERT INTO test1 (firstname, lastname, email) VALUES ('Julie',
  'Dooley', 'julie@example.com')";

#执行语句
if(mysqli_multi_query($conn,$sql)){
    echo "数据批量插入成功";
}
else {
    echo mysqli_error($conn);
    //关闭连接
    mysqli_close($conn);
}

六、读取数据

mysqli_num_rows() ：返回sql语句查询到的总⾏数

mysqli_fetch_assoc() 函数从结果集中取得⼀⾏作为关联数组

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

//创建查询语句
$sql="select id,firstname,lastname from test1";
$res=mysqli_query($conn,$sql);
// 返回sql语句查询到的总⾏数
// mysqli_num_rows($res)
if(mysqli_num_rows($res) > 0){
    while($row = mysqli_fetch_assoc($res)){
        echo "id:" . $row["id"] . "-name:" . $row["firstname"] . " "
            .$row['lastname']."<br>";
    }
}
//关闭连接
mysqli_close($conn);