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

一、php连接mysql数据库

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

<?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();
?>

三、创建表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

<?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);

四、插入数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

<?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); }

五、插入多条数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

<?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() 函数从结果集中取得⼀⾏作为关联数组

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

<?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);