封装一个万能的update语句

Update 语句用于修改表中的数据。语法：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

实验对象：两个学生表

一个stu学生表,一个stu1学生表.
上述表有三个字段 (学生id,学生性别,学生名字)

update语句常见场景,分为两大类：

1.单表update

1.1 单表update单字段

update stu t set t.NAME = ‘mike’ where t.ID = ‘1’;

1.2 单表update多字段

update stu t set t.NAME = ‘mike’, t.SEX = ‘1’ where t.ID = ‘2’;

多表关联update的时候,记得要加exists()条件,否则不满足条件的记录被update称NULL：比如：stu表存在,但stu1表不存在的数据,对应的字段会被updat成NULL;

2.1 多表关联update单字段

update stu t set t.NAME = (select t1.NAME from stu1 t1 where t1.ID = t.ID) where exists(select 1 from stu1 t2 where t2.ID = t.ID);

2.2 多表关联update多字段

update stu t set (t.NAME, t.SEX) = (select t1.NAME, t1.SEX from stu1 t1 where t1.ID = t.ID) where exists(select 1 from stu1 t2 where t2.ID = t.ID);

<?php
/**
 * Created by PhpStorm.
 * User: nobita
 * Date: 8/21
 * Time: 16:37
 */

header('content-type:text/html;charset=utf8');
include '../config.php';
$table = 'category';
$data['id'] = 10;
$data['name'] = '电视';
$data['sort_order'] = '30';
$data['parentid'] = 8;
$fields = array_keys($data);
$values = array_values($data);
$fields = array_map(function ($field) use ($data){
    return "`$field` = '$data[$field]'";
},$fields);
function getPrimaryKey($table,$link){
    $sql = "desc `$table`";
    $rs = mysqli_query($link,$sql);
    while ($rows = mysqli_fetch_assoc($rs)){
        if ($rows['Key'] == 'PRI'){
            return $rows['Field'];
        }
    }
}
$pk = getPrimaryKey($table,$link);
$index = array_search($pk,$fields);
unset($fields[$index]);
$fields = implode(',',$fields);
$sql = "update `$table` set ` $fields where `$pk` = $data[$pk]";
echo $sql;

0 likes