php连接MySQL数据库及增删改查

  1、连接MySQL数据库$conn = new mysqli($host,$user,$password,$db);

 

$conn->set_charset("utf8");

1.1 判断连接状态
if ($conn->connect_error) {
　　die("数据库连接异常");
}   

//数据库的创建
$db = "create database db";
if($conn -> query($db)){
   echo "成功创建数据库";
}else{
  echo "创建失败";
}

//数据表的创建
$table = "create table table_name(
   id int(10) auto_increment primary key,
   user varchar(20) NOT NULL
);";

//1.1.1 添加数据方法1:预处理语句及绑定参数方法(适合多条查询语句的执行)
$table = $conn -> prepare("insert into user_table(id,user) values(NULL,?)");//准备sql语句
$name = '小明';　　　　　　
$table -> bind_param("s",$name); //(类型:s,类型值);参数s为string,i为interger,d为double,类型值仅可为一变量
if ($table -> execute() && $conn -> affected_rows){     //判断是否添加成功
　　　echo "添加成功";　
}else{
　　　echo "添加失败"; 
}

//1.1.1 添加数据方法二:直接调用query()方法(适合单条语句;多条用";"隔开,用multi_query()方法查询);
$insert = "insert into table(name) values('你好')";
if ($conn -> query($insert) && $conn -> affected_rows){  
　　echo "成功添加";
}else{
　　echo "添加失败";　　
}

//1.1.2 删除数据方法1直接调用query()方法;

$delete = "delete from user_table where user in('张三','1') or id in(1)";//删除user='张三'的列名 或 id=1的列名
if($conn -> query($delete) && $row = $conn -> affected_rows){
   echo "删除成功,一共:$row"."行";
}else{
　　ehco "删除失败";
}

//1.1.2 删除数据方法2:预处理语句及绑定参数
$delete = $conn -> prepare("delete from user_table where id in(?) or user in(?);");
$id = 1;
$name = '张三';
$delete -> bind_param('ii',$id,$name);
if($delete -> execute() && $row = $conn -> affected_rows){
  echo "删除成功,一共:$row"."行";
}else{
　　echo "删除失败";
}

//1.1.3 更改数据方法1:直接query()方法
$update = "update user_table set user = '刘老师' where id = '115';";
if($conn -> query($update) && $row = $conn -> affected_rows){
 　　echo "更改:$row"."行";
}else{
　　echo "更改失败";
}

//1.1.3 更改数据方法2:预处理语句及绑定参数
$update = $conn -> prepare("update user_table set user = ? where id = ?;");
 $user = "黄老师";
 $id = 1;
$update -> bind_param("si",$user,$id);
if($update -> execute() && $row = $conn -> affected_rows){
    echo "更改:$row"."行";
}else{
　　echo "更改失败";
}

//1.1.4 查询数据方法:直接query()方法

$select = "select id,user from user_table ORDER BY user,id DESC "; 
$result = $conn -> query($select);
if($result -> num_rows>0){　　　　　　　　　　　　　　// ORDER BY排序 DESC降序排序
   while ($row = $result -> fetch_array()){　　　　//循环查询并返回指定条件的每条语句
         echo "$row[id]:$row[user]<br>";
   }
}else{
　　echo "没有数据";
}

表关联合查询
$res = $db->query("select message.id,message.title,message.hits,COUNT(comment.id) from message LEFT JOIN comment ON message.id=comment.id GROUP BY message.id ORDER BY COUNT(comment.id) DESC");
while ($row = $res -> fetch_assoc()){
      echo '文章id:'.$row['id'].'<br>';
      echo '文章标题:'.$row['title'].'<br>';
      echo '文章点击率:'.$row['hits'].'<br>';
      echo '文章评论数量:'.$row['COUNT(comment.id)'].'<br>';
}