PHP连接MySQL 联表操作 增删改查

联表查询

select *,a.id from stu_message a inner join stu_detail b on a.id=b.sid

联表删除

delete a,b from stu_message a inner join stu_detail b on a.id=b.sid where a.id={$id}

联表修改

update stu_message a inner join stu_detail b on a.id=b.sid
set a.username='{$name}',a.sex={$sex},
b.major='{$zhuanye}',b.address='{$address}' where a.id={$id};

多表插入(只能操作完一个表,再操作另一个)

// 第一个sql语句
$sql = "insert into stu_message(id,username,sex,age,class) value(null,'{$name}',{$sex},{$age},'{$class}')";
// 发送sql语句
$res = mysqli_query($con,$sql);
// 返回上一个insert语句插入的id值
$sid = mysqli_insert_id($con);
// 第二个sql语句
$sql2 = "insert into stu_detail(id,sid,major,address,tel,creatime) value(null,{$sid},'{$zhuanye}','{$address}','{$tel}',{$creatime})";
// 成功操作的结果数量,用来判断第一个sql语句插入成功与否
$num = mysqli_affected_rows($con);
if($num>0){
  $res2 = mysqli_query($con,$sql2);   $num2 = mysqli_affected_rows($con);
  if($num2>0){
    echo "<script>alert('信息创建成功');
  }else{
    echo "<script>alert('信息修改失败');
  }
}else{
  echo "<script>alert('信息修改失败');
}