0
篇帖子
Update 语句用于修改表中的数据。 语法:
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
update语句常见场景,分为两大类:
update stu t set t.NAME = ‘mike’ where t.ID = ‘1’;
update stu t set t.NAME = ‘mike’, t.SEX = ‘1’ where t.ID = ‘2’;
多表关联update的时候,记得要加exists()条件,否则不满足条件的记录被update称NULL: 比如:stu表存在,但stu1表不存在的数据,对应的字段会被updat成NULL;
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);
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;
本博客内所有原创和翻译的文章的版权归本人所有,允许第三方转载,但转载时请务必保留作者名,并注明出处链接,否则本人将保留追究其法律责任的权利。
「人生在世,留句话给我吧」