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;